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