DB2 schema operation details

Question

I am trying to find out find out number of operation happened for a particular schema in following format

schemaname operation_count
ibmschema  2

operation can be CREATE TABLE ,INSERT ,UPDATE ,SELECT ,DELETE ,DROP TABLE

Is there amy metadata table to get such info in DB2 10 LUW

Answer 1

Db2 does not (by default) record every operation that occurs. In an OLTP system with millions of transactions a day, the overhead of recording every statement would be a significant overhead

Db2 does have in-memory statistics on activity, and this data is available in the MON table functions. The stats are cumulative since the instance was last restarted, with the exception of things like the package cache stats that are just for statements currently in the package cache.

You can create event monitors to capture more detailed information. See the Knowledge Center for information on those, or consider using Data Server Manager to help capture this extra level of data.

If you need this information for audit requirements, consider the db2 audit facility, which has protections against unauthorized tampering.

Still, if you want counts of rows inserted (since last instance restart), by schema, that is quickly derivable from MON_GET_TABLE. E.g.

SELECT V.* FROM
(   SELECT
        TABSCHEMA
    ,   SUM(ROWS_READ)     ROWS_READ
    ,   SUM(ROWS_INSERTED) ROWS_INSERTED
    ,   SUM(ROWS_UPDATED)  ROWS_UPDATED
    ,   SUM(ROWS_DELETED)  ROWS_DELETED 
    FROM
        TABLE(MON_GET_TABLE(NULL,NULL,-2))
    GROUP BY
        TABSCHEMA
), TABLE(VALUES 
    (TABSCHEMA, 'ROWS_READ'    ,ROWS_READ)
,   (TABSCHEMA, 'ROWS_INSERTED',ROWS_INSERTED )
,   (TABSCHEMA, 'ROWS_UPDATED' ,ROWS_UPDATED )
,   (TABSCHEMA, 'ROWS_DELETED' ,ROWS_DELETED )
) AS V(SCHEMA, OPERATION, COUNT)
ORDER BY 1,2

which would return e.g.

 SCHEMA   OPERATION     COUNT
 -------- ------------- ---------
 PAUL     ROWS_DELETED          0
 PAUL     ROWS_INSERTED        10
 PAUL     ROWS_READ     321954698
 PAUL     ROWS_UPDATED          0
 SYSIBM   ROWS_DELETED       4883
 SYSIBM   ROWS_INSERTED      4954
 SYSIBM   ROWS_READ        575652
 SYSIBM   ROWS_UPDATED        340

If you want counts of statements, you will hit the issue that many statements can cross schemas. There is no easy way to know the "schema" for a statement (short of parsing the SQL yourself). Also statements are not tagged by operation (you can INSERT, UPDATE, DELETE and SELECT in one statement if you are using changed table references, or triggers etc). So you end up havng to do some simple classification such as this

SELECT SUM(NUM_EXECUTIONS) EXECS, OPERATION FROM ( 
    SELECT NUM_EXECUTIONS
    ,      CASE 
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'INSERT' THEN 'INSERT'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'UPDATE' THEN 'UPDATE'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'SELECT' THEN 'SELECT'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'VALUES' THEN 'VALUES'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'CREATE' THEN 'CREATE'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,6)) = 'DELETE' THEN 'DELETE' 
        WHEN UPPER(SUBSTR(STMT_TEXT,1,5)) = 'MERGE'  THEN 'MERGE' 
        WHEN UPPER(SUBSTR(STMT_TEXT,1,3)) = 'SET'    THEN 'SET'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,4)) = 'DROP'   THEN 'DROP'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,4)) = 'WITH'   THEN 'WITH'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,4)) = 'CALL'   THEN 'CALL'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,4)) = 'LOCK'   THEN 'LOCK'
        WHEN UPPER(SUBSTR(STMT_TEXT,1,7)) = 'DECLARE' THEN 'DECLARE'
        ELSE UPPER(SUBSTR(STMT_TEXT,1,6)) END OPERATION
    FROM
        TABLE(MON_GET_PKG_CACHE_STMT ( '', NULL, NULL, -2)) AS T
) GROUP BY OPERATION

which might return e.g.

 EXECS OPERATION
 ----- ---------
   123 CALL
    78 DECLARE
    12 DELETE
     4 INSERT
    34 LOCK
 63036 SELECT
   964 SET
 21218 UPDATE
     3 VALUES
   100 WITH

( but does not cope with, e.g. statements that start with a comment. When you move to Db2 11.1 you could use REGEXP_LIKE to strip out any leading comments from the match)