Growth Monitoring of size of DB2 tables – SYSIBMADM.ADMINTABINFO

Application database tables in DB2 LUW environment grows or shrinks over the period of time , and for DBA to monitor space growth can be viewed in DB2 administrative view called SYSIBMADM.ADMINTABINFO so nothing too complicated to do here; full details about SYSIBMADM.ADMINTABINFO can be found in the IBM Help Centre.

Below I will go through the DB2  temporary table to store history of sizes and how you can implement this yourself.

The view using SYSIBMADM.ADMINTABINFO

So that I have created custom stored procedure to record or save table sizes on daily basis and generate report that can be mailed to DBA’s inbox:

CREATE VIEW DB_MAIN.TABLE_SIZES AS (

SELECT CURRENT_DATE AS STATS_DATE,

TABNAME AS TABNAME,TABSCHEMA AS TABSCHEMA,TABTYPE AS TABTYPE,TOTAL_SIZE AS TOTAL_OBJECT_P_SIZE,DATA_SIZE AS DATA_OBJECT_P_SIZE,DICT_SIZE AS DICTIONARY_SIZE,INDEX_SIZE AS INDEX_OBJECT_P_SIZE,LOB_SIZE AS LOB_OBJECT_P_SIZE,LONG_SIZE AS LONG_OBJECT_P_SIZE,XML_SIZE AS XML_OBJECT_P_SIZE FROM table(SELECT

TABNAME,

TABSCHEMA,

TABTYPE,

DECIMAL(((data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size)/ 1024.0),10,3) as total_size,

DECIMAL((DATA_OBJECT_P_SIZE / 1024.0),10,3) AS DATA_SIZE,

DECIMAL((DICTIONARY_SIZE / 1024.0),10,2) AS DICT_SIZE,

DECIMAL((INDEX_OBJECT_P_SIZE / 1024.0),10,3) AS INDEX_SIZE,

DECIMAL((LOB_OBJECT_P_SIZE / 1024.0),10,3) AS LOB_SIZE,

DECIMAL((LONG_OBJECT_P_SIZE / 1024.0),10,3) AS LONG_SIZE, DECIMAL((XML_OBJECT_P_SIZE / 1024.0),10,3) AS XML_SIZE

FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE ‘SYS%’

AND TABSCHEMA NOT LIKE ‘SNAP%’) as TABLESIZE

)

The view is not all the columns that are available in the view but are the ones that are the most useful for general day to day usage, there are many more here that you could use. The values are stored in Kb’s so need dividing by 1024 to get it too Mb’s. The other GOTCHA is that partitioned tables will appear as one row per partition.

Table to save sizes of  actual tables of the application database:

 

CREATE TABLE DB_MAIN.TABLE_SIZES_STATS  (

STATS_DATE             DATE NOT NULL,

TABNAME                VARCHAR(128),

TABSCHEMA              VARCHAR(128),

TABTYPE                CHARACTER(1),

TOTAL_OBJECT_P_SIZE    DECIMAL(10,3),

DATA_OBJECT_P_SIZE     DECIMAL(10,3),

DICTIONARY_SIZE        DECIMAL(10,2),

INDEX_OBJECT_P_SIZE    DECIMAL(10,3),

LOB_OBJECT_P_SIZE      DECIMAL(10,3),

LONG_OBJECT_P_SIZE     DECIMAL(10,3),

XML_OBJECT_P_SIZE      DECIMAL(10,3)

)

IN DB_MAIN_TS

COMPRESS YES

Note that if you do not have the “Storage Optimisation Feature” from IBM then please do not include the line “COMPRESS YES”, otherwise if the big blue comes to do an audit you could be in trouble. The best thing to avoid this is set the licensing to hard

Stored procedure to calculate and store table sizes using SYSIBMADM.ADMINTABINFO

This is the stored procedure that I use to stored the size of the at the time of running the SP.

CREATE PROCEDURE DB_MAIN.ADD_TABLE_SIZES_STATS   ()

LANGUAGE SQL

BEGIN

INSERT INTO DB_MAIN.TABLE_SIZES_STATS

SELECT *

FROM DB_MAIN.TABLE_SIZES

WITH UR;

END

What to do next

Use  this table to generate report and mail it to yourself (DBA) or application partners if they wish to receive them.