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:
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.
|
