JR33488: PERFORMANCE ISSUE WHEN CAST AROUND LITERAL FOR BIT DATA IS PRESENT

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

• This problem happens ONLY WHEN CAST for BIT DATA and only when
  it is on one side and not another and only when literals.
  DB2 generate non-optimal plans because BIT_STRING function is
  injected and it prevents certain optimization logic. Please
  consider the following examples:
  
  CAST(x'00' AS CHAR(1) FOR BIT DATA) = CAST(x'01' AS CHAR(1) FOR
  BIT DATA)
  - will perform well
  
  x'00'    = x'01'
  - will perform well
  
  CAST(x'00' AS CHAR(1) FOR BIT DATA) = x'01'
  - here PERFROMANCE is BAD.
  

Local fix

• rewrite the statement in the following manner
  CAST(x'00' AS CHAR(1) FOR BIT DATA) = CAST(x'01' AS CHAR(1) FOR
  BIT DATA)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * This problem happens ONLY WHEN CAST for BIT DATA and         *
  * onlywhenit is on one side and not another and only when      *
  * literals.DB2 generate non-optimal plans because BIT_STRING   *
  * functionisinjected and it prevents certain optimization      *
  * logic. Pleaseconsider the following examples:CAST(x'00' AS   *
  * CHAR(1) FOR BIT DATA) = CAST(x'01' AS CHAR(1)FORBIT DATA)-   *
  * will perform wellx'00'    = x'01'- will perform              *
  * wellCAST(x'00' AS CHAR(1) FOR BIT DATA) = x'01'- here        *
  * PERFROMANCE is BAD.                                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * rewrite the statement in the following mannerCAST(x'00' AS   *
  * CHAR(1) FOR BIT DATA) = CAST(x'01' AS CHAR(1)FORBIT          *
  * DATA)Upgrade to DB 9.5 FP6                                   *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in Version 9.5 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:

  IC68939 IC68993

Fix information

• Fixed component name

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R950 PSY

     UP