Fixes are available
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 some cases, the compilation of an extremely long SQL statement containing too many column expressions and predicates using parameter marker or host variable comparisons may result in an SQL1585N, SQLSTATE=54048 error. When parameter markers or host variables are used with a between predicate, such as "COL between :hv1 and :hv2" or "COL >= :hv1 and COL <= :hv2", the query rewrite phase will add a check to ensure that :hv1 <= :hv2. As a result of adding this condition, you may hit a limitation in the optimizer that results in the SQL1585N error. When constants are used instead of the parameter markers or host variables, we don't hit the limitation in the optimizer. This limitation may not always result in SQL1585N. You can identify if this check is added by collecting an EXPLAIN of the query and using the db2exfmt tool to examine the details. The following steps describes one method to collect this information: 1. db2 set current explain mode explain 2. db2 -tvf <file_containing_query> 3. db2 set current explain mode no 4. db2exfmt -d <database_name> -1 -g -o <output_filename> In the output_filename file, under the "Optimized Statement" section, the following indicates that this condition was added: (SELECT Q1.$C0 FROM (VALUES 0) AS Q1 WHERE (:? <= :? SELECTIVITY 1.000000)) AS Q2 and the "Access Plan" will include operators similar to the following: 0.0111111 NLJOIN ( 4) 1455.04 192.4 /-------+-------\ 1 0.0111111 TBSCAN FILTER ( 5) ( 6) 9.563e-05 896.185 0 118.483 | | 1 ... TABFNC: SYSIBM GENROW Q1 The hv1<=hv2 condition is one example where this limitation applies. This fix addresses the general limitation in the optimizer where the query contains SELECT Q1.$C0 FROM (VALUES 0) AS Q1 The fix for this APAR is enabled by setting the DB2_EXTENDED_OPTIMIZATION registry variable, and there are two parts to it. It can be enabled using any of the following: 1. To enable both parts: db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD,NO_HVCHECK_ALL 2. To enable one portion of the change: db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD db2set DB2_EXTENDED_OPTIMIZATION=NO_HVCHECK_ALL Note: if you already have DB2_EXTENDED_OPTIMIZATION set, then you will need to include it when setting this new keywords; for example, db2set DB2_EXTENDED_OPTIMIZATION=YES,NLJNORD,NO_HVCHECK_ALL The "NLJNORD" value enables the optimizer improvement. The "NO_HVCHECK_ALL" value disables the injection of the :hv1 <= :hv2 condition, resulting in the same behavior as when the user supplies constants instead of host variables or parameter markers.
Local fix
Users could try replacing the parameter markers or host variables with constants, or use REOPT ALWAYS. Users could also try to change the optimization level since it may cause the optimizer to choose a different plan that may satisfy the order earlier and thus avoid SQL1585N.
Problem summary
Users affected: All Problem Description: As same as Error description. Problem Summary: As same as APAR abstract.
Problem conclusion
Problem was first fixed in Version 9.5 Fix Pack 3 (s081118).
Temporary fix
Comments
APAR Information
APAR number
JR30604
Reported component name
DB2 UDB WSE WIN
Reported component ID
5765F3501
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-09-26
Closed date
2009-03-08
Last modified date
2009-03-08
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 WSE WIN
Fixed component ID
5765F3501
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
Document Information
Modified date:
08 March 2009