Calculating size of table in a DPF database

Starting from DB2 versions 9.5 and up, a rich and detailed DB2 administrative view called SYSIBMADM.ADMINTABINFO comes bundled with DB2 engine. Among many things, it provides a handy way to calculate table size. In this blog post, we will look at how to calculate table size in a DPF database.

Table Size in a single partition database:

Let us first look at the SQL to calculate table size in a single partition database. Later, we will make changes to this SQL to fit to a DPF database.


select
substr(tabschema, 1, 18)as tabschema
,substr(tabname, 1, 40) as tabname
,(COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as tab_size_mb
from sysibmadm.admintabinfo
where tabtype='T' 
and tabschema like ('%SCHEMA%' )
and tabname like ('%TABLE%')
with ur;

TABSCHEMA          TABNAME             TABLE_SIZE_MB
------------------ -----------------  -------------
SCHEMA1             TABLE1              22463
SCHEMA2             TABLE2              15735
  2 record(s) selected.

Table Size in a multi-partition database:

If we run the above query in a multi-partition (DPF) database, for each table we get 1 row for each partition. In other words, if the table is spread across 5 database partitions, we get 5 rows.
In the example below, there are 5 rows for TABLE1 and 7 rows for TABLE2. This is because TABLE1 is spread across 5 database partitions and TABLE2 is spread across 7 database partitions.


select
substr(tabschema, 1, 18)as tabschema
,substr(tabname, 1, 40) as tabname
,SUM(COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as table_size_mb
from sysibmadm.admintabinfo
where tabtype='T' 
and tabschema like ('%SCHEMA%' )
and tabname like ('%TABLE%')
with ur;

TABSCHEMA          TABNAME      TABLE_SIZE_MB
------------------ -----------  -------------------
SCHEMA1              TABLE1         4501
SCHEMA1              TABLE1         4492
SCHEMA1              TABLE1         4491
SCHEMA1              TABLE1         4490
SCHEMA1              TABLE1         4489
SCHEMA2              TABLE2         2251
SCHEMA2              TABLE2         2250
SCHEMA2              TABLE2         2249
SCHEMA2              TABLE2         2248
SCHEMA2              TABLE2         2247
SCHEMA2              TABLE2         2246
SCHEMA2              TABLE2         2244
  12 record(s) selected.

What if we wanted to get the table size across all database partitions combined?
To do so, there needs to be only a slight change to the above SQL. We will add the table size on each partition and group by tabschema and tabname.


select
substr(tabschema, 1, 18)as tabschema
,substr(tabname, 1, 40) as tabname
,SUM(COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as table_size_mb
from sysibmadm.admintabinfo
where tabtype='T' 
and tabschema like ('%SCHEMA%' )
and tabname like ('%TABLE%')
-- Below group by helps us to get table size across all database partitions 
GROUP BY tabschema, tabname
with ur;

TABSCHEMA          TABNAME          TABLE_SIZE_MB
------------------ ----------------  -------------
SCHEMA1             TABLE1              22463
SCHEMA2             TABLE2              15735
  2 record(s) selected.

Table Size by Database Partition:

If we want to find out table size by each partition along with the partition number, just add the partition number to the original SQL.


select
substr(tabschema, 1, 18)as tabschema
,substr(tabname, 1, 40) as tabname
,SUM(COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 as table_size_mb
,DBPARTITIONNUM
from sysibmadm.admintabinfo
where tabtype='T' 
and tabschema like ('%SCHEMA%' )
and tabname like ('%TABLE%')
with ur;

TABSCHEMA          TABNAME      TABLE_SIZE_MB     DBPARTITIONNUM
------------------ -----------  -------------------   -------------------
SCHEMA1              TABLE1         4501                 1
SCHEMA1              TABLE1         4492                 2 
SCHEMA1              TABLE1         4491                 3 
SCHEMA1              TABLE1         4490                 4
SCHEMA1              TABLE1         4489                 5  
SCHEMA2              TABLE2         2251                 1
SCHEMA2              TABLE2         2250                 2
SCHEMA2              TABLE2         2249                 3
SCHEMA2              TABLE2         2248                 4   
SCHEMA2              TABLE2         2247                 5 
SCHEMA2              TABLE2         2246                 6
SCHEMA2              TABLE2         2244                 7
 12 record(s) selected.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s