|
Problem IC73096
|
Status: Closed |
POOR PERFORMANCE FOR QUERIES WITH "GENROW" TABLE FUNCTION RUN FROM CLP PACKAGES BIND WITH 'INSERT BUF' OPTION |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
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 |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
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 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.12.2010
07.12.2011
07.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
