Parameters for external user-defined functions

Input parameter values

DB2 obtains the input parameters from the invoker's parameter list, and your user-defined function receives those parameters according to the rules of the host language in which the user-defined function is written. The number of input parameters is the same as the number of parameters in the user-defined function invocation. If one of the parameters in the function invocation is an expression, DB2 evaluates the expression and assigns the result of the expression to the parameter.

For all data types except LOBs, ROWIDs, locators, and VARCHAR (with C language), see the tables listed in the following table for the host data types that are compatible with the data types in the user-defined function definition.

For LOBs, ROWIDs, and locators, see the following table for the assembler data types that are compatible with the data types in the user-defined function definition.

DS FL4

var     DS 0FL4
var_length  DS FL4
var_data DS CLn

var     DS 0FL4
var_length  DS FL4
var_data DS CL65535
        ORG var_data+(n-65535)

var DS  0FL4
var_length  DS FL4
var_data DS CLn

var     DS 0FL4
var_length  DS FL4
var_data DS CL65535
        ORG var_data+(n-65535)

var     DS 0FL4
var_length  DS FL4
var_data DS CLm

var     DS 0FL4
var_length  DS FL4
var_data DS CL65534
        ORG var_data+(m-65534)

DS HL2,CL40

For LOBs, ROWIDs, VARCHARs, and locators see the following table for the C data types that are compatible with the data types in the user-defined function definition.

unsigned long

struct
{unsigned long length;
  char data[n];
}            var;

struct
{unsigned long length;
  char var_data[n];
}            var;

struct
{unsigned long length;
sqldbchar     data[n];
}      var;

struct {
   short int length;
   char data[40];
} var;

char data[n+1];

struct
{short len;
 char data[n];
}    var;

For LOBs, ROWIDs, and locators, see the following table for the COBOL data types that are compatible with the data types in the user-defined function definition.

Table 4. Compatible COBOL declarations for LOBs, ROWIDs, and locators

SQL data type in definition	COBOL declaration
TABLE LOCATOR BLOB LOCATOR CLOB LOCATOR DBCLOB LOCATOR	01 var PIC S9(9) COMP-5
BLOB(n)	01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC X(n).
CLOB(n)	01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC X(n).
DBCLOB(n)	01 var. 49 var-LENGTH PIC S9(9) COMP-5. 49 var-DATA PIC G(n) DISPLAY-1.
ROWID	01 var. 49 var-LEN PIC S9(4) COMP-5. 49 var-TEXT PIC X(40).

For LOBs, ROWIDs, and locators, see the following table for the PL/I data types that are compatible with the data types in the user-defined function definition.

BIN FIXED(31)

01 var,
    03 var_LENGTH
       BIN FIXED(31),
    03 var_DATA
       CHAR(n);

01 var,
   02 var_LENGTH
      BIN FIXED(31),
   02 var_DATA,
      03 var_DATA1(n)
         CHAR(32767),
      03 var_DATA2
         CHAR(mod(n,32767));

01 var,
    03 var_LENGTH
       BIN FIXED(31),
    03 var_DATA
       CHAR(n);

01 var,
   02 var_LENGTH
      BIN FIXED(31),
   02 var_DATA,
      03 var_DATA1(n)
         CHAR(32767),
      03 var_DATA2
         CHAR(mod(n,32767));

01 var,
   03 var_LENGTH
      BIN FIXED(31),
   03 var_DATA
      GRAPHIC(n);

01 var,
   02 var_LENGTH
      BIN FIXED(31),
   02 var_DATA,
      03 var_DATA1(n)
         GRAPHIC(16383),
      03 var_DATA2
         GRAPHIC(mod(n,16383));

CHAR(40) VAR;

Result parameters: Set these values in your user-defined function before exiting. For a user-defined scalar function, you return one result parameter. For a user-defined table function, you return the same number of parameters as columns in the RETURNS TABLE clause of the CREATE FUNCTION statement. DB2 allocates a buffer for each result parameter value and passes the buffer address to the user-defined function. Your user-defined function places each result parameter value in its buffer. You must ensure that the length of the value you place in each output buffer does not exceed the buffer length. Use the SQL data type and length in the CREATE FUNCTION statement to determine the buffer length.

