IBM Software Group

Backup & Recovery

®

IBM Software Group | DB2 Information Management Software

Agenda

 Backup Types  Backup Process Model  Backup Performance Tunning  Restore Types  Restore Process Model  Restore Performance Tunning  RollForward Performance Tunning

IBM Software Group | DB2 Information Management Software

Backup

Backup Types  Online or Offline  Database Level backup or Tablespace Level backup  Full, Incremental or Delta

IBM Software Group | DB2 Information Management Software

Offline Backup

 Database needs to be offline  Internally establishes connection to the specified database  Faster than online  Default option

IBM Software Group | DB2 Information Management Software

Online Backup

 Database is accessible  Needs archival logging enabled  Slower than offline backup  Log files are included by default  Incremental and delta

IBM Software Group | DB2 Information Management Software

Incremental Backup

 Copy of all data that has been updated since the last full successful backup  Cumulative backup  Need to maintain only the last incremental backup and last full backup  Database parameter TRACKMOD should be enabled  Incremental backup is not permitted until a full backup is taken to set a base from which it can recover

IBM Software Group | DB2 Information Management Software

Incremental Delta Backup

 Copy of all data that has been updated since the last any successful backup (full, incremental or delta)  Non cumulative  Need to maintain all backup images since the last full backup

IBM Software Group | DB2 Information Management Software

Incremental Backup

 Granularity of tracking is at Tablespace level  Entire Large Objects are backed up if any other object in that Tablespace is modified  Normal data is backed up only if it has changed  In addition to the changed data incremental backup also includes the database's metadata  Not enabled by default due to minimal performance impact while tracking changes

IBM Software Group | DB2 Information Management Software

Backup Process Model

 db2bm Buffer Manipulator Reads data from the disk and writes into shared memory buffer  db2med Media Controller Reads from shared memory buffer and writes the pages out to the target devices  db2agent Controls the flow between db2bm and db2med

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2bm  Controlled by PARALLELISM option  Reads data from disk into backup buffer  Sends full buffer message on the full queue for db2med

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2bm  Function stack trace while waiting for the next available buffer sqluReadAdrFromQueue sqlubGetNextBuffer  Function stack trace while reading pread64 sqloReadBlocks  Function stack trace after completing reading data sqluReadMessageFromQueue sqlubbuf

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2med  Controlled by number of output destinations or Open Sessions clause when using TSM  Reads data from full buffers and writes to the media  Sends empty buffer message on the empty queue for db2bm

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2med  Function stack trace while waiting for full buffer notification sqluReadAdrFromQueue sqluMCContinueBackup  Function stack trace while writing write sqlowrite

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2agent  Responsible for setting up the message queues and spawn db2med and db2bm  Once backup starts db2agent process will wait for the backup complete notification  Function stack while waiting for notification sqluReadMessageFromQueue SqluxReadAgentQueue sqlubcka

IBM Software Group | DB2 Information Management Software

Backup Process Model

Message queues.

 Communication between db2agent, db2bm and db2med is managed by message queues  Following message queues created 1 message queue for the db2agent 1 message queue for full buffers 1 message queue for empty buffers 1 message queue for each db2bm edu 1 message queue for each db2med edu

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2med with TSM  db2vend process used  db2med invokes the TSM code inside db2vend

IBM Software Group | DB2 Information Management Software

Backup Process Model

db2vend  Independent process to execute the vendor API code  Similar to db2fmp  Runs outside DB2 address space Prevents unexpected API errors from crashing DB2

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

Backup command options  WITH num-buffers BUFFERS  PARALLELISM n  BUFFER buffer-size

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

Backup command options  WITH num-buffers BUFFERS Use at least twice as many buffers as backup sessions which will not cause the target devices to wait for data  PARALLELISM n Number of processes that are started to read data from the database Set n equal to the number of tablespaces being backed up  BUFFER buffer-size Buffer-size is a multiple of the table space extent size plus one page

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

 If no options specified optimal values for number of buffers buffer size and parallelism are selected automatically  db2diag.log entry shows the values that are selected

FUNCTION: DB2 UDB, database utilities, sqluxGetDegreeParallelism, probe:537 DATA #1 : Autonomic BAR - using parallelism = 10.

FUNCTION: DB2 UDB, database utilities, sqlubTuneBuffers, probe:898 DATA #1 : Autonomic backup - tuning enabled.

Using buffer size = 4481, number = 10.

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

UTIL_HEAP_SZ  Uitlity Heap Size  Maximum memory that can be used simultaenously by Backup, Restore and LOAD  Used for backup buffers  More than buffer-size * number of buffers

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

Fragmentation  db2bm reads contiguous blocks of data  Fragmentation due free extents can affect performance  REORG should be done to reduce fragmentation

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

Prefetching  I/O performed by db2bm if tablespace has only one container  I/O performed by Prefetchers (db2pfchr) for tablespace with multiple containers  Parallel I/O improves performance

IBM Software Group | DB2 Information Management Software

Backup Performance Tuning

