Kategorie: News from the Labs

Each month, SEG sends out valuable technical DB2 z/OS information based on knowledge gained by the experienced developers and technicians in our product labs. We would like to share this newsletter with other DB2 users, like yourself.

Veröffentlicht am

2018-10 Db2 bind lock: The Scary Case of the Missing Package

In the Db2 world during a BIND/REBIND, particularly while using the command EXPLAIN(ONLY),  you may experience a real Halloween horror story. Data is disappearing and reappearing, which can cause LOCK problems in production.

As luck would have it, we have got the magic formula (see text with long SQL queries…) to get rid of the horrible ghosts, so that you can feel as clean as a ghostbuster.

The scary tale begun like this:

We were contacted by a customer who has been using our BindImpactExpert (BIX) for years to check that no access paths go horribly wrong during REBIND or BIND.

Now with a BIND you obviously have no choice – the program has been compiled and linked and they probably changed the SQL as well, so no chance of just changing the CONTOKEN in the load module, (That’s what another of our products does, ConditionalBind, by the way – it saves the BIND completely if the SQL has not changed.)

Anyway, they were simply doing a BIND … EXPLAIN(ONLY) and…
…  got a LOCK problem in production.

RTFM Time

This was very odd as the EXPLAIN(ONLY) is documented thus:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/comref/src/tpc/db2z_bindoptexplain.html

„Populates EXPLAIN tables but does not create or rebind a package. This option allows EXPLAIN to be run when the authorization ID of the bind or rebind process does not have the privilege to execute statements in the package. If the command that specifies EXPLAIN(ONLY) refers to a package and version that already exists, the existing package is not dropped or replaced, even if ACTION(REPLACE) is specified. „

This has not changed since DB2 10. Highlighting from me, by the way.

CHAPTER 1 : Horror scenes

Trust is good – Test is better

So we decided to do some tests and picked a little package with 38 SQL statements that was already bound to a collection. I generated a 100 BIND statements all looking like this:

BIND PACKAGE(IQA_COLLECTION_610) MEMBER(PDB2D900) QUALIFIER(IQA0610) -
     OWNER(BOXWELL) EXPLAIN(ONLY)

What you see is what you get

While these were running I did a DISPLAY LOCKS and saw this:

DSNT360I  -QB1A **************************************
DSNT361I  -QB1A *    DISPLAY DATABASE SUMMARY        *  
                *      GLOBAL LOCKS                  *
DSNT360I  -QB1A **************************************  

DSNT362I  -QB1A     DATABASE = DSNDB01  STATUS = RW                  
                    DBD LENGTH = 108200                               
DSNT397I  -QB1A                                                       
NAME     TYPE PART   STATUS           CONNID  CORRID    LOCKINFO 
-------- ---- -----  --------------   ------- --------- ---------
SPT01    TS    0001  RW               BATCH   BINDROY   H-IX,P,C 
    -                AGENT TOKEN 568                               
SPT01    TS                                                          
********** DISPLAY OF DATABASE DSNDB01  ENDED ********************
DSN9022I  -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION        
***

So it is doing locks!

I Claim, I Saw, I Conquered

And this from a DISPLAY CLAIMERS:

DSNT360I  -QB1A ***********************************  
DSNT361I  -QB1A *  DISPLAY DATABASE SUMMARY       * 
                *    GLOBAL CLAIMERS              *
DSNT360I  -QB1A ***********************************                   

DSNT362I  -QB1A    DATABASE = DSNDB01  STATUS = RW                    
                   DBD LENGTH = 108200                                 
DSNT397I  -QB1A                                                        
NAME     TYPE PART  STATUS             CONNID   CORRID      CLAIMINFO   
-------- ---- ----- -----------------  -------- ----------- --------   
SPT01    TS    0001 RW                 BATCH    BINDROY      (CS,C)     
    -                  AGENT TOKEN 576                                 
    -                  MEMBER NAME                                     
SPT01    TS    0001 RW                 BATCH    BINDROY      (WR,C)     
    -                  AGENT TOKEN 576                                 
    -                  MEMBER NAME                                     
SPT01    TS                                                            
**************** DISPLAY OF DATABASE DSNDB01  ENDED ***************** 
DSN9022I  -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION          
***                                                                   

And then after a few BINDs had completed:

DSNT360I  -QB1A ***********************************              
DSNT361I  -QB1A *  DISPLAY DATABASE SUMMARY       *   
                *    GLOBAL CLAIMERS              *  
DSNT360I  -QB1A ***********************************               
DSNT362I  -QB1A    DATABASE = DSNDB01  STATUS = RW     
                   DBD LENGTH = 108200    
DSNT397I  -QB1A                                                        
NAME     TYPE   PART  STATUS             CONNID    CORRID       CLAIMINFO   
-------- ----   ----- -----------------  --------  -----------   ---------   
SPT01    TS     0001 RW,AREOR            BATCH     BINDROY       (CS,C)     
    -                   AGENT TOKEN 580                                 
    -                   MEMBER NAME                                     
SPT01    TS     0001 RW,AREOR            BATCH     BINDROY       (WR,C)     
    -                   AGENT TOKEN 580                                 
    -                   MEMBER NAME                                     
SPT01    TS                                                            
************** DISPLAY OF DATABASE DSNDB01  ENDED ************************ 
DSN9022I  -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION          
***

So the AGENT TOKEN is increasing for each bind which is OK, but that WR claim is not good!

Where’d he go? Where’d who go?

But the weirdest, most unbelievable thing, was a mass select I wrote against SYSIBM.SYSPACKAGE.

Here’s a cut of the output:

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+--------
2018-10-05-07.22.43.532257                     
---------+---------+---------+---------+--------
SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN)
FROM SYSIBM.SYSPACKAGE                         
WHERE COLLID = 'IQA_COLLECTION_610'            
  AND NAME   = 'PDB2D900'                      
WITH UR ;
---------+---------+---------+---------+--------
NAME                                           
---------+---------+---------+---------+--------
PDB2D900  19A8269C16C11114                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1         

Package is there, all is fine.

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+--------
2018-10-05-07.22.43.548474                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1         
SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN)
FROM SYSIBM.SYSPACKAGE                         
WHERE COLLID = 'IQA_COLLECTION_610'            
  AND NAME   = 'PDB2D900'                      
WITH UR ;                                      
---------+---------+---------+---------+--------
NAME                                           
---------+---------+---------+---------+--------
DSNE610I NUMBER OF ROWS DISPLAYED IS 0

Package has *gone* !!!

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+--------
2018-10-05-07.22.43.568380                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1         
SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN)
FROM SYSIBM.SYSPACKAGE                         
WHERE COLLID = 'IQA_COLLECTION_610'            
  AND NAME   = 'PDB2D900'                      
WITH UR ;                                       
---------+---------+---------+---------+--------
NAME                                           
---------+---------+---------+---------+--------
DSNE610I NUMBER OF ROWS DISPLAYED IS 0

Package is still *gone* !!!

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+--------
2018-10-05-07.22.43.609963                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1         
SELECT SUBSTR(NAME, 1, 8) AS NAME, HEX(CONTOKEN)
FROM SYSIBM.SYSPACKAGE                         
WHERE COLLID = 'IQA_COLLECTION_610'             
  AND NAME   = 'PDB2D900'                      
WITH UR ;                                      
---------+---------+---------+---------+--------
NAME                                           
---------+---------+---------+---------+--------
PDB2D900  199F572E0A8E6FF0                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Package is back from its little holiday by the sea and all is fine – well apart from the timeouts in production that is…

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+--------
2018-10-05-07.22.43.628259 
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

You have got to be kidding me!

So you have seen that my package was *gone* for about 0.06 seconds.

It would appear that BIND … EXPLAIN(ONLY) “moves” the current *live* running package “out of the way” then does the EXPLAIN(ONLY) processing and then “moves” the package back… this is, to put it mildly, NOT GOOD!!!

CHAPTER 2 : The magic formula

We can Fix it!

Luckily, the “fix” is pretty easy – Use a dummy Collection and all is fine!

Here’s how it looks with a different COLLECTION ID:

BIND PACKAGE(ROYBOYECTION_610) MEMBER(PDB2D900) QUALIFIER(IQA0610) -
     OWNER(BOXWELL) EXPLAIN(ONLY)

Then DISPLAYs:

DSNT360I  -QB1A *************************************                 
DSNT361I  -QB1A *    DISPLAY DATABASE SUMMARY       *
                *      GLOBAL LOCKS                 *
DSNT360I  -QB1A *************************************              
DSNT362I  -QB1A     DATABASE = DSNDB01  STATUS = RW  
DBD LENGTH = 108200 
DSNT397I  -QB1
NAME      TYPE  PART   STATUS      CONNID    CORRID     LOCKINFO 
--------  ----  -----  ----------  --------  ---------  --------
SPT01     TS    0001   R
SPT01     TS 
*************** DISPLAY OF DATABASE DSNDB01  ENDED **************
DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION         
***

No locks!

DSNT360I  -QB1A ****************************************
DSNT361I  -QB1A *     DISPLAY DATABASE SUMMARY         *  
                *       GLOBAL CLAIMERS                *
DSNT360I  -QB1A ****************************************
DSNT362I  -QB1A    DATABASE = DSNDB01  STATUS = RW
                    DBD LENGTH = 108200       
DSNT397I  -QB1A                            
NAME      TYPE PART   STATUS       CONNID   CORRID   CLAIMINFO   
-------- ---- -----  ---------    -------  -------  --------- 
SPT01    TS   0001   RW           BATCH    BINDROY   (CS,C) 
    -                AGENT TOKEN  700                        
    -                MEMBER NAME                                 
SPT01    TS  
************** DISPLAY OF DATABASE DSNDB01 ENDED ***********
DSN9022I -QB1A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION  
***

Just a CS claim!

Be careful out there!

And no “disappearing” rows in SYSPACKAGE!

Hallelujah!! I think this is interesting information for most Db2 Users out there. I know more and more shops are doing EXPLAIN(ONLY) in the, mistaken, belief that it cannot harm production…

What about the Directory?

They wouldn’t do that to the DSNDB01.SPT01 SYSIBM.SPTR table as well, would they? Well guess what? They did!

I wrote another little SQL against the SYSIBM.SPTR table for the same package and re-ran the BINDs:

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;                                          
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.51.928875                 
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT  SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION
      , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE  
      , HEX(A.SPTCONID)           AS CONTOKEN 
      , HEX(A.SPTRESV)            AS RESV 
      , HEX(A.SPTSEC)             AS SEC                   
      , HEX(A.SPTSEQ)             AS SEQ            
      , SUBSTR(A.SPTVER, 1, 8)    AS VERSION       
      , A.SPTLL                   AS LL  
FROM SYSIBM.SPTR A  
WHERE A.SPTLOCID = ''   
  AND A.SPTCOLID = 'IQA_COLLECTION_610'  
  AND A.SPTNAME  = 'PDB2D900'  
ORDER BY 1 , 2 , 3 , 4 , 5 , 6   
WITH UR ;                                                                                  
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION         PACKAGE  CONTOKEN         RESV SEC      SEQ VERSION      LL
---------+---------+---------+---------+---------+---------+---------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000001 0001 2.50 2147468080
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000B 0001 2.50 2147461080
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50-2147472632
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 41

Everything fine and dandy!

