Sample programs to help you prepare and run external SQL procedures

Db2 provides sample jobs to help you prepare and run external SQL procedures. All samples are in data set DSN1310.SDSNSAMP. Before you can run the samples, you must customize them for your installation.

Deprecated function: External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.

See the prolog of each sample for specific instructions.

The following table lists the sample jobs that Db2 provides for external SQL procedures.

Table 1. External SQL procedure samples shipped with Db2
Member that contains source code Contents Purpose
DSNHSQL JCL procedure Precompiles, compiles, prelink-edits, and link-edits an external SQL procedure
DSNTEJ63 JCL job Invokes JCL procedure DSNHSQL to prepare external SQL procedure DSN8ES1 for execution
DSN8ES1 External SQL procedure A stored procedure that accepts a department number as input and returns a result set that contains salary information for each employee in that department
DSNTEJ64 JCL job Prepares client program DSN8ED3 for execution
DSN8ED3 C program Calls SQL procedure DSN8ES1
DSN8ES2 External SQL procedure A stored procedure that accepts one input parameter and returns two output parameters. The input parameter specifies a bonus to be awarded to managers. The external SQL procedure updates the BONUS column of DSN1310.SDSNSAMP. If no SQL error occurs when the external SQL procedure runs, the first output parameter contains the total of all bonuses awarded to managers and the second output parameter contains a null value. If an SQL error occurs, the second output parameter contains an SQLCODE.
DSN8ED4 C program Calls the SQL procedure processor, DSNTPSMP, to prepare DSN8ES2 for execution
DSN8WLMP JCL procedure A sample startup procedure for the WLM-established stored procedures address space in which DSNTPSMP runs
DSN8ED5 C program Calls external SQL procedure DSN8ES2
DSNTEJ65 JCL job Prepares and executes programs DSN8ED4 and DSN8ED5.

DSNTEJ65 uses DSNTPSMP, the SQL procedure processor, which requires that the default EBCDIC CCSID that is used by Db2 also be compatible with the C compiler. Do not run DSNTEJ65 if the default EBCDIC CCSID for Db2 is not compatible with the C compiler. Examples of incompatible CCSIDs include 290, 930, 1026, and 1155.
DSNTEJ67 JCL job

Prepares an existing external SQL procedure (sample DSN8.DSN8ES2) for conversion to a native SQL procedure.

DSNTEJ67 obtains the source of external SQL procedure DSN8.DSN8ES2 from the catalog and formats it into a data set. DSNTEJ67 executes DSNHPSM with HOST(SQLPL), obtains a listing for the source, and replaces the offending procedure options in the source data set.

DSNTIJRT JCL job Prepares a Db2 for z/OS® server for operation with the SQL Debugger and the Unified Debugger