home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
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

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 FixList