Away With The SQL Slash Separator

June 19, 2013 Michael Sansoterra

One potentially confusing aspect of programming SQL in DB2 for i for non-IBMers is the use of the forward slash character to qualify an SQL object with a schema. IBM i devs don’t have a problem with the slash because we recognize it as the character we use when qualifying objects while tooling around the i/OS green screen. Others database devs may wonder what sophisticated function the slash serves. However, in IBM i 7.1, IBM introduced a change in DB2 Group PTF SF99701 Level 15 that is worth noting.

When using the system naming convention, developers can now use either a dot (.) or a slash (/) to separate a schema and object name. Here are a few examples:

/* Table or view name */
SELECT * FROM PRODLIB.ITEMS

/* Table Function */
SELECT * 
  FROM TABLE(PRODLIB.ORDER_REVISION(ORDER_NUM)) ORDER_REV

/* Scalar Function */
SELECT ITEM,PRODLIB.CUR_PRICE(CUSTOMER,SALE_DATE)  
AS CURRENT_PRICE FROM ORDER_HISTORY

/* Procedure Call */
CALL PRODLIB.GET_ITEMS

This change to use the dot separator is beneficial for a number of reasons:

Most database management systems use the dot notation.
Most client/server tools (JDBC, ODBC, OLE DB, .NET) default to use the dot notation.
Using the dot makes writing portable code easier (and it will be code that non-“DB2 for i” devs will understand).
You can take advantage of the benefits of the system naming convention’s library list search.
Table and scalar user-defined function names can now be qualified with a schema (a.k.a., library) even when the system naming convention is used. In the past, you couldn’t qualify a UDF when using the system naming convention.
You can interchange the same SQL statement between different tools that default to *SQL and *SYS without having to change the blasted separator character.

At the time of this writing, there is one caveat; the latest IBM i ODBC driver may not be up to snuff when handling this change. I was hoping to use this new feature with a SQL Server linked server using the ODBC driver. Using the dot separator with the system naming convention would allow me to use the four-part naming convention when accessing DB2 data, as well as allowing me to call stored procedures and dynamic SQL statements that use the library list.

Similarly, I was unable to use an earlier version of the IBMDASQL OLE DB provider with the SQL Server “dual purpose” linked server either, but an IBM rep tried it with the latest i Access service pack (-SI47412) and was able to make it work. (Because I was running my tests on Windows 8, I couldn’t upgrade to the latest SP). Hopefully, this problem in the ODBC driver will be removed in one of the upcoming service packs.

In short, it’s advantageous to forget the slash separator when coding SQL. Fortunately, we can now do this and still maintain the benefit of the library list where applicable.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot