CALL statement syntax for invoking DSNTPSMP

You can invoke the SQL procedure processor, DSNTPSMP, from an application program by using an SQL CALL statement. DSNTPSMP prepares an external SQL procedure.

The following diagrams show the syntax of invoking DSNTPSMP through the SQL CALL statement:

Figure 1. DSNTPSMP syntax
Read syntax diagramSkip visual syntax diagramCALLSYSPROC.DSNTPSMP(function,SQL-procedure-name, SQL-procedure-sourceempty-string , bind-optionsempty-string , compiler-optionsempty-string , precompiler-optionsempty-string , prelink-optionsempty-string , link-optionsempty-string , alter-statementempty-string , source-data-set-nameempty-string , build-ownerempty-string , build-utilityempty-string ,return-code)
Figure 2. CALL DSNTPSMP bind-options, compiler-options, precompiler-options, prelink-options, link-options
Read syntax diagramSkip visual syntax diagram' ,option '
Note: You must specify:
  • The DSNTPSMP parameters in the order listed
  • The empty string if an optional parameter is not required for the function
  • The options in the order: bind, compiler, precompiler, prelink, and link
The DSNTPSMP parameters are:
function
A VARCHAR(20) input parameter that identifies the task that you want DSNTPSMP to perform. The tasks are:
BUILD
Creates the following objects for an external SQL procedure:
  • A DBRM, in the data set that DD name SQLDBRM points to
  • A load module, in the data set that DD name SQLLMOD points to
  • The C language source code for the external SQL procedure, in the data set that DD name SQLCSRC points to
  • The stored procedure package
  • The stored procedure definition
The following input parameters are required for the BUILD function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name

If you choose the BUILD function, and an external SQL procedure with name SQL-procedure-name already exists, DSNTPSMP issues an error message and terminates.

BUILD_DEBUG
Creates the following objects for an external SQL procedure and includes the preparation necessary to debug the external SQL procedure with the SQL Debugger and the Unified Debugger:
  • A DBRM, in the data set that DD name SQLDBRM points to
  • A load module, in the data set that DD name SQLLMOD points to
  • The C language source code for theexternal SQL procedure, in the data set that DD name SQLCSRC points to
  • The stored procedure package
  • The stored procedure definition
The following input parameters are required for the BUILD_DEBUG function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name

If you choose the BUILD_DEBUG function, and an external SQL procedure with name SQL-procedure-name already exists, DSNTPSMP issues an error message and terminates.

REBUILD
Replaces all objects that were created by the BUILD function for an external SQL procedure, if it exists, otherwise creates those objects.
The following input parameters are required for the REBUILD function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name
REBUILD_DEBUG
Replaces all objects that were created by the BUILD_DEBUG function for an external SQL procedure, if it exists, otherwise creates those objects, and includes the preparation necessary to debug the external SQL procedure with the SQL Debugger and the Unified Debugger.
The following input parameters are required for the REBUILD_DEBUG function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name
REBIND
Binds the external SQL procedure package for an existing external SQL procedure.
The following input parameter is required for the REBIND function:
  • SQL-procedure name
DESTROY
Deletes the following objects for an existing external SQL procedure:
  • The DBRM, from the data set that DD name SQLDBRM points to
  • The load module, from the data set that DD name SQLLMOD points to
  • The C language source code for the external SQL procedure, from the data set that DD name SQLCSRC points to
  • The stored procedure package
  • The stored procedure definition
The following input parameter is required for the DESTROY function:
  • SQL-procedure name
ALTER
Updates the registration for an existing external SQL procedure.
The following input parameters are required for the ALTER function:
  • SQL-procedure name
  • alter-statement
ALTER_REBUILD
Updates an existing external SQL procedure.
The following input parameters are required for the ALTER_REBUILD function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name
ALTER_REBUILD_DEBUG
Updates an existing external SQL procedure, and includes the preparation necessary to debug the external SQL procedure with the SQL Debugger and the Unified Debugger.
The following input parameters are required for the ALTER_REBUILD_DEBUG function:
  • SQL-procedure name
  • SQL-procedure-source or source-data-set-name
