Fixes are available
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 number
JR33488
Reported component name
DB2 UDB EXE WIN
Reported component ID
5724E4901
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-06-26
Closed date
2010-10-07
Last modified date
2010-10-07
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 UDB EXE WIN
Fixed component ID
5724E4901
Applicable component levels
R950 PSY
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
12 October 2021