External stored procedures as main programs and subprograms

A stored procedure that runs in a WLM-established address space and uses Language Environment® Release 1.7 or a subsequent release can be either a main program or a subprogram. A stored procedure that runs as a subprogram can perform better because Language Environment does less processing for it.

In general, a subprogram must do the following extra tasks that Language Environment performs for a main program:

Initialization and cleanup processing
Allocating and freeing storage
Closing all open files before exiting

When you code stored procedures as subprograms, follow these rules:

Follow the language rules for a subprogram. For example, you cannot perform I/O operations in a PL/I subprogram.
Avoid using statements that terminate the Language Environment enclave when the program ends. Examples of such statements are STOP or EXIT in a PL/I subprogram, or STOP RUN in a COBOL subprogram. If the enclave terminates when a stored procedure ends, and the client program calls another stored procedure that runs as a subprogram, Language Environment must build a new enclave. As a result, the benefits of coding a stored procedure as a subprogram are lost.
In COBOL stored procedures that are defined as PROGRAM TYPE SUB and STAY RESIDENT YES, if you use stored procedure parameters as host variables, set the SQL-INIT-FLAG variable to 0. This variable is generated by the Db2 precompiler. Setting it to 0 ensures that the SQLDA is updated with the current addresses.

The following table summarizes the characteristics that define a main program and a subprogram.

Table 1. Characteristics of main programs and subprograms
Language	Main program	Subprogram
Assembler	MAIN=YES is specified in the invocation of the CEEENTRY macro.	MAIN=NO is specified in the invocation of the CEEENTRY macro.
C	Contains a main() function. Pass parameters to it through argc and argv.	A fetchable function. Pass parameters to it explicitly.
COBOL	A COBOL program that ends with GOBACK	A dynamically loaded subprogram that ends with GOBACK
PL/I	Contains a procedure declared with OPTIONS(MAIN)	A procedure declared with OPTIONS(FETCHABLE)

The following code shows an example of coding a C stored procedure as a subprogram.

/******************************************************************/
/*  This C subprogram is a stored procedure that uses linkage     */
/*  convention GENERAL and receives 3 parameters.                 */
/******************************************************************/
#pragma linkage(cfunc,fetchable)
#include <stdlib.h>
void cfunc(char p1[11],long *p2,short *p3)
{
  /****************************************************************/
  /*  Declare variables used for SQL operations.  These variables */
  /*  are local to the subprogram and must be copied to and from  */
  /*  the parameter list for the stored procedure call.           */
  /****************************************************************/
  EXEC SQL BEGIN DECLARE SECTION;
    char parm1[11];
    long int parm2;
    short int parm3;
  EXEC SQL END DECLARE SECTION;

  /*************************************************************/
  /*  Receive input parameter values into local variables.     */
  /*************************************************************/
  strcpy(parm1,p1);
  parm2 = *p2;
  parm3 = *p3;
  /*************************************************************/
  /*  Perform operations on local variables.                   */
  /*************************************************************/
  ⋮
  /*************************************************************/
  /*  Set values to be passed back to the caller.              */
  /*************************************************************/
  strcpy(parm1,"SETBYSP");
  parm2 = 100;
  parm3 = 200;
  /*************************************************************/
  /*  Copy values to output parameters.                        */
  /*************************************************************/
  strcpy(p1,parm1);
  *p2 = parm2;
  *p3 = parm3;
}

The following code shows an example of coding a C++ stored procedure as a subprogram.

/******************************************************************/
/*  This C++ subprogram is a stored procedure that uses linkage   */
/*  convention GENERAL and receives 3 parameters.                 */
/*  The extern statement is required.                             */
/******************************************************************/
extern  "C"  void cppfunc(char p1[11],long *p2,short *p3);
#pragma linkage(cppfunc,fetchable)
#include <stdlib.h>
EXEC SQL INCLUDE SQLCA;
void cppfunc(char p1[11],long *p2,short *p3)
{
  /****************************************************************/
  /*  Declare variables used for SQL operations.  These variables */
  /*  are local to the subprogram and must be copied to and from  */
  /*  the parameter list for the stored procedure call.           */
  /****************************************************************/
  EXEC SQL BEGIN DECLARE SECTION;
    char parm1[11];
    long int parm2;
    short int parm3;
  EXEC SQL END DECLARE SECTION;

  /*************************************************************/
  /*  Receive input parameter values into local variables.     */
  /*************************************************************/
  strcpy(parm1,p1);
  parm2 = *p2;
  parm3 = *p3;
  /*************************************************************/
  /*  Perform operations on local variables.                   */
  /*************************************************************/
  ⋮
  /*************************************************************/
  /*  Set values to be passed back to the caller.              */
  /*************************************************************/
  strcpy(parm1,"SETBYSP");
  parm2 = 100;
  parm3 = 200;
  /*************************************************************/
  /*  Copy values to output parameters.                        */
  /*************************************************************/
  strcpy(p1,parm1);
  *p2 = parm2;
  *p3 = parm3;
}