Two functionalities that work perfectly together.

One of my favorite programming techniques is linking together functionality to make even-more-powerful functionality. An example of this is wrapping an SQL user-defined table function (UDTF) around a System i list API to retrieve a given list through an SQL interface. As an example, let's explore a service program that can be used for exactly that purpose.

SQLLSTAPI

The SQLLSTAPI service program contains two subprocedures. The getMessages subprocedure utilizes the retrieve message API (QMHRTVM) to retrieve a list of message file messages. The getSpooledFileList subprocedure retrieves a list of spooled files through the QUSLSPL API. The full source for the code associated with this tip can be found here.

getMessages

This subprocedure retrieves individual messages from the message file identified by the subprocedure's two parameters (inLibrary and inMsgf). Below is the source for this subprocedure.

      //*********************************************************************

      //  Procedure: getMessages

      //  Function:  Retrieve message descriptions for all messages in a

      //             given message file. Data is returned as an SQL table

      //             function.

      //*********************************************************************

     d getMessages     pr

      * Input Parameters

     d   inLibrary                   10a   Const varying

     d   inMsgF                      10a   Const varying

      * Output Parameters

     d   outMsgId                     7a

     d   outMsgSev                    2  0

     d   outMsgDesc                 500a   varying

     d   outMsgHelp                 500a   varying

      * NULL Indicators

     d   niLibr                       5i 0

     d   niMsgf                       5i 0

     d   niMsgId                      5i 0

     d   niMsgSev                     5i 0

     d   niMsgDesc                    5i 0

     d   niMsgHelp                    5i 0

      * SQL Info

     d   SQLState                     5

     d   FunctName                  517a   varying

     d   SpecName                   128a   varying

     d   SQLMsg                      70a   varying

     d   CallType                    10I 0

      //*********************************************************************

      //  Procedure: getSpooledFileList

      //  Function:  Retrieve a list of spooled files based on supplied

      //             criteria. Data is returned as an SQL table function.

      //*********************************************************************

     d getSpooledFileList...

     d                 pr

      * Input Parameters

     D  inpUserId                    10a   varying

     D  inpOutQueue                  20a   varying

     D  inpFormType                  10a   varying

     D  inpUserData                  10a   varying

      * Output Parameters

     d  outSplfNam                   10a

     d  outSplfnum                   10i 0

     d  outSplfSts                   10a

     d  outFrmTyp                    10a

     d  outOutq                      10a

     d  outPages                     10i 0

     d  outCopies                    10i 0

     d  outJobNam                    10a

     d  outUserNam                   10a

     d  outJob#                       6a

      * NULL Indicators

     d   niUserId                     5i 0

     d   niOutQueue                   5i 0

     d   niFormType                   5i 0

     d   niUserData                   5i 0

     d   niSplfNam                    5i 0

     d   niSplfnum                    5i 0

     d   niSplfSts                    5i 0

     d   niFrmTyp                     5i 0

     d   niOutq                       5i 0

     d   niPages                      5i 0

     d   niCopies                     5i 0

     d   niJobNam                     5i 0

     d   niUserNam                    5i 0

     d   niJob#                       5i 0

      * SQL Info

     d   SQLState                     5

     d   FunctName                  517a   varying

     d   SpecName                   128a   varying

     d   SQLMsg                      70a   varying

     d   CallType                    10I 0

      //*********************************************************************

      //  Procedure: getMessages

      //  Function:  Retrieve message descriptions for all messages in a

      //             given message file. Data is returned as an SQL table

      //             function.

      //*********************************************************************

     p getMessages     b                   EXPORT

     d getMessages     pi

      * Input Parameters

     d   inLibrary                   10a   Const varying

     d   inMsgF                      10a   Const varying

      * Output Parameters

     d   outMsgId                     7a

     d   outMsgSev                    2  0

     d   outMsgDesc                 500a   varying

     d   outMsgHelp                 500a   varying

      * NULL Indicators

     d   niLibr                       5i 0

     d   niMsgf                       5i 0

     d   niMsgId                      5i 0

     d   niMsgSev                     5i 0

     d   niMsgDesc                    5i 0

     d   niMsgHelp                    5i 0

      * SQL Info

     d   SQLState                     5

     d   FunctName                  517a   varying

     d   SpecName                   128a   varying

     d   SQLMsg                      70a   varying

     d   CallType                    10I 0

     dRetrieveMsg      pr                  ExtPgm('QMHRTVM')

     d MsgInfo                    32767a   Options(*varsize)

     d RtnLength                     10i 0 Const

     d RtnFormat                      8a   Const

     d MsgID                          7a   Const

     d QualMsgF                      20a   Const

     d RplData                    32767a   Options(*varsize) Const

     d RplDataLen                    10i 0 Const

     d RplSubValue                   10a   Const

     d RtnCtlChar                    10a   Const

     d ErrorCode                    500a

     d RtvOption                     10a   Const

     d CCSIDCnvTo                    10i 0 Const Options(*Omit)

     d CCSIDRplDta                   10i 0 Const Options(*Omit)

     dRTVM0300         ds                  Qualified

     d BytesReturned                 10i 0

     d BytesAvailable                10i 0

     d MsgSev                        10i 0

     d AlertIndex                    10i 0

     d AlertOption                    9a

     d LogPrb                         1a

     d MsgID                          7a

     d                                3a

     d NbrSubValueFormat...

     d                               10i 0

     d CCSIDConvStsText...

     d                               10i 0

     d CCSIDConvRplDta...

     d                               10i 0

     d CCSIDRtnText                  10i 0

     d OffsetDftRpy                  10i 0

     d DftRpyRtnLen                  10i 0

     d DftRpyAvlLen                  10i 0

     d OffsetMsg                     10i 0

     d MsgLenRtn                     10i 0

     d MsgLenAvl                     10i 0

     d OffsetMsgHlp                  10i 0

     d MsgHlpRtnLen                  10i 0

     d MsgHlpAvlLen                  10i 0

     d OffsetSubVar...

     d                               10i 0

     d SubVarRtnLen...

     d                               10i 0

     d SubVarAvlLen...

     d                               10i 0

     d SubVarElement...

     d                               10i 0

     d                              500a   Varying

     d DftRpy                       500a   Varying

     d Msg                          500a   Varying

     d MsgHlp                       500a   Varying

     d SubVar                       500a   Varying

     d SQL_OK          c                   '00000'

     d SQL_EOF         c                   '02000'

     d SQL_FILE_NOT_FOUND...

     d                 c                   '00204'

     d ErrorDS         ds                  Qualified

     d BytesReturned                 10i 0 Inz(%Size(ErrorDS))

     d BytesAvailable                10i 0

     d MsgID                          7a

     d                                1a

     d MsgText                      512a

     d MsgID           s              7a

     d LogPrb          s              4a

     d msgRtv          s             10a   STATIC

     d lastMsg         s              7a   STATIC

      /free

          SQLState = SQL_OK;

          if calltype = -1;

            if niLibr = 1 OR niMsgf = 1;

              SQLState=SQL_FILE_NOT_FOUND;

              msgRtv = '';

              *INLR = *ON;

              Return;

            ENDIF;

            lastMsg = '';

            msgRtv = '*FIRST';

          else;

            msgRtv = '*NEXT';

          ENDIF;

          RetrieveMsg( RTVM0300

                     : %Len(RTVM0300)

                     : 'RTVM0300'

                     : LastMsg

                     : inMsgF  + inLibrary

                     : ' '

                     : 1

                     : '*NO'

                     : '*NO'

                     : ErrorDS

                     : msgRtv

                     : 0

                     : 0 );

          If ErrorDS.BytesAvailable <> 0;

              exsr setEOF;

          Else;

            If RTVM0300.MsgID <> '';

              outMsgId = RTVM0300.MsgId;

              outMsgSev = RTVM0300.MsgSev;

              outMsgDesc = %subst( RTVM0300

                                 : RTVM0300.OffsetMsg+1

                                 : RTVM0300.MsgLenRtn);

              outMsgHelp = %subst( RTVM0300

                                 : RTVM0300.OffsetMsgHlp+1

                                 : RTVM0300.MsgHlpRtnLen);

              lastMsg = outMsgId;

            else;

              exsr setEOF;

            EndIf;

          EndIf;

          Return;

          Begsr setEOF;

            outMsgId = '';

            outMsgSev = 0;

            outMsgDesc = '';

            outMsgHelp = '';

            SQLState = SQL_EOF;

            msgRtv = '';

            *InLR = *On;

            niMsgID = 1;

            niMsgID = 1;

            niMsgID = 1;

            niMsgID = 1;

            lastMsg = '';

          ENDSR;

      /end-free

     p getMessages     e

