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.

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.

 

Parent topic:

Examples: UDF code