db_backup_restore [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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 :**
<sxh bash>
db2look -d <DBNAME> -e -a -x -l -o ddl_<DBNAME>.sql
</sxh>

**2. Export the database :**
<sxh bash>
db2move <DBNAME> export
</sxh>

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

**4. Recreate database and import the data :**
<sxh bash>
db2 create db <DBNAME>
db2 -tvf ddl_<DBNAME>.sql
db2move <DBNAME> load
</sxh>


**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 :**
<sxh bash>
db2 "backup db <DBNAME> tablespace(<TBSP1>,<TBSP2>,<TBSPXX>) online to /dev/null (NUL on windows)"
</sxh>