I have to collect some statistics about DB2 databse usage. Eg : I want to find out the space usage by 1000 transactions of a particular kind.
What should be the correct approach for doing this?

What I have planned is :
call SYSPROC.GET_DBSIZE_INFO(?,?,?,0).
followed by
select * from SYSTOOLS.STMG_DBSIZE_INFO.

Looks OK?? Any suggestions are welcome...

Hi,

Here follows a list of useful info you can get from DB2 v9, using the new SYSIBMADM views. They won't help with your particular 1000 transactions issue, but can help with other needs you may have.

Hope that helps.


****************************************
** v9 SYSIBMADM useful views

**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;