---------+---------+---------+---------+---------+---------+---------+--------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;       
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.51.944246                                         
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                        
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100      
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT  SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION       
      , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE           
      , HEX(A.SPTCONID)           AS CONTOKEN      
      , HEX(A.SPTRESV)            AS RESV      
      , HEX(A.SPTSEC)             AS SEC              
      , HEX(A.SPTSEQ)             AS SEQ          
      , SUBSTR(A.SPTVER, 1, 8)    AS VERSION         
      , A.SPTLL                   AS LL           
FROM SYSIBM.SPTR A            
WHERE A.SPTLOCID = ''                   
  AND A.SPTCOLID = 'IQA_COLLECTION_610'               
  AND A.SPTNAME  = 'PDB2D900'            
ORDER BY 1 , 2 , 3 , 4 , 5 , 6                      
WITH UR ;              
---------+---------+---------+---------+---------+---------+--------+--------+
COLLECTION         PACKAGE  CONTOKEN         RESV SEC      SEQ VERSION     LL
---------+---------+---------+---------+---------+---------+--------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50 2147472632
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 39  

My mind is going… I can feel it Dave…

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                               
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;              
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.51.972933  
DSNE610I NUMBER OF ROWS DISPLAYED IS 1             
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100      
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT  SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION                     
      , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE            
      , HEX(A.SPTCONID)           AS CONTOKEN             
      , HEX(A.SPTRESV)            AS RESV            
      , HEX(A.SPTSEC)             AS SEC           
      , HEX(A.SPTSEQ)             AS SEQ              
      , SUBSTR(A.SPTVER, 1, 8)    AS VERSION          
      , A.SPTLL                   AS LL             
FROM SYSIBM.SPTR A                 
WHERE A.SPTLOCID = ''            
  AND A.SPTCOLID = 'IQA_COLLECTION_610'         
  AND A.SPTNAME  = 'PDB2D900'            
ORDER BY 1 , 2 , 3 , 4 , 5 , 6      
WITH UR ;                           
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION         PACKAGE  CONTOKEN         RESV SEC      SEQ  VERSION     LL
---------+---------+---------+---------+---------+---------+---------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 7

Dave, what are you doing Dave?

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                  
---------+---------+---------+---------+---------+---------+---------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;        
---------+---------+---------+---------+---------+---------+---------+
2018-10-05-11.15.51.986345  
DSNE610I NUMBER OF ROWS DISPLAYED IS 1    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
---------+---------+---------+---------+---------+---------+---------+
SELECT  SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION            
      , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE             
      , HEX(A.SPTCONID)           AS CONTOKEN        
      , HEX(A.SPTRESV)            AS RESV                      
      , HEX(A.SPTSEC)             AS SEC          
      , HEX(A.SPTSEQ)             AS SEQ            
      , SUBSTR(A.SPTVER, 1, 8)    AS VERSION         
      , A.SPTLL                   AS LL             
FROM SYSIBM.SPTR A                           
WHERE A.SPTLOCID = ''                                 
  AND A.SPTCOLID = 'IQA_COLLECTION_610'   
  AND A.SPTNAME  = 'PDB2D900'        
ORDER BY 1 , 2 , 3 , 4 , 5 , 6     
WITH UR ;        
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION         PACKAGE  CONTOKEN         RESV SEC      SEQ  VERSION     LL
---------+---------+---------+---------+---------+---------+---------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Dave, stop. Stop, will you?

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ; 
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.51.997994 
DSNE610I NUMBER OF ROWS DISPLAYED IS 1 
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION 
, SUBSTR(A.SPTNAME, 1, 8) AS PACKAGE 
, HEX(A.SPTCONID) AS CONTOKEN 
, HEX(A.SPTRESV) AS RESV 
, HEX(A.SPTSEC) AS SEC 
, HEX(A.SPTSEQ) AS SEQ 
, SUBSTR(A.SPTVER, 1, 8) AS VERSION 
, A.SPTLL AS LL 
FROM SYSIBM.SPTR A 
WHERE A.SPTLOCID = '' 
AND A.SPTCOLID = 'IQA_COLLECTION_610' 
AND A.SPTNAME = 'PDB2D900' 
ORDER BY 1 , 2 , 3 , 4 , 5 , 6 
WITH UR ; 
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION PACKAGE CONTOKEN RESV SEC SEQ VERSION LL
---------+---------+---------+---------+---------+---------+---------+--------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0

Flat line or “Daisy, Daisy…”

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.52.010002
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION
     , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE
     , HEX(A.SPTCONID)           AS CONTOKEN
     , HEX(A.SPTRESV)            AS RESV
     , HEX(A.SPTSEC)             AS SEC
     , HEX(A.SPTSEQ)             AS SEQ
     , SUBSTR(A.SPTVER, 1, 8)    AS VERSION
     , A.SPTLL                   AS LL
FROM SYSIBM.SPTR A
WHERE A.SPTLOCID = ''
  AND A.SPTCOLID = 'IQA_COLLECTION_610'
  AND A.SPTNAME  = 'PDB2D900'
ORDER BY 1 , 2 , 3 , 4 , 5 , 6
WITH UR ;
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION     PACKAGE   CONTOKEN          RESV  SEC       SEQ   VERSION     LL
---------+---------+---------+---------+---------+---------+---------+--------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0

Still gone…

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100  
---------+---------+---------+---------+---------+---------+---------+--------+
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;     
---------+---------+---------+---------+---------+---------+---------+--------+

---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.52.159740       
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                    
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100         
---------+---------+---------+---------+---------+---------+---------+--------+
SELECT  SUBSTR(A.SPTCOLID, 1, 18) AS COLLECTION                   
      , SUBSTR(A.SPTNAME, 1, 8)   AS PACKAGE                      
      , HEX(A.SPTCONID)           AS CONTOKEN                    
      , HEX(A.SPTRESV)            AS RESV    
      , HEX(A.SPTSEC)             AS SEC    
      , HEX(A.SPTSEQ)             AS SEQ                     
      , SUBSTR(A.SPTVER, 1, 8)    AS VERSION             
      , A.SPTLL                   AS LL                
FROM SYSIBM.SPTR A                                    
WHERE A.SPTLOCID = ''                          
  AND A.SPTCOLID = 'IQA_COLLECTION_610'                         
  AND A.SPTNAME  = 'PDB2D900'                         
ORDER BY 1 , 2 , 3 , 4 , 5 , 6              
WITH UR ;                                             
---------+---------+---------+---------+---------+---------+---------+--------+
COLLECTION         PACKAGE  CONTOKEN         RESV SEC      SEQ VERSION     LL
---------+---------+---------+---------+---------+---------+---------+--------+
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000001 0001 2.50 2147468080
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000B 0001 2.50 2147461080
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000C 0001 2.50 2147455728
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000D 0001 2.50 2147445836
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000E 0001 2.50 2147458316
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000000F 0001 2.50 2147441860
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000010 0001 2.50 2147460296
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000011 0001 2.50 2147449216
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000012 0001 2.50 2147463256
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000013 0001 2.50 2147452416
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000014 0001 2.50 2147468032
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000015 0001 2.50 2147450804
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000016 0001 2.50 2147461896
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000017 0001 2.50 2147466700
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000018 0001 2.50 2147452760
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000019 0001 2.50 2147466152
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001A 0001 2.50 2147449352
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001B 0001 2.50 2147469548
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001C 0001 2.50 2147472392
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001D 0001 2.50 2147468924
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001E 0001 2.50 2147465912
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000001F 0001 2.50 2147469084
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000020 0001 2.50 2147471688
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000021 0001 2.50 2147481020
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000022 0001 2.50 2147472632
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000023 0001 2.50 2147471160
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000024 0001 2.50 2147473128
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000025 0001 2.50 2147466564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000026 0001 2.50 2147473972
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000027 0001 2.50 2147473692
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000028 0001 2.50 2147474100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000029 0001 2.50 2147474532
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002A 0001 2.50 2147477556
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002B 0001 2.50 2147477988
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002C 0001 2.50 2147464100
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002D 0001 2.50 2147474904
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002E 0001 2.50 2147466772
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 0000002F 0001 2.50 2147468564
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000030 0001 2.50 2147472764
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 00000031 0001 2.50 2147468820
IQA_COLLECTION_610 PDB2D900 199F572E0A8E6FF0 0000 FFFFFFFC 0001 2.50 2147481296
DSNE610I NUMBER OF ROWS DISPLAYED IS 41                        
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 ;                
---------+---------+---------+---------+---------+---------+---------+--------+
2018-10-05-11.15.52.278732         
DSNE610I NUMBER OF ROWS DISPLAYED IS 1      
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

It is back!! HAL 9000 is online and the AE35 unit will still fail in 72 hours…

 

Feel free to send me your comments and ask questions. Also feel free to ask IBM why it does this and please forgive my usage of 2001 quotes.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-11 db2 Numeric Primary Keys: Space, the final frontier?

What do Db2 numeric Primary Keys have to do with space management ?

SMALLINT  –  INTEGER  –  DECIMAL  –  BIGINT…

How to find out which sequences are nearing their physical limit ?

Recently I was at a customer site and was showing them our SpaceAssuranceExpert (SAX) software. It basically does all sorts of space management and checking, and has recently been extended to now also check for any numeric Primary Keys that are running out of space.

Traffic is needed

The simple way to show that the started task is up and running, and working as designed, is to generate some traffic. To do this, I normally just do a really awful Cartesian join insert into a dummy table.

First I create my target like so:

CREATE TABLE ROY.SYSTABLESPACE LIKE SYSIBM.SYSTABLESPACE ;

And then I simply do an INSERT from SELECT with no WHERE predicate in a batch SPUFI job:

INSERT INTO ROY.SYSTABLESPACE           
(SELECT A.* FROM SYSIBM.SYSTABLESPACE A 
                ,SYSIBM.SYSTABLESPACE B 
                ,SYSIBM.SYSTABLESPACE C 
                ,SYSIBM.SYSTABLESPACE D 
                ,SYSIBM.SYSTABLESPACE E 
                ,SYSIBM.SYSTABLESPACE F 
                ,SYSIBM.SYSTABLESPACE G 
                ,SYSIBM.SYSTABLESPACE H 
                ,SYSIBM.SYSTABLESPACE I 
                ,SYSIBM.SYSTABLESPACE J)
;

CCSID Problems

Now at this site, the first CREATE actually failed due to some odd sort of CCSID problem. The lead DBA said “No problem I have a little test table where we can do the same sort of thing.” This little table was used for the INSERT and we huddled over the keyboard waiting for the started task to start reporting on EXTENTS and other space data…

Nothing happened…

We waited and waited and waited, and still a big zilch!

Trust is everything

Now, I know my software and I know that this works fine, so I was a little perplexed to say the least!

I thought and thought and thought and then reviewed their SQL again:

INSERT INTO ROY.DSN_COLDIST_TABLE       
(SELECT A.* FROM ROY.DSN_COLDIST_TABLE A
                ,ROY.DSN_COLDIST_TABLE B
                ,ROY.DSN_COLDIST_TABLE C
                ,ROY.DSN_COLDIST_TABLE D
                ,ROY.DSN_COLDIST_TABLE E
                ,ROY.DSN_COLDIST_TABLE F
                ,ROY.DSN_COLDIST_TABLE G
                ,ROY.DSN_COLDIST_TABLE H
                ,ROY.DSN_COLDIST_TABLE I
                ,ROY.DSN_COLDIST_TABLE J
                ,ROY.DSN_COLDIST_TABLE K
                ,ROY.DSN_COLDIST_TABLE L
                ,ROY.DSN_COLDIST_TABLE M
                ,ROY.DSN_COLDIST_TABLE N
                ,ROY.DSN_COLDIST_TABLE O)
