Saving information between invocations of a user-defined function by using a scratchpad

You can use a scratchpad to save information between invocations of a user-defined function. To indicate that a scratchpad should be allocated when the user-defined function executes, the function definer specifies the SCRATCHPAD parameter in the CREATE FUNCTION statement.

The scratchpad consists of a 4-byte length field, followed by the scratchpad area. The definer can specify the length of the scratchpad area in the CREATE FUNCTION statement. The specified length does not include the length field. The default size is 100 bytes. DB2 initializes the scratchpad for each function to binary zeros at the beginning of execution for each subquery of an SQL statement and does not examine or change the content thereafter. On each invocation of the user-defined function, DB2 passes the scratchpad to the user-defined function. You can therefore use the scratchpad to preserve information between invocations of a reentrant user-defined function.

The following example demonstrates how to enter information in a scratchpad for a user-defined function defined like this:

CREATE FUNCTION COUNTER()
  RETURNS INT
  SCRATCHPAD
  FENCED
  NOT DETERMINISTIC
  NO SQL
  NO EXTERNAL ACTION
  LANGUAGE C
  PARAMETER STYLE SQL
  EXTERNAL NAME 'UDFCTR';

The scratchpad length is not specified, so the scratchpad has the default length of 100 bytes, plus 4 bytes for the length field. The user-defined function increments an integer value and stores it in the scratchpad on each execution.

#pragma linkage(ctr,fetchable)
#include <stdlib.h>
#include <stdio.h>
/* Structure scr defines the passed scratchpad for function ctr */
  struct scr {
    long len;
    long countr;
    char not_used[96];
  };
/***************************************************************/
/* Function ctr:  Increments a counter and reports the value   */
/*                from the scratchpad.                         */
/*                                                             */
/*     Input:  None                                            */
/*     Output: INTEGER out      the value from the scratchpad  */
/***************************************************************/
void ctr(
  long *out,                       /* Output answer (counter)  */
  short *outnull,                  /* Output null indicator    */
  char *sqlstate,                  /* SQLSTATE                 */
  char *funcname,                  /* Function name            */
  char *specname,                  /* Specific function name   */
  char *mesgtext,                  /* Message text insert      */
  struct scr *scratchptr)          /* Scratchpad               */
{
  *out = ++scratchptr->countr;     /* Increment counter and    */
                                   /* copy to output variable  */
  *outnull = 0;                    /* Set output null indicator*/
   return;
}
/* end of user-defined function ctr */