2016-04 DB2 SYSCOPY – Do you know what is in it? Redux

DB2 10, DB2 11 Migration & DB2 Database maintenance

A special query to check and clear out the DB2 Catalog

 

This is a redux of my original Newsletter from February 2013 with additional data at the very end – The reason for this relaunch? Simple – I have had numerous emails about this topic, especially with regard to migrating from DB2 10 to DB2 11, and it is obviously still very important to check and to clear out *before* you start your DB2 migration. So now enjoy the redux version…

 

DB2 Database Maintenance and the DB2 Catalog

If you have written your own DB2 database maintenance programs then you almost certainly run SQL queries against the DB2 Catalog. If you are also checking for Incremental Image Copies (IIC) or Full Image Copies (FIC) then you will probably be using a mix of Real-Time Statistics tables (RTS) and the SYSIBM.SYSCOPY to figure out which type of utility to generate. Further if you are in DB2 10 (any mode! CM8, CM9, or NF) then this newsletter is for you!

 

I had a problem in one of our test centers with a cursor that I noticed was taking a long time to finish and so I went into our Analyze+ tool and extracted the EDM Pool data (this is the new data in DB2 10 NF that is synonymous with the Dynamic Statement Cache counters and statistics) and sorted by Total Elapsed Time descending to get this:

 

Analyze+ for DB2 z/OS ----- EDM Pool (6/12) -------- Stmt 1 from 316 
Command ===>                                         Scroll ===> CSR  
                                                           DB2: QA1B 
Primary cmd: END, SE(tup), Z(oom), L(ocate) 
             total elapse time                 
Line    cmd: Z(oom), A(nalyze), D(ynamic Analyze), E(dit Statement), 
             P(ackage), S(tatement Text)
                                                               
                     Total       Average         Total       Average
     StmtID   Elapsed Time  Elapsed Time      CPU Time      CPU Time
----------- HHHH:MM:SS.ttt HHH:MM:SS.ttt HHH:MM:SS.ttt HHH:MM:SS.ttt
     115967    1:28.107705     29.369235   1:12.151391     24.050464
     114910       8.367834      0.000331      6.779229      0.000268
      79642       7.998559      0.054412      6.346829      0.043176
     114907       5.760045      0.000238      4.378691      0.000181
     115974       5.031890      2.515945      2.937258      1.468629
       5439       4.037261      0.000739      2.685938      0.000492

 

Over one hour total and over 29 minutes average for our small amount of test data set alarm bells ringing – so I drilled down to the SQL:

 

Analyze+ for DB2 z/OS -- View EDM-Pool Statement LINE 00000001 COL 001 080
Command ===>                                             Scroll ===> CSR
                                                         DB2: QA1B
Primary cmd: END
Collection:RTDX0510_PTFTOOL
Package   :M2DBSC09 
Contoken  :194C89620AE53D88  PrecompileTS: 2012-10-29-15.34.40.938230
StmtID    :          115967  StmtNo      :      1223  SectNo:       2
---------------------------------------------------------------------
DECLARE
  SYSCOPY-IC-MODI-9N
CURSOR WITH HOLD FOR 
SELECT                                                                       
  T1.N1 , T1.N2 , T1.N3 , T1.N4 , T1.N5 , T1.N6 , T1.N7 , T1.N8 , T1.N9 
  , T1.N10 , T1.N11 , T1.N12 
FROM (
  SELECT       
    ICTS.DBNAME AS N1
  , ICTS.TSNAME AS N2
  , ICTS.TIMESTAMP AS N3
  , ' ' AS N4
  , ICTS.DSNUM AS N5
 , ICTS.ICTYPE AS N6
 , DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICTS.TIMESTAMP ) AS N7
 , ICTS.OTYPE AS N8
 , ICTS.DSNAME AS N9
 , ICTS.ICUNIT AS N10
 , ICTS.INSTANCE AS N11
 , ICTS.STYPE AS N12                                                                    
  FROM SYSIBM.SYSCOPY ICTS                                                     
  WHERE ICTS.ICBACKUP IN ( '  ' , 'LB' , 'FC' )                            
  AND  ICTS.OTYPE = 'T' 
  UNION                    
   SELECT                           
    ICIX.DBNAME AS N1
  , CAST ( TABLES.TSNAME AS CHAR ( 8 ) CCSID EBCDIC ) AS N2
  , ICIX.TIMESTAMP AS N3
  , ICIX.TSNAME AS N4
  , ICIX.DSNUM AS N5
  , ICIX.ICTYPE AS N6
  , DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICIX.TIMESTAMP ) AS N7
  , ICIX.OTYPE AS N8
  , ICIX.DSNAME AS N9
  , ICIXS.ICUNIT AS N10
  , ICIX.INSTANCE AS N11
  , ICIX.STYPE AS N12
   FROM SYSIBM.SYSCOPY ICIX
      , SYSIBM.SYSINDEXES INDEXES
      , SYSIBM.SYSTABLES TABLES           
   WHERE ICIX.ICBACKUP IN ( '  ' , 'LB' , 'FC' )                          
   AND  ICIX.OTYPE = 'I' 
   AND VARCHAR ( ICIX.DBNAME , 24 ) = INDEXES.DBNAME       
   AND VARCHAR ( ICIX.TSNAME , 24 ) = INDEXES.INDEXSPACE 
   AND INDEXES.TBNAME = TABLES.NAME
   AND INDEXES.TBCREATOR = TABLES.CREATOR           
   AND  TABLES.TYPE IN ( 'H' , 'M' , 'P' , 'T' , 'X' ) )    
 AS T1                                                   
 ORDER BY CAST (T1.N1 AS CHAR ( 8 ) CCSID EBCDIC )
        , CAST (T1.N2 AS CHAR ( 8 ) CCSID EBCDIC )
        , N3 DESC 
 FOR FETCH ONLY       
 WITH UR                                                             



 HOSTVARIABLE NAME             NULLABLE  TYPE           LENGTH  SCALE
 ---------------------------  --------  --------------  -----  -----
 WORK-CURRENT-DATE             NO        CHAR              26       
 WORK-CURRENT-DATE             NO        CHAR              26       
