PI09293: INVALID OR EXCESSIVE WHERE CLAUSE ON EXTRACT WITH SQL CRITERIA

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Client is performing an Optim EXTRACT using SQL criteria. The
  criteria length exceeds 32K-1 characters and the processing
  terminates with RC=12 and error "Invalid or Excessive Length
  Where Clause for cid.table".
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Optim/z users performing EXTRACT/ARCHIVE processing of   *
  * data and using SQL selection criteria.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When performing EXTRACT or ARCHIVE processing and using SQL  *
  * selection criteria the may encounter the following error     *
  * message: "Invalid or Excessive Length Where Clause for       *
  * cid.table" which is being issued when the length of the SQL  *
  * exceeds the Optim processing limit of approximately 32K      *
  * characters of SQL.                                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply the provided fix.                                      *
  ****************************************************************
  Optim internally limits the size of the SQL selection criteria
  to approximately 32K characters. DB2 starting with V8 NFM allows
  for the SQL to reach the length of approximately 2M characters.
  

Problem conclusion

• Optim/z code will be changed to allow for up to 2M characters of
  SQL to be specified for selection criteria during EXTRACT or
  ARCHIVE creation processing.
  Note that the above limit is applicable only in the following
  scenarios:
  o While an Optim Access Definition may be created under ISPF,
  the size of the SQL as entered via ISPF interface is still
  limited to about 425 lines of text. Such AD may be EXPORTED to a
  sequential file, modified manually by the user for longer SQL
  and IMPORTED back to Optim with the longer SQL intact. However
  the SQL attached to the AD will not be directly modifiable by
  the user via the ISPF interface.
  o An AD with criteria SQL text exceeding 32K and created using
  the above steps may be used for any Optim online ISPF or BATCH
  processing without any restriction.
  o A temporary AD with criteria SQL text exceeding 32K may be
  created manually for BATCH processing, directly without
  performing the steps outlined above.
  

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:

  PI09295 UI14716

Modules/Macros

• FOPD2VA5 FOPMDMAI FOPCFPMN FOPMAIN  FOPD2VNN FOP4PMMN FOPZMAIN
  FOP0PIMN FOPQHDB8 FOP1ODRV FOPD2V91 FOPMUXIM FOPHSTM  FOPQHDB9
  FOPD2V85 FOPOMN   FOP9APIM FOPMADDE FOPMDXTR FOPMSORT FOPQLDB9
  FOPMEMAI FOPMECMN FOPMDPRC FOPMCMAI FOPMMUNU FOPZFBMN FOPQDB8
  

Fix information

• Fixed component name

  OPTIM MOVE FOR

• Fixed component ID

  5655V0700

Applicable component levels

• R710 PSY UI14716

     UP14/02/05 P F402

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.