This subprocedure is designed to return a different "record" each time that the routine is called with different parameters. This is how the SQL table function that will later be associated with the subprocedure is able to retrieve an entire record set from the routine. The output parameters sent back by the subprocedure will include the message ID, the message severity value, the message text description, and the message help text. Just a side note: the value returned into the text description and the help text will include any replacement variables in the original form (&1, &2). For purposes of the SQL function, the subprocedure also includes a set of null indicators—one for each of the previously defined parameters, both input and output. A set of SQL information values is also sent back to the caller to indicate the status of the call along with any message information. Each time this subprocedure is called, it will continue to return records until there is no more data available. At that point, it will return an SQL State value of SQL_EOF, which will indicate an end-of-file condition to the calling SQL interface. Note that when the message file identified on the subprocedure's input parameters cannot be found, the routine will return an SQL_FILE_NOT_FOUND condition as the SQL State value.

getSpooledFileList

This subprocedure retrieves information about spooled files based on a supplied set of four parameters. These include the user ID for the user who generated the spooled file, the output queue that contains the spooled file, the form type of the spooled file and the user data specified on the spooled file. The source for this subprocedure is shown below.

      //*********************************************************************

      //  Procedure: getSpooledFileList

      //  Function:  Retrieve spooled file list entries based on specified

      //             input parameters. Data is returned as an SQL table

      //             function.

      //*********************************************************************

     p getSpooledFileList...

     p                 b                   export

     d getSpooledFileList...

     d                 pi

      * Input Parameters

     D  inpUserId                    10a   varying

     D  inpOutQueue                  20a   varying

     D  inpFormType                  10a   varying

     D  inpUserData                  10a   varying

      * Output Parameters

     d  outSplfNam                   10a

     d  outSplfnum                   10i 0

     d  outSplfSts                   10a

     d  outFrmTyp                    10a

     d  outOutq                      10a

     d  outPages                     10i 0

     d  outCopies                    10i 0

     d  outJobNam                    10a

     d  outUserNam                   10a

     d  outJob#                       6a

      * NULL Indicators

     d   niUserId                     5i 0

     d   niOutQueue                   5i 0

     d   niFormType                   5i 0

     d   niUserData                   5i 0

     d   niSplfNam                    5i 0

     d   niSplfnum                    5i 0

     d   niSplfSts                    5i 0

     d   niFrmTyp                     5i 0

     d   niOutq                       5i 0

     d   niPages                      5i 0

     d   niCopies                     5i 0

     d   niJobNam                     5i 0

     d   niUserNam                    5i 0

     d   niJob#                       5i 0

      * SQL Info

     d   SQLState                     5

     d   FunctName                  517a   varying

     d   SpecName                   128a   varying

     d   SQLMsg                      70a   varying

     d   CallType                    10I 0

      //***************************************************************

      // Miscellaneous Variables

      //***************************************************************

     D RRn1            S              5  0

     D Spl#            S              6a

      //***************************************

      // Input format for API QUSLSPL

      //**************************************

     D outFormat       S              8a

     D usrSpace        S             20a   static

     D listID          S             10i 0 static inz(0)

     D ofStart         S             10i 0  static

     D ofLen           S             10i 0  static

      /FREE

         if calltype = -1;

           *INLR = *OFF;

           usrSpace= 'SPLFLIST  QTEMP     ';

           descTxt = 'SPLF List User Space';

           extAttr = ' ';

           inzSize = 5000;

           intlVal  = ' ';

           pubAuth = '*CHANGE';

           outFormat = 'SPLF0300';

           deleteUserSpace( usrSpace

                          : errorDS);

           createUserSpace( usrSpace

                         : extAttr

                         : inzSize

                         : intlVal

                         : pubAuth

                         : descTxt

                         : rplc

                         : errorDS);

           if ErrorDS.BytesAvailable = 0;

             User = inpUserID;

             OutQ = inpOutQueue;

             FormType = inpFormType;

             UserData = inpUserData;

             retrieveSpooledFileList( usrSpace

                                    : outFormat

                                    : User

                                    : OutQ

                                    : FormType

                                    : UserData

                                    : errorDS

                                     );

             ofStart = 1;

             ofLen = 140;

              retrieveUserSpaceHeader( usrSpace

                                     : ofStart

                                     : ofLen

                                     : headerDS

                                     : errorDS);

             if ErrorDS.BytesAvailable <> 0;

               exsr setEOF;

               return;

             endif;

             ofStart = headerDS.ListOffset + 1;

           else;

              exsr setEOF;

              return;

           endif;

         endif;

        if niUserId   = 1 OR

           niOutQueue = 1 OR

           niFormType = 1 OR

           niUserData = 1;

           SQLState=SQL_FILE_NOT_FOUND;

           usrSpace = '';

           *INLR = *ON;

           Return;

         ENDIF;

         if calltype <= 0;

                if listId < headerDS.ListNumber;

                  listid += 1;

                  clear Splf0300;

                  retrieveUserSpaceSpl0300( usrSpace

                                         : ofStart

                                         : ofLen

                                         : Splf0300

                                         : errorDS);

                 if ErrorDS.BytesAvailable = 0 and SSplfName <> '';

                    Select;

                    When SSplfStat = 1;

                      outSplfsts = 'READY';

                    When SSplfStat = 2;

                      outSplfsts = 'OPENED';

                    When SSplfStat = 3;

                      outSplfsts = 'CLOSED';

                    When SSplfStat = 4;

                      outSplfsts = 'SAVED';

                    When SSplfStat = 5;

                      outSplfsts = 'WRITING';

                    When SSplfStat = 6;

                      outSplfsts = 'ON HOLD';

                    When SSplfStat = 7 or SSplfStat = 8;

                      outSplfsts = 'PENDING';

                    When SSplfStat = 10;

                      outSplfsts = 'FINISHED';

                    When SSplfStat = 11;

                      outSplfsts = 'SENDING';

                    When SSplfStat = 12;

                      outSplfsts = 'DEFERRED';

                    Endsl;

                    outSplfNam = SSplfName;

                    outSplfnum = SSplfNum;

                    outFrmTyp = SFormType;

                    outOutq = Soutq;

                    outPages = SPages;

                    outCopies = SCopies;

                    outJobNam =  SJObName;

                    outUserNam = SUserName;

                    outJob#    = SJob#;

                    SQLState = SQL_OK;

                  else;

                    exsr setEOF;

                    Return;

                  endif;

                  ofStart = ofStart + headerDS.EntrySize;

             else;

               exsr setEOF;

               Return;

             Endif;

           Endif;

           Return;

           Begsr setEOF;

             outSplfNam = '';

             outSplfnum = 0;

             outFrmTyp  = '';

             outOutq =  '';

             outPages = 0;

             outCopies  = 0;

             outJobNam  = '';

             outUserNam = '';

             outJob#    = '';

             SQLMsg  = '';

             SQLState = SQL_EOF;

             listId = 0;

             *InLR = *On;

             niSplfNam = 1;

             niSplfnum = 1;

             niFrmTyp = 1;

             niOutq = 1;

             niPages = 1;

             niCopies = 1;

             niJobNam = 1;

             niUserNam = 1;

             niJob# = 1;

          ENDSR;

      /END-FREE

     p getSpooledFileList...

     p                 e

