Midwest Database User Group September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

MWDUG – am Mittwoch den 15. September 2016

Don’t let ICIs put your DB2 application in the ICU!

How to discover incompatible changes in your DB2 System

The what and why of ICIs
If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time.
Ways to deal with incompatibilities
This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!
Customer experiences
Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of  WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”.

Speaker’s biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

Wisconsin DB2 Users Group September 2016

SEGUS & SOFTWARE ENGINEERING präsentieren

WDUG – am Dienstag den 14. September 2016

Don’t let ICIs put your DB2 application in the ICU!

How to discover incompatible changes in your DB2 System

The what and why of ICIs
If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time.
Ways to deal with incompatibilities
This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!
Customer experiences
Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of  WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”.

Speaker’s biography

Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.

2016-05 What’s in an Acronym? ICI

Do you know the difference between BIF & ICI?

Release migration: Do you know which role ICIs are playing in the Db2 compatibility?

Just over a year ago I did a little newsletter all about IFICDs 366 and 376, what I call BIFCIDs, that report usage of a function that has changed or a function that works now, but will either fail or work differently, at some point in the future, e.g.: after a Db2 release migration.

When is a BiF not a BiF?

Now, back then I labelled these guys “BIFCIDs” and asked: “Where’s the BiF?” I see now that the name I chose was actually pretty inaccurate! If you take the time to study the macro versions of 366 and 376 (all described in the SDSNMACS library member DSNDQW05) you see that from the 22 IFCIDs only six are actually for a Built-in Function “problem” – all the rest are really ICIs.

What’s an ICI? It’s a TLA (another nice acronym), from Db2 development and stands for “Incompatible Change Indicator”. A really catchy name for a really nasty problem! So let us review the IFCID details, but now from the perspective of “Are you a BIFCID or are you an ICI?”

 

The details for BIFCIDs – die BIFs der IFCIDS list

***********************************************************************
**  QW0366FN = 1                                                      *
**    Indicates that the pre Version 10 CHAR built-in function has    *
**    been invoked. There is an incompatible change to the output of  *
**    the CHAR function for some decimal data. The zparm              *
**    BIF_COMPATIBILITY and/or the SYSCOMPAT_V9 schema have been used *
**    by this application to get the old behavior. Please make the    *
**    appropriate changes and rebind with the SYSCURRENT schema to    *
**    use the Version 10 CHAR(decimal) built-in function.             *
**    (PM29124 V10 only, usermod V8/V9)                               *
**  QW0366FN = 2                                                      *
**    Indicates that the pre Version 10 VARCHAR built-in function or  *
**    CAST(decimal AS CHAR or VARCHAR) has been invoked.              *
**  QW0366FN = 10                                                     *
**   RTRIM, LTRIM or STRIP version 9 being used with mixed data       *
**  QW0366FN = 1109                                                   *
**    Indicates that CAST(string AS TIMESTAMP) was processed for the  *
**    input string of length 8 and input was treated as a store clock *
**    value (or input string was of length 13 and was treated as a    *
**    GENERATE_UNIQUE value). This behavior is incorrect for a CAST   *
**    and is valid for TIMESTAMP built-in function only. This behavior*
**    is being corrected in Db2 11 so that input to CAST is not       *
**    treated as a store clock value nor GENERATE_UNIQUE.             *
**  QW0366FN = 1110                                                   *
**    Indicates the integer argument of SPACE function is greater     *
**    than 32764.                                                     *
**  QW0366FN = 1111                                                   *
**    Indicates the optional integer argument of VARCHAR function     *
**    has a value greater than 32764.                                 *
***********************************************************************

 

IFCIDS list follow: the details for ICIs

