IC80663: POOR QUERY PERFORMANCE WHEN STRING HOST VARIABLE OR PARAMETER MARKER IS CAST TO ANOTHER STRING TYPE

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

APAR status

• Closed as program error.

Error description

• This APAR improves performance for queries that cast string host
  variable or parameter marker to another string type.  The result
  only needs to be evaluated once, before this fix, it was
  evaluated for every row.  For example:
  
    SELECT * FROM T WHERE GRAPHICCOL = cast( ? as char(10) )
  
  The parameter marker has type CHAR(10), there is an implicit
  cast to GRAPHIC type.  Before this fix, the CHAR to GRAPHIC cast
  was evaluated for every row of the table instead of once at the
  beginning of the query.
  
  Note that the same thing can occur without explicit host
  variables or parameter markers when the statement concentrator
  is enabled, e.g.:
  
    SELECT * FROM T WHERE GRAPHICCOL = 'literal'
  
  The statement concentrator replaces the string literal with a
  host variable of type VARCHAR.  As above, the VARCHAR host
  variable is internally cast to GRAPHIC type to match the left
  hand side of the predicate.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 Version 9.7 Fix Pack 6 or later.              *
  ****************************************************************
  

Problem conclusion

• The problem has been fixed in DB2 Version 9.7 Fix Pack 6.
  

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

• Fixed component ID

  DB2FORLUW

Applicable component levels

• R970 PSN

     UP