DB2 Database Queries 

**************** check Db2 version ******************

$ db2level

***************************Database SIZE******************************************

db2 "CALL GET_DBSIZE_INFO(?, ?, ?, 0)"

db2 "select db_size/1073741824 as SIZE_GB, db_capacity/1073741824 as CAPACITY_GB from systools.stmg_dbsize_info"

select sum(TBSP_USED_SIZE_KB) as DATABASE_SIZE from sysibmadm.TBSP_UTILIZATION

**********************SCHEM SIZE***************************************

select ((select sum(fpages*pagesize) from syscat.tablespaces as a, syscat.tables as b where 

a.TBSPACEID = b.TBSPACEID and tabschema = 'TEST' group by tabschema)+(select sum(nleaf*pagesize) 

from syscat.tablespaces as a, syscat.indexes as b where a.TBSPACEID = b.TBSPACEID and 

tabschema='TEST' group by tabschema))/1024/1024/1024 as SCHEMA_SIZE_GB from sysibm.sysdummy1

***********************ALL SCHEMA  SIZE IN MB/GB************************

SELECT SUBSTR(TABSCHEMA, 1, 18) AS SCHEMA_NAME,

       SUM((DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + 

            LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)) AS TOTAL_SIZE_IN_KB,

       SUM((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,

       SUM((DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + 

            LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)) / (1024 * 1024) AS TOTAL_SIZE_IN_GB

FROM SYSIBMADM.ADMINTABINFO

WHERE TABSCHEMA NOT LIKE 'SYS%'

GROUP BY SUBSTR(TABSCHEMA, 1, 18)

ORDER BY TOTAL_SIZE_IN_KB DESC;

********************************Tables SIZE***************************************

SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) 

TABNAME,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) 

AS 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) 

AS TOTAL_SIZE_IN_GB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%'

http://www.dbatodba.com/db2/how-to-do/how-to-calculate-the-size-of-one-table-or-one-schema

db2 "with rs as (select distinct(substr(t.tabname,1,35)) as table, t.card as card, t.type as type , \

t.fpages as fpages, case when ind.nleaf is null then 0 else ind.nleaf end as nleaf, \

ts.pagesize as pagesize,  t.tabname tab2, t.tabschema as schema from \

syscat.tables t inner join syscat.tablespaces ts on t.tbspaceid=ts.tbspaceid left \

join syscat.indexes ind on t.tabname=ind.tabname) select table,(sum(nleaf)+fpages)*pagesize as size,\

card from rs where schema='SET' and type='T' group by card,table,pagesize,fpages"

