|
Read a Data Area As a One-Row Table with SQL
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
|
WORKSRIGHT SOFTWARE