See Parameters for external user-defined functions to determine the host data type to use for each result parameter value. If the CREATE FUNCTION statement contains a CAST FROM clause, use a data type that corresponds to the SQL data type in the CAST FROM clause. Otherwise, use a data type that corresponds to the SQL data type in the RETURNS or RETURNS TABLE clause.

To improve performance for user-defined table functions that return many columns, you can pass values for a subset of columns to the invoker. For example, a user-defined table function might be defined to return 100 columns, but the invoker needs values for only two columns. Use the DBINFO parameter to indicate to DB2 the columns for which you will return values. Then return values for only those columns. See DBINFO for information about how to indicate the columns of interest.

Code the user-defined function to check all indicators for null values unless the user-defined function is defined with RETURNS NULL ON NULL INPUT. A user-defined function defined with RETURNS NULL ON NULL INPUT executes only if all input parameters are not null.

SQLSTATE value: This CHAR(5) value represents the SQLSTATE that is passed in to the program from the database manager. The initial value is set to ‘00000'. Although the SQLSTATE is usually not set by the program, it can be set as the result SQLSTATE that is used to return an error or a warning. Returned values that start with anything other than ‘00', ‘01', or ‘02' are error conditions.

User-defined function name: DB2 sets this value in the parameter list before the user-defined function executes. This value is VARCHAR(257): 128 bytes for the schema name, 1 byte for a period, and 128 bytes for the user-defined function name. If you use the same code to implement multiple versions of a user-defined function, you can use this parameter to determine which version of the function the invoker wants to execute.

Specific name: DB2 sets this value in the parameter list before the user-defined function executes. This value is VARCHAR(128) and is either the specific name from the CREATE FUNCTION statement or a specific name that DB2 generated. If you use the same code to implement multiple versions of a user-defined function, you can use this parameter to determine which version of the function the invoker wants to execute.

Diagnostic message: Your user-defined function can set this CHAR or VARCHAR value to a character string of up to 1000 bytes before exiting. Use this area to pass descriptive information about an error or warning to the invoker.

DB2 allocates a buffer for this area and passes you the buffer address in the parameter list. At least the first 17 bytes of the value you put in the buffer appear in the SQLERRMC field of the SQLCA that is returned to the invoker. The exact number of bytes depends on the number of other tokens in SQLERRMC. Do not use X'FF' in your diagnostic message. DB2 uses this value to delimit tokens.

Scratchpad: If the definer specified SCRATCHPAD in the CREATE FUNCTION statement, DB2 allocates a buffer for the scratchpad area and passes its address to the user-defined function. Before the user-defined function is invoked for the first time in an SQL statement, DB2 sets the length of the scratchpad in the first 4 bytes of the buffer and then sets the scratchpad area to X'00'. DB2 does not reinitialize the scratchpad between invocations of a correlated subquery.

You must ensure that your user-defined function does not write more bytes to the scratchpad than the scratchpad length.

Call type: For a user-defined scalar function, if the definer specified FINAL CALL in the CREATE FUNCTION statement, DB2 passes this parameter to the user-defined function. For a user-defined table function, DB2 always passes this parameter to the user-defined function.

During the first call, your user-defined scalar function should acquire any system resources it needs. During the final call, the user-defined scalar function should release any resources it acquired during the first call. The user-defined scalar function should return a result value only during normal calls. DB2 ignores any results that are returned during a final call. However, the user-defined scalar function can set the SQLSTATE and diagnostic message area during the final call.

If an invoking SQL statement contains more than one user-defined scalar function, and one of those user-defined functions returns an error SQLSTATE, DB2 invokes all of the user-defined functions for a final call, and the invoking SQL statement receives the SQLSTATE of the first user-defined function with an error.

If a user-defined table function is defined with FINAL CALL, the user-defined function should allocate any resources it needs during the first call and release those resources during the final call that sets a value of 2.

If a user-defined table function is defined with NO FINAL CALL, the user-defined function should allocate any resources it needs during the open call and release those resources during the close call.

During a fetch call, the user-defined table function should return a row. If the user-defined function has no more rows to return, it should set the SQLSTATE to 02000.

During the close call, a user-defined table function can set the SQLSTATE and diagnostic message area.

If a user-defined table function is invoked from a subquery, the user-defined table function receives a CLOSE call for each invocation of the subquery within the higher level query, and a subsequent OPEN call for the next invocation of the subquery within the higher level query.

