DB2 tuning

Add logs to monitor the time spent on specific sql script

1. prepare a log table to store the timestamps in testing environment

drop table ebiz1.owen_log;

2. modify the sproc to populate the log table with message and timestamp for each queries or other DMLs(insert, update, delete, open cursor)

insert into ebiz1.owen_log(log) values('section 1');

Query 1 in sproc

insert into ebiz1.owen_log(log) values('section 2');

Query 2 in sproc

insert into ebiz1.owen_log(log) values('section 3');

...
...
...

3. check the log table

select * from ebiz1.owen_log order by time with ur;

======================================================================================================================
load sp

db2 -td@ -f S_Q2_BRWS_P_CODE.SQL

======================================================================================================================
get sp package

db2 select substr(char(r.lib_id+10000000),2) from syscat.routines r, syscat.procedures p where r.specificname = p.specificname and ucase(p.procname) =ucase('S_QT_STAT_BY_HDINF') and ucase(p.procschema) =ucase('EBIZ1') with ur

db2 "select b.routineschema, b.routinename, a.bschema pkgshema, a.bname pkgname from syscat.routinedep a, syscat.routines b where a.routinename=b.specificname and a.routineschema=b.routineschema and b.routinename='U_QT_SUBMISSION'and b.routineschema='EBIZ1'"

rebind SP

db2 "rebind package EBIZ1.P993866597"

RUNSTATS

在给表创建一个index后，我们最好做一次runstat，否则可能index没有生效
在对table做了一次reorg后，记得要做一次runstats

RUNSTATS ON TABLE EBIZ1.WEB_QUOTE For INDEX EBIZ1.WEB_QUOTE_I26

RUNSTATS ON TABLE EBIZ1.WEB_QUOTE
RUNSTATS ON TABLE EBIZ1.WEB_QUOTE WITH DISTRIBUTION AND DETAILED INDEXES ALL


Reorg table

当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性能。
针对数据库对象的大量操作，如反复地删除表，存储过程，会引起系统表中数据的频繁改变，在这种情况下，也要考虑对系统表进行REORG操作。

db2 reorg table EBIZ1.WEB_QUOTE_TACTIC_CODES

查询表的所有引用

select * from syscat.references where REFKEYNAME='WEB_QUOTE_PK';

select * from syscat.references where REFTABNAME='WEB_QUOTE_LOCK';

查询所有索引
select * from SYSCAT.INDEXES where INDNAME like 'WEB_QUOTE_I%'
======================================================================================================================
query error code info

db2 ? sql-727

db2 ? sqlstate
======================================================================================================================
get sp created time

select * from sysibm.sysroutines sr where sr.ROUTINENAME = 'S_QT_CUSTLIST_ATTR';
======================================================================================================================
get sp execution plan

db2expln -database dbname -schema EBIZ1 -package P0512749 -g -i -output WEBP_EBIZ1.S_QT_STAT_BY_HDINF.exp -user username password

get UDF execution plan

http://9.123.158.189:9082/DWSA/login.jsp

Check SQL access plan:
db2expln -d webrod -user web_dev2 ebiz20vs -stmtfile test.sql -z @ -output test.log -g


CREATE UNIQUE INDEX EBIZ1.WQ_LION_TEST ON EBIZ1.WEB_QUOTE(WEB_QUOTE_NUM) INCLUDE(PROG_CODE, EFF_DATE)

-----------------------------------------------------------------

db2 load query table EBIZ1.WEB_QUOTE_TACTIC_CODES

======================================================================================================================

Steps:

1. Check the access plan to locate the high cost sections.
2. Find the table scan from the section.
3. Locate the SQL from source code
4. Replace the parameters with real value to the SQL then get "advise" from DBA tool
5. Add indexes per the "advise" then RUNSTATS and REBIND the SP
6. Get the new access plan from DBA tool to check if the table scan has been eliminated and if the cost reduced
7. If not, add other recommended indexes

8. Repeat steps 1 to 6 to get rid of all table scan and reduce the cost to acceptable

======================================================================================================================

SP tuning steps:

DBA tool: http://9.119.86.169/dbatools/login.jsp

1) get access plan from DBA or DBA tool
2) find the highest estimated cost section
3) run advisor for the identified sql script in step 2) in DBA tool, please notice it must be executable sql script, can't have variaibles
4) add recommended index  in step 3
5）rebind sp, RUNSTATS on table
6) get access plan again and compare with step 1)



Commands(Example):

CREATE INDEX SYSTEM .IDX1312010659200 ON SODS2.QUOTE(WEB_QUOTE_NUM ASC, QUOTE_NUM ASC) ALLOW REVERSE SCANS;
     
select bname as packagename from syscat.procedures, syscat.routinedep, syscat.packages  
where syscat.procedures.specificname=syscat.routinedep.routinename
and syscat.packages.pkgname =bname and procschema ='EBIZ1' and procname ='S_QT_STAT_BY_ORD_NUM' with ur

RUNSTATS ON TABLE SODS2.QUOTE FOR INDEX SYSTEM.IDX1312010659200

db2 rebind package ebiz1.P1674421182

======================================================================================================================

Step 1. Check SP's code, use runstats to collect statistic information on all related tables in development environment.

Step 2. Get target SP's explain information from production, prod support and development environment. Using explain information from prod environment as standard, check prod support and development's explain information to see if there is big difference. If there is, find the reason(this may be caused by some non-production indexes created by developers for testing purpose. In that case, you can drop the indexes after negotiating with indexes creator, runstats on related tables, rebind sp, then get explain information again)
After fixing the problem, you can paste explain information(from production, prod support and development environment) to ebiz ticket for future reference

Step 3. Find some testing cases,  which include not only complex cases which take long time to finish, but also some cases which is not so slow. Record their execution time. We need to guarantee the slow cases get faster, and the fast cases won't get slower after tuning.

Step 4. Locate SQL(s) which needs to be tuned
     4.1 find the time-consuming SQLs, there are two ways:
       4.1.1 Load a testing SP to database, which adds some output parameters used to get timestamps on some pre-defined points.
         pros: don't need to create new tables
         cons: Need coding and calculate manually. Need DBA to deploy to prod support environment.
       4.1.2 Use Data studio's profiling tool

         pros: easy to use and can get rich information
         cons: Profilings tool will create 3 tables in database. DBA may have concerns for this in prod support environment      
   
     4.2. Check explain information to find SQLs with high cost and/or with TBSCAN on first level table fetch