Skip to main content

Defining Tables

This topic describes how you create tables in InterSystems SQL.

Table Names and Schema Names

You can create a table either by defining the table (using CREATE TABLE) or by defining a persistent class that is projected to a table:

  • DDL: InterSystems IRIS® data platform uses the table name specified in CREATE TABLE to generate a corresponding persistent class name, and uses the specified schema name to generate a corresponding package name.

  • Class Definition: InterSystems IRIS® data platform uses the persistent class name to generate a corresponding table name, and uses the package name to generate a corresponding schema name.

The correspondence between these two names may not be identical for the following reasons:

  • Persistent classes and SQL tables follow different naming conventions. Different valid character and length requirements apply. Schema and table names are not case-sensitive; package and class names are case-sensitive. The system automatically converts a valid supplied name to a valid corresponding name, insuring that the generated name is unique.

  • The match between a persistent class name and the corresponding SQL table name is a default. You can use the SqlTableName class keyword to supply a different SQL table name.

  • The default schema name may not match the default package name. If you specify an unqualified SQL table name or persistent class name, the system supplies a default schema name or package name. The initial default schema name is SQLUser; the initial default package name is User.

Schema Name

A table, view, or stored procedure name is either qualified (schema.name) or unqualified (name).

  • If you specify a schema name (qualified name), the specified table, view, or stored procedure is assigned to that schema. If the schema does not exist, InterSystems SQL creates the schema and assigns the table, view, or stored procedure to it.

  • If you do not specify a schema name (unqualified name), InterSystems SQL assigns a schema using either the default schema name or a schema search path, as described below.

This section describes the following topics:

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.

Caution:

When you change the default SQL schema name, the system automatically purges all cached queries in all namespaces on the system. By changing the default schema name, you change the meaning of all queries that contain unqualified table, view, or stored procedure names. It is strongly recommended that the default SQL schema name be established at InterSystems IRIS installation and not subsequently modified.

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.

_CURRENT_USER Keyword

  • As System-wide Default Schema Name: If you specify _CURRENT_USER as the default schema name, InterSystems IRIS assigns the user name of the currently logged-in process as the default schema name. The _CURRENT_USER value is the first part of the $USERNAME ObjectScript special variable value. If $USERNAME consists of a name and a system address (Deborah@TestSys), _CURRENT_USER contains only the name piece; this means that _CURRENT_USER can assign the same default schema name to more than one user. If the process has not logged in, _CURRENT_USER specifies SQLUser as the default schema name.

    If you specify _CURRENT_USER/name as the default schema name, where name is any string of your choice, then InterSystems IRIS assigns the user name of the currently logged-in process as the default schema name. If the process has not logged in, name is used as the default schema name. For example, _CURRENT_USER/HMO uses HMO as the default schema name if the process has not logged in.

    In $SYSTEM.SQL.Schema.SetDefault()Opens in a new tab, specify "_CURRENT_USER" as a quoted string.

  • As Schema Name in DDL Command: If you specify _CURRENT_USER as the explicit schema name in a DDL statement, InterSystems IRIS replaces it with the current system-wide default schema. For example, if the system-wide default schema is SQLUser, the command DROP TABLE _CURRENT_USER.OldTable drops SQLUser.OldTable. This is a convenient way to qualify a name to explicitly indicate that the system-wide default schema should be used. It is functionally identical to specifying an unqualified name. This keyword cannot be used in DML statements.

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")

    For further details, refer to “Using Dynamic SQL”.

  • 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.

Table Name

Every table has a unique name within its schema. A table has both an SQL table name and a corresponding persistent class name; these names differ in permitted characters, case-sensitivity, and maximum length. If defined using the SQL CREATE TABLE command, you specify an SQL table name that follows identifier conventions; the system generates a corresponding persistent class name. If defined as a persistent class definition, you must specify a name that contains only alphanumeric characters; this name is used as both the case-sensitive persistent class name and (by default) the corresponding non-case-sensitive SQL table name. The optional SqlTableName class keyword allows the user to specify a different SQL table name.

When you use the CREATE TABLE command to create a table, InterSystems IRIS uses the table name to generate a corresponding persistent class name. Because the naming conventions for tables and their corresponding classes differ, the user should be aware of name conversion considerations for non-alphanumeric characters:

  • Initial character:

    • % (percent): % as the first character of a table name is reserved and should be avoided (see Identifiers). If specified, the % character is stripped from the corresponding persistent class name.

    • _ (underscore): If the first character of a table name is the underscore character, this character is stripped from the corresponding persistent class name. For example, the table name _MyTable generates the class name MyTable.

    • Numbers: The first character of a table name cannot be a number. If the first character of the table name is a punctuation character, the second character cannot be a number. This results in an SQLCODE -400 error, with a %msg value of “ERROR #5053: Class name 'schema.name' is invalid” (without the punctuation character). For example, specifying the table name _7A generates the %msg “ERROR #5053: Class name 'User.7A' is invalid”.

  • Subsequent characters:

    • Letters: A table name must include at least one letter. Either the first character of the table name or the first character after initial punctuation characters must be a letter. A character is a valid letter if it passes the $ZNAME test; $ZNAME letter validation differs for different locales. (Note that $ZNAME cannot be used validate SQL identifiers because an identifier can contain punctuation characters.)

    • _ (underscore), @, #, $ characters: If a table name contains any of these characters, these characters are stripped from the corresponding class name and a unique persistent class name is generated. Because generated class names do not include punctuation characters, it is not advisable to create table names that differ only in their punctuation characters.

  • A table name must be unique within its schema. Attempting to create a table with a name that differs only in letter case from an existing table generates an SQLCODE -201 error.

    A view and a table in the same schema cannot have the same name. Attempting to do so results in an SQLCODE -201 error.

    You can determine if a table name already exists using the $SYSTEM.SQL.Schema.TableExists()Opens in a new tab method. You can determine if a view name already exists using the $SYSTEM.SQL.Schema.ViewExists()Opens in a new tab method. These methods also return the class name corresponding to the table or view name. The Management Portal SQL interface Catalog Details Table Info option displays the Class Name corresponding to the selected SQL table name.

Attempting to specify “USER” or any other SQL reserved word as a table name or schema name results in an SQLCODE -312 error. To specify an SQL reserved word as a table name or schema name, you can specify the name as a delimited identifier. If you use a delimited identifier to specify a table or schema name that contains non-alphanumeric characters, InterSystems IRIS strips out these non-alphanumeric characters when generating the corresponding class or package name.

The following table name length limits apply:

  • Uniqueness: InterSystems IRIS performs uniqueness checking on the first 189 characters of the persistent class name. The corresponding SQL table name may be more than 189 characters long, but, when stripped of non-alphanumeric characters, it must be unique within this 189 character limit. InterSystems IRIS performs uniqueness checking on the first 189 characters of a package name.

  • Recommended maximum length: as a general rule, a table name should not exceed 128 characters. A table name may be much longer than 96 characters, but table names that differ in their first 96 alphanumeric characters are much easier to work with.

  • Combined maximum length: a package name and its persistent class name (when added together) cannot exceed 220 characters. This includes the default schema (package) name (if no schema name was specified) and the dot character separating the package name and class name. A combined schema and table name can be longer than 220 characters when the characters in excess of 220 are stripped out when the table name is converted to the corresponding persistent class name.

For further details on table names, refer to the CREATE TABLE command. For further details, see Classes.

RowID Field

In SQL, every record is identified by a unique integer value, known as the RowID. In InterSystems SQL you do not need to specify a RowID field. When you create a table and specify the desired data fields, a RowID field is automatically created. This RowID is used internally, but is not mapped to a class property. By default, its existence is only visible when a persistent class is projected to an SQL table. In this projected table, an additional RowID field appears. By default, this field is named "ID" and is assigned to column 1.

By default, when a table is populated with data, InterSystems IRIS assigns sequential positive integers to this field, starting with 1. The RowID data type is BIGINT (%Library.BigIntOpens in a new tab). The values generated for the RowID have the following constraints: Each value is unique. The NULL value is not permitted. Collation is EXACT. By default, values are not modifiable.

