Autor: Roy Boxwell

Veröffentlicht am

2019-03 EXPLAIN data review

A quick history of EXPLAIN and a new standard Db2 EXPLAIN Query to bring out the best and most underused columns.

EXPLAIN has been with us from nearly the start of Db2 (DB2 V2R1 when the b was B!) and, over the years, the number of Explain tables has mushroomed up to 20 in Db2 12. Not *all* of this data is really useful but there are bits and pieces that are well worth adding to your standard repertoire of explain queries!

Table of contents
  1. PLAN_TABLE
  2. DSN_DETCOST_TABLE
  3. DSN_FILTER_TABLE
  4. DSN_PREDICAT_TABLE
  5. New Standard Explain SQL

PLAN_TABLE

This can be used to see in which release the PLAN_TABLE was created:

V2.1 – 25 column format
V2.2 – 28 column format
V2.3 – 30 column format
V3.1 – 34 column format
V4.1 – 43 column format
V5.1 – 46 column format
V6.1 – 49 column format
V7.1 – 51 column format
V8.1 – 58 column format
V9.1 – 59 column format
10 – 64 column format ending with MERGN
11 – 66 Column format ending with EXPANSION_REASON
12 – 67 column format ending with PER_STMT_ID

What could be interesting here?? Well what about:

PREFETCH                           D, S, L, U, or Blank?

  • D for Dynamic
  • S for pure Sequential
  • L for through a page list
  • U for unsorted RID list
  • Blank for Unknown or no prefetch

COLUMN_FN_EVAL R, S, X, Y, or Blank?

  • R for while data is read
  • S for while performing a sort
  • X for while data is read but using OFFSET
  • Y for while performing a sort but using OFFSET
  • Blank for after data retrieval and any sort

PAGE_RANGE Y or Blank.

  • Y for yes the table qualifies for page range screening
    so that only the needed partitions are scanned
  • Blank for no

PRIMARY_ACCESSTYPE D, P, S, T, Blank. Is direct row access attempted first:

  • D it tries to use direct row access with a rowid column. If it cannot do this it uses the access path that is described in the ACCESSTYPE column
  • P it uses a DPSI and a part-level operation to access the data
  • S it uses sparse index access for a sideways table reference
  • T the base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed
  • Blank it does not try to use direct row access by using a rowid column or sparse index access for a work file.

DSN_DETCOST_TABLE

There are a ton of interesting columns here but most are “IBM internal only”, however, these are available for our enjoyment:

ONECOMPROWSThe number of rows qualified after applying
local predicates.
IMLEAFThe number of index leaf pages scanned
by Data Manager.
IMFF  The filter factor of matching predicates only.
IMFFADJ  The filter factor of matching and screening
DMROWS  The number of data manager rows returned
(after all stage 1 predicates are applied).
RDSROWThe number of data manager rows returned
(after all stage 1 predicates are applied).
IXSCAN_SKIP_DUPSWhether duplicate index key values
are skipped during an index scan.
  • Y Duplicate key values are skipped
  • N Duplicate key values are not skipped
IXCAN_SKIP_SCREENWhether key ranges that are disqualified
by index screening predicates are
skipped during an index scan.
  • Y Disqualified key ranges are skipped
  • N Key ranges are not skipped
EARLY_OUTWhether fetching from the table stops
after the first qualified row.
  • Y Internal fetching stops after the first
    qualified row
  • N Internal fetching continues after the first
    qualified row
BLOCK_FETCHY  or N Was block fetch used?

DSN_FILTER_TABLE

Sometimes it is really interesting to see when the predicate is applied and whether or not it could be pushed down.

STAGEThe stage that the predicate was evaluated.
MATCHING, SCREENING, PAGERANGE,
STAGE1, or STAGE2.
PUSHDOWNWhether the predicate was pushed down.
  • I for the Index Manager evaluates it
  • D for the Data Manager evaluates it
  • Blank means no push down was used

DSN_PREDICAT_TABLE

Here lives the really good stuff that most people do not use! The Bubble Up is normally an SQL coding error and the T is “forgetfulness”…

ADDED_PREDIf this column is non-blank it means that Db2 has
rewritten the query to some extent.
This is not good as it adds CPU to the process.
In my opinion any “added predicate“
should already be coded correctly in the SQL!
  • Blank Db2 did not add the predicate
  • B for bubble up
  • C for correlation
  • J for join
  • K for LIKE for expression-based index
  • L for localization
  • P for push down
  • R for page range
  • S for simplification
  • T for transitive closure

