Using the Db2 precompiler to assist you in converting an external SQL procedure to a native SQL procedure

The Db2 precompiler can be useful when considering any conversion of an external SQL procedure to a native SQL procedure.

About this task

Use the Db2 precompiler to inspect the SQL procedure source from a native SQL PL perspective. A listing is produced that helps to isolate problems and incompatibilities between external and native SQL procedure coding. Source changes can then be made before making any changes in Db2.

Procedure

To inspect the quality of native SQL PL source coding using the Db2 precompiler:

  1. Copy the original SQL PL source code to a FB80 data set. Reformat the source as needed to fit within the precompiler margins.
  2. Precompile the SQL PL source by executing program DSNHPSM with the HOST(SQLPL) option.
  3. Inspect the produced listing (SYSPRINT). Pay attention to error and warning messages.
  4. Modify the SQL PL source to address coding problems that are identified by messages in the listing.
  5. Repeat steps 1 - 4 until all error and warning messages are resolved. Then address informational messages as needed.
  6. Copy the modified SQL PL source file back to its original source format, reformatting as needed.

Results

Sample JCL DSNTEJ67 demonstrates this process for an external SQL procedure that was produced using the Db2 SQL procedure processor DSNTPSMP.