DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 LUW Info

Home



Kleine Scripte 




listet alle datenbanken in jeder instance  auf dem Rechner

#!/bin/bash


function cmd_rccode {
     local sqlcode

     case $2 in
       0 )  echo "connecttion to $1 war ok"
            return 0               
            ;;
       1 )  echo "No rows Found SQL 100 "
             return 0
                ;;
       2 )  echo "Warning "
             return 0
                ;;
       4 )  echo "DB2 command or SQL statement error"
                ;;
       8 )  echo "Command line processor system errorr"
                ;;   
        * ) echo "Falscher Fehler $2"
             return 0
                ;;
      esac


   for  sqlcode in $3
    do
 
        if [[  $sqlcode == 'SQL1032N' ]]
         then
          echo start db2
          db2start
         fi
        return 4 
    done

}

ilist=$(db2ilist)     #db2 Instancen

oinst=$DB2INSTANCE
for instance in $ilist     # fuer alle Instancen
 do
   echo "DB2INSTANCE="$instance
   export "DB2INSTANCE="$instance  # setzen instance
   dblist=$(db2 list db directory | grep 'Database name' | tr -s ' ' | cut -f5 -d' ')     # Liste aller datenbanken
   for databases in $dblist     # loop fuer alle Datenbanken
    do
    check=$(db2 connect to $databases )      # Irgend was 
    RC=$?
    if (( $RC == 0))
    then
      echo "connect to $databases war OK"
      reset=$(db2 connect reset)
    else
       echo "connect to $databases  RC=$RC "    # Returncode ausgeben
       cmd_rccode $databases $RC $check
       echo $check
    fi
    done
done
 
DB2INSTANCE=$oinst        # Originale Instance wieder zurück setzen




Ausführen dateien die SLQ's erzeugen


beispiel prunelogfiles.gen  erzeugen und ausführen .....

SELECT 'CONNECT TO ' CONCAT CURRENT SERVER CONCAT ' ;'
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'PRUNE LOGFILE PRIOR TO '
CONCAT CHAR(FIRSTLOG,15)
CONCAT ' ;'
FROM ( SELECT FIRSTLOG, START_TIME
       FROM SYSIBMADM.DB_HISTORY
       WHERE OPERATION          = 'B'
       AND   OPERATIONTYPE    IN ('F', 'N')
       ORDER BY START_TIME
       FETCH FIRST 1 ROWS ONLY
     ) T1
UNION ALL
SELECT 'CONNECT RESET ; ' FROM SYSIBM.SYSDUMMY1
FOR FETCH ONLY     WITH UR


aufruf : sqlgen -db sample -gen  prunelogfiles

#!/bin/bash

db=''
gen=''
pgname=$0

for i in $*
 do
  [[ $i == "-db" ]] && { ep="-db"; shift ; continue ; }
  [[ $i == "-gen" ]] && { ep="-gen"; shift ; continue ; }

  if [[ $ep == "-db" ]]
   then
     db=$i
     shift
     continue
   fi
  if [[ $ep == "-gen" ]]
   then
     gen=$i
     shift
     continue
   fi
done

if  [[ $db == "" ]]
 then
   echo '********************************************'
   echo Aufruf $pgname -db datenbank -gen sqlscript
   echo -db missing $dbset
   echo '********************************************'
   exit 1
fi

if [[ $gen == "" ]]
 then
   echo '********************************************'
   echo Aufruf $pgname -db datenbank -gen sqlscript
   echo -gen  missing $genset
   echo '********************************************'
   exit 1
fi

tempfile=$gen$$
rm $tempfile > /dev/null

db2rc=$(db2 "connect to $db")
RC=$?
if (( $RC  != 0))
 then
   echo '********************************************'
   echo "$pgname  :"
   echo "Connection zur Datenbank $db nicht  erfolgreich "
   echo "darum wir die Verareitung abgebrochen  "
   echo "Returncode was $RC   "
   echo '********************************************'
   echo $db2rc
   exit 2
fi

db2 -x -tf $gen -z $tempfile
RC=$?
db2 "connect reset" > /dev/null

if (( $RC == 0))
 then
   echo "start db2 -tvf  $tempfile"
   db2 -tvf $tempfile -z $tempfile.out
   RC=$?
   if (( $RC == 0 ))
    then
       rm $tempfile
    else
       echo '********************************************'
       echo "$pgname  :"
       echo "Ausgabe Script ist Fehlerhaft gelaufen,  "
       echo "darum wir die Verareitung abgebrochen  "
       echo "Returncode was $RC Script=$tempfile  "
       echo '********************************************'
       exit 2
    fi