;

Ouch! That hurt!  😯

And then it hit me!

Look at the SQL and you can see that it is trying to INSERT into itself…

What does Db2 do in this situation?

It inserts into the work database until the INSERT is finished and then it actually moves all the data into the table…

*duh*

Waiting for Godot

If we had waited long enough we would have seen messages like:

O2RT-SU04-006I: Extent activity for
DC10.DSNDBD.DSNDB07.DSN32K00.I0001.A001
                Number of extents : 00032 - EXTS threshold is    1        
O2RT-SU04-011I: 08:11:35 - Datasets will be processed now                 
O2RT-SU04-006I: Extent activity for 
DC10.DSNDBD.DSNDB07.DSN32K00.I0001.A001
                Number of extents : 00034 - EXTS threshold is    1

With hindsight everything is clear!

But, of course, we didn’t see that until much later… then I noticed another little detail that I though was quite funny – the tablespace in question was in COPY Pending:

DSNT360I -DC10 *********************************** 
DSNT361I -DC10 * DISPLAY DATABASE SUMMARY        * 
               *       GLOBAL                    *
DSNT360I -DC10 *********************************** 
DSNT362I -DC10 DATABASE = DSN00201 STATUS = RW 
DBD LENGTH = 4028 
DSNT397I -DC10 
NAME     TYPE PART  STATUS       PHYERRLO PHYERRHI CATALOG  PIECE
-------- ---- ----- ------------ -------- -------- -------- -----
DSNRCOLD TS   0001  RW,COPY 
DSNRCOLD TS 
******* DISPLAY OF DATABASE DSN00201 ENDED **********************
DSN9022I -DC10 DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION 
***

It appears that Db2 does not actually even check if it can complete the INSERT at the beginning…

Get it right   😀

The moment we changed the INSERT to be on a different table (No work file usage) and it was not in COPY Pending status all worked as usual:

O2RT-SU04-006I: Extent activity for 
DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00002 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:40 - Datasets will be processed now                  
O2RT-SU04-006I: Extent activity for DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00003 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:41 - Datasets will be processed now                  
O2RT-SU04-006I: Extent activity for DC10.DSNDBD.DSN00200.SYSTABLE.I0001.A001
                Number of extents : 00004 - EXTS threshold is    1         
O2RT-SU04-011I: 08:38:42 - Datasets will be processed now

Much better data!

Why Primary Keys?

If you are wondering what numeric Primary Keys have to do with space management just think back to the good ol’ days before Sequences and Identity columns. You created keys with numeric types

  • SMALLINT,
  • INTEGER,
  • DECIMAL and
  • possibly even with the newer BIGINT.

These were, basically, “sequences”.

The challenge is to find out which of these is nearing its physical limit.
With SMALLINT it is not that big and DECIMAL can be very, very small indeed!

Catalog or Data?

Now this data is fetched from a couple of catalog tables and so is very dependent on the quality of your RUNSTATS.

so, in the next stage, it will be extended to actually read the User Data to see what the value currently really is.

Cool stuff, huh?

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-09 Db2 Console output : Message in a bottle

With these sample queries, find out how to review your Db2 messages from a high level overview down to the details.

You can review the Db2 Console output from different perspectives and simply bypass the boring „J“ ones for Full Log and Log Offload to leave behind the interesting ones.

Now you have two possibilities to get the Console Messages from Db2, just write your own IFI program or buy our newly updated software  (!)

Buy it or Write it

Buy it    😉

Indeed. One of our major products Db2 SQL WorkLoadExpert  just got an extra little addition: The Db2 Console message support.
Now I must admit I thought “Whoopy do!” that will be excellent data… but I have to say it is actually incredibly cool!

Write it   😯

You can, of course, write your own IFI program to get the Console Messages from Db2. Especially after you have been inspired by the following queries!

After a while I ran this query (remember this is all on my little testplex!) which worked on data processed from 2018-04-23-12.02.07.303151 up until today. So about five months.

SELECT COUNT(*)                      
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES
FOR FETCH ONLY                       
WITH UR                              
;                                    
---------+---------+---------+--------
---------+---------+---------+--------
      18684                          
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Db2 is a talkative little beast : get the Console Overview

That is quite a lot of messages… so now the next query GROUPing it into message ids to get the overview:

SELECT DB2_MESSAGE_ID , COUNT(*)    
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES
GROUP BY DB2_MESSAGE_ID             
ORDER BY 1                          
FOR FETCH ONLY                      
WITH UR                              
;

Very interesting selection of messages here (For details look further down!) even some of the “old school” E ending ones!

---------+---------+---------+-------
DB2_MESSAGE_ID                      
---------+---------+---------+-------
DSNB250E                175         
DSNB260I               1013         
DSNI005I                162         
DSNI006I                162         
DSNI031I                  7         
DSNI070I                 24         
DSNJ001I               4164      <- Current copy 1 is xx--xx   
DSNJ002I               4164      <- Full active log   
DSNJ003I               2082      <- Full archive log   
DSNJ004I                 15         
DSNJ005I                 15         
DSNJ031I               1421          
DSNJ103I                918         
DSNJ110E                349         
DSNJ111E                168         
DSNJ115I                641         
DSNJ125I                277         
DSNJ139I               2575      <- Log offload ended 
DSNL003I                 12         
DSNL004I                 12         
DSNL005I                 14         
DSNL006I                 11         
DSNL007I                  2         
DSNL008I                  2         
DSNL012I                  2         
DSNL030I                 12         
DSNL500I                  1         
DSNL510I                 31         
DSNL511I                  2         
DSNL512I                 12         
DSNL519I                 15          
DSNL523I                 10         
DSNP002I                  2         
DSNP007I                 14           
DSNP009I                  2           
DSNP010I                  2           
DSNP016I                  2           
DSNR035I                  1           
DSNT375I                  1           
DSNT376I                  5           
DSNT500I                  3           
DSNT501I                 33           
DSNT736I                  1           
DSNU241I                  1           
DSNU971I                  7           
DSNU973I                  7           
DSN3201I                133           
DSNE610I NUMBER OF ROWS DISPLAYED IS 47

Boring, boring, boring: get the „J “ message

Nice little break down huh? Now weeding out the “boring ones,” the J messages about:

  • Full Log and
  • Log Offload leaves:
SELECT COUNT(*)                         
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES   
WHERE NOT DB2_MESSAGE_ID IN ('DSNJ001I' 
                           , 'DSNJ002I' 
                           , 'DSNJ003I' 
                           , 'DSNJ139I')
FOR FETCH ONLY                          
WITH UR                                 
;                                       
---------+---------+---------+---------+
                                        
---------+---------+---------+---------+
       5699                             
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Get now the detailed Console view

And drilling on down:

SELECT DB2_MESSAGE_ID , COUNT(*)       
FROM IQA0610.WLX_DB2_CONSOLE_MESSAGES  
WHERE NOT DB2_MESSAGE_ID IN ('DSNJ001I'
                           , 'DSNJ002I'
                           , 'DSNJ003I'
                           , 'DSNJ139I')
GROUP BY DB2_MESSAGE_ID                
ORDER BY 1                             
FOR FETCH ONLY                          
WITH UR                                
;                                      

---------+---------+---------+---------+
DB2_MESSAGE_ID                         
---------+---------+---------+---------+
DSNB250E                175       <- Page range added to LPL             
DSNB260I               1013       <- Long running reader            
DSNI005I                162       <- LPL or GREP cannot be done     
DSNI006I                162            
DSNI031I                  7       <- Lock escalation              
DSNI070I                 24       <- FTB usage stats            
DSNJ004I                 15            
DSNJ005I                 15            
DSNJ031I               1421       <- 5000 log recs         
DSNJ103I                918       <- Log allocation error            
DSNJ110E                349       <- Last copy of log nnn% full            
DSNJ111E                168       <- Out of log space             
DSNJ115I                641       <- Log offload failed            
DSNJ125I                277       <- Error dumping BSDS            
DSNL003I                 12            
DSNL004I                 12            
DSNL005I                 14       <- DDF is stopping              
DSNL006I                 11            
DSNL007I                  2       <- DDF is abnormally ending           
DSNL008I                  2            
DSNL012I                  2            
DSNL030I                 12            
DSNL500I                  1            
DSNL510I                 31            
DSNL511I                  2            
DSNL512I                 12            
DSNL519I                 15            
DSNL523I                 10            
DSNP002I                  2       <- Define failed for dataset            
DSNP007I                 14       <- Extend failed             
DSNP009I                  2           
DSNP010I                  2           
DSNP016I                  2       <- Create failed               
DSNR035I                  1       <- Uncommitted UR after X checkpoints
DSNT375I                  1       <- Deadlock              
DSNT376I                  5       <- Timeout             
DSNT500I                  3            
DSNT501I                 33           
DSNT736I                  1       <- Asynch stop database command          
DSNU241I                  1           
DSNU971I                  7       <- Table is in check pending             
DSNU973I                  7       <- Table is no longer in check pending
DSN3201I                133           
DSNE610I NUMBER OF ROWS DISPLAYED IS 43

A nice little mix don’t you think?

Please remember that this is my sandbox so loads of errors are perfectly normal, and actually desired, for test purposes! Then of course even the “boring” J ones are actually quite handy as it tells you how often you get a Full Log and a Log Offload.

 

IFCID_TIMESTAMP             DB2_MESSAGE_ID    DB2_MESSAGE_TEXT              
---------+---------+---------+---------+---------+---------+---------+-----
2018-08-20-06.31.30.216749  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-06.31.30.221125  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 1 A
2018-08-20-06.31.30.225300  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-06.31.30.228824  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 2 A
2018-08-20-06.31.31.877862  DSNJ003I        -DC10 DSNJOFF3 FULL ARCHIVE LOG
2018-08-20-06.31.31.903315  DSNJ139I        -DC10 LOG OFFLOAD TASK ENDED.
2018-08-20-10.38.48.051394  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.38.48.056692  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 1 A
2018-08-20-10.38.48.060583  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.38.48.065864  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 2 A
2018-08-20-10.38.49.806783  DSNJ003I        -DC10 DSNJOFF3 FULL ARCHIVE LOG
2018-08-20-10.38.49.840157  DSNJ139I        -DC10 LOG OFFLOAD TASK ENDED.
2018-08-20-10.38.57.778588  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.38.57.782337  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 1 A
2018-08-20-10.38.57.786754  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.38.57.790629  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 2 A
2018-08-20-10.38.59.211112  DSNJ003I        -DC10 DSNJOFF3 FULL ARCHIVE LOG
2018-08-20-10.38.59.238033  DSNJ139I        -DC10 LOG OFFLOAD TASK ENDED.
2018-08-20-10.39.07.774517  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.39.07.779967  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 1 A
2018-08-20-10.39.07.783267  DSNJ002I        -DC10 FULL ACTIVE LOG DATA SET
2018-08-20-10.39.07.787409  DSNJ001I        -DC10 DSNJW307 CURRENT COPY 2 A
2018-08-20-10.39.09.283591  DSNJ003I        -DC10 DSNJOFF3 FULL ARCHIVE LOG
2018-08-20-10.39.09.303103  DSNJ139I        -DC10 LOG OFFLOAD TASK ENDED.

