Read a Data Area As a One-Row Table with SQL

May 19, 2004 Hey, Ted

Is it possible to treat a data area as if it were a one-record physical file in an SQL query? We currently copy the data area to a one-record file, which we use in our queries. However, occasionally the data area gets updated but the refresh lags behind.

–W.G.

I’ve never thought about it, but that’s an interesting idea. In my S/36 days, I frequently wished it were possible to treat the local data area as a one-record disk file in Query/36.

To answer your question: yes, it’s possible if you’re at V5R2. You will need to create a table function, which is a program that returns data through parameters, one virtual row at a time.

I cannibalized Mike Sansoterra’s program from the March 24 issue of Four Hundred Guru to throw together an RPG IV program to read a 24-character data area I call PLANT. The first four bytes of PLANT are a plant number. The next 20 bytes are a plant location.

Here’s the RPG code.

      // GetPlantInfo -- Return one record from data area PLANT
      //
      // To create:
      //
      // 1) CRTRPGMOD MODULE(xxx/GETPLANT)
      //              SRCFILE(xxx/QRPGLESRC)
      //              SRCMBR(GETPLANT)
      // 2) CRTSRVPGM SRVPGM(xxxxx/GETPLANT)
      //              EXPORT(*ALL)
      //
      //  To create an SQL function:
      //
      //  create function xxx/GetPlantInfo ()
      //  returns table(Number   Char(4),
      //                Location Char(20))
      //  External Name 'THOLTS/GETPLANT(GETPLANTINFO)'
      //  Language RPGLE
      //  Disallow Parallel
      //  No SQL
      //  Parameter Style DB2SQL
      //  Deterministic
      //
      //  To use the function in SQL:
      //
      //  select *
      //     from table(GetPlantInfo()) as plant

     H NoMain

     DGetPlantInfo     PR
      // Table Function Input Parameters
      // Table Function Output Parameters (columns)
     D pPlantNbr                      4A
     D pPlantLoc                     20A
      // Null Indicator Input Parameters
      // Null Indicator Output Parameters
     D pPlantNbr_NI                   5I 0
     D pPlantLoc_NI                   5I 0
      // DB2SQL Style Parameters
     D pSQLState                      5
     D pFunctionName                517
     D pSpecificName                128
     D pSQLMsgText                   70    Varying
      // UDTF Call Type
     D pTFCallType                   10I 0

      //  UDTF call parameter constants

     D UDTF_FirstCall  S             10I 0 Inz(-2)
     D UDTF_Open       S             10I 0 Inz(-1)
     D UDTF_Fetch      S             10I 0 Inz(0)
     D UDTF_Close      S             10I 0 Inz(1)
     D UDTF_LastCall   S             10I 0 Inz(2)

      //  SQL States

     D SQLSTATEOK      C                   '00000'
     D ENDOFTABLE      C                   '02000'
     D UDTF_ERROR      C                   'US001'

      //  NULL Constants

     D ISNULL          C                   -1
     D NOTNULL         C                    0

     D Plant           ds                  DtaAra(Plant)
     D  PlantNbr                      4A
     D  PlantLoc                     20a

     D FirstFetch      s               n
     D NullData        s               n

     PGetPlantInfo     B                   Export
     DGetPlantInfo     PI
      // Table Function Input Parameters
      // Table Function Output Parameters (columns)
     D pPlantNbr                      4A
     D pPlantLoc                     20A
      // Null Indicator Input Parameters
      // Null Indicator Output Parameters
     D pPlantNbr_NI                   5I 0
     D pPlantLoc_NI                   5I 0
      // DB2SQL Style Parameters
     D pSQLState                      5
     D pFunctionName                517
     D pSpecificName                128
     D pSQLMsgText                   70    Varying
      // UDTF Call Type
     D pTFCallType                   10I 0

     C/Free
         pSQLState=SQLStateOK;

         Monitor;
             Select;
             // Function Open
             When pTFCallType=UDTF_Open;
                 In(e) Plant;
                 NullData = %Error;
                 FirstFetch = *on;

             // Function Fetch -> Return data
             When pTFCallType=UDTF_Fetch;
                 if FirstFetch;
                     FirstFetch = *off;
                     if not NullData;
                         pPlantNbr=PlantNbr;
                         pPlantLoc=PlantLoc;
                         pPlantNbr_NI=NOTNULL;
                         pPlantLoc_NI=NOTNULL;
                     Else;
                         pPlantNbr_NI=ISNULL;
                         pPlantLoc_NI=ISNULL;
                     endif;
                 else;
                     pSQLState=ENDOFTABLE;
                 endif;

             // Function Close -> Cleanup Work
             When pTFCallType=UDTF_Close;
                 *InLR=*On;
             EndSl;
         On-Error;
                *InLR=*On;
                pSQLState=UDTF_ERROR;
         Endmon;

         Return;
      /End-Free

     PGetPlantInfo     E

The instructions to create the function are in the comments, but let me briefly explain. The first step is to compile the RPG source member into a module. Next, create a service program from the module. Last, create a function using your SQL interface of choice.

Here’s how the program works. When you run an SQL command that references the table function, the GetPlantInfo subprocedure will be called four times. The first time, parameter pTFCALLTYPE will have a value of -1. This is a cue to do initial processing. The subprocedure reads the data area.

The second time, pTFCALLTYPE has a value of zero, which means that it wants a row of data. GetPlantInfo returns the plant number and location, if it has them, or nulls if it doesn’t.

The third time, pTFCALLTYPE again has a value of zero. The subprocedure returns a SQL status of 02000, which indicates the end of a data set.

Because the program returned status 02000, pTFCALLTYPE has a value of 1 on the fourth call. This is a signal to shut down.

Here are some SQL commands I was able to run successfully. The first one displays the plant number and location as a table. The second two use a transaction file that has a plant number in it. Both queries use the data from the data area to select records from the transaction file.

select * from table(GetPlantInfo()) as plant

select pxact.PlantID, pxact.name, plant.location 
  from pxact                                   
  join table(GeTPlantInfo()) as plant          
    on plant.number = pxact.PlantID

select * from pxact where PlantID in
  (select plant.number
     from table(GeTPlantInfo()) as plant)

For more information about table functions, see Michael Sansoterra’s article and the IBM iSeries Information Center.

–Ted