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.

Sponsored by: BCD

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.

1. Override the Database File
2. 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