Application Development Guide

Using LOB Locators as UDF Parameters or Results

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:

• For parameters, DB2 passes a four byte locator instead of the entire LOB value. This locator can be used in a number of ways, involving a set of special APIs (described below), to retrieve and manipulate the actual bytes. The savings are clear for the case where the UDF only needs a few bytes of the value:

  Storage

  Memory for the entire LOB need not be allocated.

  Performance

  Materializing the entire value can take a significant amount of I/O time and byte moving instructions.

• For results, returns a four-byte locator instead of the entire LOB value. Again there can be storage and performance benefits.

Do not modify the locator values as 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.

The APIs which follow are defined using the function prototypes contained in the sqludf.h UDF include file.

 
    extern int sqludf_length( 
        sqludf_locator*     udfloc_p,       /* in:  User-supplied LOB locator value */
        sqlint32*           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 */ 
        sqlint32            start,          /* in:  Substring start value (starts at 1) */ 
        sqlint32            length,         /* in:  Get this many bytes */ 
        unsigned char*      buffer_p,       /* in:  Read into this buffer */ 
        sqlint32*           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 */ 
        sqlint32            length,         /* in:  Length of data to be appended */ 
        sqlint32*           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.

Return codes. Interpret the return code passed back to the UDF by DB2 for each API as follows:

0

Success.

-1

Locator passed to the API was freed by sqludf_free_locator() prior to making the call.

-2

Call was attempted in FENCED mode UDF.

-3

Bad input value was provided to the API. For examples of bad input values specific to each API, see its description below.

other

Invalid locator or other error (for example, memory error). The value that is returned for these cases is the SQLCODE corresponding to the error condition. For example, -423 means invalid locator. Please note that before returning to the UDF with one of these "other" codes, DB2 makes a judgment as to the severity of the error. For severe errors, DB2 remembers that the error occurred, and when the UDF returns to DB2, regardless of whether the UDF returns an error SQLSTATE to DB2, DB2 takes action appropriate for the error condition. For non-severe errors, DB2 forgets that the error has occurred, and leaves it up to the UDF to decide whether it can take corrective action, or return an error SQLSTATE to DB2.

• sqludf_length().

  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.

  A return code of 3 may indicate:

  • udfloc_p (address of locator) is zero
  • return_len_p (address of where to put length) is zero
• sqludf_substr()

  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.

  A return code of 3 may indicate:

  • udfloc_p (address of locator) is zero
  • start is less than 1
  • length is negative
  • buffer_p (buffer address) is zero
  • return_len_p (address of where to put length) is zero
• sqludf_append()

  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 append 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 effect on any LOB values in the SQL statement, or stored in the database.

  This API can be used to build very large LOB values in a piecemeal manner. In cases where a large number of appends is used to build a result, the performance of this task can be improved by:

  • allocating a large application control heap (APP_CTL_HEAP_SZ is the database manager configuration parameter)
  • doing fewer appends of larger buffers; for example, instead of doing 20 appends of 50 bytes each, doing a single 1000 byte append

  SQL applications which build many large LOB values via the sqludf_append() API may encounter errors caused by limitations on the amount of disk space available. The chance of these errors happening can be reduced by:

  • using larger buffers for the individual appends
  • doing frequent COMMITs between statements
  • in cases where each row of a SELECT statement is building a LOB value via this API, using a CURSOR WITH HOLD and doing COMMITs between rows

  A return code of 3 may indicate:

  • udfloc_p (address of locator) is zero
  • length is negative
  • buffer_p (buffer address) is zero
• sqludf_create_locator()

  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.

  A return code of 3 may indicate:

  • udfloc_p (address of locator) is zero
  • loc_type is not one of the three valid values
  • loc_p (address of where to put locator) is zero
• sqludf_free_locator()

  Frees the passed locator.

  Use this API to free any locators that were created with the sqludf_create_locator() API, and which were used only for internal manipulation. It is NOT NECESSARY to free locators passed into the UDF. It is NOT NECESSARY to free any locator created by the UDF via sqludf_create_locator() if that locator is passed out of the UDF as an output.

  A return code of 3 may indicate:

  • udfloc_p (address of locator) is zero

The following notes apply to the use of these APIs:

Notes:

1. A UDF which is defined to return a LOB locator has several possibilities available to it. It can return:
   • an input locator passed to it
   • an input locator passed to it which has been appended to via sqludf_append()
   • a locator created to via sqludf_create_locator(), and appended to via sqludf_append()

2. A table function can be defined as returning one or more LOB locators. Each of them can be any of the possibilities discussed in the preceding item. It is also valid for such a table function to return the same locator as an output for several table function columns.

3. A LOB locator passed to a table function as an input argument remains alive for the entire duration of the row generation process. In fact, the table function can append to a LOB using such a LOB locator while generating one row, and see the appended bytes on a subsequent row.

4. The internal control mechanisms used to represent a LOB which originated in DB2 as a LOB locator output from a UDF (table or scalar function), take 1950 bytes. For this reason, and because there are limitations on the size of a row which is input to a sort, a query which attempts to sort multiple such LOBs which originated as UDF LOB locators will be limited to (at most) two such values per row, depending on the sizes of the other columns involved. The same limitation applies to rows being inserted into a table.

Scenarios for Using LOB Locators

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.

• Varying access to parts of an input LOB.

  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().

• Process most of an input LOB one part at a time.

  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.

• Return one of the two input LOBs

  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.

• Cut and paste an input LOB, and return the result.

  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.