Example PL/I stored procedure with a GENERAL WITH NULLS linkage convention

You can call a stored procedure that uses the GENERAL WITH NULLS linkage convention from a PL/I program.

This example stored procedure searches the Db2 SYSIBM.SYSROUTINES catalog table for a row that matches the input parameters from the client program. The two input parameters contain values for NAME and SCHEMA.

The linkage convention for this stored procedure is GENERAL WITH NULLS.

The output parameters from this stored procedure contain the SQLCODE from the SELECT operation, and the value of the RUNOPTS column retrieved from the SYSIBM.SYSROUTINES table.

The CREATE PROCEDURE statement for this stored procedure might look like this:

CREATE PROCEDURE GETPRML(PROCNM CHAR(18) IN, SCHEMA CHAR(8) IN,
  OUTCODE INTEGER OUT, PARMLST VARCHAR(254) OUT)
  LANGUAGE PLI
  DETERMINISTIC
  READS SQL DATA
  EXTERNAL NAME "GETPRML"
  COLLID GETPRML
  ASUTIME NO LIMIT
  PARAMETER STYLE GENERAL WITH NULLS
  STAY RESIDENT NO
  RUN OPTIONS "MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)"
  WLM ENVIRONMENT SAMPPROG
  PROGRAM TYPE MAIN
  SECURITY DB2
  RESULT SETS 0
  COMMIT ON RETURN NO;

The following example is a PL/I stored procedure with linkage convention GENERAL WITH NULLS.

*PROCESS SYSTEM(MVS);
 
 GETPRML:
   PROC(PROCNM, SCHEMA, OUT_CODE, PARMLST, INDICATORS)
                       OPTIONS(MAIN NOEXECOPS REENTRANT);
 
   DECLARE PROCNM CHAR(18),    /* INPUT parm -- PROCEDURE name */
           SCHEMA CHAR(8),     /* INPUT parm -- User's schema  */
 
           OUT_CODE FIXED BIN(31), /* OUTPUT -- SQLCODE from   */
                                   /*   the SELECT operation.  */
           PARMLST CHAR(254)       /* OUTPUT -- PARMLIST for   */
                   VARYING;        /*  the matching row in     */
                                   /*  SYSIBM.SYSROUTINES      */
   DECLARE 1 INDICATORS,       /* Declare null indicators for  */
                               /* input and output parameters. */
            3 PROCNM_IND   FIXED BIN(15),
            3 SCHEMA_IND   FIXED BIN(15),
            3 OUT_CODE_IND FIXED BIN(15),
            3 PARMLST_IND  FIXED BIN(15);
 
   EXEC SQL INCLUDE SQLCA;
 
   IF PROCNM_IND<0 |
      SCHEMA_IND<0 THEN
    DO;                      /* If any input parm is NULL,     */
      OUT_CODE = 9999;       /* Set output return code.        */
      OUT_CODE_IND = 0;
                             /* Output return code is not NULL.*/
      PARMLST_IND = -1;      /* Assign NULL value to PARMLST.  */
    END;
   ELSE                      /* If input parms are not NULL,   */
    DO;                      /*                                */
   /************************************************************/
   /* Issue the SQL SELECT against the SYSIBM.SYSROUTINES      */
   /* DB2 catalog table.                                       */
   /************************************************************/
       EXEC SQL
         SELECT RUNOPTS INTO :PARMLST
             FROM SYSIBM.SYSROUTINES
             WHERE NAME=:PROCNM AND
                   SCHEMA=:SCHEMA;
       PARMLST_IND = 0;      /* Mark PARMLST as not NULL.      */
 
       OUT_CODE = SQLCODE;   /* return SQLCODE to caller       */
       OUT_CODE_IND = 0;
       OUT_CODE_IND = 0;     /* Output return code is not NULL.*/
    END;
   RETURN;
 
 END GETPRML;