Here you can see the day started OK but then it got pretty busy around 10:38!

All-in-all a wonderful extra tool available for the DBA to use on a daily basis. Just check for the bad guy messages that I hope you never have!

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-08 SOUNDEX and other „cool“ features – Part six All new for Db2 12

Part six of my – everlasting – walk through of new Aggregate and Scalar functions.
Following with :  HASH & WRAP

Previous „SOUNDEX“ Newsletters

SCALARS

Now I move on to the new scalar functions, really only two new ones came with Db2 12, HASH and WRAP.

HASH it

Four new HASH Scalar functions are supplied. The first runs every time:

SELECT HEX(HASH_CRC32  ('ROY LIKES BEER A LOT!')) 
FROM SYSIBM.SYSDUMMYU;                            
---------+---------+---------+---------+---------+----------+---------+---------
4FA13CD
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Then I tried MD5

SELECT HEX(HASH_MD5    ('ROY LIKES BEER A LOT!'))
FROM SYSIBM.SYSDUMMYU;
---------+---------+---------+---------+---------+---------+---------+-----------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -20223, ERROR:  THE ENCRYPT_TDES OR DECRYPT FUNCTION
FAILED.  ENCRYPTION FACILITY NOT AVAILABLE 12 0
DSNT418I SQLSTATE   = 560BF SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXRBIN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 1032 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'00000408'  X'00000000'  X'00000000'  X'FFFFFFFF'
X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

Oops! What is that?

–  20223 THE OPERATION FAILED. ENCRYPTION FACILITY NOT AVAILABLE return-code, reason-code

Explanation: The encryption facility is not available, or not able to service the encryption or decryption request.

System action: The statement cannot be processed.

Programmer response: If the encryption facility is not installed, install it before you use the ENCRYPT_TDES, DECRYPT, HASH_CRC32, HASH_MD5, HASH_SHA1, HASH_SHA256 functions or data set encryption. If the encryption facility is installed, verify that it is working correctly. The returncode and reason-code might give further information about why this message was issued.

Digging deeper into the “ICSF and cryptographic coprocessor return and reason codes” docu

Reason codes for return code C (12)
Reason Code Hex (Decimal)Description
0 (0)ICSF is not available. One of the following situations is possible:

  • ICSF is not started

Yep – We have no crypto here in the labs, so no big surprise! But hey, at least the CRC32 worked!

Let’s WRAP it up

WRAP is useful for people like me that write software that other people buy! If you are using any of these:

  • CREATE FUNCTION (compiled SQL scalar)
  • CREATE FUNCTION (inlined SQL scalar)
  • CREATE PROCEDURE (SQL - native)
  • CREATE PROCEDURE (SQL table)
  • CREATE TRIGGER (basic)
  • CREATE TRIGGER (advanced)

Then you are aware that the code is in clear text and easy to read – Not good for my intellectual property rights! WRAP solves this dilemma by letting me obfuscate the code completely.

Here are two examples with the problems they can pose:

SELECT WRAP ('CREATE FUNCTION SALARY(WAGE DECFLOAT) RETURNS DECFLOAT RETURN WAGE * 40 * 52')
FROM SYSIBM.SYSDUMMY1
;
SELECT WRAP('
CREATE TRIGGER BOXWEL2.TESTTRIGH
   AFTER
   UPDATE OF COL1 ON BOXWEL2.TESTTRIG
   REFERENCING NEW AS NTABLE
   FOR EACH ROW
   MODE DB2SQL
   WHEN (NTABLE.COL1 = ''2'')
   UPDATE MVNXTEST.MVNXT80 A
   SET UT_STATUS = ''X''
')
FROM SYSIBM.SYSDUMMY1
;

The output looks like this:

CREATE FUNCTION SALARY(WAGE DECFLOAT) WRAPPED DSN12100 
ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZi2mdKZidaWmdaWmdaWmZG1mIaGicaGi6TRm8Eu4e30 AmlGUcEdToUnWtd2:hIfdEN_bBCpEmtl1lKH:UAzaqaa

One very long line that you must then “reverse” parse to get it back into, e.g. 72 byte chunks for SPUFI use.

The second example is even better, because of the whitespace being not encoded/encoded as well:

                                                            CREATE TRIGGER
BOXWEL2.TESTTRIGH                                           WRAPPED DSN1210
ablGWmdiWmtGTmdCTmtaTmtmUntqUmJeUmZm0otKXidaWmdaWmda1nJKGicaGicaGQ:TRO87Axb:VP8p_d8E9N887FL:EnRZ:8ltM:4sMnV5iyw3QGPUu0hDP4uwUK4lkDz0xvk3PU6lCMBGOCZar_sbJSQyaHYumRRSkATFXus8DesyjqaYmOLMD2HWdLt6GFDTxkNr4g8ht874tPZXN5ZIpJW4Xx15CI:VZ4f7ENGxV6_jxL4tVN0MFF2:tFR6EcD:g3nZurpPzOd2PYTuslWXKWXEWsWk2Q1KuT1VjPlY_MeNhCnpppEFEoQgp:dudFUcPhFyesEPIrPYTZxKAHlj1sDOKQTp1fYWoQ5nQBXOZGGtBreN2j9oSdSJNgf3roCpVJS8EFxdZ5DuoI_PDP5t7d1DPTL7Vtlp2EMsESTnl6s80KH4O13Wr72s4y56iQwBwB0KuOUYKxUZb:zNdchpguBUNZom2p2yyTpOXXkcHhKGnFnbBaUd2rlMbRmKZdgDiNwi7rEKHTLYulpLOa7kqrHKLymXZKurj64TGpF2IjXUmYkMbgbn4CFnOLk20rArSOIWIzbpajemWcmyHWmWdWfhyr8dJWZ8ghXJ5mF_nKr3ZyNUNw7Co9OqUxuEPuocFy4vUswVtOmonhr23d4VgBQz3Zf8nV2p5AVOEYLsJ7QDnNq7Bb132t3R7Rn603gF0PUi_PdCH6ef8Kmk4a8uWr2hkicHj8apO7hQf5w3Tc2tsvOCvOe5RaopCLKgg5BmnnYYzAl7lLiNGAvob1_Dd7PkoX51LDZ3QlBJUugQm5WsPJKKtwDjM81Wa

Notice that I had to double up the apostrophes due to this being passed as input to WRAP now – so you do have to watch out for that.

Remember too, that this is not encryption, it is just encoding and can be broken – It just makes it a tick harder to read your code, that is all. IBM writes:

Important
The encoding of the statement is meant to obfuscate the content and is not considered a form of strong encryption.

 

Well that wraps up this newsletter! Sorry….

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-07 – SOUNDEX and other „cool“ features – Part five All new for Db2 12

Part five of my – everlasting – walk through of new Aggregate and Scalar functions. This time I will start with the new AGGREGATE functions introduced in Db2 12.

LISTAGG

This is mainly of interest due to the fact that it was the very first Agile Function Level feature in Db2. You must be at FL501 to use this in SQL otherwise you get a

DSNT408I SQLCODE = -4700 ATTEMPT TO USE NEW FUNCTION BEFORE FUNCTION LEVEL IS ACTIVATED

Or a

DSNT408I SQLCODE = -4743, ERROR:  ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL

What this function does, is basically a horizontal recursive join which can be sorted in a different sequence. Here’s the SQL example:

SELECT WORKDEPT,                                       
LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME)
AS EMPLOYEES                                           
FROM SAMPLE.EMP                                        
GROUP BY WORKDEPT;

Which returns:

---------+---------+---------+---------+---------+---------+---------+-----+
WORKDEPT  EMPLOYEES                                                        
---------+---------+---------+---------+---------+---------+---------+-----+
A00       HAAS, LUCCHESI, O'CONNELL 
B01       THOMPSON 
C01       KWAN, NICHOLLS, QUINTANA    
D11       ADAMSON, BROWN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER,
          YOSHIMURA
D21       JEFFERSON, JOHNSON, MARINO, PEREZ, PULASKI, SMITH
E01       GEYER  
E11       HENDERSON, PARKER, SCHNEIDER, SETRIGHT, SMITH  
E21       GOUNOT, LEE, MEHTA, SPENSER 
DSNE610I NUMBER OF ROWS DISPLAYED IS 8

So you can see it adds the LASTNAME and a comma repeatedly for each WORKDEPT value.

Db2 Catalog Docu

This function is actually very handy for one of the things I create for our programmers here in the labs. It is always a good idea to “know the catalog” and so I created a little word document years ago with all the tablespaces, tables, indexes and index columns with sort order so that they did not have to look in three separate books to find the data. Here is the SQL for listing out the tables with indexes:

SELECT SUBSTR(A.DBNAME, 1, 8) AS DB      
      ,SUBSTR(A.NAME, 1, 8 ) AS TS       
      ,SUBSTR(B.NAME, 1, 18) AS TABLE    
      ,SUBSTR(C.NAME, 1, 8 ) AS INDEX    
      ,B.TYPE                            
      ,CASE C.UNIQUERULE                 
        WHEN 'D' THEN '-'                
        ELSE 'Y'                         
       END AS U                          
      ,D.COLSEQ                          
      ,SUBSTR(D.COLNAME, 1, 18) AS COL   
      ,CASE D.ORDERING                   
        WHEN ' ' THEN 'I'                
        WHEN 'A' THEN '-'                
        WHEN 'D' THEN 'D'                
        WHEN 'R' THEN 'R'                
       END AS ORDER                      
FROM SYSIBM.SYSTABLESPACE A              
    ,SYSIBM.SYSTABLES     B              
    ,SYSIBM.SYSINDEXES    C              
    ,SYSIBM.SYSKEYS       D              
WHERE A.DBNAME IN ('DSNDB01','DSNDB06')  
  AND A.DBNAME  = B.DBNAME               
  AND A.NAME    = B.TSNAME               
  AND B.CREATOR = 'SYSIBM'               
  AND B.NAME    = C.TBNAME               
  AND B.CREATOR = C.TBCREATOR            
  AND C.NAME    = D.IXNAME                
  AND C.CREATOR = D.IXCREATOR            
ORDER BY DB, TS , TABLE , INDEX, D.COLSEQ ;

It returns 855 rows of data on my test Db2 12 Fl501 system:

---------+-------+-------+---+-------+------+--------+------------------+--
DB        TS      TABLE         INDEX     TYPE  U  COLSEQ  COL       ORDER
---------+-------+-------+---+-------+------+--------+------------------+--
DSNDB01   DBD01   DBDR          DSNDB01X   T   Y      1   DBID         -
DSNDB01   DBD01   DBDR          DSNDB01X   T   Y      2   SECTION      -
DSNDB01   SCT02   SCTR          DSNSCT02   T   Y      1   SCTNAME      -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      1   SPTLOCID     -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      2   SPTCOLID     -
DSNDB01   SPT01   SPTR          DSNSPT01   T   Y      3   SPTNAME      -

And ending with

