The size of the database can be determined by calling
CALL GET_DBSIZE_INFO(?, ?, ?, 0);
When we call above Db2 procedure, it also populates information in SYSTOOLS.STMG_DBSIZE_INFO table.
We can write the following query to properly format the results and get the info in bytes, KB, MB, GB and TB.
select distinct 'D' AS Object, 'BLUDB' as Name ,DB_SIZE AS Bytes ,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1024.0 ) , '999,999,999,999' ),1,15) AS KB ,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1048576.0 ) , '999,999,999' ),1,15) AS MB ,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1073741824.0 ) , '999,999.0'),1,15) AS GB ,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1099511627776.0 ) , '999.0'),1,15) AS TB ,substr(CURRENT USER,1,12) as OWNER from SYSTOOLS.STMG_DBSIZE_INFO;
If we we want similar information for all tables in TPCDS schema, then the following query provides the similar results per table.
select t.tabtype AS Object,
substr(RTRIM(t.TABSCHEMA) ||'.'|| t.TABNAME,1,50) as Name
,sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE)*1024 AS Bytes
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1.0 ) , '999,999,999,999' ),1,15) AS KB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1024.0 ) , '999,999,999' ),1,15) AS MB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1048576.0 ) , '999,999.0'),1,15) AS GB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1073741824.0 ) , '999.0'),1,15) AS TB
,substr(tb.OWNER,1,12) OWNER
from
TABLE (SYSPROC.ADMIN_GET_TAB_INFO('TPCDS', '')) AS T
,syscat.tables tb
where
t.TABNAME=tb.TABNAME and t.TABSCHEMA=tb.TABSCHEMA and
t.tabschema not in ('SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS')
GROUP BY t.tabschema, t.tabname, t.tabtype, tb.owner;
Now, you can also combine both of the above queries in a single statement but remember to call first CALL GET_DBSIZE_INFO(?, ?, ?, 0) before running the query.
-- Call GET_DBSIZE_INFO(?, ?, ?, 0) to refresh SYSTOOLS.STMG_DBSIZE_INFO
CALL GET_DBSIZE_INFO(?, ?, ?, 0);
select * from (
select distinct 'D' AS Object,
'BLUDB' as Name
,DB_SIZE AS Bytes
,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1024.0 ) ,
'999,999,999,999' ),1,15) AS KB
,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1048576.0 ) ,
'999,999,999' ),1,15) AS MB
,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1073741824.0 ) ,
'999,999.0'),1,15) AS GB
,substr(TO_CHAR ( nvl( DB_SIZE ,0 ) / ( 1099511627776.0 ) ,
'999.0'),1,15) AS TB
,substr(CURRENT USER,1,12) as OWNER
from SYSTOOLS.STMG_DBSIZE_INFO, syscat.tablespaces
UNION ALL
select t.tabtype AS Object,
substr(RTRIM(t.TABSCHEMA) ||'.'|| t.TABNAME,1,50) as Name
,sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE)*1024 AS Bytes
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1.0 ) ,
'999,999,999,999' ),1,15) AS KB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1024.0 ) ,
'999,999,999' ),1,15) AS MB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1048576.0 ) ,
'999,999.0'),1,15) AS GB
,substr(TO_CHAR ( nvl( sum(DATA_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+
XML_OBJECT_P_SIZE+COL_OBJECT_P_SIZE) ,0 ) / ( 1073741824.0 ) ,
'999.0'),1,15) AS TB
,substr(tb.OWNER,1,12) OWNER
from
TABLE (SYSPROC.ADMIN_GET_TAB_INFO('TPCDS', '')) AS T
,syscat.tables tb
where
t.TABNAME=tb.TABNAME and t.TABSCHEMA=tb.TABSCHEMA and
t.tabschema not in ('SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS')
GROUP BY t.tabschema, t.tabname, t.tabtype, tb.owner)
ORDER BY bytes;
The sample output for a TPCDS schema that I populated with 1 TB of data.
OBJECT NAME BYTES KB MB GB TB OWNER ------ ----------------------------- -------------------- --------------- --------------- --------------- --------------- ------------ T TPCDS.SHIP_MODE 262144 25 0 .0 .0 DBPEMON T TPCDS.WAREHOUSE 262144 25 0 .0 .0 DBPEMON T TPCDS.PROMOTION 262144 25 0 .0 .0 DBPEMON T TPCDS.CALL_CENTER 262144 25 0 .0 .0 DBPEMON T TPCDS.REASON 262144 25 0 .0 .0 DBPEMON T TPCDS.DBGEN_VERSION 262144 25 0 .0 .0 DBPEMON T TPCDS.INCOME_BAND 262144 25 0 .0 .0 DBPEMON T TPCDS.STORE 262144 25 0 .0 .0 DBPEMON T TPCDS.WEB_SITE 262144 25 0 .0 .0 DBPEMON T TPCDS.WEB_PAGE 262144 25 0 .0 .0 DBPEMON T TPCDS.HOUSEHOLD_DEMOGRAPHICS 524288 51 1 .0 .0 DBPEMON T TPCDS.CATALOG_PAGE 2097152 2,04 2 .0 .0 DBPEMON T TPCDS.TIME_DIM 10616832 10,36 10 .0 .0 DBPEMON T TPCDS.DATE_DIM 10878976 10,62 10 .0 .0 DBPEMON T TPCDS.ITEM 13631488 13,31 13 .0 .0 DBPEMON T TPCDS.CUSTOMER_ADDRESS 31588352 30,84 30 .0 .0 DBPEMON T TPCDS.CUSTOMER 78905344 77,05 75 .1 .0 DBPEMON T TPCDS.CUSTOMER_DEMOGRAPHICS 141688832 138,36 135 .1 .0 DBPEMON T TPCDS.INVENTORY 327417856 319,74 312 .3 .0 DBPEMON T TPCDS.WEB_RETURNS 9750183936 9,521,66 9,299 9.1 .0 DBPEMON T TPCDS.CATALOG_RETURNS 21585068032 21,079,16 20,585 20.1 .0 DBPEMON T TPCDS.STORE_RETURNS 32669171712 31,903,48 31,156 30.4 .0 DBPEMON T TPCDS.WEB_SALES 137349038080 134,129,92 130,986 127.9 .1 DBPEMON T TPCDS.CATALOG_SALES 274693619712 268,255,48 261,968 255.8 .2 DBPEMON T TPCDS.STORE_SALES 377689210880 368,837,12 360,193 351.8 .3 DBPEMON D BLUDB 856484638720 836,410,78 816,807 797.7 .8 DBPEMON 26 record(s) selected.
Source: Of course, I did not write these SQLs and I took it from db_size.