******************************** Bottom of Data **********************

 

Ok, ok this SQL is not going to win a beauty contest any day soon but it used to run just fine…so now I explained it:

Analyze+ for DB2 z/OS -- Explain Data (1/6) --------- Entry 1 from 7  
Command ===>                                         Scroll ===> CSR   
EXPLAIN: DYNAMIC     MODE: CATALOG                         DB2: QA1B  
Primary cmd: END, T(Explain Text), V(iolations), R(unstats), 
             P(redicates), S(tatement Text), C(atalog Data),
             M(ode Catalog/History),Z(oom), PR(int Reports), 
             SAVExxx, SHOWxxx
             
Line    cmd: Z(oom), C(osts), I(ndexes of table), S(hort catalog),
             T(able), V(irtual indexes of table), 
             X(IndeX)                               
Collection : RTDX0510_PTFTOOL   Package : M2DBSC09   Stmt :     1223
Version    : - NONE -         
Milliseconds:  77519  Service Units: 220222  Cost Category: B
                                                                                
  QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO 
  PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ 
  ---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- --- 
     1 SELECT R510PTFT T1         R        0 N     0    0 W    S      0 
     1 5 
     1 SELECT                              0 N     3    0 -           0 
     2 0  
     2 UNION                               0       3    1 -           0 
     1 0 
     3 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0 
     1 1         
     4 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0 
     1 2        
     4 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0 
     2 3      SYSIBM   DSNDXX02       
     4 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0 
     3 4      SYSIBM   DSNDTX01      
  ---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- ---

 

This is *after* I had REORGed the SYSCOPY, SYSTSIXS and SYSTSTAB and then run the RUNSTATS on the SYSTSIXS and SYSTSTAB as you cannot do inline RUNSTATS on those two of course!

 

Two tablespace scans against the SYSCOPY is not brilliant of course but in this system we only have 4,000 table spaces and 2,500 indexes… so then I used the Catalog primary command to have another look at the catalog data:

TS   : DSNDB06 .SYSCOPY 
Stats: 2013-02-04-10.49.32.600316 
  Partitions:  0 , Tables: 1 , NACTIVEF: 18.272 pages 
  Type      :  Neither a LOB nor a MEMBER CLUSTER.
  RTS data TOTALROWS : 347.087 , Pages: 18.268
              
Table: SYSIBM.SYSCOPY 
Stats: 2013-02-04-10.49.32.600316
  No. of rows (CARDF): 347.082 , Pages: 18.268 
  Index: SYSIBM.DSNUCH01 
  Stats: 2013-02-04-10.49.32.600316     Type: Type-2 index
    Levels: 3 , Leaf pages: 3.945 
    FIRSTKEYCARDF: 101 , FULLKEYCARDF: 347.082 
    RTS data Levels: 3 , Leaf pages: 3.945 , TOTALENTRIES: 347.087 
    CLUSTERING: Y , CLUSTERED: Y , CLUSTERRATIO = 100,00% 
    DATAREPEATFACTORF: 18.268 
    Indexcolumn  ! Format        ! Dist. Values ! A/D ! NL ! Stats  
    -------------+---------------+--------------+-----+----+-------
    DBNAME       ! CHAR(8)       !          101 ! ASC ! N  ! OK  
    TSNAME       ! CHAR(8)       !          712 ! ASC ! N  ! OK  
    START_RBA    ! CHAR(6)       !       72.398 ! DSC ! N  ! OK  
    TIMESTAMP    ! TIMESTAMP(6)  !      347.082 ! DSC ! N  ! OK  
                                                                               
  Index: SYSIBM.DSNUCX01                                                 
  Stats: 2013-02-04-10.49.32.600316   Type: Type-2 index 
    Levels: 3 , Leaf pages: 509
    FIRSTKEYCARDF: 1.820 , FULLKEYCARDF: 1.820
    RTS data Levels: 3 , Leaf pages: 509 , TOTALENTRIES: 347.087 
    CLUSTERING: N , CLUSTERED: Y , CLUSTERRATIO = 100,00%
    DATAREPEATFACTORF: 18.275 
    Indexcolumn  ! Format        ! Dist. Values ! A/D ! NL ! Stats 
    -------------+---------- ----+--------------+-----+----+-------
    DSNAME       ! CHAR(44)      !        1.820 ! ASC ! N  ! OK    
                                                

Here I had a heart attack! 347,087 rows?!?!?!?!?!? How in the wide wide world of sports did that happen? Time to drill down into the contents of SYSCOPY with this little query:

SELECT ICTYPE , STYPE,  COUNT(*) 
FROM SYSIBM.SYSCOPY  
GROUP BY ICTYPE , STYPE            
;

Which returned these rather surprising results:

---------+---------+---------+
ICTYPE  STYPE                          
---------+---------+---------+
A       A                4             
B                       46             
C       L             1669             
C       O                4             
F                      100             
F       W               16             
I                        4             
L       M           344723             
M       R               18             
R                      151             
S                       62             
W                       18             
W       S                1             
Y                        2             
Z                      269             
DSNE610I NUMBER OF ROWS DISPLAYED IS 15

The L and M combination appears 344,723 times!!!

Grab your handy DB2 10 SQL reference and page on down to DB2 Catalog tables, SYSIBM.SYSCOPY and you will see:

ICTYPE CHAR(1) NOT NULL

Type of Operation:

A ALTER
B REBUILD INDEX
C CREATE
D CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility)
E RECOVER (to current point)
F COPY FULL YES
I COPY FULL NO
L SQL (type of operation)
M MODIFY RECOVERY utility
P RECOVER TOCOPY or RECOVER TORBA (partial recovery point)
Q QUIESCE
R LOAD REPLACE LOG(YES)
S LOAD REPLACE LOG(NO)
T TERM UTILITY command
V REPAIR VERSIONS utility
W REORG LOG(NO)
X REORG LOG(YES)
Y LOAD LOG(NO)
Z LOAD LOG(YES)

Now in my version the L entry has a ‘|’ by it to signify it is new. Scroll on down further to STYPE to read

STYPE CHAR(1) NOT NULL

Sub-type of operation:

When ICTYPE=L, the value is:

M Mass DELETE, TRUNCATE TABLE, DROP TABLE, or ALTER TABLE ROTATE PARTITION.
The LOWDSNUM column contains the table OBID of the affected table.

So, in other words, every time a program does a MASS DELETE it inserts a row into SYSCOPY. So then I ran another query to see when this all began and, hopefully, ended:

SELECT MAX(ICDATE), MIN(ICDATE) 
FROM SYSIBM.SYSCOPY                   
WHERE ICTYPE = 'L'                    
;                                     
---------+---------+---------+--------
                                      
---------+---------+---------+--------
121107  120828                        
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

 

So we started getting records on the 28th August 2012 and the last one was the 7th November 2012 so in just about ten weeks even we managed 344,723 Mass Deletes!

So now, with my Sherlock Holmes deer stalker hat on, the question was “Why did it stop in November?” Happily we have a history here of APARs and that’s when this PMR bubbled to the Surface:

PM52724: MASS DELETES ENDS UP WITH LOCK ESCALATION ON SYSCOPY IN V10. BECAUSE PM30991 INTALLED CODE INSERTING L 12/01/04 PTF PECHANGE

 

I will let you go and read the text but suffice it to say IBM realized what a disaster this “logging” of Mass Deletes was and HIPERed a quick fix to stop it! Plus you can see the APAR that “brought in the dead mouse” PM30991.

PM30991 UK66327 Closed 2011-03-30

PM52724 UK80113 Closed 2012-07-03

So if you installed the PM30991 and not the PM52724 you probably have some cleaning up to do…

By the way I also rewrote the Ugly Duckling SQL:

  SELECT  T1.N1      
         ,T1.N2         
         ,T1.N3              
         ,T1.N4         
         ,T1.N5        
         ,T1.N6       
         ,T1.N7 
         ,T1.N8      
         ,T1.N9     
         ,T1.N10         
         ,T1.N11        
         ,T1.N12       
    FROM (                                   
   SELECT ICTS.DBNAME    AS N1   
         ,ICTS.TSNAME    AS N2      
         ,ICTS.TIMESTAMP AS N3      
         ,' '            AS N4     
         ,ICTS.DSNUM     AS N5     
         ,ICTS.ICTYPE    AS N6     
         ,DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICTS.TIMESTAMP ) AS N7
         ,ICTS.OTYPE     AS N8             
         ,ICTS.DSNAME    AS N9               
         ,ICTS.ICUNIT    AS N10              
         ,ICTS.INSTANCE  AS N11              
         ,ICTS.STYPE     AS N12              
     FROM SYSIBM.SYSCOPY ICTS                
    WHERE ICTS.ICBACKUP IN ('  ','LB','FC')  
      AND ICTS.OTYPE    = 'T'
UNION ALL    
   SELECT ICIX.DBNAME     AS N1    
         ,CAST(TABLES.TSNAME                 
          AS CHAR(8) CCSID EBCDIC) AS N2  
         ,ICIX.TIMESTAMP  AS N3      
         ,ICIX.TSNAME     AS N4
         ,ICIX.DSNUM      AS N5              
         ,ICIX.ICTYPE     AS N6              
         ,DAYS ( :WORK-CURRENT-DATE ) - DAYS ( ICIX.TIMESTAMP ) AS N7
         ,ICIX.OTYPE      AS N8              
         ,ICIX.DSNAME    AS N9               
         ,ICIX.ICUNIT    AS N10              
         ,ICIX.INSTANCE  AS N11              
        ,ICIX.STYPE     AS N12                
    FROM SYSIBM.SYSCOPY ICIX                  
        ,SYSIBM.SYSINDEXES INDEXES            
        ,SYSIBM.SYSTABLES TABLES              
   WHERE ICIX.ICBACKUP IN ('  ','LB','FC')    
     AND ICIX.OTYPE        = 'I'              
     AND ICIX.DBNAME      = INDEXES.DBNAME    
     AND ICIX.TSNAME      = INDEXES.INDEXSPACE
     AND INDEXES.TBNAME    = TABLES.NAME      
     AND INDEXES.TBCREATOR = TABLES.CREATOR   
 ) AS T1                                      
ORDER BY CAST(T1.N1 AS CHAR(8) CCSID EBCDIC)  
        ,CAST(T1.N2 AS CHAR(8) CCSID EBCDIC)  
        ,        N3 DESC                      
  FOR FETCH ONLY                              
  WITH UR                                     
  ;

 

To now perform like this:

Milliseconds:  55911  Service Units:   158836  Cost Category: A 
                                                                                 
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO 
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ 
---- ------ -------- ---------- ----- ---- -- ---- ---- ---- ---- ---
   1 NCOSUB SYSIBM   SYSINDEXES I        0 N    0     2 T    S      0 
   1 3      SYSIBM   DSNDXX07   
   1 NCOSUB SYSIBM   SYSTABLES  I        2 N    1     2 T           0 
   2 4      SYSIBM   DSNDTX01
   1 NCOSUB SYSIBM   SYSCOPY    I        2 N    1     2 T    S      0 
   3 2      SYSIBM   DSNUCH01    
   2 UNIONA                              0 N    3     0 -           0 
   1 0   
   5 NCOSUB SYSIBM   SYSCOPY    R        0 N    0     2 T    S      0 
   1 1         
   ------ -------- ----------  ------ ---- -- ---- ---- ---- ----- ---

 

