A fix is available
APAR status
Closed as program error.
Error description
Virtual indexes do not appear in the PLAN_TABLE after Explain of a query involving a view.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All users of DB2 Explain. * **************************************************************** * PROBLEM DESCRIPTION: Indexes in DSN_VIRTUAL_INDEXES are * * not used in EXPLAIN for a statement * * with views under certain conditions. * **************************************************************** * RECOMMENDATION: * **************************************************************** Indexes in DSN_VIRTUAL_INDEXES may not be used in EXPLAIN for a statement with views. If the virtual index is created as an actual index, EXPLAIN will show that the actual index is used in PLAN_TABLE. For DB2 R810, indexes in DSN_VIRTUAL_INDEXES are not used in EXPLAIN for a statement when all of the following apply: (1) The index(es) in DSN_VIRTUAL_INDEXES are on a base table with a view reference. (2) The view in the SQL statement being explained meets any one of the following conditions: (a) The view refers to special registers USER or CURRENT SQLID. (b) The view is created before V8 New Function Mode. (c) The view has to be regenerated. Conditions for view regeneration are listed in the SQL Reference for ALTER TABLE in Table 71. For DB2 R910, indexes in DSN_VIRTUAL_INDEXES are not used in EXPLAIN for a statement when the index(es) in DSN_VIRTUAL_INDEXES are on a base table with a view reference. Keywords: EXPLAIN DSN_VIRTUAL_INDEX DSN_VIRTUAL_INDEXES OPTIMIZATION EXPERT INDEX ADVISOR
Problem conclusion
Code has been fixed so that indexes in DSN_VIRTUAL_INDEXES are used in EXPLAIN for a statement with views.
Temporary fix
Comments
APAR Information
APAR number
PK67554
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-06-12
Closed date
2008-07-25
Last modified date
2008-09-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK38414 UK38413
Modules/Macros
DSNXOADT DSNXOTL
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 September 2008