DSNDB06  SYSXML   SYSXMLRELS    DSNXRX02   T   -      1  XMLTBOWNER    -
DSNDB06  SYSXML   SYSXMLRELS    DSNXRX02   T   -      2  XMLTBNAME     -
DSNDB06  SYSXML   SYSXMLSTRINGS DSNXSX01   T   Y      1  STRINGID      -
DSNDB06  SYSXML   SYSXMLSTRINGS DSNXSX02   T   Y      1  STRING        -
DSNE610I NUMBER OF ROWS DISPLAYED IS 855

Then I used my amazing WORD skills to move all the COL columns after each other to get a DOC file that looks like this:

DSNDB0n.     SYSIBM.       SYSIBM.      U  INDEX FIELD
DBD01        DBDR          DSNDB01X     Y  DBID.SECTION
SCT02        SCTR          DSNSCT02     Y  SCTNAME
SPT01        SPTR          DSNSPT01     Y  SPTLOCID.SPTCOLID.SPTNAME.etc.

Which, of course, the programmers loved, as it enabled them to quickly find which columns, in which sort order, (I convert the ORDER D COL column to be in bold by the way), are available on the catalog and directory tables. I started this with DB2 V5…

Now with LISTAGG the query looks like:

SELECT SUBSTR(A.DBNAME, 1, 8)                                 
      ,SUBSTR(A.NAME, 1, 8 )                                  
      ,SUBSTR(B.NAME, 1, 18)                                  
      ,SUBSTR(C.NAME, 1, 8 )                                  
      ,B.TYPE AS T                                            
      ,CASE C.UNIQUERULE                                      
        WHEN 'D' THEN '-'                                     
        ELSE 'Y'                                              
       END AS U                                               
      ,LISTAGG( CHAR(SUBSTR(D.COLNAME, 1, 18) CONCAT           
                     CASE D.ORDERING                          
                     WHEN ' ' THEN 'I'                        
                     WHEN 'A' THEN '-'                        
                     WHEN 'D' THEN 'D'                        
                     WHEN 'R' THEN 'R'                        
                     END                                      
                    )                                         
               , ' ') WITHIN GROUP (ORDER BY D.COLSEQ ASC)    
FROM SYSIBM.SYSTABLESPACE A                                   
    ,SYSIBM.SYSTABLES     B                                   
    ,SYSIBM.SYSINDEXES    C                                   
    ,SYSIBM.SYSKEYS       D                                   
WHERE A.DBNAME IN ('DSNDB01','DSNDB06')                       
  AND A.DBNAME  = B.DBNAME                                    
  AND A.NAME    = B.TSNAME                                    
  AND B.CREATOR = 'SYSIBM'                                    
  AND B.NAME    = C.TBNAME                                    
  AND B.CREATOR = C.TBCREATOR                                 
  AND C.NAME    = D.IXNAME
  AND C.CREATOR = D.IXCREATOR                                 
GROUP BY A.DBNAME, A.NAME, B.NAME, C.NAME, B.TYPE, C.UNIQUERULE
;

And the output:

---------+---------+-------+-----------+---------------------------+---
                                            T  U 
---------+---------+-------+-----------+---------------------------+---
DSNDB01   DBD01   DBDR            DSNDB01X  T  Y  DBID     - SECTION  -     
DSNDB01   SCT02   SCTR            DSNSCT02  T  Y  SCTNAME  -  
DSNDB01   SPT01   SPTR            DSNSPT01  T  Y  SPTLOCID - SPTCOLID - SPTNAME

And ends

DSNDB06   SYSXML  SYSXMLRELS      DSNXRX02  T  -  XMLTBOWNER - XMLTBNAME
DSNDB06   SYSXML  SYSXMLSTRINGS   DSNXSX01  T  Y  STRINGID   -         
DSNDB06   SYSXML  SYSXMLSTRINGS   DSNXSX02  T  Y  STRING     -         
DSNE610I NUMBER OF ROWS DISPLAYED IS 287

Which is a lot less data for my poor little fingers to work with!

Of course it is not perfect… the really big GOTCHA, is ORDER BY is *not* allowed!

PERCENTILES

For the next couple of examples, I will be using the table SAMPLE.EMP that contains these rows and columns of interest:

---------+---------+---------+---------+---------+-----
EMPNO   WORKDEPT       SALARY        BONUS         COMM
---------+---------+---------+---------+---------+-----
000210  D11          18270.00       400.00      1462.00
000190  D11          20450.00       400.00      1636.00
000180  D11          21340.00       500.00      1707.00
000160  D11          22250.00       400.00      1780.00
000170  D11          24680.00       500.00      1974.00
000150  D11          25280.00       500.00      2022.00
000200  D11          27740.00       600.00      2217.00
000220  D11          29840.00       600.00      2387.00
000060  D11          32250.00       600.00      2580.
DSNE610I NUMBER OF ROWS DISPLAYED IS 9

PERCENTILE_CONT

Calculates the requested percentile as a continuous value. Use this if you want a calculated value based upon your input. E.g.:

SELECT PERCENTILE_CONT(0,50)
WITHIN GROUP (ORDER BY SALARY ASC )
FROM SAMPLE.EMP
WHERE WORKDEPT = 'D11'
;
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
+0.2468000000000000E+05                    (EMPLOYEE 150 row 5)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Here you can see that I require the 50th percentile, and as the data happens to have nine rows it would be the 5th row If you look at the data you will see that that is indeed the case.

Now, reversing the direction of the percentile, I want the 90th descending value. This row does not exist in the input, so the function computes the value that will probably best fit

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------
SELECT PERCENTILE_CONT(0,90)                              
       WITHIN GROUP (ORDER BY SALARY DESC)                
FROM SAMPLE.EMP                                           
WHERE WORKDEPT = 'D11'                                    
;                                                         
---------+---------+---------+---------+---------+---------
                                                           
---------+---------+---------+---------+---------+---------
+0.2001400000000000E+05     (value between first and second rows)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

PERCENTILE_DISC

Calculates the requested percentile as a discrete value. Use this if you do not want a calculated value based upon your input. E.g.:

SELECT PERCENTILE_DISC(0,50)             
       WITHIN GROUP (ORDER BY SALARY ASC )
FROM SAMPLE.EMP                          
WHERE WORKDEPT = 'D11'                   
;                                        
---------+---------+---------+---------+--
                                        
---------+---------+---------+---------+--
   24680.00                 (EMPLOYEE 150 row 5)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Here you can see that I still require the 50th percentile, and, as the data happens to still have nine rows, it would be the 5th row again.

Now, reversing the direction of the percentile, I want the 90th descending value. This row does not exist in the input so the function returns the nearest input value.

DSNE616I STATEMENT EXECUTION WAS SUCCESSFU
---------+---------+---------+---------+--
SELECT PERCENTILE_DISC(0,90)             
       WITHIN GROUP (ORDER BY SALARY DESC)
FROM SAMPLE.EMP                          
WHERE WORKDEPT = 'D11'                   
;                                        
---------+---------+---------+---------+--
                                       
---------+---------+---------+---------+--
   18270.00        (90th is not in the input data but this is the nearest)
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-06 – DST Db2 timestamp problems: I really hate Daylight Saving Time

How to avoid timestamp problems while going from winter to summer time in a Db2 for z/OS system ?

Is the CHAR or Timestamp use, the safest timestamp procedure ?

How do you fix it?

This year, as every year, the moment arrives for most of us when the clocks go forward and then in autumn back again. I really hate this, as I still have a bunch of clocks that do not automatically do it for me. My PC, phone, laptop, TV etc. all do it for me but the rest… anyway what has this got to do with Db2 I hear you all wonder? Well it really is quite a horrible little story coming up…

Same procedure as every year

At precisely 02:00 on the 25th of March a SET CLOCK console command was issued to change the UTC Offset to +2 thus leaping from 02:00 to 03:00 in the blink of an eye.

How long?

Now “how long” is the blink of an eye? For Db2 these days – too long!

Day of reckoning

At 2018-03-25-02.00.00.006999 a transaction was logged in the Audit system, in fact *lots* of transactions were in-flight at this time. Normally it is not a problem and, in fact, nothing happened until nearly three months later when someone found that there was possibly some data missing.

Alarm!

Alarm bells are ringing as these inventory checks cannot have missing data. The code is nowadays all JAVA and the developer in charge of the problem found out that the data was indeed missing!

Oh no it isn’t!

The DBA group were then involved, as it could be data corruption, and they looked and found the data – but it was not the same data as the developers had… then the penny dropped!

Clever old JAVA

In fact, the data the developer had was *exactly* one hour later than the data found by the DBA group. I mentioned earlier that the 25th March was the switch to summer time and, perhaps, the JAVA Driver is “helping” us, a bit too much help if you ask me!

Date Check

Here is a bit of SQL for you to recreate the problem and gaze in wonder at how cool/horrible (delete what is not applicable) JAVA really is.

CREATE TABLE BOXWELL.DAY_LIGHT                           
  (COL1 SMALLINT     NOT NULL                            
  ,COL2 TIMESTAMP    NOT NULL)                           