I am sure once I have deleted all the SYSCOPY rows (Note that we do not need to RECOVER on our test machine so I have the luxury of being able to delete the data – You, of course, cannot!) that it will return to being a nice little SQL!

 

After a large DELETE run which left only 2,365 rows followed by a REORG with inline RUNSTATS the original SQL now looks like:

 

Milliseconds:       672  Service Units:       1909  Cost Category: B  
                                                                               
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ
---- ------ -------- ---------- ----- ----- ---- -- ---- ---- ---- --
   1 SELECT R510PTFT T1         R        0 N     0    0 W    S      0
   1 5   
   1 SELECT                              0 N     3    0 -           0
   2 0   
   2 UNION                               0       3    1 -           0
   1 0 
   3 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 1  
   4 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 2  
   4 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0
   2 3      SYSIBM   DSNDXX02                 
   4 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0
   3 4      SYSIBM   DSNDTX01      
  ---- ------ -------- ------------------ ----- ---- -- ---- ---- ----

And my version:

Milliseconds:      631  Service Units:     1792  Cost Category: A          
                                                                                
QBNO QBTYPE CREATOR  TABLE NAME       MTCH IX METH PRNT TABL PRE  MXO
PLNO TABNO  XCREATOR INDEX NAME ACTYP COLS ON OD   QBLK TYPE FTCH PSQ
---- ------ -------- ---------- ----- ---- ---- -- ---- ---- ---- ---- 
   1 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 2       
   1 NCOSUB SYSIBM   SYSINDEXES I        2 N     1    2 T           0
   2 3      SYSIBM   DSNDXX02  
   1 NCOSUB SYSIBM   SYSTABLES  I        2 N     1    2 T           0
   3 4      SYSIBM   DSNDTX01 
   2 UNIONA                              0 N     3    0 -           0
   1 0             
   5 NCOSUB SYSIBM   SYSCOPY    R        0 N     0    2 T    S      0
   1 1   
   ---- ------ -------- ------------------ ----- ---- -- ---- ---- ----

Doesn’t look quite so impressive now…sniff…sniff

 

Here’s my SYSCOPY query for all cases:

SELECT ICTYPE, STYPE, MIN(ICDATE) AS OLDEST, MAX(ICDATE) AS NEWEST
     , COUNT(*) AS COUNT                                          
FROM SYSIBM.SYSCOPY                                               
GROUP BY ICTYPE , STYPE                                           
;                                                                 
---------+---------+---------+---------+---------+
ICTYPE  STYPE  OLDEST  NEWEST        COUNT           
---------+---------+---------+---------+---------+
A       A      121228  121228            4        
B              121228  130128           46       
C       L      100809  130204         1669    
C       O      120827  120827            4    
F              100809  130204          100     
F       W      100809  130204           16   
I              130131  130204            4
M       R      130102  130131           18         
R              120829  130130          151        
S              120829  130131           62      
W              100809  130204           18       
W       S      100809  100809            1     
Y              120828  120828            2    
Z              120828  130201          269    
DSNE610I NUMBER OF ROWS DISPLAYED IS 14

 

Clean Up

OK, so what can you do if you have 1000’s of these records? Well I would start with MODIFY RECOVER utilities to delete the bad guys. Your “normal” DB2 Database Maintenance jobs should take care of this for you but if you do not run these on a regular basis then start with this query:

SELECT A.DBNAME, A.TSNAME, A.DSNUM                              
      , MAX(DATE(A.TIMESTAMP)), MIN(DATE(A.TIMESTAMP)), COUNT(*)
FROM SYSIBM.SYSCOPY A                                           
WHERE A.ICTYPE = 'L'                                            
  AND A.STYPE  = 'M'                                            
  AND EXISTS (SELECT 1                                          
              FROM SYSIBM.SYSCOPY B                             
              WHERE A.DBNAME = B.DBNAME                         
                AND A.TSNAME = B.TSNAME                         
                AND (A.DSNUM  = B.DSNUM                         
                 OR (A.DSNUM > 0                                
                 AND B.DSNUM = 0 ))                             
                AND A.START_RBA < B.START_RBA                   
                AND B.ICTYPE = 'F')                             
GROUP BY A.DBNAME, A.TSNAME, A.DSNUM                            
FOR FETCH ONLY                                                  
WITH UR                                                         
;

The output shows you the DBNAME, TSNAME, DSNUM and counts for all of the “bad guys” that have at least one Full Image Copy *after* the bad guy was inserted:

---------+---------+---------+---------+---------+---------+---------+
DBNAME    TSNAME          DSNUM                                       
---------+---------+---------+---------+---------+---------+---------+
R510D0PT  R510S04             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S10             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S12             0  2016-01-26  2015-11-11           75  
R510D0PT  R510S14             0  2016-01-26  2015-11-11           75

 

This gives you the needed input to write a simple MODIFY RECOVER utility input like this:

 

MODIFY RECOVERY TABLESPACE R510D0PT.R510S04 DELETE DATE 20151203

Why did I use 2015-12-03 when in the query output I have 2015-11-11? That’s because I do not want to delete *all* of the Image Copy data in SYSCOPY – just the data from 2015-11-11 to 2015-12-02 (remember that MODIFY works up to the date *before* you enter). The output from the MODIFY looked like this:

 

