DB2 9 provides a brand new schema SYSIBMADM with many new views in it. This is nothing new as same capability was available through SQL Table functions but this is more elegant.
If you worked with Oracle and liked its v$ views so this is very much similar to v$ views of Oracle in DB2.
You can find out lots of information through these view and it is worthwhile to go through them. Some of the SQL queries using these views are given below:
Show Connected applications to Database
SELECT AGENT_ID, SUBSTR(APPL_NAME,1,10) AS APPL_NAME,
AUTHID,
APPL_STATUS
FROM SYSIBMADM.APPLICATIONS
WHERE DB_NAME = 'SAMPLE';
Show top 5 most frequently run Dynamic SQL
SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S,
SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT
FROM SYSIBMADM.TOP_DYNAMIC_SQL
ORDER BY NUM_EXECUTIONS DESC
FETCH FIRST 5 ROWS ONLY;
List Tablespaces
SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) as TBSP_NAME,
TBSP_TYPE,TBSP_CONTENT_TYPE,
SUBSTR(TBSP_STATE,1,20) TBST_STATE
FROM SYSIBMADM.TBSP_UTILIZATION;
Bufferpool hit ratio
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME,
SUBSTR(BP_NAME,1,40) AS BP_NAME,
TOTAL_HIT_RATIO_PERCENT
FROM SYSIBMADM.BP_HITRATIO;
List the log utilization for the currently connected database
SELECT SUBSTR(DB_NAME,1,10) DB_NAME,
LOG_UTILIZATION_PERCENT
TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB
FROM SYSIBMADM.LOG_UTILIZATION;
Get all critical log messages logged in the last 24 hours
SELECT TIMESTAMP, SUBSTR(DBNAME,1,10) DBNAME,
SUBSTR(MSG,1,80) MSG
FROM SYSIBMADM.PDLOGMSGS_LAST24HOURS
WHERE MSGSEVERITY = 'C'
ORDER BY TIMESTAMP DESC;
Show Database paths
SELECT SUBSTR(TYPE,1,20) TYPE, SUBSTR(PATH,1,50) PATH
FROM SYSIBMADM.DBPATHS;
How much shared memory can be used / is being used by a DB2 data server
SELECT SUBSTR(NAME,1,20) NAME, INT(VALUE)*4/1024 MB
FROM SYSIBMADM.DBCFG
WHERE NAME = 'database_memory';
Find out list of MQT’s in your database
SELECT SUBSTR(CREATOR,1,20) CREATOR,
SUBSTR(NAME,1,50) NAME
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'S';
Find out list of MDC’s in your database
SELECT SUBSTR(A.TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(A.TABNAME,1,30) TABNAME
FROM SYSCAT.INDEXES A, SYSCAT.TABLES B
WHERE (A.TABNAME=B.TABNAME
AND A.TABSCHEMA=B.TABSCHEMA)
AND A.INDEXTYPE = 'BLOK';
Find out if Query Parallelism is being used
SELECT SUBSTR(NAME,1,20) NAME, SUBSTR(VALUE,1,20) VALUE
FROM SYSIBMADM.DBMCFG
WHERE NAME = 'intra_parallel';
Check if Connection Concentrator is being used
SELECT CASE WHEN int(b.value) > int(a.value)
THEN 'ENABLED' ELSE 'DISABLED'
END AS "CONNECTION CONCENTRATOR"
FROM sysibmadm.dbmcfg a, sysibmadm.dbmcfg b
WHERE a.name = 'max_coordagents'
AND b.name = 'max_connections';
List XML columns in a database
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(TABNAME,1,30) TABNAME,
SUBSTR(COLNAME,1,30) COLNAME
FROM SYSCAT.COLUMNS
WHERE TYPENAME = 'XML';
Check if any tables have row level compression enabled
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(TABNAME,1,30) TABNAME
FROM SYSCAT.TABLES
WHERE COMPRESSION IN ('R','B');
Find out tables using Label Based Access Control (LBAC)
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(TABNAME,1,30) TABNAME
FROM SYSCAT.TABLES
WHERE SECPOLICYID > 0;
To determine if HADR is turned on
SELECT SUBSTR(NAME,1,20) NAME,
CASE WHEN VALUE = 'STANDARD'
THEN 'NO' ELSE 'YES' END
FROM SYSIBMADM.DBCFG
WHERE NAME = 'hadr_db_role';
Show Physical space used by tables
SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,
SUBSTR(TABNAME,1,30) TABNAME,
SUM(DATA_OBJECT_P_SIZE) DATA_OBJECT_P_SIZE,
SUM(INDEX_OBJECT_P_SIZE) INDEX_OBJECT_P_SIZE,
SUM(LONG_OBJECT_P_SIZE) LONG_OBJECT_P_SIZE,
SUM(LOB_OBJECT_P_SIZE) LOB_OBJECT_P_SIZE,
SUM(XML_OBJECT_P_SIZE) XML_OBJECT_P_SIZE
FROM SYSIBMADM.ADMINTABINFO
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA, TABNAME;
Show Database size and maximum capacity
CALL GET_DBSIZE_INFO(?, ?, ?, 0);