;                                                         
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (1 , '2018-03-25-01.59.59.999999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (2 , '2018-03-25-02.00.00.006999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (3 , '2018-03-25-03.00.00.000099');               
COMMIT ;                                                 

SELECT * FROM BOXWELL.DAY_LIGHT                           
ORDER BY 1                                               
;
---------+---------+---------+---------+---------+--------
  COL1   COL2                                         
---------+---------+---------+---------+---------+--------
     1   2018-03-25-01.59.59.999999                     
     2   2018-03-25-02.00.00.006999                      
     3   2018-03-25-03.00.00.000099                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 3

The output of SPUFI looks great! Timestamps are correct and all is fine.

It is a GUI world

Now do the select using a JAVA driver of your choice, here I am using DataStudio:

DST Db2 timestamp problems - Char - Daylight Saving Time

And then running it gives:
DST Db2 timestamp problems - Char- Daylight Saving Time

Spot the difference!

Isn’t that

great/terrifying (delete what is not applicable)

as the JAVA driver is “looking” at the timestamp data and seeing “oh oh! That timestamp is impossible! I know – I will add one hour to correct it!”

This scares me a little…actually quite a lot!

Docu – What Docu?

The only place I could find anything about this was in a chapter about not using 24 as midnight and the problem of using timestamps between October 5th 1582 and October 14th 1582:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0053436.html

If you read it you can find this one sentence:

If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value.

Which, of course, explains everything!

The quick fix…

There is no quick fix!

1 – The customer must either change all SQL to use the CHAR function – Not good!

Or

2 – Check all of their important timestamp columns for the range 02.00.00.000000 -> 02.59.59.999999 data and then update them with plus one hour – Not good!

Faster and Faster : the best fix ?

This problem will get worse the faster the machines get and so my idea to solve it next year is simply issue a

SET LOG SUSPEND

at one second before 02:00 which flushes the log, issues a system checkpoint (non data-sharing), updates the BSDS and basically pauses the system. Then do the SET CLOCK command and then do a

SET LOG RESUME

It all takes about three seconds and so should not cause any timeouts.

 

I really hope that, one day, we simply get rid of daylight saving time…

 

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

TTFN,

Roy Boxwell

Veröffentlicht am

2018-05 Audit 2.0 – GDPR Audit guide and checklist for Db2 z/OS

Or: How I Learned to Stop Worrying and Love the Auditor

A guideline in 5 big Steps to Audit for Personal Data Protection

First up, this is not another Audit review and health check sell! We, and I mean especially in the European Union (EU), are about to get the biggest update to “my data”, “my right to know” and “my right to forget (delete)” that has ever happened, when the General Data Protection Regulation (GDPR) Kicks off on May 25th, 2018.

Who cares?

Well, we all should really! Very soon hordes of people could well start demanding to know if you have any data about them and if so where it is and who you give it to. Personal Data will be big business!

Age of Consent

Just assuming all is ok is also no longer valid or lawful – any personal data that you keep must be kept with the *active* consent of the data owner… This is going to get awfully messy awfully quickly! Do not forget that the definition of personal data has also changed quite a bit e.g. IP Addresses. For the record here is the actual text in the GDPR regulations:

Art. 4 GDPR Definitions

For the purposes of this Regulation:

1.      ‘personal data’ means any information relating to an identified or identifiable natural person (‘data subject’); an identifiable natural person is one who can be identified, directly or indirectly, in particular by reference to an identifier such as a name, an identification number, location data, an online identifier or to one or more factors specific to the physical, physiological, genetic, mental, economic, cultural or social identity of that natural person;

And

Art. 9 GDPR Processing of special categories of personal data

1.      Processing of personal data revealing racial or ethnic origin, political opinions, religious or philosophical beliefs, or trade union membership, and the processing of genetic data, biometric data for the purpose of uniquely identifying a natural person, data concerning health or data concerning a natural person’s sex life or sexual orientation shall be prohibited.

Security by design and Security of processing

Very important are Articles 25 and 32:

Art. 25 GDPR Data protection by design and by default

1.       Taking into account the state of the art, the cost of implementation and the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for rights and freedoms of natural persons posed by the processing, the controller shall, both at the time of the determination of the means for processing and at the time of the processing itself, implement appropriate technical and organisational measures, such as pseudonymisation, which are designed to implement data-protection principles, such as data minimisation, in an effective manner and to integrate the necessary safeguards into the processing in order to meet the requirements of this Regulation and protect the rights of data subjects.

2.       The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual’s intervention to an indefinite number of natural persons.

Last part of Paragraph two is important here.

Art. 32 GDPR Security of processing

1.      Taking into account the state of the art, the costs of implementation and the nature, scope, context and purposes of processing as well as the risk of varying likelihood and severity for the rights and freedoms of natural persons, the controller and the processor shall implement appropriate technical and organisational measures to ensure a level of security appropriate to the risk, including inter alia as appropriate:

1.      the pseudonymisation and encryption of personal data;

2.      the ability to ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services;

3.      the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident;

4.      a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing.

2.      In assessing the appropriate level of security account shall be taken in particular of the risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, unauthorised disclosure of, or access to personal data transmitted, stored or otherwise processed.

Here paragraphs 1.2, 1.4 and 2 are the biggies!

A small or large fine for you today?

The fines are also amazingly high. First, for the “minor” problem of being over 72 hours late when data leaks have occurred (a breach), is 2% of global turnover or €10,000,000 – whichever is *higher* and, if you are really naughty, like disregarding basic data laws, moving data abroad or ignoring an individual’s rights then you get hit for 4% of global turnover or €20,000,000 – again whichever is *higher*

First up?

I, for one, do not want to be the first company that makes the headlines… How about you?

Who are you afraid of?

Now I know that most of the talk about GDPR is “right to data” for the general public but we all know, as IT specialists, that the people to be really afraid of are ex-employees who did not leave on happy terms. They know *exactly* where the knife could best be put… They know exactly where, and on which platforms, all of the data really is.

Protect yourself – Due diligence

What can you do? Well the first thing is to actually understand what GDPR means to you and your firm’s data. Then you must be able to prove to auditors that you tried your very best. We will all probably get hacked at some time, but if you tried your best it is enough. Using all of the data you gather in a post-mortem, or for forensics, is also a very good idea as can be seen in paragraphs 2.3 and 2.4 below:

Art. 83 GDPR General conditions for imposing administrative fines

1. Each supervisory authority shall ensure that the imposition of administrative fines pursuant to this Article in respect of infringements of this Regulation referred to in paragraphs 4, 5 and 6 shall in each individual case be effective, proportionate and dissuasive.

 2. Administrative fines shall, depending on the circumstances of each individual case, be imposed in addition to, or instead of, measures referred to in points (a) to (h) and (j) of Article 58(2). When deciding whether to impose an administrative fine and deciding on the amount of the administrative fine in each individual case due regard shall be given to the following:

1. the nature, gravity and duration of the infringement taking into account the nature scope or purpose of the processing concerned as well as the number of data subjects affected and the level of damage suffered by them;

2. the intentional or negligent character of the infringement;

3. any action taken by the controller or processor to mitigate the damage suffered by data subjects;

4. the degree of responsibility of the controller or processor taking into account technical and organisational measures implemented by them pursuant to Articles 25 and 32;

The list goes on after these of course…

Db2 Audit – a new way?

Most sites use SMF and do daily cuts to then offload to a repository system of some sort on some sort of hardware. The problem here is that the amount of SMF data you are generating can swamp you and this method is not nearly quick enough. Waiting a day is 24 hours of the 72 that are available to you…

Faster, Better, Securer?

Can you do it faster?  Can you do it yourself faster?  Can you do it better? Maybe…

 

If you can handle OPx and a bit of High Level Assembler then “Bob’s your uncle!” as long as Bob is your Auditor of course…There are more than enough examples in the web about how to write and do OPx reading (There are even vendors that are willing to sell you their software 🙂 )

A guideline in 5 big steps to Audit in a new way for Personal Data protection :

1 – To do list – IFI commands, IFCIDS & Audit Class

Create a program that can issue the required IFI commands to start and stop traces and to issue the READA and READS IFI calls that you wish to have. If you don’t know which Audit Class is which IFCID – Here’s my handy list:

Audit ClassIFCIDsWhatWhy
1140Authorization failuresPossible brute force password attack
2141GRANTs and REVOKEsPossible temporary raising of privilege
3142CREATE/ALTER/DROP of table with AUDIT attributeRemoving the AUDIT attribute is a big red flag
41431st Change of table with AUDIT attributeSensitive data updates
51441st Select of table with AUDIT attributeSensitive data usage
6145BIND/PREPARE using table with AUDIT attributePossible usage of sensitive data
755, 83, 87, 169, 319SET CURRENT SQLID, end of identify, end of signon CICS/IMSElevation of privilege
823, 24, 25, 219, 220Db2 UtilitiesUNLOADing data to where?
9146, 392For customer usen/a
10269, 270, 271*

 

271 is not started automatically with Class 10

Establish reuse trusted context, trusted context CREATE/ALTER and Column Mask/Row Permission CREATE/DROP/ALTERAnything with MASKs or ROW Permissions must be checked
11361Successful accessOnly makes sense if working with AUDIT POLICY SYSADMIN or DBADMIN

So, what is missing from this picture?

Well, what about the COMMANDs to actually see what people are issuing on the machine? So you must also add IFCIDs 90 and 91. Then you do not see any of the DDL so you must start IFCID 62 to get that as you do want to see what people are ALTERing, creating and dropping don’t you?

2 – All done in design?

Once you have a system that gathers all these you must buffer them in memory and then do one of two things:

1) Write them out to a file for post-processing and enrichment later
2) Directly write them to your SIEM system of choice

I am not a fan of number 2 because if the SIEM system cannot accept the data then your data is lost…

With No. 1 you can keep processing even if the SIEM system cannot ingest, for whatever reason, and as long as you trigger yourself every 5 – 10 minutes or when the OPx buffer is full then that is “real time” enough for me!

3 – Care for something else for the weekend?

Plus you can enrich the data with extra stuff that the naked IFCIDs do not have attached to them e.g. map dbid/psid to a real database and tablespace etc.

Store all of this data in a bunch of Db2 tables

then kick-off a Db2 to a LEEF re-formatter program that reads all the audit data that has been collected since last time

writes it all out.

4 – Code Page – don’t forget the Code Page

Then you must do an ICONV for code page conversion, swiftly followed by a gzip to get it down to size and then copy it down to a USS file ready for your SIEM system to ingest its prey.

5 – Done, Finished and Complete

To prove that this system is always available you should also schedule a simple

GRANT SELECT ON SYSIBM.SYSDUMMY1 TO PUBLIC;

Every morning at 06:00 which you should see in the output every day. Remember – Due Diligence!

 

So now you are done and finished and can let the SIEM system do the completion!

 

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

TTFN,

Roy Boxwell

PS: Please note that the bold of parts of the GDPR text is only from me!

Veröffentlicht am

2018-04 Negative DBIDs: Something new something old

Or: „Even Roy learns things from Listserv!“

While discussing negative DBIDs on Listserv the other day, another poster commented on my SQL Code snippet and said that it could well be better changing from LOCATE and HEX to using the ASCII Built-In Scalar function. But first, the old data must be able to understand the new data!

About IBM List Servers Discussions – Forums: http://www.vm.ibm.com/techinfo/listserv.html
Db2 z/OS „Listserv“:
IBM-MAIN newsgroup, mostly z/OS, OS/390, & MVS
DB2 Database discussion list

The DBID what?

The DBID, or DataBase object Identifier, is a SMALLINT number that is used internally for *all* objects in Db2. IBM has reserved the first 254 characters for their own use and we, as users, have the rest – up to 65,535. Now, if you are sharp you will have noticed that I wrote SMALLINT.A quick glance in the “limits of Db2” chapter of??? shows us:

Table 143. Numeric limits
Item Limit
Smallest SMALLINT value -32768
Largest  SMALLINT value  32767

The trick is…

So how do they do it? Well, they stuff a quart into a pint pot by going negative on us!

If a DBID goes over the value 32511 it then switches to negative numbers.

This saved IBM two bytes of storage… of course it is two bytes that is *everywhere*, so it actually saves quite a bit of space (pardon the pun!)

The IDs have it… PSID – OBID – ISOBID…

The PSID or Page Set Identifier is also involved here, as that is the “count” of things within that Database and it cannot exceed 32767. Unless, of course, it goes negative! Why here? Well think CLONE, any CLONE objects have the high-bit set, thus flipping them over to be the “mirror image” of the base object.

To round this brief discussion of the IDs, we also have the OBID or OBject IDentifier and the ISOBID or Index Space OBject IDentifier. All of these are stored as SMALLINTs everywhere in the Catalog and Directory and, naturally, the absolute physical never-going-to-change-maximum number of things in a Database is 65535.

LOCATE and HEX

I referred back to one of my older newsletters: “Discovering hidden recovery problems in the SYSLGRNX” from October 2016, where I presented an SQL to “extract” the DSNDB01 SYSLGRNX table into a “usable” format. The SQL started like this:

SELECT COALESCE(
  CASE WHEN
  ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
        > 32767 THEN
  (((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
       - 32768) * -1
  ELSE
  ((LOCATE(SUBSTR(HEX(A.LGRDBID), 1 , 1),'0123456789ABCDEF')-1)*4096)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 2 , 1),'0123456789ABCDEF')-1)*256)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 3 , 1),'0123456789ABCDEF')-1)*16)
+ ((LOCATE(SUBSTR(HEX(A.LGRDBID), 4 , 1),'0123456789ABCDEF')-1))
  END
      , 0 ) AS LGRDBID

I thought that was a neat way to change a CHAR(2) field into a real number and to also handle the problem of negative DBIDs when they “overflow” the SMALLINT boundary. (Remember, this happens if you have more than 32,511 Databases).

Problem one