DSNU000I    028 09:10:43.97 DSNUGUTC - OUTPUT START FOR UTILITY, 
            UTILID = PTFMO000MOU001
DSNU1044I   028 09:10:44.16 DSNUGTIS - PROCESSING SYSIN AS EBCDIC
DSNU050I    028 09:10:44.17 DSNUGUTC -  MODIFY RECOVERY TABLESPACE
            R510D0PT.R510S04 DELETE DATE 20151203
DSNU517I    -QA1B 028 09:10:44.28 DSNUMDEL 
            - SYSCOPY RECORD DELETED BY MODIFY UTILITY.
DSN=SETEST.R510D0PT.R510S04.D15142.T0635, VOL=SER=(CATLG), FILESEQNO=0
DSNU575I   -QA1B 028 09:10:44.49 DSNUMODA - MODIFY COMPLETED SUCCESSFULLY
DSNU010I    028 09:10:44.52 DSNUGBAC - UTILITY EXECUTION COMPLETE, 
            HIGHEST RETURN CODE=0

 

Now you must also IDCAMS Delete any, and all, of the dataset names output here.

Now re-running the original query gives:

---------+---------+---------+---------+---------+---------+---------
DBNAME    TSNAME          DSNUM                                      
---------+---------+---------+---------+---------+---------+---------
R510D0PT  R510S04             0  2016-01-26  2015-12-03           53 
R510D0PT  R510S10             0  2016-01-26  2015-11-11           75 
R510D0PT  R510S12             0  2016-01-26  2015-11-11           75 
R510D0PT  R510S14             0  2016-01-26  2015-11-11           75

 

24 type L & M records gone from between 2015-11-11 and 2015-12-02 and of course the object is not in COPY Pending status!

However, there are cases where you cannot do this straightaway (Perhaps never been image copied?). In these cases, the simplest method is to do an Image copy and then a Modify, but if the object is to big then you must make the DB2 Catalog updateable (ask your friendly Sysprog about this,) and simply use an SQL DELETE to remove all of the entries for an object *before* the last Full Image Copy.

Once you have removed all the fluff, remember to REORG SYSCOPY and RUNSTAT the indexes. Then it should be a *lot* thinner and make migrating a tad easier!

 

As always if you have any comments or questions please email me!

TTFN

Roy Boxwell

 

2016-02 Real Time Statistics (RTS) Revisited – Information missing (part 1)

Easy Real Time Statistics (RTS) data initialization from DB2 9 to DB2 11:

Special query to run before a REORG, RUNSTAT or a DB2 Migration. How many RTS rows did you find?

 

Hands up who knows nothing about the RTS? Good, all hands are down! I had an interesting experience the other day with one of my customers as they are in the process of doing the big bang “REORG the world” to get from a six byte RBA/LRSN to a 10 Byte RBA/LRSN due to problems with data cloning in a mixed DB2 release Environment.

 

RTS Database Maintenance

They use the RTS to drive the creation of REORG, RUNSTAT, and COPY utilities as this is the modern and correct way to go, right? Well, they ended up with a bunch of objects that refused to REORG. I looked high and low for *any* reason as to why they would be excluded from processing and found none. Well, actually, I lie – there was one, and that was the fact that the candidate list was based upon a SELECT from the RTS tables and then joining to the DB2 Catalog to refine the data and then finally generating the required REORG jobs.

 

RTS data missing

It was noticed that these tablespaces were either empty or very small and it was seen that they did not even *exist* in the RTS! Now cast your mind way way way back to DB2 V7 when the RTS were introduced as an “optional” feature. I wrote a little SQL INSERT to populate the RTS for any missing elements as the IBM way of populating the RTS was to “REORG the world” (remember those halcyon days?) Anyway these days, about 11 years later, it is *always* assumed that:

– The RTS data exists
– The RTS data is correct (mainly!)
– RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

RTS data initialization made easy

So, to save you all from trying to find my SQL from those days, here’s the DB2 9 and above version which you can, perhaps must, run to make sure you have no “bodies in the cellar” like my customer did!

 

------------------------------------------------------------------------
-- THESE TWO QUERIES WILL FILL THE RTS TABLES SYSIBM.SYSTABLESPACESTATS
-- AND SYSIBM.SYSINDEXSPACESTATS WITH DEFAULT AND, WHEN POSSIBLE,     --
-- WITH CATALOG DATA FOR MISSING ENTRIES                              --
-- (OBJECTS FOUND IN THE CATALOG BUT NOT IN RTS TABLES)               --
------------------------------------------------------------------------
-- LOCK TABLE SYSIBM.SYSTABLESPACESTATS IN EXCLUSIVE MODE ;         

INSERT INTO SYSIBM.SYSTABLESPACESTATS
 (UPDATESTATSTIME,NACTIVE,EXTENTS)
 ,LOADRLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGUPDATES,REORGUNCLUSTINS
 ,REORGDISORGLOB,REORGMASSDELETE,REORGNEARINDREF,REORGFARINDREF
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSUPDATES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,PSID,PARTITION,INSTANCE,SPACE,TOTALROWS 
 ,DBNAME,NAME)
 SELECT CURRENT TIMESTAMP 
 ,CASE A.SPACEF 
  WHEN -1 THEN CASE A.SPACE 
               WHEN 0 THEN NULL 
               ELSE A.SPACE / B.PGSIZE 
               END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.EXTENTS
  WHEN -1 THEN NULL
  ELSE A.EXTENTS
  END
  ,TIMESTAMP('0001-01-01-00.00.00.000000')
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0 , 0   
  , 0 , 0 , 0 , 0  
  ,CASE 
    WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000') 
    THEN A.STATSTIME 
    WHEN A.STATSTIME < A.CREATEDTS THEN 
                       TIMESTAMP('0001-01-01-00.00.00.000000')
    ELSE A.STATSTIME
    END
  , 0 , 0 , 0 , 0 
  ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0   
  , 'N'   
  ,B.DBID,B.PSID,A.PARTITION,B.INSTANCE  
  ,CASE A.SPACEF 
    WHEN -1 THEN CASE A.SPACE 
                 WHEN 0  THEN NULL  
                 ELSE A.SPACE 
                 END 
    ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
    END 
   ,CASE A.CARDF 
    WHEN -1 THEN NULL  
    ELSE A.CARDF 
    END 
   ,A.DBNAME,A.TSNAME  
    FROM SYSIBM.SYSTABLEPART  A 
        ,SYSIBM.SYSTABLESPACE B 
    WHERE NOT EXISTS (SELECT C.*  
                     FROM SYSIBM.SYSTABLESPACESTATS C 
                     WHERE A.DBNAME = C.DBNAME 
                       AND A.TSNAME = C.NAME  
                       AND A.PARTITION = C.PARTITION)    
     AND NOT A.SPACE  = -1   
     AND A.DBNAME     = B.DBNAME   
     AND A.TSNAME     = B.NAME    
  ; 