Note that all of the special values supported by the WRKSPLF command for these parameters are also supported here. The output parameters from this subprocedure include the spooled file name, the spooled file number, the status of the spooled file at the time the subprocedure is called, the form type, the output queue, the number of pages in the spooled file, the number of copies specified on the spooled file, along with the job name, user name, and job number that created the file. As with the getMessages subprocedure, this subprocedure also returns a set of null indicators and SQL status information to be passed back to the caller.

Creating the Functions

Now that we have examined the subprocedures that will act as our SQL functions, we need to execute the commands to create those functions. First, we need to compile to service program that contains these two subprocedures. To do this, execute the two commands below to create the module and service program;

CRTRPGMOD MODULE(mylib/SQLLSTAPI) SRCFILE(mylib/QRPGLESRC) 

    SRCMBR(SQLLSTAPI) REPLACE(*NO)               

CRTSRVPGM SRVPGM(mylib/SQLLSTAPI) MODULE(mylib/SQLLSTAPI)  

          EXPORT(*ALL)             

Now that we have our compiled service program, we need to associate that service program—or more appropriately, the subprocedures contained in that service program—with an SQL user-defined table function (UDTF). To do this, we need to execute an SQL statement. The easiest way to do this is to enter the statements into a source file member for each UDTF that we're creating. Below is the text of the statements to create the getMessages UDTF.