By default, InterSystems IRIS names this field “ID”. However this field name is not reserved. The RowID field name is re-established each time the table is compiled. If the user defines a field named “ID”, when the table is compiled InterSystems IRIS names the RowID as “ID1”. If, for example, the user then uses ALTER TABLE to define a field named “ID1”, the table compile renames the RowID as “ID2”, and so forth. In a persistent class definition you can use the SqlRowIdName class keyword to directly specify the RowID field name for the table to which this class is projected. For these reasons, referencing the RowID field by name should be avoided.

InterSystems SQL provides the %ID pseudo-column name (alias) which always returns the RowID value, regardless of the field name assigned to the RowID. (InterSystems TSQL provides the $IDENTITY pseudo-column name, which does the same thing.)

ALTER TABLE cannot modify or delete the RowID field definition.

When records are inserted into the table, InterSystems IRIS assigns each record an integer ID value. RowID values always increment. They are not reused. Therefore, if records have been inserted and deleted, the RowID values will be in ascending numeric sequence, but may not be numerically contiguous.

  • By default, a table defined using CREATE TABLE performs ID assignment using $SEQUENCE, allowing for the rapid simultaneous populating of the table by multiple processes. When $SEQUENCE is used to populate the table, a sequence of RowID values is allocated to a process, which then assigns them sequentially. Because concurrent processes are assigning RowIDs using their own allocated sequences, records inserted by more than one process cannot be assumed to be in the order of insert.

    You can configure InterSystems IRIS to perform ID assignment using $INCREMENT by setting the SetOption()Opens in a new tab method DDLUseSequence option; to determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method.

  • By default, a table defined by creating a persistent class performs ID assignment using $INCREMENT. In a persistent class definition, the IdFunction storage keyword can be set to either sequence or increment; for example, <IdFunction>sequence</IdFunction>.

In a persistent class definition, the IdLocation storage keyword global (for example, for persistent class Sample.Person: <IdLocation>^Sample.PersonD</IdLocation>) contains the highest assigned value of the RowID counter. (This is the highest integer assigned to a record, not the highest allocated to a process.) Note that this RowID counter value may no longer correspond to an existing record. To determine if record with a specific RowID value exists, invoke the table’s %ExistsId() method.

The RowID counter is reset by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table. If no data has been inserted into the table, or TRUNCATE TABLE has been used to delete all table data, the IdLocation storage keyword global value is undefined.

By default, RowID values are not user-modifiable. Attempting to modify a RowID value generates an SQLCODE -107 error. Overriding this default to allow modifying of RowID values can have serious consequences and should only be done in very specific cases and with extreme caution. The Config.SQL.AllowRowIDUpdateOpens in a new tab property allows RowID values to be user-modifiable.

RowID Based on Fields

By defining a persistent class that projects a table, you can define the RowID to have values from a field or a combination of fields. To do this, specify an index with the IdKey index keyword. For example, a table can have a RowID whose values are the same as the values of the PatientName field by specifying the index definition IdxId On PatientName [IdKey];, or the combined values of the PatientName and SSN fields by specifying the index definition IdxId On (PatientName,SSN) [IdKey];.

  • A RowID based on fields is less efficient than a RowId that takes system-assigns sequential positive integers.

  • On INSERT: The values specified for the field or combination of fields that make up the RowId must be unique. Specifying a non-unique value generates an SQLCODE -119 “UNIQUE or PRIMARY KEY constraint failed uniqueness check upon INSERT”.

  • On UPDATE: By default, the values of each of the fields that makes up the RowId are non-modifiable. Attempting to modify the value of one of these fields generates an SQLCODE -107 “Cannot UPDATE RowID or RowID based on fields”.

When a RowID is based on multiple fields, the RowID value is the values of each of its component fields joined by the || operator. For example, Ross,Betsy||123-45-6789. InterSystems IRIS attempts to determine the maximum length of a RowID that is based on multiple fields; if it cannot determine the maximum length, the RowID length defaults to 512.

For further details, refer to Primary Key.

RowID Hidden?

  • When using CREATE TABLE to create a table, the RowID is hidden by default. A hidden field is not displayed by SELECT * and is PRIVATE. When you create a table you can specify the %PUBLICROWID keyword to make the RowID not hidden and public. This optional %PUBLICROWID keyword can be specified anywhere in the CREATE TABLE comma-separated list of table elements. It cannot be specified in ALTER TABLE. For further details, refer to The RowID Field and %PUBLICROWID in the CREATE TABLE reference page.

  • When creating a persistent class that projects as a table, the RowID is not hidden by default. It is displayed by SELECT * and is PUBLIC. You can define a persistent class with a RowID that is hidden and PRIVATE by specifying the class keyword SqlRowIdPrivate.

A RowID used as a foreign key reference must be public.

By default, a table with a public RowID cannot be used as either source or destination table to copy data into a duplicate table using INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable.

You can display whether the RowID is hidden using the Management Portal SQL interface Catalog Details Fields listing Hidden column.

You can use the following program to return whether a specified field (in this example, ID) is hidden:

  SET myquery = "SELECT FIELD_NAME,HIDDEN FROM %Library.SQLCatalog_SQLFields(?) WHERE FIELD_NAME='ID'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data" 

To list the field names (hidden and non-hidden) in a table, refer to “Column Names and Numbers”.

Primary Key

InterSystems IRIS provides two ways to uniquely identify a row in a table: the RowID and the Primary Key.

The optional primary key is a meaningful value an application can use to uniquely identify a row in the table (for example in joins). A primary key can be user-specified data field or can be a combination of more than one data fields. Primary key values must be unique, but are not required to be integer values. The RowID is an integer value used internally to identify a row in the table. Often the primary key is a value generated by the application, while the RowID is a unique integer value generated by InterSystems IRIS.

The system automatically creates a Master Map to access rows of data using the RowID field. If you define a primary key field, the system automatically creates and maintains a primary key index.

Obviously, the duality of having two different fields and indexes to identify rows might not necessarily be a good thing. You can resolve to a single row identifier and index in either of two ways:

  • Use the application-generated primary key value as the IDKEY. You can do this by identifying the primary key index in the class definition with both keywords PrimaryKey and IdKey (you can also do that from DDL if you set the PKey is IDKey flag for this purpose). This makes the primary key index the table's Master Map. Thus, the primary key will be used as the main internal address for the rows. This can be less efficient if the primary key consists of more than one field, or if the primary key values are not integers.

  • Do not use an application-generated primary key value, but instead use the system-generated RowID integer within the application as the application-used primary key (for example in joins). The advantage of doing this is that the integer RowID lends itself to more efficient processing, including use of bitmap indexes.

Depending on the nature of the application, you may wish to resolve to a single row identifier and index or to have separate indexes for the application-generated primary key and the system-generated RowID.

RowVersion, AutoIncrement, and Serial Counter Fields

InterSystems SQL supports three special-purpose data types for automatically-incrementing counter values. All three data types are subclasses that extend the %Library.BigIntOpens in a new tab data type class.

  • %Library.RowVersionOpens in a new tab: Counts inserts and updates to all RowVersion tables namespace-wide. Only inserts and updates in tables that contain a ROWVERSION field increment this counter. ROWVERSION values are unique and non-modifiable. This namespace-wide counter never resets. For further details, see ROWVERSION Field .

  • %Library.CounterOpens in a new tab (also known as a SERIAL counter field): Counts inserts to the table. By default, this field receives an automatically incremented integer. However, a user can specify a non-zero integer value to this field. A user can specify a duplicate value. If the user-supplied value is greater than the highest system-supplied value, the automatic increment counter is set to increment from the user-specified value. For further details, see Serial Counter Field.

  • %Library.AutoIncrementOpens in a new tab: Counts inserts to the table. By default, this field receives an automatically incremented integer. However, a user can specify a non-zero integer value to this field. A user can specify a duplicate value. Specifying a user value has no effect on the automatic increment counter. For further details, see AutoIncrement Field.

All three of these fields, and the IDENTITY field, return AUTO_INCREMENT = YES, as shown in the following example:

SELECT COLUMN_NAME,AUTO_INCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'

RowVersion Field

The RowVersion field is an optional user-defined field that provides row-level version control, allowing you to determine the order in which changes were made to the data in each row namespace-wide. InterSystems IRIS maintains a namespace-wide counter, and assigns a unique incremental positive integer to this field each time the row data is modified (insert, update, or %Save). Because this counter is namespace-wide, an operation on one table with a ROWVERSION field sets the increment point for the ROWVERSION counter that is used for all other tables with a ROWVERSION field in the same namespace.

You create a RowVersion field by specifying a field of data type ROWVERSION (%Library.RowVersionOpens in a new tab). You can only specify one ROWVERSION data type field per table. Attempting to create a table with more than one ROWVERSION field results in a 5320 compilation error.

This field can have any name and can appear in any column position. The ROWVERSION (%Library.RowVersionOpens in a new tab) data type maps to BIGINT (%Library.BigIntOpens in a new tab).

This field receives a positive integer from an automatic increment counter, starting with 1. This counter increments whenever data in any ROWVERSION-enabled table is modified by an insert, update, or %Save operation. The incremented value is recorded in the ROWVERSION field of the row that has been inserted or updated.

A namespace can contain tables with a RowVersion field and tables without this field. Only data changes to tables that have a RowVersion field increment the namespace-wide counter.

When a table is populated with data, InterSystems IRIS assigns sequential integers to this field for each inserted row. If you use ALTER TABLE to add a ROWVERSION field to a table that already contains data, this field is created as NULL for pre-existing fields. Any subsequent insert or update to the table assigns a sequential integer to the RowVersion field for that row. This field is read-only; attempting to modify a RowVersion value generates an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field. Therefore, a RowVersion field is defined as unique and non-modifiable, but not required or non-null.

RowVersion values always increment. They are not reused. Therefore, inserts and updates assign unique RowVersion values in temporal sequence. Delete operations remove numbers from this sequence. Therefore, RowVersion values may not be numerically contiguous.

This counter is never reset. Deleting all table data does not reset the RowVersion counter. Even dropping all tables in the namespace that contain a ROWVERSION field does not reset this counter.

The RowVersion field should not be included in a unique key or primary key. The RowVersion field cannot be part of an IDKey index.

A sharded table cannot include a RowVersion field.

The RowVersion field is not hidden (it is displayed by SELECT *).

This is shown in the following example of three tables in the same namespace.

  1. Create Table1 and Table3, each of which has a ROWVERSION field, and Table2 that does not have a ROWVERSION field.

  2. Insert ten rows into Table1. The ROWVERSION values of these rows are the next ten counter increments. Since the counter has not previously been used, they are 1 through 10.

  3. Insert ten rows into Table2. Because Table2 does not have a ROWVERSION field, the counter is not incremented.

  4. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (11 in this case).

  5. Insert ten rows into Table3. The ROWVERSION values of these rows are the next ten counter increments (12 through 21).

  6. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (22 in this case).

  7. Delete a row of Table1. The ROWVERSION counter is unchanged.

  8. Update a row of Table3. The ROWVERSION values for this row is changed to the next counter increment (23 in this case).

Serial Counter Field

You can use the SERIAL data type (%Library.CounterOpens in a new tab in a persistent class table definition) to specify one or more optional integer counter fields to record the order of inserts of records into a table. Each serial counter field maintains its own independent counter.

A serial counter field receives a positive integer from its automatic increment counter whenever a row is inserted into the table with either no value supplied to this field (NULL) or a value of 0. However, a user can specify a non-zero integer value for this field during an insert, overriding the table counter default.

  • If an INSERT does not specify a non-zero integer value for the counter field, the counter field automatically receives a positive integer counter value. Counting starts from 1. Each successive value is an increment of 1 from the highest allocated counter value for this field.

  • If an INSERT specifies a non-zero integer value for the counter field, the field receives that value. It can be a positive or negative integer value, can be lower or higher than the current counter value, and can be an integer already assigned to this field. If this value is higher than any assigned counter value, it sets the increment starting point for the automatic increment counter to that value.

Attempting to UPDATE a counter field value results in an SQLCODE -105 error.

This counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.

A sharded table cannot include a serial counter field.

AutoIncrement Field

You can use the %Library.AutoIncrementOpens in a new tab data type (or BIGINT AUTO_INCREMENT) to specify an integer counter field to record the order of inserts of records into a table. You can only specify one %AutoIncrement data type field per table. This field receives a positive integer from an automatic increment counter whenever a row is inserted into the table with either no value supplied to this field (NULL) or a value of 0. However, a user can specify a non-zero integer value for this field during an insert, overriding the table counter default.

  • If an INSERT does not specify a non-zero integer value for the counter field, the counter field automatically receives a positive integer counter value. Counting starts from 1. Each successive value is an increment of 1 from the highest allocated counter value for this field.

  • If an INSERT specifies a non-zero integer value for the counter field, the field receives that value. It can be a positive or negative integer value, can be lower or higher than the current counter value, and can be an integer already assigned to this field. User-assigned values have no effect on the automatic increment counter.

Attempting to UPDATE a counter field value results in an SQLCODE -105 error.

This counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.

A sharded table can include an AutoIncrement field.

Defining a Table by Using DDL

You can define tables in InterSystems SQL using standard DDL commands:

Available DDL Commands in InterSystems SQL
ALTER Commands CREATE Commands DROP Commands

ALTER TABLE

ALTER VIEW

CREATE TABLE

CREATE VIEW

CREATE INDEX

CREATE TRIGGER

DROP TABLE

DROP VIEW

DROP INDEX

DROP TRIGGER

You can execute DDL commands in a variety of ways, including:

Using DDL in Embedded SQL

Within an ObjectScript method or routine, you can use Embedded SQL to invoke DDL commands.

For example, the following method creates a Sample.Employee table:

ClassMethod CreateTable() As %String
{
 &sql(CREATE TABLE Sample.Employee (
    EMPNUM              INT NOT NULL,
    NAMELAST            CHAR (30) NOT NULL,
    NAMEFIRST           CHAR (30) NOT NULL,
    STARTDATE           TIMESTAMP,
    SALARY              MONEY,
    ACCRUEDVACATION     INT,
    ACCRUEDSICKLEAVE    INT,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)))
        
  IF SQLCODE=0 {WRITE "Table created"  RETURN "Success"}
  ELSEIF SQLCODE=-201 {WRITE "Table already exists"  RETURN SQLCODE}
  ELSE {WRITE "Serious SQL Error, returning SQLCODE"  RETURN SQLCODE_" "_%msg}
 }

When this method is invoked it attempts to create a Sample.Employee table (as well as the corresponding Sample.Employee class). If successful, the SQLCODE variable is set to 0. If unsuccessful, SQLCODE contains an SQL Error Code indicating the reason for the failure.

The most common reasons that a DDL command such as this one will fail are:

  • SQLCODE -99 (Privilege Violation): This error indicates that you do not have permission to execute the desired DDL command. Typically this is because an application has not established who the current user is. You can do this programmatically using the $SYSTEM.Security.Login()Opens in a new tab method:

     DO $SYSTEM.Security.Login(username,password)
  • SQLCODE -201 (Table or view name not unique): This error indicates that you are attempting to create a new table using the name of a table that already exists.

Using a Class Method to Execute DDL

Within ObjectScript you can use the Dynamic SQL %SQL.StatementOpens in a new tab object to prepare and execute DDL commands using Dynamic SQL.

The following example defines a class method to create a table using Dynamic SQL:

  Class Sample.NewT
  {
  ClassMethod DefTable(user As %String,pwd As %String) As %Status [Language=objectscript]
    {
    DO ##class(%SYSTEM.Security).Login(user,pwd)
    SET myddl=2
    SET myddl(1)="CREATE TABLE Sample.MyTest "
    SET myddl(2)="(NAME VARCHAR(30) NOT NULL,SSN VARCHAR(15) NOT NULL)"
    SET tStatement=##class(%SQL.Statement).%New()
    SET tStatus=tStatement.%Prepare(.myddl)
      IF tStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset=tStatement.%Execute()
    IF rset.%SQLCODE=0 {WRITE "Created a table"}
    ELSEIF rset.%SQLCODE=-201 {WRITE "table already exists"}
    ELSE {WRITE "Unexpected error SQLCODE=",rset.%SQLCODE}
    }
  }