COMMIT ;
-- LOCK TABLE SYSIBM.SYSINDEXSPACESTATS IN EXCLUSIVE MODE ;
INSERT INTO SYSIBM.SYSINDEXSPACESTATS
 (UPDATESTATSTIME
 ,NLEVELS,NLEAF,NACTIVE,SPACE,EXTENTS
 ,LOADRLASTTIME
 ,REBUILDLASTTIME
 ,REORGLASTTIME,REORGINSERTS,REORGDELETES,REORGAPPENDINSERT
 ,REORGPSEUDODELETES,REORGMASSDELETE,REORGLEAFNEAR,REORGLEAFFAR
 ,REORGNUMLEVELS
 ,STATSLASTTIME,STATSINSERTS,STATSDELETES,STATSMASSDELETE
 ,COPYLASTTIME,COPYUPDATEDPAGES,COPYCHANGES
 ,IBMREQD
 ,DBID,ISOBID,PSID,PARTITION,INSTANCE
 ,TOTALENTRIES,DBNAME,NAME,CREATOR,INDEXSPACE)
  SELECT CURRENT TIMESTAMP
 ,CASE B.NLEVELS
   WHEN -1 THEN NULL
   ELSE B.NLEVELS
   END
  ,CASE B.NLEAF
   WHEN -1 THEN NULL
   ELSE B.NLEAF
   END
  ,CASE A.SPACEF
   WHEN -1 THEN CASE A.SPACE
                WHEN 0  THEN NULL
                ELSE A.SPACE / B.PGSIZE
                END
  ELSE MIN( 2147483647 , ( MAX(A.SPACEF , A.SPACE) / B.PGSIZE ) )
  END
 ,CASE A.SPACEF
  WHEN -1 THEN CASE A.SPACE
               WHEN 0  THEN NULL
               ELSE A.SPACE
               END
  ELSE MAX( MIN( 2147483647 , A.SPACEF ) , A.SPACE)
  END
 ,CASE A.EXTENTS
   WHEN -1 THEN NULL
   ELSE A.EXTENTS
   END
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000')
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0 , 0
 , 0 , 0 , 0 , 0 , 0
 ,CASE
  WHEN A.STATSTIME = TIMESTAMP('0001-01-01-00.00.00.000000')
  THEN A.STATSTIME
  WHEN A.STATSTIME < A.CREATEDTS THEN
                     TIMESTAMP('0001-01-01-00.00.00.000000')
  ELSE A.STATSTIME
  END
 , 0 , 0 , 0
 ,TIMESTAMP('0001-01-01-00.00.00.000000'), 0 , 0
 , 'N'
 ,B.DBID,B.ISOBID, C.PSID
 ,A.PARTITION,C.INSTANCE
 ,CASE A.CARDF
  WHEN -1 THEN NULL
  ELSE A.CARDF
  END
 ,B.DBNAME,B.NAME,B.CREATOR,B.INDEXSPACE
  FROM SYSIBM.SYSINDEXPART  A
      ,SYSIBM.SYSINDEXES    B
      ,SYSIBM.SYSTABLESPACE C
      ,SYSIBM.SYSTABLES     D
 WHERE NOT EXISTS (SELECT E.*
                   FROM SYSIBM.SYSINDEXSPACESTATS E
                   WHERE B.DBNAME = E.DBNAME
                     AND B.INDEXSPACE = E.INDEXSPACE
                     AND A.PARTITION  = E.PARTITION)
   AND B.CREATOR    = A.IXCREATOR
   AND B.NAME       = A.IXNAME
   AND NOT A.SPACE  = -1
   AND B.TBCREATOR  = D.CREATOR
   AND B.TBNAME     = D.NAME
   AND D.DBNAME     = C.DBNAME
   AND D.TSNAME     = C.NAME
;
COMMIT ;

 

It may even be a good idea to run these two queries on a regular basis… just in case!

I would like to know how many rows these queries INSERTed at your shops – Here in Düsseldorf, in the labs, it found two TS’s and three IX’s in a DB2 11 NFM system.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell

2015-06 SOUNDEX and other “cool” features – Part four: DB2 10-DB2 11

 

Update for DB2 10 and all new for DB2 11

 

This newsletter completes my walk through of new Scalar functions that I began a few years ago. I will start today with two new ones that were introduced by APAR PM56631 in May 2012 to DB2 10. PACK (a Scalar Function), and its opposite number UNPACK (a Row Function).

 

New Scalar functions in DB2 10:

Some PACK examples

PACK basically “packs” together a list of columns, or literals, into one variable length binary string in UNICODE format. Here’s some real PACK examples—note that SYDUMMYU is used!

