Using Sumall utility

Sumall utility collects SQL workload and performance statistics.

Before you begin

Set the environment variable $JAVA_RT, by using the following command to source the contents of the compat.sh file:

. $TOP/bin/compat.sh

About this task

The Sumall utility is used to parse the db.log files and generate a statistical summary of tracked SQL runtimes. While investigating performance problems, it is recommended to track all the SQL statements by setting DB appender to the debug mode. Only by seeing the full SQL workload, you can evaluate the relative impact of specific SQL statements. Not only long running SQLs but also fast running SQLs, can impact performance, and are only seen when tracking DB appender in the debug mode.

Procedure

Use the following command to run the Sumall utility:

$JAVA_RT com.ibm.ccd.common.wpcsupport.util.Sumall <db.log files>

Results

By default, the only the SQLs, which are not marked as DELAYED are evaluated. Equal SQLs are aggregated at one line. Dynamic queries are differentiated by the first n characters as defined by option leng (default = 70). Parameter values are replaced '?', thus similar SQLs can be aggregated.

For each unique SQL, statistical values are generated:

Total runtime in milliseconds: sum(ms)
number of executions: cnt
average runtime in milliseconds: avg(ms)
Coefficient of Variation for the tracked SQLs: CV
SQL identifier: QUERY

SQL statistics is sorted on overall runtime in an ascending order. An aggregated statistical overview is printed at the end, that shows:

Monitored timeperiod (MT) - Time-range for which SQLs were tracked,
Aggregated total statistics - With %MT calculation that represents total tracked SQL in relation to the monitored time period.

What to do next

Use the following options to modify the default behavior.

$JAVA_RT com.ibm.ccd.common.wpcsupport.util.Sumall
                [leng=n characters] [delayed] [printsql] [cvon] [cleanoff]
                [fromtime="yyyy-MM-dd HH:mm:ss[,SSS]"] [totime="yyyy-MM-dd HH:mm:ss[,SSS]"]
                [threadname="<threadname>"] [threadlist] [version] [h|help] <db.log files>

Where,

leng: Defines how many characters from the beginning of the SQL are used for statistical aggregation.

printsql: Extract SQLs and runtime in sequential order, print one line per SQL.
delayed: Only SQLs marked as delayed is analyzed.
cvon: Provides a normalized metric on how much a single runtime varies compared to average runtime.
cleanoff: When set, parameter values are not replaced in the Dynamic SQL strings. Depending on the leng, might cause dynamic queries of the same type to be reported on the separate lines, in case the individual parameter values are contained in the first n (leng) characters.
fromtime: Analyze only SQLs with a timestamp > fromtime, required input format: yyyy-MM-dd HH:mm:ss.
totime: Analyze only SQLs with a timestamp < totime, required input format: yyyy-MM-dd HH:mm:ss.
threadname: SQLs are analyzed only for the provided thread. Use a threadname as listed by the threadlist.
threadlist: Print sqlstatistics per thread ordered by total SQLruntime.
version: Print current version sample output.

java -jar Sumall.jar db.log* threadname="jsp_57678: moveEntriesToNextStep.wpc"
…
      130     130       1,0   GEN_CTG_CSA_CAT_SYS_ATTR_GETBYCATEGORYID] SELECT * FROM tctg_csa_cat_s
      134     130       1,0   GEN_CTG_CHI_CATEGORY_HIERARCHY_GETBYCHILDID] SELECT * FROM tctg_chi_ca
      134     130       1,0   GEN_CTG_CFP_CAT_FULL_PATHS_GETBYCATEGORYID] SELECT * FROM tctg_cfp_cat
      158       1     158,0   GET_EVENT_ENTRY_MATCHING_SIGNATURE] SELECT wee_entry_id FROM twfl_wfe_
      219     235       0,9   GEN_SEC_SCU_USER_GETBYID] SELECT * FROM tsec_scu_user WHERE scu_user_i
      223     220       1,0   GEN_SEC_CMP_COMPANY_GETBYID] SELECT * FROM tsec_cmp_company WHERE cmp_
      267     278       1,0   GEN_CTG_ICM_ITEM_CATEGORY_MAP_GETBYITEMVERSION] SELECT * FROM tctg_icm
      276     250       1,1   GEN_CTG_ITD_ITEM_DETAIL_GETBYIDVERSION] SELECT * FROM tctg_itd_item_de
      431     428       1,0   GEN_CTG_ITM_ITEM_GETBYPRIMARYKEYVERSION] SELECT * FROM tctg_itm_item W
-----------------------------------------------
   sum(ms)    cnt   avg(ms)  QUERY

Monitored timeperiod (MT): 2015-07-24 10:37:54,467 - 2015-07-24 10:37:57,563 : 0,00 h | 0,00 m | 3,10 s
Statistical values for all queries
Total      Count      Average    Total        Total Runtime    % of MT
(ms)       (cnt)      (avg ms)   (minutes)    (hours)            (%)
2862       2733       1        0,0            0,0              92,4