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