Examples of receiving parameters in a user-defined function:

The following examples show how a user-defined function that is written in each of the supported host languages receives the parameter list that is passed by DB2.

These examples assume that the user-defined function is defined with the SCRATCHPAD, FINAL CALL, and DBINFO parameters.

Assembler: The follow figure shows the parameter conventions for a user-defined scalar function that is written as a main program that receives two parameters and returns one result. For an assembler language user-defined function that is a subprogram, the conventions are the same. In either case, you must include the CEEENTRY and CEEEXIT macros.

MYMAIN   CEEENTRY AUTO=PROGSIZE,MAIN=YES,PLIST=OS
         USING PROGAREA,R13
 
         L     R7,0(R1)            GET POINTER TO PARM1
         MVC   PARM1(4),0(R7)      MOVE VALUE INTO LOCAL COPY OF PARM1
         L     R7,4(R1)            GET POINTER TO PARM2
         MVC   PARM2(4),0(R7)      MOVE VALUE INTO LOCAL COPY OF PARM2
         L     R7,12(R1)           GET POINTER TO INDICATOR 1
         MVC   F_IND1(2),0(R7)  MOVE PARM1 INDICATOR TO LOCAL STORAGE
         LH    R7,F_IND1        MOVE PARM1 INDICATOR INTO R7
         LTR   R7,R7               CHECK IF IT IS NEGATIVE
         BM    NULLIN              IF SO, PARM1 IS NULL
         L     R7,16(R1)           GET POINTER TO INDICATOR 2
         MVC   F_IND2(2),0(R7)  MOVE PARM2 INDICATOR TO LOCAL STORAGE
         LH    R7,F_IND2        MOVE PARM2 INDICATOR INTO R7
         LTR   R7,R7               CHECK IF IT IS NEGATIVE
         BM    NULLIN              IF SO, PARM2 IS NULL
         ⋮
NULLIN   L     R7,8(R1)            GET ADDRESS OF AREA FOR RESULT
         MVC   0(9,R7),RESULT      MOVE A VALUE INTO RESULT AREA
         L     R7,20(R1)           GET ADDRESS OF AREA FOR RESULT IND
         MVC   0(2,R7),=H'0'       MOVE A VALUE INTO INDICATOR AREA
         ⋮
         CEETERM  RC=0
*******************************************************************
*  VARIABLE DECLARATIONS AND EQUATES                              *
*******************************************************************
R1       EQU   1                   REGISTER 1
R7       EQU   7                   REGISTER 7
PPA      CEEPPA  ,                 CONSTANTS DESCRIBING THE CODE BLOCK
         LTORG ,                   PLACE LITERAL POOL HERE
PROGAREA DSECT
         ORG   *+CEEDSASZ          LEAVE SPACE FOR DSA FIXED PART
PARM1    DS    F                   PARAMETER 1
PARM2    DS    F                   PARAMETER 2
RESULT   DS    CL9                 RESULT
F_IND1   DS    H                   INDICATOR FOR PARAMETER 1
F_IND2   DS    H                   INDICATOR FOR PARAMETER 2
F_INDR   DS    H                   INDICATOR FOR RESULT
 
PROGSIZE EQU   *-PROGAREA
         CEEDSA  ,                 MAPPING OF THE DYNAMIC SAVE AREA
         CEECAA  ,                 MAPPING OF THE COMMON ANCHOR AREA
         END   MYMAIN

C or C++: For C or C++ user-defined functions, the conventions for passing parameters are different for main programs and subprograms.

The following figure shows the parameter conventions for a user-defined scalar function that is written as a main program that receives two parameters and returns one result.

#include <stdlib.h>
#include <stdio.h>
 
main(argc,argv)
  int argc;
  char *argv[];
 {
   /***************************************************/
   /* Assume that the user-defined function invocation*/
   /* included 2 input  parameters in the parameter   */
   /* list.  Also assume that the definition includes */
   /* the SCRATCHPAD, FINAL CALL, and DBINFO options, */
   /* so DB2 passes the scratchpad, calltype, and     */
   /* dbinfo parameters.                              */
   /* The argv vector contains these entries:         */
   /*      argv[0]         1    load module name      */
   /*      argv[1-2]       2    input parms           */
   /*      argv[3]         1    result parm           */
   /*      argv[4-5]       2    null indicators       */
   /*      argv[6]         1    result null indicator */
   /*      argv[7]         1    SQLSTATE variable     */
   /*      argv[8]         1    qualified func name   */
   /*      argv[9]         1    specific func name    */
   /*      argv[10]        1    diagnostic string     */
   /*      argv[11]        1    scratchpad            */
   /*      argv[12]        1    call type             */
   /*      argv[13]      + 1    dbinfo                */
   /*                   ------                        */
   /*                     14    for the argc variable */
   /***************************************************/
   if argc<>14
   {
   ⋮
     /**********************************************************/
     /* This section would contain the code executed if the    */
     /* user-defined function is invoked with the wrong number */
     /* of parameters.                                         */
     /**********************************************************/
   }

   /***************************************************/
   /* Assume the first parameter is an integer.       */
   /* The following code shows how to copy the integer*/
   /* parameter into the application storage.         */
   /***************************************************/
   int parm1;
   parm1 = *(int *) argv[1];
 
   /***************************************************/
   /* Access the null indicator for the first         */
   /* parameter on the invoked user-defined function  */
   /* as follows:                                     */
   /***************************************************/
   short int ind1;
   ind1 = *(short int *) argv[4];
 
   /***************************************************/
   /* Use the following expression to assign          */
   /* 'xxxxx' to the SQLSTATE returned to caller on   */
   /* the SQL statement that contains the invoked     */
   /* user-defined function.                          */
   /***************************************************/
   strcpy(argv[7],"xxxxx");
 
   /***************************************************/
   /* Obtain the value of the qualified function      */
   /* name with this expression.                      */
   /***************************************************/
   char f_func[28];
   strcpy(f_func,argv[8]);
   /***************************************************/
   /* Obtain the value of the specific function       */
   /* name with this expression.                      */
   /***************************************************/
   char f_spec[19];
   strcpy(f_spec,argv[9]);
 
   /***************************************************/
   /* Use the following expression to assign          */
   /* 'yyyyyyyy' to the diagnostic string returned    */
   /* in the SQLCA associated with the invoked        */
   /* user-defined function.                          */
   /***************************************************/
   strcpy(argv[10],"yyyyyyyy");
 
   /***************************************************/
   /* Use the following expression to assign the      */
   /* result of the function.                         */
   /***************************************************/
   char l_result[11];
   strcpy(argv[3],l_result);
 
   ⋮
 }

The following figure shows the parameter conventions for a user-defined scalar function written as a C subprogram that receives two parameters and returns one result.

#pragma runopts(plist(os))
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sqludf.h> 

void myfunc(long *parm1, char parm2[11], char result[11],
            short *f_ind1, short *f_ind2, short *f_indr,
            char udf_sqlstate[6], char udf_fname[138],
            char udf_specname[129], char udf_msgtext[71],
            struct sqludf_scratchpad *udf_scratchpad,
            long *udf_call_type,
            struct sql_dbinfo *udf_dbinfo);  
{
    /***************************************************/
    /* Declare local copies of parameters              */
    /***************************************************/
    int l_p1;
    char l_p2[11];
    short int l_ind1;
    short int l_ind2;
    char ludf_sqlstate[6];     /* SQLSTATE               */
    char ludf_fname[138];       /* function name          */
    char ludf_specname[129];   /* specific function name */
    char ludf_msgtext[71]      /* diagnostic message text*/
    sqludf_scratchpad *ludf_scratchpad; /* scratchpad    */
    long *ludf_call_type;      /* call type              */
    sqludf_dbinfo *ludf_dbinfo /* dbinfo                 */
    /***************************************************/
    /* Copy each of the parameters in the parameter    */
    /* list into a local variable to demonstrate       */
    /* how the parameters can be referenced.           */
    /***************************************************/
 
    l_p1 = *parm1;
    strcpy(l_p2,parm2);
    l_ind1 = *f_ind1;
    l_ind1 = *f_ind2;
    strcpy(ludf_sqlstate,udf_sqlstate);
    strcpy(ludf_fname,udf_fname);
    strcpy(ludf_specname,udf_specname);
    l_udf_call_type = *udf_call_type;
    strcpy(ludf_msgtext,udf_msgtext);
    memcpy(&ludf_scratchpad,udf_scratchpad,sizeof(ludf_scratchpad));
    memcpy(&ludf_dbinfo,udf_dbinfo,sizeof(ludf_dbinfo));
    ⋮
 }

The following figure shows the parameter conventions for a user-defined scalar function that is written as a C++ subprogram that receives two parameters and returns one result. This example demonstrates that you must use an extern "C" modifier to indicate that you want the C++ subprogram to receive parameters according to the C linkage convention. This modifier is necessary because the CEEPIPI CALL_SUB interface, which DB2 uses to call the user-defined function, passes parameters using the C linkage convention.

#pragma runopts(plist(os))
#include <stdlib.h>
#include <stdio.h>
#include <sqludf.h>

extern "C" void myfunc(long *parm1, char parm2[11],
            char result[11], short *f_ind1, short *f_ind2, short *f_indr,
            char udf_sqlstate[6], char udf_fname[138],
            char udf_specname[129], char udf_msgtext[71],
            struct sqludf_scratchpad *udf_scratchpad,
            long *udf_call_type,
            struct sql_dbinfo *udf_dbinfo);
  
{
    /***************************************************/
    /* Define local copies of parameters.              */
    /***************************************************/
    int l_p1;
    char l_p2[11];
    short int l_ind1;
    short int l_ind2;
    char ludf_sqlstate[6];     /* SQLSTATE               */
    char ludf_fname[138];      /* function name          */
    char ludf_specname[129];   /* specific function name */
    char ludf_msgtext[71]      /* diagnostic message text*/
    sqludf_scratchpad *ludf_scratchpad; /* scratchpad          */
    long *ludf_call_type;      /* call type                    */
    sqludf_dbinfo *ludf_dbinfo /* dbinfo                       */
    /***************************************************/
    /* Copy each of the parameters in the parameter    */
    /* list into a local variable to demonstrate       */
    /* how the parameters can be referenced.           */
    /***************************************************/
    l_p1 = *parm1;
    strcpy(l_p2,parm2);
    l_ind1 = *f_ind1;
    l_ind1 = *f_ind2;
    strcpy(ludf_sqlstate,udf_sqlstate);
    strcpy(ludf_fname,udf_fname);
    strcpy(ludf_specname,udf_specname);
    l_udf_call_type = *udf_call_type;
    strcpy(ludf_msgtext,udf_msgtext);
    memcpy(&ludf_scratchpad,udf_scratchpad,sizeof(ludf_scratchpad));
    memcpy(&ludf_dbinfo,udf_dbinfo,sizeof(ludf_dbinfo));
    ⋮
 }

