Examples of invoking the SQL procedure processor (DSNTPSMP)

You can invoke the BUILD, DESTROY, REBUILD, and REBIND functions of DSNTPSMP.

DSNTPSMP BUILD function: Call DSNTPSMP to build an external SQL procedure. The information that DSNTPSMP needs is listed in the following table:
Table 1. The functions DSNTPSMP needs to BUILD an SQL procedure
Function BUILD
External SQL procedure name MYSCHEMA.SQLPROC
Source location String in CLOB host variable procsrc
Bind options VALIDATE(BIND)
Compiler options SOURCE, LIST, LONGNAME, RENT
Precompiler options SOURCE, XREF, STDSQL(NO)
Prelink options None specified
Link options AMODE=31, RMODE=ANY, MAP, RENT
Build utility SYSPROC.DSNTPSMP
Return value String returned in varying-length host variable returnval
The CALL statement is: 
EXEC SQL CALL SYSPROC.DSNTPSMP('BUILD','MYSCHEMA.SQLPROC',:procsrc,
 'VALIDATE(BIND)',
 'SOURCE,LIST,LONGNAME,RENT',
 'SOURCE,XREF,STDSQL(NO)',
 '',
 'AMODE=31,RMODE=ANY,MAP,RENT',
 '','','','SYSPROC.DSNTPSMP',
 :returnval);
DSNTPSMP DESTROY function: Call DSNTPSMP to delete an external SQL procedure definition and the associated load module. The information that DSNTPMSP needs is listed in the following table:
Table 2. The functions DSNTPSMP needs to DESTROY an SQL procedure
Function DESTROY
External SQL procedure name MYSCHEMA.OLDPROC
Return value String returned in varying-length host variable returnval
The CALL statement is: 
EXEC SQL CALL SYSPROC.DSNTPSMP('DESTROY','MYSCHEMA.OLDPROC','',
 '','','','','',
 '','','','',
 :returnval);
DSNTPSMP REBUILD function: Call DSNTPSMP to re-create an existing external SQL procedure. The information that DSNTPMSP needs is listed in the following table:
Table 3. The functions DSNTPSMP needs to REBUILD an SQL procedure
Function REBUILD
External SQL procedure name MYSCHEMA.SQLPROC
Bind options VALIDATE(BIND)
Compiler options SOURCE, LIST, LONGNAME, RENT
Precompiler options SOURCE, XREF, STDSQL(NO)
Prelink options None specified
Link options AMODE=31, RMODE=ANY, MAP, RENT
Source data set name Member PROCSRC of partitioned data set DSN1210.SDSNSAMP
Return value String returned in varying-length host variable returnval
The CALL statement is: 
EXEC SQL CALL SYSPROC.DSNTPSMP('REBUILD','MYSCHEMA.SQLPROC','',
 'VALIDATE(BIND)',
 'SOURCE,LIST,LONGNAME,RENT',
 'SOURCE,XREF,STDSQL(NO)',
 '',
 'AMODE=31,RMODE=ANY,MAP,RENT',
 '','DSN1210.SDSNSAMP(PROCSRC)','','',
 :returnval);
If you want to re-create an existing external SQL procedure for debugging with the SQL Debugger and the Unified Debugger, use the following CALL statement, which includes the REBUILD_DEBUG function: 
EXEC SQL CALL SYSPROC.DSNTPSMP('REBUILD_DEBUG','MYSCHEMA.SQLPROC','',
 'VALIDATE(BIND)',
 'SOURCE,LIST,LONGNAME,RENT',
 'SOURCE,XREF,STDSQL(NO)',
 '',
 'AMODE=31,RMODE=ANY,MAP,RENT',
 '','DSN1210.SDSNSAMP(PROCSRC)','','',
 :returnval);
DSNTPSMP REBIND function: Call DSNTPSMP to rebind the package for an existing external SQL procedure. The information that DSNTPMSP needs is listed in the following table:
Table 4. The functions DSNTPSMP needs to REBIND an SQL procedure
Function REBIND
ExternalSQL procedure name MYSCHEMA.SQLPROC
Bind options VALIDATE(RUN), ISOLATION(RR)
Return value String returned in varying-length host variable returnval
The CALL statement is: 
EXEC SQL CALL SYSPROC.DSNTPSMP('REBIND','MYSCHEMA.SQLPROC','',
 'VALIDATE(RUN),ISOLATION(RR)','','','','',
 '','','','',
 :returnval);