CLAUSE
Is this a SELECT, HAVING, ON, or WHERE clause?
ORIGIN  Where did it come from?
  • C for a column mask
  • R for a Row Permission
  • U specified by User
  • Blank generated by Db2

New Standard Explain SQL

Just adding these to your standard Explain query like this:

SET CURRENT SQLID = 'BOXWELL' ;                                   
 SELECT SUBSTR(P.PROGNAME, 1 , 8 )        AS PROGNAME              
       ,SUBSTR(DIGITS(P.QUERYNO), 6, 5)   AS LINE                  
       ,SUBSTR(DIGITS(P.QBLOCKNO), 4, 2)  AS QNO                   
       ,SUBSTR(DIGITS(P.PLANNO), 4, 2)    AS PNO                   
       ,SUBSTR(DIGITS(P.MIXOPSEQ), 4, 2)  AS SQ                    
       ,SUBSTR(DIGITS(P.METHOD), 5, 1)    AS M                     
       ,SUBSTR(P.TNAME, 1, 18)            AS TABLE_NAME            
       ,P.ACCESSTYPE                      AS A                     
       ,P.PRIMARY_ACCESSTYPE              AS PA                    
       ,SUBSTR(DIGITS(P.MATCHCOLS), 4, 2) AS CS                    
       ,SUBSTR(P.ACCESSNAME, 1, 12)       AS INDEX                 
       ,P.INDEXONLY                       AS IO                    
       ,SUBSTR(CASE SORTN_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOG                  
       ,SUBSTR(CASE SORTC_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOGC                 
       ,P.PREFETCH                        AS P                     
       ,P.COLUMN_FN_EVAL                  AS CE                    
       ,P.PAGE_RANGE                      AS PR                    
       ,P.QBLOCK_TYPE                     AS TYPE                  
       ,P.MERGE_JOIN_COLS                 AS MJC                   
       ,S.PROCMS                          AS MS                    
       ,S.PROCSU                          AS SU                    
       ,D.EARLY_OUT                       AS EO                    
       ,D.BLOCK_FETCH                     AS BF                    
       ,F.ORDERNO                         AS ON                    
       ,F.PREDNO                          AS PN                    
       ,F.STAGE                           AS STAGE                 
       ,F.PUSHDOWN                        AS PD                    
       ,R.TYPE                            AS TYPE                  
       ,R.ADDED_PRED                      AS AP                    
       ,R.CLAUSE                          AS CLAUSE                
       ,R.ORIGIN                          AS OR                    
       ,R.REDUNDANT_PRED                  AS RP                    
       ,R.TEXT                            AS TRANSFORMED_PREDICATE 
 FROM PLAN_TABLE         P                                         
 FULL OUTER JOIN                                                   
      DSN_STATEMNT_TABLE S               
 ON    P.QUERYNO      = S.QUERYNO        
   AND P.APPLNAME     = S.APPLNAME       
   AND P.PROGNAME     = S.PROGNAME       
   AND P.COLLID       = S.COLLID         
   AND P.GROUP_MEMBER = S.GROUP_MEMBER   
   AND P.SECTNOI      = S.SECTNOI        
   AND P.VERSION      = S.VERSION        
   AND P.EXPLAIN_TIME = S.EXPLAIN_TIME   
 FULL OUTER JOIN                         
      DSN_DETCOST_TABLE  D               
 ON    P.QUERYNO      = D.QUERYNO        
   AND P.APPLNAME     = D.APPLNAME       
   AND P.PROGNAME     = D.PROGNAME       
   AND P.COLLID       = D.COLLID         
   AND P.GROUP_MEMBER = D.GROUP_MEMBER   
   AND P.SECTNOI      = D.SECTNOI        
   AND P.VERSION      = D.VERSION        
   AND P.EXPLAIN_TIME = D.EXPLAIN_TIME   
   AND P.QBLOCKNO     = D.QBLOCKNO       
   AND P.PLANNO       = D.PLANNO         
 FULL OUTER JOIN                         
      DSN_FILTER_TABLE   F               
 ON    P.QUERYNO      = F.QUERYNO        
   AND P.APPLNAME     = F.APPLNAME       
   AND P.PROGNAME     = F.PROGNAME       
   AND P.COLLID       = F.COLLID         
   AND P.GROUP_MEMBER = F.GROUP_MEMBER   
   AND P.SECTNOI      = F.SECTNOI        
   AND P.VERSION      = F.VERSION        
   AND P.EXPLAIN_TIME = F.EXPLAIN_TIME   
   AND P.QBLOCKNO     = F.QBLOCKNO       
   AND P.PLANNO       = F.PLANNO         
 FULL OUTER JOIN                         
      DSN_PREDICAT_TABLE R               
 ON    F.QUERYNO      = R.QUERYNO        
   AND F.APPLNAME     = R.APPLNAME       
   AND F.PROGNAME     = R.PROGNAME       
   AND F.COLLID       = R.COLLID         
   AND F.GROUP_MEMBER = R.GROUP_MEMBER   
   AND F.SECTNOI      = R.SECTNOI        
   AND F.VERSION      = R.VERSION        
   AND F.EXPLAIN_TIME = R.EXPLAIN_TIME   
   AND F.QBLOCKNO     = R.QBLOCKNO       
   AND F.PREDNO       = R.PREDNO         
 WHERE 1 = 1                             
   AND P.QUERYNO  IN (1 , 2 )                               
 ORDER BY 1 , 2 , 3 , 4 , 5 , 24 , 25                       
 ;

Here I limit it to just the QUERYNO 1 and 2 as these were the numbers used for the EXPLAIN command:

EXPLAIN ALL SET QUERYNO = 1 FOR              
 SELECT INSTANCE, CLONE                       
 FROM SYSIBM.SYSTABLESPACE A                  
 WHERE ( SELECT B.TSNAME                      
         FROM SYSIBM.SYSTABLES  B             
             ,SYSIBM.SYSINDEXES C             
         WHERE C.CREATOR   = ?                
           AND C.NAME      = ?                
           AND C.TBCREATOR = B.CREATOR        
           AND C.TBNAME    = B.NAME ) = A.NAME
 AND A.DBNAME = ?                             
 ;                                            
 EXPLAIN ALL SET QUERYNO = 2 FOR              
 SELECT A.INSTANCE, A.CLONE                   
 FROM SYSIBM.SYSTABLESPACE A                  
     ,SYSIBM.SYSTABLES     B                  
     ,SYSIBM.SYSINDEXES    C                  
 WHERE C.CREATOR   = ?                        
   AND C.NAME      = ?                        
   AND C.TBCREATOR = B.CREATOR                
   AND C.TBNAME    = B.NAME                   
   AND A.DBNAME    = ?                        
   AND A.NAME      = B.TSNAME                 
   AND A.DBNAME    = B.DBNAME                 
 WITH UR                                      
 ;                         
--------+--------+------+--------+---------+---------+--------+------+---
PROGNAME LINE QNO PNO SQ M  TABLE_NAME    A PA CS INDEX    IO UJOG  UJOGC
--------+--------+------+--------+---------+---------+--------+------+---
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNE610I NUMBER OF ROWS DISPLAYED IS 13 
--+---------+---------+---------+---------+---------+---------+--------
 P  CE  PR  TYPE      MJC            MS           SU  EO  BF        ON
--+---------+---------+--------+---------+---------+---------+---------
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          3
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
---------+---------+---------+---------+---------+---------+---------+-----
PN  STAGE  PD TYPE  AP  CLAUSE    OR  RP  TRANSFORMED_PREDICATE 
---------+---------+---------+---------+---------+---------+---------+-----
3  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR) 
2  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"=(SELECT "B"."TSNAME"
5  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
6  MATCHING   EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
7  MATCHING   EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"
8  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
2  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
3  MATCH      EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
4  MATCH      EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"  
5  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
9  STAGE1     EQUAL  T  WHERE         N   "B"."DBNAME"=(EXPR)            
6  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR)            
7  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"="B"."TSNAME"

