DB2 FOR LUW / DB2FORLUW / 910 - DB2

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.

Problem Description: 
SQL1585N, SQLSTATE=54048 MAY OCCUR FOR EXTREMELY LONG SQL 
STATEMENTS WITH TOO MANY COLUMN EXPRESSIONS INVOLVED. 
Problem Summary: 
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.

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.

DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

Problem is first fixed in Version 9.1 Fix Pack 10

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.

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

02.11.2010
13.06.2011
13.06.2011

9.1.FP10