DB2 Backup & Restore

Set backup with TSM : http://www.ibm.com/developerworks/servicemanagement/library/sm-tsm-db2/

Logical Backup

1. Create DDL for the whole database :

2. Export the database :

3. Transfer the files ddl_<DBNAME>.sql and all exported files to another instance/server

4. Recreate database and import the data :

5. Extract DDL For certain tables: <sxh bash> db2look -d dbname -f -a -e -m -t table1 table2 … -o tablename.ddl </sxh> 6. Verify integrity on tables with check constraint : <sxh bash> db2 -x “select 'SET INTEGRITY FOR '||rtrim(creator)||'.'||rtrim(name)|| ' IMMEDIATE CHECKED;' from sysibm.systables where status='C' and creator not like 'SYS%' and type='T'” </sxh> 7. Refresh MQTs : <sxh bash> db2 -x “select 'refresh table '||rtrim(creator)||'.'||rtrim(name)|| ';' from sysibm.systables where creator not like 'SYS%' and type='S'” </sxh> ==== EXPORT/LOAD ==== Compressed export/load : <sxh bash> For example, one can create a pipe and set up gzip to compress things piped to it: mkfifo /tmp/mypipe gzip -c < /tmp/mypipe > mytable.del.gz & In a separate process, independently, one could send the data to be compressed: db2 “export to /tmp/mypipe of del select * from myschema.mytable” The dump will be compressed by the background process you created and directed to the compressed file mytable.del.gz. Later the named pipe can be deleted just like any file: rm /tmp/mypipe The other way around is also possible, you can pipe the output of gzip into a named pipe like so: mkfifo /tmp/mypipe cat employee.del.gz | gzip -dc > /tmp/mypipe & Then load the uncompressed data into your DB2 table: db2 “load from /tmp/mypipe of del replace into myschema.mytable nonrecoverable” Remove the pipe after the load is completed: rm /tmp/mypipe </sxh> Create exception tables : <sxh bash> db2 -x “select 'create table datauser.' || tabname || '_BAD like datauser.' || Tabname || ';' from syscat.tables where status <> 'N' and type = 'T'” > bad_tables.sql </sxh> Set integrity with exceptions to exception table created previously : <sxh bash> db2 -x “select 'set integrity for datauser.' || tabname || ' immediate checked for exception in datauser.' || tabname || ' use datauser.' || tabname || '_BAD;' from syscat.tables where status <> 'N' and type = 'T'” > set_bad.sql </sxh> ==== Optim High Performance Unload ==== Unload table from backup image : <sxh bash> GLOBAL CONNECT TO <DB>; UNLOAD TABLESPACE FLUSH BUFFERPOOLS NO LOCK NO USING BACKUP DATABASE <DB> USE TSM TAKEN AT <TIMESTAMP>; SELECT * FROM DATAUSER.CATENTRY; OUTFILE(“unload_<table>.ixf”) FORMAT IXF; </sxh> ===== Physical Backup ===== Backup history (from history file) : <sxh bash> db2 “select distinct case DAYOFWEEK(TIMESTAMP(START_TIME)) when 1 then 'Sun' when 2 then 'Mon' when 3 then 'Tue' when 4 then 'Wed' when 5 then 'Thu' when 6 then 'Fri' when 7 then 'Sat' end as DAY, DATE(TIMESTAMP(START_TIME)) DATE, TIME(TIMESTAMP(START_TIME)) TIME, timestampdiff(4,CHAR(TIMESTAMP(END_TIME)-TIMESTAMP(START_TIME))) || ' min' DURATION, case OPERATIONTYPE when 'F' then 'Full offline' when 'I' then 'Incremental Offline' when 'O' then 'Incremental Online' when 'N' then 'Full online' when 'D' then 'Delta Offline' when 'E' then 'Delta Online' end as BACKUP, start_time timestamp, substr(LOCATION,1,10) LOCATION, SQLCODE from SYSIBMADM.DB_HISTORY where OPERATION = 'B' order by date(TIMESTAMP(START_TIME)) desc,time(timestamp(start_time)) desc fetch first 60 rows only with ur” </sxh> ==== Offline backup to TSM/disk :==== <sxh bash> db2 connect to <DBNAME> db2 quiesce database immediate force connections db2 unquiesce database db2 connect reset db2 terminate db2 deactivate database <DBNAME> db2 list applications db2 “backup db <DBNAME> [use tsm|to '</path/to/backup/directory>']” db2 activate database <DBNAME> db2 terminate </sxh> ====Online backup to TSM/disk :==== <sxh bash> db2 “backup db <DBNAME> online [use tsm|to '</path/to/backup/directory>']” db2 activate database <DBNAME> </sxh> ===== Restore DB2 database ===== http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/c0011981.html?cp=SSEPGG_9.7.0 ==== Partial redirected rebuild of database (selected tablespaces) :==== <sxh bash> db2 “restore database <DBNAME> rebuild with tablespace (syscatspace,<TBSP1>,<TBSP2>) from </path/to/backups/> (use TSM) taken at <BACKUPIMAGE> into <NEWDBNAME> newlogpath '/new/path/to/logs' redirect” db2 list tablespaces db2 “set tablespace containers for <TABLESPACEID> using (file /new/datapath/tbs2.lrg 2000)” db2 “set tablespace containers for <TABLESPACEID> using (file /new/datapath/tbs1.lrg 2000)” db2 restore db <NEWDBNAME> continue db2 rollforward db <NEWDBNAME> to end of backup and complete </sxh> ==== Restore of Log files from a backup ==== <sxh> db2 restore db pnrwh logs from . logtarget /opt/backup/newpnrwh DB20000I The RESTORE DATABASE command completed successfully. </sxh> ==== Speed up restore ==== <sxh bash> [db2inst1@appro01-rj backup]$ db2 restore database CMNDB WITH 10 BUFFERS BUFFER 20 ←– Pages SQL2539W The specified name of the backup image to restore is the same as the name of the target database. Restoring to an existing database that is the same as the backup image database will cause the current database to be overwritten by the backup version. Do you want to continue ? (y/n) y </sxh> ==== Partial redirected rebuild of database (selected tablespaces) with generated script ==== <sxh bash> db2 “restore database <DBNAME> rebuild with tablespace (syscatspace,<TBSP1>,<TBSP2>) from /path/to/backup taken at <BACKUPIMAGE> on '/new/dbpath' into <NEWDBNAME> newlogpath '/new/logpath' redirect generate script <scriptname>.clp” </sxh> ==== Cross-node TSM restore/recovery ==== 1. Restore the DB using previous authorization : <sxh bash> db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON </sxh> 2. Grant access to different node/user on source server : <sxh bash> db2adutl grant user <TARGETUSER> on nodename <TARGETNODENAME> for db <DBNAME> </sxh> 3. Query backup images on target server : <sxh bash> db2adutl query db <DBNAME> nodename <SOURCENODENAME> owner <SOURCEUSER> </sxh> 4. Restore the backup image from source server : <sxh bash> db2 “restore db <DBNAME> use tsm options '-fromnode=<SOURCENODE> -fromowner=<SOURCEUSER>' redirect generate script <SCRIPTNAME>” </sxh> ==== Tablespace backup/restore ==== !! DB MUST BE IN LOG ARCHIVING MODE !!! 1. Take offline/online tablespace level backup : <sxh bash> db2 “backup db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) [offline|online] use tsm” </sxh> 2. Restore tablespace from backup (offline/online) : <sxh bash> db2 “restore db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) [offline|online] use tsm taken at <TIMESTAMP>” </sxh> 3. Rollforwad restored tablespaces : <sxh bash> db2 “rollforward db dbdev97 to end of backup and complete tablespace(tsdata01,tsindx01)” </sxh> 4. Take restored tablespace from backup_pending state :**