Creating an external SQL procedure by using DSNTPSMP

The SQL procedure processor, DSNTPSMP, is one of several methods that you can use to create and prepare an external SQL procedure. DSNTPSMP is a REXX stored procedure that you can invoke from your application program.

Before you begin

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.

Set up support for external SQL procedures. For more information, see Setting up support for external SQL procedures.

Also ensure that you have the required authorizations, as indicated in the following table, for invoking DSNTPSMP.

Table 1. Required authorizations for invoking DSNTPSMP
Required authorization Associated syntax for the authorization
Procedure privilege to run application programs that invoke the stored procedure. EXECUTE ON PROCEDURE SYSPROC.DSNTPSMP
Collection privilege to use BIND to create packages in the specified collection. You can use an asterisk (*) as the identifier for a collection. CREATE ON COLLECTION collection-id
Package privilege to use BIND or REBIND to bind packages in the specified collection. BIND ON PACKAGE collection-id.*
System privilege to use BIND with the ADD option to create packages and plans. BINDADD
Schema privilege to create, alter, or drop stored procedures in the specified schema. The BUILDOWNER authorization ID must have the CREATEIN privilege on the schema. You can use an asterisk (*) as the identifier for a schema. CREATEIN, ALTERIN, DROPIN ON SCHEMA schema-name
Table privileges to select or delete from, insert into, or update the specified catalog tables.
  • SELECT ON TABLE SYSIBM.SYSROUTINES
  • SELECT ON TABLE SYSIBM.SYSPARMS
  • SELECT, INSERT, UPDATE, DELETE ON TABLE SYSIBM.SYSROUTINES_SRC
  • SELECT, INSERT, UPDATE, DELETE ON TABLE SYSIBM.SYSROUTINES_OPTS
  • ALL ON TABLE SYSIBM.SYSPSMOUT
Any privileges that are required for the SQL statements and that are contained within the SQL procedure body. These privileges must be associated with the OWNER authorization-id that is specified in your bind options. The default owner is the user that is invoking DSNTPSMP. Syntax varies depending on the SQL procedure body

Procedure

To create an external SQL procedure by using DSNTPSMP:

  1. Write an application program that calls DSNTPSMP. Include the following items in your program:
    • A CLOB host variable that contains a CREATE PROCEDURE statement for the external SQL procedure. That statement should include the FENCED keyword or the EXTERNAL keyword, and the procedure body, which is written in SQL.

      Alternatively, instead of defining a host variable for the CREATE PROCEDURE statement, you can store the statement in a data set member.

    • An SQL CALL statement with the BUILD function. The CALL statement should use the proper syntax for invoking DSNTPSMP.
      Pass the SQL procedure source to DSNTPSMP as one of the following input parameters:
      SQL-procedure-source
      Use this parameter if you defined a host variable in your application to contain the CREATE PROCEDURE statement.
      source-data-set-name
      Use this parameter if you stored the CREATE PROCEDURE statement in a data set.
    • Based on the return value from the CALL statement, issue either an SQL COMMIT or a ROLLBACK statement. If the return value is 0 or 4, issue a COMMIT statement. Otherwise, issue a ROLLBACK statement.

      You must process the result set before issuing the COMMIT or ROLLBACK statement.

      A QUERYLEVEL request must be followed by the COMMIT statement.

  2. Precompile, compile, and link-edit the application program.
  3. Bind a package for the application program.
  4. Run the application program.