The factors influencing whether your UDF should use a scratchpad or not are important enough to warrant this special section. Other coding considerations are discussed in Other Coding Considerations.
It is important that you code UDFs to be re-entrant. This is primarily due to the fact that many references to the UDF may use the same copy of the function body. In fact, these many references may even be in different statements or applications. However, note that functions may need or want to save state from one invocation to the next. Two categories of these functions are:
An example of such a function is a simple counter function which returns a '1' the first time it is called, and increments the result by one each successive call. Such a function could be used to number the rows of a SELECT result:
SELECT counter(), a, b+c, ...
FROM tablex
WHERE ...
This type of function is NOT DETERMINISTIC (or VARIANT). Its output does not depend solely on the values of its SQL arguments. This counter function is shown in Example: Counter.
An example of such a function, which may be a part of a document application, is a match function, which returns 'Y' if a given document contains a given string, and 'N' otherwise:
SELECT docid, doctitle, docauthor
FROM docs
WHERE match('myocardial infarction', docid) = 'Y'
This statement returns all the documents containing the particular text string value represented by the first argument. What match would like to do is:
Retrieve a list of all the document IDs which contain the string myocardial infarction from the document application which is maintained outside of DB2. This retrieval is a costly process, so the function would like to do it only one time, and save the list somewhere handy for subsequent calls.
Use the list of document IDs saved during this first call to see if the document ID which is passed as the second argument is contained in the list.
This particular match function is DETERMINISTIC (or NOT VARIANT). Its answer only depends on its input argument values. What is shown here is a function whose performance, not correctness, depends on the ability to save information from one call to the next.
Both of these needs are met by the ability to specify a SCRATCHPAD in the CREATE FUNCTION statement:
CREATE FUNCTION counter()
RETURNS int ... SCRATCHPAD;
CREATE FUNCTION match(varchar(200), char(15))
RETURNS char(1) ... SCRATCHPAD;
This SCRATCHPAD keyword tells DB2 to allocate and maintain a scratchpad for the function. DB2 initializes the scratchpad to binary zeros. If the table function is specified with NO FINAL CALL (the default), DB2 refreshes the scratchpad before each OPEN call. If you specify the table function option FINAL CALL, DB2 does not examine or change the content of the scratchpad thereafter. The scratchpad is passed to the function on each invocation. The function can be re-entrant, and DB2 preserves its state information in the scratchpad.
So for the counter example, the last value returned could be kept in the scratchpad. And the match example could keep the list of documents in the scratchpad if the scratchpad is big enough, or otherwise could allocate memory for the list and keep the address of the acquired memory in the scratchpad.
Because it is recognized that a UDF may want to acquire system resources, the UDF can be defined with the FINAL CALL keyword. This keyword tells DB2 to call the UDF at end-of-statement processing so that the UDF can release its system resources. In particular, since the scratchpad is of fixed size, the UDF may want to allocate memory for itself and thus uses the final call to free the memory. For example the match function above cannot predict how many documents will match the given text string. So a better definition for match is:
CREATE FUNCTION match(varchar(200), char(15))
RETURNS char(1) ... SCRATCHPAD FINAL CALL;
Note that for UDFs that use a scratchpad and are referenced in a subquery, DB2 may decide to make a final call (if the UDF is so specified) and refresh the scratchpad between invocations of the subquery. You can protect yourself against this possibility, if your UDFs are ever used in subqueries, by defining the UDF with FINAL CALL and using the call-type argument, or by always checking for the binary zero condition.
If you do specify FINAL CALL, please note that your UDF receives a call of type FIRST. This could be used to acquire and initialize some persistent resource.