Job DSNTEJ66

Job DSNTEJ66 creates a sample native SQL procedure. It also prepares and executes a sample caller of that SQL procedure on the local Db2 subsystem. It then modifies the SQL procedure slightly, activates the change on the local subsystem, and executes the sample caller to call the updated SQL procedure.

Specifically, job DSNTEJ66 completes the following actions:

  • Creates a sample native SQL procedure called DSN8.DSN8ES3 that generates and returns a CREATE PROCEDURE statement for a given stored procedure.
  • Prepares and executes a sample caller of DSN8ES3 called DSN8ED9.
  • Illustrates how to use ALTER PROCEDURE... ADD VERSION to create a version V2 of DSN8ES3 that does the same thing as the original version but also adds a terminating semicolon at the end of the generated CREATE PROCEDURE statement.
  • Illustrates how to use ALTER ACTIVATE to make version V2 the active version of DSN8ES3.
  • If you specify a remote Db2 subsystem in the REMOTE LOCATION field and it does not match the name that is specified in the DB2 LOCATION NAME field of panel DSNTIPR, additional job steps do the following:
    • Deploys the sample native SQL procedure on the remote Db2 subsystem.
    • Binds the sample caller on the remote Db2 subsystem and execute it to call the native SQL procedure at that site.
    If the REMOTE LOCATION field is blank or matches the name that is specified in the DB2 LOCATION NAME field of panel DSNTIPR, the deploy-related steps are removed from DSNTEJ66 by the installation CLIST.

    As part of the setup for deploying DSN8ES3 on a remote server, the DSNTEP2 application needs to be able to connect to the remote site.

The installation CLIST edits this job only if the following conditions are met:

  • You specified AUTO or COMMAND in the DDF STARTUP OPTION field of panel DSNTIPR.
  • You specified a name in the WLM ENVIRONMENT field of panel DSNTIPX.

Before running DSNTEJ66, perform the manual editing described in the dependencies information in the job prolog. You must also manually tailor DSN8WLMP, the sample WLM startup procedure for DSNTPSMP. If DSNTEJ66 runs successfully, it produces the return codes that are shown in the table below.

Table 1. DSNTEJ66 return codes
Step
PROCSTEP
Return code
PH066S01   0000
PH066S02   0000
PH066S03   0000
PH066S04
PC
C
PLKED1
LKED
0000
0000
0004
0000
PH066S05   0000 or 0004
PH066S06   0000
PH066S07   0000
PH066S08   0000
PH066S09   0000
PH066S10   0000
PH066S11   0000
PH066S12   0000 or 0004
PH066S13   0000
PH066S14   0000
PH066S15   0000 or 0004
PH066S16   0000
Start of change
Note:
  1. The PLKED step does not exist in the job if the value of the C LE PRELINKER PHASE field is set to NO.
End of change

You can compare the output from this job to the sample output for DSNTEJ66, which is found in member DSN8TJ66 in the data set named prefix.SDSNIVPD.