How To Find Schema Name For A Table In Db2

IBM Support

How to find out the size of each table inside a DB2 database

How To

Summary

Client/consultant would like to observe out how large each of their Controller database tables (for example 'xdb18') are.

How can they do this?

Environs

The script (inside this Technote) has been tested on DB2 x.5.

TIP: For similar instructions when using Microsoft SQL, see dissever IBM Technote #1345780.

Steps

Run the post-obit DB2 script on your database:

SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,thirty) TABNAME,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) Equally TOTAL_SIZE_IN_KB,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 As TOTAL_SIZE_IN_MB, (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) / (1024*1024) Equally TOTAL_SIZE_IN_GB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%';

This provides:

Schema proper noun
Table name
Total size (kb)
Full size (Mb)
Total size (Gb)

For example:

image-20181213201805-1

Related Information

[{"Business organisation Unit of measurement":{"code":"BU059","label":"IBM Software w\/o TPS"},"Production":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]