DB2 Backup & Restore

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

1. Create DDL for the whole database :

db2look -d <DBNAME> -e -a -x -l -o ddl_<DBNAME>.sql

2. Export the database :

db2move <DBNAME> export

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

4. Recreate database and import the data :

db2 create db <DBNAME>
db2 -tvf ddl_<DBNAME>.sql
db2move <DBNAME> load

5. Extract DDL For certain tables:

db2look -d dbname -f -a -e -m -t table1 table2 ... -o tablename.ddl

6. Verify integrity on tables with check constraint :

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

db2 -x "select 'refresh table '||rtrim(creator)||'.'||rtrim(name)|| ';' from sysibm.systables 
where creator not like 'SYS%' and type='S'"

Compressed export/load :

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

Create exception tables :

db2 -x "select 'create table datauser.' || tabname ||  '_BAD like datauser.' || Tabname || ';' from syscat.tables where status <> 'N' and type = 'T'" > bad_tables.sql

Set integrity with exceptions to exception table created previously :

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

Unload table from backup image :

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;

Backup history (from history file) :

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"

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

db2 "backup db <DBNAME> online [use tsm|to '</path/to/backup/directory>']"
db2 activate database <DBNAME>

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

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

db2 restore db pnrwh logs from . logtarget /opt/backup/newpnrwh
DB20000I  The RESTORE DATABASE command completed successfully.

[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

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"

1. Restore the DB using previous authorization :

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON

2. Grant access to different node/user on source server :

db2adutl grant user <TARGETUSER> on nodename <TARGETNODENAME> for db <DBNAME>

3. Query backup images on target server :

db2adutl query db <DBNAME> nodename <SOURCENODENAME> owner <SOURCEUSER>

4. Restore the backup image from source server :

db2 "restore db <DBNAME> use tsm options '-fromnode=<SOURCENODE> -fromowner=<SOURCEUSER>' redirect generate script <SCRIPTNAME>"

!! DB MUST BE IN LOG ARCHIVING MODE !!!

1. Take offline/online tablespace level backup :

db2 "backup db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) [offline|online] use tsm"

2. Restore tablespace from backup (offline/online) :

db2 "restore db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) [offline|online] use tsm taken at <TIMESTAMP>"

3. Rollforwad restored tablespaces :

db2 "rollforward db dbdev97 to end of backup and complete tablespace(tsdata01,tsindx01)"

4. Take restored tablespace from backup_pending state :

db2 "backup db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) online to /dev/null (NUL on windows)"