Running External SQL Stored Procedures

Several methods for running external SQL stored procedures are available:
  • Use the Run As > Db2 for z/OS Routine menu option in the SQL editor pop-up menu.
  • Use the Db2 for z/OS > Call Stored Procedure menu option in the SQL editor pop-up menu.
  • Use the Run toolbar button Run toolbar button.
  • Open the pop-up menu for a stored procedure object in the Catalog navigation and select the Call Routine option.
All methods look for an existing Db2 for z/OS run configuration that matches the file in context. If no match exists the tooling creates a run configuration for the file in context and uses it. If necessary, the Run Routine window opens to prompt you for variable or parameter values. If the SQL statement has been run against the active database recently, the window can be populated with recent values for the Data Type, Null, and Value fields.

The Input Parameters dialog includes Load and Save options, allowing users to export parameter values to an XML file or load them back later. This is especially useful for large or frequently reused parameter sets. The dialog displays single-line fields for basic types and multi-line fields for XML and LOB types.

The results of the call 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. The CALL request is also added to the Query History folder of the Db2 for z/OS connection.

Creating a run configuration

  1. To open the Run Configurations window, click Run > Run Configurations.
  2. In the navigation list, right-click Db2 for z/OS Routine and select New Configuration. A new configuration is added to the navigation list as a child of Db2 for z/OS Routine, and the new configuration page opens in the Run Configurations editor pane.
  3. On the new configuration page, click Browse, and then type a search string, such as *.spsql in the text field. This string matches all stored procedures is defined in a local or remote z/OS project in the z/OS Projects view.
    Db2 for z/OS Routine Run Configuration
  4. Select a stored procedure from the list and click OK.
  5. In the Name field on the new configuration page, type a name for the new configuration.
  6. To save the run configuration, click Apply.

Mapping remote files

IBM® Developer for z/OS® defines the SQL editor as the default editor for files with the extension .spsql. Files or partitioned data sets that define stored procedures must have this file extension.

The product provides a z/OS data set mapping definition called **SPSQL, that maps members of partitioned data sets with the SPSQL low-level qualifier to workstation files with the .spsql extension. This mapping ensures that the default editor association is recognized for data set members.

To ensure that the file mapping functions correctly, verify that the **SPSQL z/OS file system mapping precedes the **SQL mapping in the z/OS File System Mapping view. To change the order of the **SQL and **SPSQL mappings, right-click the **SPSQL mapping and select Raise Priority.

z/OS File System Mapping views