It shows you a lot more data than the normal bare bones Explain PLAN_TABLE data especially here the TYPE, STAGE and ADDED_PREDICATE (AP Column).

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2019-02 Global Temporary Confusion

Global Temporary Table usage in Db2 z/OS

Learn, through examples, why CGTT (CREATEd Global Temporary Tables) are possibly better than DGTT (DECLAREd Global Temporary Tables) and how to use them to speed up performance.

When Global Temporary Tables were first introduced (DB2 V5.1) it was all pretty clear! You used normal DDL to CREATE a GLOBAL TEMPORARY Table that existed in the Db2 Catalog, but every process got “their” own copy. There were limitations like No UPDATE, No Single DELETE, no Default Values and ROLLBACK / COMMIT and data reuse was not simple but it all sort of worked…

Global – but not as you know it!

Then along came the brand new Global Temporary Table (DB2 V6.1) which, just to make matters clearer, they prefixed with DECLARE so that it was 100% obvious that it had *nothing* to do with the “other” temporary table… I tell ya, if I ever meet the developer who came up with these names in a dark alley one night…. But I digress… So with the new DECLARE version you suddenly *could* do Update and Delete and it came with an ON COMMIT clause to make it simpler to handle data with COMMIT – Hoorah!

Global Temporary Table in Db2 12 – All new and improved

