2013-02: SYSCOPY – Do you know what is in it?

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 SQL PerformanceExpert 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,082 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 CHAR1) 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…
Now try and figure out how to clear up the mess!

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

Finally 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

All the L records have gone! Yippee!

 

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