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
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
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 ")
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
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