IBM Support

'STMTID' the 2nd episode - Understanding by examples

Technical Blog Post


Abstract

'STMTID' the 2nd episode - Understanding by examples

Body

Some years ago, I wrote a blog about using 'STMTID' to track long term performance changes for SQLs.
( Blog link : 'STMTID' - The key for long term performance tracking of an SQL )
Has anyone of you ever applied to your system ?

Intermittently, I have gotten personal contacts asking to give some examples showing how it works.
So here let me introduce very simple examples just to share what I meant.

To remind, 'mon_get_pkg_cache_stmt' function has kinds of ID columns.
- EXECUTABLE_ID : It always changes whenever the same SQL is compiled into package cache.
- STMT_PKG_CACHE_ID : internal package cache identifier (ID) for a dynamic SQL statement
                                           Usually does not change even after multiple compilation and restarting database.
                                           But from my experience on a heavy workload system, it appears not to be consistent when restarting occurs.
                                          ( On this blog, we are not interested about why. )
                                           By the way, it's still be able to be used for same SQL tracking purpose, but no guarantee to give the same value always.
- STMTID : The hash key value that identifies normalized statement text that is associated with a section.
                  This is supported since DB2 V10.5 FP3.

 

1. Firstly, let's see how it looks like. I compared IDs before and after DB reactivation on a marked SQL.
 

1-1) V9.7 FP11 : On V9.7, there is not STMTID. Therefor looking at others.
   - You can see EXECUTABLE_ID changes for the same SQL whereas 'STMT_PKG_CACHE_ID' is same.
 
$ cat mon_get_stmt_id_test.sql
select
'JUNSU' as FILTER, --to filter my SQL temporarily
executable_id,
STMT_PKG_CACHE_ID,
--semantic_env_id, -- not available in V9.7
--STMTID,          -- not available in V9.7
varchar(STMT_TEXT,50) as STMT_TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1))
where STMT_TEXT like '%JUNSU%';
 
$ db2 terminate;db2 deactivate db JSDB;db2 activate db JSDB;db2 connect to JSDB
 
$ db2 -tf mon_get_stmt_id_test.sql |egrep -v "SQL0445|truncated|warning"
 
FILTER EXECUTABLE_ID                                                       STMT_PKG_CACHE_ID    STMT_TEXT                                         
------ ------------------------------------------------------------------- -------------------- --------------------------------------------------
JUNSU  x'0000000100000000000000000000000200000000000220170803230653421702'           4353490944 select 'JUNSU' as FILTER, --to filter my SQL tempo

 
$ db2 terminate;db2 deactivate db JSDB;db2 activate db JSDB;db2 connect to JSDB
 
$ db2 -tf mon_get_stmt_id_test.sql |egrep -v "SQL0445|truncated|warning"       
 
FILTER EXECUTABLE_ID                                                       STMT_PKG_CACHE_ID    STMT_TEXT                                         
------ ------------------------------------------------------------------- -------------------- --------------------------------------------------
JUNSU  x'0000000100000000000000000000000700000000000220170803230728363772'           4353490944 select 'JUNSU' as FILTER, --to filter my SQL tempo
 
 
1-2) V10.5 FP8 : STMTID remains the same.
 
$ cat mon_get_stmt_id_test.sql
select
'JUNSU' as FILTER, --to filter my SQL temporarily
executable_id,
STMT_PKG_CACHE_ID,
semantic_env_id,
STMTID,
varchar(STMT_TEXT,50) as STMT_TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1))
where STMT_TEXT like '%JUNSU%';
 
$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample
 
$ db2 -tf mon_get_stmt_id_test.sql |egrep -v "SQL0445|truncated|warning"               
 
FILTER EXECUTABLE_ID                                                       STMT_PKG_CACHE_ID    SEMANTIC_ENV_ID      STMTID               STMT_TEXT                                         
------ ------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------------------
JUNSU  x'0000000100000000000000000000000200000000000220170803224640863187'           4307615744 -4484546821111269827  -740123849692862110 select 'JUNSU' as FILTER, --to filter my SQL tempo
 
$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample
 
$ db2 -tf mon_get_stmt_id_test.sql |egrep -v "SQL0445|truncated|warning"               
 