CREATE FUNCTION mylib/GETMESSAGES(Library VARCHAR (10),           

                                  MessageFile VARCHAR (10))       

RETURNS TABLE ( MsgID CHAR (7 ),                                  

                MsgSev DECIMAL (2 , 0),                            

                MsgDesc VARCHAR (500 ),                           

                MsgHelp VARCHAR (500 ))                           

LANGUAGE RPGLE                                                    

EXTERNAL NAME 'mylib/SQLLSTAPI(GETMESSAGES)'                      

PARAMETER STYLE DB2SQL                                            

DISALLOW PARALLEL                                                 

NO SQL                                                            

NO FINAL CALL                                                      

Note that the function accepts the same two input parameters identified in our getMessages subprocedure and returns the four values that we identified as output parameters from that subprocedure. The external name value is specified in the following format:

library/service program(sub-procedure)

The value "PARAMETER STYLE DB2SQSL" identifies that the program will return and expect the null indicator and SQL info variables. Once you have the source for each of the subprocedures saved into a source member, execute the following commands to run the statements to create the UDTF.

RUNSQLSTM SRCFILE(mylib/QSQLSRC) SRCMBR(GETMSGF)      

RUNSQLSTM SRCFILE(mylib/QSQLSRC) SRCMBR(GETSPLF)          

