Calling a Program With SQL EX

In this section:

How to:

Using SQL EX is similar to using EXEC, the difference is that the output from SQL EX is stored into a HOLD file called SQLOUT. The resulting SQLOUT file can then be processed with additional SELECT or TABLE statements which may (or may not) contain additional selection criteria, and possibly return less fields or create a virtual field that is derived from the data.


Top of page

x
Syntax: How to Call a Program From Dialogue Manager Using SQL EX
CALLPGM progname[,parmval1][,...]
END

or

SET EXORDER=PGM/FEX
EX[EC] progname[parmval1][,...]
END

or

SET EXORDER=PGM/FEX
SET SQLENGINE=CPGFOC
SQL EX PROGRAM [parmval1][,...] 
TABLE FILE SQLOUT 
PRINT field [ON TABLE PCHOLD]
END 
SET SQLENGINE=OFF

where:

progname

Is the name of the program to be run. (If CALLPGM is used, it cannot be another Dialogue Manager procedure.)

parmval1

Is an optional positional Dialogue Manager parameter passed to progname. A Dialogue Manager parameter is an alphanumeric value. See Passing Parameters for examples.

The length of a single parameter (for example, parmval1) cannot exceed 32,000 characters. The total length of all specified parameters cannot exceed 32,000 characters.

END

Is a required command that terminates CALLPGM or EXEC.


Top of page

x
Switching Plans in DB2 (MVS Only)

How to:

DB2 requires that all programmed interaction with a database be controlled at the program module level. The program is represented to the database using an object called a plan. The installation procedure automatically creates a plan for a server. When the server accesses the RDBMS, it uses the plan name.

When a program executed by CALLPGM contains SQL statements, it may be necessary to switch from the plan named in the installation procedure to the plan required by the program.



x
Syntax: How to Switch Plans in DB2
SQL DB2 SET PLAN &progplan 
CALLPGM &program...
END
SQL DB2 SET PLAN ' '

where:

&progplan

Is the name of the plan required by the program.

&program

Is the name of the program to be run.

SET PLAN ' '

Resets the plan.

An alternative is to use DB2 3.1 packages. Here, each CALLPGM program has its own package (called by the same name as the program), and all programs are included in the package list for the plan.

For example, assume that your server plan is called EDASQL. You wish to have two stored procedures, called SPG1 and SPG2, that use static SQL to access DB2.

In this case, there are three DB2 database resource modules (DBRMs) created: EDASQL, SPG1, and SPG2. Create three packages, called EDASQL.EDASQL, EDASQL.SPG1, and EDASQL.SPG2, using the command CREATE PACKAGE. Then bind the packages together into a plan using the command BIND PLAN with the package list option. When the server executes, DB2 automatically selects the package with the same name as the program.

For more information on plans, see the applicable DB2 manuals.



Example: Processing an Answer Set on the Server

When executing a CALLPGM stored procedure, it is sometimes desirable to retain the answer set on the server. The following example illustrates the method used to retain the answer set on the server and assumes the called program is a fex with syntax to call the actual external procedure:

 
1. SQL EDA SET SERVER servername 
2. SQL EDA EX programname parm1,...;
  
3. TABLE FILE SQLOUT
   PRINT *
   ON TABLE HOLD AS filename 
   END
  
4. TABLE FILE filename 
   PRINT col2 AS 'COLUMN,   2'
         col3 AS 'COLUMN,   3'
   END

The procedure processes as follows:

  1. Identifies the remote server name in which to execute remote requests.
  2. Executes the program name on the remote server.
  3. Specifies that the temporary information is to be retained on the server in an extract file.
  4. Executes a TABLE request to generate an answer set containing column 2 and column 3 in the retained table.

Note:


iWay Software