Rational Developer for System z
PL/I for Windows, Version 7.6, Programming Guide

User defined functions sample programs

You must install the following items to access the User Defined Function (UDF) sample programs:

Several PL/I programs have been included to show how to code and use UDFs. Here is a short description of how to use them.

The file UDFDLL.PLI contains five sample UDFs. While these are simple in nature, they show basic concepts of UDFs.

MyAdd
Adds two integers and returns the result in a third integer.
MyDiv
Divides two integers and returns the result in a third integer.
MyUpper
Changes all lowercase occurrences of a,e,i,o,u to uppercase.
MyCount
Simple implementation of counter function using a scratchpad.
ClobUpper
Changes all lowercase occurrences of a,e,i,o,u in a CLOB to uppercase then writes them out to a file.

Use the command file bldudfdll to compile and link it into the udfdll library.

After the udfdll library has been compiled and linked, copy it to the user defined function directory for your database instance. If you are using PL/I for AIX, for example, you would copy udfdll to /u/inst1/sqllib/function if that were the user defined function directory on your AIX machine for your database instance.

Before the functions can be used they must be defined to DB2. This is done using the CREATE FUNCTION command. The sample program, addudf.pli, has been provided to perform the CREATE FUNCTION calls for each UDF. CREATE FUNCTION calls would look something like the following:

     CREATE FUNCTION MyAdd ( INT, INT ) RETURNS INT NO SQL
       LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
       STYLE DB2SQL EXTERNAL NAME 'udfdll!MyAdd'

     CREATE FUNCTION MyDiv ( INT, INT ) RETURNS INT NO SQL
       LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
       STYLE DB2SQL EXTERNAL NAME 'udfdll!MyDiv'

     CREATE FUNCTION MyUpper ( VARCHAR(61) ) RETURNS VARCHAR(61) NO SQL
       LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
       STYLE DB2SQL EXTERNAL NAME 'udfdll!MyUpper'

     CREATE FUNCTION MyCount ( ) RETURNS INT NO SQL
       LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
       STYLE DB2SQL EXTERNAL NAME 'udfdll!MyCount'
       SCRATCHPAD

     CREATE FUNCTION ClobUpper ( CLOB(5K) ) RETURNS CLOB(5K) NO SQL
       LANGUAGE C FENCED VARIANT NO EXTERNAL ACTION PARAMETER
       STYLE DB2SQL EXTERNAL NAME 'udfdll!ClobUpper'

These are just sample CREATE FUNCTION commands. Consult your DB2 manuals for more information or refinement.

Use the command file bldaddudf to compile and link the addudf.pli program. After it is compiled and linked, run it to define the user defined functions to your database.

Several sample PL/I programs are provided that call the user defined functions you have just created and added to the database:

UDFMYADD.PLI
Fetches ID and Dept from the STAFF table then adds them together by calling MyAdd UDF. Use the command file bldmyadd to compile and link it.
UDFMYDIV.PLI
Fetches ID and Dept from the STAFF table then divides them by calling MyDiv UDF. Use the command file bldmydiv to compile and link it.
UDFMYUP.PLI
Fetches Name from the STAFF table then calls MyUpper to change the vowels to uppercase. Use the command file bldmyup to compile and link it.
UDFMYCNT.PLI
Fetches ID from the STAFF table, outputs the count of the call, then divides ID by the count. Use the command file bldmycnt to compile and link it.
UDFCLOB.PLI
Fetches the resume for employee '000150' then calls ClobUpper to change the vowels to uppercase. Use the command file bldclobu to compile and link it. After this program is run, look in the file udfclob.txt for the results.

Once these sample programs are compiled, linked, and the UDFs defined to DB2, the PL/I programs can be run from the command line.

These UDFs may also be called from the DB2 Command Line just line any other builtin DB2 function. For further information on how to customize and get the most out of your UDFs, please refer to your DB2 manuals.

Determining compatibility of SQL and PL/I data types