***********************************************************************
**  QW0366FN = 3                                                      *
**    Indicates that an unsupported character representation of a     *
**    timestamp string was used. PM48741 V10 only.                    *
**  QW0366FN = 4                                                      *
**   A QW0366FN 4 record indicates that the statement uses the        *
**    word ARRAY_EXISTS as an unqualified user-defined function name  *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 5                                                      *
**    A QW0366FN 5 record indicates that the statement uses the       *
**    word CUBE as an unqualified user-defined function name          *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 6                                                      *
**   A QW0366FN 6 record indicates that the statement uses the        *
**    word ROLLUP as an unqualified user-defined function name        *
**    in a context that may be incompatible with Version 11.          *
**  QW0366FN = 7                                                      *
**   A QW0366FN 7 record indicates that Db2 for z/OS server issued    *
**   a SQLCODE -301 for incompatible data type conversion from        *
**   string data type (e.g. CHAR, VARCHAR, GRAPHIC, VARGRAPHIC        *
**   etc.) to numeric data type in V10 CM mode when implicit          *
**   cast is not supported or V10 NFM mode when DDF_COMPATIBILITY     *
**   zparm is set to DISABLE_IMPCAST_NJV or SP_PARMS_NJV to           *
**   disable implicit cast, and the client is CLI driver              *
**   or v11 NFM mode & APPLCOMPAT = V10R1 when DDF_COMPATIBILITY      *
**   is set to SP_PARMS_NJV or DISABLE_IMPCAST_NJV to disable         *
**   implicit cast either from string data type to numeric or         *
**   from numeric data type to string data type.                      *
**  QW0366FN = 8                                                      *
**   A QW0366FN 8 record indicates that Db2 for z/OS server           *
**   returned output data match the data types of the                 *
**   corresponding CALL statement arguments when DDF_COMPATIBILITY    *
**   zparm is set to SP_PARMS_NJV.                                    *
**                                                                    *
**  QW0366FN = 9                                                      *
**    A QW0366FN 9 record indicates a data type conversion from       *
**    a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data            *
**    during input host variable bind-in process on server when       *
**    DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the       *
**    time zone information sent by Java IBM Data Server Driver.      *
**  QW0366FN = 1101                                                   *
**    Indicates that the INSERT statement that inserts into an XML    *
**    column without XMLDOCUMENT function has been processed (which   *
**    should result in SQLCODE -20345 when run on Db2 release prior   *
**    to V11). Starting with V11, SQL error will no longer be issued. *
**    Application will no longer recieve SQLCODE for this statement.  *
**  QW0366FN = 1102                                                   *
**    Indicates that V10 XPath evaluation behavior was in effect which*
**    resulted in an error. For instance, a data type conversion error*
**    could have occured for a predicate that would otherwise be      *
**    evaluated to false. Starting from V11, such "irrelevant" errors *
**    might be suppressed so an application might no longer recieve   *
**    the SQLCODE for this statement.                                 *
**  QW0366FN = 1103                                                   *
**    Indicates that a dynamic SQL uses the ASUTime limit that has    *
**    been set for the entire thread for RLF reactive governing.      *
**    For instance, when a dynamic SQL is processed from package A,   *
**    if the ASUTime limit is already set during other dynamic SQL    *
**    processing from package B in the same thread, the SQL from      *
**    package A will use the ASUTime limit set during the SQL         *
**    processing from package B. Stating with v11, dynamic SQLs from  *
**    multiple packages will use the ASUTime limit that is set        *
**    considering its own package information.                        *
**  QW0366FN = 1104, 1105, 1106, 1107                                 *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    A shorter value has been used instead.                          *
**  QW0366FN = 1108                                                   *
**    Indicates that CLIENT special register (CLIENT_USERID,          *
**    CLIENT_WRKSTNNAME, CLIENT_APPLNAME, CLIENT_ACCTNG) has been set *
**    to a value that is longer than what is supported prior to V11.  *
**    Truncated values upto the supported lengths prior to v11 have   *
**    been used for RLF table search instead.                         *
**  QW0366FN = 1112                                                   *
**   Indicates the empty XML element is serialized to <X></X>         *
**   instead of <X/>.                                                 *
***********************************************************************

What does this all mean?

You might well be wondering: “What’s the point?” Well, the point is to try and get the ICI’s acknowledged in your company and to start checking for them now. Why do I say this? Because the APPLCOMPAT is only going to work for two down levels. What that means is that APPLCOMPAT at Db2 10 is good for Db2 11 and Db2 12 but *dies* horribly at Db2 12 +1 which, if IBM stick to their track record of Db2 delivery dates, will be October 2019. Not that far away in the future is it?

 

The BiF problem is fixed – long live the ICI Problem!

OK, you might have traced and tracked your BIFs, but what about the ICIs? At one firm I visited, all the BIFCIDs were clean until this February which, as a leap year, had 29 days. This caused different programs to run and…Tra La! New BIFCIDs came creeping out of the woodwork…along with some new ICIs…

 

Freeware to the rescue

To help you save time, just download and test our little BiF Freeware, which tracks down all ICIs and BiFs for you. Register, download, and then see where your system stands in the ICI rankings. We have also changed our Use Case “BIF usage” in WorkloadExpert to be only for BiFs and have introduced a new Use Case called “BIF/ICI occurrences” which shows them all.

As usual, any question or comments gladly welcome!

 

TTFN

Roy Boxwell

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

 

BIF HealthCheck Licensed Freeware for DB2 10

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF/ICI HealthCheck overview (Built-in Function Checker for DB2 z/OS)

BIF/ICI HealthCheck reports the following BIF incompatibilities in DB2 10

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ARRAY_EXISTS
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name CUBE
  • Use of a USER-DEFINED FUNCTION (UDF) that has the unqualified name ROLLUP

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).

  • Execution of a SQL statement by a client non-Java application that included an unsupported conversion from a string type to a numeric type, while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)
    • In version 10 Conversion Mode (CM)
    • In version 10 New-Function Mode (NFM) and implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJ

 

BIF/ICI incompatibilities in DB2 11

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealthCheck – This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation


Customer Comments
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

 

 

BIF HealthCheck Licensed Freeware for DB2 11

Detect static and dynamic SQL and match to the relevant collection, packages,…

