$ db2level
db2 "select bpname,pagesize,npages from syscat.bufferpools"
Catalog tablespace
User tablespace
Temporary tablespace
SELECT VALUE FROM SYSIBMADM.DBCFG WHERE NAME = 'codeset';
Server Name:
SELECT host_name FROM sysibmadm.env_sys_info
SELECT VARCHAR(HOST_NAME, 50) FROM SYSIBMADM.ENV_SYS_INFO;
Database Name
SELECT CURRENT SERVER AS DATABASE_NAME FROM SYSIBM.SYSDUMMY1;
Schema Name
SELECT SCHEMANAME FROM SYSCAT.SCHEMATA;
Table Name
SELECT TABSCHEMA FROM SYSCAT.TABLES WHERE TABNAME = 'your_table_name';
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
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
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
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;
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
db2 -x "SELECT distinct (trim(T.TABSCHEMA))|| '.' ||(trim(T.TABNAME)) from SYSCAT.TABLES T where tabschema IN ('NTARIFF') and TYPE='T' order by 1" > TABLIST.lst
for i in `cat TABLIST.lst`
do
db2 -x "select trim('$i'), trim(count(*)) from $i"
done
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
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"
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"
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"
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"
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"
db2 "SELECT REF.REFTABSCHEMA AS SCHEMA_NAME, REF.REFTABNAME AS TABLE_NAME, TRIM(KEY.COLNAME) AS COLUMN_NAME, REF.CONSTNAME AS FK_CONST_NAME, REF.TABNAME AS FOREIGN_TABLE_NAME FROM SYSCAT.REFERENCES REF LEFT OUTER JOIN SYSCAT.KEYCOLUSE KEY ON KEY.TABSCHEMA = REF.TABSCHEMA AND KEY.TABNAME = REF.TABNAME AND KEY.CONSTNAME = REF.CONSTNAME WHERE REF.TABSCHEMA IN ('XN') ORDER BY REF.REFTABSCHEMA, REF.REFTABNAME, REF.CONSTNAME, REF.TABNAME, TRIM(KEY.COLNAME)"
db2 "select TABSCHEMA,CONSTNAME,TABNAME,TYPE,ENFORCED from syscat.tabconst where tabschema = 'Test' and tabname = 'FLIGHT' and constname = ? "
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"
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
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"
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"
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
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;
STEP # 1
db2 -x "
SELECT substr(R.reftabschema,1,20) AS P_Schema,
substr(R.reftabname,1,40) AS PARENT,
substr(R.tabschema,1,20) AS C_Schema,
substr (R.tabname,1,40) AS CHILD,
substr(R.constname,1,80) AS CONSTNAME,
substr(LISTAGG(C.colname,', ') WITHIN
GROUP (
ORDER BY C.colname),1,40) AS FKCOLS
FROM syscat.references R,
syscat.keycoluse C
WHERE R.constname = C.constname
AND R.tabschema = C.tabschema
--AND R.tabname = C.tabname
AND R.tabschema not like '%sys%'
GROUP BY R.reftabschema,
R.reftabname,
R.tabschema,
R.tabname,
R.constname" > FOREIGN_KEY_RI.txt
STEP # 2 Remove the exist file
rm -f RI_Not_Enforced.sql
rm -f RI_Enforced.sql
STEP # 3 Run below script generate two files (RI_Not_Enforced & RI_Enforced)
cat FOREIGN_KEY_RI.txt | while read P_SCHEMA PARENT C_SCHEMA CHILD CONSTNAME FKCOLS
do
echo "ALTER TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $CONSTNAME NOT ENFORCED;" >> RI_Not_Enforced.sql
echo "Alter TABLE $C_SCHEMA.$CHILD ALTER FOREIGN KEY $CONSTNAME ENFORCED;" >> RI_Enforced.sql
done
STEP # 4: Run Not_Enforced
db2 -tvf RI_Not_Enforced.sql
STEP # 5
Truncate/Load data into tables
STEP # 6
db2 -tf RI_Enforced.sql
DB2 INTRIGITY CHECK
db2 -x "select 'REORG table ' || trim(tabschema) || '.' || trim(tabname) || ' ; ' from SYSIBMADM.ADMINTABINFO where REORG_PENDING='Y'"
db2 -x "select 'db2 set integrity for ' || trim(tabschema) || '.' || trim(tabname) || ' ALL IMMEDIATE UNCHECKED ' from syscat.tables where STATUS='C' and type='T'"
db2 -x "select 'db2 set integrity for ' || trim(tabschema) || '.' || trim(tabname) || ' immediate checked ' from syscat.tables where status<>'N'"
-------------------SINGLE INTRIGITY -----------------
db2 -x "select 'REORG table ' || trim(tabschema) || '.' || trim(tabname) || ' ; ' from SYSIBMADM.ADMINTABINFO where REORG_PENDING='Y'"
db2 -x "select 'db2 set integrity for ' || trim(tabschema) || '.' || trim(tabname) || ' ALL IMMEDIATE UNCHECKED ' from syscat.tables where STATUS='C' and type='T'"
db2 -x "select 'db2 set integrity for ' || trim(tabschema) || '.' || trim(tabname) || ' immediate checked ' from syscat.tables where status<>'N'"
-------------------SINGLE INTRIGITY -----------------
db2 "load from /dev/null of del terminate into NTARIFF.NTP_AUTH_DETAILS1"
Monitor performance metrics from tables and to query details
SELECT
NUM_EXECUTIONS,
STMT_EXEC_TIME,
TOTAL_CPU_TIME, ROWS_READ
,substr(STMT_TEXT,1,20) AS STMT
FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T
where NUM_EXEC_WITH_METRICS>0
ORDER BY TOTAL_CPU_TIME desc fetch first 10 rows ONLY;
db2 get snapshot for database manager