2020-05 Things I never knew

Believe it or not, I actually do not know everything (Please do not tell my long-suffering wife!) about Db2. I do know a ton of stuff, but you never ever stop learning.

I thought I would create a newsletter out of all the stuff I never knew before that I have learnt over the last few years. Maybe you’re very clever and know all of this already? But if you don’t, that’s OK too.

Sliding Scale

We have a nice piece of software called Space AssuranceExpert for Db2 z/OS (SAX), that guarantees that you do not hit any of the various limits within space management, and beyond, on Db2. When IBM introduced their sliding scale of secondary allocations it collided with our variant. (We use “seed” values to get, what I believe, is a much better utilization of available space, but I digress…) so we changed our system to check the MGEXTSZ. This, by the way, is one of the worst documented ZPARMs I have ever tried to understand! The documentation states “MGEXTSZ default YES – If SECQTY is greater than 0 then the sliding scale will be used”. So, when you create an object using SECQTY -1 or just not using SECQTY (also gives you -1) it would appear that the sliding scale is switched off… Well what IBM really meant was the SECQTY of the allocated VSAM dataset. Naturally this is either zero (no secondaries for you!) or a positive number! If you look at the Db2 Catalog tables you will also see that the fields you use as PRIQTY and SECQTY are actually stored as PQTY, SQTY, and SQTYI so, technically speaking, IBM are correct. The only case where MGEXTSZ is actually used is if you *do* have a positive value in the SQTY/SQTYI field. If MGEXTSZ is YES then this value is ignored and the sliding scale is used, if MGEXTSZ is NO then the value of SQTY/SQTYI, converted back to kilobytes of course, will be used.

An extent by any other name

Talking about SAX: I also saw something quite amazing as a customer allocated a PBG space with 11 maxpartitions and a DSSIZE of 8GB. He managed to hit maxpartitions without getting warned by SAX. “Impossible”, I said! There is no way it can allocate one dataset at 8GB with *no* extents… Yet this customer had somehow managed it… Their EAVs are so huge, they could allocate without a *single* extent being requested. Now, I always thought that every dataset that is allocated gets one extent, and that this extent would trigger the IFCID 258. I was wrong! The creation of an LDS, if it does not require more than one extent, does not trigger this IFCID. It does, however, trigger the IFCID 97 (LDS Creation).So I added this IFCID into our SAX system too and now we are able to catch LDS problems even with *massive* disks!

Index for DGTT

This brings me to the next item. If you create a DGTT:

DECLARE GLOBAL TEMPORARY TABLE T (COL1 CHAR(1) NOT NULL);

You get *no* dataset being created after all why? It just goes in DSNDB07 etc. so a create makes no sense, right? 

SAX has seen nothing either:

O2RT-SU04-011I: 10:01:09 - Datasets will be processed now                  

Now create an index on that DGTT

DECLARE GLOBAL TEMPORARY TABLE T (COL1   CHAR(1) NOT NULL);
CREATE INDEX I ON SESSION.T (COL1 ) ;        

Now you do get a dataset being allocated and deleted!

O2RT-SU04-011I: 10:01:09 - Datasets will be processed now                 
O2RT-SU04-024I: LDS creation for DB2DC1.DSNDBD.DSNDB07.TIX12768.I0001.A001 
O2RT-SU04-011I: 14:41:58 - Datasets will be processed now                 

Notice the Database name “DSNDB07”?- This tells you that it is creating a dataset in the work database but this is not really documented anywhere. Naturally, if you think about it for a while, it *must* create a VSAM LDS to simply hold the data, as it cannot use another one of the DSNDB07 tablespaces for that purpose! This is something to bear in mind when using indexes on DGTTs. You get dataset create/delete elapsed time…

RUNSTATS for XML

In Db2 12, FL100 RUNSTATS was enhanced to enable Frequency and Histogram data to be collected into the SYSKEYTGTDIST catalog table. I created an XML Object:

