Case sensitivity in object names:
CREATE TABLE Tab1 ( Key INT, Col1 CHAR(20) ) SELECT COL1 FROM TAB1
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"
The DB2 schema concept:
With non-ANSI Informix databases, you do not have to give a schema name before the tables when executing an SQL statement.
SELECT ... FROM table-name WHERE ...
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.
DB2 provides "aliases", but they cannot be used to make a database object name public because aliases belong to schemas also.
Case sensitivity in object names:
Avoid the usage of double quotes around the database object names. All names will be converted to uppercase letters.
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"
In accordance with this automatic schema selection, you must create a DB2 schema for your application:
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.