Relaxed Qualified Names in SQL

For decades we SQL users have had to decide between using the *SQL naming convention of LIBRARY.FILE or the *SYS (system) naming convention of LIBRARY/FILE when using SQL.

As programmers we really didn't care which syntax was required, but the advantage of *SYS over *SQL in the context of IBM i systems, was obvious to most programmers. I mean, really? Who on IBM i uses the User Profile as the library name for end-users? There are some, but in a production environment it is rare and not the defacto standard that you see on other non-IBM platforms.

The disadvantage of *SYS is that when IBM implemented the new 3-tier naming we *SYS users were out of luck. xxx.yyy.zzz was valid, but xxx/yyy/zzz is not. So we ended up having to pick between the usefuly *SYS naming and the important remote access that *SQL provided with 3-tier naming.

In a recent TRx refresh on IBM i v7.1 and v7.2 IBM relaxed the *SQL and *SYS syntax for qualified names. This means that when using the *SYS naming convention, you can use *SQL syntax or *SYS syntax in your SQL statements--both work; and both work like *SYS regardless of syntax used.

This means that MYLIB.FILE is the same as MYLIB/FILE when NAMING(*SYS) is being used. When NAMING(*SQL) is used, the normal *SQL syntax rules apply and the slash is prohibited.

Why is this beneficial?

The advantage of *SYS naming rules means that if you use NAMING(*SYS) when an unqualified name is specified, the *LIBL is searched for the file. This is a huge advantage over NAMING(*SQL) which looks for unqualified files in a library of the same name as the USRPRF running the SQL command--which, as I mentioned, is almost never the case on IBM i.

Okay, so period vs slash; what's the big deal?

The real advantage is that you can now use IBM's 3-level names; formally named "3-Tier qualified names". Until now, to use 3-level names, you had to use *SQL syntax, which meant that everything worked the *SQL way. Now when NAMING(*SYS) is specified, you can use *SQL syntax where needed and rest assured that *SYS naming features are being implemented for unqualified names. Which is what we always wanted.

3-level/3-tier naming allows you to qualify a file to a specific system. Yes I said "system". This is a shortcut form of Remote Database Access without the need to issue a CONNECT TO statement. You simply specify the remote system name (as designated on the WRKRDBDIRE command) and you can query (or update) the table on the other system directly. This is very cool if, like me, you're connected to several systems and want to deliberately identify which system you are updating.

SELECT CSTNBR,CSTNAME, BALDUE FROM CHICAGO.PRODDATA.SALES WHERE BALDUE > 1000

The above statement queries the SALES file in the library named PRODDATA. It issues a "CONNECT TO" the CHICAGO remote database automajically and returns the data. For example, using SQL Query File's RUNSQLF command, you used to have to connect as follows:

RUNSQLF 'SELECT CSTNBR, CSTNAME, BALDUE FROM PRODDATA/SALES' RDB(CHICAGO) PWD(ROSEBUD)

Now, you simply add the "CHICAGO" database name (remember "database" is SQL-speak for "Library") to the qualified SQL name and omit the RDB, USER, and PWD parameters of the RUNSQLF command.

RUNSQLF 'SELECT CSTNBR, CSTNAME, BALDUE FROM CHICAGO.PRODDATA.SALES' 

Using NAMING(*SYS) or NAMING(*SQL) supports this feature. However, NAMING(*SYS) allows users to specify unqualified file names and *LIBL is used as... well you know that story.

Deprecated V5R4

I love this new feature but the only slight drawback is that if the remote system is running IBM i5 / OS V5R4 it won't connect to that system. The legacy method is still required. Attempting to connect to a v5r4 system using 3-level naming will return the following message:

Qualified object name SALES not valid.

But aside from that very slight issue, the new relaxed NAMING(*SYS) means that we can now use *SYS exclusively without worrying about missing out on the advanced features offered by good old SQL.