Stuck with your redirected restore? These steps can prove a good road map for your data refresh activity using db2 redirected restore.

Please do not follow the below steps blindly, first read them, understand and map it to your environment, and use it as a guidance.

If you are using some 3rd party tool for example HIBACK, ask the backup team to restore full backup , and it's associated logs(plus-minus 2 extra logs), to a location which is accessible to both source and target servers.

Once the backup dump is restored, issue db2ckbkp on the backup file, to get the backup name as per DB2 requirement

Step 1: At source server, identify the database name and it's full backup file name and timestamp. To see the backup details, below command can be used,
       
        db2 list history backup all for db dbname.

        From the output of this command, make a note of (i) Timestamp of backup to be used;(ii) Type of backup-Offline/Online; (iii) path of backup file

        List the space used by all tablespaces
         db2pd -db dbname -tablespaces
       
        (sum of TotalPgs for all tablespaces * pagesize(4/8/16/32) Kb)

        Also capture backup of db cfg for source database
        db2 get db cfg for source dbname>dbcfg_backup_source.out

        Get buffer pool details at prod using command db2pd -db dbname -bufferpools
       

Step 2: Run below command to generate restore script at source

        db2 restore db source_dbname from source_dbname_backup_path taken at source_dbname_backup_timestamp redirect generate script redirect_restore_script.sql

        Now copy the restore script(redirect_restore_script.sql and backup file identified in Step 1 to some staging location for example /tmp.
        After copying to staging location,run below command to change file permisions,

         chmod 777 /tmp/backup-file-name

         chmod 777 /tmp/redirect_restore_script.sql

Step 3: Now copy these two files to target server. Below scp commands can be used to trasnfer files from source server to target server
       
        scp file-to-be-transfered your_username@remotehost.edu:/some/remote/directory


Step 4: At target server, perform following tasks,

        a) generate restore script of database at target server, which is to be restored.
           db2 restore db target_dbname from backup_path taken at backup_timestamp redirect generate script -o redirect_restore_script_target.sql
       
        b) Run db2look to capture grants and federation objects if any
           db2look -d target_dbname -a -x > target_dbname_grants.sql
           db2look -d target_dbname -a -e -fedonly > target_dbname_fed_objects.sql

           db2 get db cfg for target_dbname>dbcfg_backup_target.out
       
        c) Compare the restore script generated from source and target databases. Then modify the one genertated at source, by using the parameters from target
            sdiff source_restore_script target_restore_sript>diff_restore_script.out

        d) Run db2set -all|grep -e 'DB2_RESTORE_GRANT_ADMIN_AUTHORITIES',  on target server to check the status of registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES.
         
           If this registry variable is set to NO, the instance id(different from source instance) at target server will not be able to perform restore.
           In such a case, update this parameter to YES(db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES), and stop-start db2 instance.

       e) Get buffer pool details at prod using command db2pd -db dbname -bufferpools


Step 5: Make sure there is enough space(as identified in Step 1) on target server. Also recheck the NEWLOGPATH being used;If it has any old log files, remove them.
       
        List all the applications running on target database to be restored. Then force them, after confirming with application team using below command,

        db2 "force application (APPL_HDL)"

        After all the applications have been forced, move to next step

Step 6:
     (a) If restore is to be done using offline backup, use below command,

         db2 restore db dbname from path-of-backup on dbpath without rolling forward


      (b) For online backups, restore statement should look like,

          RESTORE DATABASE db-name FROM backup-location TAKEN AT backup-timestamp on path-for-db2-data LOGTARGET 'path for extracting logs from online backup'
          (make sure that log target path does not have existing logs)

          Once the restore is complete, we need to do roll forward recovery using below command(can be appended to the restore script),
         
 db2 "rollforward db dbname to end of backup and complete overflow log path('path for extracting logs from online backup')"        
         
         

Step 7: Once all the restore(and rollforward statements are ready), execute them all in same session. This can be done by putting all the statements in a script,
        and executing the script with nohup option eg. nohup db2 -tvf restore_script.sql

 

Step 8: Once the restore script is submitted, monitor the progress of restore using db2pd -target-dbname -util, or db2 list utilities

Step 9: Compare the db cfg, and update the restored db database cfg parameters if required
       
        Execute grants ddls.

        Create the federation objects(do remember to provide passwords)
       
        alter buffer pool size as per test environment available memory.