As of Db2 12, the full list of things you can do with a CREATE GLOBAL TEMPORARY TABLE (from now on simply CGTT) as opposed to a DECLARE GLOBAL TEMPORARY TABLE (from now on simply DGTT) is:

 What            CGTT           DGTT
 MERGE           No             Yes
 UPDATE          No             Yes
 Single DELETE   No             Yes
 DEFAULTs        No             Yes
 ROLLBACK        Deletes all    Yes (Plus NOT LOGGED ON ROLLBACK)
 COMMIT          Deletes all    Depends on ON COMMIT
                 (unless WITH HOLD)
 Indexes?        No             Yes
 WITH RETURN     Yes            Yes <- This is very good news
 TO CLIENT

CGTT why?

So, looking at the list, you have to wonder – “Why would anyone be using CGTTs?”
Well the reason is “Performance!” –
DGTTs might do all the tricks but they also run like a dog!

Belief is ok, test is trust!

I wrote a couple of small COBOL programs that did the same thing: one using a DGTT and one using a CGTT. All the programs did, was call a section 1000 times that DECLARED the table (or obviously not!) and then called another section that inserted 10 rows and opened a cursor to then fetch these rows right back with an ORDER BY. I chose this test as most DGTT/CGTT usage is low-volume but high called rate (Think stored procedures here!) so I wanted to see what sort of overhead 1000 DGTTs caused.

No EDMPOOL here!

Monitoring is not easy as DGTT usage is not in the EDMPOOL, so I did it the “good old way” by running my tests when I was alone on the system and five times each. I was a bit shocked by the results…

What               CGTT                   DGTT
DBM1 EXCPs          0                      8,817
MSTR EXCPs          0                     59,653
Db2 CPU             0.140 secs             3.546 secs
Program cpu         4.506 secs           101.124 secs
Program elapsed     4.958 secs           118.730 secs

I then changed the DGTT to also be NOT LOGGED

What                   DGTT 
DBM1 EXCPs               66
MSTR EXCPs               47
Db2 CPU               2.446 secs
Program cpu         100.818 secs
Program elapsed     116.426 secs

Not that much better! But remember, I did not do any DELETE or UPDATE processing so your performance data may be better than mine.

Impressive!

Now the reason for all of the I/O and CPU is
all of the internals that Db2 must do for the DGTT, remember the CGTT has *already* been declared and exists in the catalog. The DGTT naturally not…
This can, as be seen, add a lot of overhead!

ROT still correct

Now the good old Rule of Thumb still holds true:

A CGTT is great for doing zero updates and sequential access!

Db2 10 improvement

When a cursor is declared with the attribute RETURN TO CLIENT then this cursor is available from the originating calling client even if the current stored procedure is down line. This is very neat as the cursor is “invisible” to all the intermediate procedures.

Death by DGTT

The problem in the past was the “hand shaking” of the final stored procedure result set. It had to be read out and put into another DGTT and then this handling was repeated all the way back up the calling chain. Now, if you have a “final cursor”, you can simply declare it as RETURN TO CLIENT – This saves tons of CPU and elapsed time!

Final tip!

Remember if you use STATIC SCROLLABLE cursors then they *must* use DGTTs in the background to actually work!

Even more work…

Then a colleague who proof-reads all my newsletters, a nasty job but someone must do it, asked me:

“I wonder what the numbers would be if you declared the GTT only once, then INSERTED/SELECTED/DELETED a bunch of rows 1000 times? Because that’s how I’m using it …“

So then I changed the programs to only DECLARE once, insert a 1000 rows, Select a 1000 rows and then do a MASS DELETE of the data.

What             CGTT           DGTT            DGTT NOT LOGGED
DBM1 EXCPs       7,572          7,720           7,769
MSTR EXCPs       0                672             238
Db2 CPU          0.220 secs     0.310 secs      0.410 secs
Program cpu      0.346 secs     0.482 secs      0.468 secs
Program elapsed  0.704 secs     0.836 secs      0.818 secs

