This section discusses some of the details of the interface between DB2 and a UDF, and discusses the sqludf.h include file which makes the interface manageable. This include file only applies to C and C++ UDFs. For information on coding UDFs in Java, see "Coding a Java UDF".
In addition to the SQL arguments and those values passed in the DML reference to the function, DB2 passes additional arguments to the external UDF. For C and C++, all of these arguments are passed in the order shown in Figure 24. Java UDFs take only the SQL-argument and SQL-result arguments, leaving the other information available to extra function calls. Java UDFs have the same restrictions on the resulting SQL-state and diagnostic-message arguments documented below. For information on coding UDFs in Java, see "Coding a Java UDF".
Figure 24. Passing Arguments to a UDF
<------------<
>>--.------------------.----SQL-result----.----------------------.------>
| <--------------< | | <------------------< |
'---SQL-argument---' '---SQL-argument-ind---'
<----------------<
>----SQL-result-ind----SQL-state--function-name--specific-name---------->
>--diagnostic-message--.------------.--.-----------.--.--------.-------><
'-scratchpad-' '-call-type-' '-dbinfo-'
|
| Note: | Each of the above arguments passed to the external function is a pointer to the value, and not the actual value. |
The arguments are described as follows:
For both scalar functions and table functions, DB2 allocates the buffer and passes its address to the UDF. The UDF puts each result value into the buffer. Enough buffer space is allocated by DB2 to contain the value expressed in the data type. For scalar functions, this data type is defined in the CAST FROM clause,if it is present, or in the RETURNS clause, if no CAST FROM clause is present. For table functions, the data types are defined in the RETURNS TABLE(...) clause. For information on how these types map to C language constructs, see "How the SQL Data Types are Passed to a UDF".
Note that for table functions, DB2 defines a performance optimization where every defined column does not have to be returned to DB2. Your UDF returns only the columns required by the statement referencing the table function. For example, consider a CREATE FUNCTION statement for a table function defined with 100 result columns. If a given statement referencing the function is only interested in two of them, this optimization enables the UDF to only return those two columns for each row. See the dbinfo argument below for more information on this optimization.
For each value returned, (that is, a single value for a scalar function, and in general, multiple values for a table function), the UDF code should not change more bytes than is required for the data type and length of the result. DB2 will attempt to determine if the UDF body has written beyond the end of the result buffer by a few bytes, returning SQLCODE -450 (SQLSTATE 39501). However, a major overwrite by the UDF may not be detected by DB2 can cause unpredictable results or an abnormal termination,
If the function is defined with NOT NULL CALL, the UDF body does not need to check for a null value. However, if it is defined with NULL CALL, any argument can be NULL and the UDF should check it.
The indicator takes the form of a SMALLINT value, and this can be defined in your UDF as described in "How the SQL Data Types are Passed to a UDF".
This argument is used by the UDF to signal if the particular result value is null:
Even if the function is defined with NOT NULL CALL, the UDF body must set the indicator of the result.
The indicator takes the form of a SMALLINT value, and this can be defined in your UDF as described in "How the SQL Data Types are Passed to a UDF".
If the optimization using the dbinfo argument column list is being utilized, then only the indicators corresponding to the required columns need be set.
Any other value is treated as an error situation resulting in SQLCODE -463 (SQLSTATE 39001).
The form of the function name that is passed is:
<schema-name>.<function-name>
The parts are separated by a period. Two examples are:
PABLO.BLOOP WILLIE.FINDSTRING
This form enables you to use the same UDF body for multiple external functions, and still differentiate between the functions when it is invoked.
| Note: | Although it is possible to include the period in object names, it is not recommended. For example, if a function, rotate is in a schema, obj.op, the function name that is returned is obj.op.rotate, and it is not obvious if the schema name is obj or obj.op. |
willie_find_feb95 SQL9507281052440430
This first value is provided by the user in his CREATE FUNCTION statement. The second is a value generated by DB2 when the user does not specify a value.
As with the function-name argument, the reason for passing this value is to give the UDF the means of distinguishing exactly which specific function is invoking it.
When the UDF returns either an error or a warning, using the SQL-state argument described above, it can include descriptive information here. DB2 includes this information as a token in its message.
DB2 sets the first character to null before calling the UDF. Upon return, it treats the string as a C null-terminated string. This string will be included in the SQLCA as a token for the error condition. At least the first 17 characters in this string will appear in the SQLCA or DB2 CLP message. However, the actual number of characters which will appear depends on the lengths of the other tokens. Avoid using X'FF' in the text since this character is used to delimit tokens in the SQLCA.
The UDF code should not change more than the VARCHAR(70) buffer which is passed to it. DB2 will attempt to determine if the UDF body has written beyond the end of this buffer by a few characters, SQLCODE -450 (SQLSTATE 39501). However, an overwrite by the UDF can cause unpredictable results or an abend, as it may not be detected by DB2.
DB2 assumes that any message tokens returned from the UDF to DB2 are in the same code page as the database. Your UDF should ensure that If this is the case. If you use the 7-bit invariant ASCII subset, your UDF can return the message tokens in any code page.
For table functions, the scratchpad is initialized as above on each OPEN call to the UDF, and never looked at or modified by DB2 thereafter unless another OPEN call occurs. (This can happen if the UDF is used in a subquery.)
The scratchpad can be mapped in your UDF using the same type as either a CLOB or a BLOB, since the argument passed has the same structure. See "How the SQL Data Types are Passed to a UDF" for more information.
Ensure your UDF code does not make changes outside of the scratchpad buffer. DB2 attempts to determine if the UDF body has written beyond the end of this buffer by a few characters, SQLCODE -450 (SQLSTATE 39501), but a major overwrite by the UDF can cause unpredictable results, or an abend, and may not result in a graceful failure by DB2.
For scalar functions it contains: REFID='wq4704'.
Note that if SCRATCHPAD is specified in the CREATE FUNCTION statement, but FINAL CALL is not, the call-type argument is not passed to the UDF. If it is important for the UDF to identify this first call situation, it must do so by some other means. For example, it could key off the all binary zeros state of the scratchpad.
A UDF is expected to release system resources (for example, memory) acquired during the first call and succeeding normal calls. On a final call, a UDF should not return any answer using the SQL-result or SQL-result-ind arguments. Both of these are ignored by DB2 upon return from a final call to a UDF.
However, on a final call, the UDF may set the SQL-state and diagnostic-message arguments. These arguments are handled the same way as on other calls to the UDF.
If a UDF that is used in a subquery both uses a scratchpad and requires a final call, DB2 may decide to make a final call (and then refresh the scratchpad) between invocations of the subquery.
For table functions it contains:
Remember that the FINAL CALL option is mandatory for table functions, but that SCRATCHPAD is not.
A UDF is expected to release system resources (for example, memory) acquired during the OPEN call and succeeding FETCH calls. On a final call, a table UDF should not return any result row to DB2. In fact, the SQL-result and SQL-result-ind arguments are ignored by DB2 upon return from a CLOSE call to a UDF.
However, on a CLOSE call, the UDF may set the SQL-state and diagnostic-message arguments. These arguments are handled the same way as on other calls to the UDF.
If a table function UDF is used in a subquery, the table function should expect 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.
The length of data base name below. This field is an unsigned short integer.
The name of the currently connected database. This field is a long identifier of 128 characters. The data base name length field described above identifies the actual length of this field. It does not contain a null terminator or any padding.
The length of application authorization ID below. This field is an unsigned short integer.
The application run time authorization ID. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The application authorization ID length field described above identifies the actual length of this field.
This is an union of two 48-byte long structures; one is used by DB2 Universal Database, the other is reserved for future use. The DB2 Universal Database structure contains the following fields:
The length of schema name below. Contains 0 (zero) if a table name is not passed. This field is an unsigned short integer.
Schema for the table name below. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The schema name length field described above identifies the actual length of this field.
The length of the table name below. Contains 0 (zero) if a table name is not passed. This field is an unsigned short integer.
This is the name of the table being updated or inserted. This field is set only if the UDF reference is the right-hand side of a SET clause in an UPDATE statement, or an item in the VALUES list of an INSERT statement. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The table name length field described above, identifies the actual length of this field. The schema name field above, together with this field form the fully qualified table name.
Length of column name below. It contains a 0 (zero) if a column name is not passed. This field is an unsigned short integer.
Under the exact same conditions as for table name, this field contains the name of the column being updated or inserted; otherwise not predictable. This field is a long identifier of 128 characters. It does not contain a null terminator or any padding. The column name length field described above, identifies the actual length of this field.
An 8 character field that identifies the product and its version, release, and modification level with the format pppvvrrm where:
The following identifies the operating platform for the application server:
For additional platforms that are not contained in the above list, see the contents of the sqludf.h file.
The number of non-zero entries in the table function column list specified in the table function column list field below.
This field is for future use. It is defined as 24 characters long.
If a table function is defined, this field is a pointer to an array containing 1 000 short integers that is dynamically allocated by DB2. If a table function is not defined, this pointer is null.
This field is used only for table functions. Only the first n entries, where n is specified in the number of table function column list entries field are of interest. n may be equal to 0, and is less than or equal to the number of result columns defined for the function in the RETURNS TABLE(...) clause of the CREATE FUNCTION statement. The values correspond to the numbers of the columns which this statement needs from the table function. A value of '1' means the first defined result column, '2' means the second defined result column, and so on, and the values may be in any order. Note that n could be equal to zero, that is, the very first array element might be zero, for a statement similar to SELECT COUNT(*) FROM TABLE(TF(...)) AS QQ, where no actual values are needed.
This array represents an opportunity for optimization. The UDF need not return all values for all the result columns of the table function, only those needed in the particular context, and these are the columns identified (by number) in the array. Since this optimization may complicate the UDF logic in order to gain the performance benefit, the UDF can optimally choose to return every defined column.
This field is for future use. It is defined as 24 characters long.
The following is a summary of the arguments described above, and how you use them in the interface between DB2 and an external UDF.
For scalar functions are:
This argument passes the values identified in the function reference from DB2 to the UDF. There is one of these arguments for each SQL argument.
This argument passes the result value generated by the UDF back to DB2 and to the SQL statement where the function reference occurred.
This argument corresponds positionally to SQL-argument, and tells the UDF whether or not a particular argument is null. There is one of these for each SQL-argument.
This argument is used by the UDF to report back to DB2 whether the function result in SQL-result contains nulls.
These arguments are used by the UDF to signal exception information back to DB2.
These arguments are used by DB2 to pass the identity of the referenced function to the UDF.
These arguments are used by DB2 to manage the saving of UDF state between calls. The scratchpad is created and initialized by DB2 and thereafter managed by the UDF. DB2 signals the type of call to the UDF using the call-type argument.
A structure passed by DB2 to the UDF containing additional information.
A table function logically returns a table to the SQL statement that references it, but the physical interface between DB2 and the table function is row by row.
For table functions, the arguments are:
This argument passes the values identified in the function reference from DB2 to the UDF. The argument has the same value for FETCH calls as it did for the OPEN call. There is one of these for each SQL argument.
This argument is used to pass back the individual column values for the row being returned by the UDF. There is one of these arguments for each result column value defined in the RETURNS TABLE (...) clause of the CREATE FUNCTION statement.
This argument corresponds positionally to SQL-argument values, and tells the UDF whether the particular argument is null. There is one of these for each SQL argument.
This argument is used by the UDF to report back to DB2 whether the individual column values returned in the table function output row is null. It corresponds positionally to the SQL-result argument.
These arguments are used by the UDF to signal exception information and the end-of-table condition back to DB2.
These arguments are used by DB2 to pass the identity of the referenced function to the UDF.
These arguments are used by DB2 to manage the saving of UDF state between calls. The scratchpad is created and initialized by DB2 and thereafter managed by the UDF. DB2 signals the type of call to the UDF using the call-type argument. For table functions these call types are OPEN, FETCH, CLOSE.
This is a structure passed by DB2 to the UDF containing additional information.
Observe that the normal outputs of the UDF, SQL-result, SQL-result-ind, and SQL-state, are returned to DB2 using arguments passed from DB2 to the UDF. Indeed, the UDF is written not to return anything in the functional sense (that is, the function's return type is void). See the void definition and the return statement in the following example:
#include ...
void SQL_API_FN divid(
... arguments ... )
{
... UDF body ...
return;
}
In the above example, SQL_API_FN is a macro that specifies the calling convention for a function that may vary across each supported operating platform. This macro is required when you write stored procedures or UDFs.
For programming examples of UDFs, see "Examples of UDF Code".
This section identifies the valid types, for both UDF parameters and result, and specifies for each how the corresponding argument should be defined in your C or C++ language UDF. Note that if you use the sqludf.h include file and the types defined there, you can automatically generate language variables and structures that are correct for the different data types. This include file is discussed in "The UDF Include File: sqludf.h".
It is the data type for each function parameter defined in the CREATE FUNCTION statement that governs the format for argument values. Promotions from the argument data type may be needed to get the value in that format. Such promotions are performed automatically by DB2 on the argument values; argument promotion is discussed in the SQL Reference.
For the function result, it is the data type specified in the CAST FROM clause of the CREATE FUNCTION statement that defines the format. If no CAST FROM clause is present, then the data type specified in the RETURNS clause defines the format.
In the following example, the presence of the CAST FROM clause means that the UDF body returns a SMALLINT and that DB2 casts the value to INTEGER before passing it along to the statement where the function reference occurs:
... RETURNS INTEGER CAST FROM SMALLINT ...
In this case the UDF must be written to generate a SMALLINT, as defined below. Note that the CAST FROM data type must be castable to the RETURNS data type, so one cannot just arbitrarily choose another data type. Casting between data types is discussed in the SQL Reference.
The following is a list of the SQL types and their C language representations. It includes information on whether each type is valid as a parameter or a result. Also included are examples of how the types could appear as an argument definition in your C or C++ language UDF:
Valid. Represent in C as short.
When defining integer UDF parameters, consider using INTEGER rather than SMALLINT as DB2 does not promote SMALLINT arguments to INTEGER. For example, suppose you define a UDF as follows:
CREATE FUNCTION SIMPLE(SMALLINT)...
If you invoke the SIMPLE function using INTEGER data, (... SIMPLE(1)...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function may not perceive the reason for the message. In the above example, 1 is an INTEGER, so you can either cast it to SMALLINT or define the parameter as INTEGER.
Example:
short *arg1; /* example for SMALLINT */
short *arg1_null_ind; /* example for any null indicator */
Valid. Represent in C as long.
Example:
long *arg2; /* example for INTEGER */
Not valid, because there is no C language representation. If you want to pass a decimal value, you must define the parameter to be of a data type castable from DECIMAL (for example CHAR or DOUBLE), and explicitly cast the argument to this type. In the case of DOUBLE, you do not need to explicitly cast a decimal argument to a DOUBLE parameter as DB2 promotes it automatically .
Suppose you have two columns, WAGE as DECIMAL(5,2) and HOURS as DECIMAL(4,1), and you wish to write a UDF to calculate weekly pay based on wage, number of hours worked and some other factors. The UDF could be as follows:
CREATE FUNCTION WEEKLY_PAY (DOUBLE, DOUBLE, ...)
RETURNS DECIMAL(7,2) CAST FROM DOUBLE
...;
For the above UDF, the first two parameters correspond to the wage and number of hours. You invoke the UDF WEEKLY_PAY in your SQL select statement as follows:
SELECT WEEKLY_PAY (WAGE, HOURS, ...) ...;
Note that no explicit casting is required because the DECIMAL arguments are castable to DOUBLE.
Alternatively, you could define WEEKLY_PAY with CHAR arguments as follows:
CREATE FUNCTION WEEKLY_PAY (VARCHAR(6), VARCHAR(5), ...)
RETURNS DECIMAL (7,2) CAST FROM VARCHAR(10)
...;
You would invoke it as follows:
SELECT WEEKLY_PAY (CHAR(WAGE), CHAR(HOURS), ...) ...;
Observe the explicit casting that is required because DECIMAL arguments are not promotable to VARCHAR.
An advantage of using floating point parameters is that it is easier to perform arithmetic on the values in the UDF; an advantage of using character parameters is that it is always possible to exactly represent the decimal value. This is not always possible with floating point.
Valid. Represent in C as float.
Example:
float *result; /* example for REAL */
Valid. Represent in C as double.
Example:
double *result; /* example for DOUBLE */
Valid. Represent in C as char...[n+1] (this is a C null-terminated string, the last character is a null, that is X'00').
For a CHAR(n) parameter, DB2 always moves n bytes of data to the buffer and sets the n+1 byte to null. For a RETURNS CHAR(n) value, DB2 always takes the n bytes and ignores the n+1 byte. For this RETURNS CHAR(n) case, you are warned against the inadvertent inclusion of a null-character in the first n characters. DB2 will not recognize this as anything but a normal part of the data, and it might later on cause seemingly anomalous results if it was not intended.
If FOR BIT DATA is specified, exercise caution about using the normal C string handling functions in the UDF. Many of these functions look for a null to delimit the string, and the null-character (X'00') could be a legitimate character in the middle of the data value.
When defining character UDF parameters, consider using VARCHAR rather than CHAR as DB2 does not promote VARCHAR arguments to CHAR. For example, suppose you define a UDF as follows:
CREATE FUNCTION SIMPLE(INT,CHAR(1))...
If you invoke the SIMPLE function using VARCHAR data, (... SIMPLE(1,'A')...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function may not perceive the reason for the message. In the above example, 'A' is VARCHAR, so you can either cast it to CHAR or define the parameter as VARCHAR.
Example:
char arg1[14]; /* example for CHAR(13) */
char *arg1; /* also perfectly acceptable */
Valid. Represent in C as a structure similar to:
struct sqludf_vc_fbd
{
unsigned short length; /* length of data */
char data[1]; /* first char of data */
};
The [1] is merely to indicate an array to the compiler. It does not mean that only one character is passed; because the address of the structure is passed, and not the actual structure, it just provides a way to use array logic.
These values are not represented as C null-terminated strings because the null-character could legitimately be part of the data value. The length is explicitly passed to the UDF for parameters using the structure variable length. For the RETURNS clause, the length that is passed to the UDF is the length of the buffer. What the UDF body must pass back, using the structure variable length, is the actual length of the data value.
Example:
struct sqludf_vc_fbd *arg1; /* example for VARCHAR(n) FOR BIT DATA */
struct sqludf_vc_fbd *result; /* also for LONG VARCHAR FOR BIT DATA */
Valid. Represent in C as char...[n+1]. (This is a C null-terminated string.)
For a VARCHAR(n) parameter, DB2 will put a null in the (k+1) position, where k is the length of the particular occurrence. The C string-handling functions are thus well suited for manipulation of these values. For a RETURNS VARCHAR(n) value, the UDF body must delimit the actual value with a null, because DB2 will determine the result length from this null character.
Example:
char arg2[51]; /* example for VARCHAR(50) */
char *result; /* also perfectly acceptable */
Valid. Represent in C as sqldbchar[n+1]. (This is a null-terminated graphic string). Note that you can use wchar_t[n+1] on platforms where wchar_t is defined to be 2 bytes in length; however, sqldbchar is recommended. Refer to "Selecting the wchar_t or sqldbchar Data Type" for more information on these two data types.
For a GRAPHIC(n) parameter, DB2 moves n double-byte characters to the buffer and sets the following two bytes to null. Data passed from DB2 to a UDF is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option described in "The WCHARTYPE Precompiler Option". For a RETURNS GRAPHIC(n) value, DB2 always takes the n double-byte characters and ignores the following bytes.
When defining graphic UDF parameters, consider using VARGRAPHIC rather than GRAPHIC as DB2 does not promote VARGRAPHIC arguments to GRAPHIC. For example, suppose you define a UDF as follows:
CREATE FUNCTION SIMPLE(GRAPHIC)...
If you invoke the SIMPLE function using VARGRAPHIC data, (... SIMPLE('graphic_literal')...), you will receive an SQLCODE -440 (SQLSTATE 42884) error indicating that the function was not found, and end-users of this function may not understand the reason for this message. In the above example, graphic_literal is a literal DBCS string that is interpreted as VARGRAPHIC data, so you can either cast it to GRAPHIC or define the parameter as VARGRAPHIC.
Example:
sqldbchar arg1[14]; /* example for GRAPHIC(13) */
sqldbchar *arg1; /* also perfectly acceptable */
Valid. Represent in C as sqldbchar[n+1]. (This is a null-terminated graphic string). Note that you can use wchar_t[n+1] on platforms where wchar_t is defined to be 2 bytes in length; however, sqldbchar is recommended. Refer to "Selecting the wchar_t or sqldbchar Data Type" for more information on these two data types.
For a VARGRAPHIC(n) parameter, DB2 will put a graphic null in the (k+1) position, where k is the length of the particular occurrence. A graphic null refers to the situation where all the bytes of the last character of the graphic string contain binary zeros ('\0's). Data passed from DB2 to a UDF is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option described in "The WCHARTYPE Precompiler Option". For a RETURNS VARGRAPHIC(n) value, the UDF body must delimit the actual value with a graphic null, because DB2 will determine the result length from this graphic null character.
Example:
sqldbchar args[51], /* example for VARGRAPHIC(50) */
sqldbchar *result, /* also perfectly acceptable */
Valid. Represent in C as a structure:
struct sqludf_vg
{
unsigned short length; /* length of data */
sqldbchar data[1]; /* first char of data */
};
Note that in the above structure, you can use wchar_t in place of sqldbchar on platforms where wchar_t is defined to be 2 bytes in length, however, the use of sqldbchar is recommended. Refer to "Selecting the wchar_t or sqldbchar Data Type" for more information on these two data types.
The [1] merely indicates an array to the compiler. It does not mean that only one graphic character is passed. Because the address of the structure is passed, and not the actual structure, it just provides a way to use array logic.
These are not represented as null-terminated graphic strings. The length, in double-byte characters, is explicitly passed to the UDF for parameters using the structure variable length. Data passed from DB2 to a UDF is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option described in "The WCHARTYPE Precompiler Option". For the RETURNS clause, the length that is passed to the UDF is the length of the buffer. What the UDF body must pass back, using the structure variable length, is the actual length of the data value, in double byte characters.
Example:
struct sqludf_vg *arg1; /* example for VARGRAPHIC(n) */
struct sqludf_vg *result; /* also for LONG VARGRAPHIC */
Valid. Represent in C same as CHAR(10), that is as char...[11]. The date value is always passed to the UDF in ISO format: yyyy-mm-dd.
Example:
char arg1[11]; /* example for DATE */
char *result; /* also perfectly acceptable */
Valid. Represent in C same as CHAR(8), that is, as char...[9]. The time value is always passed to the UDF in ISO format: hh.mm.ss.
Example:
char *arg; /* example for DATE */
char result[9]; /* also perfectly acceptable */
Valid. Represent in C same as CHAR(26), that is. as char...[27]. The timestamp value is always passed with format: yyyy-mm-dd-hh.mm.ss.nnnnnn.
Example:
char arg1[27]; /* example for TIMESTAMP */
char *result; /* also perfectly acceptable */
Valid. Represent in C as a structure:
struct sqludf_lob
{
unsigned long length; /* length in bytes */
char data[1]; /* first byte of lob */
};
The [1] merely indicates an array to the compiler. It does not mean that only one character is passed; because the address of the structure is passed, and not the actual structure, it just provides a way to use array logic.
These are not represented as C null-terminated strings. The length is explicitly passed to the UDF for parameters using the structure variable length. For the RETURNS clause, the length that is passed back to the UDF, is the length of the buffer. What the UDF body must pass back, using the structure variable length, is the actual length of the data value.
Example:
struct sqludf_lob *arg1; /* example for BLOB(n), CLOB(n) */
struct sqludf_lob *result;
Valid. Represent in C as a structure:
struct sqludf_lob
{
unsigned long length; /* length in graphic characters */
sqldbchar data[1]; /* first byte of lob */
};
Note that in the above structure, you can use wchar_t in place of sqldbchar on platforms where wchar_t is defined to be 2 bytes in length, however, the use of sqldbchar is recommended. Refer to "Selecting the wchar_t or sqldbchar Data Type" for more information on these two data types.
The [1] merely indicates an array to the compiler. It does not mean that only one graphic character is passed; because the address of the structure is passed, and not the actual structure, it just provides a way to use array logic.
These are not represented as null-terminated graphic strings. The length is explicitly passed to the UDF for parameters using the structure variable length. Data passed from DB2 to a UDF is in DBCS format, and the result passed back is expected to be in DBCS format. This behavior is the same as using the WCHARTYPE NOCONVERT precompiler option described in "The WCHARTYPE Precompiler Option". For the RETURNS clause, the length that is passed to the UDF is the length of the buffer. What the UDF body must pass back, using the structure variable length, is the actual length of the data value, with all of these lengths expressed in double byte characters.
Example:
struct sqludf_lob *arg1; /* example for DBCLOB(n) */
struct sqludf_lob *result;
Valid or invalid depending on the base type. Distinct types will be passed to the UDF in the format of the base type of the UDT, so may be specified if and only if the base type is valid.
Example:
struct sqludf_lob *arg1; /* for distinct type based on BLOB(n) */
double *arg2; /* for distinct type based on DOUBLE */
char res[5]; /* for distinct type based on CHAR(4) */
The AS LOCATOR type modifier is valid only in UDF parameter and result definitions. It may only be used to modify the LOB types or any distinct type that is based on a LOB type. If you specify the type modifier, a four byte locator is passed to the UDF rather the entire LOB value.
Example:
sqludf_locator *arg1; /* locator argument */
sqludf_locator *result; /* locator result */
The type udf_locator is defined in the header file sqludf.h, which is discussed in "The UDF Include File: sqludf.h". The use of these locators is discussed in "Using LOB Locators as UDF Parameters or Results".
This include file contains structures, definitions and values which are useful when writing your UDF. Its use is optional, however, and in the sample UDFs shown in "Examples of UDF Code", some examples use the include file. When compiling your UDF, you need to reference the directory which contains this file. This directory is sqllib/include.
The sqludf.h include file is self-describing. Following is a brief summary of its content:
Also included is a C language type for an argument or result which is defined with the AS LOCATOR appendage.
Some of the UDF examples in the next section illustrate the inclusion and use of sqludf.h.
(5) DB2 treats the function result as null (-2) if the following is true:
This is also true if you define the function with the NOT NULL CALL option.