DROP DATABASE      "BOXWELLX" ;                                     
COMMIT ;                                                           
  CREATE DATABASE "BOXWELLX"                                       
         BUFFERPOOL BP0                                            
         INDEXBP    BP0                                            
         STOGROUP   SYSDEFLT                                       
         CCSID UNICODE                                              
;                                                                  
COMMIT ;                                                           
  CREATE       TABLESPACE "XMLCUSTO"                               
         IN "BOXWELLX"                                             
         USING STOGROUP SYSDEFLT                                   
         PRIQTY      720                                           
         SECQTY      720                                           
         ERASE NO                                                  
         DEFINE YES                                                
         FREEPAGE    0                                             
         PCTFREE     5                                              
         GBPCACHE CHANGED                                          
         TRACKMOD YES                                              
         LOG      YES                                              
         MAXPARTITIONS  6                                           
         BUFFERPOOL BP0                                            
         LOCKSIZE ROW                                              
         LOCKMAX SYSTEM                                            
         CLOSE YES                                                 
         COMPRESS NO                                               
         CCSID UNICODE                                             
         MAXROWS  255                                               
         SEGSIZE    4                                              
;                                                                  
COMMIT ;                                                           
  CREATE TABLE "BOXWELL"."XMLCUSTOMER"                             
  ("CID"                            BIGINT                  NOT NULL
  ,"INFO"                           XML                             
  ,CONSTRAINT CID                                                  
   PRIMARY KEY                                                     
  ("CID"                                                           
  )                                                                 
  )                                                                
  IN  "BOXWELLX"."XMLCUSTO"                                        
  AUDIT NONE                                                       
  DATA CAPTURE NONE                                                
  CCSID UNICODE                                                    
      VOLATILE CARDINALITY                                         
  APPEND NO                                                         
;                                                                  
  COMMIT ;                                                   
    CREATE UNIQUE INDEX                "BOXWELL"."XML_PRIMARY"
           ON "BOXWELL"."XMLCUSTOMER"                         
           ("CID"                            ASC             
           )                                                 
           CLUSTER                                           
           USING STOGROUP SYSDEFLT                            
           PRIQTY      720                                   
           SECQTY      720                                   
           ERASE NO                                          
           FREEPAGE    0                                      
           PCTFREE    10                                     
           GBPCACHE CHANGED                                  
           DEFINE YES                                        
           COMPRESS NO                                       
           BUFFERPOOL BP0                                    
           CLOSE NO                                          
           PIECESIZE    2 G                                  
           COPY NO                                           
  ;                                                          
  COMMIT ;                                                   
    CREATE INDEX                       "BOXWELL"."CUST_TYPE" 
           ON "BOXWELL"."XMLCUSTOMER"                        
           ("INFO"                                           
           )                                                 
    GENERATE KEY USING XMLPATTERN                            
/customerinfo/phone/@type'                                   
    AS SQL VARCHAR( 128)                                     
           NOT CLUSTER                                       
           NOT PADDED                                        
           USING STOGROUP SYSDEFLT                           
           PRIQTY        4                                   
           SECQTY        4                                   
           ERASE NO                                          
           FREEPAGE    0                                     
           PCTFREE    10                                     
           GBPCACHE CHANGED                                  
           DEFINE YES                                        
           COMPRESS NO                                        
           BUFFERPOOL BP0                                    
           CLOSE YES                                         
           PIECESIZE    2 G                                  
           COPY NO                                           
  ;                                                          
    CREATE INDEX                       "BOXWELL"."CUST_ZIP"  
           ON "BOXWELL"."XMLCUSTOMER"                        
           ("INFO"                                           
           )                                                
    GENERATE KEY USING XMLPATTERN                           
//pcode-zip'                                                
    AS SQL DECFLOAT(34)                                     
           NOT CLUSTER                                      
           NOT PADDED                                       
           USING STOGROUP SYSDEFLT                          
           PRIQTY        4                                  
           SECQTY        4                                  
           ERASE NO                                         
           FREEPAGE    0                                    
           PCTFREE    10                                    
           GBPCACHE CHANGED                                 
           DEFINE YES                                        
           COMPRESS NO                                      
           BUFFERPOOL BP0                                   
           CLOSE YES                                        
           PIECESIZE    2 G                                  
           COPY NO                                          
  ;                                                         
  COMMIT ;                                                  
    CREATE INDEX                       "BOXWELL"."CUST_PHONE"
           ON "BOXWELL"."XMLCUSTOMER"                       
           ("INFO"                                          
           )                                                
    GENERATE KEY USING XMLPATTERN                           