PL/I host variables in SQL statements must be type compatible with the columns which use them:

Using host structures

A PL/I host structure name can be a structure name with members that are not structures or unions. For example:

  dcl 1 A,
        2 B,
          3 C1 char(...),
          3 C2 char(...);

In this example, B is the name of a host structure consisting of the scalars C1 and C2.

Host structures are limited to two levels. A host structure can be thought of as a named collection of host variables.

You must terminate the host structure variable by ending the declaration with a semicolon. For example:

  dcl 1 A,
      2 B char,
      2 (C, D) char;
  dcl (E, F) char;

Host variable attributes can be specified in any order acceptable to PL/I. For example, BIN FIXED(31), BINARY FIXED(31), BIN(31) FIXED, and FIXED BIN(31) are all acceptable.

The following diagram shows the syntax for valid host structures.

Read syntax diagramSkip visual syntax diagram>>-+-DECLARE-+--level--variable-name---------------------------->
   '-DCL-----'
 
>--+----------------------+--,---------------------------------->
   '-Scope and/or storage-'
 
   .-,------------------------------------------.
   V                                            |
>----level--+-var-1-----------+--| Attributes |-+--;------------>
            |    .-,-----.    |
            |    V       |    |
            '-(----var-2-+--)-'
 
>--| Attributes: |---------------------------------------------->
 
>------+-+-BINARY--+--+-FIXED--+-----------------------------+-+-+-----><
       | +-BIN-----+  |        '-(--precision--+--------+--)-' | |
       | +-DECIMAL-+  |                        '-,scale-'      | |
       | '-DEC-----'  '-FLOAT--+-----------------+-------------' |
       |                       '-(--precision--)-'               |
       +-+-CHARACTER-+--+---------------+--+---------+-----------+
       | '-CHAR------'  '-(--integer--)-'  +-VARYING-+           |
       |                                   '-VAR-----'           |
       '-GRAPHIC--+---------------+--+---------+-----------------'
                  '-(--integer--)-'  +-VARYING-+
                                     '-VAR-----'
 

Using indicator variables

An indicator variable is a two-byte integer (BIN FIXED(15)). On retrieval, an indicator variable is used to show whether its associated host variable has been assigned a null value. On assignment to a column, a negative indicator variable is used to indicate that a null value should be assigned.

Indicator variables are declared in the same way as host variables and the declarations of the two can be mixed in any way that seems appropriate to the programmer.

Given the statement:

  exec sql fetch Cls_Cursor into :Cls_Cd,
                                 :Day :Day_Ind,
                                 :Bgn :Bgn_Ind,
                                 :End :End_Ind;

Variables can be declared as follows:

  exec sql begin declare section;
  dcl Cls_Cd    char(7);
  dcl Day       bin fixed(15);
  dcl Bgn       char(8);
  dcl End       char(8);
  dcl (Day_Ind, Bgn_Ind, End_Ind)   bin fixed(15);
  exec sql end declare section;

The following diagram shows the syntax for a valid indicator variable.

Read syntax diagramSkip visual syntax diagram>>-+-DECLARE-+--variable-name--+-BINARY-+--FIXED(15)--;--------><
   '-DCL-----'                 '-BIN----'
 

The following diagram shows the syntax for a valid indicator array.

Read syntax diagramSkip visual syntax diagram>>-+-DECLARE-+--+-variable-name--(--dimension--)-----------+---->
   '-DCL-----'  |    .-,------------------------------.    |
                |    V                                |    |
                '-(----variable-name--(--dimension--)-+--)-'
 
>--+-BINARY-+--FIXED(15)--;------------------------------------><
   '-BIN----'
 

Host structure example

