LI73609: DB2 V9.5 FP1:EXPLAIN TABLES DOES NOT STORE INLIST VALUES.

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• In DB2 Database for Linux, UNIX, and Windows, when IN list
  predicates are used in SQL statements, sometimes the optimizer
  chooses to apply an IN list predicate by transforming it into a
  join. When a join is chosen to apply the IN list predicate, the
  constant values used in the IN list do not get stored in the
  explain tables in a manner that associates them with either the
  join or the "GENROW" table scan (TBSCAN) that is used to apply
  the IN list. The only predicate stored in the explain tables is
  an equality (HOW_APPLIED column keyword of "EQ")  join predicate
  (RELOPT_TYPE column keyword of "JOIN") involving a tokenized
  column name  in the EXPLAIN_PREDICATE table in the
  PREDICATE_TEXT column.
  
  This can make it difficult to tell what the IN list values are,
  or, in the case of an SQL statement with multiple IN lists,
  which IN list is being applied in a join.
  

Local fix

• In DB2 V9.5, the Optimized SQL can be parsed to determine the IN
  list values.
  
  CHANGES AFER APPLYING THE APAR FIX:
  
  After applying this APAR fix, if an IN list is transformed into
  a join, in addition to the current JOIN predicate described in
  the ERROR DESCRIPTION for this APAR, you will see an new row in
  the EXPLAIN_ARGUMENT table with an ARGUMENT_TYPE column keyword
  of "GENRVALS" and "VALUES( comma separated IN list values)" in
  either the ARGUMENT_VALUE column (if the IN list is 1024
  characters or fewer) or the LONG_ARGUMENT_VALUE (if the IN list
  is greater than 1024 characters). If the IN list values are in
  the ARGUMENT_VALUE column, then the LONG_ARGUMENT_VALUE column
  will be NULL. If the IN list values are in the
  LONG_ARGUMENT_VALUE column, then the ARGUMENT_VALUE column will
  be NULL.
  

Problem summary

• In DB2 Database for Linux, UNIX, and Windows, when IN list
  predicates are used in SQL statements, sometimes the optimizer
  chooses to apply an IN list predicate by transforming it into a
  join. When a join is chosen to apply the IN list predicate, the
  constant values used in the IN list do not get stored in the
  explain tables in a manner that associates them with either the
  join or the "GENROW" table scan (TBSCAN) that is used to apply
  the IN list. The only predicate stored in the explain tables is
  an equality (HOW_APPLIED column keyword of "EQ")  join predicate
  (RELOPT_TYPE column keyword of "JOIN") involving a tokenized
  column name  in the EXPLAIN_PREDICATE table in the
  PREDICATE_TEXT column.
  

Problem conclusion

• First Fixed in DB2 v9.5 FP3
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R950 PSY

     UP