Much closer, in terms of performance, but still the CGTT is quicker – Time to check your usage of these Temporary Tables if you ask me!

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2019-01: Regular Expressions in Db2 SQL – Regulate this!

Impress your JAVA colleagues with Regular Expressions in Db2 SQL – Become a regex specialist with these samples (regex usage in the TSO Editor & regex as a predicate).

The references to blogs focusing on this topic will help round out your knowledge all about „Regular Expressions in Db2 SQL“
(advanced pattern matching, regex testing and REGEXP_LIKE usage).

I have never really been a fan of regular expressions (regex) as, by default, I work on Mainframes and regex was always “just for the small boxes that you can lift”, however, since Db2 12 I have started to play around with them in various abstruse places.

TSO? You gotta be kidding?

Yep, even in the TSO editor you can use regex. This example will find all occurrences of data that starts with an M, contains DB and ends with at least two numerics. After the pipe (an OR in regex) it does the same but for strings that start with an O followed by 2DB and ends with two numerics:

VIEW       SE.MDB2VNEX.TCOBOL(#GOALL) - 01.00              Columns 00001 00072
Command ===> F R'^M[DB].*[0-9]{2}|^O[2DB].*[0-9]{2}' all  Scroll===> 
****** ***************************** Top of Data ********************
001600 MDB2DBRI  EXPD=N                                             
001700 MDB2DBRT  EXPD=N                                              
001800 MDB2DBSC  EXPD=N                                              
001900 MDB2DBSF  EXPD=N                                              
002000 MDB2DBSG  EXPD=N                                              
002100 MDB2DBTA  EXPD=N                                              
002200 MDB2DBTP  EXPD=N                                              
002300 MDB2DBTS  EXPD=N                                              
002400 MDB2DB01  EXPD=N                                              
002500 MDB2DB02  EXPD=N                                              
002600 MDB2DB03  EXPD=N                                              
002700 MDB2DB04  EXPD=N

Note that changing the R to be RC then makes the regex case sensitive.

Using “x all” first, so you can see what it finds…

VIEW SE.MDB2VNEX.TCOBOL(#GOALL) - 01.00 28 CHARS '^M[DB].*[0-9]{
Command ===>                                     Scroll ===> CSR 
002400 MDB2DB01 EXPD=N 
002500 MDB2DB02 EXPD=N 
002600 MDB2DB03 EXPD=N 
002700 MDB2DB04 EXPD=N 
002800 MDB2DB05 EXPD=N 
002900 MDB2DB06 EXPD=N 
003000 MDB2DB07 EXPD=N 
003100 MDB2DB08 EXPD=N 
003200 MDB2DB09 EXPD=N 
003300 MDB2DB10 EXPD=N 
003400 MDB2DB11 EXPD=N 
003500 MDB2DB12 EXPD=N 
003600 MDB2DB13 EXPD=N 
- - - - - - - - - - - - - - - - - - - 7 Line(s) not Displayed
009900 MDB2U001 EXPD=N 
010000 MDB2U003 EXPD=N 
010100 MDB2U005 EXPD=N 
010200 MDB2U006 EXPD=N 
010300 MDB2U007 EXPD=N 
- - - - - - - - - - - - - - - - - - - 4 Line(s) not Displayed
019800 O2DB60 EXPD=N 
019900 O2DB61 EXPD=N 
020000 O2DB62 EXPD=N 
020100 O2DB63 EXPD=N 
020200 O2DB64 EXPD=N 
020300 O2DB65 EXPD=N 
020400 O2DB66 EXPD=N 
020500 O2DB67 EXPD=N 
020600 O2DB68 EXPD=N 
020700 O2DB69 EXPD=N

And the Message looks like:

All pretty cool! Naturally you can use nearly any format of regex wherever you like and so it is very powerful. They are fully supported in Find, Change (But not for the second argument in a Change!) and Exclude.

Further infos about regex

Usage in TSO: For more info about their usage in TSO check out the KC entry

https://www.ibm.com/docs/en/zos/2.1.0?topic=string-regular-expressions-string1#useofr1

REXX Usage: Rexx usage is also available and Martin Packer wrote a great little Blog all about a regex testing routine that you can use to play with as well:
https://mainframeperformancetopics.com/2020/01/05/tso-regular-expression-testing-tool/

So what about Db2?

Well… on the “boxes you can lift” they have had regex for a long time. All built into the Db2 Engine. Fred Sobotka’s article “Advanced Pattern Matching with Regular Expressions in DB2 11.1 for LUW” in the IDUG Blog shows lots of really cool ways of using REGEXP_LIKE and its brethren and is well worth a read.

What about “real” Db2?

Well… we have it… sort of… not really what you would think but it is indeed there! You just have to jump over some fences and avoid some rocks and twist your required data into XML and Bob’s your uncle!

Say what? Regex lists out useful tables like:

Ok, ok, enough delaying… here is a regex that lists out all tables that start with between two and five characters ranging from B to Z and then ends with just two numerics:

SELECT NAME, creator from SYSIBM.SYSTABLES 
WHERE 
XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING
        XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
        as "newXDoc")    
order by 1     
--fetch first 10 rows onl
;
---------+---------+---------+---------+---------+---------+--------
NAME
---------+---------+---------+---------+---------+---------+--------
CERNT01  
CERNT02 
CERNT03
CERNT04  
CERNT05 
CERNT06

It will not win a beauty contest any day soon but, hey, it works!

How does that work?

SELECT NAME, creator from SYSIBM.SYSTABLES
WHERE

Just a run-of-the-mill, bog standard SELECT, FROM and WHERE – Nothing new or changed here!

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING 
XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")
as "newXDoc")