Step 10: make a test connection to restored database, and query any table for verification purpose.

Step 11: Send communication.

Notes:

If for some reason , restore utility is to be cancelled, use command db2 restore db yourdbname abort

redirected restore using offline backup, and related access problem on target instance

I have two instances-db2inst1 and db2inst2

In db2inst1, I created a db SAMPLE, and took it’s offline backup in a shared director /backups

Now I login in to other instance on another server, and issue below restore command,

db2inst2@db2v10:~> db2 restore db sample from /backups/ on /home/db2inst2/ without rolling forward

SQL2539W  Warning!  Restoring to an existing database that is the same as the

backup image database.  The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

Now I will try to access a table TESTTB,

db2inst2@db2v10:~> db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0

 SQL authorization ID   = DB2INST2

 Local database alias   = SAMPLE

db2inst2@db2v10:~> db2 "select count(*) from DB2INST1.TESTTB"

SQL0551N  "DB2INST2" does not have the required authorization or privilege to

perform operation "SELECT" on object "DB2INST1.TESTTB".  SQLSTATE=42501

If we grant db2inst2 select privilege at db2inst1, it will not get reflected on other server where db2inst2 is located. If we try to issue grant on the server where db2inst2 is located, we will get error,

db2inst2@db2v10:~> db2 "grant select on DB2INST1.TESTTB to user db2inst2"

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0551N  "DB2INST2" does not have the required authorization or privilege to

perform operation "GRANT" on object "DB2INST1.TESTTB".  SQLSTATE=42501

to get the grant from user db2inst1 on it’s objects, user db2inst1 should be created at server where db2inst2 is located. After that grants can be issued.

Another alternative is to run db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON, and then recyle db2 server. Followed by restore.  Observe below steps,

db2inst2@db2v10:~> db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON

db2inst2@db2v10:~> db2 force application all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

db2inst2@db2v10:~> db2 terminate

DB20000I  The TERMINATE command completed successfully.

db2inst2@db2v10:~> db2stop

SQL1064N  DB2STOP processing was successful.

db2inst2@db2v10:~> db2start

SQL1063N  DB2START processing was successful.

db2inst2@db2v10:~> db2 restore db sample from /backups/ on /home/db2inst2/ without rolling forward

SQL2539W  Warning!  Restoring to an existing database that is the same as the

backup image database.  The database files will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

db2inst2@db2v10:~> db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.0

 SQL authorization ID   = DB2INST2

 Local database alias   = SAMPLE

db2inst2@db2v10:~> db2 "select count(*) from DB2INST1.TESTTB"

1

-----------

     336336

  1 record(s) selected.

SQL1040N The maximum number of applications is already connected to the

If Maximum number of connections is reached, we get below messages,

db2inst2@db2v10:~> db2 connect to testdb

SQL1040N  The maximum number of applications is already connected to the

database.  SQLSTATE=57030

In db2diag, we will get below message,

2014-04-17-13.41.49.929364+330 I2038E599             LEVEL: Severe

PID     : 8786                 TID : 140315335845632 PROC : db2sysc

INSTANCE: db2inst2             NODE : 000            DB   : TESTDB

APPHDL  : 0-156                APPID: *LOCAL.db2inst2.140417081149

AUTHID  : DB2INST2             HOSTNAME: db2v10

EDUID   : 59                   EDUNAME: db2agent (TESTDB)

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::SubsequentConnect, probe:10117

RETCODE : ZRC=0xFFFFFBF0=-1040

          SQL1040N  The maximum number of applications is already connected to

          the database.

Now to check the maximum number of threads allowed, and high water mark for number of threads in an instance, use below commands,

db2 connect to dbname

db2 get db cfg | grep -i MAXAPPLS ==>at db level

db2 get dbm cfg | grep -i max ==> at instance level

db2 get snapshot for dbm | grep -i high ==> give hig water mark for db conenctions

Do you want to reduce your column length, while keeping table available in DB2 LUW?

Let me discuss a situation, you have a table in which there is a column defined as varchar(1000). This table has data too. Now due to some business requirement, you want to reduce the column length to varchar(100). You are fine with data truncation for this column, but are concerned with  table availability. 

In this situation, you can follow below steps,

1.Create a table with a different name(say table2), but same structure and indexes(with different names)

2.export data like this

   db2 "export to myfile.ixf of ixf messages msgs.txt select col1,col2,,substr(varchar,1,256),..,coln from schema.table"

3.load into new table created in step 1, like

  load from myfile.ixf of ixf insert into table2

4.After successful load of all rows, rename original table to some other name, and table2 to original table name.

The rename statements will look like,

db2 “rename table table1 to table2”

db2 “rename index index1 to index2”

And for creating staging table, we can use db2look like db2look -d sample -e -x -t db2inst1.testvar  -o tableddl.sql

With these steps original table will remain available for most of the time.

SQL to list useful information from from the database history file using SYSPROC.ADMIN_LIST_HIST table function

"SELECT 
  SUBSTR(FIRSTLOG,1,20) AS FIRSTLOG
 ,SUBSTR(LASTLOG,1,20) AS LASTLOG
 ,SUBSTR(TABSCHEMA,1,8) as TABSCHEMA
 ,SUBSTR(TABNAME,1,15) as TABNAME
 ,CASE OPERATION
                WHEN 'A' THEN 'Create table space'
                WHEN 'B' THEN 'Backup'
                WHEN 'C' THEN 'Load copy'
                WHEN 'D' THEN 'Dropped table'
                WHEN 'F' THEN 'Roll forward'
                WHEN 'G' THEN 'Reorganize table'
                WHEN 'L' THEN 'Load'
                WHEN 'N' THEN 'Rename table space'
                WHEN 'O' THEN 'Drop table space'
                WHEN 'Q' THEN 'Quiesce'
                WHEN 'R' THEN 'Restore'
                WHEN 'T' THEN 'Alter table space'
                WHEN 'U' THEN 'Unload'
                WHEN 'X' THEN 'Archive log'
   END AS OPERATION
  ,CASE 
                WHEN OPERATION ='X' AND OPERATIONTYPE = 'P' THEN 'Primary log path'
                WHEN OPERATION ='X' AND OPERATIONTYPE = 'M' THEN 'Secondary (mirror) log path'
                WHEN OPERATION ='X' AND OPERATIONTYPE = 'F' THEN 'Failover archive path'
                WHEN OPERATION ='X' AND OPERATIONTYPE = '1' THEN 'Primary log archive method'
                WHEN OPERATION ='X' AND OPERATIONTYPE = '2' THEN 'Secondary log archive method'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'F' THEN 'Offline'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'N' THEN 'Online'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'I' THEN 'Incremental offline'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'O' THEN 'Incremental online'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'D' THEN 'Delta offline'
                WHEN OPERATION ='B' AND OPERATIONTYPE = 'E' THEN 'Delta online'
                WHEN OPERATION ='F' AND OPERATIONTYPE = 'E' THEN 'End of logs'
                WHEN OPERATION ='F' AND OPERATIONTYPE = 'P' THEN 'Point in time'
                WHEN OPERATION ='L' AND OPERATIONTYPE = 'I' THEN 'Insert'
                WHEN OPERATION ='L' AND OPERATIONTYPE = 'R' THEN 'Replace'
                WHEN OPERATION ='T' AND OPERATIONTYPE = 'C' THEN 'Add containers'
                WHEN OPERATION ='T' AND OPERATIONTYPE = 'R' THEN 'Rebalance'
                WHEN OPERATION ='Q' AND OPERATIONTYPE = 'S' THEN 'Quiesce share'
                WHEN OPERATION ='Q' AND OPERATIONTYPE = 'U' THEN 'Quiesce update'
                WHEN OPERATION ='Q' AND OPERATIONTYPE = 'X' THEN 'Quiesce exclusive'
                WHEN OPERATION ='Q' AND OPERATIONTYPE = 'Z' THEN 'Quiesce reset'
   END AS OPERATIONTYPE
 ,START_TIME
 ,END_TIME
 ,SQLCODE
FROM TABLE(SYSPROC.ADMIN_LIST_HIST()) AS LISTHISTORY
"

list of useful db2pd commands

