DB2 huge IREF and lots of scans

Question

I received a task from one of my colleagues to check why he always receives high CPU load alerts. I don't have too much experience with databases and I need some clarifications. I created a script to capture the first 5 threads that are using most of the CPU when the alerts are triggered. The order is almost always the same, first three are db2agents and the other 2 are db2pfchr. From my knowledge, I found nothing wrong with the SQL statements so I checked for table scans and IREF.

What I noticed immediately was the difference between rows read and rows selected which looks like this for example:

Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 1329
Rows selected                              = 714
Rows read                                  = 3903430587518
Rows written                               = 3471

First 3 biggest table scan values are 26586532, 15538513 and 942177. The first table has one index that was never used, the second table has 1 index that was used around 70k times and the third table has no index.

So, my obvious guess is that indexes need to be added in order to avoid table scans

Since I never added an index in my life and must read more about it, I needed to be sure about my assumptions and maybe there something else to check? Also, all values in the queries are "?"... hidden? Like UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?

This is an OLTP DB, and it's about DB2 V10.5 on RHEL IREF - Index Read Efficiency = ROWS READ/ROWS RETURNED

Answer 1

You can find out which statement(s) that is causing most read rows:

select num_executions, rows_read, stmt_text
from sysibmadm.snapdyn_sql
order by 2 desc 
fetch first 5 rows only

If UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ? is among the culprits you likely need to add an index on X.AB:

CREATE INDEX ... ON X (AB)
ALLOW REVERSE SCANS
COLLECT SAMPLED DETAILED STATISTICS  

There's like a zillion possible options for an index, but something like above is usually good enough. If you have access to db2exfmt you can compare plans before and after like:

db2 "set explain mode explain"
db2 "UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?"
db2exfmt -d <db> -g -1 -o before.exfmt
db2 "set explain mode no"
db2 "create index ..."
db2 "set explain mode explain"
db2 "UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ?"
db2exfmt -d <db> -g -1 -o after.exfmt
db2 "set explain mode no"

All from memory, so there might be a glitch or two in there

Answer 2

The ?'s are Parameter markers. Read abut them here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0020295.html

I would suggest you add an index on column AB

Also consider using IBM Data Server Manager to monitor you database. It includes the ability to see explain plans and also invoke the design advisor