02 - DB2 for i (EN)02a - SQL (EN)

Naming SYS vs SQL how many differences

Last Updated on 1 January 2020 by Roberto De Pedrini

SQL has become the industry standard for all relational DBs, both for defining databases and for accessing database objects.

“DB2 for i” is the only database integrated directly into the operating system and at the time it already had its own method of accessing objects and assigning authorizations with its applicable rules for the use of library lists.

Unfortunately these rules did not correspond to the SQL standard, so to enable both the “IBM i” and SQL standard naming conventions we have two modes of naming conventions * SYS and * SQL.

How do you set the type of denomination? It depends on the work environment:

Work environment Setting mode
Interactive STRSQL Optional key F13
SQL Incorporated into programs EXEC SQL Set Option Naming = * SQL
ACS Defined in the connection

The first macro difference

With * SYS we can use “/” or “.” As qualifier:

Select * from MYLIB / MYTABLE

equivalent to

Select * from MYLIB.MYTABLE

instead with * SQL only the qualifier “.”;

If you do not specify the schema, a “Select * from MYTABLE”

with * SYS it will search for the table in the list of libraries while with * SQL it will search for the table in the schema with the same name as the user.

Differences between the creation of a library, scheme or collection

The terms library, schema and collection can be used interchangeably and describe a container in which objects are grouped.

The term library comes from the area of “AS / 400”, the term “collection” was used for the container in which the database objects are originally stored by IBM.

However, both terms (library and collection) do not match the term defined in the SQL standard, where the same type of object is called a schema.

A library can be created with the CRTLIB command or with the CREATE COLLECTION (IBM specific) or CREATE SCHEMA (SQL standard) statements.

While only an empty library is generated when using the CRTLIB command, a number of objects are created directly in the schema when SQL statements are used.

This creates a journal receiver and a journal in which the tables created in the schema are automatically posted, catalog views are also created that contain information about the database objects in the schema.

Creating a schema or database objects with * SYS system naming:

If a library is created using SQL using CREATE SCHEMA, CREATE COLLECTION or CRTLIB with system naming conventions, the user profile or group profile becomes the owner of the schema / library.

The owner has all rights to the created schema, the * PUBLIC receives the authorization stored in the QCRTAUT system value (default * CHANGE).

The same rule applies to all other database objects created with * sys.

Creating a schema with * SQL system naming, things change:

The Library object created belongs to the user profile with the same name as the schema or if the user who created the schema does not exist.

It doesn’t matter if the user profile is assigned to a group profile, the owner has all the rights to the object and is the only one who can access the object.

The * PUBLIC permission is always set to * EXCLUDE or the QCRTAUT system value is ignored.

Creating database objects with SQL naming changes here too

If a database object is created in a schema / library using SQL commands with SQL naming conventions, the owner is the user profile that created the object.

However, if the user profile is assigned to a group profile and it is stored in the user profile that the group profile becomes the owner of the created objects, the group profile becomes the owner of the object.

However, the two rules mentioned above (owner = user and owner = group profile) only apply if there is no user profile corresponding to the name of the schema / library in which the object is to be created.

If a corresponding user profile is available, this user profile becomes the owner of the object.

Although you cannot assign permissions to a schema / library using SQL commands, you can use the following SQL commands to assign or revoke permissions to database objects within a schema / library:

The SQL GRANT statement can be used to assign permissions for users, group profiles, but also for * PUBLIC to tables and views, as well as to stored procedures and user-defined functions.

The permissions assigned with the SQL commands, however, differ from the permissions assigned with the CL commands, if a user receives all the permissions via the SQL GRANT command with * ALL, he can modify an object but not delete it!

On the other hand, you can assign column or field permissions within tables or physical files using the SQL GRANT command.

The SQL REVOKE statement revokes authorizations is the opposite of GRANT.

The SET SESSION AUTHORIZATION and SET SESSION USER statements can affect the ownership and permissions of objects when working with the SQL naming convention.

After establishing a connection, the user profile can be switched to a different user profile (authorization ID) to adopt the access permissions of this user profile using the SET SESSION AUTHORIZATION or SET SESSION instructions.

You have already learned how the user profile value is applied to object ownership and authorities when creating objects with SQL Naming so pay attention.

Creating / searching database objects with * SYS naming without a bulleted scheme

If a database object is created unqualified using system naming conventions, the object is created in CURLIB.

However, if a current library has been set up using the CL CHGCURLIB (edit current library) command, the database object is created in this library.

If a current library / schema was specified using SQL using the SET CURRENT SCHEMA SQL command, the object is created in this library, even if another current library was specified using the CL CHGCURLIB command.

Creating / searching database objects with * SQL naming without a bulleted scheme

If a database object is created without qualification using SQL naming conventions, an attempt is made to generate this object in a library / schema that corresponds to the name of the user profile that wants to create the object.

If a corresponding library is not available, an error message is issued and the object is not created.

If a current schema / library was specified using the SQL SET CURRENT SCHEMA command, the object is created in this library, just like when using SQL naming conventions.


Given the differences between the two naming conventions, I hope I have made some clarity on any problems that may happen to you, the next.

Dario Carnevale Schianca

Leave a Reply

%d bloggers like this: