Referencing Tables Owned by User informix

If you use the owner name as one of the selection criteria to access database object information from one of the system catalog tables, the owner name is case sensitive. To preserve lettercase, you must enclose owner in single or double quotation marks, and you must type the owner name exactly as it is stored in the system catalog table. Of the following two examples, only the second successfully accesses information on the table Kaths.table1.

SELECT * FROM systables WHERE tabname = 'tab1' AND owner = 'kaths';
SELECT * FROM systables WHERE tabname = 'tab1' AND owner = 'Kaths';

User informix is the owner of the system catalog tables, and in an ANSI-compliant database you must specify informix as a qualifier when SQL statements reference system catalog tables, unless you are user informix:

SELECT * FROM "informix".systables WHERE tabname = 'tab1' AND owner = 'Kaths';

HCL OneDB™ accepts any of the following notations to specify a system catalog table of an ANSI-compliant database:

"informix".system_table
informix.system_table
'informix'.system_table

Of these three formats, however, only the first, where the owner is specified as a delimited identifier, is directly interoperable with most other database servers. For the format with no delimiters, the ANSI/ISO standard for SQL upshifts the lowercase letters to INFORMIX, and the same standard does not support single ( ' ) quotation marks as valid delimiters for owner names or for schema names.

In contrast, HCL OneDB treats the name informix as a special case, and preserves lowercase letters when informix is specified, with or without delimiters, whether or not the database is ANSI-compliant. To write SQL code that is portable to non-HCL OneDB database servers, however, you should always delimit the owner names of database objects between double ( " ) quotation marks.

The following SQL examples use undelimited owner names:

CREATE TABLE informix.t1(i SERIAL NOT NULL);
CREATE TABLE someone.t1(i SERIAL NOT NULL);

If these statements execute successfully, the first table has informix registered in systables as the owner, and the second has SOMEONE registered as the owner. When the owner name is delimited by quotation marks in SQL statements, the specified lettercase of owner is preserved, but the lettercase does not matter when the owner name is undelimited, because HCL OneDB upshifts most undelimited owner names, but downshifts the undelimited informix (or INFORMIX) owner name to informix.

For example, suppose that after the previous two CREATE TABLE statements execute successfully, user informix issues the following statement:

CREATE TABLE INFORMIX.t1(i SERIAL NOT NULL);

This statement fails, because the combination of owner name and table name is not unique, if the previously registered table t1 that is owned by informix already exists in the database.

Tip: The USER operator returns the login name of the current user exactly as it is stored on the system. If the owner name is stored differently from the login name (for example, a mixed-case owner name and an all lowercase login name), the owner = USER syntax fails.