Informix Guide to SQL: Syntax Segments

The owner name segment specifies the name of the owner of a database object in a database. Use this segment whenever you see a reference to Owner Name in a syntax diagram.

Usage

In databases that are not ANSI compliant, the owner name is optional. You do not need to specify owner when you create database objects or use data access statements. If you do not specify owner when you create a database object, the database server assigns your login name as the owner of the object in most cases. For exceptions to this rule, see Ownership of Created Database Objects in CREATE FUNCTION and Ownership of Created Database Objects in CREATE procedure.

If you specify owner in data-access statements, the database server checks it for correctness. Without quotation marks, owner is case insensitive.

The following example shows four queries that can access data successfully from the table kaths.tab1:

SELECT * FROM tab1
SELECT * FROM kaths.tab1
SELECT * FROM KATHS.tab1
SELECT * FROM Kaths.tab1

When you use quotation marks, owner is case sensitive. In other words, quotation marks signal the database server to read or store the name exactly as typed. This case sensitivity applies when you create or access a database object.

Suppose you have a table whose owner is Sam. You can use one of the following two statements to access data in the table.

SELECT * FROM table1
SELECT * FROM 'Sam'.table1

The first query succeeds because the owner name is not required. The second query succeeds because the specified owner name matches the owner name as it is stored in the database.

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. Because this type of query requires that you use quotation marks, 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 = 'table1' AND owner 
= 'kaths'
SELECT * FROM systables WHERE tabname = 'table1' AND owner 
= 'Kaths'

User informix is the owner of the system catalog tables.

Tip: The USER keyword returns the login name 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.

ANSI-Compliant Databases Restrictions and Case Sensitivity

If you specify the owner name when you create or rename a database object in an ANSI-compliant database, you must include the owner name in data access statements. You must include the owner name when you access a database object that you do not own.

The following table describes how the database server reads and stores owner when you create, rename, or access a database object.

Because the database server automatically upshifts owner if it is not enclosed in quotation marks, case-sensitive errors can cause queries to fail.

For example, if you are the user nancy, and you use the following statement, the resulting view has the name nancy.njcust.

CREATE VIEW 'nancy'.njcust AS

	SELECT fname, lname FROM customer WHERE state = 'NJ'

The following SELECT statement fails because it tries to match the name NANCY.njcust to the actual owner and table name of nancy.njcust.

SELECT * FROM nancy.njcust


Tip: When you use the owner name as one of the selection criteria in a query, (for example, WHERE owner = 'kaths'), make sure that the quoted string matches the owner name as it is stored in the database. If the database server cannot find the database object or database, you might need to modify the query so that the quoted string uses uppercase letters (for example, WHERE owner = 'KATHS').