SELECT SUBSTR(TABSCHEMA, 1, 18) AS SCHEMA_NAME,

       SUM((DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + 

            LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)) AS TOTAL_SIZE_IN_KB,

       SUM((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,

       SUM((DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + 

            LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)) / (1024 * 1024) AS TOTAL_SIZE_IN_GB

FROM SYSIBMADM.ADMINTABINFO

WHERE TABSCHEMA NOT LIKE 'SYS%'

GROUP BY SUBSTR(TABSCHEMA, 1, 18)

ORDER BY TOTAL_SIZE_IN_KB DESC;

db2 "SELECT tabname,TABSCHEMA, SUM(DATA_OBJECT_P_SIZE)+

SUM(INDEX_OBJECT_P_SIZE)+ SUM(LONG_OBJECT_P_SIZE)+

SUM(LOB_OBJECT_P_SIZE)+ SUM(XML_OBJECT_P_SIZE) FROM

SYSIBMADM.ADMINTABINFO where TABSCHEMA='SET' group by tabname,tabschema" > SET-INDEX.TXT

***********************************SCHEM NAME ********************************

select schemaname as schema_name, owner as schema_owner, case ownertype when 'S' then 'system' when 'U' 

then 'individual user' end as schema_owner_type, 

definer as schema_definer, case definertype when 'S' then 'system' when 'U' then 'individual user' end as schema_definer_type 

from syscat.schemata order by schema_name

------------------------------------------------------------------------

https://www.imjhachandan.com/my-blog/categories/db2     ------------Issue

https://www.ibm.com/support/pages/how-do-i-find-out-disk-space-usage-managing-server-octigate-database-tables

***********************Verification Totals for Tables**************************

db2 "select substr(creator, 1, 15) as Schema,  count(*) as Total_Tables

from sysibm.systables

where creator not like ('SYS%')

  and Type not in ('N', 'V')

group by rollup (creator)

order by creator"

*********************** Verification Totals for Views***********************

db2 "select substr(creator, 1, 15) as Schema,  count(*) as Total_Views

from sysibm.systables  where creator not like ('SYS%')   and Type = 'V' group by rollup (creator) order by creator"

***********************Verification Totals for Indexes ***********************

db2 "select substr(tbcreator, 1, 15) as Schema,  count(*) as Total_Indexes

from sysibm.sysindexes a

left outer join sysibm.systables b on (a.tbcreator=b.creator and a.tbname=b.name)

where tbcreator not like ('SYS%')

  and type = 'T'

group by rollup (tbcreator)

order by tbcreator"

***********************Verification Totals for Primary Keys ***********************

db2 "select substr(dtbcreator, 1, 15) as Schema,  count(*) as Total_Primary_Keys

from sysibm.sysconstdep a

left outer join sysibm.systables b on (a.dtbcreator=b.creator and a.dtbname=b.name)

where dtbcreator not like ('SYS%')   and type = 'T'  group by rollup (dtbcreator)

order by dtbcreator"

***********************Verification Totals for Constraints ***********************

db2 "select substr(creator, 1, 15) as Schema,  count(*) as Total_Constraints

from sysibm.sysrels  where creator not like ('SYS%')  group by rollup (creator)  order by creator"

***********************Verification Totals for Columns ***********************

db2 "select substr(tbcreator, 1, 15) as Schema,  count(*) as Total_Columns

from sysibm.syscolumns a

left outer join sysibm.systables b on (a.tbcreator=b.creator and a.tbname=b.name)

where tbcreator not like ('SYS%')

  and type = 'T'

group by rollup (tbcreator)

order by tbcreator"

**************Verification Totals for Columns Length and Type *******************

db2 "select substr(a.tbcreator, 1, 15) as Schema,  substr(a.TBNAME, 1, 30) as TBNAME,   substr(a.NAME, 1, 30) as COLNAME,  a.COLTYPE,  a.LENGTH

from sysibm.syscolumns a

left outer join sysibm.systables b on (a.tbcreator=b.creator and a.tbname=b.name)

where tbcreator in (' ')

  and b.type = 'T'

order by tbcreator" > F_col_length.txt

db2 "select substr(a.tbcreator, 1, 15) as Schema,  substr(a.TBNAME, 1, 30) as TBNAME,   substr(a.NAME, 1, 30) as COLNAME,  a.COLTYPE,  a.LENGTH

from sysibm.syscolumns a

left outer join sysibm.systables b on (a.tbcreator=b.creator and a.tbname=b.name)

where tbcreator in (' ')

  and b.type = 'T'

order by tbcreator" > D_col_length.txt

***********************Verification Totals for Triggers ***********************

db2 "select substr(tbcreator, 1, 15) as Schema,  count(*) as Total_Triggers

from sysibm.systriggers

where tbcreator not like ('SYS%')

group by rollup (tbcreator)

order by tbcreator"

***********************Verification Totals for Functions ***********************

db2 "select substr(DEFINER, 1, 15) as Schema,  count(*) as Total_Functions

from sysibm.sysfunctions

where DEFINER not like ('SYS%')

group by rollup (DEFINER)

order by DEFINER"

***********************Verification Totals for Procedures ***********************

db2 "select substr(DEFINER, 1, 15) as Schema,  count(*) as Total_Procedures

from sysibm.SYSPROCEDURES

where DEFINER not like ('SYS%')

group by rollup (DEFINER)

order by DEFINER"

db2 "select substr(TBNAME, 1, 30) as TBNAME,  count(*) as Total_Columns

from sysibm.syscolumns a

left outer join sysibm.systables b on (a.tbcreator=b.creator and a.tbname=b.name)

where tbcreator='QUEST'

  and type = 'T'

  group by rollup (TBNAME)

order by TBNAME" | more

**************************CLOB\BLOB SIZE***********************

SELECT 

    TABSCHEMA AS SchemaName,

    TABNAME AS TableName,

    COLNAME AS ColumnName,

    TYPENAME AS ColumnType,

    LENGTH AS ColumnLength

FROM 

    SYSCAT.COLUMNS

WHERE 

    TYPENAME IN ('CLOB', 'BLOB')

    and TABSCHEMA = 'NTARIFF'

ORDER BY 

    SchemaName, TableName, ColumnName;

SELECT MAX(LENGTHB(LOB_COLUMN)) FROM TABLE; 

**************Snapshot monitor to get detailed performance data***************************

db2 get snapshot for database manager