You can append AS LOCATOR to any of the LOB data types, or any distinct types based on LOB types in a CREATE FUNCTION statement. This applies both to the parameters that are passed and the results that are returned. When this happens, DB2 does the following:
Do not modify the locator values this makes them unusable, and the APIs will return errors.
These special APIs can only be used in UDFs which are defined as NOT FENCED. This implies that these UDFs in test phase should not be used on a production database, because of the possibility that a UDF with bugs could cause the system harm. When operating on a test database, no lasting harm can result from the UDF if it should have bugs. When the UDF is known to be free of errors it can then be applied to the production database, and run NOT FENCED without causing difficulty.
The APIs, which follow, are defined using the function prototypes contained in the sqludf.h UDF include file:
Figure 25. DB2 Lob Locator APIs Defined in sqludf.h
extern int sqludf_length(
sqludf_locator* udfloc_p, /* in: User-supplied LOB locator value */
long* Return_len_p /* out: Return the length of the LOB value */
);
extern int sqludf_substr(
sqludf_locator* udfloc_p, /* in: User-supplied LOB locator value */
long start, /* in: Substring start value (starts at 1) */
long length, /* in: Get this many bytes */
unsigned char* buffer_p, /* in: Read into this buffer */
long* Return_len_p /* out: Return the length of the LOB value */
);
extern int sqludf_append(
sqludf_locator* udfloc_p, /* in: User-supplied LOB locator value */
unsigned char* buffer_p, /* in: User's data buffer */
long length, /* in: Length of data to be appended */
long* Return_len_p /* out: Return the length of the LOB value */
);
extern int sqludf_create_locator(
int loc_type, /* in: BLOB, CLOB or DBCLOB? */
sqludf_locator** Loc_p /* out: Return a ptr to a new locator */
);
extern int sqludf_free_locator(
sqludf_locator* loc_p /* in: User-supplied LOB locator value */
);
|
The following is a discussion of how these APIs operate. Note that all lengths are in bytes, regardless of the data type, and not in single or double-byte characters.
Given a LOB locator, it returns the length of the LOB value represented by the locator. The locator in question is generally a locator passed to the UDF by DB2, but could be a locator representing a result value being built (using sqludf_append()) by the UDF.
Typically, a UDF uses this API when it wants to find out the length of a LOB value when it receives a locator.
The return code passed back to the UDF by DB2 is:
Given a LOB locator, a beginning position within the LOB, a desired length, and a pointer to a buffer, this API places the bytes into the buffer and returns the number of bytes it was able to move. (Obviously the UDF must provide a buffer large enough for the desired length.) The number of bytes moved could be shorter than the desired length, for example if you request 50 bytes beginning at position 101 and the LOB value is only 120 bytes long, the API will move only 20 bytes.
Typically, this is the API that a UDF uses when it wants to see the bytes of the LOB value, when it receives a locator.
The return code passed back to the UDF by DB2 is:
Given a LOB locator, a pointer to a data buffer which has data in it, and a length of data to append, this API appends the data to the end of the LOB value, and returns the length of the bytes appended. (Note that the length appended is always equal to the length given to append. If the entire length cannot be appended, the call to sqludf_append() fails with the return code of other.)
Typically, this is the API that a UDF uses when the result is defined with AS LOCATOR, and the UDF is building the result value one at a time after creating the locator using sqludf_create_locator(). After finishing the build process in this case, the UDF moves the locator to where the result argument points.
Note that you can also append to your input locators using this API, which might be useful from the standpoint of maximum flexibility to manipulate your values within the UDF, but this will not have any affect on any LOB values in the SQL statement, or stored in the database.
The return code passed back to the UDF by DB2 is:
Given a data type, for example SQL_TYP_CLOB, it creates a locator. (The data type values are defined in the external application header file sql.h.)
Typically, a UDF uses this API when the UDF result is defined with AS LOCATOR, and the UDF wants to build the result value using sqludf_append(). Another use is to internally manipulate LOB values.
The return code passed back to the UDF by DB2 is:
Frees the passed locator.
Typically, this is used to free locators passed to the UDF that are used in the internal manipulation of LOB values. A UDF should not need to free a locator that it returns to DB2 as a result as DB2 takes care of freeing it.
This is a brief summary of possible scenarios that show the usefulness of LOB locators. These four scenarios outline the use of locators, and show how you can reduce space requirements and increase efficiency.
A UDF looks at the first part of a LOB value using sqludf_substr(), and based on a size variable it finds there, it may want to read just a few bytes from anywhere in the 100 million byte LOB value, again using sqludf_substr().
This UDF is looking for something in the LOB value. Most often it will find it near the front, but sometimes it may have to scan the entire 100 million byte value. The UDF uses sqludf_length() to find the size of this particular value, and steps through the value 1 000 bytes at a time by placing a call to sqludf_substr() in a loop. It uses a variable as the starting position, increasing the variable by 1 000 each time through the loop. It proceeds in this manner until it finds what it is looking for.
This UDF has two LOB locators as inputs, and returns a LOB locator as an output. It examines and compares the two inputs, reading the bytes received using sqludf_substr() and then determines which of the two to select based on some algorithm. When it determines this, it copies the locator of the selected input to the buffer indicated by the UDF result argument, and exits.
The UDF is passed a LOB value and maybe some other arguments which presumably tell it how to proceed. It creates a locator for its output, and proceeds to build the output value sequentially, taking most of the result value from different parts of the input LOB which it reads using sqludf_substr(), based on the instructions contained in the other input arguments. Finally when it is done it copies the result locator to the buffer to which the UDF result argument points, and then exits.