DB2 huge IREF and lots of scans 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
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