Troubleshooting
Problem
Most SQL statements are run by IBM® InfoSphere® Master Data Management Collaboration Server as prepared statements using parameter markers. Usually these perform fast, but at some installations using DB2® as database server it was found that SQL statements GET_ICM_INFO_FOR_ITEMSET, GET_CSM_INFO_FOR_ITEMSET or GET_LINKEDITEM_INFO sometimes perform slower than when run as dynamic SQL using literals and impact performance of specific tasks. This technote describes how to detect and resolve this issue on environments using DB2® as database server.
Symptom
Tasks like opening items in multi edit screen, checking out items to collaboration area or opening an item might be slow and might be due to slow performing SQL statements as described in the Abstract. In that case you should see those being tracked as "DELAYED" queries in db.log files while performing such tasks.
Cause
These SQL statements are run as prepared statements using parameter markers. This means, DB2 creates and stores the access plan during the prepare phase while the explicit values for the statements' filter condition are yet not known. In many cases the calculated access plan will be fine, but sometimes data statistics cause the optimizer to choose an inappropriate access plan.
Diagnosing The Problem
Verify whether all or some of the mentioned SQL statements constantly are slow and contribute significantly to overall SQL runtime of tasks done in IBM® InfoSphere® Master Data Management Collaboration Server (MDMCS).
To do so, enable debug mode for DB appender in log.xml, reproduce the slow performing task and use sumall utility to generate a statistical summary of SQL statements logged in the db.log files of the respective MDMCS service (e.g. any user interface interaction will be tracked in $TOP/logs/appsvr*, scheduled jobs are tracked in $TOP/logs/scheduler* ...). For details on this tool check infocenter chapter "Sumall utility to collect SQL workload and performance".
These SQL statements might cause noticeable performance impact if an average runtime of more than 500 milliseconds is reported for any of those three statements and the accumulated runtime is one of the biggest contributors out of all SQL statements.
For example:
$JAVA_RT com.ibm.ccd.common.wpcsupport.util.Sumall $TOP/logs/appsvr*/db.log
...
1646 1821 0,9 Dynamic: [ SELECT DISTINCT sel_selection_id FROM tctg_sel_selection W
150662 50 3013,2 GET_LINKEDITEM_INFO] SELECT ITA_TEMP.CGM_SRC_NODE_ID, ITA_TEMP.ITA_OCC
-----------------------------------------------
sum(ms) cnt avg(ms) QUERY
or
...
3556 59 60,3 0,4 Dynamic: [SELECT noa_node_id, noa_name, noa_value FROM tctg_noa_node_
58684 83 707,0 0,1 GET_ICM_INFO_FOR_ITEMSET] SELECT * FROM tctg_icm_item_category_map WHE
62076 83 747,9 0,1 GET_CSM_INFO_FOR_ITEMSET] SELECT distinct csm_category_id, csm_spec_id
-----------------------------------------------
sum(ms) cnt avg(ms) CV QUERY
To verify whether the long runtime for those statements is indeed due to the database optimizer choosing an inefficient data accessplan as the SQL statements are run as prepared statements using literals, you should extract some sample statements from db.log files and run them from any database client. As by then the statements are run as dynamic statements using literals, db2 optimizer knows the exact filter conditions during data access plan generation and may choose a different access plan that performs much faster.
Resolving The Problem
In most cases the slow performance of those statements can be resolved by ensuring that basic configuration and maintenance guidelines are met and especially database table and index statistics are up to date (see link Basic health checks ...).
If that does not improve performance for those statements as seen in db.log files, you should consider enabling the reopt option in DB2 using optimization profile for these three specific statements. This will trigger that a new data access plan gets generated by DB2 for these SQLs once the real values for the parameter markers have been received by DB2. A more detailed discussion on this topic and guideline for using optimization profile can be found in developer works article "Improve SQL execution time ...".
In accordance to the steps outlined in that article under section "Controlling reoptimization using a profile", please find in the following detailed instructions. Please note: Steps 1 - 6 have to be done by the DB2 Administrator directly on DB2 server side. Steps 7 and 8 would be done by the MDMCS Administrator:
1. Create an optimization profile for the SELECT statements. Optimization profiles are XML documents. The optimization profile is named mdmceprofile1.xml and contains instruction to use reopt always for these statements. You might remove the <STMTPROFILE ID>... </STMTPROFILE> sections for those statements, for which you do not see that issue.
- <?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
<STMTPROFILE ID="GET_ICM_INFO_FOR_ITEMSET">
<STMTMATCH EXACT="FALSE"/>
<STMTKEY>
<![CDATA[SELECT * FROM tctg_icm_item_category_map WHERE icm_item_id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND icm_catalog_id = ? AND icm_company_id = ? AND icm_version_id <= ? AND icm_next_version_id >= ?]]>
</STMTKEY>
<OPTGUIDELINES>
<REOPT VALUE="ALWAYS"/>
</OPTGUIDELINES>
</STMTPROFILE>
<STMTPROFILE ID="GET_CSM_INFO_FOR_ITEMSET">
<STMTMATCH EXACT="FALSE"/>
<STMTKEY>
<![CDATA[SELECT distinct csm_category_id, csm_spec_id FROM tctg_csm_category_spec_map, tctg_icm_item_category_map WHERE icm_item_id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND icm_company_id = ? AND csm_company_id = icm_company_id AND icm_category_id = csm_category_id AND icm_catalog_id = ? AND csm_filter_catalog_id in (?, ?) AND icm_version_id <= ? AND icm_next_version_id >= ? AND csm_version_id <= ? AND csm_next_version_id >= ?]]>
</STMTKEY>
<OPTGUIDELINES>
<REOPT VALUE="ALWAYS"/>
</OPTGUIDELINES>
</STMTPROFILE>
<STMTPROFILE ID="GET_LINKEDITEM_INFO">
<STMTMATCH EXACT="FALSE"/>
<STMTKEY>
<![CDATA[SELECT ITA_TEMP.CGM_SRC_NODE_ID, ITA_TEMP.ITA_OCCURRENCE_ID, ITA_TEMP.ITM_PRIMARY_KEY, ITA_VALUE_STRING, ITA_VALUE_NUMERIC FROM (SELECT ITM_ID, ITM_PRIMARY_KEY, ITM_CONTAINER_ID, CGM_SRC_NODE_ID, CGM_DST_DISP_NODE_ID, ITA_OCCURRENCE_ID FROM ITM, ITA, CGM WHERE ITM_COMPANY_ID = ? AND ITA.ITA_ITEM_ID = ? AND CGM.CGM_SRC_CTG_ID = ? AND (ITM_PRIMARY_KEY = ITA_VALUE_STRING OR ITM_PRIMARY_KEY = ITA_VALUE_STRING_IGNORECASE) AND ITM_COMPANY_ID = CGM_COMPANY_ID AND CGM_SRC_CTG_ID = ITA_CATALOG_ID AND CGM_SRC_NODE_ID = ITA_NODE_ID AND ITM.ITM_CONTAINER_ID = CGM_DST_CTG_ID AND ITM_NEXT_VERSION_ID = ? AND CGM_NEXT_VERSION_ID = ? AND ITA_NEXT_VERSION_ID = ?) ITA_TEMP LEFT OUTER JOIN ITA ON ITA_NODE_ID = ITA_TEMP.CGM_DST_DISP_NODE_ID AND ITA_ITEM_ID = ITA_TEMP.ITM_ID AND ITA_CATALOG_ID = ITA_TEMP.ITM_CONTAINER_ID AND ITA_NEXT_VERSION_ID = ?]]>
</STMTKEY>
<OPTGUIDELINES>
<REOPT VALUE="ALWAYS"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
2. Set the DB2 registry variable DB2_OPTPROFILE to a value of YES to explicitly activate the use of optimization profiles in DB2
- db2set DB2_OPTPROFILE=YES
3. Restart the DB2 instance for DB2 registry changes to take effect.
- db2stop
db2start
4. Call the stored procedure SYSPROC.SYSINSTALLOBJECTS to create the SYSTOOLS.OPT_PROFILE table to store the optimization profile
- CALL SYSPROC.SYSINSTALLOBJECTS('OPT_PROFILES', 'C',CAST(NULL AS VARCHAR(128)), CAST(NULL AS VARCHAR(128)));
5. Use the DB2 IMPORT utility to insert the XML file containing the optimization profile definition (profile1.xml) into table SYSTOOLS.OPT_PROFILE.
- a) Create an input file for the IMPORT utility (such as profile1.del) to reference the XML file and to provide a schema (<schemaname>) and a name (PROFILE1) for the optimization profile
profile1.del:
"<schemaname>","PROFILE1","mdmceprofile1.xml"
b) Call the IMPORT utility for the input file
db2 IMPORT FROM profile1.del OF DEL MODIFIED BY LOBSINFILE
INSERT INTO SYSTOOLS.OPT_PROFILE;
6. Flush the DB2 optimization profile cache to ensure that changes to optimization profiles will take effect
- db2 FLUSH OPTIMIZATION PROFILE CACHE ALL;
7. Configure MDMCE to use the defined optimization profile. This is done by appending the property optimizationProfile to the connection URL in $TOP/etc/default/db.xml
<db_url>jdbc:db2://<server>:<port>/<db>:optimizationProfile=<schema>.PROFILE1;</db_url>
8. Restart MDMCE and check whether the statements still show long runtimes
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21697485