/customerinfo/phone'                                        
    AS SQL VARCHAR( 128)                                    
           NOT CLUSTER                                      
           NOT PADDED                                       
           USING STOGROUP SYSDEFLT                          
           PRIQTY        4                                  
           SECQTY        4                                  
           ERASE NO                                          
           FREEPAGE    0                                    
           PCTFREE    10                                    
           GBPCACHE CHANGED                                 
           DEFINE YES                                        
           COMPRESS NO                                      
           BUFFERPOOL BP0                                   
           CLOSE YES                                        
           PIECESIZE    2 G                                  
           COPY NO                                          
  ;                                                         
  COMMIT ;                                                

Phew!

Then I needed to insert a few rows. Here are the five I used for my tests:

SET CURRENT SQLID = 'BOXWELL' ;                                  
COMMIT ;                                                         
DELETE FROM BOXWELL.XMLCUSTOMER ;                                
COMMIT ;                                                         
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06001, XMLPARSE(DOCUMENT '
<customerinfo><name>Justin Horovitz</name>                        
<addr country="United States"><street>327 Ramses Ave</street>    
<city>Glendale</city><prov-state>California</prov-state>         
<pcode-zip>91208-06001</pcode-zip></addr>                        
<phone type="work">818-956-06001</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06002, XMLPARSE(DOCUMENT '
<customerinfo><name>Matthew Broad</name>                         
<addr country="United States"><street>808 Mayo St</street>       
<city>Burbank</city><prov-state>California</prov-state>          
<pcode-zip>9150600-06002</pcode-zip></addr>                      
<phone type="work">818-541-06002</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06003, XMLPARSE(DOCUMENT '
<customerinfo><name>Laura McCarthy</name>                        
<addr country="United States"><street>5224 Grover Court</street> 
<city>San Jose</city><prov-state>California</prov-state>         
<pcode-zip>95123-06003</pcode-zip></addr>                        
<phone type="work">408-956-06003</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06004, XMLPARSE(DOCUMENT '
<customerinfo><name>Mira Glass</name>                            
<addr country="United States"><street>444 Valencia St</street>   
<city>San Francisco</city><prov-state>California</prov-state>    
<pcode-zip>94110-06004</pcode-zip></addr>                        
<phone type="work">415-762-06004</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06005, XMLPARSE(         
'<customerinfo><name>Amir Malik</name>                           
<addr country="United States"><street>555 Bailey Ave</street>    
<city>San Jose</city><prov-state>California</prov-state>         
<pcode-zip>95141-06005</pcode-zip></addr>                        
<phone type="work">408-555-06005</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06995, XMLPARSE(         
'<customerinfo><name>Amir Malik</name>                           
<addr country="United States"><street>555 Bailey Ave</street>    
<city>San Jose</city><prov-state>California</prov-state>         
<pcode-zip>95141-06995</pcode-zip></addr>                         
<phone type="work">408-555-06995</phone></customerinfo>'));      INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06996, XMLPARSE(         
'<customerinfo><name>Kathy Smith</name>                          
<addr country="Canada"><street>25 EastCreek</street>             
<city>Toronto</city><prov-state>Ontario</prov-state>             
<pcode-zip>M8X-3T6-06996</pcode-zip></addr>                      
<phone type="work">416-555-06996</phone></customerinfo>'));      
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06997, XMLPARSE(     
'<customerinfo><name>Jim Noodle</name>                       
<addr country="Canada"><street>25 EastCreek</street>         
<city>Markham</city><prov-state>Ontario</prov-state>         
<pcode-zip>N9C-3T6-06997</pcode-zip></addr>                  
<phone type="work">905-555-06997</phone></customerinfo>'));  
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06998, XMLPARSE(     
'<customerinfo><name>Anant Jhingran</name>                   
<addr country="United States"><street>555 Bailey Ave</street>
<city>San Jose</city><prov-state>California</prov-state>     
<pcode-zip>95141-06998</pcode-zip></addr>                    
<phone type="work">408-555-06998</phone>                     
<phone type="home">416-555-06998</phone>                     
<phone type="cell">905-555-06998</phone>                     
<phone type="cottage">613-555-06998</phone></customerinfo>'));
INSERT INTO BOXWELL.XMLCUSTOMER VALUES (06999, XMLPARSE(     
'<customerinfo><name>Bert and Ernie Inc.</name>              
<addr country="Canada"><street>1 Yonge Street</street>       
<city>Toronto</city><prov-state>Ontario</prov-state>         
<pcode-zip>M5W-IE6-06999</pcode-zip></addr>                  
<phone type="work">416-555-06999</phone></customerinfo>'));  
COMMIT ;                                                     

If you run a RUNSTATS on that tablespace:

RUNSTATS TABLESPACE BOXWELLX.XMLCUSTO         
   TABLE(ALL)                                 
   INDEX(ALL                                  
   KEYCARD                                    
         FREQVAL   NUMCOLS 0001 COUNT   10 MOST
         FREQVAL   NUMCOLS 0002 COUNT   10 MOST
         HISTOGRAM NUMCOLS 0001               
         HISTOGRAM NUMCOLS 0002               
        )                                     
   SHRLEVEL CHANGE                            
   REPORT YES                                 
   UPDATE ALL                                  
   SORTDEVT SYSALLDA                          
   SORTNUM  0004                              

In Db2 11 and Db2 12 I got *exactly* the same output. Even though I have created three XML indexes that *should* have got the SYSKEYTGTDIST statistics! The Db2 Catalog entries for these three indexes also look a bit “odd” as the UNIQUERULE column is set to “D” (I was expecting an “X” for XML!) and the IX_EXTENSION_TYPE is set to “V” (XML). Interestingly the implicit I_DocId Index *is* a UNIQUERULE ‘X’ and IX_EXTENSION_TYPE blank entry even though, in my eyes, it is *not* an XML index!

Now, when you create the test objects listed above, you also get a whole bunch of implicit objects. The tablespace (mine was called XXML0000), which contains the implicit table BOXWELL.XXMLCUSTOMER with five columns and an implicit I_NodeId index.

If you now run this RUNSTATS:

RUNSTATS TABLESPACE BOXWELLX.XXML0000          
   TABLE(ALL)                                   
   INDEX(ALL                                   
   KEYCARD                                     
         FREQVAL   NUMCOLS 0001 COUNT   10 MOST
         FREQVAL   NUMCOLS 0002 COUNT   10 MOST
         HISTOGRAM NUMCOLS 0001                
         HISTOGRAM NUMCOLS 0002                
        )                                      
   SHRLEVEL CHANGE                             
   REPORT YES                                  
   UPDATE ALL                                   
   SORTDEVT SYSALLDA                           
   SORTNUM  0004                             

In Db2 11 it moans about the FREQVAL and HISTOGRAM keywords:

DSNU1354I -QB1A 120 09:14:41.60 DSNUSIIX - KEYWORD KEYCARD IS NOT SUPPORTED FOR XML OBJECTS. THE KEYWORD IS IGNORED 
DSNU1354I -QB1A 120 09:14:41.60 DSNUSIIX - KEYWORD FREQVAL IS NOT SUPPORTED FOR XML OBJECTS. THE KEYWORD IS IGNORED 
DSNU1354I -QB1A 120 09:14:41.60 DSNUSIIX - KEYWORD HISTOGRAM IS NOT SUPPORTED FOR XML OBJECTS. THE KEYWORD IS IGNORED

And it does not create the SYSKEYTGTDIST entries. In Db2 12 it moans, but a bit differently:

DSNU1354I -DC10 120 09:13:07.35 DSNUSIIX - KEYWORD KEYCARD IS NOT SUPPORTED FOR CERTAIN TYPES OF XML OBJECTS. 
THE KEYWORD IS IGNORED.                                                                                        DSNU1354I -DC10 120 09:13:07.35 DSNUSIIX - KEYWORD FREQVAL IS NOT SUPPORTED FOR CERTAIN TYPES OF XML OBJECTS. 
THE KEYWORD IS IGNORED.                                                                                       DSNU1354I -DC10 120 09:13:07.35 DSNUSIIX - KEYWORD HISTOGRAM IS NOT SUPPORTED FOR CERTAIN TYPES OF XML OBJECTS.
THE KEYWORD IS IGNORED.                                                                                        

In my case, right at the end of the job, were the successful results:

DSNU1353I -DC10 120 09:13:07.43 DSNUSUKD - SYSKEYTGTDIST CATALOG STATISTICS FOR                                 

                                       CUST_TYPE  KEYSEQ 0001                                                  

                              FREQUENCY           COLVALUE                                                     

                              ---------           --------                                                     

                              7,6923076923076E-01 X'776F726B0000000000000000000000000000000000000000000000000000

                                                    000000000000000000000000000000000000000000000000000000000000

                                                    000000000000000000000000000000000000000000000000000000000000

                                                    000000000000000000000000000000000000000000000000000000000000

                                                    00000000000000000004'                                      

And

DSNU1356I -DC10 120 09:13:07.44 DSNUSUKD - SYSKEYTGTDIST CATALOG HISTOGRAM STATISTICS FOR CUST_TYPE KEYSEQ 1 QUANTILE 1

                              LOWVALUE          = X'63656C6C0000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    00000000000000000004'                                             

                              HIGHVALUE         = X'63656C6C0000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    000000000000000000000000000000000000000000000000000000000000      

                                                    00000000000000000004'                                             

                              CARDF             = 1,0E+00                                                              

                              FREQUENCYF        = 7,6923076923076E-02                                                 

Most of the other columns were also reported, apart from one set of data which was strangely missing. Can you guess which one and why? Scroll back up to the creation of the indexes and you will see that I made one resolve into a DECFLOAT. This is*not* supported and so is simply ignored!

Where’s the beef?

Well, the interesting point about this “voyage of XML discovery” is you *must* run a RUNSTATS on a different tablespace than where the indexes were created. Does your current RUNSTATS creation software handle this? Or are you missing out on possible XMLEXISTS exploitation due to missing RUNSTATS? From the “What’s New?”:

Db2 can use frequency and histogram statistics for XML indexes to estimate the filter factor of XMLEXISTS predicates when the following conditions are true:

The predicate is one of the following types:

  • Equality predicates: =
  • Range predicates: >, >=, >=, <=
  • The right side of the predicate is a literal value.

Our Freeware StatisticsHealthCheck was just upgraded to version 3.4 to handle exactly this “problem”.

Naturally, if you can read, it is an advantage and the IBM Db2 documentation clearly states:

“XML indexes are related to XML tables, and not to the associated base tables. If you specify a base table space and an XML index in the same RUNSTATS control statement, Db2 generates an error. When you run RUNSTATS against a base table, RUNSTATS collects statistics only for indexes on the base table, including the document ID index.”

Db2 11/12 – Utilities – RUNSTATS – Collection of statistics on XML objects

I had never noticed that paragraph before… Shame on me!

Caught in a BIND

Back in Db2 10, IBM introduced the BIND PACKAGE(xxx) EXPLAIN(ONLY) syntax to fill the PLAN_TABLE and its buddies, but without actually doing a BIND. Very cool feature – until you find out that it jolly well *does* do a BIND, with all of the locking implications that we all know and hate, before casually doing a ROLLBACK as if “these are not the droids you are looking for”. One of my pet bugbears I can tell you!

And this can really hurt in production!

Learning Curve

What have you learned from Db2 over the years that was strange/obtuse or just downright weird? Don’t be shy! We’d love to hear about it.

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

TTFN,

Roy Boxwell