NOTE: RPG Report Free Preview Has Ended
Our
two February issues require no membership, our two March issues
require a free membership, and all subsequent issues of RPG Report
require a Premium (paid) membership. Learn more about our
membership choices here.
Accessing Physical File Members with SQL
Contemporary thinking is that we should start using embedded SQL to
access database files from within RPG IV. Free format RPG IV has a fantastic
embedded SQL syntax, making nearly as easy as RPG native I/O to access database
content. In the Premiere issue of RPG Report, I illustrated one additional
feature of SQL over traditional DDS-based database definitions, the Identity
column. You can read
that article in its entirety here.
Just about every IBM i shop has SQL development tools installed, and all
IBM i shops have the SQL runtime license (the runtime is bundled). Whenever a shop
licenses SQL for the first time, one of the first questions that pops up is:
"How do I access a specific member with SQL?" Short answer, you can't, directly.
You can however use an override or use a little known feature of SQL called
Aliasing to access members.
Database file/member support issues with SQL stem from the support of members
in IBM DB2 UDB for i only. Outside the IBM i world, DB2 UDB does not support file members. Since SQL is the
DML (data manipulation language) for DB2 and must be cross-platform compatible,
generally speaking SQL syntax does not support file members.
Here's a simple query that may be run in Interactive SQL to display the data
in the database file member named REGION6. I'm using a standard member name syntax used in other
areas of the system for this SQL statement:
SELECT * FROM mylib/sales(region6)
WHERE slsamt > 100000
ORDER BY slsamt DESC
Pretty simple except that it does not work. The member name syntax (enclosed in parens in
this example) is not supported by SQL. Instead of a resulting data set, you get the
following message:
"Token ( was not valid."
This statement does not run as is. So you have to use one of two available
work-arounds.
- Override the Database File
- Create an Alias
Read more now,
on midrangeNews.com
Override Database File
One of the most often used techniques to circumvent the lack of member
support in SQL is to
issue an Override with Database File (OVRDBF) command and specify the member (MBR)
parameter. The MBR parameter identifies the file's member that is used
whenever the file is open without a specific member name being specified. This applies to
everything in the job, RPG IV, CL, and yes, even SQL. Here's an example:
==> OVRDBF SALES MBR(REGION6)
==> STRSQL
To access the REGION6 member from Interactive SQL, type the following SQL statement,
then press Enter.
SELECT * FROM sales
WHERE slsamt > 100000
ORDER BY slsamt DESC
Even though no member name is specified on the SQL statement, the SQL
statement runs as expected and the resulting data set from member REGION6 is
displayed.
The problem with this approach is that you have to keep jumping in
and out of Interactive SQL to change members. For end-user applications, you would need to run the
OVRDBF command via the QCMDEXC API or call a CL program and specify
OVRDBF SALES MBR(REGION6) OVRSCOPE(*JOB) or something similar.
If embedded SQL is participating in the application, you'd have to make
sure to run the OVRDBF command before the first EXEC SQL statement that accesses
the file. Overrides are the typical way most shops handle member processing with RPG
IV or SQL today, however, it is not the only way.
SQL ALIAS
SQL supports an ALIAS object that is similar to a Logical File
created with DDS. The ALIAS object points to the original file and may be used in
an SQL statement where a file name (I mean table name) would normally appear. You create the ALIAS
using SQL and the object is built just like logical created with DDS, except there is no DDS.
Here is an example:
CREATE ALIAS BOB CUSTMAST
This creates an ALIAS named BOB for the CUSTMAST file. An ALIAS is created with a *FILE object type and an attribute of DDMF.
Assume the file named CUSTMAST exists in a library named MYDATA; the new
ALIAS named BOB is created in the MYDATA library. To qualify these objects, you
need to specify the library name (called a "COLLECTION" in SQL) using the
*SYS or *SQL naming convention. If *SYS is the naming convention, then the
following statement can be used to build the ALIAS in
the MYDATA library:
CREATE ALIAS MYDATA/BOB MYDATA/CUSTMAST
Where do members come into play? Actually, you can create an ALIAS
that points a file or to a member in that file. Simply add the
member name in parens after the file name, for example:
CREATE ALIAS MYDATA/BOB MYDATA/CUSTMAST (REGION6)
Now wherever you use the BOB alias, you are really using the REGION6 member
of the CUSTMAST file. This could be as a file declared in an RPG File
Specification or on an Embedded SQL statement. Unlike OVRDBF whose influence
ends at invocation or job level, the ALIAS is a permanent object that may be
used by any other job or program.
The following SQL statement, run from within Interactive SQL, produces a
resulting data set from member REGION6 without the need to issue an OVRDBF command.
SELECT * FROM bob
WHERE slsamt > 100000
ORDER BY slsamt DESC
Of course we could have qualified BOB to the MYDATA library in the above
example, but I
prefer to use the library list along with the *SYS naming convention.
Embedded SQL in Free Format RPG IV
Using SQL in free-format RPG IV, as I've mentioned before, is a pleasure,
not to mention easy. To summarize, enter free format and then prefix
the SQL statement with EXEC SQL and terminate the statement with a semicolon,
just like all other RPG IV free-format operations. Here is an example:
....../free
exec SQL CREATE ALIAS QTEMP/BOB mydata/CUSTMAST(region6);
// Use the alias as necessary
if (jobCtrl = 6);
exec SQL SELECT SALESAMT INTO :totSales
FROM QTEMP/BOB
WHERE CUSTNO = :cstnbr
FETCH FIRST ROW ONLY;
... continue...
....../end-free
Deleting an Alias
You can delete the ALIAS when you are finished with it. For example,
Create an ALIAS at the beginning of an RPG IV program. Use the ALIAS in the
program. Then before exiting, delete the ALIAS. To delete SQL objects such
as an ALIAS, the SQL DROP command is used
(do not use "DELETE" as you may have a learning experience). To drop the BOB
ALIAS previously created in QTEMP using Embedded SQL within free format RPG IV,
do the following:
/free
EXEC SQL DROP ALIAS QTEMP/BOB;
/end-free
When RPG IV added the EXTFILE and EXTMBR keywords, the need for using OVRDBF
with native RPG I/O was
substantially reduced and in some cases, eliminated completely. With the
integration of the SQL ALIAS object,
the need to issue the OVRDBF command for embedded SQL member processing has also been eliminated.
Call Me
If you have any questions on any area of RPG IV,
Web development, API, C/C++ or anything else IBM i development related (except subfiles,
data areas and RPGII/III because I don't care about that stuff) write a comment
using the Feedback link on the midrangeNews.com website. I may include a response in an upcoming issue of RPG
Report.
You can subscribe to RPG Report (we call it "follow") by
visiting the RPG Report
page
on midrangeNews.com and then click the FOLLOW link in the table of contents
for that
page. To unsubscribe, simply click that same link. You must be
signed up and signed
in to midrangeNews.com to start following RPG Report.
-Bob Cozzi
Follow Bob on Twitter