IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM13844

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-05

  • Closed date

    2010-12-21

  • Last modified date

    2011-02-01

  • 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.



Document information

More support for: DB2 for z/OS

Software version: 9.1

Reference #: PM13844

Modified date: 01 February 2011


Translate this page: