DB2 - Problembeschreibung
Problem IC73096 | Status: Geschlossen |
POOR PERFORMANCE FOR QUERIES WITH "GENROW" TABLE FUNCTION RUN FROM CLP PACKAGES BIND WITH 'INSERT BUF' OPTION | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
In V9.7 on DB2 Linux, Unix Windows systems with the Data Partitioning Facility (DPF), when CLP packages are bound with the 'insert buf' option, queries that use a "GENROW" function can perform very poorly due to the use of Listener Table Queue accesses and/or the coordinator node for producing query results that involve the "GENROW" function You can identify if you are experiencing this problem by using the Explain facility (Visual Explain, Optimum Query Tuner, or db2exfmt with the explain statement or explain mode), and examining the query access plan for a SELECT query, the following characteristics: 1) GENROW function is used in the query and has a partitioning of "COOR" Example db2exfmt output, examining the Input Streams for the TBSCAN over the GENROW: Input Streams: ------------- 4) From Object SYSIBM.GENROW Estimated number of rows: 1 Partition Map ID: -100 Partitioning: (COOR ) Coordinator Partition Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.NUMB Partition Column Names: ---------------------- +NONE 2) The query contains a Nested Loop Join (shown as "NLJOIN" in the query graph. 3) The "GENROW" appears on the outer, or left input leg of the NLJN 4) A Listener Table Queue (TQ) appears on the inner, or right input leg of the NLJN A Listener Table Queue is typically shown in the db2exfmt graph as one of the following: DTQ* BTQ* MDTQ* MBTQ* The important marker is the "*" following the TQ. This indicates the Table Queue is a Listener Table Queue. One can also examine the details for the Table Queue to determine if it is a Listener Table Queue. It will having the following argument listed: LISTENER: (Listener Table Queue type) TRUE If the above 4 conditional are all met, then it is likely you are experiencing this issue. | |
Problem-Zusammenfassung: | |
Users Effected: All users Problem Description: POOR PERFORMANCE FOR QUERIES WITH "GENROW" TABLE FUNCTION RUN FROM CLP PACKAGES BIND WITH 'INSERT BUF' OPTION Problem Summary: In V9.7 on DB2 Linux, Unix Windows systems with the Data Partitioning Facility (DPF), when CLP packages are bound with the 'insert buf' option, queries that use a "GENROW" function can perform very poorly due to the use of Listener Table Queue accesses and/or the coordinator node for producing query results that involve the "GENROW" function You can identify if you are experiencing this problem by using the Explain facility (Visual Explain, Optimum Query Tuner, or db2exfmt with the explain statement or explain mode), and examining the query access plan for a SELECT query, the following characteristics: 1) GENROW function is used in the query and has a partitioning of "COOR" Example db2exfmt output, examining the Input Streams for the TBSCAN over the GENROW: Input Streams: ------------- 4) From Object SYSIBM.GENROW Estimated number of rows: 1 Partition Map ID: -100 Partitioning: (COOR ) Coordinator Partition Number of columns: 1 Subquery predicate ID: Not Applicable Column Names: ------------ +Q1.NUMB Partition Column Names: ---------------------- +NONE 2) The query contains a Nested Loop Join (shown as "NLJOIN" in the query graph. 3) The "GENROW" appears on the outer, or left input leg of the NLJN 4) A Listener Table Queue (TQ) appears on the inner, or right input leg of the NLJN A Listener Table Queue is typically shown in the db2exfmt graph as one of the following: DTQ* BTQ* MDTQ* MBTQ* The important marker is the "*" following the TQ. This indicates the Table Queue is a Listener Table Queue. One can also examine the details for the Table Queue to determine if it is a Listener Table Queue. It will having the following argument listed: LISTENER: (Listener Table Queue type) TRUE If the above 4 conditional are all met, then it is likely you are experiencing this issue. | |
Local-Fix: | |
The typical workaround is to rebind the packages without 'insert buf' option and with 'replace' option | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
Lösung | |
defect wsdbu00804865 CSD First fixed in DB2 UDB Version 9.7, FixPak 5 module engn_sqno | |
Workaround | |
Upgrade to DB2 Version Version 9.7, FixPak 5 | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 08.12.2010 07.12.2011 07.12.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7. | |
Problem behoben lt. FixList in der Version | |
9.7.0.5 |