Deploying External SQL Stored Procedures

Prerequisites

  • The file must contain a single CREATE PROCEDURE statement. If multiple are present, only the first is deployed.

To deploy an external SQL stored procedure, do these steps:

  1. Right-click anywhere in the body of a stored procedure open in the SQL editor and select Db2 for z/OS > Deploy Routine or click on the Deploy Routine option. The Deploy Routine wizard opens.
  2. Specify deployment, routine, and external options on the wizard pages, and then click Deploy. For descriptions of the options you can specify in this wizard, see the list at the end of this procedure.
  3. The results of the deploy operation are shown in the Execution Status dialog, where you can see any warnings or errors and link to more information about SQL codes and status.
Deployment options

Db2 Connection: Select a connection to a Db2 location to deploy the stored procedure to. The default is the active connection for the .spsql file, if one is defined.

Target schema: Set the target schema for unqualified stored procedures. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSchema is used. If the JDBC currentSchema property is not set, the JDBC connection username is used. If the stored procedure content declares the target schema, it appears in this field and cannot be changed.

Build owner: Specify the owner of the stored procedure. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSQLID is used. If currentSQLID is not set, the JDBC connection username is used.

Default path: Specify one or more schemas for resolving an unqualified data type, function, or procedure that is referenced by the procedure being deployed. Separate multiple schemas with commas, for example, "ADMF002","ADMF003","ADMF004".

Duplicate handling: Choose one of these options to specify how to handle a stored procedure that already exists:
  • Drop duplicates: Calls DROP PROCEDURE before running the CREATE PROCEDURE DDL. By default, Db2 activates the first version of a stored procedure.
  • Treat duplicate deployments as errors: Returns an error. If other versions of the procedure do not already exist, the procedure is created.

For more information about JDBC properties, see Common IBM Data Server Driver for JDBC and SQLJ properties for Db2 and Db2 for z/OS servers.

Routine options
Enable debugging: Select this checkbox to specify whether this procedure is available to be debugged.
Limitation: This checkbox is disabled for Java and SQLJ stored procedures. Debugging Java and SQLJ stored procedures is not supported.

WLM environment: Specify which Workload Manager environment to use for debugging the procedure. If the source file declares a WLM environment, it is used as the default and this field is disabled. If you enable debugging but leave this field empty, the default WLM environment is used.

ASU time limit: Specify the number of CPU seconds permitted for each SQL statement. The default value of 0 means no time limit. If the source file declares an ASU time limit, it is used as the default and this field is disabled.

Tip: The WLM environment and ASU time limit fields can be populated with values specified in the CREATE PROCEDURE statement.

Additional routine options: Specify any additional options that you want to include. Separate each option with a semicolon. For example: QUALIFIER ADMF002; ISOLATION LEVEL RS.

External Options
Options Descriptions
Build utility Identifies the Db2 SQL procedure processor, SYSPROC.DSNTPSMP, which is used to create an external SQL stored procedure.
Precompile options Specify options for precompiling the C language program that Db2 generates for the external SQL procedure. Do not specify the HOST option. For a list of these options see SQL processing options.
Compile options Specify options for compiling the C language program that Db2 generates for the external SQL procedure.
Prelink options Specify options for prelinking the C language program that Db2 generates for the external SQL procedure.
Link options Specify options for linking the C language program that Db2 generates for the external SQL procedure.
Bind options Specify options for binding the external SQL procedure package. Do not specify the MEMBER or LIBRARY option for the Db2 BIND PACKAGE command. For a list of these options, see BIND and REBIND options.