COBOL: The following figure shows the parameter conventions for a user-defined table function that is written as a main program that receives two parameters and returns two results. For a COBOL user-defined function that is a subprogram, the conventions are the same.

 CBL APOST,RES,RENT
       IDENTIFICATION DIVISION.
       ⋮
       DATA DIVISION.
       ⋮
       LINKAGE SECTION.
      *********************************************************
      * Declare each of the parameters                        *
      *********************************************************
       01  UDFPARM1 PIC S9(9) USAGE COMP.
       01  UDFPARM2 PIC X(10).
      ⋮
      *********************************************************
      * Declare these variables for result parameters         *
      *********************************************************
       01  UDFRESULT1 PIC X(10).
       01  UDFRESULT2 PIC X(10).
      ⋮
      *********************************************************
      * Declare a null indicator for each parameter           *
      *********************************************************
       01  UDF-IND1 PIC S9(4) USAGE COMP.
       01  UDF-IND2 PIC S9(4) USAGE COMP.
      ⋮
      *********************************************************
      * Declare a null indicator for result parameter         *
      *********************************************************
       01  UDF-RIND1 PIC S9(4) USAGE COMP.
       01  UDF-RIND2 PIC S9(4) USAGE COMP.
      ⋮
      *********************************************************
      * Declare the SQLSTATE that can be set by the           *
      * user-defined function                                 *
      *********************************************************
       01  UDF-SQLSTATE PIC X(5).
      *********************************************************
      * Declare the qualified function name                   *
      *********************************************************
       01  UDF-FUNC.
           49 UDF-FUNC-LEN PIC 9(4) USAGE BINARY.
           49 UDF-FUNC-TEXT PIC X(137).
      *********************************************************
      * Declare the specific function name                    *
      *********************************************************
       01  UDF-SPEC.
           49 UDF-SPEC-LEN PIC 9(4) USAGE BINARY.
           49 UDF-SPEC-TEXT PIC X(128).
      *********************************************************
      * Declare SQL diagnostic message token                  *
      *********************************************************
       01  UDF-DIAG.
           49 UDF-DIAG-LEN PIC 9(4) USAGE BINARY.
           49 UDF-DIAG-TEXT PIC X(1000).
      

      *********************************************************
      * Declare the scratchpad                                *
      *********************************************************
       01  UDF-SCRATCHPAD.
           49 UDF-SPAD-LEN PIC 9(9) USAGE BINARY.
           49 UDF-SPAD-TEXT PIC X(100).      
      *********************************************************
      * Declare the call type                                 *
      *********************************************************
       01  UDF-CALL-TYPE PIC 9(9) USAGE BINARY.
      *********************************************************
      * CONSTANTS FOR DB2-EBCODING-SCHEME.                    *
      *********************************************************
       77 SQLUDF-ASCII PIC 9(9) VALUE 1.
       77 SQLUDF-EBCDIC PIC 9(9) VALUE 2.
       77 SQLUDF-UNICODE PIC 9(9) VALUE 3.
      *********************************************************
      * Structure used for DBINFO                             *
      *********************************************************
       01  SQLUDF-DBINFO.
      *       location name length
           05 DBNAMELEN PIC 9(4) USAGE BINARY.
      *       location name
           05 DBNAME PIC X(128).
      *       authorization ID length
           05 AUTHIDLEN PIC 9(4) USAGE BINARY.
      *       authorization ID 
           05 AUTHID PIC X(128).
      *       environment CCSID information
           05  CODEPG PIC X(48).
           05 CDPG-DB2 REDEFINES CODEPG.
              10 DB2-CCSIDS OCCURS 3 TIMES.            
                 15 DB2-SBCS   PIC 9(9) USAGE BINARY.
                 15 DB2-DBCS   PIC 9(9) USAGE BINARY.
                 15 DB2-MIXED  PIC 9(9) USAGE BINARY. 
              10 ENCODING-SCHEME   PIC 9(9) USAGE BINARY.
              10 RESERVED    PIC X(8).
      * other platform-specific deprecated CCSID structures not included here 
      *       schema name length
           05 TBSCHEMALEN PIC 9(4) USAGE BINARY.
      *       schema name
           05 TBSCHEMA PIC X(128).
      *       table name length
           05 TBNAMELEN PIC 9(4) USAGE BINARY.
      *       table name
           05 TBNAME PIC X(128).
      *       column name length
           05 COLNAMELEN PIC 9(4) USAGE BINARY.
      *       column name
           05 COLNAME PIC X(128).
      *       product information
           05 VER-REL PIC X(8).
      *       reserved for expansion
           05 RESD0 PIC X(2).
      *       platform type
           05 PLATFORM PIC 9(9) USAGE BINARY.
      *       number of entries in tfcolumn list array (tfcolumn, below)
           05 NUMTFCOL PIC 9(4) USAGE BINARY.
      

      *       reserved for expansion
           05 RESD1 PIC X(26).
      *       tfcolumn will be allocated dynamically if TF is defined
      *       otherwise this will be a null pointer
           05 TFCOLUMN USAGE IS POINTER.
      *       Application identifier
           05 APPL-ID USAGE IS POINTER.
      *       reserved for expansion
           05 RESD2 PIC X(20).      *
       PROCEDURE DIVISION USING UDFPARM1, UDFPARM2, UDFRESULT1,
                              UDFRESULT2, UDF-IND1, UDF-IND2,
                              UDF-RIND1, UDF-RIND2,
                              UDF-SQLSTATE, UDF-FUNC, UDF-SPEC,
                              UDF-DIAG, UDF-SCRATCHPAD,
                              UDF-CALL-TYPE, SQLUDF-DBINFO.

PL/I: The following figure shows the parameter conventions for a user-defined scalar function that is written as a main program that receives two parameters and returns one result. For a PL/I user-defined function that is a subprogram, the conventions are the same.

