Using Statistical Views in DB2 to improve query performance

In a previous post i mentioned about how to find slow queries in DB2 and this has proved very useful. By looking through a lot of different bits of SQL that were consistently slow i found a common bit of SQL was used in lots of places.

Here is the common bit of SQL I was finding in lots of places...

...
LEFT OUTER JOIN (  
 eone.TPM_USER_ADRS_DTL UAD
  INNER JOIN eone.TSCH_LKP_DTL LKP
       ON UAD.AddressType = LKP.LOOKUPDATAID 
       AND LKP.LOOKUPID = 2 
      AND LKP.ISDELETED = '0' 
       AND LKP.LOOKUPCode = 'C'
  ) ON PCTD.CONTACTID = UAD.USERID
...

Just these joins caused the SQL to take 1-2 minutes to run.

My first thought is to check to see if an index would help but unfortunately it didn't. So i logged a ticket in our Jira for each different bit of SQL and waited patiently for someone to fix each and every place this bit of SQL was used. However this can take months to be fixed and released due to our internal development and release processes.

So after a couple of months with no progress i decided to have a look at the SQL again and see if i could help DB2 pick a better access plan using a statistical view.

I created the following statistical view...

CREATE VIEW EONE.Statistical_User_Address_View
    ( Col1 )
AS
select 1 from 
       eone.TPM_USER_ADRS_DTL UAD
       INNER JOIN eone.TSCH_LKP_DTL LKP
       ON UAD.AddressType = LKP.LOOKUPDATAID 
          AND LKP.LOOKUPID = 2 
          AND LKP.ISDELETED = '0' 
          AND LKP.LOOKUPCode = 'C'
WITH NO ROW MOVEMENT;

--This makes the view a statistical view...
ALTER VIEW EONE.Statistical_User_Address_View ENABLE QUERY OPTIMIZATION;

runstats on table eone.TPM_USER_ADRS_DTL WITH DISTRIBUTION AND DETAILED INDEXES ALL;
runstats on table eone.tsch_lkp_dtl WITH DISTRIBUTION AND DETAILED INDEXES ALL;
runstats on table EONE.Statistical_User_Address_View WITH DISTRIBUTION;

After creating this view all the SQL statements that matched this views joins and where clauses were now taking just a couple of seconds to run. A statistical view won't cause any issues with existing functionality and it won't require much testing so its very quick and easy to apply to production.

This statistical view is a great temporary solution to a performance problem like this. However i would only recommend it as a temporary solution because fixing the SQL is always a better option.

Also if you want to find all your statistical views in the SYSCAT.TABLES table then you need to run this...

--The 13th charater in the PROPERTY column will be Y for statistical views...
select * from SYSCAT.TABLES where TYPE='V' and substr(PROPERTY,13,1)='Y';

Find Slow SQL Running in DB2

At work we have serval web applications pointing to the same database and a lot of the SQL isn't written optimally. So I wrote a simple scheduler task in Java to check the DB for slow SQL every 15 seconds and then email me the results.

Originally I was using the following SQL...

select ELAPSED_TIME_MIN, AGENT_ID, APPL_NAME, 
APPL_STATUS, AUTHID, INBOUND_COMM_ADDRESS,STMT_TEXT 
from SYSIBMADM.LONG_RUNNING_SQL 
where STMT_TEXT is not null and ELAPSED_TIME_MIN >= 1

This seemed to work well at first but I found it was reporting some SQL as running for more than a minute but it was actually taking as little as 8 seconds!

I couldn't find any information about how the ELAPSED_TIME_MIN value was calculated in the LONG_RUNNING_SQL view, so I had to come up with a better alternative and I ended up with the following SQL statement...

select 
timestampdiff (2, char(coalesce(s.STMT_STOP,s.SNAPSHOT_TIMESTAMP)-s.STMT_START)) as STMNT_SEC, 
timestampdiff (2, char(coalesce(a.UOW_STOP_TIME,s.SNAPSHOT_TIMESTAMP)-a.UOW_START_TIME)) as UOW_SEC, 
a.LOCK_WAIT_TIME, s.AGENT_ID, s.SECTION_NUMBER, 
ai.APPL_NAME, ai.APPL_STATUS, ai.PRIMARY_AUTH_ID, 
s.QUERY_COST_ESTIMATE, s.STMT_TEXT
from  SYSIBMADM.SNAPSTMT s,SYSIBMADM.SNAPAPPL a, SYSIBMADM.SNAPAPPL_INFO ai
where a.agent_id=s.agent_id and  a.agent_id=ai.agent_id
and s.STMT_TEXT is not null 
and timestampdiff (2, char(coalesce(s.STMT_STOP,s.SNAPSHOT_TIMESTAMP)- s.STMT_START)) >= 45 
and ai.APPL_STATUS not in ('UOWWAIT')
order by a.AGENT_ID, s.SECTION_NUMBER

This gives a much more accurate picture of long running SQL and SNAPSTMT and SNAPAPPL have lots of useful information that you could get out if you needed to.

Here is a typical email i would receive...

ADMIN_MOVE_TABLE and it's limitations

At work I upgraded our DB2 installs to 9.7 last year and 1 of the new features in 9.7 that looked interesting was the ADMIN_MOVE_TABLE procedure.

This procedure claimed to move tables online. Although it does do this, it has some limitations that you need to be aware of...

Referential Constraints
The major one is that it doesn't handle referential constraints. So you need to drop them on the table you want to move and also any FK constraints on other tables that link to the PK of the table you want move. Then when the move is finished you would need to manually create them again. This limitation can be an issue if you rely on the constraints to CASCADE a delete or update.

Comments
I also found that the comments on a table weren't copied to the new table, including column comments.

Privileges
The user that you use to run the procedure needs to have SECADM rights otherwise it won't copy the GRANTS on the table. I know this isn't a limitation but i thought it was worth mentioning.