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
