Wednesday, December 28, 2011

Find out who loaded a DB2 table

In a development environment, multiple developers may work simultaneously and they may unload and load DB2 tables for testing purpose. Some times you may find that your data is missing and it is possible that somebody may have overlaid the data in that table. In that situation you may want to find out who are all loaded that table, so you can talk to them to find out a way to restore your data.  

The below query will help you to find who are all loaded the table.

SELECT                                                 
   DBNAME                                              
  ,TSNAME                                              
  ,TIMESTAMP                                           
  ,CASE ICTYPE                                          
    WHEN 'R' THEN CHAR('LOAD REPLACE LOG(YES)')        
    WHEN 'S' THEN CHAR('LOAD REPLACE LOG(NO)')         
    WHEN 'Y' THEN CHAR('LOAD RESUME LOG(NO)')          
    WHEN 'Z' THEN CHAR('LOAD RESUME LOG(YES)')         
   END AS ICTYPE                                       
  ,JOBNAME                                             
  ,AUTHID                                              
 FROM SYSIBM.SYSCOPY                                   
 WHERE DBNAME = 'MYTSTDBS' AND                  
       TSNAME IN(SELECT TSNAME                 
                  FROM SYSIBM.SYSTABLES        
                 WHERE DBNAME = 'MYTSTDBS' AND 
                       NAME   = 'MY_TEST_TABLE')    
   AND ICTYPE IN('R','S','Y','Z');                  

The output of the table SQL query will be as shown below.

+------------------------------------------------------------------------------------------------+
|  DBNAME  |  TSNAME  |         TIMESTAMP          |        ICTYPE         | JOBNAME  |  AUTHID  |
+------------------------------------------------------------------------------------------------+
| MYTSTDBS | MYTBLSPC | 2011-09-28-05.34.16.413254 | LOAD RESUME LOG(NO)   | LOADJOBA | TSXXXXD  |
+------------------------------------------------------------------------------------------------+

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.