BIF HealtchCheck overview (Built-in Function Checker for DB2 z/OS)

BIF HealthCheck reports the following BIF incompatibilities in DB2 11

  • Execution of the DB2 9 for z/OS version of SYSIBM.CHAR(DECIMAL-EXPR)
  • Execution of the DB2 9 for z/OS version of SYSIBM.VARCHAR(DECIMAL-EXPR), CAST (DECIMAL AS VARCHAR), OR CAST (DECIMAL AS CHAR)

  • Use of an unsupported character string representation of a TIMESTAMP
  • Use of the DB2 10 for z/OS default SQL path instead of the V11 path, which has more implicit Schemas

  • Execution of a non-Java client that called a Stored Procedure (SP) that is on the DB2 for z/OS Data Server, while subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV (the Data Server returned output argument values whose data types matched the data types of the call statement arguments).
  • Execution of an insert statement that inserts into an XML column without the XMLDOCUMENT function, which generates SQLCODE -20345 on a DB2 release prior to V11, but does not generate an error starting in V11

  • V10 XPATH evaluation behavior was in effect, which resulted in an error (e.g. a data type conversion error occurred for a predicate that would otherwise be evaluated to false.). Starting in V11, such errors might be suppressed

  • Execution of a SQL statement by a client non-Java, or Java application that included an unsupported conversion from a string type to a numeric type, or from a numeric type to a string type while the DB2 z/OS Data Server environment was one of the following (the Data Server issues SQLCODE -301)

o The Data Server was in version 11 New-Function Mode (NFM)
o APPLICATION COMPATIBILITY was set to V10R1
o Implicit casting was disabled because subsystem parameter DDF_COMPATIBILITY was set to SP_PARMS_NJV, or DISABLE_IMPCAST_NJV

BIF incompatibilities in DB2 10

 

Mehr über BIF

BIF-Usage

Präsentation

BIF KompatibilitätDB2 10 Kompatibilität Mode

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von DB2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon
„Give and Take
Program“
Seite
Give and Take
Program
We have „GIVEn“ various free-of-charge Use Cases from SQL Workload Expert for DB2 z/OS like
1  Index Maintenance Costs
2  EXPLAIN Suppression
3  BIF Usage 
BIF HealtchCheck (Freeeware)– This last one is still available
We TAKE the anonymized results for research
and will communicate with the local User Groups for discussions
BIF Usage

„Give and Take
Program 3“

Kundenmeinungen
Präsentation

Inspiring experiences

[Customer Comments]

Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

GIVE and TAKE Programme 1, 2, 3


Give and Take 2020

Information on the Give and Take Programs 4,5,6,7


 

Previous Give & Take

We have „GIVEn“ various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

  1  Index Maintenance Costs

  2  EXPLAIN Suppression

  3  BIF Usage

Limited free-of-Charge Db2 Application

This Program started in Europe, during our 30th anniversary was such a success, that it is now being Extended for the benefit of North American Db2 z/OS sites.

SQL WorkloadExpert for Db2 z/OS (WLX) contains several “Use Cases”. We provided three of them, free of charge, for one month to different sites.

In return, we received their results. We’d like to share this inspiring experiences with you now.


Inspiring experiences

We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Kundenmeinungen

3BIF USAGE
News
Lesen Sie die Kundenmeinungen aus unterschiedlichen Industrie Sektoren     [Customer Comments]

 

  • Gesetzliche Krankenversicherung
  • Automobil Industrie
  • IT Provider für Banken
  • Versicherungen

Erste Ergebnisse von Db2 z/OS Kunden

1Index Mantenance CostsNearly all the data we got back showed a positive result for created Indexes…
2EXPLAIN SuppressionAbout 10% of SQLs are actually “left over”…
3BIF Usage When migrating to a new Db2 version, the BIFs are not always compatible and an extreme amount of data is produced.

 

The difficulty of near-time analysis to track down BIFs within dynamic SQL have been solved with the BIF Usage Use Case…

[Ergebnisse von Db2 z/OS Kunden (engl)]

Program 3 – BIF Usage   

BIF-Usage

 

Präsentation

BIF KompatibilitätDb2 10 Kompatibilität Mode

 

Änderungen bei der STRING Formatierung von Decimal Data bei der CHAR und VARCHAR built-in Funktion und bei der CAST Spezifikation mit CHAR und VARCHAR Ergebnis Typen sowie UNSUPPORTED TIMESTAMP STRINGs.

White PaperWo sind die BIFs?
Finding BIFs (engl)
Wo sind die BIFs? Und wie können wir in Zukunft problemlos mit BIFs leben?
Wege aus der mangelnden Aufwärtskompatibilität bei der Migration von Db2 Versionen
Newsletter2015-01 – BIFCIDS – Where’s the BIF? (engl.)How will you deal with loop-hole usage in production code?
VideoBIF Usage (engl)(11min.) Trap  and correct the BIFs that will cause belly-ache one day soon

BIF Usage Video