This method is invoked as follows:

  DO ##class(Sample.NewT).DefTable("myname","mypassword")

As with the embedded SQL example, this method will fail if there is no current user logged in.

Defining Tables by Importing and Executing a DDL Script

You can import InterSystems SQL DDL script files using either the $SYSTEM.SQL.Schema.Run()Opens in a new tab method interactively from a Terminal session, or the $SYSTEM.SQL.Schema.ImportDDL("IRIS")Opens in a new tab method as a background job. This method can import and execute multiple SQL commands, enabling you to use a txt script file to define tables and views and populate them with data. For further details, see Importing SQL Code.

If you are migrating tables from another vendor’s relational database to InterSystems IRIS, you may have one or more DDL scripts within text files. InterSystems IRIS provides several %SYSTEM.SQL.SchemaOpens in a new tab methods to help load such tables into InterSystems IRIS. You can use the general-purpose ImportDDL()Opens in a new tab method or the %SYSTEM.SQL.SchemaOpens in a new tab Load method for the specific vendor. The vendor-specific SQL is converted to InterSystems SQL and executed. Errors and unsupported features are recorded in log files. For further details, see Code Migration: Importing non-InterSystems SQL.

For example, to load an Oracle DDL file from the ObjectScript command line:

  1. Start a Terminal session using the Terminal command in the InterSystems IRIS launcher menu.

  2. Switch to the namespace in which you wish to load the table definitions:

     SET $namespace = "MYNAMESPACE"
  3. Invoke the desired DDL import method:

     DO $SYSTEM.SQL.Schema.LoadOracle()

    and follow the directions displayed at the terminal.

Defining a Table by Creating a Persistent Class

Although the standard way to define SQL tables is through using the CREATE TABLE DDL command, either from a SQL prompt or over a JDBC or ODBC connection, you can also create a persistent class table definition through an IDE such as VS Code or Studio. A class must either be defined as %Persistent or inherit from a superclass that is defined as %Persistent. When these classes are saved and compiled within the InterSystems IRIS database, they automatically project to a relational table that corresponds to the class definition: each class represents a table; each property represents a column, and so on. The maximum number of properties (columns) definable for a class (table) is 1000.

For example, the following defines the persistent class MyApp.Person:

Class MyApp.Person Extends %Persistent 
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);

Index BitmapExtent [ Extent, Type = bitmap ];
}

The class has these characteristics:

  • When compiled, this definition creates the MyApp.Person persistent class and the corresponding SQL table, Person within the MyApp schema. For details on how to perform these operations, see Defining Classes.

  • The class definition includes a package name, MyApp. When defining a persistent class, an unspecified package name defaults to User. This name corresponds to the default SQL schema name, SQLUser. For example, defining a table named Students as a persistent class creates the class User.Students, and the corresponding SQL schema.table name SQLUser.Students.

  • The persistent class name, Person, is used as the SQL table name. To supply a different SQL table name, you can use the SqlTableName class keyword.

  • The USEEXTENTSET class parameter is defined and set to 1. This parameter organizes table storage into a more efficient set of globals. As a best practice, specify the USEEXTENTSET parameter for all persistent classes that project to SQL tables.

  • The bitmap extent index creates an index of all IDs in the extent set. This settings makes counting and other operations more efficient. As a best practice, create a bitmap extent index for all persistent classes that project to SQL tables.

The same MyApp.Person table could have been defined using the DDL CREATE TABLE statement, specifying the SQL schema.table name. Successful execution of this SQL statement generates a corresponding persistent class with package name MyApp and class name Person:

CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    SSN VARCHAR(15) DEFAULT 'Unknown',
    DateOfBirth DATE,
    Sex VARCHAR(1)
)

When defining a table using DDL commands, you do not need to specify USEEXTENTSET or create a bitmap extent index. InterSystems SQL applies these settings automatically and includes them in the projected persistent class.

By default, CREATE TABLE specifies the Final class keyword in the corresponding class definition, indicating that it cannot have subclasses.

To learn how the object view of the database corresponds to the relational view, see Introduction to the Default SQL Projection.

A persistent class definition such as the one shown above creates the corresponding table when it is compiled, but this table definition cannot be modified or deleted using SQL DDL commands (or by using the Management Portal Drop action), which give you the message “DDL not enabled for class 'schema.name'...”). You must specify [DdlAllowed] in the table class definition to permit these operations:

Class MyApp.Person Extends %Persistent [DdlAllowed]

Defining Property Parameters

When defining a table in a persistent class, the properties you define project to columns of the table. Every property definition must specify a data type class, which specifies the class that the property is based on. A specified data type limits a property’s allowed values to that data type. When defining a persistent class that projects to a table you must specify this data type using a class in the %Library package. This class can be specified as either %Library.Datatype or as %Datatype.

Many data type classes provide parameters that allow you to further define allowed property values. These parameters are specific to individual data types. The following are some of the more common data definition parameters:

When data is inserted or updated in a field, InterSystems SQL automatically validates the data, enforcing data type and referential integrity constraints. If you use other means to populate a table with data, you should validate the table data.

Property Value Limits

For numeric data types, you can specify MAXVAL and MINVAL parameters to limit the range of allowed values. By definition, a numeric data type has a maximum supported values (positive and negative). You can use MAXVAL and MINVAL to further limit the allowed range.

For string data types, you can specify a MAXLEN and MINLEN parameters to limit the allowed length (in characters). By definition, a string data type has a maximum supported length. You can use MAXLEN and MINLEN to further limit the allowed range. The length limits imposed by the MAXLEN and MINLEN parameters are applied only when data is being stored in the database, as queries assume all data in the database is valid. Specifically, the limits are enforced when using INSERT or UPDATE (or %Save()Opens in a new tab in ObjectScript) to add or edit data in the database. By default, a property value that exceeds MAXLEN generates a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE. You can specify TRUNCATE=1 to permit string values that exceed MAXLEN; the specified string is truncated to the MAXLEN length. The default maximum length of a string is 4096; this limit can be configured with the ODBCVarcharMaxlen field in the CPF.

Permitted Property Values

You can limit the actual property values in two ways:

  • A list of allowed values (Enumerated Values with VALUELIST and DISPLAYLIST).

  • A match pattern for allowed values (PATTERN).

Enumerated Property Values

Defining a table as a persistent class allows you to define properties (columns) that can only contain certain specified values. This is done by specifying the VALUELIST parameter. VALUELIST (which specifies a list of logical storage values) is commonly used with DISPLAYLIST (which specifies a list of corresponding display values). Both lists begin with the list delimiter character. Several data types can specify VALUELIST and DISPLAYLIST. The following example defines two properties with enumerated values:

Class Sample.Students Extends %Persistent 
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property ChoiceStr As %String(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");
Property ChoiceODBCStr As %EnumString(VALUELIST=",0,1,2",DISPLAYLIST=",NO,YES,MAYBE");

Index BitmapExtent [ Extent, Type = bitmap ];
}

If VALUELIST is specified, an INSERT or UPDATE can only specify one of the values listed in VALUELIST, or be provided with no value (NULL). VALUELIST valid values are case-sensitive. For required properties, specifying a value that doesn’t match the VALUELIST values results in a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE. For non-required properties, non-matching VALUELIST values are converted to NULL values.

The %String and the %EnumString data types behave differently when displayed in ODBC mode. Using the example above, when displayed in Logical mode, both ChoiceStr and ChoiceODBCStr display their VALUELIST values. When displayed in Display mode, both ChoiceStr and ChoiceODBCStr display their DISPLAYLIST values. When displayed in ODBC mode, ChoiceStr displays VALUELIST values; ChoiceODBCStr displays DISPLAYLIST values.

Pattern Matching for Property Values

