|
Problem IC82701
|
Status: Closed |
DB2EXFMT. OPTIM DATA STUDIO AND QUERY WORKLOAD TUNER EXPLAINS DO NOT SHOW ROWID ($RID$) COLUMN FOR IXSCAN AND FETCH OPERATORS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When a Query Access Plan is displayed using either the db2exfmt
utility, or the IBM Optim(TM) family of Database tools, such as
visual explain or
the access plan graph or access plan explorer features under
Optim Query Workload Tuner, the
RowID column (shown as $RID$ in the column list) is not included
in the list of columns that are retrieved from an index and used
either in Index ANDing, Index ORing, Starjoin IXA, or Fetch
operations. In some cases, if the $RID$ is the only column
returned
by these operators, it appears in the current version of the
explains that no
columns are output from these operators. The need is to get the
$RID$ column
passed through these operators so they can indicate that output
columns are provided.
In the following extract of db2exfmt output, prior to this fix,
the Q4.$RID$ column was missing:
Input Streams:
-------------
8) From Object FINLAY.FX4
Estimated number of rows: 1e+06
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4."D4"(A)+Q4.$RID$
Output Streams:
--------------
9) To Operator #19
Estimated number of rows: 10
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4."D4"(A)+Q4.$RID$
This missing column can lead to a number of issues with the
various tools:
1) db2exfmt cannot indicate the quantifier number "Qxxx" in the
query graph because no columns may show as flowing from an Index
Scan (IXSCAN) operator.
2) Optim Query Workload Tuner may not be able to perform column
analysis
functions because either extra, unnecessary columns are shown
being retrieved from the index, or columns may be missing from
Explain.
This fix only affects the output from the Explain Facility for
DB2 for Linux, Unix and Windows output, it does not in any way
affect or indicate a problem or issue with the query access
plan. For IXSCAN operators, the list of columns shown in explain
is dervied for Explain purposes only and does not impact the
plan execution. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All LUW users of Serial and Intra-Partition Parallelism and *
* Optim Query Tuner *
****************************************************************
* PROBLEM DESCRIPTION: *
* When a Query Access Plan is displayed using either the *
* db2exfmt *
* utility, or the IBM Optim(TM) family of Database tools, the *
* RowID column (shown as $RID$ in the column list) is not *
* included *
* in the list of columns that are retrieved from an index and *
* used *
* either in Index ANDing, Index ORing, Starjoin IXA, or Fetch *
* operations. *
* *
* In the following extract of db2exfmt output, prior to this *
* fix, *
* the Q4.$RID$ column was missing: *
* *
* Input Streams: *
* ------------- *
* 8) From Object FINLAY.FX4 *
* Estimated number of rows: 1e+06 *
* Number of columns: 2 *
* Subquery predicate ID: Not Applicable *
* *
* Column Names: *
* ------------ *
* +Q4."D4"(A)+Q4.$RID$ *
* *
* *
* Output Streams: *
* -------------- *
* 9) To Operator #19 *
* Estimated number of rows: 10 *
* Number of columns: 2 *
* Subquery predicate ID: Not Applicable *
* *
* Column Names: *
* ------------ *
* +Q4."D4"(A)+Q4.$RID$ *
* *
* This missing column can lead to a number of issues with the *
* various tools: *
* 1) db2exfmt cannot indicate the quantifier number "Qxxx" in *
* the *
* query graph because no columns may show as flowing from an *
* Index *
* Scan (IXSCAN) operator. *
* 2) Optim Query Tuner may not be able to perform column *
* analysis *
* functions because either extra, unnecessary columns are *
* shown *
* being retrieved from the index, or columns may be missing *
* from *
* Explain. *
* *
* This fix only affects the output from the Explain Facility *
* for *
* DB2 for Linux, Unix and Windows output, it does not in any *
* way *
* affect or indicate a problem or issue with the query access *
* plan. For IXSCAN operators, the list of columns shown in *
* explain *
* is dervied for Explain purposes only and does not impact the *
* plan execution. *
****************************************************************
* RECOMMENDATION: *
* Upgrade the DB2 for Linux, Unix and Windows server to v9.7 *
* Fix Pack 6 or higher. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 |
This problem is addressed DB2 for Linux, Unix and Windows server
to v9.7 Fix Pack 6 or higher. Once the Fix Pack is applied, the
Explain Facility will properly indicate the $RID$ column in the
Input/Output stream for IXSCANs and other operators up to the
FETCH that uses the RowID to fetch the specified row and get
additional columns for other operators. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC87830 IC87874
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.04.2012
04.06.2012
04.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
