DB2 Database Queries
**************** check Db2 version ******************
$ db2level
***************************Database SIZE******************************************
db2 "CALL GET_DBSIZE_INFO(?, ?, ?, 0)"
-----Disk Capacity
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
*******************Check the Privileges of the User ***************************
Check the Privileges of the User: To verify the current privileges of DMSUSER, you can query the SYSCAT.ROUTINEAUTH table. This will help ensure that the user doesn’t already have the necessary permissions.
SELECT GRANTEE, GRANTOR,EXECUTEAUTH
FROM SYSCAT.ROUTINEAUTH
WHERE GRANTEETYPE = 'U' AND GRANTEE = 'DMSUSER';
Check DBADM & SECADM Authority
The DBADM (Database Administrator) authority allows a user to manage the database, including granting certain privileges.Use this query to check if a user has DBADM & SECADM Authority :
--If DBADMAUTH returns 'Y' for the user, then they have DBADM authority.
--If SECMADMAUTH returns 'Y', then the user has SECADM authority.
--If it returns 'N', the user does not have DBADM & SECMADMAUTH .
SELECT GRANTEE, DBADMAUTH,SECURITYADMAUTH
FROM SYSCAT.DBAUTH
WHERE GRANTEE = 'DMSUSER';
Check Role Memberships for DBADM and SECADM Authorities
If the user has been assigned to a role that includes DBADM or SECADM, you can verify this by checking role membership:
SELECT * FROM SYSCAT.ROLEAUTH WHERE GRANTEE = 'DMSUSER';
Verify Privileges Using AUTH_LIST_AUTHORITIES_FOR_AUTHID
Another option is to use the AUTH_LIST_AUTHORITIES_FOR_AUTHID administrative view, which provides a consolidated list of authorities for a specific user. This view checks both directly granted privileges and those inherited from roles.Verify Privileges Using AUTH_LIST_AUTHORITIES_FOR_AUTHID
SELECT AUTHORITY, D_USER, ROLE_USER, D_GROUP, ROLE_GROUP
FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('DMSUSER', 'U')) AS T
WHERE AUTHORITY IN ('DBADM', 'SECADM');
Granting DBADM or SECADM Authority
If the user does not currently have DBADM or SECADM and you need to grant these, you can do so with the following statements (requires SECADM authority to execute):
-- Grant DBADM
GRANT DBADM ON DATABASE TO USER DMSUSER;
-- Grant SECADM
GRANT SECADM ON DATABASE TO USER DMSUSER;