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

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

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 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