IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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:

    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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 March 2009