Cross-node recovery - TSM

To recover/access the backup image of the database that was backed up from a different server. Typically to access production database from dev/qa

Comment out "PASSWORDACCESS = generate" in file "/usr/tivoli/tsm/client/ba/bin/dsm.sys" by adding a '*' in the beginning of the line.

Now we should be able to query any backup image, provided you have the required details.

db2adutl query db <dbname> nodename <nodename> owner <owner> password <passwd>


Update the following db parameters in the target db with source db values from dsm.sys to restore the db backed-up from a different server

• TSM_NODENAME
  
• TSM_OWNER
  
• TSM_PASSWORD
  

Now restore the database with 'use adsm' on the target server.

NOTE:

• Revert back the dsm.sys & db params after the refresh. Else local backups to TSM will fail.

Other References: IBM Documentation

Fly back in time and recover backups! :-)

Parallel export for partitioned tables

db2_all "<<+$nodenum<db2 connect to $dbname; db2 export to $filename of del select * from $tablename where nodenumber($partitioningkey)=$nodenumber"

Example:

db2_all "<<+11<db2 connect to testdb; db2 export to /backup/test_table.del.11 of del select * from test_table where nodenumber(ID) = 11; db2 terminate"

NOTE:

• Useful for large tables.
• Be sure to export all the partitions in which the table is defined.
• Export each partition to seperate filesystem for better performance.
  

Cataloging AS/400 database on DB2 Connect

Command sequence to catalog AS/400 database in DB2 Connect:

• db2 catalog tcpip node <node_name> remote <host_name> server <port_no>
  
• db2 catalog dcs database <local_name> as <real_db_name>
• db2 catalog database <local_name> as <alias> at node <node_name> authentication dcs
  

Command sequence to catalog the as/400 database that was already cataloged in DB2 Connect.

• db2 catalog tcpip node <node_name> remote <host_name_or_address> server <port_no>*
• db2 catalog database <local_name> as <alias> at node <node_name>
  

*Use the portnumber of DB2 connect not AS/400

SQL0031C File sqllib/bnd/db2clish.bnd could not be opened and SQL0805N

Received the above while accessing DB2 database from MS Access. db2clish.bnd was not found in the local machine.

Binding the files didn't help

o "DB2 bind @db2ubind.lst blocking all grant public" for the
DB2 utilities.

o "DB2 bind @db2cli.lst blocking all grant public" for CLI.

Re-installing the DB2 client fixed the problem.

DB2 Stats!

Instance Uptime

db2 get snapshot for dbm | grep -i start

Last Backup Timestamp

db2 get snapshot for db on dbname | grep backup

Database Activation timestamp

db2 get snapshot for db on dbname | grep First

SPM0438 The Sync point manager recovery log is bad

You might also see the following message when starting DB2

SQL5043N Support for one or more communications protocols failed to start successfully. However, core database manager functionality started successfully.

or
with the following message and db2 will not start.

SQL5042N One of the communication protocol server support processes failed to start up.

Solution:

• Stop the instance
• Delete/move all the spm logs located in sqllib/spmlog/SPMLOGSD
• Also del/move SPMLOG.LCF located in sqllib/spmlog
• start the instance. Should starup without any error messages.
  

db2licm

SQL8000N DB2START processing failed.

Cause: License Expired or License key have not yet been added.

If you get this after installing You might have to add the license key

As root

/usr/opt/db2_08_01/adm/db2licm -a /ese.sbcs/db2/license/db2ese.lic

License added successfully!!

SQL8017W The number of processors on this machine exceeds the defined
entitlement of...

db2licm -n "Product identifier" "Number of processors entitled to this product"

db2licm -n DB2ESE 2

Updates the number of processors to 2 for the product DB2ESE.

db2licm -l gives more information about the installed products with license info.

SQL0443N Routine "*RCH_1K16" (specific name "") has returned an error SQLSTATE with diagnostic text "CTE0101 A search engine operation failed

Received this error while trying to do db2text (NetSearchExtender) operation on a database that was restored from a different machine.

Noticed the text index was not there in the restored databases. Recreating & Updating the Text index helped.

SQL10004C An I/O error occurred while accessing the database directory

If there were no changes made to the Hardware. Then its most likely that the database directory file sqldbdir is corrupted.

You will not be able to create new databases, connect to or drop existing databases.

Solution:

• Move all the files found in “sqllib/sqldbdir” to a different location.
• Now catalog all the databases. For Local databases use: db2 catalog db “dbname”.
• This should get ride of the error.

Causes:

I received this error after *successful* installation of DB2 8.1 fixpak 7a on DB2 8.1 Fixpak 2 OS AIX 5.3.

Include logs in online backup (As of DB2 8.2)

db2 BACKUP DB “dbname” ONLINE TO “backupdir” INCLUDE LOGS

Above command includes the logs required to restore the database and bring back the database to a consistent point.

And to restore

db2 RESTORE DB “dbname” FROM “backupdir” LOGTARGET “logdir”

ROLLFORWARD DB “dbname” TO END OF LOGS AND STOP OVERFLOW LOG PATH “logdir”

To restore just the logs from the backup image

db2 restore db “dbname” logs from “backupdir” logtarget ‘logdir”