Note that these commands assume that you have each of the subprocedures saved into a source file QSQLSRC in the library mylib. The advantage to saving these statements in a source member versus simply executing them from an interactive SQL prompt or from the SQL utility within Operations Navigator is that it makes it easier to recreate the function in the future or on multiple systems. Once you've completed both of these commands, the UDTFs are available for use.

Calling the UDTFs

Now that we've created our UDTFs, we can execute them from anywhere that an SQL command is accepted. This includes the interactive SQL tool (STRSQL) as well as embedded SQL (SQLRPGLE type) programs. This also means that remote applications that access the System i through OLE DB or ODBC connections can call these functions. Below is a sample of calling the getMessages UDTF from interactive SQL.

SELECT * FROM TABLE(mylib/getMessages('QSYS      ', 'QCEEMSG   ')) Msgs

Note that the Msgs value at the end of the line is an alias that is required when calling a UDTF. The "TABLE" prefix is used to identify that we are calling a user-defined table function. Figure 1 shows a sample of the output generated by this call.

Figure 1: These are the results generated by the getMessages UDTF.

Similarly the getSpooledFileList function can be called as shown below;

SELECT * FROM

 TABLE(mylib/getSpooledFileList('*CURRENT  ', '*ALL ','*ALL ', '*ALL ')) Splf

Note that in this example the parameters are all passed as special values. In each of the examples given, we may choose to filter results using a WHERE clause. Figure 2 shows an example of the output from this statement.

Figure 2: These are the results from the getSpooledFileList UDTF.

What's Your Function?

Now that we've explored combining functionality like user-defined table functions and system APIs, I hope you'll think of other ways to make use of this concept in your shop.

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1