IBM Support

IT27030: SQL1585N is returned when the optimizer does not select a possible plan under some circumstances

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Under some conditions, the optimizer may not select a possible
    plan and as a result, returns an error. This is related to sort
    and allocating temp tablespace.
    
    Steps to reproduce the error:
     - CREATE BUFFERPOOL "BP32K" SIZE AUTOMATIC PAGESIZE 32768
     - CREATE TABLESPACE testTbs PAGESIZE 32K MANAGED BY SYSTEM
    USING('testTbs_32k') BUFFERPOOL BP32K
     - CREATE USER TEMPORARY TABLESPACE usr_temp_tbs PAGESIZE 32K
    MANAGED BY SYSTEM USING('user_temp_testTbs_32k') BUFFERPOOL
    BP32K
     - CREATE SYSTEM TEMPORARY TABLESPACE temp_systbs PAGESIZE 32K
    MANAGED BY SYSTEM USING('sys_temp_testTbs_32k') BUFFERPOOL BP32K
     - CREATE TABLE T1 (C1 INT, C2 INT, C3 BLOB (512 M) INLINE
    LENGTH 32000)
     - CREATE TABLE T2 (C1 INT, C2 INT, C3 BLOB (512 M) INLINE
    LENGTH 32000)
     - SET CURRENT QUERY OPTIMIZATION 0
     - MERGE INTO T1 USING T2 ON T1.C1=T2.C1 WHEN MATCHED THEN
    UPDATE SET C2=T2.C2, C3=NVL(T1.C3,T2.C3)
     - SQL1585N is observed at optimization level 5.
    However, a possible plan exists. If we change the query
    optimization level from 5 to 9 in the previous steps:
    The query is compiled successfully without SQL1585N. In this
    case we have the sort before NLJoin.
    The optimizer should have chosen this plan instead of a plan
    returning error.
    

Local fix

  • The suggested workaround for this issue is as follows:
    Create a UNIQUE constraint on the source table's column which is
    used to join two tables.
    For example, for the case in error description, it is:
     - CREATE TABLE DBA.TEST2 (C1 INT NOT NULL UNIQUE, C2 INT, C3
    BLOB (512 M) INLINE LENGTH 32000)
    This forces the optimizer not to sort thus avoids the problem.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7                                          *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.1.4.7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT27030

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-11-19

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

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

    IT26649

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

    IT35741

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       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":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022