Contention during online backup  db2bm must guarantee that the block of data being backed up does not change during the read operation  db2bm will wait on other shared users to complete their I/O operations to get exclusive access db2n

IBM Software Group | DB2 Information Management Software

Backup Process Model

IBM Software Group | DB2 Information Management Software

Restore

Restore Types  Full database restore  Tablespace restore  Redirect restore  Incremental Restore

IBM Software Group | DB2 Information Management Software

Redirect Restore

 Used to restore database on a different machine  Restores tablespaces to a different location  Add, Change or remove containers of a DMS tablespace  Modify containers of a SMS tablespace

IBM Software Group | DB2 Information Management Software

Redirect Restore

 Issue RESTORE command with REDIRECT option

db2 restore db sample redirect

 Use SET TABLESPACE CONTAINERS command for each tablespace to specify the new container location

db2 set tablespace containers for 2 using (path '')

 Issue RESTORE command with CONTINUE option

db2 restore db sample continue

IBM Software Group | DB2 Information Management Software

Redirect Restore Automatic Storage

 Use ON option to set the path for automatic storage tablespaces

db2 restore db sample on redirect

 If no normal tablespaces then CONTINUE command

db2 restore db sample continue

IBM Software Group | DB2 Information Management Software

Incremental Restore

 Identify and restore the last incremental backup image This restore only restores the metadata and not the actual data  Identify and restore the most recent full backup image  Restore the last incremental backup image again This time the actual data will be restored

IBM Software Group | DB2 Information Management Software

Incremental Restore

 Use db2ckrst utility to get the correct restore sequence db2ckrst -d -t  Timestamp should be that of the last incremental backup you want to restore  Reads the database history file to give the correct restore sequence

IBM Software Group | DB2 Information Management Software

Redirect Incremental Restore

 Restore Incremental backup image first

db2 restore db sample incremental taken at redirect

 SET TABLESPACE CONTAINERS command

db2 set tablespace containers for 5 using (file '')

 CONTINUE command

db2 restore db sample continue

 Restore the full backup and incremental after this

db2 restore db mydb incremental taken at db2 restore db mydb incremental taken at

IBM Software Group | DB2 Information Management Software

Restore Process Model

 db2bm Buffer Manipulator Reads data from buffer and writes to the disk  db2med Media Controller reads from the backup image and writes to the buffer  db2agent Controls the flow between db2bm and db2med

IBM Software Group | DB2 Information Management Software

Restore Performance Tunning

 Increase the restore buffer size Multiple of the backup buffer size specified during the backup operation  Increase the number of buffers  Increase the value of the PARALLELISM parameter More number of db2bm will be used for write operation  Increase the utility heap size  Tablespaces with multiple containers will benefit from parallel I/O Multiple page cleaners (db2pclnr) will write data parallely

IBM Software Group | DB2 Information Management Software

Restore Performance Tunning

 Increase the restore buffer size Multiple of the backup buffer size specified during the backup operation  Increase the number of buffers  Value of the PARALLELISM parameter equal to number of tablespaces One db2bm for each tablespace will be used for write operation  Increase the utility heap size  Tablespaces with multiple containers will benefit from parallel I/O Multiple page cleaners (db2pclnr) will write data parallely

IBM Software Group | DB2 Information Management Software

Restore Performance Tuning

 If no options specified optimal values for number of buffers buffer size and parallelism are selected automatically  db2diag.log entry shows the values

FUNCTION: DB2 UDB, database utilities, sqluxGetDegreeParallelism, probe:537 DATA #1 : Autonomic BAR - using parallelism = 14.

FUNCTION: DB2 UDB, database utilities, sqludTuneBuffers, probe:898 DATA #1 : Autonomic restore - tuning enabled.

Using buffer size = 3841, number = 14.

IBM Software Group | DB2 Information Management Software

Restore Process Model

IBM Software Group | DB2 Information Management Software

RF Performance Tunning

 PREC_NUM_AGENTS Recovery agents (db2redow) PREC_NUM_AGENTS=number of online CPUs+1  PREC_NUM_QSETSIZE Message Queue size Increase it if you reduce the PREC_NUM_QSETS  PREC_NUM_QSETS Number of message queues Decrease the size if PREC_NUM_QSETS is a high value

IBM Software Group | DB2 Information Management Software

RF Performance Tunning

 Setting the parameters Create DB2BPVARS.cfg file with the following details PREC_NUM_AGENTS= PREC_NUM_QSETS= PREC_NUM_QSETSIZE= db2set DB2BPVARS= Recycle the instance

IBM Software Group | DB2 Information Management Software

RF Performance Tunning

 db2diag.log entry

FUNCTION: DB2 UDB, recovery manager, sqlpAllocatePRCB, probe:2000 DATA #1 : Using parallel recovery with 9 agents 16 QSets 48 queues and 0 chunks

PREC_NUM_AGENTS=9 PREC_NUM_QSETS=16 PREC_NUM_QSETSIZE=4

IBM Software Group | DB2 Information Management Software

Thank You