Schema Name

Schema Naming Considerations

Schema names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters. A schema name should not be specified as a delimited identifier. Attempting to specify “USER” or any other SQL reserved word as a schema name results in an SQLCODE -312 error. The INFORMATION_SCHEMA schema name and the corresponding INFORMATION.SCHEMA package name are reserved in all namespaces. Users should not create tables/classes within this schema/package.

When you issue a create operation, such as CREATE TABLE, that specifies a schema that does not yet exist, InterSystems IRIS creates the new schema. InterSystems IRIS uses the schema name to generate a corresponding package name. Because the naming conventions for schemas and their corresponding packages differ, the user should be aware of name conversion considerations for non-alphanumeric characters. These name conversion considerations are not the same as for tables:

• Initial character:

  • % (percent): Specify % as the first character of a schema name denotes the corresponding package as a system package, and all of its classes as system classes. This usage requires appropriate privileges; otherwise, this usage issues an SQLCODE -400 error with the %msg indicating a <PROTECT> error.

  • _ (underscore): If the first character of a schema name is the underscore character, this character is replaced by a lowercase “u” in the corresponding package name. For example, the schema name _MySchema generates the package name uMySchema.

• Subsequent characters:

  • _ (underscore): If any character other than the first character of a schema name is the underscore character, this character is replaced by a period (.) in the corresponding package name. Because a period is the class delimiter, an underscore divides a schema into a package and a sub-package. Thus My_Schema generates the package My containing the package Schema (My.Schema).

  • @, #, $ characters: If a schema name contains any of these characters, these characters are stripped from the corresponding package name. If stripping these characters would produce a duplicate package name, the stripped package name is further modified: the final character of the stripped schema name is replaced by a sequential integer (beginning with 0) to produce a unique package name. Thus My@#$Schema generates package MySchema, and subsequently creating My#$Schema generates package MySchem0. The same rules apply to table name corresponding class names.

Reserved Schema Names

The INFORMATION_SCHEMA schema name and the corresponding INFORMATION.SCHEMA package name are reserved in all namespaces. Users should not create tables/classes within this schema/package.

The IRIS_Shard schema name is reserved in all namespaces. Users should not create tables, views, or procedures within this schema. Items stored in the IRIS_Shard schema are not displayed by catalog queries or INFORMATION_SCHEMA queries.

Default Schema Name

• When performing a DDL operation, such as creating or deleting a table, view, trigger, or stored procedure, an unqualified name is supplied the default schema name. Schema search path values are ignored.

• When performing a DML operation, such as a SELECT, CALL, INSERT, UPDATE, or DELETE to access an existing table, view, or stored procedure, an unqualified name is supplied the schema name from the schema search path (if provided). If there is no schema search path, or the named item is not located using the schema search path, the default schema name is supplied.

The initial setting is to use the same default schema name for all namespaces (system-wide). You can set the same default schema name for all namespace, or set a default schema name for the current namespace.

If you create a table or other item with an unqualified name, InterSystems IRIS assigns it the default schema name, and the corresponding persistent class package name. If a named or default schema does not exist, InterSystems IRIS creates the schema (and package) and assigns the created item to the schema. If you delete the last item in a schema, InterSystems IRIS deletes the schema (and package). The following description of schema name resolution applies to table names, view names, and stored procedure names.

The initial system-wide default schema name is SQLUser. The corresponding persistent class package name is User. Therefore, either the unqualified table name Employee or the qualified table name SQLUser.Employee would generate the class User.Employee.

Because USER is a reserved word, attempting to specify a qualified name with the schema name of User (or any SQL Reserved Word) results in an SQLCODE -1 error.

To return the current default schema name, invoke the $SYSTEM.SQL.Schema.Default()Opens in a new tab method.

Or use the following pre-processor macro:

#include %occConstant
  WRITE $$$DefSchema

You can change the default schema name using either of the following:

• Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then SQL. On this screen you can view and edit the current system-wide setting of Default Schema. This option sets the default schema name system-wide. This system-wide setting can be overridden by a SetDefault() method value for the current namespace.

• The $SYSTEM.SQL.Schema.SetDefault()Opens in a new tab method. By default, this method sets the default schema name system-wide. However, by setting the Boolean 3rd argument = 1, you can set the default schema for just the current namespace. When different namespaces have different default schema names, the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method returns the default schema name for the current namespace.

The schema name is used to generate the corresponding class package name. Because these names have different naming conventions, they may not be identical.

You can create a schema with the same name as an SQL reserved word by setting this as the system-wide Default Schema, though this is not recommended. A default schema named User generates the corresponding class package name Use0, following the class naming uniqueness convention.

Schema Search Path

When accessing an existing table (or view, or stored procedure) for a DML operation, an unqualified name is supplied the schema name from the schema search path. Schemas are searched in the order specified and the first match is returned. If no match is found in the schemas specified in the search path, or no search path exists, the default schema name is used. (Note that the #import macro directive uses a different search strategy and does not “fall through” to the default schema name.)

• In Embedded SQL you can use the #sqlcompile path macro directive or the #import macro directive to supply a schema search path that InterSystems IRIS uses to resolve unqualified names. #sqlcompile path resolves an unqualified name with the first match encountered. #import resolves an unqualified name if there is exactly one match for all the schemas listed in the search path.

• The following example provides a search path containing two schema names:

  #sqlcompile path=Customers,Employees

  

  For further details, refer to Macro Preprocessor Directives.

• In Dynamic SQL you can use the %SchemaPath property to supply a schema search path that InterSystems IRIS uses to resolve unqualified table names. You can specify the %SchemaPath property directly or specify it as the second parameter of the %SQL.StatementOpens in a new tab %New()Opens in a new tab method. The following example provides a search path containing two schema names:

    SET tStatement = ##class(%SQL.Statement).%New(0,"Customers,Employees")

  

• In SQL Shell you can set the PATH SQL Shell configuration parameter to supply a schema search path that InterSystems IRIS uses to resolve unqualified names.

If the unqualified name does not match any of the schemas specified in the schema search path or the default schema name, an SQLCODE -30 error is issued, such as the following: SQLCODE: -30 Message: Table 'PEOPLE' not found within schemas: CUSTOMERS,EMPLOYEES,SQLUSER.

Platform-Specific Schema Name Inclusion

When creating an ODBC-based query to run from Microsoft Excel via Microsoft Query on the Mac, if you choose a table from the list of those available, the generated query does not include the table’s schema (equivalent to the package for a class). For example, if you choose to return all the rows of the Person table from the Sample schema, the generated query is:

SELECT * FROM Person

Because InterSystems IRIS interprets an unqualified table name as being in the SQLUser schema, this statement either fails or returns data from the wrong table. To correct this, edit the query (on the SQL View tab) to explicitly refer to the desired schema. The query should then be:

SELECT * FROM Sample.Person

Listing Schemas

The INFORMATION.SCHEMA.SCHEMATAOpens in a new tab persistent class lists all schemas in the current namespace.

The following example returns all non-system schema names in the current namespace:

SELECT SCHEMA_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA WHERE NOT SCHEMA_NAME %STARTSWITH '%'

The left side of the Management Portal SQL interface allows you to view the contents of a schema (or multiple schemas that match a filter pattern). See Filtering Schema Contents for further details.