This idea for this post comes from a Comment posted on Creating a SQL table on the fly. Anonymous posed the question "Now how do you use that table in your RPG program?".
The post I describe how it is possible to use the SQL CREATE TABLE to build a SQL table extracting data from another file. I have mainly used this in processes that extract data and then send the extracted data to the requester via email. But there is no reason why I cannot read the table created in a RPG program.
The SQL statement I am going to use is very similar to the one in the aforementioned article:
CREATE TABLE QTEMP/WORKFILE AS
(SELECT A.ORDNO,A.ORQTY,A.JOBNO,A.DUEDT
A.ITEM,B.ITDSC,A.UFLG1
FROM ORDHDR A, ITMMST B
WHERE A.ITEM = B.ITEM
AND A.DUEDT > 1150115)
WITH DATA
RCDFMT WORKFILER
|
The SELECT defines the columns (fields) that are in the table, and allow me to only select the records with a due date of greater than January 15, 2015. WITH DATA is needed so that the table is generated containing data. I always use the RCDFMT so that the table will have a record format name just in case I need to use it in a RPG program.
As the table is generated in QTEMP I cannot add a key to the file. I am going to create a unique index with the key I want. I could just as well just create an index, but I want to ensure the keys in this index are unique:
CREATE UNIQUE INDEX QTEMP/WORKFILE1
ON QTEMP/WORKFILE
(DUEDT,ORDNO)
|
In this example I am going to create the table using embedded SQL statements in a RPG program, and then read the table using RPG.
01 ctl-opt alwnull(*inputonly) ;
02 dcl-f WORKFILE1 usage(*input:*output:*update)
keyed
extfile('QTEMP/WORKFILE1')
usropn ;
03 exec sql SET OPTION COMMIT = *NONE ;
04 exec sql DROP TABLE QTEMP/WORKFILE ;
05 exec sql CREATE TABLE QTEMP/WORKFILE AS
(SELECT A.ORDNO,A.ORQTY,A.JOBNO,A.DUEDT
A.ITEM,B.ITDSC,A.UFLG1
FROM ORDHDR A, ITMMST B
WHERE A.ITEM = B.ITEM
AND A.DUEDT > 1150115)
WITH DATA
RCDFMT WORKFILER ;
06 exec sql CREATE UNIQUE INDEX QTEMP/WORKFILE1
ON QTEMP/WORKFILE
(DUEDT,ORDNO) ;
07 open WORKFILE1 ;
08 dow (1 = 1) ;
09 read WORKFILER ;
10 if (%eof) ;
11 leave ;
12 endif ;
13 if (%subst(JOBNO:1:1) = 'A') ;
14 UFLG1 = '1' ;
15 update WORKFILER %fields(UFLG1) ;
16 endif ;
16 enddo ;
18 *inlr = *on ;
19 clear WORKFILER ;
20 ORDNO = '*END' ;
21 write WORKFILER ;
22 close WORKFILE1 ;
|
Those of you who are regular readers of this site know that I code everything using the latest all free RPG. Line 2 is a file definition statement defining the file WORKFILE1 will be used for input, update and output, I am going to read it in key sequence, I am "hard coding" the location of the file using the EXTFILE keyword (see Useful keywords for your F-specs), USROPN means I will open the file using the OPEN and CLOSE operation codes. I want to open the file rather than let the program open it as program initialization as I have to build it first using the SQL CREATE TABLE.
If I want to define the file using the fixed format File specification it would look like:
FWORKFILE1 UF A E K DISK extfile('QTEMP/WORKFILE1')
F usropn
|
I do not want to use commitment control as this is a work file, therefore, I set the SQL option to not use commitment control on line 3.
The DROP TABLE on line 4 is like the DLTF command but better. When I DROP TABLE any indexes that are built over this file are also deleted.
I create the table on line 5, and create the unique index on line 6.
As the index now exists I can open it on line 7. From then on I can treat it like any file. I can read it with a RPG READ operation, line 9, and the %EOF indicator will come on when the end of the index is reached.
I can also update the SQL table using the RPG UPDATE operation code, see line 15.
After all the rows (records) are read and I exit the DO and seton LR, line 18, to flag that the program will end.
To show that I can write to a SQL index using the RPG WRITE operation I write an ending record, line 21.
I always prefer the close the file, line 22, after setting on LR. You can do line 19 - 22 before setting on LR if you prefer.
To be able to compile this program I have to first create the table. I did this by creating another program that just contained the CREATE TABLE and CREATE UNQUEI INDEX. That way I can run it before compiling the RPG program.
If the RCDFMT had been omitted from the CREATE TABLE then the record format name name of the index would be the same as the record format name of the table, which is the name of the table. If I did not want to use WORKFILE as the record format name I could rename it in the file definition using the RENAME keyword:
dcl-f WORKFILE1 usage(*input:*output:*update)
keyed
extfile('QTEMP/WORKFILE1')
rename(WORKFILE:INPUT)
usropn ;
FWORKFILE1 UF A E K DISK extfile('QTEMP/WORKFILE1')
F rename(WORKFILE:INPUT)
F usropn
|
This is a very simple example to show that it is possible to use a SQL table like a file in a RPG program. I am sure you can come up with more complicated scenarios for your own situations.
Also see Reading a SQL table in SQL for how to do the same using SQL.
This article was written for IBM i 7.2, and it should work with earlier releases too.
don't forget HSPEC ALWNULL ...
ReplyDeleteExcellent point. If the JOIN fails then there will be rows with nulls. I have made the change to the code by adding CTL-OPT.
DeleteNice beginner example!
ReplyDelete...but why bother with RPG at all in this case?
Isn't that an implied INNER join? So I don't see the JOIN failing to select rows from both tables. Perhaps the selected fields could be NULL. But I'd code an alwnull anyway for cloneability.
ReplyDeleteChris Ringer
That is a cool technique! :) In lieu of workfiles I often just create a SQL cursor and use SQL fetch to read the data. But when you need to build a temp file populated with data your method is awesome. I will have to use it!!!
ReplyDeleteRookie question: would you please explain why you prefer setting *inLR ON before closing files?.
ReplyDeleteThank You!
As I mention in the body of the post it is just a personal preference, In my opinion when I set on LR it signifies that the end of the program is about to happen. Then I do any end of program processing, including the closing of any files I may have opened.
DeleteYou could equally well close the files, etc, and then set on LR.
In my opinion programming in RPG is like writing an essay There is not just one answer, each programmer will write a program in a slightly differently way. There are many ways to write a good one that will get you the grade of an A. Then again there are also many ways to write a bad one too.
Simon, a couple of points:
ReplyDelete1) Having to use another program to create WORKFILE1 to get the program to compile is bit of a drag.
2) Using SQL instead of RLA to read WORKFILE1 instead avoids the drag of 1).
3) And if you can do 2), then you can avoid creating the file in QTEMP by using a CTE (Common Table Expression, the WITH construct) and an ORDER BY clause.
Maybe you plan to cover these in followup articles. If not, please give it some consideration--SQL and CTEs can improve productivity.
Sam
I think he is just demonstrating that it can be done. I can't tell you how many times I have heard someone say (incorrectly) that SQL Tables cannot be processed using native RPG opcodes.
DeleteNice example. I enjoyed it. Its nice to know the different things you can do. I guess it just boils down to what you said personal preference and style. I am not a big fan of creating the temp file so I can compile the program. It becomes annoying when there are many files involved.
ReplyDeleteSet SQL is great when you need to update a batch of date. Alternatively recursive queries can be used for iterative processing instead of using RLA. But if you need to fill a subfile or execute another process you are stuck with RLA.
ReplyDeleteI use embedded SQL quite a bit but can't seem to get away from RLA as the data is being written to a subfile, being placed in an XML string or placed in a data queue structure. I don't know in these instances that you can get away from RLA. In other languages, it is much easier to get away from RLA but maybe I depend upon it too much in RPG.
ReplyDeleteEven though if it is possible to read SQL tables and indexes with native I/O, I'd not create a temporary table and read it after with native I/O.
ReplyDeleteWhen creating a temporary table, data must be written physically to any space on the disk.
Creating an index over this temporary table will take time, because several table scans on the temporary table must be performed (finding out the distinct key values building the bit maps with the reference to the records/rows ...)
And after you'll start reading the result with native I/O.
IMHO a lot of wasted time.
Creating a permanent SQL View and the right indexes on the based phyiscal file/SQL tables and using embedded SQL for accessing this view is a far better solution.
This view can be accessed in any interface that understands SQL.
Learning SQL and using embedded SQL is absolutely important for RPG programmers.
BTW all new "physical" and "logical" files, respective tables and indexes should be created with SQL, because DDS is "stabilized" there will be no future enhancement. All new development is done in SQL.
Birgitta
It give me a compiler error
ReplyDeleteSQL1001 30 21 External file definition for WORKFILE1 not found. It looks like I have to create duplicate object before compile. Any trick to solve it?