*PROCESS SYSTEM(MVS);
 MYMAIN: PROC(UDF_PARM1, UDF_PARM2, UDF_RESULT,
              UDF_IND1, UDF_IND2, UDF_INDR,
              UDF_SQLSTATE, UDF_NAME, UDF_SPEC_NAME,
              UDF_DIAG_MSG, UDF_SCRATCHPAD,
              UDF_CALL_TYPE, UDF_DBINFO)
         OPTIONS(MAIN NOEXECOPS REENTRANT);
 
 DCL UDF_PARM1 BIN FIXED(31);    /* first parameter              */
 DCL UDF_PARM2 CHAR(10);         /* second parameter             */
 DCL UDF_RESULT CHAR(10);        /* result parameter             */
 DCL UDF_IND1 BIN FIXED(15);     /* indicator for 1st parm       */
 DCL UDF_IND2 BIN FIXED(15);     /* indicator for 2nd parm       */
 DCL UDF_INDR BIN FIXED(15);     /* indicator for result         */
 DCL UDF_SQLSTATE CHAR(5);       /* SQLSTATE returned to DB2     */
 DCL UDF_NAME CHAR(137) VARYING; /* Qualified function name      */
 DCL UDF_SPEC_NAME CHAR(128) VARYING; /* Specific function name  */
 DCL UDF_DIAG_MSG CHAR(70) VARYING; /* Diagnostic string         */
 DCL 01 UDF_SCRATCHPAD            /* Scratchpad                  */
      03 UDF_SPAD_LEN BIN FIXED(31),
      03 UDF_SPAD_TEXT CHAR(100);
 DCL UDF_CALL_TYPE BIN FIXED(31); /* Call Type                  */
 DCL DBINFO PTR;
    /* CONSTANTS FOR DB2_ENCODING_SCHEME */
 DCL SQLUDF_ASCII BIN FIXED(15) INIT(1);              
 DCL SQLUDF_EBCDIC BIN FIXED(15) INIT(2);             
 DCL SQLUDF_MIXED BIN FIXED(15) INIT(3);              

   DCL 01 UDF_DBINFO BASED(DBINFO),              /* Dbinfo                 */
          03 UDF_DBINFO_LLEN BIN FIXED(15),      /* location length        */
          03 UDF_DBINFO_LOC  CHAR(128),          /* location name          */
          03 UDF_DBINFO_ALEN BIN FIXED(15),      /* auth ID  length        */
          03 UDF_DBINFO_AUTH CHAR(128),          /* authorization ID       */
          03 UDF_DBINFO_CDPG,                    /* environment CCSID info */
            05 DB2_CCSIDS(3),
              07 R1                BIN FIXED(15), /* Reserved              */
              07 DB2_SBCS  BIN FIXED(15),         /* SBCS CCSID            */
              07 R2                BIN FIXED(15), /* Reserved              */
              07 DB2_DBCS  BIN FIXED(15),         /* DBCS CCSID            */
              07 R3                BIN FIXED(15), /* Reserved              */
              07 DB2_MIXED  BIN FIXED(15),        /* MIXED CCSID           */
            05 DB2_ENCODING_SCHEME BIN FIXED(31),
            05 DB2_CCSID_RESERVED CHAR(8),
          03 UDF_DBINFO_SLEN BIN FIXED(15),       /* schema length         */
          03 UDF_DBINFO_SCHEMA  CHAR(128),        /* schema name           */
          03 UDF_DBINFO_TLEN BIN FIXED(15),       /* table length          */
          03 UDF_DBINFO_TABLE   CHAR(128),        /* table name            */
          03 UDF_DBINFO_CLEN BIN FIXED(15),       /* column length         */
          03 UDF_DBINFO_COLUMN  CHAR(128),        /* column name           */
          03 UDF_DBINFO_RELVER  CHAR(8),          /* DB2 release level     */
          03  UDF_DBINFO_RESERV0 CHAR(2),          /* reserved              */
          03 UDF_DBINFO_PLATFORM BIN FIXED(31),   /* database platform     */
          03 UDF_DBINFO_NUMTFCOL BIN FIXED(15),   /* # of TF columns used  */
          03 UDF_DBINFO_RESERV1 CHAR(26),         /* reserved              */
          03 UDF_DBINFO_TFCOLUMN PTR,             /* -> TFcolumn list      */
          03 UDF_DBINFO_APPLID   PTR,             /* -> application id     */
          03 UDF_DBINFO_RESERV2 CHAR(20);         /* reserved              */
 ⋮