else
   echo '********************************************'
   echo "$pgname  :"
   echo "SQL Script ist Fehlerhaft gelaufen, "
   echo "darum wir die Verareitung abgebrochen "
   echo "Returncode was $RC Script=$gen "
   echo '********************************************'
   exit 1
fi

exit


Ausführen SLQ's  (db2 +c -tvf xxx.sql)

aufruf : sqlexec -db sample -gen  xxx.sql

#!/bin/bash

db=''
gen=''

for i in $*
 do
  [[ $i == "-db" ]] && { ep="-db"; shift ; continue ; }
  [[ $i == "-gen" ]] && { ep="-gen"; shift ; continue ; }

  if [[ $ep == "-db" ]]
   then
     db=$i
     shift
     continue
   fi
  if [[ $ep == "-gen" ]]
   then
     gen=$i
     shift
     continue
   fi
done

if  [[ $db == "" ]]
 then
   echo '********************************************'
   echo Aufruf sqlexec  -db datenbank -gen sqlscript
   echo -db missing $dbset
   echo '********************************************'
   exit 1
fi

if [[ $gen == "" ]]
 then
   echo '********************************************'
   echo Aufruf sqlexec -db datenbank -gen sqlscript
   echo -gen  missing $genset
   echo '********************************************'
   exit 1
fi

tempfile=$gen.out
rm $tempfile > /dev/null

db2 "connect to $db" >/dev/null
db2 +c -tf $gen -z $tempfile >/dev/null
RC=$?
if (( $RC == 0  ))       # Alles paletti
 then
   db2 commit
elif (( $RC == 1 ))      # SQLCODE 100 iss eigentlich OK oder
 then
   db2 commit
elif (( $RC == 2 ))      # RC Warning  iss das  OK ?? 
 then
   db2 commit
else
   db2 rollback
   echo '********************************************'
   echo "SQL Script ist Fehlerhaft gelaufen, "
   echo "Aenderungen werden rückgängig gemacht (rollback) "
   echo "Returncode was $RC Script=$gen "
   echo '********************************************'
   exit 1
fi

db2 "connect reset" > /dev/null

exit





Kleine Auswertung über die UDI (RTSUDI) mit hilfe von db2pd -tcbstats




#!/usr/bin/bash

db=''
out=''

for i in $*
 do
  [[ $i == "-db" ]]  && { ep="-db"; shift ; continue ; }
  [[ $i == "-out" ]] && { ep="-out"; shift ; continue ; }

  if [[ $ep == "-db" ]]
   then
      db=$i
     shift
     continue
   fi
  if [[ $ep == "-out" ]]
   then
      out=$i
     shift
     continue
   fi
done


if [ "$db" = "" ]
then  
      echo "Eingabe der Datenbank ist zwingend "
      echo "stats_usage -db dbname -out /OutDir"
      echo "Verarbeitung wird unterbrochen "
      exit 8
fi

if [ "$out" = "" ]
then  
     OutputDir="/tmp"
else
     OutputDir=$out
fi


echo $db $out

ok=$(db2 activate database $db)

found="N"
Datetime="`date +%Y-%m-%d-%H:%M:%S`"
db2pd -d $db -tcbstats | tr -s " " " "  >  $OutputDir/db2pd.output

while read line; do
      if [ "$line" == "TCB Table Stats:" ]
       then
          found="Y"
       fi

      if [ $found == 'Y' ]
       then
          set $line
          printf "%-20s%-50s%-13s%-13s%-13s" $3 $2 $4 $5 $6
          printf "%-10s%-10s%-10s" "${11}" "${12}" "${13}"
          printf "\n"  

#         tablename=$2
#         schemanm=$3
#         scans=$4
#         udi=$5
#         rtsudi=$6
#         pgreorgs=$7
#         nochgup=$8
#         dtsreads=$9
#         fscrupdates=${10}
#         inserts=${11}
#         updates=${12}
#         deletes=${13}
#         ovflreads=${14}
#         ovflcrtes=${15}
#         pgdictscrt=${16}
#         cclogreads=${17}
#         storebytes=${18}
#         bytessaved=${19}
#         echo $schemanm  $tablename $scans $udi $rtsudi | awk '{print $1,"\t",$2,"\t",$3,"\t",$4,"\t",$5}'
       fi

