Deploying Native SQL Stored Procedures

To deploy a native 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. The Deploy Routine wizard opens.
2. Specify deployment and routine 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:

• Alter duplicates: Modifies the CREATE PROCEDURE DDL to ALTER PROCEDURE. Exception: If you’re using CREATE OR REPLACE PROCEDURE, which is available in Db2 12 function level 507 or later, this option does not result in any modifications. This option is available for native SQL stored procedures only.
• 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.

Activate the deployed version: Select this check box to activate the procedure upon deployment. By default, Db2 activates the first version of a stored procedure. This checkbox is enabled only if the Duplicate handling option is set to Alter Duplicates. This option is available for native SQL stored procedures only.

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.