Summary of Lettercase Rules for Owner Names

To create a database object, such as a table called mytab, a user whose login name is Otho can declare the name of the new database object in any of the following ways:
  1. CREATE TABLE mytab . . .
  2. CREATE TABLE Otho.mytab . . .
  3. CREATE TABLE "Otho".mytab . . .
The format in which an undelimited owner name (as in the second example) is stored in the owner column of the systables system catalog table is dependent on whether or not the local database is an ANSI-compliant database.
  • In case 1, no owner name is specified. The implicit owner of the table is Otho, the user who created the table, and that owner name is stored in the systables table in the same format (Otho) as the user ID of the owner, independent of the ANSI-compliance status of the database.
  • In case 2, an undelimited owner name is specified. The systables table stores all letters in the owner name in lowercase (here as otho) for databases that are not ANSI-compliant databases. For ANSI-compliant databases in which ANSIOWNER is not set to 1, systables table stores all owner name letters in uppercase (here as OTHO). If ANSIOWNER is set to 1, however, the name is stored in the same lettercase as specified in the DDL statement (here as Otho).
  • In case 3, the delimited owner name is stored in the systables table in the same format in which it was specified (here as Otho), independent of the ANSI-compliance status of the database.
Note that user identifiers are case sensitive, but database object names are case insensitive. Therefore, the same user cannot own both a table tab and a table TAB.
In addition to the CREATE TABLE statement in these examples, all SQL statements and SPL statements follow these rules where a table name can be specified. For example, when using DROP TABLE, the format in which owner name appears while the statement is being processed is dependent upon the same conditions:
  • whether an explicit owner name is specified.
  • if an explicit owner name is specified, whether quotation marks delimit the owner name.
  • if an explicit owner name is not delimited by quotation marks, whether or not the database is ANSI compliant.
  • if the database is ANSI compliant, whether or not ANSIOWNER was set to 1 before the database was initialized.