Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    10

    Unanswered: DB usage statistics

    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...

  2. #2
    Join Date
    Oct 2003
    Location
    Curitiba - PR - Brazil
    Posts
    18
    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;
    Norberto Gasparotto Filho

    IBM Cert. DB2 v9 DBA
    IBM Cert. DB2 v9 App. Developer
    SCJP 5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •