Query performance monitoring and data capture

Edit online

IBM® Content Manager includes two library server trace levels to monitor and record data that is related to query performance.

When a query runs, most of the time is used by the Db2 open cursor of the generate SELECT statement in the library server. Most query performance problems can be solved by using the Db2 runstats command, and creating indexes: see Database tuning.

These trace levels are available for Db2. For information about how to turn on traces, see Enabling the library server trace facility.

Trace level 512

Trace level 512 records the query's open cursor time. You can turn it on for a limited time, and save the log file for future reference.

You can use this trace level with trace level 8, which records the performance of the library server stored procedure calls.

Example output

The following output shows the effect of using trace levels 8 and 512 together.

ICMPLSQU ICMSEARCH                        00599 05/08 22:22:20.015 GMT ;08222219845227 30963410522498186341 ICMADMIN    21 msec /WORKLIST
ICMPLSQU ICMSEARCH                        00599 05/08 22:22:20.046 GMT ;08222219845227 30967191422498186341 ICMADMIN     8 msec /ROUTINGPROCESS[@ICMPRSTATE=1]
ICMPLSLR ICMLISTITEMTYPEREL               00308 05/08 22:22:20.046 GMT ;08222219845227 30968752722498186341 ICMADMIN     2 msec
ICMPLSLR ICMLISTITEMTYPEREL               00308 05/08 22:22:20.062 GMT ;08222219845227 30968752722498186341 ICMADMIN     1 msec
ICMPLSLR ICMLISTITEMTYPEREL               00308 05/08 22:22:20.062 GMT ;08222219845227 30970314322498186341 ICMADMIN     0 msec
ICMPLSLK ICMLISTNLSKEYWRD                 00906 05/08 22:22:20.062 GMT ;08222219845227 30970314322498186341 ICMADMIN     1 msec
ICMPLSLK ICMLISTNLSKEYWRD                 00906 05/08 22:22:20.062 GMT ;08222219845227 30970314322498186341 ICMADMIN     1 msec
ICMPLSQU ICMSEARCH                        00599 05/08 22:22:20.115 GMT ;08222219845227 30971877122498186341 ICMADMIN    21 msec /ICMSAVEDSEARCH[@IsPublic = 1 OR (@IsPublic = 0 AND @CREATEUSERID = "ICMADMIN")]
ICMPLSQU ICMSEARCH                        00599 05/08 22:22:36.524 GMT ;08222219845227 32614878222498186341 ICMADMIN     8 msec /ICMCLIENTEXITS

Trace level 1024

Trace level 1024 records data when the open cursor time of the SELECT is greater than or equal to the QUERYTHRESHOLD value (in the ICMSTSysControl system control table). To set QUERYTHRESHOLD, use the system administration client, or DKLSCfgDefICM.setQueryThreshold in the Java API.

It records the SQL statement that is generated for the query, You can run the SQL statement in Db2 to get information about its performance.

To get information about the performance of a query

  1. Clear the library server log file.
  2. Use the system administration client to set TRACELEVEL to 1024 and to set the QueryThreshold.
  3. Run the queries that you want to test.
  4. In the library server log file, search for the section that starts with the following text:
    BEGIN QUERY PERFORMANCE DATA CAPTURE

    The section contains log entries that are similar to the following ones:

    --BEGIN QUERY PERFORMANCE DATA CAPTURE
--Time (GMT): 2023/05/08 23:13:50.087
--XQPE string: /'tsdoctest'[@SEMANTICTYPE BETWEEN 1 AND 2]
--Execution time (ms): 10000
--Threshold setting (ms): 5000
--Userid: ICMADMIN
--Unum: 2
--Length of SQLStmt: 361
SET SCHEMA ICMADMIN;
SELECT T.* FROM ( SELECT DISTINCT tsdoctest_1.ITEMID, tsdoctest_1.COMPONENTID, tsdoctest_1.VERSIONID, 1008 AS COMPONENTTYPEID, 1005 AS ITEMTYPEID FROM ICMUT01008001 tsdoctest_1 WHERE
(tsdoctest_1.SEMANTICTYPE BETWEEN 1 AND 2) AND (((( (1=1) ))))) T , ICMSTITEMS001001 I WHERE T.ITEMID = I.ITEMID AND T.VERSIONID = I.VERSIONID OPTIMIZE FOR 500 ROWS FOR READ ONLY ;
--END QUERY DATA CAPTURE
  5. Copy all the lines from "BEGIN QUERY PERFORMANCE DATA CAPTURE" to "END QUERY DATA CAPTURE", and then save them in a file called, for example, MySlowSQL.txt.
  6. Run the following commands:
    db2batch -d <database_name> -a <library_server_admin_id>/<library_server_admin_password> -f MySlowSQL.txt -i complete -o e 2 p 5 f 10 o 5 r 10 -r MySlowSQL.batch.out
db2exfmt -1 -d <database_name> -e <library_server_sschema> -u <library_server_admin_id> <library_server_admin_password> -o MySlowSQL.exp.out
  7. These commands create two output files, MySlowSQL.batch.out and MySlowSQL.exp.out. Send the output files to IBM support for analysis.