This must be viewed as two separate statements, first the inner statement:

XMLQUERY('<doc>{$xmltbname}</doc>' PASSING NAME as "xmltbname")

This “translates” the column NAME into an XML construct with the element name xmltbname ready for the regex which we do by “faking it” through an XMLEXISTS:

XMLEXISTS('$newXDoc[fn:matches(., "^[B-Z]{2,5}[0-9]{2}$")]' PASSING 
         …
         as "newXDoc")

This uses the fn:matches function which will actually do the regex.

The data in the regex ^[B-Z]{2,5}[0-9]{2}$ is a normal regex and it uses the xmltbname as input. fn:matches is boolean and so returns True or False. This is then a “normal” predicate as any row that is “true” must have passed the regex.

Clear as mud!

It is a bit weird and I can recommend some more reading,

firstly the excellent regex site where you can learn all about the joys and dangers of these beasts:

http://www.rexegg.com/

And the IBM XQuery docu that describes how the fn:matches works in detail:

https://www.ibm.com/docs/en/db2-for-zos/12?topic=expressions-regular

Both are well worth a read – be especially careful about “explosive quantifiers”!

CPU?

Yep, you guessed it. There is no such thing as a free lunch!

The use of this method is *not* cpu light.

It should only ever be used if normal LIKE, REPLACE or TRANSLATE cannot easily get the job done and if you end up coding a regex like:

^(?=(?!(.)\1)([^\DO:105-93+30])(?-1)(?<!\d(?<=(?![5-90-3])\d))).[^\WHY?]$

Then do not be surprised if your colleagues all start to hate you! 

  😉 😉

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

Veröffentlicht am

2018-12: Db2 Checklist – SQLCODEs as never seen before

With this SQLCODE list, from the “bad guys” through to the “odd guys”  – e.g. the SQLCODE 100 – and then onto the „not so bad guys“, you can see a practical list of current SQLCODEs in production from around the globe, including their meaning. This review also analyzes the behaviour of some SQLCODEs and also proposes some solutions to get your Db2 z/OS programs correctly processing these SQLCODEs.

Well perhaps not quite that dramatic! Our SQL WorkLoadExpert for z/OS software (aka WLX) has been running at customer sites for a while now with the “Failing Statements” Use Case active. So I thought it would be interesting to review the eclectic mix of SQLCODEs that it receives…

Bad Guys