The following example shows the declaration of a host structure and an indicator array followed by two SQL statements that are equivalent, either of which could be used to retrieve the data into the host structure.

  dcl 1 games,
        5 sunday,
          10 opponents char(30),
          10 gtime     char(10),
          10 tv        char(6),
          10 comments  char(120) var;
  dcl indicator(4) fixed bin (15);


  exec sql
       fetch cursor_a
       into :games.sunday.opponents:indicator(1),
            :games.sunday.gtime:indicator(2),
            :games.sunday.tv:indicator(3),
            :games.sunday.comments:indicator(4);


  exec sql
       fetch cursor_a
       into :games.sunday:indicator;

CONNECT TO statement

You can use a host variable to represent the database name you want your application to connect to, for example:

  exec sql connect to :dbase;

If a host variable is specified:

DECLARE TABLE statement

The preprocessor ignores all DECLARE TABLE statements.

DECLARE STATEMENT statement

The preprocessor ignores all DECLARE STATEMENT statements.

Logical NOT sign (¬)

The preprocessor performs the following translations within SQL statements:

Handling SQL error return codes

PL/I provides a sample program DSNTIAR.PLI that you can use to translate an SQLCODE into a multi-line message for display purposes. This PL/I program provides the same function as the DSNTIAR program on mainframe DB2*.

You must compile DSNTIAR with the same DEFAULT and SYSTEM compile-time options that are used to compile the programs that use DSNTIAR.

The caller must declare the entry and conform to the interface as described in the mainframe DB2 publications. For your information, the declaration is of the following form:

  dcl dsntiar entry options(asm inter retcode);

Three arguments are always passed:

arg 1
This input argument must be the SQLCA.
arg 2
This input/output argument is a structure of the form:
  dcl 1 Message,
        2 Buffer_length fixed bin(15) init(n), /* input  */
        2 User_buffer char(n);         /* output */
You must fill in the appropriate value for n.
arg 3
This input argument is a FIXED BIN(31) value that specifies logical record length.

Use of varying strings under DFT(EBCDIC NONNATIVE)

If you specify the compile-time option DFT(EBCDIC NONNATIVE) and you use a varying string host variable as input to the database, you must initialize the host variable or you might get a protection exception during the execution of your program.

If you use an uninitialized varying string on mainframe DB2, your program would be in error and might also get a protection exception.

Using the DEFAULT(EBCDIC) compile-time option

When you use the compile-time option DEFAULT(EBCDIC) with SQL statements that contain input or output character host variables, the SQL preprocessor inserts extra code in the expansion for the SQL statements to convert character data between ASCII and EBCDIC unless the character data has the FOR BIT DATA column attribute.

Avoiding automatic conversion for specific character data

If you do not want data to be converted, you have to give explicit instructions to the preprocessor. For example, if you did not want conversion to occur between a CHARACTER variable and a FOR BIT DATA column, you could include a PL/I comment as shown in the following example:

  dcl SL1 /* %ATTR FOR BIT DATA */ char(9);

The first nonblank character in the comment must be a percent (%) sign followed by the keywords ATTR FOR BIT DATA.

You can put this comment anywhere after the variable name as long as it appears before the end of the declaration for that variable. Neither SL2 nor SL4 are converted in the following example:

  Dcl SL2 /* %ATTR FOR BIT DATA */ char(9),
      SL3 char (20); /* %ATTR FOR BIT DATA */
  Dcl (SL4 /* %ATTR FOR BIT DATA */,
      SL5) char (9);
Avoiding automatic conversion using DCLGEN

Another way to avoid the conversion caused by using DEFAULT(EBCDIC) is to use the DCLGEN utility that is provided with PL/I for Windows to create the declares for database tables.

DCLGEN automatically generates the comment directive required in the output when it recognizes that a column is defined with the FOR BIT DATA attribute.

Using the DEFAULT(NONNATIVE) compile-time option

When you use the compile-time option DEFAULT(NONNATIVE) with an SQLDA that describes a decimal field, you must re-reverse the SQLLEN field after the conversion done by the SQL preprocessor.


Terms of use | Feedback

This information center is powered by Eclipse technology. (http://www.eclipse.org)