JR30604: SQL1585N, SQLSTATE=54048 MAY OCCUR FOR EXTREMELY LONG SQL STATEMENTS WITH TOO MANY COLUMN EXPRESSIONS INVOLVED.

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 is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC72326

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