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

  PI09293

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

Modules/Macros

• FOPCFPMN FOP4PEMN FOPHSTM  FOPQHDB9 FOP9APIM FOP00103 FOP0ITKA
  FOP009   FOP00303 FOPQDB8  FOPMCENU FOPMTMDB FOP00003 FOPUUNLD
  FOPVOU   FOPMARCN FOPD2VNN FOPZMAIN FOPQHDB8 FOPZSUB  FOPZFMMN
  FOP059   FOP1ODRV FOPD2V91 FOP079   FOPOMN   FOPMMCMN FOP00203
  FOPMADDE FOPMMUTL FOPMECMN FOPMDPRC FOPMCMAI FOPMCDRT FOPMDBMN
  FOP4IPMN FOPMDMAI FOPMAIN  FOP4ZHSH FOPMDDDL FOPMCOFN FOPUMCBT
  FOPMMJBS FOPMDXTR FOPMSORT FOPMAFCN FOPQLDB9 FOPMEMAI FOPMMUNU
  FOP099   FOP089   FOPMAMAI FOP3DTPR FOPZFBMN FOPMCOLP FOPMSELM
  FOPD2VA5 FOPMIXMM FOP4PMMN FOPIO    FOP9GINF FOPMUXIM FOPD2V85
  FOPMMCMR FOP1OSFN FOP019   FOP4TRCE
  

Fix information

• Fixed component name

  OPTIM MOVE FOR

• Fixed component ID

  5655V0700

Applicable component levels

• R720 PSY UI14999

     UP14/02/13 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.