DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

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.

The typical workaround is to rebind the packages without 'insert 
buf' option and with 'replace' option

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
 

defect  wsdbu00804865 
CSD     First fixed in DB2 UDB Version 9.7, FixPak 5 
module  engn_sqno

Upgrade to DB2 Version Version 9.7, FixPak 5

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.12.2010
07.12.2011
07.12.2011

9.7.