done <  $OutputDir/db2pd.output

rm $OutputDir/db2pd.output

exit


Kleines script um Backups zu machen


#!/bin/bash

initvar() {

db=''
test=''
offline=''
online=''
incremental=''
delta=''
compress=''
nocompress=''
tablespace=''
dbcfg=''
dbmcfg=''
db2set=''
u=''
p=''
utilimpact=''
parallelism=''
buffers=''
buffer=''
out=''
usetsm=''
outunique=''
to='/databases/dbbackup'
todir="$to"
help=''
komma=''
dircnt=0


}

dbparms() {

connect_db

logarchmeth1=$(db2 -x "select Trim(upper(value)) from sysibmadm.dbcfg where upper(name) = 'LOGARCHMETH1'  fetch first 1 rows only for fetch only with ur ")
logarchmeth1="$(echo -e "${logarchmeth1}" | tr -d '[:space:]')" 
keep=$(db2 -x "select value from sysibmadm.dbcfg where upper(name) = 'NUM_DB_BACKUPS'  fetch first 1 rows only for fetch only with ur ")

connect_reset

}

readcmd() {
#   echo "Anzahl der Parameter in der Kommandozeile : $#"
   for var in $*
   do
   [[ $var == "-db" ]] && {   ep=$var ; shift; continue ; }
   [[ $var == "-u" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-p" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-utilimpact" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-parallelism" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-buffers" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-buffer" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-out" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-to" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-tablespace" ]] && { ep=$var ; shift; continue ; }
   [[ $var == "-logs" ]] && { ep=$var ; shift; continue ; }
  
   [[ $var == "-test" ]] && { test='test'; shift ;  continue ; }
   [[ $var == "-offline" ]] && { offline='offline'; shift ;  continue ; }
   [[ $var == "-online" ]] && { online='online'; shift ;  continue ; }
   [[ $var == "-incremental" ]] && { incremental='incremental'; shift ;  continue ; }
   [[ $var == "-delta" ]] && { delta='delta'; shift ;  continue ; }
   [[ $var == "-compress" ]] && { compress='compress'; shift ;  continue ; }
   [[ $var == "-nocompress" ]] && { nocompress='nocompress'; shift ;  continue ; }
   [[ $var == "-dbcfg" ]] && { dbcfg='dbcfg'; shift ;  continue ; }
   [[ $var == "-dbmcfg" ]] && { dbmcfg='dbmcfg'; shift ;  continue ; }
   [[ $var == "-db2set" ]] && { db2set='db2set'; shift ;  continue ; }
   [[ $var == "-usetsm" ]] && { usetsm='usetsm'; shift ;  continue ; }
   [[ $var == "-help" ]] && { help='help'; shift ;  continue ; }

   if [[ $ep == "-db" ]]
    then
      db=$var
      db="$(echo $var | tr '[a-z]' '[A-Z]')"
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-u" ]]
    then
      u=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-p" ]]
    then
      p=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-utilimpact" ]]
    then
      utilimpact=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-parallelism" ]]
    then
      parallelism=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-buffers" ]]
    then
      buffers=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-buffer" ]]
    then
      buffer=$var
      ep=''
      shift
      continue
     fi
   if [[ $ep == "-out" ]]
    then
      out=$var
      ep=''
      shift
      continue
     fi

   if [[ $ep == "-to" ]]
    then
      (( dircnt = dircnt + 1 ))
      if [ $dircnt -eq 1 ]
       then 
       todir=$var
       fi

      to="$to $var"
      shift
      continue
     fi
     
   if [[ $ep == "-tablespace" ]]
    then
          tablespace="$tablespace $komma $var"
      komma=','
      shift
      continue
     fi

   if [[ $ep == "-logs" ]]
    then
      logs="$(echo $var | tr '[a-z]' '[A-Z]')"
      ep=''
      shift
      continue
     fi
     
     
     
   done
}