SELECT PACK(CCSID 1208, 'ALICE', DATE('2014-07-22') , DOUBLE(8.6))      
FROM SYSIBM.SYSDUMMYU;                                                  
---------+---------+---------+---------+---------+---------+---------+--
00000301C0018001E004B80005414C494345201407224021333333333333            
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT PACK(CCSID 1208, 'ROY', BIGINT(123456789) ,                      
TIMESTAMP('2014-07-22-07.43.23.123456'))                                
FROM SYSIBM.SYSDUMMYU;                                                  
---------+---------+---------+---------+---------+---------+---------+--
00000301C001EC018804B80003524F5900000000075BCD15000620140722074323123456
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT PACK(CCSID 1208,                                                 
TIMESTAMP('2014-07-22-07.55.23.123456'))                                
FROM SYSIBM.SYSDUMMYU;                                                  
---------+---------+---------+---------+---------+---------+---------+--
0000010188000620140722075523123456                                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

You can see the format that it creates is actually pretty standard and can have uses in normal processing!

Using the second example above, we can see what is in the output:

The VARBIN string starts with a control flag byte which we can happily ignore

00

Then comes how many elements are in the data

0003

Then comes a set of “number of element” SQLTYPES. If any are odd numbered then the data value is *not* in the string as it is in fact NULL

01C0   Varying Character data 448 in decimal
01EC   BIGINT data 492 in decimal
0188   TIMESTAMP data 392 in decimal

 

The first data element CHAR containing “ROY” looks like

04B8   CCSID of String 1208 in decimal
0003   Length of string 
524F59 Data in UNICODE

 

The second data element BIGINT containing 123456789 looks like

00000000075BCD15      BIGINT data big endian always!

The third data element TIMESTAMP containing ‘2014-07-22-07.43.23.123456’ looks like

0006                  TIMESTAMP precision
20140722074323123456  TIMESTAMP data

UNPACK statements

Now, these VARBIN strings can get very long indeed, but what can you do with all of the data? Well, obviously UNPACK springs to mind… Here’s the above data again, but getting input to an UNPACK statement you have to create a FUNCTION first… something like this:

--#SET TERMINATOR $                                        
CREATE FUNCTION PACKUDF ()                                 
        RETURNS VARBINARY(128)                             
        VERSION V1                                         
        LANGUAGE SQL                                       
        DETERMINISTIC                                      
        NO EXTERNAL ACTION                                 
        CONTAINS SQL                                       
        CALLED ON NULL INPUT                               
        STATIC DISPATCH                                    
        NOT SECURED                                        
        DISABLE DEBUG MODE                                 
        BEGIN                                              
        DECLARE PACKSTR VARBINARY(128);                    
        SET PACKSTR =                                      
               PACK(CCSID 1208                             
                   ,VARCHAR('ROY' , 40)                    
                   ,BIGINT(123456789)                      
                   ,TIMESTAMP('2014-07-22-07.43.23.123456')
                   );                                      
        RETURN PACKSTR;                                    
        END                                                
$   

This just does what the second example did, but buried in a Scalar SQL Function. This can then easily be called by just doing this:

SELECT UNPACK(PACKUDF()).*                                                           
          AS (USER_NAME    VARCHAR(40) CCSID UNICODE                    
             ,LARGE_NUMBER BIGINT                                       
             ,WHEN_USED    TIMESTAMP)                                   
FROM SYSIBM.SYSDUMMY1 ;                                                 
---------+---------+---------+-..--------+---------+---------+---------+---------+---
USER_NAME                      ..            LARGE_NUMBER  WHEN_USED                 
---------+---------+---------+-..--------+---------+---------+---------+---------+---
ROY                                             123456789  2014-07-22-07.43.23.123456
DSNE610I NUMBER OF ROWS DISPLAYED IS 1   
            

I deleted some white space (..) above just to get it all to fit on the page, by the way! Now quite *what* you would do with PACK and UNPACK I’ll leave up to you!!!

 

On now to DB2 11: Aggregate Functions and 11 new scalar functions for new ARRAY data type

Here we got three new Aggregate Functions and 11 new Scalar Functions. Most of these are for the new ARRAY data type that was introduced in DB2 11. First, here’s how you can create ARRAYs, so that you can understand a bit more about the Functions that follow:

CREATE TYPE BOXWELL.ROYARRAY  AS INTEGER  ARRAY ??(??) ;     
CREATE TYPE BOXWELL.ROYARRAY4 AS CHAR(16) ARRAY ??( 32 ??) ; 
CREATE TYPE BOXWELL.ROYARRAY5 AS CHAR(16)                    
   ARRAY ??( VARCHAR(8) CCSID UNICODE FOR MIXED DATA ??) ;

Note that I use trigraphs ( ??( = [ and ??) = ] ), as I *hate* the square brackets! (I would *love* to meet that developer in a dark alley one night…I know, I know, it is like that in the “other” SQL world but it doesn’t mean I have to use them!).

 

Now arrays are pretty cool for passing data back from SQL Scalar Functions, or for Parameters of SQL Scalar Functions and Native SQL Procedures, as it simplifies the handling of lots of same type parameters. This is really good for all the JAVA programmers out there who use array types all the time!

In fact, all of the ARRAY stuff is *only* allowed within the confines of Stored Procedures and SQL PL.

What also arrived in DB2 11 is the ability to “unravel the array” using the new “collection-derived-table” syntax—otherwise known as UNNEST—and here are the two examples from the doc:

 

Example 1: Suppose that PHONENUMBERS is a user-defined array type that is defined as an ordinary array.

RECENT_CALLS is an array variable of the PHONENUMBERS type. RECENT_CALLS contains the following phone numbers:

9055553907
4165554213
4085553678
The following SELECT statement uses UNNEST to retrieve the list of phone numbers from the Array:

