Re: SQL scratchpad example

Hi Barbara,

Good catch!  I got the code from

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/sqlp/rbafyexamudfcounter.htm

---

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

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

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 */

----

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Barbara Morris wrote:

"Peter Dow (ML)" wrote:

d ScratchPad      ds                  qualified based(ptrScrPad)
d  len                          10i 0
d  countr                       10i 0
d  not_used                     96a

Rather than the not_used subfield, I think it's better explicitly code a
length of 100 on the DS to avoid defining too many/too big subfields,
and overrunning the length of the scratchpad.

 d ScratchPad      ds           100     qualified based(ptrScrPad)
 d  len                          10i 0
 d  countr                       10i 0

Ack, I just noticed that you provided an example of the problem by
coding not_used as 96a instead of 92a.  If you did a CLEAR ScratchPad,
it would overrun the real SCRATCHPAD by 4 bytes.