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.
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:
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.
PL/I host variables in SQL statements must be type compatible with the columns which use them:
Graphic data types are compatible with each other. A GRAPHIC or VARGRAPHIC column is compatible with a fixed-length or varying-length PL/I graphic character host variable.
When necessary, the Database Manager automatically converts a fixed-length character string to a varying-length string or a varying-length string to a fixed-length character string.
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.
>>-+-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-----'
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.
>>-+-DECLARE-+--variable-name--+-BINARY-+--FIXED(15)--;-------->< '-DCL-----' '-BIN----'
The following diagram shows the syntax for a valid indicator array.
>>-+-DECLARE-+--+-variable-name--(--dimension--)-----------+----> '-DCL-----' | .-,------------------------------. | | V | | '-(----variable-name--(--dimension--)-+--)-' >--+-BINARY-+--FIXED(15)--;------------------------------------>< '-BIN----'
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;
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:
dcl dbase char (10); dbase = 'SAMPLE'; /* blanks are padded automatically */ exec sql connect to :dbase;
IBM1214I W xxx.x A dummy argument is created for argument
number 6 in entry reference SQLESTRD_APIThe preprocessor ignores all DECLARE TABLE statements.
The preprocessor ignores all DECLARE STATEMENT statements.
The preprocessor performs the following translations within SQL statements:
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:
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.
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.
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.
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);
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.
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.