• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Category Archives: Mon_get_table_usage_list

Usage Lists – Big Brother is watching, but only where he is looking

April 22, 2013 12:00 pm / 1 Comment / dangerousDBA

From my holiday reading and from watching the most excellent DB2 Night Show and specifically an episode that was done a while back by Iqbal Goralwalla of Triton Consulting (@iqbalgoralwalla) on “DB2 LUW 10.1 Cool Features No One is Talking About” I have come across Usage Lists for tables and indexes in DB2.

Why use – Usage Lists

Have you got a table or an index and you never know when or how it is used; Stored Procedures, screens, systems or dynamic SQL, or do you want to monitor the SQL that runs against a table or index then and what work is done then this could save you ploughing through a lot of code, but means it won’t be an instant fix as the code has to run.

Usage Lists

Usage Lists in DB2 essentially allow you to monitor the SQL that runs against a tables or indexes that you have identified that you want monitoring. This does not come without costs and a list of the GOTCHA’s can be found on the “Notes” section of the page here and in “Chapter 26. Usage lists” of the “Preparation Guide for Exam 611” (not sure on how much these will come up in the exam?)

GOTCHA

  • Please note in the above paragraph the words “you have identified that you want monitoring” as you will see you will only get the stats if the table is monitored and you have set up the individual monitor!

Usage Lists – Creation

Not going to lie there is a page of the IBM Info Centre that has a version of this information but it is a little hard to find unless you type in the exact words but it can be found here, as you can see from the title then it is not really close to usage lists!

First you need to set a database configuration parameter MON_OBJ_METRICS:

db2 UPDATE DATABASE CONFIGURATION USING MON_OBJ_METRICS EXTENDED

On the page mentioned above then it says you need to set this so that "statistics are collected for each entry in the usage list" but on the small scale of the testing that I did I have not found any difference in captured data.

Then for each table that you want to monitor then you need to run at a minimum:

db2 CREATE USAGE LIST {Some Memorable Name} FOR TABLE {Schema}.{Table}

There are other parts to this command that can be found here and it has some useful parts like the ability too "turn its self off" when a certain number of different statements have been run by doing something like:

db2 CREATE USAGE LIST {Some Memorable Name} FOR TABLE {Schema}.{Table} LIST SIZE {Some Number} WHEN FULL DEACTIVATE

Or a rolling list, but this might create difficulties if you want repeatability:

db2 CREATE USAGE LIST {Some Memorable Name} FOR TABLE {Schema}.{Table} LIST SIZE {Some Number} WHEN FULL WRAP

From testing if you unless you specify a LIST SIZE then the collection will continue for as long as the list is active, which is the next statement to run to get it too work.

db2 SET USAGE LIST {Some Memorable Name} STATE = ACTIVE

And to disable it again:

db2 SET USAGE LIST {Some Memorable Name} STATE = INACTIVE

So above is a quick look at how to get this to work and the links to get a better lets move on to look at what it collects.

Usage Lists - The output

The output is quite useful and the full output of the MON_GET_TABLE_USAGE_LIST table function can be found here. It is also a little disappointing because this does not return the statement only an identifier (EXECUTABLE_ID) that you can supply to the MON_GET_PKG_CACHE_STMT table function which info for this can be found at here.

You can do something like this and potentially get a lot of data on what your MON_GET_TABLE_USAGE_LIST captured and the statements from MON_GET_PKG_CACHE_STMT when joined together:


SELECT *
FROM TABLE(MON_GET_TABLE_USAGE_LIST(NULL,{Some Memorable Name},0)) A 
   LEFT JOIN
     TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) B
    ON A.EXECUTABLE_ID = B.EXECUTABLE_ID

Looking at a version that yields some more focused information:


SELECT B.STMT_TEXT AS SQL_STATEMENT, 
       A.LAST_UPDATED AS LAST_RUN,
       A.NUM_REF_WITH_METRICS AS NO_TIMES_RUN,
       A.ROWS_READ,
       A.ROWS_INSERTED,
       A.ROWS_UPDATED,
       A.ROWS_DELETED,
       A.LOCK_WAIT_TIME,
       A.OBJECT_DATA_L_READS AS BUFFERPOOL_READS,
       A.OBJECT_DATA_P_READS AS NON_BUFFERPOOL_READS
FROM TABLE(MON_GET_TABLE_USAGE_LIST(NULL,{Some Memorable Name},0)) A 
   INNER JOIN
     TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) B
    ON A.EXECUTABLE_ID = B.EXECUTABLE_ID

This enables you to see how efficient the query is in terms of how often it is run and the number of times, with the work that they did and how much of the data resides in the bufferpools (BUFFERPOOL_READS) and how much has to come from disk (NON_BUFFERPOOL_READS). As you can see from my not very good test system query tracking below:


 SQL_STATEMENT                                                                                                 LAST_RUN             NO_TIMES_RUN     ROWS_READ     ROWS_INSERTED     ROWS_UPDATED     ROWS_DELETED     LOCK_WAIT_TIME     BUFFERPOOL_READS     NON_BUFFERPOOL_READS    
 ------------------------------------------------------------------------------------------------------------  -------------------  ---------------  ------------  ----------------  ---------------  ---------------  -----------------  -------------------  ----------------------- 
 insert into {schema}.{table} ({field},{field},{field}) VALUES ({value},{value},{value})  21/04/2013 10:10:54  1                0             1                 0                0                0                  1                    0                       

As you can see here this update all happened inside the bufferpool as it was very small on a table with no data. If you can find a statement that you are interested in because it has a large amount of non logical data reads you can use the captured code and pass it through db2advis to get suggestions on how to make the query better with indexes etc. Please see my blog post on db2advis if you are un-familar with it.

The future

I am currently looking at automating db2advis and monitoring its suggestions. Which once you are capturing the SQL becomes a lot easier.



Posted in: DB2, DB2 Administration, DB2 Built in commands, DB2 Development, DB2 Maintenance, DB2 Table Functions, IBM, IBM DB2 LUW, MON_GET_PKG_CACHE_STMT, MON_GET_TABLE_USAGE_LIST / Tagged: Create Usage List, DB2, DB2 Administration, DB2 Development, db2advis, IBM DB2 LUW, MON_GET_PKG_CACHE_STMT, MON_GET_TABLE_USAGE_LIST, Stored Procedures, Usage List, Usage List Status, V10.1

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2023 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress