UL/DB2 request example

Overview

This topic provides a complete UL/DB2 request that connects to a DB2 subsystem and uses the $SPIFY function to access a DB2 table. The sample request illustrates much of the material discussed in the earlier chapters. Specifically, the sample request demonstrates using:

• Model 204 DEFINE and OPEN LINK commands
• CAF CONNECT TO call
• User Language $STATUS, $STATUSD, and $ERRMSG functions
• UL/DB2 $SPIFY function

About the sample request

The example contains two procedures: DB2PRC and SPIFY. The function of each procedure is described in the following sections.

DB2PRC procedure

The DB2PRC procedure defines the required UL/DB2 entities and enables the link to DB2. Specifically, the DB2PRC procedure:

• Uses the DEFINE commands to establish the link, processgroup, and process
• Uses the OPEN LINK command to enable the link between the User Language procedure and the DB2 subsystem

In the following example, note that the link name specified in the DEFINE LINK, DEFINE PROCESSGROUP, and OPEN LINK commands must be the same (in this example, DB2LNK).

Also, note that the processgroup name specified in the DEFINE PROCESSGROUP and DEFINE PROCESS commands must be the same (in this example, DB2GRP).

SPIFY procedure

The SPIFY procedure connects the User Language request to a DB2 subsystem and retrieves data from a DB2 table. Specifically, the SPIFY procedure:

• Uses the CONNECT TO call to create a thread between a User Language procedure and DB2.

  The value specified in the CONNECT TO call must be the same value given in the DESTINATION parameter of the DEFINE PROCESS command (in this example, BOSTON).

  Also, the CONNECT TO call must be the first EXEC DB2 statement in the procedure.

• Uses the $STATUS function to determine if the CONNECT was successful. If it failed, the procedure returns diagnostic information through the $STATUS, $STATUSD, and $ERRMSG functions.
• Uses the $SPIFY function to select data from a DB2 table.

Sample request code

Note: Before running this procedure, you must set the required UL/DB2 User 0 parameters, DB2THRD and DB2PLAN, in the CCAIN input stream. See Setting the UL/DB2 User 0 parameters for more information on User 0 parameters.

The code for the sample request is:

PROCEDURE DB2PRC DEFINE LINK DB2LNK WITH SCOPE=SYSTEM TRANSPORT=INTERNAL - PROTOCOL=CAF DEFINE PROCESSGROUP DB2GRP WITH SCOPE=SYSTEM LINK=DB2LNK - DB2ID=DSN DEFINE PROCESS CCADB2 WITH SCOPE=SYSTEM - DESTINATION=(DB2GRP,BOSTON) OPEN LINK DB2LNK END PROCEDURE PROCEDURE SPIFY B * EXEC DB2 CONNECT TO BOSTON END EXEC IF $STATUS NE 0 THEN PRINT ' $STATUS=' AND $STATUS PRINT '$STATUSD=' AND $STATUSD PRINT $ERRMSG JUMP TO FINI END IF * PRINT $SPIFY('SELECT * FROM ??TABLE') * FINI: END END PROCEDURE

The SPIFY procedure uses the User Language ?? construct. When the request is run, the user is prompted to supply a value for the table name.

The output from this request is shown in the following section.

Sample request output

The output from the sample request is shown in the following figure. A value of "TDEPT" was supplied for the table name.

Sample request output