JR27631: SQL1585N ERROR MESSAGE WHEN THE DB2_REDUCED_OPTIMIZATION AND DISTINCT FUNCTION ON LARGE COLUMNS (CLOB, DBCLOB, BLOB)

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
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

APAR status

• Closed as program error.

Error description

• A query may generate SQL1585N error message when the
  DB2_REDUCED_OPTIMIZATION registry variable is set and the query
  involves a DISTINCT function on large columns, CLOB, DBCLOB,
  BLOB, etc or when a sub-select contain a large column also
  contains an EXISTS or IN subquery. For example the following
  query can generate the SQL1585N.
  
  SELECT  CHARCOLUMN,
          LARGECOLUMN
  FROM    TableA
  WHERE CHARCOLUMN2 IN
          (SELECT #tt2.REFFIELDRRef
          FROM    session.#tt2
          )
  ORDER BY CHARCOLUMN;
  

Local fix

• Unset the DB2_REDUCED_OPTIMIZATION registry variable and restart
  the database instance
  

Problem summary

• Users affected: ALL
  Problem description: SQL1585N ERROR MESSAGE WHEN THE
  DB2_REDUCED_OPTIMIZATION AND
  DISTINCT FUNCTION ON LARGE COLUMNS (CLOB, DBCLOB, BLOB)
  Problem Summary:
  ERROR DESCRIPTION:
  A query may generate SQL1585N error message when the
  DB2_REDUCED_OPTIMIZATION registry variable is set and the query
  involves a DISTINCT function on large columns, CLOB, DBCLOB,
  BLOB, etc or when a sub-select contain a large column also
  contains an EXISTS or IN subquery. For example the following
  query can generate the SQL1585N.
  
  SELECT  CHARCOLUMN,
          LARGECOLUMN
  FROM    TableA
  WHERE CHARCOLUMN2 IN
          (SELECT #tt2.REFFIELDRRef
          FROM    session.#tt2
          )
  ORDER BY CHARCOLUMN;
  

Problem conclusion

• First fixed in DB2 UDB Version 9.1, FixPak 5
  

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:

  JR27652

Fix information

• Fixed component name

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R910 PSY

     UP