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