FILTER EXECUTABLE_ID                                                       STMT_PKG_CACHE_ID    SEMANTIC_ENV_ID      STMTID               STMT_TEXT                                         
------ ------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------------------
JUNSU  x'0000000100000000000000000000000200000000000220170803224744256550'           4307615744 -4484546821111269827  -740123849692862110 select 'JUNSU' as FILTER, --to filter my SQL tempo

 

2. So let's say that we are tracking an interested SQL.
    Here I created sample tables with 1 million rows per each and made a change creating index.
    This is just an example to give you the idea what to do in real database furthermore.

2-1. creating tables and load data for the test.

$ db2 "create table A ( key int not null , f1int int , f2char20 char(20) , f3char30  char(30) , f4int int , f5char30 char(30) ) "
$ db2 -v "load from data4.unl of del modified by coldel| replace into AAA nonrecoverable"

$ db2 "create table BBB ( key int not null , f1int int , f2char20 char(20) , f3char30  char(30) , f4int int , f5char30 char(30) ) "
$ db2 -v "load from data5.unl of del modified by coldel| replace into BBB nonrecoverable"

 

2-2. Run a SQL to track for sample.

Run the SQL to track.
$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key"


2-3. Get STMTID of the SQL.

STMTID is -3791034270387023455

$ db2 "select executable_id, STMT_PKG_CACHE_ID, semantic_env_id, STMTID, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMT_TEXT like '%AAA%'"

EXECUTABLE_ID                                                       STMT_PKG_CACHE_ID    SEMANTIC_ENV_ID      STMTID               STMT_TEXT                                         
------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------------------------------
x'000000010000000000000000000001D700000000000220170804120245954359'          12894732288 -4484546821111269827  2615904627939437315 select executable_id, STMT_PKG_CACHE_ID, semantic_
SQL0445W  Value "select executable_id, STMT_PKG_CACHE_ID, semantic_env_id, ST"
has been truncated.  SQLSTATE=01004

x'000000010000000000000000000001D600000000000220170804120236465285'          12915376128  4310297683605654029 -3791034270387023455 select * from AAA , BBB where AAA.key < 50000 and
SQL0445W  Value "select * from AAA , BBB where AAA.key < 50000 and AAA.key = "
has been truncated.  SQLSTATE=01004


2-4. Get information about the SQL.
       Here, I'm just checking Total time and Rows_read for example.
       ( But there are lots of metrics columns you may be interested. ) 

 

$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample

$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key"

$ db2 "select TOTAL_ACT_TIME,TOTAL_ACT_WAIT_TIME,ROWS_READ, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMTID = -3791034270387023455"

TOTAL_ACT_TIME       TOTAL_ACT_WAIT_TIME  ROWS_READ            STMT_TEXT                                         
-------------------- -------------------- -------------------- --------------------------------------------------
                2878                 2183              2000000 select * from AAA , BBB where AAA.key < 50000 and


2-5. Made a sample change (index creation).

$ db2 "create index i1 on AAA ( key )"
$ db2 -v runstats on table $USER.AAA with distribution and detailed indexes all
$ db2 "create index i2 on BBB ( key )"
$ db2 -v runstats on table $USER.BBB with distribution and detailed indexes all

$ db2 "select * from AAA , BBB where AAA.key < 50000 and AAA.key = BBB.key”

$ db2 terminate;db2 deactivate db sample ; db2 activate db sample;db2 connect to sample

$ db2 "select TOTAL_ACT_TIME,TOTAL_ACT_WAIT_TIME,ROWS_READ, varchar(STMT_TEXT,50) as STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) where STMTID = -3791034270387023455"

TOTAL_ACT_TIME       TOTAL_ACT_WAIT_TIME  ROWS_READ            STMT_TEXT                                         
-------------------- -------------------- -------------------- --------------------------------------------------
                 346                  346                   76 select * from AAA , BBB where AAA.key < 50000 and 

 

Furthermore, you may have the followings in your mind now.

- Collecting top SQLs with MON_GET_PKG_CACHE_STMT periodically.
  Then maintain the information into a database so that you can query when necessary.
- Then see the performance trend of SQLs and check when it changes.
- Etc.

Thank you for reading this blog and have a nice weekend.
Jun.



 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11139980