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.

SAP ASE

With SAP® ASE, an object name takes the following form:
[{database|[database]}.[{owner|[owner]}.]]{identifier|[identifier]}

Informix database object names are not case sensitive in non-ANSI databases.

SAP ASE database objects names are case sensitive by default.

Solution

To write portable SQL, regarding database object names:
  1. Use simple database object names (without any owner/schema prefix)
  2. Do not use double quotes to surround database object identifiers.
  3. If needed, define public synonyms to reference database objects in others databases/schema.
  4. Specify database object identifiers in lowercase.