After I held my Deprecated Items IDUG talk at the Virtual 2021 North American IDUG one of the attendees asked me if I also had some SQL for quickly retrieving the current „size of the database“ – Normally I use our very own SpaceManager product for this as it uses CSI access calls to get up to the second info about the size of the VSAM LDS, however for a quick and dirty query the Db2 catalog can be partially trusted!
Where to begin?
The easiest places to start are the two SYSIBM.SYSxxxxxPART tables. These contain all the partition details and if the PARTITION is zero then it is a non-partitioned tablespace or it is a NPSI defined index.
What to select?
SELECT COUNT(*) AS INDEX_PARTS
,SUM(IP.SPACEF) AS KB
,INTEGER(SUM(IP.SPACEF) / 1024) AS MB
,INTEGER(SUM(IP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSINDEXES IX
,SYSIBM.SYSINDEXPART IP
WHERE IX.NAME = IP.IXNAME
AND IX.CREATOR = IP.IXCREATOR
AND NOT IP.SPACE < 0
;This query just shows you the current size of all of your indexes. Notice the NOT IP.SPACE < 0 – This is to not count any DEFINE NO indexes as these use no space of course! I simply added the two INTEGER functions – naturally you can change these to any format you would like.
Table time!
SELECT COUNT(*) AS TABLE_PARTS
,SUM(TP.SPACEF) AS KB
,INTEGER(SUM(TP.SPACEF) / 1024) AS MB
,INTEGER(SUM(TP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSTABLEPART TP
WHERE TS.NAME = TP.TSNAME
AND TS.DBNAME = TP.DBNAME
AND NOT TP.SPACE < 0
;This does the same but now for tableparts of course!
More detail?
Now these two SQLs are OK for quick results but it is probably more interesting to see which index types and which tablespace types are taking up how much space on your disks. So here the expanded queries follow:
SELECT COUNT(*) AS INDEX_PARTS
,IX.INDEXTYPE
,IX.IX_EXTENSION_TYPE
,IX.PAGENUM
,SUM(IP.SPACEF) AS KB
,INTEGER(SUM(IP.SPACEF) / 1024) AS MB
,INTEGER(SUM(IP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSINDEXES IX
,SYSIBM.SYSINDEXPART IP
WHERE IX.NAME = IP.IXNAME
AND IX.CREATOR = IP.IXCREATOR
AND NOT IP.SPACE < 0
GROUP BY IX.INDEXTYPE
,IX.IX_EXTENSION_TYPE
,IX.PAGENUM
ORDER BY IX.INDEXTYPE
,IX.IX_EXTENSION_TYPE
,IX.PAGENUM
;The INDEXTYPE column can be Blank (For ancient Type 1 indexes which I hope no-one has), 2 for „normal“ Type 2 Indexes, D for Data-partitioned Secondary Indexes (DPSIs) and P for Indexes which are both Partitioned and Partitioning (which I also hope no-one has anymore!)
What’s in a TYPE?
The IX_EXTENSION_TYPE column can be Blank for „normal“ indexes, N for Node ID indexes on XML spaces, S for Scalar Expressions, T for Spatial or V for real XML Indexes.
Using new features yet?
PAGENUM is just telling you how many of your indexes are enjoying the great feature of fully variable DSSIZE, adjustable on-the-fly with no outage (As long as you adjust upwards of course!) If set to „A“ for Absolute then sadly not, much better is „R“ for Relative!
SELECT COUNT(*) AS TABLE_PARTS
,TS.TYPE
,TS.PAGENUM
,SUM(TP.SPACEF) AS KB
,INTEGER(SUM(TP.SPACEF) / 1024) AS MB
,INTEGER(SUM(TP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSTABLESPACE TS
,SYSIBM.SYSTABLEPART TP
WHERE TS.NAME = TP.TSNAME
AND TS.DBNAME = TP.DBNAME
AND NOT TP.SPACE < 0
GROUP BY TS.TYPE
,TS.PAGENUM
ORDER BY TS.TYPE
,TS.PAGENUM
;What’s in a tablespace TYPE?
Now at the tablespace level we have, again, the TYPE column which can be Blank for a „normal“ table, G for UTS PBG, L for Large, O for LOB, P for XML or R for UTS PBR. Going forward you will have to migrate any of the Blank tablespaces to PBG/PBR and L should be gone anyway!
My Output
When I run these here in Düsseldorf in my little Db2 12 FL508 test system I get this output:
---------+---------+---------+---------+---------+---------+--
INDEX_PARTS KB MB GB
---------+---------+---------+---------+---------+---------+--
4453 +0.5951116000000000E+07 5811 5---------+---------+---------+---------+---------+---------+--
TABLE_PARTS KB MB GB
---------+---------+---------+---------+---------+---------+--
3733 +0.3255953100000000E+08 31796 31---------+---------+---------+---------+---------+---------+-
INDEX_PARTS INDEX IX_EXT PAGE KB MB GB
TYPE TYPE NUM
---------+---------+---------+---------+---------+---------+-
3280 2 A +0.5100220000000000E+07 4980 4
25 2 N A +0.1800000000000000E+05 17 0
3 2 S A +0.1224000000000000E+05 11 0
8 2 V A +0.3744000000000000E+04 3 0
15 D A +0.8112000000000000E+04 7 0
24 P A +0.1392000000000000E+05 13 0
1098 P R +0.7948800000000000E+06 776 0---------+---------+---------+---------+---------+--------
TABLE_PARTS TYPE PAGENUM KB MB GB
---------+---------+---------+---------+---------+--------
363 A +0.5242134000000000E+07 5119 4
2094 G A +0.2109695400000000E+08 20602 20
4 L A +0.1312000000000000E+04 1 0
116 O A +0.1186235000000000E+07 1158 1
25 P A +0.2952000000000000E+05 28 0
33 R A +0.6932800000000000E+05 67 0
1098 R R +0.4934048000000000E+07 4818 4So you can see I already have over 1000 UTS PBR RPN table parts in use! Not bad! As a vendor I cannot just migrate everything of course so those 24 Index Controlled Partitioning indexes and the 33 Absolute Partitions aren’t going anywhere soon!
How does your site look? How are your plans for migrating to UTS PBR RPN going?
As always, I would love to hear the results!
TTFN,
Roy Boxwell