DB2PD is a diagnostic tool avaialble in DB2 forLUW.It is used for monitoring and troubleshooting. It gets the information from DB2 memory.
To quote from IBM manual,
"
The tool collects information without acquiring any latches or using any engine
resources. It is therefore possible (and expected) to retrieve information that is
changing while db2pd is collecting information; hence the data might not be
completely accurate. If changing memory pointers are encountered, a signal
handler is used to prevent db2pd from ending abnormally. This can result in
messages such as "Changing data structure forced command termination" to
appear in the output. Nonetheless, the tool can be helpful for troubleshooting. Two
benefits to collecting information without latching include faster retrieval and no
competition for engine resources.
"
   db2pd –osinfo => To display the operating system information
   
   db2pd -inst => To display all instance-related information

   db2pd -db dbname => To display all database-related information to the database dbname

   db2pd -db databasename -locks -transactions -applications -dynamic =>Diagnosing a lockwait

   db2pd -wlocks -db dbname => capture all the locks being waited on

   db2pd -apinfo AppHandl -db dbname => capture detailed runtime information about the lock owner and the lock waiter

   db2pd -db sample -locks => Using the callout scripts when considering a locking problem

   db2pd -applications -dynamic =>reports the current and last anchor ID and statement unique ID for dynamic SQL statements. This allows direct mapping from an application to a dynamic SQL                                    statement.

   db2pd -tcbstats => you can identify the number of inserts for a table.

   db2pd -tablespaces => gives number of inserts at tablespace level

   db2pd -db sample -dyn => you can identify the dynamic SQL statement that is using the table

   db2pd -recovery -db sample (or alldbs)=> the output shows several counters that you can use to verify that recovery is progressing
   
   db2pd -transactions -db dbname => the output shows the number of locks, the first log sequence number (LSN), the last LSN, the first LSO, the last LSO, the log space used, and the space reserved
   
   db2pd -logs -db dbname => for monitoring log usage for a database. By using thePages Written value, as shown in the following sample output, you can determine whether the log usage is increasing

                             You can identify two types of problems by using this output:
                             a)If the most recent log archive fails, Archive Status is set to a value of Failure. If there is an ongoing archive failure, preventing logs from being archived at all,
                               Archive Status is set to a value of First Failure.
                             b)If log archiving is proceeding very slowly, the Next Log to Archive value is lower than the Current Log Number value. If archiving is very slow, space for
                               active logs might run out, which in turn might prevent any data changes from occurring in the database.

   db2pd -sysplex => Viewing the sysplex list
   
   db2pd -stack all => to produce stack traces for all processes in the current database partition. You might want to use this command iteratively when you suspect that a process or thread is                        looping or hanging.

   db2pd -stack eduid => obtain the current call stack for a particular engine dispatchable unit (EDU)   

   db2pd -dbptnmem => shows how much memory the DB2 server is currently consuming and, at a high level, which areas of the server are using that memory.

   db2pd -reorgs index -db dbname => reports index reorg progress for partitioned indexes (Fix Pack 1 introduced support for only non-partitioned indexes).   

   db2pd -edus interval=5 => the deltas for processor user time and processor system time are given across a five-second interval

   db2pd -edus interval=5 top=5 stacks

   db2pd -agents event => If you need to determine whether an agent changed state during a specific period of time, use the event option together with the -agents parameter. The
                          AGENT_STATE_LAST_UPDATE_TIME(Tick Value) column that is returned shows the last time that the event being processed by the agent was changed

   db2pd -tablespaces -db sample  => list tablespaces details

   db2pd -db dbname -tcbstats tmp_space_id to monitor temp space


   db2pdcfg -catch => to capture information about the database management system when a specific SQLCODE, ZRC code or ECF code occurs
                      When the errors are caught, the db2cos (callout script) is launched. The db2cos script can be dynamically altered to run any db2pd
                      command, operating system command, or any other command needed to resolve the problems. The template db2cos script file is located in sqllib/bin on UNIX
                      and Linux. On the Windows operating system, db2cos is located in the $DB2PATH\bin directory.

   db2pd -addnode => When adding a new node, you can monitor the progress of the operation on the database partition server, that is adding the node.Use optional oldviewapps and detail parameters                      for more detailed information.


   db2pd -gfw  => If you require a list of event monitors that are currently active or have been, for some reason, deactivated, run the db2pd -gfw command. This command also
                  returns statistics and information about the targets, into which event monitors write data, for each fast writer EDU.