• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Quirky SQL Creations

    December 11, 2017 Ted Holt

    Hey, Ted:

    We are creating a view from a source member using the Run SQL Statements (RUNSQLSTM) command. None of the objects are qualified in the source member. The system always creates the view in the wrong library, no matter how we set the current library. Can you tell me what is happening?

    –William

    William ran up against the quirky behavior of the SQL CREATE VIEW statement. It sure threw me for a loop. I would have thought that the view would be created in the current library. Not so. William found the answer to his question in the IBM Knowledge Center. “If not qualified and there is no default schema, the view name will be created in the same schema as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expression).” I read a bit more and learned some things about unqualified objects and SQL. I thought you might like to know what I learned.

    The destination of an unqualified object depends first on whether you are using system (*SYS) or SQL (*SQL) naming convention. The SQL convention is the one used on other systems, which don’t have the option of using the system convention that we have with DB2 for i. When using SQL convention, the system creates unqualified objects in a library with the same name as the user who is running the CREATE command. That is, if my user profile is JSMITH, the system will try to create unqualified objects in JSMITH. There may be exceptions to this rule, but if so, I don’t know what they are. I have never worked in a shop that used the SQL convention, but all my tests have worked that way. If you’re a wizard with the SQL naming convention, please email me and enlighten us all.

    William was using the system convention. Chances are that you are, too. The system convention is wonderful because it lets us use that powerful IBM i construct called the library list. The library list is great for reading tables and views, but it doesn’t apply when you create an unqualified object.

    Where the object is created depends on the type of object and the current library.

    Here’s a table I made by copying and pasting from IBM’s website:

    Statement Rule
    CREATE TABLE

    CREATE FUNCTION

    CREATE TYPE (ARRAY)

    CREATE TYPE (distinct)

    CREATE VARIABLE

     

    If the value of the CURRENT SCHEMA special register is *LIBL, the object will be created in the current library (*CURLIB).

     

    Otherwise, the object will be created in the current schema.

     

    CREATE VIEW If there is no default schema, the view name will be created in the same schema as the first table specified on the first FROM clause (including FROM clauses in any common table expressions or nested table expression).

     

    If no tables are referenced in the fullselect, the view will be created in the same schema as the first user defined table function.

     

    If no table or user defined table function is referenced in the fullselect, the current library (*CURLIB) will be used.

     

    CREATE INDEX

    CREATE TRIGGER

     

    The object will be created in the same schema as the subject table.
    CREATE ALIAS The alias will be created in the same schema as the table or view for which the alias was created.

     

    If the table is not qualified and does not exist at the time the alias is created:

     

    If the value of the CURRENT SCHEMA special register is *LIBL, the alias will be created in the current library (*CURLIB).

    Otherwise, the alias will be created in the current schema.

     

    CREATE MASK

    CREATE PERMISSION

    If not qualified and there is no default schema, the object will be created in the same schema as the table-name.

     

    CREATE PROCEDURE If the value of the CURRENT SCHEMA special register is *LIBL, the procedure will be created in the current library (*CURLIB).

     

    Otherwise, the procedure will be created in the current schema.

     

    CREATE TYPE (ARRAY)

    CREATE TYPE (distinct)

    CREATE VARIABLE

    Same as CREATE TABLE.

     

    If the value of the CURRENT SCHEMA special register is *LIBL, the object will be created in the current library (*CURLIB).

     

    Otherwise, the object will be created in the current schema.

     

     

    Fortunately, there is an easy way to avoid all this mess. Use the SET SCHEMA statement to tell SQL where to put the objects. As with many other features of SQL, SET SCHEMA has three forms:

    SET SCHEMA = 'BR549';
    SET CURRENT SCHEMA = 'BR549';
    SET CURRENT_SCHEMA = 'BR549';

    These commands set the current schema, which is not the same thing as the current library. This confused me at first. A schema is implemented as a library, so I would have thought that current schema and current library would be the same. Well, I would have been wrong.

    When you create an unqualified object, the system will place that object in the current schema. Setting the current schema is a good way to create unqualified objects, considering that you typically want all related objects — tables, views, indexes, etc. — in the same schema.

    RELATED STORIES

    Qualification of unqualified object names

    CREATE VIEW (IBM Knowledge Center)

    Where Do Library Lists Reside?

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Four Hundred Guru, IBM i, SQL

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Mad Dog 21/21: Bubbling Over Trinity Guard Fills Gap In IBM i Network Security

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 81

This Issue Sponsored By

  • Profound Logic Software
  • COMMON
  • HelpSystems
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • The AS/400 Lessons Come Back Around With Power9 Systems
  • Trinity Guard Fills Gap In IBM i Network Security
  • Guru: Quirky SQL Creations
  • Mad Dog 21/21: Bubbling Over
  • As I See It: Disruption

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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 © 2025 IT Jungle