about sql UDF(scratchpad) with RPGLE MODULE -SAMPLE

["Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>]

<snip>
anyone have a sample about how to using sql UDF(scratchpad) with
rpgle module ?
</snip>

Here is example I put together. If you want source code without having
to cut and paste out, send me an e-mail at alan0307d@xxxxxxxxxxx and I
will send it with the copy modules included. 

This program is an RPG ILE version of C example "Counter" in manuals
under keyword "scratchpad".

Create instructions are in the header of each one to be used by my make
tool COMPILE. 

EXUDF     - Top level
EXUDF_PR  - Prototypes.
EXUDF_B   - Bindary.
EXUDF_M01 - RPG/ILE module.

************************************************************************
*************
Result of using function Counter.

SELECT counter(), STID, ITNBR FROM itmrva

COUNTER ( )   Site  Item number       
              ID                      
          1   1     .                 
          2   1     ABSORBANT         
          3   1     ACETONE           
          4   1     ACID 

************************************************************************
***
SQL Statement to create UDF. EXUDF_SQL

CREATE FUNCTION COUNTER()
       RETURNS INT
       SCRATCHPAD
       NOT DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE RPGLE
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'ALANC/EXUDF(UDF_Counter)'
       DISALLOW PARALLEL                                               

************************************************************************
***
Top level EXUDF

      *_> DLTSRVPGM SRVPGM(@5/@4)
      *_> CRTSRVPGM  SRVPGM(@5/@4) +
      *_>   MODULE(EXUDF_M01) +
      *_>   SRCFILE(@2/@1) SRCMBR(EXUDF_B) +
      *_>   TEXT('Example Scratchpad UDF') +
      *_>   ACTGRP(QILE)

      *=============================================================
      * Service Pgm: EXUDF
      * By.........: Alan G. Campin
      * Date.......: 09/19/2006
      * Purpose....: Example SQL UDF using scratchpad. RPG/ILE version
      *                of C example in manual.
      *=============================================================


************************************************************************
*****
Prototype EXUDF_PR

     d UDF_Counter...
     d                 pr                  ExtProc('UDF_Counter')
     d   PR_OutCounter...
     d                                     Like(StdInt)
     d   PR_OutCounterNull...
     d                                     Like(StdIntSml)
     d   PR_OutSQLState...
     d                                5a   Varying
     d   PR_InFunctionName...
     d                              139a   Varying
     d                                     Const
     d   PR_InSpecificName...
     d                              128a   Varying
     d                                     Const
     d   PR_OutMessageText...
     d                               70a   Varying
     d   PR_InOutScratchPad...
     d                                     LikeDs(TD_ScratchPad)

      * Type definition for ScratchPad
     d TD_ScratchPad...
     d                 ds                  Qualified
     d                                     Based(StdNulPtr)
     d   DataStructureLength...
     d                                     Like(StdInt)
     d   Counter...
     d                                     Like(StdInt)
     d   UnUsed...
     d                               96a


************************************************************************
**
Bindary source EXUDF_B

              STRPGMEXP  PGMLVL(*CURRENT)
              EXPORT     SYMBOL('UDF_Counter')
              ENDPGMEXP                                      

************************************************************************
*
RPG/ILE Module. EXUDF_M01

      *_> DLTMOD MODULE(@5/@4)
      *_> CRTRPGMOD MODULE(@5/@4) SRCFILE(@2/@1) SRCMBR(@3) +
      *_>   DBGVIEW(@9) OPTIMIZE(@8) OPTION(*EVENTF)
      *=============================================================
      * Module.....: EXUDF_M01
      * By.........: Alan G. Campin
      * Date.......: 09/19/2006
      * Purpose....: Example SQL UDF using scratchpad. RPG/ILE version
      *                of C example in manual.
      *=============================================================
     h NoMain Option(*Srcstmt:*Nodebugio)

      /copy *libl/qsrcf,cb_std_con

      /copy *libl/qsrcf,cb_stdtype

      /copy *libl/qsrcf,exudf_pr

     p UDF_Counter...
     p                 b                   Export
     d                 pi
     d   OutCounter...
     d                                     Like(StdInt)
     d   OutCounterNull...
     d                                     Like(StdIntSml)
     d   OutSQLState...
     d                                5a   Varying
     d   InFunctionName...
     d                              139a   Varying
     d                                     Const
     d   InSpecificName...
     d                              128a   Varying
     d                                     Const
     d   OutMessageText...
     d                               70a   Varying
     d   InOutScratchPad...
     d                                     LikeDs(TD_ScratchPad)
      /Free

       InOutScratchPad.Counter += 1;
       OutCounter = InOutScratchPad.Counter;
       OutCounterNull = 0;
       Return;

      /End-Free
     p                 e