• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored By
    SEQUEL SOFTWARE

    Advance to the Next Level of Data Access

    The idea of handing over the keys to your entire database
    to multiple users is daunting.
    It doesn't have to be!

    With the right KPI reporting tools and secure data access,
    you can give each user what they need, when they need it--and free your team
    to work on projects more strategic to the business.

    Download the Data Access & Reporting Kit.

    Sponsored Links

    Profound Logic Software:  Live Webinar: How to Take IBM i Everywhere with Mobile Apps. June 26
    Maxava:  Webinar: Business Continuity For The 21st Century. June 20.
    Abacus Solutions:  Qualified IBM i users eligible for free pair of running shoes

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    StorageQuest Uses Flash Memory in VTL Silver Anniversary For Silverlake

    Leave a Reply Cancel reply

Volume 13, Number 12 -- June 19, 2013
THIS ISSUE SPONSORED BY:

Bug Busters Software Engineering
SEQUEL Software
WorksRight Software

Table of Contents

  • Creating And Using i Project
  • Away With The SQL Slash Separator
  • Removing Members From And Deleting An i OS Group Profile

Recent Posts

  • Big Blue Finally Brings IBM i To Its Own Public Cloud
  • Building A Positive Culture of Learning On IBM i
  • Guru: Change XML Elements in SQL
  • Four Hundred Monitor, February 18
  • IBM i PTF Guide, Volume 21, Number 7
  • Guru Classic: Triggers – Allow Repeated Change
  • Guru Classic: Who Needs Custom Perspectives In RDi?
  • Guru Classic: A Bevy of BIFs — %CHAR, %EDITC and %EDITW
  • Settling In With IBM i For The Long Haul
  • Domino And Notes 10 Finally Come To IBM i

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2017 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.