DB2 Scripts
Aus wiki.drap.de
Datenbank Offline-Sicherung
#!/bin/sh
BackupDir=/db2/db2inst
DB=SAMPLE
db2 connect to $DB
db2 quiesce database immediate force connections
db2 connect reset
db2 -v backup database $DB to $BackupDir with 2 buffers buffer 1024 parallelism 1 compress without prompting
db2 connect to $DB
db2 unquiesce database
db2 connect reset
ls -lth ${DB}*NODE* | head -1
Datenbank Online-Sicherung
#!/bin/sh
BackupDir=/db2/db2inst
DB=SAMPLE
db2 archive log for database $DB
db2 -v backup database $DB online to $BackupDir with 2 buffers buffer 1024 parallelism 1 compress without prompting
ls -lth ${DB}*NODE* | head -1
Datenbank Rücksicherung
#!/bin/sh BackupDir=/db2/db2inst DB=SAMPLE TimeStamp=`ls -t $DB*NODE* | head -1 | cut -d. -f6` db2 connect to $DB db2 quiesce database immediate force connections db2 connect reset db2 -v restore database $DB from $BackupDir taken at $TimeStamp with 2 buffers buffer 1024 replace history file parallelism 1 without prompting db2 -v rollforward database $DB to end of logs and complete db2 connect to $DB db2 unquiesce database db2 connect reset
Logging userexit aktivieren
Aus der Vorlage: cp /opt/IBM/db2/V8.1/samples/c/db2uext2.cdisk db2uext2.c Pfade anpassen: ARCHIVE_PATH RETRIEVE_PATH AUDIT_ERROR_PATH Compelieren: cc -D_INCLUDE_POSIX_SOURCE -o db2uext2 db2uext2.c cp db2uext2 sqllib/bin db2 update db cfg for sample using logretain on db2 update db cfg for sample using userexit on Datenbank Offline Sicherung zum aktivieren s.o. db2 get db cfg | grep -i log
Public Grants, die beim Create Database angelegt werden, wieder entfernen
revoke connect on database from public ; revoke createtab on database from public ; revoke bindadd on database from public ; revoke implicit_schema on database from public ;
Grants für einen Datenbank User anlegen
Dieser soll alle Rechte auf Tabellenebene haben
revoke sysadm on database from db_usr ; revoke sysctrl on database from db_usr ; revoke sysmaint on database from db_usr ; revoke dbadm on database from db_usr ; grant createtab on database to db_usr ; revoke bindadd on database from db_usr ; grant connect on database to db_usr ; revoke create_not_fenc on database from db_usr ; grant implicit_schema on database to db_usr ; grant load on database to db_usr ; revoke quiesce_connect on database from db_usr ; revoke create_external_routine on database from db_usr ; revoke sysmon on database from db_usr ;
Grants für einen Tabellen User anlegen
Dieser soll alle Rechte auf der Taebellenebe haben
grant connect on database to co_usr ;
Script um die select,update,insert und delete Rechte auf allen Tabellen zu vergeben
#!/bin/bash
# drap MMVI
USERs=co_usr
DBs=sample
out=/tmp/table_grants.sql
tmp=/tmp/mk_table_grants.tmp
$out
for DB in $DBs
do
# Tabellen je Datenbank ermitteln
db2 connect to $DB
db2 select creator||'.'||name TABLENAME from sysibm.systables where type in ('T','V') and not creator like 'SYS%' | \
grep -v \- | grep . | grep -v selected\. | grep -v TABLENAME | sed s/ //g $tmp
# Grants je Datenbank erzeugen
echo ------------------------------------------------------------------------- $out
echo $out
echo connect to $DB ; $out
echo $out
for table in `cat $tmp`
do
table=\${table/\./\.\}\
for USER in $USERs
do
echo grant select on $table to $USER ; $out
echo grant update on $table to $USER ; $out
echo grant insert on $table to $USER ; $out
echo grant delete on $table to $USER ; $out
done
echo $out
done
echo $out
rm $tmp
done
# Grants ausfuehren
echo -------------------------------------------------------------------------
echo Grants werden ausgefuehrt:
db2 -tvf $out | grep -v ^grant | grep . | uniq
echo -------------------------------------------------------------------------
rm $out
Script um alle Grants zu entfernen
Dieses Script muss ggf. mehrfach ausgeführt werden. Hinter einem grant dbadm verbergen sich z.B. mehrere implizite Grants. Ein enfaches revoke dbadm lässt diese impliziten Grants stehen. Daher muss dann dieses Script einfach ein weiteres mal ausgeführt werden.
#!/bin/bash # drap MMVI DB=sample tmp=/tmp/tmp.sql echo ======================================================= echo CONNECT TO $DB ; $tmp db2look -d $DB -x 2/dev/null | grep GRANT | sed -e s/ GRANT/ REVOKE/ -e s/ TO / FROM / $tmp echo Vorher: cat $tmp echo ======================================================= db2 -tvf $tmp rm $tmp echo ======================================================= echo Jetzt: db2look -d $DB -x 2/dev/null | grep GRANT echo =======================================================
