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.