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'"7. Refresh MQTs :
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>
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)"