These all have negative SQLCODE values and so are bad…but look at the mix…

     SQLCODE        COUNT
     -------        -----
      -30081          209  Communication error
      -30080            1  Communication error
      -20398            3  Error during XML Parsing
      -20385            1  PENDING DDL caused error
      -20289            3  Invalid string unit
      -20223            4  Encryption facility not available
      -20186            5  A dynamic SQL clause is invalid
      -20183            1  ALTER ADD PARTITION invalid syntax
      -20008            2  Attempt to use unsupported feature
       -4743            3  Attempt to use new function
        -950            4  LOCATION name invalid
        -926            1  ROLLBACK not valid in IMS, CICS or RRSAF
        -925          816  COMMIT not valid in IMS, CICS or RRSAF
        -913            6  Deadlock or Timeout
        -911            8  Deadlock or Timeout
        -905            3  RLF timeout
        -904          165  Unavailable resource
        -876            2  xxx cannot be created or altered
        -874            1  Encoding scheme conflict
        -846           17  Invalid IDENTITY or SEQUENCE
        -822            1  SQLDA contains invalid data address
        -811       39,231  More than one row returned for a SELECT
        -805          389  Package not found in PLAN
        -804           21  SQLDA is incorrect
        -803    4,437,491  Duplicate INSERT attempted
        -713            2  Replacement value is invalid
        -669            8  Object cannot be dropped
        -650            2  ALTER cannot be executed
        -647           18  BP for implicit not activated
        -644            5  Invalid value for keyword
        -637            3  Duplicate keyword or clause
        -628            5  Clauses are mutually exclusive
        -612            4  LOCKMAX 0 only if LOCK SIZE TS or TAB
        -607           11  Operation is not defined for this object
        -601           49  Object already exists
        -556            1  Priv cannot be revoked
        -553            1  Authorization not valid (SET CURRENT SQLID)
        -552            1  Auth does not have the privilege
        -551            8  Authorization failure
        -530            4  Insert or Update of Foreign Key invalid
        -525            9  Statement in error at bind time
        -518            8  EXECUTE does not identify a prepared stmt
        -516           15  Describe for a not prepare
        -514            8  Cursor not in a prepared state
        -512            9  Statement reference remote obj invalid
        -502            2  Cursor in an OPEN is already OPEN
        -501   18,825,773  Cursor in a FETCH or CLOSE is not OPEN
        -471        1,832  Procedure/Function failed
        -440            4  Routine not found
        -433            9  VALUE is too long
        -421            8  Operands of SET not the same column count
        -420          266  String value is not acceptable to function
        -419            2  Decimal divide invalid – negative scale
        -418            2  Statement contains invalid parameter markers
        -413           14  Overflow or Underflow
        -408           12  VALUE incompatible with target
        -407           22  UPDATE, INSERT, SET is NULL column NOT NULL
        -406           12  Calculated/Derived numeric out of range
        -405            8  Numeric constant out of range
        -401            2  Incompatible data types
        -390            5  Object not valid where it is used
        -338            9  An ON clause is invalid
        -327            1  Row is outside the bounds of last partition 
        -313           12  Number of host vars not equal par.markers 
        -312            4  Variable not defined or unusable
        -311           98  Length of input host variable out of bounds
        -310      154,274  Decimal host var contains no decimal data
        -305    2,244,618  NULL value cannot be assigned
        -214            1  An expression is invalid
        -304            1  Value cannot be assigned data type range
        -303            1  Value cannot be assigned data type incompat
        -302           13  Value of input var/arg x too large
        -301            3  Value of input var/arg x cannot be used
        -220           10  Column in PLAN_TABLE is incorrect
        -219          916  Required PLAN_TABLE does not exist
        -214            1  Expression invalid
        -208            7  Order by invalid
        -206           90  x is not valid
        -205           11  x is not a column of table
        -204        7,665  x is an undefined name
        -203            1  A reference to col x is ambiguous
        -199           30  Illegal use of keyword
        -196            4  Col cannot be dropped
        -183            5  Out of range for date/timestamp
        -181    1,156,892  String expression of datetime is invalid
        -180            8  Date, Time or Timestamp invalid
        -171            4  Data type/len/val of arg x of y is invalid
        -170            8  Invalid no. args
        -158            1  No.cols not equal result table
        -151            4  Update of catalog col not allowed
        -138            4  2nd or 3rd arg in SUBSTR out of range
        -126            2  Select contains UPDATE and ORDER BY
        -122            6  Col or exp in the select list is not valid
        -120            1  Aggregate or OLAP not valid
        -117           29  No. values not equal no. columns
        -109            7  Clause is not permitted
        -107            3  Name is too long
        -104           23  Illegal symbol
        -103            1  Invalid numeric constant
         -84            2  Unacceptable SQL statement
         -10            1  Non-terminated string constant

Now the ones that jump out are

811     Badly written SELECT that “normally” returns one row – Code must be corrected or the SELECT changed into a CURSOR.

803   This is the all-time classic “Should I insert or update?” problem. It could well be that MERGE is actually a better way forward.