First problem that jumped out at me, is the fact that the math is wrong! I should, of course,  have subtracted 32767 from the DBID.

Where does ASCII fit?

Well, it came out in Db2 V8 and got reviewed by me in a very old newsletter called: “SOUNDEX and other “cool” features – part one for DB2 V8” in April of 2012 !

Starting off with DB2 V8 :  ASCII function

V8 introduced us to the ASCII function:

SELECT ASCII('BOXWELL')             
FROM SYSIBM.SYSDUMMY1 ;             
         66

So it takes the leftmost character of any given string expression and returns the ASCII value, in this case 66 for B – I must admit that I have *not* yet found a use for this little gem…

Back to LISTSERV…

The poster wrote:

Roy,

Perhaps your query performs well enough already, so there is no need to change, and I have used LOCATE on a HEX digit myself for similar reasons on SYSPACKSTMT table. However I intend to change my query over to use ASCII function.

For anyone needing to convert a Char for BIT Data column, to an Integer in this case Char 2 bytes, based on the string actually containing a binary integer, you could look at the ASCII function to do it (more efficiently), I believe:

e.g. ASCII(SUBSTR(arg, 1, 1)) * 256 + ASCII(SUBSTR(arg, 2, 1))

I believe ASCII function accepts Char for Bit Data without auto conversion to another CCSID. ASCII and a lot of other fancy functions arrived in Db2 V8. Slightly less of a Pain in the A***. Because of the fancy functions, CTEs, and Recursive SQL, V8 was possibly my favourite release of DB2, when SQL could do absolutely anything, so I did not need REXX post processors any more. I forgot to mention Native SQL Stored Procs, and Non-Padded VARCHAR in indexes, DPSIs. Wow, what a release!

Of course this is amusing, since the function ASCII is somewhat misnamed. What we might use it for has nothing to do with ASCII encoding at all.

Aha! Moment

I thought wow! That could indeed be a lot faster and easier than my code so I changed my SQL to look like:

SELECT COALESCE( 
      CASE WHEN ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1)) > 32767 THEN 
              ((ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1))) - 32767) * -1 
           ELSE ASCII(SUBSTR(A.LGRDBID, 1, 1)) * 256 
              + ASCII(SUBSTR(A.LGRDBID, 2, 1)) 
      END , 0 ) AS LGRDBID 
       ,COALESCE( 
      CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN 
             (ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1))) - 32767 
           ELSE ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRPSID, 2, 1)) 
           END , 0 ) AS LGRPSID 
            ,TIMESTAMP( 
      CASE WHEN SUBSTR(A.LGRUCDT, 5 , 2 ) > '16' THEN '19' 
           ELSE '20' 
      END CONCAT 
           SUBSTR(A.LGRUCDT, 5 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCDT, 1 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCDT, 3 , 2 ) CONCAT '-' CONCAT 
           SUBSTR(A.LGRUCTM, 1 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 3 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 5 , 2 ) CONCAT '.' CONCAT 
           SUBSTR(A.LGRUCTM, 6 , 2 ) CONCAT '0000' 
                 ) AS LGRUCTS 
      ,A.LGRSRBA 
      ,A.LGRSPBA 
      ,A.LGRPART 
      ,A.LGRSLRSN 
      ,A.LGRELRSN 
      ,COALESCE( 
               ASCII(SUBSTR(A.LGRMEMB, 1, 1)) * 256 
             + ASCII(SUBSTR(A.LGRMEMB, 2, 1)) 
         , 0 ) AS LGRMEMB 
      ,CASE WHEN ASCII(SUBSTR(A.LGRPSID, 1, 1)) * 256 
            + ASCII(SUBSTR(A.LGRPSID, 2, 1)) > 32767 THEN 2
            ELSE 1 
       END       AS LGRINST 
FROM SYSIBM.SYSLGRNX A 
FOR FETCH ONLY 
WITH UR 
;

Whoosh!

Yes indeed, over 30% faster than my code…

Now remember, you can only really do this if your CHAR data has the FOR BIT DATA attribute so that no code page conversion is done. The LGRMEMB column is ok because it contains the hex values 00 (For non-datasharing) or 01 to 32 (Maximum number of members in a data-sharing group). These do not convert so they don’t cause a problem.

Thanks again to LISTSERV and the Posters who keep it alive and help old dogs (like me) learn new tricks.

I might even update my old ASCII review to mention that I do indeed have a use for it now!

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

TTFN,

Roy Boxwell

Veröffentlicht am

2018-03 RTS RUNSTATS

„Breaking the rules is often fun, although we cannot condone it. But breaking the rules of Real Time Statistics (RTS) in Db2 12 can really land you in hot water.

We provide two queries that may give you a Get Out of Jail Free Card – at least as far as RTS is concerned…”

In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.
In this short newsletter, I wish to briefly discuss a change in behavior of the Real Time Statistics (RTS) tables that I have now seen at customer sites. I am not too happy about it! Before we start, a quick resumé of the RTS is required.

The Arrival of RTS

The Real Time Statistics were basically created by Dr Jim Teng in Db2 V7 and have always obeyed a few quite simple rules.

Real Time Statistics (RTS) rules:

  1. Rule Number one
    If in doubt set to NULL.
    This might read a bit odd but the idea from Jim was that if any value is not 100% known then the column must be set to the NULL value.

  2. Rule Number two
    Externalize when asked, or by timer.

  3. Rule Number three
    No DEFINE NO data.

  4. Rule Number four
    Utilities will always update the RTS unless it cannot – see Rule Number one.

  5. Rule Number five
    SQL updates the counters unless they cannot – Think Mass Delete in a multi-table tablespace. Totalrows cannot be updated.

Rules are made to be broken

A couple of years into RTS usage and the clamor for changing various insert values got too big, so IBM enhanced the RTS so that on creation the REORGLASTTIME got set to the created timestamp and all the counter columns got set to zero instead of NULL. Now this was a good change as adding 1,000,000 to NULL you get NULL, whereas adding 1,000,000 to zero means you get 1,000,000 which is obviously better for working out whether or not you need a utility to run. The REORGLASTTIME was also accepted as when you create a nice empty object or you insert one row into an object, then by definition, it is in a perfectly reorganized state!

RTS rule one broken

Mass delete always caused problems, as mentioned earlier, so IBM then made a “half way” fix for the INDEX statistics in Db2 9 by zeroing the TOTALENTRIES when there is a Mass Delete as Db2 knows that the index is now empty.

(PM34730: RTS SYSINDEXSPACESTATS TOTALENTRIES INCORRECT AFTER MASS DELETE.)

Of course it did not update the TOTALROWS, as it didn’t “know” how many rows were mass deleted or truncated. This causes “drift” between the TS and IX statistics, but is only a minor annoyance.

Db2 12 breaks rule one

Now in Db2 12 Rule one has been broken again. Not that much of a break, but still not brilliant! What they have done, is change the STATSLASTTIME to now also be, nearly, the created TS – as if creating an object sets the runstats columns to valid data!

Naturally, the Db2 Catalog is still all -1’s. This makes generating utilities based on the RTS a little bit tricky, as you cannot trust the STATSLASTTIME to now ever actually be the time a RUNSTATS utility really ran – which was the *whole*, and only, purpose of this column! If only they had set the STATSLASTTIME to ‘0001-01-01-00.00.00.00.000000’ then all would be well…

Who woke the dogs up? (Or: Who let the dogs out? I.e. American jargon)

PI79234: SYSIBM.SYSTABLESPACESTATS.STATSINSERTS IS NOT UPDATED BY RTS EXTERNALIZATION SINCE OBJECT IS CREATED.
Is the APAR (PTF UI48494) that caused me all the trouble.

Where’s the beef?

Well, the problem is, if you are generating RUNSTATS based on the RTS – and who isn’t these days? – Then you are probably using this column. Now it *looks* like a RUNSTATS has been run at least once.

This is false and can lead to the not running of RUNSTATS when it most definitely should have been run. Check all your home-grown RUNSTATS checkers. Remember DSNACCOX is also a little bit broken, as it uses these predicates:

