PI89867: IMPROVE PARSING OF FREE FORM SQL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Optim SQL validation errors when SQL statements are not properly
  formatted for Optim with leading and trailing // characters.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users modifying Optim generated SQL // SYSIN control         *
  * statements and not adhering to the documented syntax rule    *
  * that each line of the SQL statement record should begin and  *
  * end with //                                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Although Optim is designed such that Optim generated SQL     *
  * statements are bounded by // on every record, free form SQL  *
  * bounded only by SQL // at the start and // and the end of    *
  * the block of SQL does work in many instances.                *
  * One example where this does not work is the case where an    *
  * SQL record ends with a hyphen, because Optim uses the hyphen *
  * (-) character internally (in generated SYSIN) to indicate    *
  * the continuation of control statements on the next record.   *
  *                                                              *
  * Example of unsupported syntax :-                             *
  *                                                              *
  * TABLE (OPTIM_CUSTOMERS  ACCESS D  REF N                      *
  *   PREDOP A  COLFLAG N                                        *
  *   SQL //                                                     *
  *   (CUST_ID > '00001' AND                                     *
  *                            CUSTNAME <> '................'    *
  * AND                                                          *
  *                            ADDRESS1 = 'BLUW PARROT COURT'    *
  *         AND                                                  *
  *             YTD_SALES > (YTD_SALES  -           <===== Hypen *
  * will cause an error                                          *
  *                                     100)+100-YTD_SALES)      *
  *   //                                                         *
  * :                                                            *
  * :                                                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * To ensure correct operation of Optim, ensure SYSIN WHERE     *
  * clause SQL statements (SQL //) are bounded by // statement   *
  * // on all statements that span more than one SYSIN input     *
  * record.                                                      *
  ****************************************************************
  

Problem conclusion

• Optim has been modified to ignore hyphens in SYSIN columns other
  than  79 and 80, therefore as long as free form SQL does not
  include a hyphen in these columns the SQL might be processed
  correctly.
  
  This APAR does not change the documented syntax for SQL
  statements, i.e. every record should still begin and end with //
  otherwise results may be undesirable.
  
  Example of supported syntax :-
  
  TABLE (OPTIM_CUSTOMERS  ACCESS D  REF N
    PREDOP A  COLFLAG N
    SQL // (CUST_ID > '00001' AND //
            //            CUSTNAME <> '................' AND //
            //             ADDRESS1 = 'BLUW PARROT COURT' //
            //  AND //
            //  YTD_SALES > (YTD_SALES  -  //
           //                          100)+100-YTD_SALES) //
  :
  :
  

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:

Modules/Macros

• FOPMMUTL
  

Fix information

• Fixed component name

  OPTIM MOVE FOR

• Fixed component ID

  5655V0700

Applicable component levels

• RB30 PSY UI51961

     UP17/11/21 P F711

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.