PM13844: PROVIDE PRECOMPILER TO SUPPORT NATIVE SQL PROCEDURE MIGRATION AND DEVELOPMENT ACTIVITY

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• A HOST(SQLPL) precompiler is provided to perform a checkout
  inspection for an SQL procedure definition, validating that the
  source is properly coded to conform with native SQL PL syntax
  and behaviors.  This is a recommended step to perform for all
  native SQL procedure development. It is a vital step to perform
  prior to any migration of an external SQL procedure to a native
  SQL procedure.
  
  The HOST(SQLPL) checkout precompiler is able to detect
  commonplace coding practices used in external SQL procedures
  which are incompatible when brought forward to native SQL
  procedures. The location of these incompatibilities are then
  easily identified in the precompiler listing. The iterative
  process of SQL procedure source code inspection and correction
  can be transferred to an independent task on z/OS thus freeing
  up DB2 resources for other workloads.
  
  Achieving a clean HOST(SQLPL) precompile prior to removing,
  adding or changing any SQL stored procedure definition in DB2
  can help speed the migration to native SQL procedures and
  assist in reducing the overall development costs.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of SQL procedures with DB2 Version 9.1 *
  *                 for z/OS and DB2 Version 10.1 for z/OS.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Provide precompiler to support native   *
  *                      SQL procedure migration and development *
  *                      activity                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A HOST(SQLPL) precompiler is provided to perform a checkout
  inspection for an SQL procedure definition, validating that the
  source is properly coded to conform with native SQL PL syntax
  and behaviors.  This is a recommended step to perform for all
  native SQL procedure development. It is a vital step to perform
  prior to any migration of an external SQL procedure to a native
  SQL procedure.
  
  The following sample JCL can be used to invoke the HOST(SQLPL)
  checkout precompiler:
  
   //CHKSQLPL EXEC PGM=DSNHPSM,
   //         PARM='HOST(SQLPL),SOURCE,XREF,NEWFUN(YES)'
   //STEPLIB  DD DSN=...  <== location of DSNHPSM loadmod ALIAS
   //SYSIN    DD DSN=...  <==  input: FB80 w/SQL procedure source
   //SYSPRINT DD SYSOUT=* <== output: Native SQL PL listing w/msgs
   //SYSUT1   DD DUMMY
   //SYSUT2   DD DUMMY
   //SYSTERM  DD DUMMY
   //SYSCIN   DD DUMMY
  
  Note: Adjust the precompiler options to match your source and
        suit your needs. In particular, pay close attention to the
        accurate use of precompiler options CCSID() and MARGINS().
  
  This APAR adds the following new messages to the DB2 Messages
  publication:
  
  
  DSNH4772I I csectname LINE nnnn COL cc
    UNQUALIFIED IDENTIFIER name IS USED IN AN AMBIGUOUS CONTEXT
  
  Explanation:
  The named identifier is not a unique reference in the SQL
  statement context.  An sql-parameter or an sql-variable with
  that name is declared but does not have priority over a column
  with that name in this context.
  
  User Response:
  Inspect the ambiguous reference and qualify the named
  identifier to clarify the intended usage in the SQL statement.
  
  - For a reference to an sql-parameter qualify the name with the
    SQL PL routine name.
  - For a reference to an sql-variable qualify the name with the
    label of the compound-statement that declares the
    sql-variable. If missing, first add a label to the
    compound-statement.
  - For a reference to a column qualify the name with the
    appropriate table designator.
  
  Severity:
    0 (informational)
  
  
  DSNH4773I W csectname LINE nnnn COL cc
    HANDLER BODY IS A CONTROL STATEMENT OTHER THAN A COMPOUND
    STATEMENT
  
  Explanation:
  The body of a handler-declaration is an SQL-control-statement
  other than an SQL-compound-statement. This usage will displace
  the original condition information available to the condition
  handler logic. The SQL diagnostic area accessed by a GET CURRENT
  DIAGOSTICS statement will be modified and unrelated to the
  original condition. The values of variables SQLSTATE and SQLCODE
  will be modified and unrelated to the original condition.
  
  User Response:
  Replace the SQL-control-statement (IF, CASE, LOOP, REPEAT,
  WHILE) that defines the body of the handler-declaration with an
  SQL-compound-statement (BEGIN).
  
  Severity:
    4 (warning)
  
  
  Additional keywords: DB2PRECOMPILER MSGDSNH4772I MSGDSNH4773I
                       SQLEXTERNALSQLPL SQLNATIVESQLPL SQLSP
  

Problem conclusion

Temporary fix

Comments

• The DB2 precompiler is modified to support the HOST(SQLPL)
  precompiler option for providing a checkout inspection for an
  SQL procedure definition.
  

APAR Information

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

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

  UK63419 UK63420

Modules/Macros

• DSNHAPLY DSNHCCB  DSNHDBRM DSNHERRM DSNHLIST
  DSNHNBRM DSNHNTRY DSNHOPTS DSNHOPTU DSNHPSMI DSNHSMF1 DSNHSMF2
  DSNHSMF3 DSNHSMP2 DSNHSMS1 DSNHSMUT DSNHSM1  DSNHSM2  DSNHSM2A
  DSNHSM2V DSNHSM5R DSNHSTMS DSNHTEXT DSNHTOKR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK63420

     UP11/01/06 P F101

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.