ALTER_REBIND
Updates the registration and binds the SQL package for an existing external SQL procedure.
The following input parameters are required for the ALTER_REBIND function:
  • SQL-procedure name
  • alter-statement
QUERYLEVEL
Obtains the interface level of the build utility invoked. No other input is required.
SQL-procedure-name
A VARCHAR(261) input parameter that specifies the external SQL procedure name.

The name can be qualified or unqualified. The name must match the procedure name that is specified within the CREATE PROCEDURE statement that is provided in SQL-procedure-source or that is obtained from source-data-set-name. In addition, the name must match the procedure name that is specified within the ALTER PROCEDURE statement that is provided in alter-statement. Do not mix qualified and unqualified references.

SQL-procedure-source
A CLOB(2M) input parameter that contains the CREATE PROCEDURE statement for the external SQL procedure. If you specify an empty string for this parameter, you need to specify the name source-data-set-name of a data set that contains the external SQL procedure source code.
bind-options
A VARCHAR(1024) input parameter that contains the options that you want to specify for binding the external SQL procedure package. Do not specify the MEMBER or LIBRARY option for the Db2 BIND PACKAGE command.
compiler-options
A VARCHAR(255) input parameter that contains the options that you want to specify for compiling the C language program that Db2 generates for the external SQL procedure.
precompiler-options
A VARCHAR(255) input parameter that contains the options that you want to specify for precompiling the C language program that Db2 generates for the external SQL procedure. Do not specify the HOST option.
prelink-options
A VARCHAR(255) input parameter that contains the options that you want to specify for prelinking the C language program that Db2 generates for the external SQL procedure.
link-options
A VARCHAR(255) input parameter that contains the options that you want to specify for linking the C language program that Db2 generates for the external SQL procedure.
alter-statement
A VARCHAR(32672) input parameter that contains the SQL ALTER PROCEDURE statement to process with the ALTER or ALTER_REBIND function.
source-data-set-name
A VARCHAR(80) input parameter that contains the name of a z/OS® sequential data set or partitioned data set member that contains the source code for the external SQL procedure. If you specify an empty string for this parameter, you need to provide the external SQL procedure source code in SQL-procedure-source.
build-owner
A VARCHAR(130) input parameter that contains the SQL identifier to serve as the build owner for newly created SQL stored procedures.

When this parameter is not specified, the value defaults to the value in the CURRENT SQLID special register when the build utility is invoked.

build-utility
A VARCHAR(255) input parameter that contains the name of the build utility that is invoked. The qualified form of the name is suggested, for example, SYSPROC.DSNTPSMP.
return-code
A VARCHAR(255) output parameter in which Db2 puts the return code from the DSNTPSMP invocation. The values are:
0
Successful invocation. The calling application can optionally retrieve the result set and then issue the required SQL COMMIT statement.
4
Successful invocation, but warnings occurred. The calling application should retrieve the warning messages in the result set and then issue the required SQL COMMIT statement.
8
Failed invocation. The calling application should retrieve the error messages in the result set and then issue the required SQL ROLLBACK statement.
99x
Where x is a digit in the range 0–9. Failed invocation with severe errors. The calling application should retrieve the error messages in the result set and then issue the required SQL ROLLBACK statement. To view error messages that are not in the result set, see the job log of the address space for the DSNTPSMP execution.
999
Unknown severe internal error
998
APF environment setup error
997
DSNREXX setup error
996
Global temporary table setup error
995
Internal REXX programming error
1.2x
Where x is a digit in the range 0–9. Level of DSNTPSMP when request is QUERYLEVEL. The calling application can retrieve the result set for additional information about the release and service level and then issue the required SQL COMMIT statement.