Example: Counter

Suppose that you want to number the rows in a SELECT statement. So you write a user-defined function (UDF) that increments and returns a counter.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

This example uses an external function with DB2® SQL parameter style and a scratchpad.

CREATE FUNCTION COUNTER()
       RETURNS INT
       SCRATCHPAD
       NOT DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE C
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'MYLIB/MATH(ctr)'
       DISALLOW PARALLEL

/* structure scr defines the passed scratchpad for the function "ctr" */
struct scr {
   long len;
   long countr;
   char not_used[92];
};

void ctr (
   long *out,                           /* output answer (counter) */
   short *outnull,                      /* output NULL indicator */
   char *sqlstate,                      /* SQL STATE */
   char *funcname,                      /* function name */
   char *specname,                      /* specific function name */
   char *mesgtext,                      /* message text insert */
   struct scr *scratchptr) {            /* scratch pad */

   *out = ++scratchptr->countr;      /* increment counter & copy out */
   *outnull = 0;
   return;
}
/* end of UDF : ctr */

For this UDF, observe that:

  • It has no input SQL arguments defined, but returns a value.
  • It appends the scratchpad input argument after the four standard trailing arguments, namely SQL-state, function-name, specific-name, and message-text.
  • It includes a structure definition to map the scratchpad which is passed.
  • No input parameters are defined. This agrees with the code.
  • SCRATCHPAD is coded, causing DB2 to allocate, properly initialize and pass the scratchpad argument.
  • You have specified it to be NOT DETERMINISTIC, because it depends on more than the SQL input arguments, (none in this case).
  • You have correctly specified DISALLOW PARALLEL, because correct functioning of the UDF depends on a single scratchpad.