SQL and Member Names: Forcing the Relationship

Today we have several choices for database access that includes native I/O support in RPG IV, IBM's Query/400, ProData's pervasive DBU product (does anyone not have this installed in their shop?), several third-party report and/or query tools such as mrc.com's mPower, NGS Query, and now IBM's OEM'd Web Query. On top of all of this is the wildcard SQL.

Many shops now have IBM SQL installed, but some don't. Whenever a shop licenses SQL for the first time, one of the first questions that pops up is: "How do I access a different member in SQL?" Short answer, you can't; you have to use an override. Long answer, read on.

The problem stems from the fact that effectively DB2/400 (or whatever the database on the System i is called nowadays) is largely disregarded outside of the System i world. Consequently, even though IBM created DB2 and DB2/400 as well as invented SQL, it somehow doesn't want to use the most powerful and sophisticated database in the world (native System i database) as the baseline database. Instead, IBM uses another database as the standard. So since no one else has members, SQL doesn't directly support member names in its syntax.

Try it. Try doing a simply query with SQL to display the REGION6 member in the SALES database file.

   SELECT * FROM  mylib/sales(region6)
      WHERE slsamt > 100000 
      ORDER BY slsamt DESC

Pretty simple query, except it won't work. The member name (enclosed in parens in our example) is not supported in general SQL statements. So what happens?

"Token ( was not valid."

Your statement does not run. Is there a work-around? Of course!

Override Database

One of the most often used techniques to circumvent this shortcoming is to use an Override Database File (OVRDBF) command and specify the member (MBR) parameter. The MBR parameter identifies the file's member that will be used in the SQL statement. For example:

==>  OVRDBF SALES  MBR(REGION6)
==>  STRSQL

Type SQL statement, press Enter.
==> SELECT * FROM  mylib/sales 
         WHERE slsamt > 100000 
         ORDER BY slsamt DESC

Now, even though no member name is specified on the SQL statement, the SQL statement works and is directed to the REGION6 member in the SALES file. This is what we wanted all along.

The problem with this approach, however, is that you have to keep jumping in and out of Interactive SQL to change overrides, and you can't really use it dynamically with embedded SQL, because you'd have to issue OVRDBF commands via QCMDEXC in your RPG IV program or issue the OVRDBF command before calling the RPG IV.

This is the normal way most people access members in SQL today. But is it the only way? Of course not.

ALIAS

The TV show "Alias" was not only a cool show, but it also provided an alternative to using overrides with SQL. That is the ALIAS.

SQL supports an ALIAS object that is basically an alternative name for an existing database file. The ALIAS points to the original file but can be used in SQL statements wherever a physical or logical file name (I mean table or view) can be specified.

You create the ALIAS just as you'd create a logical view, and then use it in the SQL statements. For example:

    CREATE ALIAS  BOB FOR CUSTMAST

This creates an ALIAS named BOB for the CUSTMAST file. Whatever library CUSTMAST is located in is also the library in which BOB is created.

If CUSTMAST exists in MYDATA, then BOB is created in MYDATA, and the following two statements produce identical results:

    CREATE ALIAS  BOB  FOR MYDATA/CUSTMAST

    CREATE ALIAS  MYDATA/BOB FOR  MYDATA/CUSTMAST

Of course, you can create BOB in any library you want.

ALIASES are created with a *FILE object type and an object attribute of DDMF.

Okay, so where do members come into play? Actually, you can create an ALIAS that points not only to a file but also to a member in that file. Simply add the member name in parens following the file name. For example:

     CREATE ALIAS  BOB  FOR MYDATA/CUSTMAST (REGION6)

Now, wherever you use BOB, you're really using the CUSTMAST file's REGION6 member. So the following produces the results we got after using the OVRDBF command in our first example:

    SELECT * FROM  bob
      WHERE slsamt > 100000 
      ORDER BY slsamt DESC

Of course, if necessary we can qualify BOB to its library, but I prefer to use the library list.

The cool thing is, you can easily delete the ALIAS by simply "Dropping it" (in SQL terms) as follows:

     DROP ALIAS bob

It is deleted. You can create any number of ALIASES and use them to your heart's content.

When RPG IV added the EXTFILE keyword, the necessity for using OVRDBF was substantially reduced. With the advent of the SQL ALIAS object, it's just one more nail in the OVRDBF coffin.