SELECT T.ID, T.NUM FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID);

ID NUM
1 9055553907
2 4165554213
3 4085553678

SET PHONELIST[’Home’] = ’4443051234’;
SET PHONELIST[’Work’] = ’4443052345’;
SET PHONELIST[’Cell’] = ’4447893456’;

The following SELECT statement is executed:

SELECT T.ID, T.PHONE FROM UNNEST(PHONELIST) AS T(ID, PHONE);

The result table looks like this, although the order of rows might differ:

ID PHONE
Cell 4447893456
Home 4443051234
Work 4443052345

 

The Array Aggregate Function, ARRAY_AGG, and all of the Scalar Functions:

ARRAY_DELETE,
ARRAY_FIRST,
ARRAY_LAST,
ARRAY_NEXT,
ARRAY_PRIOR,
CARDINALITY,
MAX_CARDINALITY,
and TRIM_ARRAY,
are all available to use but only within the confines of SQL PL.

 

CHAR9 and VARCHAR9

Also new in DB2 11 are CHAR9 and VARCHAR9, which are the good old functions from DB2 9 but now keeping the way they used to work forever—I hope they will not actually be used!

XSLTRANSFORM was also delivered to do XML transformations, but I have never actually tried it. You must note this bit of text:

 

This user-defined function requires IBM SDK for z/OS, Java Technology Edition Version 6.
This user-defined function uses the XSLT support that is provided by the W3C XSL Transformations V1.0 Recommendation.

 

GROUPING & MEDIAN

Then we got two new Aggregate Functions. The first is GROUPING—used in the OLAP extensions for doing automatic sub- and grand totaling, and then MEDIAN—which *only* works if you have the IDAA switched on!

So, lots of interesting stuff here. You may want to go back to the 3 newsletters  from a few years ago (2012) to recap all of these “cool” features. That way you’ll have plenty to read if you get bored on holiday!

 

Feel free to send me your comments and ask questions!

Link back to general newsletter page

2015-02: DB2 z/OS AUDIT – Boring Boring Boring

 

New IFCIDs 316 – 400/401 for DB2 z/OS Audit “on the fly”

I use the Monty Python title as it reminded me of an old telephone book joke in England: look up „boring“ in the Yellow pages – there you would find a little bit of text that simply said „See Civil Engineering.“ It still makes me laugh these days.  Anyway, back to the newsletter…

Auditing is often looked down upon as being boring, tedious and of no worth. This is rubbish of course! Without auditing we would not be allowed to do anything these days. I hardly know any DBAs who still have SYSADM.

It is just too darn powerful!

The statistics also tell us that nearly all “hack” attacks are “inside jobs” from the very people we know and trust. The statistics are also pretty brutal on the Mainframe/Server divide – Mainframes are very rarely successfully attacked, but Servers (see Sony et al.) are all the time. I think there should be a lot more auditing on the little iron really!

 

So how do you audit on the host side of the street?

Do you actively check what is happening? Or do you just wait for the thought police to arrive?
 


 

Enabling Auditing from DB2 10

When the enhanced IFCID 316 and the new IFCIDs 400/401 were introduced, it closed a gap in the ability to actually Audit your system “on the fly”. Using these IFCIDs  you could actually trap/monitor/audit all of the SQL running in your Plex. These IFCIDs are also nearly free as the overhead is “background noise” levels of CPU.

Now, let us imagine that you are capturing all of this data. That you are regularly snapping both the DSC and the SSC, (that’s what I call the EDMPOOL cache for Static SQL statements), that this data is all being rolled up and saved into a DB2 Data warehouse, and that you are triggering Batch jobs to analyse for Audit – reporting using various queries just to see if anything “untoward” is starting/or is happening!

 

What queries would you want to run?

– I have a few straight off the bat here: Who is reading from the Payroll table?
– Who is updating the Payroll or Employee tables?
– Who is accessing *any* table from the internet?
– Is anybody being really clever and using ODBC to select from my production tables?
– How many userids are out there using my data?
– Has any SYSADM enabled userid done any work on my system today?

All good Audit questions that you could put into operation very simply indeed!

 

Oh Lucky Man!

Now as luck would have it, we have a software product called SQL WorkloadExpert that actually does all this for you! What’s more you can expand it as much as you like! Cool huh?

– Who is accessing *any* table from the Internet?…
– Has any SYSADM enabled userid done any work on my system today?…
– How many userids are out there using my data?…

Audit Video (5 min.)   –  Presentation

Looking at this screen shot you can get an idea of the possibilities – Look at the Workstation name column for instance. “192.xxx” is the intranet. If any other tcp/ip address showed up here, it would be, shall we say, “worrying”. You can also see great stuff like “EXCEL.EXE” in the Transaction name column, and that a certain Mr. Boxwell has been running a few things from lots of data sources, I wonder what he’s up to?

News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Here you can see a nice list of “Intents” against a given table (in this case SYSIBM.SYSTABLES)
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Now you can see who did what type of insert against a given object.
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

This is a list of *all* Primary Authorization IDs or Collections and Packages that have run
– Any intruders?
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

This is a list of all User Data Updates done by users with SYSADM authority in the last workload.
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Triggering the Auditor

All of this with drill down to the actual SQL that was executed. Cool stuff and very handy indeed! But this is “past the point” and what you really need is a Batch Style interface that runs the SQLs and triggers alarms on the host before someone looks at the pretty GUI!

I would do this with a nice little set of batch Spufi’s that get post processed and either e-mailed directly to the Auditors, or WTO’d  as an alarm action that then triggers a batch job to do something else.

 

What would you like to Audit? Or what would your Auditors like to see? How do you currently accomplish this?

I would be fascinated to hear from you!

TTFN,

Roy Boxwell