Name resolution of SQL objects

Informix®

Informix uses the following form to identify an SQL object:

database[@dbservername]:][{owner|"owner"}.]identifier

The ANSI convention is to use double quotes for identifier delimiters (For example: "customer"."cust_name").

Informix database object names are not case-sensitive in non-ANSI databases. When using double-quoted identifiers, Informix becomes case sensitive.

With non-ANSI Informix databases, you do not have to give a schema name before the tables when executing an SQL statement:

SELECT ... FROM customer WHERE ...

In Informix ANSI compliant databases:

The table name must include "owner", unless the connected user is the owner of the database object.
The database server shifts the owner name to uppercase letters before the statement executes, unless the owner name is enclosed in double quotes.

IBM® DB2®

IBM DB2 database object names are case-sensitive. When a name is used without double quotes, it is automatically converted to uppercase letters. When using double quotes, the names are not converted:

CREATE TABLE tab1 ( Key INT, Col1 CHAR(20) )
-- Table name is "TAB1", column names are "KEY" and "COL1"
CREATE TABLE "Tab1" ( "Key" INT, "Col1" CHAR(20) )
-- Table name is "Tab1", column names are "Key" and "Col1"

In an IBM DB2 database, tables always belong to a database schema. When executing a SQL statement, a schema name must be used as the high-order part of a two-part object name, unless the current schema corresponds to the table's schema.

The default (implicit) schema is the current user's name but it can be changed with the SET SCHEMA instruction.

Example: The table "TAB1" belongs to the schema "SCH1". User "MARK" (implicit schema is "MARK") wants to access "TAB1" in a SELECT statement:

SELECT ... FROM TAB1 WHERE ... 
-- Error "MARK"."TAB1" is an undefined name. SQLSTATE=42704
SELECT ... FROM SCH1.TAB1 WHERE ...
-- OK.
SET SCHEMA SCH1
-- Changes the current schema to SCH1.
SELECT ... FROM TAB1 WHERE ...
-- OK.

Note: When executing the SET SCHEMA instruction, the database interface does not use double quotes around the schema name ( = name is converted to uppercase letters). Make sure that the schema name is created with uppercase letters in the database.

DB2 provides aliases, but they cannot be used to make a database object name public because aliases belong to schemas also.

Solution

To write portable SQL, regarding database object names:

Use simple database object names (without any owner/schema prefix)
Do not use double quotes to surround database object identifiers.
If needed, define public synonyms to reference database objects in others databases/schema.
Specify database object identifiers in lowercase.

See also Naming database objects.

Without double quotes around the database object names, all names will be converted to uppercase letters by DB2 before executing the SQL.

The DB2 schema concept:

After a connection, the database interface can automatically execute a

SET SCHEMA
name

instruction if the following FGLPROFILE entry is defined:

dbi.database.dbname.db2.schema= "name"

Here dbname identifies the database name used in the BDL program (DATABASE dbname) and name is the schema name to be used in the SET SCHEMA instruction. If this entry is not defined, no SET SCHEMA instruction is executed and the current schema defaults to the user's name.

Examples:

dbi.database.stores.db2.schema= "STORES1"
dbi.database.accnts.db2.schema= "ACCSCH"

Note: DB2 does not check the schema name when the SET SCHEMA instruction is executed. Setting a wrong schema name results in "undefined name" errors when performing subsequent SQL instructions like SELECT, UPDATE, INSERT.

In accordance with this automatic schema selection, you must create a DB2 schema for your application:

Connect as a user with the DBADM authority.
Create an administrator user dedicated to your application. For example, "STORESADM". Make sure this user has the IMPLICIT_SCHEMA privilege (this is the default in DB2).
Connect as the application administrator "STORESADM" to create all database objects ( tables, indexes, ...). In our example, a "STORESADM" schema will be created implicitly and all database objects will belong to this schema.

As a second option you can create a specific schema with the following SQL command:

CREATE SCHEMA "name" AUTHORIZATION "appadmin"

See the IBM DB2 manuals for more details about schemas.