sayhelp() {  
       echo $prog" -DB <Datenbank> <-TEST> <-ONLINE> <-OFFLINE> <-INCREMENTAL> <-DELTA> <-U userid> <-P Password>"
       echo "    "
       echo "  1.) Backup der eingegebenen Datenbank            "
       echo "  -------------------------------------------------"
       echo "  Optionen : -db <datenbankalias>   Name der Datenbank              "
       echo "             -test                  commands erstellen aber nicht ausfueren "
       echo "             -offline               OFFLINE BACKUP wird ausgefuert  "
       echo "             -online                ONLINE  BACKUP wird ausgefuert  "
       echo "             -incremental           Incemental Backup               "
       echo "             -delta                 Incemental/DELTA Backup         "
       echo "             -nocompress            Backup ohne Compress            "
       echo "             -tablespace            Backup Mit Tablespace Option    "
       echo "             -dbcfg                 Auslesen DB  Config Parameter   "
       echo "             -dbmcfg                Auslesen DBM Config Parameter   "
       echo "             -db2set                Auslesen DB2 ENVIRONMENT        "
       echo "             -u <userid>            Userid   Uberschreibt IniDatei  "
       echo "             -p <password>          Password Uberschreibt IniDatei  "
       echo "             -utilimpact <prio>     UTIL_IMPACT_PRIORITY default 20 "
       echo "             -parallelism <prio>    PARALLELISM default 2           "
       echo "             -buffers <buffers>     WITH 4 BUFFERS                  "
       echo "             -buffer <buffer>       BUFFER  1024                    "
       echo "             -out  <dateiname>      Ausgabe in Datei                "
       echo "             -logs                  Include / Exclude Logs bei Online Backup"             
       echo "             -usetsm                Parameter USE TSM               "
       echo "             -outunique             Ausgabedatei wir mit Timestamp Versehen "
       echo "             -to <backupdir, ....>  Backup Directory ueberschreibt INI-Datei"
       echo "                                    mehrere Diriectorys mit komma trennen"
       echo "                                    D:\DBBACKUP,C:\DBBACKUP  /backup1,/home/backup2,/xxx"
       echo "             -help                  Diese Anzeige "
       echo "   "
       echo "   "
       exit
}

connect_db() {  
if [[ $u == "" ]]
 then
 db2rc=$(db2 "connect to $db")
 else
 db2rc=$(db2 "connect to $db user $u using $p")
fi

rc=$?
if [ $rc -ne 0 ]
 then 
   echo "Fehler beim connect $db"
   echo "Anwendung wird beendet "
   echo "sqlcode $db2rc"
   exit 1
 fi

}

connect_reset() {  

db2c=$(db2 "connect reset")
db2c=$(db2 "terminate")

}

prune_logfiles() {

connect_db

if [ $logarchmeth1 == "OFF" ]
 then
    connect_reset
    return 0   
fi

sqlstmt=$(db2 -x "select rn, firstlog, START_TIME  from (SELECT ROWNUMBER() OVER(ORDER BY  Substr(START_TIME, 1, 8)   ) as rn , FIRSTLOG, START_TIME FROM SYSIBMADM.DB_HISTORY WHERE OPERATION  = 'B' AND OPERATIONTYPE IN ('F', 'N') AND SQLCODE IS NULL ORDER BY START_TIME desc ) t1  fetch first 1 rows only"  )

rc=$?
if [ $rc -eq 0 ]
 then 
   set $sqlstmt
   echo "db2 PRUNE LOGFILE PRIOR TO "$2
   plogs=$(db2 "prune logfile prior to "$2)
 fi

connect_reset

}

prune_history() {

connect_db

sqlstmt=$(db2 -x "select rn, firstlog, START_TIME  from (SELECT ROWNUMBER() OVER(ORDER BY  Substr(START_TIME, 1, 8) desc) as rn , FIRSTLOG, START_TIME FROM SYSIBMADM.DB_HISTORY WHERE OPERATION  = 'B' AND OPERATIONTYPE IN ('F', 'N') AND SQLCODE IS NULL ORDER BY START_TIME ) t1 where rn > $keep fetch first 1 rows only"  )

rc=$?
if [ $rc -eq 0 ] 
 then
   set $sqlstmt
   echo "db2 PRUNE HISTORY "$3" WITH FORCE OPTION AND DELETE"
   phist=$(db2 "prune history "$3" with force option and delete")
 fi

connect_reset

}

deloldcopy() {

connect_db

stmt=""
stmt=$stmt"Select backupname "
stmt=$stmt"from ( "
stmt=$stmt"select RTrim(LOCATION   ) "
stmt=$stmt"concat case when (SELECT    OS_NAME FROM "SYSIBMADM".ENV_SYS_INFO FETCH FIRST 1 ROWS ONLY ) Like 'WIN%' "
stmt=$stmt"Then '\'  ELSE     '/' END "
stmt=$stmt"concat RTrim(Current Server) "
stmt=$stmt"concat '.0.' "
stmt=$stmt"concat RTrim((SELECT INST_NAME  FROM SYSIBMADM.ENV_INST_INFO FETCH FIRST 1 ROWS ONLY  ) ) "
stmt=$stmt"concat '.' "
stmt=$stmt"concat 'DBPART'"
stmt=$stmt"Concat CHAR(SUBSTR(DIGITS(DBPARTITIONNUM)     , 3, 3), 3)  "
stmt=$stmt"concat '.' "
stmt=$stmt"concat RTrim(Char(START_TIME)) "
stmt=$stmt"concat '.*'"
stmt=$stmt"as backupname "
stmt=$stmt", ROWNUMBER() OVER(ORDER BY  Substr(START_TIME, 1, 8)  ) as rn "
stmt=$stmt"FROM SYSIBMADM.DB_HISTORY "
stmt=$stmt"WHERE OPERATION  = 'B' "
stmt=$stmt"AND OPERATIONTYPE IN ('F', 'N') "
stmt=$stmt"ORDER BY START_TIME "
stmt=$stmt") t1 "
stmt=$stmt"where rn > $keep  "

#db2c=$(db2 -x "$stmt")
#for files in $db2c
# do
#   echo "rm $files"
#   ok=$(rm "$files")
#done

anz=$(ls -l $todir/$db* | wc -l )

count="1"

if [[ $anz -ge $keep ]]
 then
  for i in $(find $todir/$db* | sort -r )
    do
     if [[ $count -gt $keep ]]; then
        echo $datum " $i wurde geloescht"
        rm $i
      fi
       (( count = count + 1 ))

     done
 fi

connect_reset
}

buildbackup() {  
    backup='backup database '$db

    if [[ $u == "" ]]
     then
     u=""
     else
     backup="$backup user $u using $p "
    fi

    if [[ $offline == "offline" ]]
     then
      online=''
    fi
   
    if [[ $online == "" ]]
     then
      offline='offline'
      else
      backup="$backup online "
    fi

    if [[ $usetsm == "USE TSM " ]]
     then
      backup="$backup usetsm "
      else
      backup="$backup to $to "
     fi

     if [[ $tablespace == "" ]]
     then
     tablespace=""
     else
     backup="$backup tablespace ($tablespace) "
    fi

     if [[ $incremental == "" ]]
     then
     incremental=""
     else
     backup="$backup incremental "
    fi
   
     if [[ $delta == "" ]]
     then
     delta=""
     else
     backup="$backup  delta "
    fi

     if [[ $buffers == "" ]]
     then
     buffers='4'
    fi
   
     if [[ $buffer == "" ]]
     then
     buffer='1024'
    fi

    backup="$backup with  $buffers buffers  buffer $buffer  "
   
    if [[ $parallelism == "" ]]
     then
     parallelism=""
     else
     backup="$backup parallelism  $parallelism "
    fi
   
    if [[ $nocompress == "" ]]
     then
     backup="$backup compress "
    fi
   
    if [[ $utilimpact == "" ]]
     then
     utilimpact=""
     else
     backup="$backup UTIL_IMPACT_PRIORITY  $utilimpact"
    fi

    if [[ $online == "online" ]]
     then
      if [[ $logs == "EXCLUDE" ]]
       then
         backup="$backup EXCLUDE LOGS "
      else
           backup="$backup INCLUDE LOGS "
      fi
    fi
   

}

prog=$0
echo 'prog='$prog

initvar

readcmd $*

if [[ $help == "help" ]]
 then
    sayhelp
    exit 0
 fi
if [[ $db == "" ]]
 then
    sayhelp
    exit 0
 fi

dbparms
 
buildbackup

echo $backup

connect_reset

if [[ $offline == "offline" ]]
 then
     dbf=$(db2 "force applications all")
     connect_db
     dbf=$(db2 "quiesce database immediate force connections")
     connect_reset
     dbf=$(db2 "deactivate database $db force")
     dbf=$(db2stop force )
     dbf=$(db2start )
 fi


back=$(db2 $backup)
echo $back

deloldcopy
 
prune_history

prune_logfiles

if [[ $offline == "offline" ]]
 then
     connect_db
     dbf=$(db2 "unquiesce database")
     connect_reset
     dbf=$(db2 "aktivate database $db ")
 fi

exit