Several data types can specify a PATTERN parameter. PATTERN restricts allowed values to those that match the specified ObjectScript pattern, specified as a quoted string with the leading question mark omitted. The following example defines a property with a pattern:

Class Sample.Students Extends %Persistent 
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Telephone As %String(PATTERN = "3N1""-""3N1""-""4N");

Index BitmapExtent [ Extent, Type = bitmap ];
}

Because a pattern is specified as a quoted string, literals specified in the pattern need to have their enclosing quotes doubled. Note that pattern matching is applied before MAXLEN and TRUNCATE. Therefore, if you are specifying a pattern for a string that may exceed MAXLEN and be truncated, you may wish to end the pattern with “.E” (an unlimited number of trailing characters of any type).

A value that does not match PATTERN generates a validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE.

Unique Property Values

CREATE TABLE allows you to define a column as UNIQUE. This means that every field value is a unique (non-duplicate) value.

Defining a table as a persistent class does not support a corresponding uniqueness property keyword. Instead, you must define both the property and a unique index on that property. The following example provides for a unique Num value for each record:

  Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
  { 
  Parameter USEEXTENTSET = 1;

  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);

  Index UniqueNumIdx On Num [ Type=index,Unique ];
  Index BitmapExtent [ Extent, Type = bitmap ];
  }

The index name follows the naming conventions for properties. The optional Type keyword specifies the index type. The Unique keyword defines the property (column) as unique.

Having a unique value column is necessary for using the INSERT OR UPDATE statement.

Computed Property Values

The following class definition example defines a table containing a column (Birthday) that uses SqlComputed to compute its value when you initially set the DateOfBirth value and SqlComputeOnChange to recompute its value when you update the DateOfBirth value. The Birthday value includes the current timestamp to record when this value was computed/recomputed:

Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  Parameter USEEXTENTSET = 1;

  Property Name As %String(MAXLEN=50) [Required];
  Property DateOfBirth As %Date;
  Property Birthday As %String 
          [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DateOfBirth},9),",")_
                              " changed: "_$ZTIMESTAMP},
                              SqlComputed, SqlComputeOnChange = DateOfBirth ];

  Index BitmapExtent [ Extent, Type = bitmap ];
}

Note that an UPDATE to DateOfBirth that specifies the existing DateOfBirth value does not recompute the Birthday value.

The SqlComputeCode property keyword contains the ObjectScript code used to compute the value. Alternatively, you can specify the compute code in a PropertyComputation method, where Property is the name of the property you want to compute. This method enables you to specify the compute code in languages other than ObjectScript, such as Python.

In this class, the AgeComputation method computes the Age property based on the DOB (date of birth) property. The cols input argument is a %Library.PropertyHelperOpens in a new tab object. You can use the getfield method of this object to reference other properties.

Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];

Index BitmapExtent [ Extent, Type = bitmap ];

ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer
{
    set today = $zdate($horolog,8) 
    set bdate = $zdate(cols.getfield("DOB"), 8)
    return $select(bdate = "":"", 1:(today - bdate) \ 10000)
}

}
Class Sample.MyStudents Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;

Property Name As %String(MAXLEN = 50) [ Required ];
Property DOB As %Date;
Property Age As %Integer [ Calculated, SqlComputed, SqlComputeOnChange = DOB ];

Index BitmapExtent [ Extent, Type = bitmap ];

ClassMethod AgeComputation(cols As %Library.PropertyHelper) As %Integer [ Language = python ]
{
    import datetime as d
    iris_date_offset = d.date(1840,12,31).toordinal()
    bdate = d.date.fromordinal(cols.getfield("DOB") + iris_date_offset).strftime("%Y%m%d")
    today = d.date.today().strftime("%Y%m%d")
    return str((int(today) - int(bdate)) // 10000) if bdate else ""
}

}

If ComputeLocalOnly is specified in addition to SqlComputed and SqlComputeCode, then the compute code is only executed when the data is stored on the same server that the query is issued from. This option is used primarily in sharded environments to return only locally-stored computed data.

For more details on specifying compute code, see Computed Columns.

For more details on class property keywords, see Property Keywords.

Embedded Object (%SerialObject)

You can simplify the structure of a persistent table by referencing an embedded serial object class that defines properties. For example, you want the MyData.Person to contain address information, consisting of street, city, state, and postal code. Rather than specifying these properties in MyData.Person, you can define a serial object (%SerialObject) class that defines these properties, and then in MyData.Person specify a single Home property that references that embedded object. This is shown in the following class definitions:

Class MyData.Person Extends (%Persistent) [ DdlAllowed ]
{  Parameter USEEXTENTSET = 1;
   Property Name As %String(MAXLEN=50);
   Property Home As MyData.Address;
   Property Age As %Integer;
   Index BitmapExtent [ Extent, Type = bitmap ];
}
Class MyData.Address Extends (%SerialObject)
{  Property Street As %String;
   Property City As %String;
   Property State As %String;
   Property PostalCode As %String;
}

You cannot access the data in a serial object property directly, you must access them through a persistent class/table that references it:

  • To refer to an individual serial object property from the persistent table, use an underscore. For example, SELECT Name, Home_State FROM MyData.Person returns the State serial object property value as a string. Serial object property values are returned in the order specified in the query.

  • To refer to all of the serial object properties from the persistent table, specify the referencing field. For example, SELECT Home FROM MyData.Person returns values of all of the MyData.Address properties as a %List structure. Serial object property values are returned in the order specified in the serial object: Home_Street, Home_City, Home_State, Home_PostalCode. In the Management Portal SQL interface Catalog Details, this referencing field is referred to as a Container field. It is a Hidden field, and therefore not returned by SELECT * syntax.

  • A SELECT * for a persistent class returns all of the serial object properties individually, including nested serial objects. For example, SELECT * FROM MyData.Person returns Age, Name, Home_City, Home_PostalCode, Home_State, and Home_Street values (in that order); it does not return the Home %List structure value. Serial object property values are returned in collation sequence. SELECT * first lists all of the fields in the persistent class in collation sequence (commonly alphabetical order), followed by the nested serial object properties in collation sequence.

Note that an embedded serial object does not have to be in the same package as the persistent table that references it. The SqlCategory for %Library.SerialObject (and all sub-classes of %SerialObject that do not define the SqlCategory explicitly) is STRING.

Defining embedded objects can simplify persistent table definitions:

  • A persistent table can contain multiple properties that reference different records in the same embedded object. For example, the MyData.Person table can contain a Home and an Office property, both of which reference the MyData.Address serial object class.

  • Multiple persistent tables can reference instances of the same embedded object. For example, the MyData.Person table Home property and the MyData.Employee WorkPlace property can both reference the MyData.Address serial object class.

  • An embedded object can reference another embedded object. For example, the MyData.Address embedded object contains the Phone property that references the MyData.Telephone embedded object, containing CountryCode, AreaCode, and PhoneNum properties. From the persistent class you use multiple underscores to refer to a nested serial object property, for example Home_Phone_AreaCode.

Compiling a serial object class generate a data specification in the storage definition. The compiler assigns this specification a data name by appending the word “State” to the serial object class name. Therefore, MyData.Address is assigned <Data name="AddressState">. If this name (AddressState in this example) is already used as a property name, the compiler appends an integer to create a unique data name: <Data name="AddressState1">.

Refer to Introduction to Serial Objects.

For information on creating an index for a serial object property, refer to Indexing an Embedded Object (%SerialObject) Property.

Class Methods

You can specify class methods as part of a table definition, as shown in the following example:

Class MyApp.Person Extends %Persistent 
{
Parameter USEEXTENTSET = 1;
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
Index BitmapExtent [ Extent, Type = bitmap ];
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}

The SqlProc keyword is required to project the class method to a SQL procedure. In a SELECT query, you can call this method by using the defined SqlName value you defined for the method. For example:

SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person

Defining a Sharded Table by Creating a Persistent Class

Before you can define a persistent class that projects as a sharded table, you must establish a sharding environment. Then, to define a sharded persistent class, specify the class keyword Sharded=1, along with any optional shard-related class attributes.

Caution:

Define sharded tables only for new persistent classes that contain no data. Applying sharding to an existing class can make your data inaccessible.

This class defines a sample sharded persistent class with optional shard-related class attributes set:

Class Sample.MyShardT Extends %Persistent [ Sharded = 1 ]
{
    Parameter DEFAULTCONCURRENCY As BOOLEAN = 0;
    Parameter USEEXTENTSET = 1;
    Index BitmapExtent [ Extent, Type = bitmap ];
}

The following class attributes apply:

  • The Sharded = 1 keyword defines the projected table for this class as sharded. With this setting, the sharding infrastructure manages the sharded table storage, including data distribution. Therefore, you cannot customize the default storage definition generated and maintained by InterSystems IRIS. Any changes made to the storage definition in this class definition are ignored.

  • The DEFAULTCONCONCURRENCY class parameter is set to 0 (no locking). Due to the distributed nature of sharded tables, the parameter value of 0 is required. This value is used as the default in object methods such as %Open and %OpenId, so that you do not have to pass the concurrency argument to every method call.

  • The USEEXTENTSET class parameter is set to 1, which organizes table storage into a more efficient set of globals. When defining a sharded table using DDL commands, InterSystems SQL applies this setting automatically.

  • The bitmap extent index creates an index of all IDs in the extent set. This settings makes counting and other operations more efficient. When defining a sharded table using DDL commands, InterSystems SQL applies this setting automatically.

You can then define the shard key index. When you create a sharded table, an abstract shard key index is generated automatically. The shard key index determines the shard in which a row resides. For more details on defining a shard key index and creating sharded tables, see Creating Sharded Tables and Loading Data.

Sharded Class Restrictions

  • Class parameters that are not supported for sharded classes: CONNECTION, DEFAULTGLOBAL, DSINTERVAL, DSTIME, IDENTIFIEDBY, OBJJOURNAL.

  • Class keywords that are not supported for sharded classes: Language, ViewQuery.

  • Super classes that are not supported for sharded classes: %Library.IndexBuilder, %DocDB.Document.

  • Property data types not supported for sharded classes: %Library.Text.

  • Relationship properties are not supported for sharded classes.

  • Projections are not supported for sharded classes.

  • Any methods with a Language other than "objectscript" are not supported for sharded classes.

  • Any class queries not of Type %SQLQuery are not supported for sharded classes.

An attempt to compile a sharded class using any of these features results in a compile-time error.

Defining a Sharded Table

There are three requirements for creating a sharded table.

  1. The License Key must support sharding. Use the Management Portal, System Administration, Licensing, License Key to display the current licence or activate a new license.

  2. You must enable sharding on your InterSystems IRIS instance. You must have the %Admin_Secure privilege to enable sharding. Use the Management Portal, System Administration, Configuration, System Configuration, Sharding Configuration to select the Enable Sharding button. This enables the current InterSystems IRIS instance for use in a sharded cluster. Select either Enable this instance for any role or Enable this instance for the shard master role only. Press OK. Restart your InterSystems IRIS instance.

  3. You must deploy a sharded cluster on your InterSystems IRIS instance. This sharded cluster contains a Shard Master namespace. If the current namespace is not configured for sharding, attempting to define a shard table fails with ERROR #9319: Current namespace %1 has no shards configured. For details, refer to Deploy the Cluster Using the API or the Management Portal.

You can then define a sharded table within the Shard Master namespace that has been defined as part of the sharded cluster. You can use CREATE TABLE to define a sharded table by specifying a shard key. Alternately, you can create a persistent class that projects to a sharded table.

For further details about defining a sharded table, refer to Create Target Sharded Tables.

Defining a Table by Querying an Existing Table

You can define and populate a new table based on an existing table (or tables or views). You specify a query and a new table name. The existing table name and/or the new table name can be qualified or unqualified. The query can contain JOIN syntax. The query can supply column name aliases that become the column names in the new table.

You can perform this operation using either the CREATE TABLE AS SELECT command or the $SYSTEM.SQL.Schema.QueryToTable()Opens in a new tab method, as described below:

  1. QueryToTable() copies the DDL definition of a existing table and assigns it the specified new table name. It copies the definitions of the fields specified in the query, including the data type, maxlength, and minval/maxval. It does not copy field data constraints, such as default value, required value, or unique value. It does not copy references from a field to another table.

    If the query specifies SELECT * or SELECT %ID, the RowID field of the original table is copied as a non-required, non-unique data field of data type integer. QueryToTable() generates a unique RowID field for the new table. If the copied RowID is named ID, the generated RowID is named ID1.

    QueryToTable() creates a corresponding persistent class for this new table. The persistent class is defined as DdlAllowed. The owner of the new table is the current user.

    The new table is defined with Default Storage = YES and Supports Bitmap Indices = YES, regardless of these settings in the source table.

    The only index created for the new table is the IDKEY index. No bitmap extent index is generated. Index definitions for the copied fields are not copied into the new table.

  2. QueryToTable() then populates the new table with data from the fields selected by the query. It sets the table’s Extent Size to 100,000. It estimates the IDKEY Block Count. Run Tune Table to set the actual Extent Size and Block Count, and the Selectivity and Average Field Size values for each field.

QueryToTable() both creates a table definition and populates the new table with data. If you wish to only create a table definition, specify a condition in the query WHERE clause that selects for no data rows. For example, WHERE Age < 20 AND Age > 20.

The following example copies the Name, and Age, fields from Sample.Person and creates an AVG(Age) field. These field definitions are used to create a new table named Sample.Youth. The method then Populates Sample.Youth with the Sample.Person data for those records where Age < 21. The AvgInit field contains the aggregate value for the selected records at the time that the table was created.

  DO $SYSTEM.SQL.Schema.QueryToTable("SELECT Name,Age,AVG(Age) AS AvgInit FROM Sample.Person WHERE Age < 21","Sample.Youth",1,.errors)

Foreign Tables

Important:

Foreign Tables are available in InterSystems IRIS 2023.3 as an Experimental Feature. This means they are not supported for production environments. However, the feature is well-tested and InterSystems believes it can add significant value to customers.

InterSystems is looking for feedback on this new capability based on customers’ use in real-world environments. Please reach out on the Developer Community or contact the Worldwide Response Center (WRC) if you would like to share your experiences or you have questions.

InterSystems SQL enables you to define a foreign table, which projects data from an external data source into an instance of InterSystems IRIS and allows you to query such external data alongside data stored within the instance.

Introduction to Foreign Tables

For various reasons, it may not be feasible or reasonable for you to load data directly into InterSystems IRIS. For example, perhaps a data file is extremely large and will not be queried often enough to justify the storage cost of loading it into an InterSystems IRIS table. A foreign table is a projection of data managed by another system that can be queried and accessed alongside data that is managed and stored within an instance of InterSystems IRIS.

Creating a Foreign Table

Prior to creating a foreign table, you must define a foreign server to determine how InterSystems IRIS will interact with the external data source. Once you have defined a foreign server, you can then define one or more foreign tables that represent data in the external source by specifying column names and types, as well as any other details required to map fields in the external data source to columns within InterSystems IRIS.

Step 1: Define a Foreign Server

Before you can define a foreign table, you must define a foreign server and specify which foreign data wrapper to use. To do so, you should use the CREATE FOREIGN SERVER command.

The CREATE FOREIGN SERVER command requires you to specify a foreign data wrapper. Foreign data wrappers determine how InterSystems IRIS interacts with a specific type of data source. Within a CREATE FOREIGN SERVER command, you will need to specify both a foreign data wrapper and the metadata that the foreign data wrapper requires. At present, the InterSystems SQL supports two foreign data wrappers: CSV and JDBC. The CSV foreign data wrapper requires you to specify a path to a folder in a local file system. The JDBC foreign data wrapper requires you to name a JDBC connection to connect with an external database.

There is no limit on the amount of foreign tables you can define on a foreign server.

The following example demonstrates how to create a foreign server that uses the CSV foreign data wrapper.

CREATE FOREIGN SERVER Sample.TestFile FOREIGN DATA WRAPPER CSV HOST '\path\to\file'

The following example demonstrates how to create a foreign server that uses the JDBC foreign data wrapper.

CREATE FOREIGN SERVER Sample.PostgresDB FOREIGN DATA WRAPPER JDBC CONNECTION 'postgresConnection'

Step 2: Define a Foreign Table

After defining a foreign server, you can use the CREATE FOREIGN TABLE command to define a foreign table. This table may have the same column names as the data from the external source, or you may choose to refer to the columns by new names within InterSystems IRIS. The syntax for creating a foreign table is similar to the LOAD DATA command.

CREATE FOREIGN TABLE Sample.AccountTeam (
   TeamID BIGINT,
   Name VARCHAR(50),
   CountryCode VARCHAR(10)
) SERVER Sample.PostgresDB TABLE 'Sample.Teams'

Creating a foreign table through a data definition language statement will create a corresponding class, which has a ClassType of “view”. You should not manually edit this class; furthermore, you must define a foreign table with the CREATE FOREIGN TABLE command and cannot create one by creating a class definition.

Querying a Foreign Table

Querying a foreign table is exactly like querying a native table:

SELECT Name, CountryCode FROM Sample.AccountTeam ORDER BY Name

Your queries can also take advantage of more advanced syntaxes:

SELECT t.Name, COUNT(m.*)
FROM Sample.AccountManager m JOIN Sample.AccountTeam t
     ON m.TeamID = t.TeamID
WHERE t.CountryCode = 'UK' AND m.Salary > 100000
GROUP BY t.Name

Where possible, InterSystems SQL will send, or push down, simple predicates in a WHERE clause, limiting the amount of data transferred over the network and taking advantage of optimizations on the remote database. However, some more complicated clauses, such as a GROUP BY or a JOIN between two foreign tables, occur within InterSystems IRIS after the external data has been retrieved.

The user that issues the query must have the %Gateway_Object:USE privilege.

Note:

Issuing a query against a foreign table makes use of an underlying Java-based engire that requires a Java Virtual Machine (JVM) installation on your server. If you have a JVM set up and accessible in your PATH environment variable, then the first time you issue a query, InterSystems IRIS automatically uses that JVM to start an External Language Server. To customize your External Language Server to use a specific JVM, or to use a remote server, see Managing External Server Connections.

Deleting a Foreign Table

To delete a foreign table, you should use the DROP FOREIGN TABLE command:

DROP FOREIGN TABLE Example.MyForeignTable

Alternatively, you may use the DROP FOREIGN SERVER command with the CASCADE option to drop a foreign server and all the foreign tables defined on that foreign server:

DROP FOREIGN SERVER Example.PostgresDB CASCADE

Federated Tables

Important:

Federated tables are available in InterSystems IRIS 2023.3 as an Experimental Feature. This means they are not supported for production environments. However, the feature is well-tested and InterSystems believes it can add significant value to customers.

InterSystems is looking for feedback on this new capability based on customers’ use in real-world environments. Please reach out on the Developer Community or contact the Worldwide Response Center (WRC) if you would like to share your experiences or you have questions.

A federated table offers query access to data that lives in disparate instances of InterSystems IRIS® data platform hosting the identical or similar schemas. Unlike a sharded table, federated tables are read-only and do not manage or distribute data among the sources based on a shard key. The source tables for a federated table, which physically store the its data and live in the distinct instances or namespaces, are managed exclusively by their host instance. As such, federated tables are read only. Federated tables offer a solution to use cases that require analytics or querying across different deployments of the same application or schema, such as multi-tenant environments.

Requirements for Creating a Federated Table

To connect source tables together as a federated table, you must first configure a sharded cluster that spans all the instances containing the relevant source data. For more information about configuring a sharded cluster, see “Deploying the Sharded Cluster.” Users that are new to sharding may find it useful to familiarize themselves with the terminology and concepts in “Horizontally Scaling for Data Volume with Sharding.”

A cluster namespace must be created for each namespace that contains one or more source tables, called a source namespace. This requirement applies to both cases where each namespace resides on a separate instance and to cases where multiple source namespaces reside on the same instance; in the latter case, multiple cluster namespaces on the same instance need to be configured so that each source namespace has a corresponding cluster namespace.

The following image shows the relationships between federated tables, source tables, cluster namespaces, and source namespaces.

On three instances of a sharded cluster, source tables project into a federated table accessible through the cluster namespac

Creating a Federated Table

Once you have configured a sharded cluster, creating a federated table consists of two parts: defining the federated table and connecting source tables to the federated table.

Defining a Federated Table

From the master namespace of the sharded cluster, you can create a federated table by using $SYSTEM.Sharding.CreateFederatedTable(). See complete documentation on how to use this method in the Class Reference.

CreateFederatedTable() registers the federated table definition in the cluster namespace that represents the federated table and defines how columns from a source table are projected into the federated table. As part of the API call, columns from the source table may be left out of the federated table or dummy columns with a constant value may be added (for example, to identify the source namespace). This method also attaches the initial source table from the specified source namespace, which is stored on the same instance. A source namespace is never impacted by the existence of a federated table that any of its tables projects to.

There are a few notes about federated table definitions:

  • Private properties in a source table are not projected to a federated table unless explicitly listed as an argument to CreateFederatedTable().

  • The types of source fields do not need to match the types of the corresponding fields in the federated tables. Implicit ype conversions are identical to those used in UNION clauses and return a data type with the highest precedence as follows: VARCHAR, DOUBLE, NUMERIC, BIGINT, INTEGER, SMALLINT, TINYINT.

  • The SqlRowIdPrivate class keyword is determined by the source table and the ID is projected with the name from the source table. If this keyword is defined, the RowID is not projected to the federated table. Note that tables created via DDL specify SqlPrivateRowId by default.

The newly created federated table is immediately accessible from any cluster namespace in the sharded cluster.

At this point, the newly created federated table only projects data from the source table in the source namespace that was referred to in the call to CreateFederatedTable(). See Connecting Source Tables to a Federated Table below for information about including data from source table in other source namespaces.

Examples

The following example demonstrates a call to CreateFederatedTable() from the terminal that creates a federated table, Hospital.Employees, in the IRISCLUSTER namespace (the default name for a cluster namespace). This federated table uses Employees.Doctors from the USER namespace as an initial source table. The federated table’s columns are identical to those of the source table.

do $SYSTEM.Sharding.CreateFederatedTable(,"Hospital.Employees", "USER", "Employees.Doctors")

The following example creates a federated table, Hospital.DiagnosisLog, in the IRISCLUSTER cluster namespace. This federated table uses Hospital.Patient from the HOSPITAL namespace as an initial source table and projects the Diagnosis and DateAdmitted columns from the source table. In this case, the DateAdmitted column is projected as AdmissionDate to the federated table. The resulting federated table in this example has only two columns.

do $SYSTEM.Sharding.CreateFederatedTable("IRISCLUSTER","Hospital.DiagnosisLog", "HOSPITAL", "Hospital.Patient", $lb($lb("Diagnosis"), $lb("DateAdmitted","AdmissionDate")))

Connecting Source Tables to a Federated Table

After creating the federated table, connect source tables from other source namespaces to it. To do so, use the $SYSTEM.Sharding.ConnectFederatedTable() method in each source namespace. See complete documentation for this method in the Class Reference.

When a source table is connected to the federated table, any table statistics that have been collected by TUNE TABLE are reported to the federated table. For optimal query performance, you should run TUNE TABLE on a source table before connecting it to a federated table, so the table statistics are most accurate. If the source table needs to be re-tuned and you wish to propagate the updated statistics to the federated table, tune the source table, then re-connect it to the federated table, specifying 1 for the Force argument to ConnectFederatedTable().

If the source table is altered or dropped, the federated table definition is not automatically updated. This is a consequence of how federated tables treat the source namespace as read-only and cannot be triggered by any events in it. When a source table is altered, you will need to call ConnectFederatedTable() in the cluster namespace corresponding to the source namespace that has changed and specify the alterations, specifying 1 for the Force argument to ConnectFederatedTable(). For example, changing a column name in the source table requires you to re-specify the column names that are projected into the federated table. Note that this step is not required for any compatible changes, such as adding a column to the source table that does not need to be projected to the federated table.