501  This is down to the application development logic being “Always first CLOSE the cursor” which is naturally madness but is “how it is” – This needs a code change to just comment out the crazy CLOSE!

310   Oh dear! Looks pretty nasty to me…

305  Null indicators “forgotten” – quite probably an SQL coding error. Typically a LEFT OUTER JOIN style SQL that “normally” gets a match. SQL and/or code must be checked and changed. COALESCE can help here!

181  What is going on with datetime formats???

Not so Bad Guys

These all have positive SQLCODE values and so are not so bad…

      SQLCODE        COUNT
      -------        ----
          98          397  Dynamic SQL ends in a ;
         162            6  TS in check pending
         203            2  Qualified column name resolved using non-unique
                           or unexposed name
         222       21,652  Reading a hole with sensitive scrollable
         238           23  SQLDA not set-up correctly but enough space
                           is there for the LOB descripton
         347           37  Recursive SQL has no “brake” and could loop
         354          289  Multi-row fetch got warnings. GET DIAGNOSTICS
                           must be used
         403            2  ALIAS points to non-existent table
         445            2  VALUE has been truncated
         466           25  Stored proc returned nnn sets of data
         535            4  Positioned UPDATE/DELETE may depend on the order
                           of rows (self-referencing constraint)
         562            1  GRANT ignored as already held
         585        1,643  Collection appears more than once 
                           when setting a special register
         610            7  DDL has caused an object to enter PENDING
       20272            1  TS converted to table part from index part
       20520       80,115  Deprecated function usage

Here the ones that jump out are

222     This is normal if using sensitive scrollable cursors, probably ok.

585      Why double set the SCHEMA? Just wastes CPU cycles

20520  Whoops! Which feature/function do they mean? I have often seen old PLAN_TABLE usage causing this.

Weird Bad Guys

These two are just odd…

   SQLCODE    COUNT
   -------    -----
         0       25  Everything was ok or perhaps not?
                     Could be warnings were issued…
       100      117  Not found, End of Cursor or ???

From the documentation 100 is actually more interesting than you would think

Explanation:
One of the following conditions occurred:

  • No row met the search conditions specified in an UPDATE or DELETE statement.
  • The result of a SELECT INTO statement was an empty table.
  • The result of the subselect of an INSERT statement is empty.
  • A FETCH statement was executed when the cursor was positioned after the last row of the result table.
  • No available rows qualified for return when SKIP LOCKED DATA was specified with isolation level CS or RS.
  • A FETCH statement that returns a rowset was issued, but there were not enough rows after the current cursor position to reposition the cursor on a full rowset. The cursor has been positioned on a partial rowset. If a target was specified, data was returned only for the number of rows that were actually fetched for the partial rowset. The number of rows that were returned is in field SQLERRD3 of the SQLCA.

– When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion.

– This SQLCODE is also issued when LOB data cannot be returned. This situation can occur when an application is running with isolation level UR and another application has locked the LOB table space.

Ignore the usual suspects

With WLX you can also exclude a list of SQLCODEs that you are not interested in. I would be tempted to add 0, 98, 100, 222, 394, 466 and 535 as a starting point.

Does it matter?

Well of course the answer is “It Depends!”
If the programs are correctly processing the SQLCODEs then everything is fine. If, however, error checking is missing, incorrect WHENEVER logic is used, or stuff is simply ignored, then it is probably one of the sources of bad data at your site. It can also cost you money as cpu is not really free.

Measured response

Just for fun (yes, I’m weird like that) I coded a COBOL program that connected to Db2 and then did 1,000,000 CLOSE cursors. On our little machine, the 1,000,000 -501’s caused 68.10 Seconds of CPU – and this was just the CLOSE, no error handling or WHENEVERs involved at all – so the saving could/should be even more. With the CLOSE not being there the job took just 0.11 Seconds of CPU.

Then I did another test, because I know a JAVA framework that loves ending with ROLLBACK, ROLLBACK, COMMIT (I kid you not dear readers!),

and here’s how my 1,000,000 test results look:

Program with Rollback, Rollback, Commit               94.40 Seconds of CPU

Program with Rollback, Commit                               71.10 Seconds of CPU

Program with Rollback                                               19.62 Seconds of CPU

Program with Commit                                                 58.13 Seconds of CPU

So the best thing for performance is to just ROLLBACK! Lol!

How are you?

What do you have at your shop in production? Do you have any tips or tricks about these, or any other, SQLCODEs that you have experienced?

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect

 

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