(STATSLASTTIME IS NULL OR
 STATSLASTTIME<LOADRLASTTIME OR
 STATSLASTTIME<REORGLASTTIME OR
 STATSLASTTIME< latest PROFILE_UPDATE for the table space1 OR

Make sure you do not use the STATSLASTTIME, but instead join across to the SYSINDEXPART or SYSTABLEPART and pull out the STATSTIME column. This data is still ok and not fake news!

The scope of the problem

To find out the scope of the problem at your site, or even if you have this problem, you can run these two queries which simply list out all the objects that have, according to the Db2 Catalog, never been RUNSTATted, but according to the RTS they have been RUNSTATted:

Query 1

-- SELECT LIST OF TABLESPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN.  
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE WORK DEFINED DATABASES AS NO RUNSTATS FOR THESE 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
-- DISTINCT IT FOR MULTI TABLE TABLESPACES 
SELECT DISTINCT A.DBNAME, A.NAME, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSTABLESPACESTATS A 
   ,SYSIBM.SYSTABLEPART        B 
   ,SYSIBM.SYSDATABASE         C 
   ,SYSIBM.SYSTABLES           D 
WHERE   B.STATSTIME       = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                           ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                          = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME        = 'DSNDB01' 
  AND A.DBNAME            = C.NAME 
  AND NOT C.TYPE          = 'W' 
  AND A.DBNAME            = B.DBNAME 
  AND A.NAME              = B.TSNAME 
  AND A.PARTITION         = B.PARTITION 
  AND A.DBNAME            = D.DBNAME 
  AND A.NAME              = D.TSNAME 
  AND D.TYPE            IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 
;

Query 2

-- SELECT LIST OF INDEXSPACES THAT ACCORDING TO DB2 CATALOG HAVE NOT 
-- BEEN RUNSTATTED BUT ACCORDING TO RTS HAVE BEEN. 
-- IGNORE DSNDB01 AS NO RUNSTATS FOR DIRECTORY OBJECTS 
-- IGNORE HASH INDEXES AS NO RUNSTATS ALLOWED 
-- ONLY CHECK FOR TABLE TYPES H,M,P,R AND T 
SELECT A.DBNAME, A.INDEXSPACE, A.PARTITION 
      ,A.REORGLASTTIME, A.STATSLASTTIME 
      ,B.STATSTIME, B.CREATEDTS 
FROM SYSIBM.SYSINDEXSPACESTATS A 
    ,SYSIBM.SYSINDEXPART       B 
    ,SYSIBM.SYSINDEXES         C 
    ,SYSIBM.SYSTABLES          D 
WHERE   B.STATSTIME       = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT COALESCE(A.STATSLASTTIME 
                          ,TIMESTAMP('0001-01-01-00.00.00.000000')) 
                          = TIMESTAMP('0001-01-01-00.00.00.000000') 
  AND NOT A.DBNAME        = 'DSNDB01' 
  AND A.CREATOR           = B.IXCREATOR 
  AND A.NAME              = B.IXNAME 
  AND A.PARTITION         = B.PARTITION 
  AND A.CREATOR           = C.CREATOR 
  AND A.NAME              = C.NAME 
  AND NOT C.HASH          = 'Y' 
  AND C.TBCREATOR         = D.CREATOR 
  AND C.TBNAME            = D.NAME 
  AND D.TYPE            IN ('H' , 'M' , 'P' , 'R' , 'T') 
ORDER BY 1 , 2 , 3 -
;

You can run these in any version of Db2 you like, but you will only get results from a Db2 12 system with this APAR applied and you have created an index or a tablespace. When I run them here on my Db2 10 and 11 systems I get zero rows back and on my test Db2 12 FL501 system I get 172 rows.

The good news is…

Our product for helping you all get “agile” in Db2 12, CDDC – ContinuousDelivery DeploymentCheck, detects and reports these data constellations as does our Statistics Health Check, of course.

I would be very interested to hear your opinions about this “little change in behavior”. Have you come across this at your site?

 

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

TTFN,

Roy Boxwell

Veröffentlicht am

2018-02 Db2 Catalog Statistics revisited

Db2 Optimizer & access path selection for Db2 11 & Db2 12 :

Db2 Catalog data | Problematic default values | Correlations in the Db2 Catalog 

It has been six years since the last update so I thought, after Terry Purcell’s excellent presentation in January 2018, it would be a good point in time to go over and rake the coals again—especially as a couple of things have changed for Db2 12!

Terry Purcell – Db2 12 for z/OS Optimizer and RUNSTATS improvements
Webcast replay          Abstract

Are you a RUNSTATS Master?

Every now and again, I hold a little presentation called “Are you a RUNSTATS Master?” Actually these days it’s called “Db2 z/OS Lies, Damn Lies, and Statistics…” where I describe in detail, what the Db2 Optimizer uses for access path selection in relation to the Db2 Catalog data.

Surprised? You will be!

Personally, I am always surprised at how often people say “just that data?” or “is that it?” (the various other reasons for access path selection like CP speed, RID Pool size, Sort Pool size, Max data caching size, and, of course, the 80 bufferpools are also mentioned, but these have nothing to do with RUNSTATS).

So generally the answer is “Yes.” However, the permutations and combinations make the devil in the detail – The Db2 Optimizer’s algorithms are top secret, but the input data it uses is fully described in the documentation.

Just the facts ma’am

What I want to do, is show :

  • the Db2 Catalog data that is used
  • the default values that can cause surprising things to happen
  • the problem of correlations in the Db2 Catalog

Which data are used by the Db2 Optimizer and which are updated by RUNSTATS?

Here is a complete list of the eleven tables used by the Db2 Optimizer:

  1. SYSIBM.SYSCOLDIST
  2. SYSIBM.SYSCOLSTATS *
  3. SYSIBM.SYSCOLUMNS
  4. SYSIBM.SYSINDEXES
  5. SYSIBM.SYSINDEXPART
  6. SYSIBM.SYSKEYTARGETS (same as SYSCOLUMNS)
  7. SYSIBM.SYSKEYTGTDIST (same as SYSCOLDIST)
  8. SYSIBM.SYSROUTINES
  9. SYSIBM.SYSTABLES
  10. SYSIBM.SYSTABLESPACE
  11. SYSIBM.SYSTABSTATS

* degree of parallelism only and, after APAR PK62804, also „sometimes“ used to bound filter factor estimates…

By the Columns

Now we can also list out all of the columns (obviously not including the key columns) which are used by the Db2 Optimizer:

SYSCOLDIST
CARDF, COLGROUPCOLNO, COLVALUE, FREQUENCYF, HIGHVALUE, LOWVALUE, NUMCOLUMNS, QUANTILENO, STATSTIME

SYSCOLSTATS
COLCARD, HIGHKEY, LOWKEY

SYSCOLUMNS
COLCARDF, HIGH2KEY, LOW2KEY

SYSINDEXES
CLUSTERING*, CLUSTERRATIO, CLUSTERRATIOF, DATAREPEATFACTORF, FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS

SYSINDEXPART
LIMITKEY*

SYSKEYTARGETS
CARDF, HIGH2KEY, LOW2KEY, STATS_FORMAT

SYSKEYTGTDIST
CARDF, KEYGROUPKEYNO, KEYVALUE, FREQUENCYF, HIGHVALUE, LOWVALUE, NUMKEYS, QUANTILENO, STATSTIME

SYSROUTINES
CARDINALITY*, INITIAL_INSTS*, INITIAL_IOS*, INSTS_PER_INVOC*, IOS_PER_INVOC*

SYSTABLES
CARDF, EDPROC*, NPAGES, NPAGESF, PCTROWCOMP

SYSTABLESPACE
NACTIVE, NACTIVEF

SYSTABSTATS
CARD, CARDF, NPAGES

Notes: * Columns are not updated by RUNSTATS and _ Columns are not updatable at all. The column STATSTIME is used only if there are duplicates in the SYSCOLDIST table, and then the Db2 Optimizer will use the “newer” data that was probably inserted by a User.

Know your defaults

Which default column values trigger the Db2 Optimizer to use its own internal default values?

SYSCOLUMNS

If COLCARDF                       = -1 then use 25

SYSINDEXES

If CLUSTERRATIOF            = 0 then use CLUSTERRATIO

If CLUSTERRATIO              = 0 then use 0.95 if the index is CLUSTERing = ‘Y’ otherwise 0.00

DATAREPEATFACTORF    = -1 then is ignored

If FIRSTKEYCARDF            = -1 then use 25

If FULLKEYCARDF             = -1 then use 25

If NLEAF                              = -1 then use 33 (Which is SYSTABLES.CARDF / 300)

If NLEVELS                         = -1 then use 2

SYSROUTINES

If CARDINALITY                  = -1 then use 10,000  

If INITIAL_INSTS                 = -1 then use 40,000

If INITIAL_IOS                     = -1 then use 0

If INSTS_PER_INVOC        = -1 then use 4,000

If IOS_PER_INVOC            = -1 then use 0

If IOS_PER_INVOC            = -1 then use 0

SYSTABLES

If CARDF                             = -1 then use 10,000

If NPAGESF                      <= 0 then use NPAGES

If NPAGES                          = -1 then use 501 (Which is CEILING (1 + SYSTABLES.CARDF / 20))

Here you must be very careful if using NPGTHRSH ZPARM as 501 is more than the default value in most shops. This is one of the little changes in Db2 12 where the value -1 is treated as -1 for the NPGTHRSH check.

SYSTABLESPACE

If NACTIVEF                     = 0 then use NACTIVE

If NACTIVE                       = 0 then use 501 (Which is CEILING (1 + SYSTABLES.CARDF / 20))

SYSTABSTATS

If CARDF                         = -1 then use 10,000

If NPAGES                       = -1 then use 501 (Which is CEILING (1 + SYSTABSTATS.CARDF / 20))

So now you can see that non-floating point “old” data, may still be used today and this may cause access path headaches!

Never ever say never

Now to top it all, the data in the SYSCOLDIST and SYSKEYTGTDIST never gets simply “deleted”.

Well, actually, in Db2 12 you can now do a RUNSTATS xxx.yyy RESET ACCESSPATH to delete all SYSCOLDIST and SYSKEYTGTDIST data and set all other relevant columns to their respective defaults, but you must time this RUNSTATS very wisely! If you run it and then forget to do a normal full RUNSTATS…

Oldie but a goldie

Once the data are inserted, they stay there, until they are overwritten by new data, a RUNSTATS RESET, or the object is dropped. This all leads to some very old data in these two tables that can and do cause the Db2 Optimizer a ton of grief! One of the first things I do is to simply select the MIN(STATSTIME) from these tables just to see how old the data really is. Do it yourself and be surprised! I have seen sites with eight-year old data in the SYSCOLDIST and that cannot be good!

Correlate the world

Now onto correlations… There are lots of little tricks that DBAs use to “massage” access path choice. One of these, is to just set NLEVELS to 15 for a given index. Then lots of queries simply refuse to touch it as it would appear to be HUGE. Now, just simply updating columns can cause the Db2 Optimizer, in the best case, to ignore the updates or, perhaps, makes things even worse! So here is a list of the correlations (In other words, if you change xxx remember to change yyy and zzz as well):

Relationships exist among certain columns of certain tables:

  •       Columns within SYSCOLUMNS
  •       Columns in the tables SYSCOLUMNS and SYSINDEXES
  •       Columns in the tables SYSCOLUMNS and SYSCOLDIST
  •       Columns in the tables SYSCOLUMNS, SYSCOLDIST, and SYSINDEXES

 If you plan to update some values, keep in mind the following correlations:

  •  COLCARDF and FIRSTKEYCARDF/FULLKEYCARDF (They must be equal for the 1st column and full, if a single column index)
  •  COLCARDF, LOW2KEY and HIGH2KEY. (For non-default COLCARDF LOW2KEY and HIGH2KEY key must be filled with data) and if the COLCARDF is 1 or 2 Db2 uses LOW2KEY and HIGH2KEY as domain statistics to generate frequencies.
  • CARDF in SYSCOLDIST.  CARDF is related to COLCARDF and FIRSTKEYCARDF and FULLKEYCARDF. It must be at a minimum
  • A value between FIRSTKEYCARDF and FULLKEYCARDF if the index contains the same set of columns
  • A value between MAX(colcardf of each col) and the product of all the columns COLCARDFs in the group
  • CARDF in SYSTABLES. CARDF must be equal or larger than any other cardinalities, such as COLCARDF, FIRSTKEYCARDF, FULLKEYCARDF, and CARDF in SYSCOLDIST
  • FREQUENCYF and COLCARDF or CARDF. The number of frequencies collected must be less than or equal to COLCARDF for the column or CARDF for the column group
  • FREQUENCYF. The sum of frequencies collected for a column or column group must be less than or equal to 1

New in Db2 11

In Db2 11, the table SYSSTATFEEDBACK was introduced giving us the first chance to see what the optimizer thinks is missing. This is truly awesome, as then we can tailor our RUNSTATS to generate exactly what the optimizer needs to really validate and generate a good, stable access path. Of course, you should be a little bit careful with this data as too much of a good thing can be bad for you!

New in Db2 12

(Not just the lowercase b!)

In Db2 12, the SYSSTATFEEDBACK was made even more interesting by now externalizing the required RUNSTATS options *directly* into the already existing RUNSTATS profile or, indeed, actually creating a RUNSTATS profile for you.

I think that is really dangerous, as then you could easily flood your system with bogus stats for end user QMF/SPUFI queries that were run “by accident,” or so called “boss queries” where someone with *no* idea of SQL clicks together a highly complex and badly written SQL before letting it run for a weekend. Naturally the SQL gets rewritten by a helpful ever present DBA, but the statistics recommendations have now landed in the profile and will be updated and kept from this point on.

My personal recommendation is to switch off this feature as it is sadly *on* by default!

Here are the ZPARMs of interest

ZPARM STATFDBK_SCOPE set to ALL by default.
ZPARM STATFDBK_PROFILE set to YES by default.
Plus, in table SYSIBM.SYSTABLES column STATS_FEEDBACK is set to Y by default.

Out-of-the box it starts automatically creating (for TYPE=’C‘ with NUMCOLS > 1 and TYPE=’F‘ or ‚H‘) profiles and updating existing profiles…Here you must manually check the size of your profiles every now and again just to make sure everything is ok!

One other new thing in Db2 12, is that XML columns can get statistics now to help XMLEXISTS get a better access path.

and finally

Do not forget that our little Freeware tool StatisticsHealthCheck will find all bad correlations, old data and badly updated data for you and it is FREE!

So I hope this little round-up of Db2 Catalog Statistics data was interesting, and, as usual, if you have any comments or questions, then please, feel free to mail me!

TTFN

Roy Boxwell