When the source tables’ structure matches exactly between different source namespaces, calls to ConnectFederatedTable() are easy to script, as you can leverage the %SYSTEM.ShardWorkMgr.Broadcast() instance method to execute them once in each cluster namespace. See the Examples for further explanation.

Examples

The following example uses ConnectFederatedTable() to connect the Employees.Nurses source table in the USER namespace to the Hospital.Employees federated table in the IRISCLUSTER namespace.

do $SYSTEM.Sharding.CreateFederatedTable("IRISCLUSTER","Hospital.Employees","USER","Employees.Nurses")

The following example employs the %SYSTEM.ShardWorkMgr.Broadcast() instance method to connect source tables, called Employees.Nurses in the HOSPITAL namespace, to a newly created federated table, also called Employees.Nurses. Note that this approach requires each instance to have identically named source tables and source namespaces, due to the same arguments being broadcast across instances. The cluster namespace argument to ConnectFederatedTable() should be left blank.

zn "IRISCLUSTER"
set status=$SYSTEM.Sharding.CreateFederatedTable(,"Employees.Nurses","HOSPITAL","Employees.Nurses")
set shardManager=$SYSTEM.ShardWorkMgr.%New()
set status=shardManager.Broadcast("DS","##class(%SYSTEM.Sharding).ConnectFederatedTable",,"Employees.Nurses","HOSPITAL","Employees.Nurses")

Querying a Federated Table

Querying a federated table is transparently similar to querying a standard table. Federated tables can be queried with Dynamic SQL, Embedded SQL, or with a database driver, such as JDBC or ODBC.

Dropping or Disconnecting a Federated Table

To drop a federated table from your sharded cluster, use $SYSTEM.Sharding.DropFederatedTable(). This method, called from the cluster namespace of any instance, drops the federated table definition from all the cluster namespaces. Further attempts to query the federated table will fail, as it no longer exists. Dropping a federated table does not affect the source tables or their data.

To remove a single source table from a federated table, call $SYSTEM.Sharding.DisconnectFederatedTable() from the cluster namespace corresponding to the source namespace that contains the source table to be disconnected. This method disconnects a source table from the federated table. When a source table is disconnected from a federated table, queries on the federated table will no longer return data from that source table. The source table can later be re-connected to the federated table with ConnectFederatedTable().

Listing Tables

The INFORMATION.SCHEMA.TABLESOpens in a new tab persistent class displays information about all tables (and views) in the current namespace. It provides a number of properties including the schema and table names, the owner of the table, and whether you can insert new records. The TABLETYPE property indicates whether it is a base table or a view.

The following example returns the table type, schema name, table name, and owner for all tables and views in the current namespace:

SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES 

The INFORMATION.SCHEMA.CONSTRAINTTABLEUSAGEOpens in a new tab persistent class displays one row for each Primary Key (explicit or implicit), Foreign Key, or Unique constraint defined for each table in the current namespace. INFORMATION.SCHEMA.KEYCOLUMNUSAGEOpens in a new tab displays one row for each field defined as part of one of these constraints for each table in the current namespace.

You can display much of the same information for a single table using the Catalog Details tab in the Management Portal SQL Interface.

Listing Column Names and Numbers

You can list all of the column names (field names) for a specified table in four ways:

  • The GetAllColumns()Opens in a new tab method. This lists all column names and column numbers, including hidden columns. The GetVisibleColumns()Opens in a new tab method lists all non-hidden columns. The ID (RowID) field may or may not be hidden. The x__classname column is always hidden; it is automatically defined unless the persistent class is defined with the Final class keyword.

  • The Management Portal SQL interface (System Explorer, SQL) schema contents Catalog Details tab. This lists all column names and column numbers ( including hidden columns) and other information, including data types and a flag indicating if a column is hidden.

  • SELECT TOP 0 * FROM tablename. This lists all non-hidden column names in column number order. Note that because hidden columns can appear anywhere in the column number order, you cannot determine the column number by counting these non-hidden column names. For further details on Asterisk Syntax, refer to the SELECT command.

  • The INFORMATION.SCHEMA.COLUMNSOpens in a new tab persistent class lists a row for each non-hidden column in each table or view in the current namespace. INFORMATION.SCHEMA.COLUMNSOpens in a new tab provides a large number of properties for listing characteristics of table and view columns. Note that ORDINALPOSITION is not the same as column number, because hidden fields are not counted. The GetAllColumns() method counts both hidden and non-hidden fields.

    The following example uses INFORMATION.SCHEMA.COLUMNSOpens in a new tab to list some of the column properties:

    SELECT TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,
           COLUMN_DEFAULT,IS_NULLABLE,UNIQUE_COLUMN,PRIMARY_KEY 
           FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Sample'

The Get Columns Methods

To list the names of the columns in a table in column number order, you can use either the GetAllColumns()Opens in a new tab or GetVisibleColumns()Opens in a new tab method, as follows:

  SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname,.bynum)
  IF stat=1 {
    SET i=1
    WHILE $DATA(bynum(i)) { WRITE "name is ",bynum(i),"   col num is ",i,!
                            SET i=i+1 }
  }
  ELSE { WRITE "GetAllColumns() cannot locate specified table" }

GetAllColumns() lists all defined columns, including hidden columns. If a table references an embedded %SerialObject class, GetAllColumns() first lists all of the columns in the persistent class, including the property that references the %SerialObject, then lists all of the %SerialObject properties. This is shown in the following GetAllColumns() results:

name is ID   col num is 1
name is Age   col num is 2
name is Home   col num is 3
name is Name   col num is 4
name is x__classname   col num is 5
name is Home_City   col num is 6
name is Home_Phone   col num is 7
name is Home_Phone_AreaCode   col num is 8
name is Home_Phone_Country   col num is 9
name is Home_Phone_TNum   col num is 10
name is Home_PostalCode   col num is 11
name is Home_State   col num is 12
name is Home_Street   col num is 13

You can also use this method to determine the column number for a specified column name, as follows:

  SET stat=##class(%SYSTEM.SQL.Schema).GetAllColumns("Sample.Person",.byname)
  IF stat=1 {
         WRITE "Home_State is column number ",byname("Home_State"),!  }
  ELSE { WRITE "GetAllColumns() cannot locate specified table" }

Listing Constraints

The INFORMATION.SCHEMA.TABLECONSTRAINTSOpens in a new tab persistent class lists the table name, constraint type, and constraint name. Constraint types include UNIQUE, PRIMARY KEY, and FOREIGN KEY. If the table definition did not specify a name for a constraint, a constraint name is generated from the table name, constraint type, and table column number — for example, MYTABLE_UNIQUE3. This is shown in the following example:

SELECT Table_Schema,Table_Name,Constraint_Type,Constraint_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

The INFORMATION.SCHEMA.CONSTRAINTCOLUMNUSAGEOpens in a new tab persistent class lists the table name, column name, and constraint name. If a constraint involves multiple columns a separate item is listed for each column. If the table definition did not specify a name for a constraint, a constraint name is generated from the table name, constraint type, and table column number — for example, MYTABLE_UNIQUE3. This is shown in the following example:

SELECT Table_Schema,Table_Name,Column_Name,Constraint_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 

The INFORMATION.SCHEMA.REFERENTIALCONSTRAINTSOpens in a new tab persistent class lists foreign key constraints including the referencing table (CONSTRAINT_SCHEMA, CONSTRAINT_TABLE_NAME), the referenced table (UNIQUE_CONSTRAINT_SCHEMA, UNIQUE_CONSTRAINT_TABLE), the foreign key name (CONSTRAINT_NAME), and the UPDATE and DELETE referential actions (UPDATE_RULE, DELETE_RULE) with the values NO ACTION, SET DEFAULT, SET NULL, or CASCADE. This is shown in the following example:

SELECT Constraint_Table_Name,Unique_Constraint_Table,Constraint_Name,Update_Rule,Delete_Rule FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
FeedbackOpens in a new tab