SQL Data Definition Language (DDL)

This topic describes the characteristics of the Model 204 SQL Data Definition Language (DDL). DDL statements are SQL statements that are used to maintain the SQL catalog by creating and altering table, view, and column definitions that describe Model 204 file data. DDL also includes SQL GRANT and REVOKE statements that define the security associated with SQL tables, views, and columns.

Emphasis in this topic is on the characteristics of Model 204 SQL DDL that differ from the ANSI SQL 1989 standard DDL. Unless otherwise specified, references to "the standard" in this topic are to the ANSI SQL 1989 standard.

The description of the Model 204 SQL DDL is organized by function (creating, altering, dropping, and granting), including basic syntax for the statements that perform each function.

A diagram of the entire Model 204 SQL DDL statement syntax is found in SQL DDL syntax.

Model 204 SQL DDL statements

The following table identifies the DDL statements that can be processed against CCACAT, the SQL catalog file. Each statement is discussed in this topic.

The statements in the table affect only the SQL catalog file. Model 204 database files other than CCACAT are never affected by these statements.

The second column of the table explains the effect of each of the statements on the SQL catalog records. The catalog has the following record types:

• SCHEMA (S)
• TABLE
  • TABLE (T)
  • TABLE (V)
• PRIVILEGE (P)
• CONSTRAINT (C)

Note: TABLE has two subtypes: TABLE(T) for tables and TABLE(V) for views.

Effects of DDL statements

DDL statement	Effect on CCACAT
CREATE SCHEMA	Adds a SCHEMA record if none is already present.
CREATE TABLE	Adds a TABLE(T) record and possibly CONSTRAINT records for multicolumn unique indexes, if present. Adds PRIVILEGE records for the table owner.
CREATE VIEW	Adds a TABLE(V) record and adds PRIVILEGE records for the view owner.
GRANT	Adds or updates one or more PRIVILEGE records. If no column list, number of records is: no.-of-grantees * no.-of-privileges If a column list, number of records is: no.-of-grantees * (no.-of-privileges-without-lists + no.-of-columns)
SET SCHEMA	None.
SET USER	None.
DROP SCHEMA	Physically deletes SCHEMA record and all TABLE, VIEW, PRIVILEGE, and CONSTRAINT records for objects associated with this schema.
DROP TABLE	Physically deletes TABLE(T) record and all PRIVILEGE and CONSTRAINT records associated with this table. If a parent table, it cannot be dropped before nested tables associated with it are dropped. No effect on TABLE(V) records that reference this table: view definitions involving this table are left intact.
DROP VIEW	Physically deletes the TABLE(V) record and all PRIVILEGE records for the view.
ALTER TABLE     ADD     DROP          MODIFY	Cannot be used against views, so no effect on TABLE(V) records. Adds COLUMN occurrence group to existing TABLE(T) record. Deletes COLUMN occurrence group from existing TABLE(T) record. Deletes PRIVILEGE record(s) that reference this column. No effect on CONSTRAINT records because DROP is not allowed for columns that are part of multi-column unique constraints. Updates column occurrence group on existing TABLE(T) record. No effect on CONSTRAINT records, because MODIFY is not allowed for constrained columns.
REVOKE	Updates or deletes existing PRIVILEGE records.

Model 204 SQL DDL extensions

In addition to the statements in Effects of DDL statements, Model 204 SQL DDL includes the extensions listed in the following table.

DDL extensions

Extension	Statement	Description
NESTED USING clause	CREATE TABLE	Defines the table as nested within a parent table and specifies the joining column. Used for mapping Model 204 files with repeating fields or repeating groups of fields.
REFERENCES clause	CREATE TABLE	Syntax as part of an optional referential constraint definition is the same as defined in the standard. Its extended functionality in Model 204 SQL DDL is that it is required in any nested table definition, it is ignored for non-nested tables, and it implies a CASCADE action.
SYSNAME clause	CREATE TABLE	Identifies the actual name of the Model 204 file or field associated with the SQL table or column being defined. For more information, see Mapping table names to file names and Column naming and the SYSNAME extension.
SYSTEM clause	CREATE TABLE	Used in column definition with the PRIMARY KEY option to provide a system-generated primary key. For more information, see Using system-generated keys.
SET USER statement		Sets SQL authorization ID without affecting Model 204 login ID, which allows a system manager to create SQL objects for another user without having to login as that user or give that user system manager privileges.

Creating SQL objects

SQL objects (tables, columns, and views) are created with the CREATE statement in the context of a schema. The Model 204 SQL syntax for CREATE SCHEMA is shown in Creating schemas.

This section introduces the descriptions of the Model 204 SQL DDL statements with which you define SQL objects:

CREATE SCHEMA
CREATE TABLE
CREATE VIEW

Note: The use and function of the DDL statements in this topic conform to the standard except where otherwise specified.

Authorization ID is equivalent to Model 204 user ID

You establish yourself as a valid SQL user by logging in to Model 204 with the LOGIN command. You are verified as a valid SQL user by Model 204 login security and any external security package that is in effect. The Model 204 user ID you specify in the LOGIN command becomes the SQL user authorization ID used by various DDL statements in a Model 204 SQL session. This Model 204 login ID is also the value returned when an SQL DML query specifies the SQL keyword USER.

For more information about Model 204 login security, see Storing security information (CCASTAT).

Model 204 SQL table types

Model 204 SQL DDL defines the following types of tables:

• Base tables are SQL schema tables that map directly to Model 204 files and fields. Such tables are not defined in terms of any other tables.
• Views are schema tables that map directly to SQL base tables or to other views. Base tables are created by CREATE TABLE; views are created by CREATE VIEW.
• Nested tables are base tables that have columns that map to Model 204 multiply occurring fields or groups of fields. A nested table is associated with a single base table parent by a unique table column key that joins the nested table to the parent table. Nested tables are a Model 204 SQL extension.

Statement ordering is important

You must create objects before they are referenced by other objects. For example, tables referenced by views must be created before the views that reference them.

Rocket Software recommends that you organize statements in a CREATE SCHEMA statement in the following order:

<tgr class="head">

Order	Statement	Description
1.	CREATE table statements	Parent tables, which cannot be nested, referenced in a NESTED clause must be defined before the nested table being created.
2.	CREATE view statements	Views referenced in other views must be created before the other views.
3.	GRANT statements (for tables and views)	Creating objects before objects that they reference causes a semantic error during compilation.

Naming SQL objects

The Model 204 SQL rules for naming SQL schemas, tables, views, and columns are the same as the standard SQL identifier rules: SQL object names can contain the characters A-Z, 0-9, or underscore, can have as many as 18 characters, and must begin with a letter. You cannot use embedded blanks or SQL reserved words.

Creating schemas

Like tables and columns, schemas exist as distinct records in the SQL catalog that are created by CREATE SCHEMA. Unlike tables and columns, a schema does not map directly to a Model 204 file.

This section focuses on the rules governing schema creation.

CREATE SCHEMA statement

Syntax

CREATE SCHEMA {schemaname | AUTHORIZATION authorization-id | schemaname AUTHORIZATION authorization-id} [schema-element ...]

Parameters

Where:

• schemaname conforms to rules for an SQL identifier (see Naming SQL objects). schemaname does not have to be explicitly specified. Examples in Indicating schema name and owner show how you can indicate the schema name.
• authorization-id conforms to the rules for a Model 204 login user ID (no more than 10 characters and no underscore characters, must begin with an alphabetic character and must not contain certain character combinations). The authorization ID cannot be an SQL reserved word).
• schema-element is one of the following:

  table-definition | view-definition | privilege-definition

  • table-definition as described in Creating tables.
  • view-definition as described in Creating views.
  • privilege-definition as described in GRANT statement and REVOKE statement.

Syntax rules

The following CREATE SCHEMA syntax rules apply:

• The schema name must be unique within the SQL catalog.
• The default authorization ID is the Model 204 login user ID.
• If the schema name is not specified, the schema name defaults to the authorization ID.
• Issuing CREATE SCHEMA for a schema that already exists is an error.
• Only a Model 204 system manager can issue CREATE SCHEMA.
• You can create schemas that have no tables, views, or privileges. Such an empty schema can be defined first and populated later. For example, the following sequence is valid DDL:

  CREATE SCHEMA S CREATE SCHEMA P CREATE SCHEMA SP

Indicating schema name and owner

Examples of schema creation with different CREATE SCHEMA formats follow. These examples show different ways to indicate the schema name and owner. You can choose whether to explicitly specify a schema name or to explicitly specify an authorization ID.

In the following example, schema name is TED, schema owner is TED. If you specify no name, the default name is your authorization ID.

CREATE SCHEMA AUTHORIZATION TED CREATE TABLE S (...) CREATE TABLE P (...) CREATE TABLE SP (...)

In the following example, schema name is MATERIAL_CONTROL, schema owner is MFTNG.

CREATE SCHEMA MATERIAL_CONTROL AUTHORIZATION MFTNG CREATE TABLE PARTS (...) CREATE TABLE SHIPMENTS (...)

In the following example, schema name is ACCOUNTS, schema owner defaults to the authorization ID of the issuing user.

CREATE SCHEMA ACCOUNTS CREATE TABLE ACCOUNTS_PAYABLE (...) CREATE TABLE ACCOUNTS_RECEIVE (...)

The schema name determined by a CREATE SCHEMA statement remains the default schema name, which is assigned to the SQL objects you define, until the CREATE SCHEMA transaction completes. For information about setting the default schema outside the context of a CREATE SCHEMA using the Model 204 SQL extension SET SCHEMA, see Using SET SCHEMA.

Creating tables

This section describes table creation syntax and considerations that apply generally to both nested and non-nested SQL tables. Information that is nested-table specific is presented in Creating nested tables. The definition of columns for a table is described in Defining columns.

A slightly abridged version of the CREATE TABLE statement syntax that the Model 204 SQL Server supports is shown below. See SQL DDL syntax for the complete syntax.

CREATE TABLE statement

Syntax

CREATE TABLE <tablename> [ SYSNAME 'filename' | NESTED USING columname ] ( <column-definition> | <table-constraint-definition> [,<column-definition> | <table-constraint-definition> ] ...)

Parameters

Where:

• tablename conforms to rules for an SQL identifier (see Naming SQL objects). See also Prefixing the schema name to an SQL object.
• SYSNAME 'filename' maps a table to a physical file; see Mapping table names to file names for more detail.
• NESTED USING columname is an extension that identifies a nested table. See Creating nested tables.
• column-definition has the following syntax, as described in Defining columns.

  columname <datatype> [SYSNAME 'fieldname'] [<column-constraint> ...]

• table-constraint-definition has the following syntax:

  { UNIQUE | PRIMARY KEY [ SYSTEM ] } ( <column-list> ) [ SYSNAME '<fieldname>' ] | FOREIGN KEY ( columname ) REFERENCES parent-table-name [ <referential-triggered-action> ]

  • UNIQUE must map to a Model 204 ORDERED UNIQUE field. For discussion, see Specifying a multicolumn UNIQUE key.
  • PRIMARY KEY [SYSTEM], where SYSTEM is an extension with which you can have the Model 204 SQL Server generate and manage a unique primary key, as described in Using system-generated keys.
  • SYSNAME 'fieldname' maps a column to the named Model 204 field; see Column naming and the SYSNAME extension.
  • FOREIGN KEY and REFERENCES clauses for defining referential integrity constraints are supported for nested tables only.

Syntax rules

The CREATE TABLE syntax rules follow:

• The table name must be unique within a schema.
• A table must have at least one column defined.
• CHECK table constraint is not supported in Model 204 SQL. If you include a CHECK clause in your SQL DDL, it does not become part of the SQL catalog definition, although you receive no syntax error.

  Model 204 SQL does support the WITH CHECK OPTION for views, however.

Mapping table names to file names

The SQL Server maps the table you define to a Model 204 file. Your CREATE TABLE statement determines both the name of the SQL table and the name of the Model 204 file to which the table is mapped. You can implicitly or explicitly identify the Model 204 file to which the table is mapped, or you can have Model 204 generate a unique file name.

Implicitly identifying the file means mapping the table you name in the CREATE TABLE to a file with the same name. Explicitly identifying the file means mapping the table you name to a file that you specify with the keyword SYSNAME. Automatic system generation of the file name is based on your CREATE TABLE specification and is invoked by setting the Model 204 parameter SQLFILE.

As shown in the following table, you indicate which of these file-naming options you want by whether you specify a SYSNAME clause in your CREATE TABLE statement and by your setting of the Model 204 SQLFILE parameter.

Naming the corresponding Model 204 file

Method	Add SYSNAME filename to CREATE TABLE tablename?	SQLFILE setting	Resulting file name
Implicit	No	0	tablename
Explicit	Yes	0 or 1	filename
System generated	No	1	Uniquely determined by Model 204

The SYSNAME clause is an optional Model 204 extension. If you specify a SYSNAME value, that value is the file name to which your table is mapped. This is true regardless of the SQLFILE setting.

SQLFILE is a Model 204 CCAIN parameter whose default setting of zero turns off system generation of file names. If the SQLFILE value is one, and no SYSNAME clause is specified, Model 204 generates a unique file name for the specified SQL table.

Processing file names

File names you provide explicitly or implicitly are subject to Model 204 file naming rules.

If you are implicitly naming a file (no SYSNAME, SQLFILE=0), the SQL table name you specify is assumed to be the name of the Model 204 file. If this table name does not conform to the Model 204 file naming rules, it is truncated and/or compressed to satisfy the Model 204 rules. (Model 204 file names cannot have more than eight characters, underscore characters, initial numbers, or certain character combinations.)

This assumed name is recorded in the SQL catalog as the Model 204 file name. If the assumed name does not match an existing Model 204 file name, you get an error when your application queries the database with that file in the query.

For example, with no SYSNAME and SQLFILE=0, Model 204 SQL maps the SQL table OUR_OLD_DATA to the Model 204 file OUROLDDA.

If you identify the file explicitly with a SYSNAME clause, the name processing is simpler: if the name you specify in the SYSNAME clause is greater than eight characters, it is truncated to eight characters and stored in the catalog as the file name. Any violations of Model 204 file naming rules are not detected until your application queries the database with that file in the query.

For more information about Model 204 file naming rules, see Creating a file.

Using CLOB or BLOB data

The CREATE TABLE statement supports the Character Large Object (CLOB) and Binary Large Object (BLOB) data types.

To define a column containing CLOB or BLOB data, specify the keyword "CLOB" or "BLOB" for the data type in the CREATE TABLE statement.

The following example defines a TABLE called MANUALS that contains all of the manuals for Model204. RECTYPE and NAME are CHARACTER columns that identify the format and the name of the manual. MANUAL is a CLOB column that contains the actual text of the manual.

CREATE TABLE MANUALS ( RECTYPE CHAR(15), NAME CHAR(25) MANUAL CLOB)

The following example defines a table containing employee information and a picture of the employee.

CREATE TABLE EMPLOYEE ( ID CHAR(15), NAME CHAR(20), PICTURE BLOB)

Defining columns

This section describes column definition syntax and considerations that apply generally to both nested and non-nested SQL tables. Information that is nested-table specific is presented in Creating nested tables.

Column definition statement

Syntax

columname <datatype> [ SYSNAME 'fieldname' ] [ <column-constraint> ... ]

Parameters

Where:

• columname conforms to rules for an SQL identifier (see Naming SQL objects).
• datatype is the column's data format, the options for which are:

  CHAR[ACTER] [(length)] | NUM[ERIC] [(precision [,scale])] | DEC[IMAL] [(precision [,scale])] | INT[EGER] | SMALLINT | FLOAT [(precision)] | REAL | DOUBLE PRECISION | CLOB | BLOB

  For more information about choosing data types, see Matching Model 204 and SQL data formats.

• SYSNAME 'fieldname' maps a column to a Model 204 field; see Column naming and the SYSNAME extension.
• column-constraint specifies the following syntax:

  [NOT NULL] [UNIQUE | PRIMARY KEY [SYSTEM]] | REFERENCES parent-table-name [ <referential-triggered-action> ]

  • NOT NULL column maps to a Model 204 field that has a non-null, nonempty value on every record in the Model 204 file. The Model 204 SQL Server does not allow you to violate this rule in an SQL DML update. If you add a NOT NULL column to an existing table, be sure the corresponding Model 204 field has non-null, nonempty values in all the records in the file. For more information about Model 204 SQL handling of nulls, see Handling NOT NULL, UNIQUE, and multiply occurring data.
  • UNIQUE must map to a Model 204 ORDERED UNIQUE field. For discussion about multiple-column uniqueness, see Specifying a multicolumn UNIQUE key.
  • PRIMARY KEY [SYSTEM], where SYSTEM is an extension with which you can have the Model 204 SQL Server generate and manage a unique primary key, as described in Using system-generated keys.
  • REFERENCES clause for defining a referential integrity constraint is supported for nested tables only, as described in Nested tables require a referential constraint definition.

Syntax rules

• Only one PRIMARY KEY clause is allowed per table. Specifying more than one brings an error message.
• A primary key call is based on a Model 204 field with unique and ordered attributes.
• Unlike the SQL standard, PRIMARY KEY is syntactically independent of NOT NULL. Specifying PRIMARY KEY without NOT NULL is not a syntax error. However, regardless of whether you specify NOT NULL, when you specify PRIMARY KEY, the SQL Server includes NOT NULL checking by default.
• Unlike the SQL standard, UNIQUE is independent of NOT NULL. If you specify UNIQUE, NOT NULL is not implied.
• The DEFAULT clause for column definition is not supported in Model 204 SQL. If you include a DEFAULT clause in your SQL DDL, it does not become part of the SQL catalog definition, although you receive no syntax error.
• The CHECK column constraint is not supported in Model 204 SQL. If you include a CHECK clause in your SQL DDL, it does not become part of the SQL catalog definition, although you receive no syntax error.
• Model 204 SQL does support the WITH CHECK OPTION for views, however.

Mapping columns to Model 204 fields

The following are requirements and recommendations for mapping SQL columns to Model 204 fields:

• You cannot map two different columns in the same table to the same Model 204 field.
• To ensure accurate and efficient data handling, you must map Model 204 fields to SQL columns that have compatible data types. For information about specifying data types for selected columns, see Compatibility of Model 204 and SQL data formats. See also Specifying attributes for additional data type requirements.
• You can map a Model 204 INVISIBLE field to an individual SQL column, but the column's usability in DML operations is restricted. For example, you can use the column in certain circumstances in the WHERE clause of a SELECT statement but cannot use the column in the SELECT list. You can use the column as the target of an INSERT, but cannot use it as the target or in the source expression of an UPDATE.

  For more information about restrictions on DML operations with columns mapped to INVISIBLE fields, see Using SQL DML against INVISIBLE fields.

  You cannot map an INVISIBLE field to a column that serves as a primary key nor to a nested table column. You can map an INVISIBLE field to a multicolumn unique constraint key, but not to the individual columns that comprise the key. For information about multicolumn unique keys, see Specifying a multicolumn UNIQUE key and, in the Table Specification facility, Defining multicolumn unique keys (Multi-Column Unique panel.

• If a Model 204 file is a sorted or hash key file, specify the sort or hash key as a column in the table for the file if the table is a parent or base table and the Model 204 FILEORG parameter has the X`02' option (key required) set. Failure to do so does not result in a DDL error message, but DML file insert attempts fail.

  Also for such files, you cannot update the sort or hash key with SQL DML. SQL UPDATE statements fail if a column that maps to the sort or hash key is included.

  The Model 204 SQL Table Specification facility requires you to specify the sort or hash key as a column. However, if you do not want to include a column for the sort or hash key, you can edit the DDL generated by the TSF (if you are setting up a read-only table or if the sort or hash key is not required, for example).

• Model 204 UNIQUE fields are guaranteed to have unique values but are not guaranteed to have no null values. When you map an SQL UNIQUE column to such a field, the SQL NOT NULL constraint is not implied. This decoupling of UNIQUE and NOT NULL is a Model 204 SQL extension to the SQL standard.

  If you include NOT NULL in an SQL UNIQUE column's definition, the SQL Server prevents updates that violate the NOT NULL condition, as well as provides Model 204 uniqueness protection.

Column naming and the SYSNAME extension

Model 204 SQL maps the SQL column you name to a Model 204 field. Unless you designate with the SYSNAME clause the Model 204 field you are mapping to the SQL column, the SQL Server assumes the SQL column name is the Model 204 field name. If your column name does not match the Model 204 field name, you are notified of the error when you attempt a DML query involving the column.

An SQL column name can contain as many as 18 characters (A-Z, 0-9, and underscore), and it must have no embedded blanks. Model 204 field names can have 255 characters, embedded blanks, and a variety of special characters; only certain character combinations are restricted.

SYSNAME allows you to resolve any conflicts between existing Model 204 names and SQL naming rules. Any pre-existing SQL naming format you have can be retained without modification.

For an example of using SYSNAME, see Specifying a multicolumn UNIQUE key, which also describes how multicolumn key names are modified and stored in the SQL catalog.

For more information about Model 204 field naming rules, see Field names.

Specifying a multicolumn UNIQUE key

You can provide uniqueness checking for an SQL column by mapping it to a Model 204 field that has the ORDERED UNIQUE field attribute. Any SQL operation on such a column that violates its uniqueness in the table is not allowed. To provide such a uniqueness constraint for the combination of the values of two or more columns in a table, you can designate a multicolumn unique key.

In Model 204 SQL DDL, a multicolumn unique key is a constraint key that has no SQL name and is not queriable. You map such a key to a Model 204 UNIQUE index field that your Model 204 file manager must add to the Model 204 file. This special field, which must be ORDERED CHAR, is a concatenation of the fields that correspond to the SQL columns in the key. Directions for defining and populating such a field follow.

Once the key is defined to the SQL catalog, SQL updates to any of the fields automatically update the index field that was added to support the multi-column unique definition. SQL INSERTs add values to the index, DELETEs remove values, and UPDATEs modify values.

Defining the key

You can use the Table Specification facility or manually define the multicolumn unique key in the DDL you submit to the SQL catalog.

To manually define a multicolumn unique key:

• Make sure the definitions of the columns that are to comprise the key include a NOT NULL specification.
• Include a UNIQUE constraint clause after the column definitions. Follow the UNIQUE keyword with a parenthesized sequence of the names of the columns forming the key. The order of the columns in parentheses determines the order in which they are concatenated to build the supporting Model 204 UNIQUE index field.

In the following example, two columns are concatenated to form a unique key.

CREATE TABLE SITE ( ORG_ID CHAR(8) SYSNAME 'ORG ID' NOT NULL, SITE_ID CHAR(4) SYSNAME 'SITE ID' NOT NULL, SITE_NAME CHAR(36) SYSNAME 'SITE NAME' , UNIQUE (ORG_ID, SITE_ID) SYSNAME 'ORG SITE ID INVIS' )

In this example, a SYSNAME clause is used in each column definition, including the multicolumn unique key. Although using SYSNAME is optional for the multicolumn unique key, remember that the Model 204 SQL Server generates an assumed field name if you do not specify one with SYSNAME.

Without the SYSNAME clause following the UNIQUE clause in the example above, the Model 204 SQL Server concatenates the individual names ORG_ID and SITE_ID with an ampersand character (&) in between to get ORG_ID&SITE_ID. This name is stored in the SQL catalog as the name of the Model 204 index field.

The maximum number of columns you may concatenate depends on your data. You cannot exceed the Model 204 limit of 255 characters for the combined lengths of the concatenated field values. In addition, you cannot exceed the 255 characters for the key name (either the SYSNAME value for the Model 204 field or, if SYSNAME is not used, the concatenation of the SQL column names).

The Model 204 field that corresponds to the SQL multicolumn unique key is normally INVISIBLE to save Table B space. The key's constituent columns (ORG_ID and SITE_ID in the example above) cannot be mapped to INVISIBLE fields.

Populating the index field

Once the Model 204 index field and multicolumn unique key are defined, you must populate the index. If you are defining a key for a table column that maps to a new Model 204 file, Model 204 SQL automatically populates the index as you insert records through SQL.

If the Model 204 file data already exists, you must populate the index with values that are concatenations of the data values in the fields that are mapped to the columns comprising the key. You can populate the index for an existing file in two ways:

• Automatically, with an SQL UPDATE statement. Use this method if you are maintaining the associated Model 204 file exclusively with SQL.

  Issue an SQL UPDATE in which you SET one of the multicolumn unique key columns equal to itself. This triggers an automatic building or rebuilding of the index for any multicolumn unique key that includes the column. If the column is a member of more than one multicolumn unique key, issuing such an UPDATE triggers the rebuilding of all of them.

  For example, you can issue the following UPDATE statement to populate the index for the key (ORG SITE ID INVIS):

  UPDATE SITE SET ORG_ID=ORG_ID

  As a precaution, make a backup copy of the file before the UPDATE.

• Manually, with Model 204 SOUL or Host Language Interface manipulation. Use this method if you are maintaining the associated Model 204 file with SOUL or the Host Language Interface. You can use the algorithm described in Using the multicolumn unique key algorithm.

  If you allow updates to the Model 204 file with SOUL or the Host Language Interface, you must ensure that you continue to manually maintain the index field.

If you want or need to repopulate an index field, use either of the following methods:

• If the field is INVISIBLE, delete the field (with the Model 204 DELETE command), define it again (with the Model 204 DEFINE command), then repopulate the field using the SQL UPDATE method.
• If the field is not INVISIBLE, flush the old index by redefining (with the Model 204 REDEFINE command) the index field without the ORDERED attribute. Then redefine the field again, this time with the ORDERED CHAR attribute. Finally, repopulate the field using the SQL UPDATE method.

Using the multicolumn unique key algorithm

This algorithm defines for each column in the key the method for generating the field value to be concatenated into the key. The algorithm is affected only by the SQL column attribute; how the data is stored in Model 204 does not matter. Each data value is individually generated as described in the following table. The values are then concatenated and stored in the index as described in the following steps.

1. For each component field, define the data value to a variable according to column data type, as shown in the following table.

   Multicolumn unique key encoding rules

   Column data type	Encoding rules
   CHARACTER	Remove trailing blanks, then convert to counted string (character string with one-byte prefix that specifies the number of characters in the string).
   INTEGER or SMALLINT	Store as 4-byte IBM binary integer.
   DECIMAL	Convert to a right-justified string with length dependent on scale: scale=o: length=precision + 1 scale=precision: length=precision + 3 scale>precision: length=precision + 2
   NUMERIC	Same as DECIMAL.
   REAL DOUBLE FLOAT	For floating point data, use the SQL UPDATE method described in Populating the index field. Model 204 SQL converts floating point data according to proprietary rules that you cannot reliably duplicate.

2. Concatenate the generated variables, removing trailing blanks (X`40'), leaving embedded blanks, and keeping one blank if the length of the concatenated key after this processing is zero.
3. Store the concatenated value as your index value.

Algorithm example

Build an index value from the following columns and values:

COL1 is DECIMAL (7, 2) COL2 is CHARACTER (6) COL3 is DOUBLE COL4 is INTEGER

(sample value: 10.2) (sample value: 'JOHN') (sample value: 16.0) (sample value: 1)

The concatenated value is:

Creating nested tables

A nested table is a base table with columns that map to Model 204 multiply occurring fields or groups of fields. A nested table is associated with a single base table parent by a unique table column key, which joins the nested table to the parent table.

The NESTED USING clause is the optional clause in a CREATE TABLE statement that identifies the table as a nested table. The abridged CREATE TABLE syntax for a nested table below shows this NESTED clause extension.

Nested tables are Model 204 SQL DDL extensions. There are no extensions to Model 204 SQL DML for using nested tables. For a DML query, nested tables are logically represented like a typical SQL table. Querying nested tables is discussed in SQL Data Manipulation Language (DML).

This section describes the DDL coding rules and requirements for the creation of nested tables. For information about creating nested tables with the TSF, see Defining nested tables. For introductory information about Model 204 nested tables, see Mapping multiply occurring fields to nested tables.

Nested table statements

Syntax

CREATE TABLE tablename NESTED USING columname ( column-definition | table-constraint-definition [ ,column-definition | table-constraint-definition ]... )

Parameters

Where:

• column-definition has the following syntax:

  columname datatype [ SYSNAME 'fieldname' ] [ column-constraint ] ...

  • column-constraint has the following syntax:

    NOT NULL [ UNIQUE ] | REFERENCES parent-table-name [ referential-triggered-action ]

    • referential triggered action has the following syntax:

      ON UPDATE CASCADE [ ON DELETE CASCADE ] | ON DELETE CASCADE [ ON UPDATE CASCADE ]

• table-constraint-definition has the following syntax:

  unique-constraint-definition | referential-constraint-definition

  • unique-constraint-definition has the following syntax:

    UNIQUE ( columname ) [ SYSNAME 'fieldname' ]

  • referential constraint definition has the following syntax:

    [FOREIGN KEY (columname) REFERENCES parent-table-name [referential-triggered-action]]

    • referential triggered action has the following syntax:

      ON UPDATE CASCADE [ ON DELETE CASCADE ] | ON DELETE CASCADE [ ON UPDATE CASCADE ]

Syntax rules

Unless otherwise specified, all the rules that apply to tables apply also to nested tables.

Rules for nested table columns

• Only one column can be referenced by a NESTED clause, and only one NESTED clause is allowed per table.
• Nested table must have one foreign key column, and can have no more.
• Nested table must have at least one column in addition to the foreign key column.
• All columns in a nested table must be defined as NOT NULL.
• Column in a nested table cannot be mapped to a field with the Model 204 field attribute INVISIBLE.
• Column in a nested table may not be mapped to a field with the Model 204 field attribute UPDATE AT END.
• Multicolumn composite UNIQUE key is not allowed for a nested table.

Rules for PRIMARY KEY and FOREIGN KEY

• A parent table that a foreign key refers to with the REFERENCES clause must have a PRIMARY KEY. This PRIMARY KEY can be a system-generated key.
  • A PRIMARY KEY must map to a Model 204 UNIQUE ORDERED field.
  • PRIMARY KEY cannot map to a Model 204 INVISIBLE field.
• If a foreign key is defined twice, first with a REFERENCES clause and then with a FOREIGN KEY clause, the statement is accepted only if the two clauses are identical and reference the same column.

SQL error message -4703 is generated if a FOREIGN key is defined twice for a nested table and the two keys do not reference the same column. For example:

Acceptable:

CREATE TABLE NESTEDINVENTOR2 NESTED USING PART_NO (PART_NO DECIMAL(8) NOT NULL REFERENCES INVENTORY, ON_HAND SYSNAME 'ON HAND' FLOAT(4) NOT NULL, LOCATION CHAR(255) NOT NULL, FOREIGN KEY (PART_NO) REFERENCES INVENTORY)

Returns SQL error -4703:

CREATE TABLE NESTEDINVENTOR2 NESTED USING PART_NO (PART_NO DECIMAL(8) NOT NULL REFERENCES INVENTORY, ON_HAND SYSNAME 'ON HAND' FLOAT(4) NOT NULL, LOCATION CHAR(255) NOT NULL, FOREIGN KEY (ON_HAND) REFERENCES INVENTORY)

• The data type of the FOREIGN KEY of a nested table must match exactly (both in type and length) the data type of the PRIMARY KEY of the referenced table. Violation causes an error message to be issued.
• When paired in a nested table relationship, PRIMARY KEY and FOREIGN KEY can specify only single-column keys. For multicolumn primary key functionality, use PRIMARY KEY SYSTEM.

For more information, see Nested tables require a foreign key and Using system-generated keys. For more information about multiple-column primary keys, see Simulating multicolumn primary keys.

Rules for SYSTEM and SYSNAME

• The SYSTEM keyword is optional, but if SYSTEM is specified, PRIMARY KEY must also be specified. The column associated with PRIMARY KEY SYSTEM must have a data type of INTEGER.
• SYSNAME cannot be specified with PRIMARY KEY SYSTEM. SYSTEM causes the mapping of the column to a system-generated field. Specifying SYSNAME, which implies a link to a Model 204 field name, conflicts with this automatic mapping. A violation of this rule causes an error message to be issued.
• Neither SYSTEM nor SYSNAME can be specified with the foreign key column of a nested table. A violation of this rule causes an error message to be issued.
• SYSTEM and SYSNAME are Model 204 SQL extensions. Using SYSTEM is discussed in Using system-generated keys; using SYSNAME is discussed in Mapping table names to file names and Column naming and the SYSNAME extension.

Mapping multiply occurring groups

Nested table rows are ordered and retrieved by matching occurrence: the first row is mapped to the first occurrences of the repeating field values, the second row is mapped to the second occurrences of the repeating field values, and so on. The rank of occurrence (first, second, and so on) is determined by physical storage order in the file. For an example showing this row-to-occurrence mapping, see Simulating normalization of Model 204 record data.

To preserve the matching occurrence ordering, each member of a group of multiply occurring values mapped to nested table columns must have the same number of occurrences. Otherwise, retrievals of these columns are not reliable. In addition, since null values invalidate matching occurrence ordering, nested table columns must be NOT NULL.

If you want to add a new column to an existing nested table, the column must have as many rows as the other table columns and must have a value for each of the existing rows.

Nested tables require a foreign key

A nested table must have a foreign key column. A foreign key is equivalent to its related primary key in the parent table. The unique values of the primary or foreign key are used to locate the Model 204 records with the repeating field values.

You cannot update the foreign key directly with SQL DML. Updates to the primary key are propagated to the foreign key. You must specify the foreign key in an SQL INSERT; it is used to locate the parent record.

For an example of specifying the foreign key in DDL, see Table constraint format.

Nested tables require a referential constraint definition

A referential constraint definition (REFERENCES clause) is required in conjunction with the NESTED clause to define a nested table and its relationship to its parent. This constraint protects SQL database integrity by ensuring that all the values of a column in the nested table (the foreign key) match all the values of the primary key of the referenced parent table. Operations that violate a defined referential constraint are not allowed.

Only one referential constraint is allowed per table. If you specify two REFERENCES clauses for a single table (one in a column definition and one in a foreign key definition), the clauses must be the same and reference the same column.

How you specify a referential constraint depends on whether you are using the column definition or table constraint definition option of the Model 204 SQL CREATE TABLE statement. Examples of each follow.

In addition, the format changes slightly if you are using a system-generated primary key. For examples with system-generated primary keys, see Using system-generated keys.

Table constraint format

There must be a FOREIGN KEY clause that refers to the column (the foreign key) specified in the NESTED clause. The FOREIGN KEY clause must be followed by a REFERENCES clause referring to the parent table.

For example:

CREATE TABLE PEOPLE ( NAME CHAR(60) NOT NULL PRIMARY KEY HIRE_DATE CHAR(8) ) CREATE TABLE TASKS NESTED USING FNAME ( TASK CHAR (25) NOT NULL, FNAME CHAR (60) NOT NULL, FOREIGN KEY (FNAME) REFERENCES PEOPLE )

Column constraint format

The column that is specified in the NESTED clause must have a REFERENCES clause referring to the parent table in the column definition.

For example:

CREATE TABLE PEOPLE ( NAME CHAR(60) NOT NULL PRIMARY KEY, HIRE_DATE CHAR(8) ) CREATE TABLE TASKS NESTED USING FNAME ( TASK CHAR (25) NOT NULL, FNAME CHAR (60) NOT NULL REFERENCES PEOPLE )

CASCADE is the only referential triggered action

A referential triggered action is a delete rule (ON DELETE action) and/or an update rule (ON UPDATE action) that governs what action a system takes if a referential constraint is violated. A system can refuse to execute a constraint-violating request, or it can cascade (that is, can automatically execute an operation that compensates for the violation).

For example, if you change a value of the primary key of the parent table referenced by a nested table, you violate the referential integrity (REFERENCES clause) of the foreign key. A system can protect the referential integrity by preventing you from changing the primary key value. Or a system can cascade, automatically associating the nested table rows that refer to the changed primary key value with the new primary key value. The Model 204 SQL Server uses only the cascade action; it does not prevent you from changing the primary key value.

If you change a value of the primary key of the parent table from n1 to n2, the Model 204 SQL Server changes all nested table foreign key references from n1 to n2.

A referential triggered action is specified with a REFERENCES clause used in a nested table definition. For the column in a nested table defined in the NESTED clause, the only Model 204 SQL Server referential triggered action that you can specify is the CASCADE action. Updates and deletes are automatically cascaded.

If you do not specify an update or delete rule, the rule is set to CASCADE by default.

Using system-generated keys

A primary key is a column that has values that uniquely identify each record in the table. A nested table parent is required to have such a column. If no single column is a unique identifier in a nested table parent, or if a combination of two or more columns comprise the unique identifier, you must have the Model 204 SQL Server generate and manage a unique primary key.

This system-generated key is a nonupdatable, unique integer key automatically assigned to each row occurrence by the system when the row is inserted. In fact, this key is the Model 204 internal record number, and other than being nonupdatable, it is like any other relational column. You can retrieve it with SELECT and use it in predicates. You can give it any name you want.

In some queries, system-generated keys might be more efficient than specified single-column keys, because they provide direct access to the data. However, the system-generated values for SYSTEM keys are not necessarily preserved across a file reorganization: after a reorganization, the value for a system-generated column might change for any particular row occurrence. Because of this impermanence, avoid operations with the value of this column if the result of the operations is required for longer than the current session.

Defining system-generated keys

To define a system-generated key, specify the SYSTEM modifier to the PRIMARY KEY constraint in the parent table column definition. The following examples use the column constraint format of CREATE TABLE.

Example 1 - Primary key is not system-generated

In this example, repeated from Column constraint format, the primary key is not system-generated. Here, NAME is a unique identifier for each record in the parent table.

CREATE TABLE PEOPLE ( NAME CHAR(60) NOT NULL PRIMARY KEY, HIRE_DATE CHAR(8) ) CREATE TABLE TASKS NESTED USING FNAME ( TASK CHAR (25) NOT NULL, FNAME CHAR (60) NOT NULL REFERENCES PEOPLE )

The column name used for the primary key in the parent table is different from its related foreign key in the nested table, but this is not a requirement in SQL.

Example 2 - Primary key is system-generated

In this example, the primary key is system-generated. Assume NAME and HIRE_DATE together form a unique identifier for each record in the parent table. Such a composite key requires you to use a system-generated primary key, because the primary key for nesting must be a single column.

To define a system-generated key, specify a name for the system-generated primary key in the parent table column definition, and follow the token PRIMARY KEY with the modifier SYSTEM:

CREATE TABLE PEOPLE ( PKEY INTEGER NOT NULL PRIMARY KEY SYSTEM, NAME CHAR(60) NOT NULL HIRE_DATE CHAR(8) ) CREATE TABLE TASKS NESTED USING PKID ( TASK CHAR (25) NOT NULL, PKID INTEGER NOT NULL REFERENCES PEOPLE )

Notice that the FNAME column definition included in Example 1 is not included in the definition in Example 2.

In Example 1, FNAME is the foreign key, that is, it is equivalent to the primary key NAME.

In Example 2, PKEY replaces NAME as the primary key. A foreign key in TASKS equivalent to PKID must be included.

For information about multicolumn keys for non-nested tables, see Specifying a multicolumn UNIQUE key.

Simulating multicolumn primary keys

As stated in Example 2, Model 204 SQL does not allow you to define a multicolumn primary key in conjunction with a nested table foreign key. In such a case, however, you can use a system-generated primary key to get the functionality of a multicolumn primary key.

In addition to the definition of the system-generated primary key shown in example 2, add a uniqueness constraint definition for the columns that together form a unique identifier for each record in the parent table. (This constraint requires designating HIRE_DATE as NOT NULL.)

CREATE TABLE PEOPLE ( PKEY INTEGER NOT NULL PRIMARY KEY SYSTEM, NAME CHAR(60) NOT NULL HIRE_DATE CHAR(8) NOT NULL UNIQUE (NAME, HIRE_DATE) SYSNAME 'HIRE ID' )

The system-generated key satisfies the Model 204 SQL requirement for a single-column unique primary key. The multicolumn unique key definition for NAME and HIRE_DATE preserves their uniqueness.

To SELECT the composite key columns (NAME and HIRE_DATE) and the multiply occurring column (TASK), you need to use the following joined-table SELECT instead of a simple non-joined SELECT:

SELECT NAME, HIRE_DATE, TASK FROM PEOPLE, TASKS WHERE PKEY=PKID

Inserts into tables with system-generated keys

You cannot insert column values into a table that has a system-generated key unless you specify the target-column list. For example, the following insert into table PEOPLE, whose primary key PKEY is system-generated, is valid. Model 204 SQL automatically provides a value for PKEY:

INSERT INTO PEOPLE (NAME, HIRE_DATE) VALUES (`CJDATE','10/01/90')

SQL syntax rules dictate that inserts without a column list require you to provide values for all the columns in the table. Because you cannot provide the primary key value (because PKEY is system-generated and you cannot update a system-generated key), removing (NAME, HIRE_DATE) from the example above causes the insert to fail.

These rules for inserts into tables that have system-generated keys apply to base and parent tables. For inserts into nested tables that have system-generated keys, you must provide the primary key value from the parent table.

Creating views

Unlike tables and columns, views do not map directly to Model 204 files and fields. A view is a selected set of columns and rows from one or more SQL tables or views that can be displayed as a unit. A view contains no data but instead is defined to access data in one or more tables.

The Model 204 SQL CREATE VIEW statement includes extended SELECT functionality that enables the simulation of Model 204 groups.

For a discussion and detailed example of the DDL statement security considerations affecting view creation and access, see Model 204 SQL view privileges.

CREATE VIEW statement

Syntax

CREATE VIEW <viewname> [ ( <column-list> ) ] AS <query-expression> [ WITH CHECK OPTION ]

Parameters

where:

• viewname conforms to the rules for a SQL identifier.
• column-list has the following syntax:

  columname [,columname ] ...

• query-expression is a query specification or a UNION of query specifications, where a query specification is a SELECT statement (with no ORDER BY clause).
• The WITH CHECK OPTION checks DML inserts and updates to ensure that they do not violate view definition conditions.

Syntax rules

Query specifications joined by the UNION operator:

• cannot contain joined tables
• cannot include GROUP BY or HAVING
• cannot contain an EXISTS clause

Rules for updating views

The following rules govern whether you can update a particular view.

• The definition of the view cannot include the UNION keyword or the DISTINCT keyword.
• You can refer to only one table in the FROM clause.
• If the CREATE VIEW statement includes a WHERE clause, the WHERE clause cannot include a subquery.
• The CREATE VIEW statement cannot include a GROUP BY clause or a HAVING clause.
• If the CREATE VIEW statement includes a SELECT clause, the SELECT clause cannot contain expressions.

This means that DDL used to define a view cannot grant the privileges UPDATE, INSERT, or DELETE to any view that ignores the rules for updating views.

For example, the following view DDL generates an error.

SET SCHEMA DEMO SET USER AGENT1 DROP VIEW TEST_VIEW CREATE VIEW TEST_VIEW (TEST_ID, POLICY_NO) AS SELECT TEST_TABLE.TEST_ID, CLIENTS.POLICY_NO FROM TEST_TABLE, CLIENTS GRANT SELECT, UPDATE, DELETE, INSERT ON TEST_VIEW TO ADMIN

Because you cannot update a view that is based on more than one table (TEST_TABLE and CLIENTS) this DDL generates the following error:

SQL Error -551 AGENT1 does not have the privilege to perform operation GRANT on object DEMO.TEST_VIEW.

However, the following GRANT SELECT clause is valid:

GRANT SELECT ON TEST_VIEW TO ADMIN

Only the updating clauses (UPDATE, INSERT, DELETE) are disallowed.

Guideline for view definitions

The following general rule for view definitions governs what is allowed in a view definition and how you can select against that view:

• Query in a DML SELECT against a view is evaluated by substituting the view definition for the references to the view in the DML SELECT. The query that results from this substitution or translation process must always be a valid SQL SELECT statement.

Using SQL views in Model 204 SQL DDL

Use SQL views for the following operations:

• To implement your security strategy more efficiently.

  Once a view is defined, the Model 204 SQL Server checks only the privileges to access the view itself, not the privileges for each of the elements of the view. For example, you might want to secure data by record type in a mixed record type file. After defining the views by record type, you can then limit access to each type of record by selectively granting privileges to access each view.

  For more information about privileges for views, see DDL statement-level security.

• To provide security that is similar to Model 204 field level security.

  Using GRANT to selectively permit updates to individual columns of a table can be degrading to DML processing performance. You can instead define a view with these columns and grant limited access to the view. Such a view is easier to define and maintain and is the only way to also grant SELECT access to just those columns.

• To simulate Model 204 file groups.

  File groups are not directly supported in Model 204 SQL DDL. However, you can use a Model 204 SQL CREATE VIEW extension to create views that simulate Model 204 groups.

• To map Model 204 files that contain mixed record types.

  Define individual tables to the SQL catalog that map to files that have a variety of types of records. You can isolate the individual record types by defining a separate view for each one.

Simulating file groups

An SQL table cannot be mapped to a Model 204 file group. You can simulate a group, however, by creating a view that is comprised of a concatenation of tables that are mapped to the files in the group.

First, map an SQL table to each of the files in the Model 204 group. Then create a view defined as a UNION (or UNION ALL) of SQL SELECT statements, each of which selects all the rows in one of the tables mapped to the group. This union of SELECT statements is extended functionality to the CREATE VIEW statement.

The following example shows three table definitions and then a view definition based on those tables that simulates a Model 204 file group. The example assumes that three Model 204 files named CLAIMS89, CLAIMS90 and CLAIMS91 are allocated to the run and are usually accessed as a group, named CLAIMS, from SOUL programs. However, since SQL tables cannot be mapped to file groups, this provides a way to simulate groups in SQL:

CREATE TABLE CLAIMS89 ( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) ) CREATE TABLE CLAIMS90 ( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) ) CREATE TABLE CLAIMS91 ( POL_NO INTEGER, ACC INTEGER, STATE CHAR(2) ) CREATE VIEW MA_POLICIES (POLICY_NO, ACCIDENTS) AS SELECT POL_NO, ACC FROM CLAIMS89 WHERE STATE = 'MA' UNION ALL SELECT POL_NO, ACC FROM CLAIMS90 WHERE STATE = 'MA' UNION ALL SELECT POL_NO, ACC FROM CLAIMS91 WHERE STATE = 'MA'

You can use SELECT statements against the view to query the "group" of tables. However, you cannot update the file group through these views.

An example of a SELECT statement against the view is:

SELECT POLICY_NO FROM MA_POLICIES WHERE ACCIDENTS > 5

Mapping files with mixed record types

An example of views defined for mixed record type files follows. The example shows manually generated DDL that defines two views of the CLIENTS file from the Model 204 demonstration database. After mapping all the fields (regardless of record type) to a single base table, you define a view for each record type:

CREATE SCHEMA AUTHORIZATION GEORGE CREATE VIEW DRIVERS (DATE_OF_BIRTH, DRIVER_ID, FULLNAME, MARITAL_STATUS, POLICY_NO, SEX, STATE) AS SELECT DATE_OF_BIRTH, DRIVER_ID, FULLNAME, MARITAL_STATUS, POLICY_NO, SEX, STATE FROM CLIENTS WHERE RECTYPE = 'DRIVER' GRANT ALL PRIVILEGES ON DRIVERS TO PUBLIC CREATE VIEW POLICIES (ADDRESS, AGENT, ANNIV_DATE, CITY, DATE_OF_BIRTH, FULLNAME, POLICY_NO, POLICYHOLDER, STATE, TOTAL_PREMIUM, ZIP) AS SELECT ADDRESS, AGENT, ANNIV_DATE, CITY, DATE_OF_BIRTH, FULLNAME, POLICY_NO, POLICYHOLDER, STATE, TOTAL_PREMIUM, ZIP FROM CLIENTS WHERE RECTYPE = 'POLICYHOLDER' GRANT ALL PRIVILEGES ON POLICIES TO PUBLIC

For the sake of simplicity, these views do not include the CLIENTS nested table columns. These columns map to multiply occurring Model 204 fields.

Maintaining views

You are responsible for ensuring that your views remain valid over time. The Model 204 SQL Server does not warn you when a view is invalidated and does not prevent you from issuing DDL that renders a view invalid.

The DDL statements you can use for views are:

CREATE VIEW DROP VIEW GRANT REVOKE

You can change the roster of users that can access a view (with GRANT and REVOKE), but you cannot change the view definition itself. Once a view is defined, you can modify it only by deleting it (with DROP VIEW) and redefining a new one (with CREATE VIEW).

You can modify objects that the view references, but the modifications are not propagated to the view definition itself. For example, if you drop a table that is referenced by a view, the change is not propagated to the view definition. The view becomes invalid, yet the view definition remains in the catalog. You are not notified that your view is invalid until the time of DML query validation.

The Model 204 SQL Server does not delete invalid views from the SQL catalog unless you explicitly drop the view or drop the schema to which the view belongs.

Querying views

Queries against views whose definitions contain UNION ALL, GROUP BY, HAVING, or SELECT DISTINCT have the following restrictions.

Views defined with UNION ALL

SELECT statements in a query expression that contains the UNION operator cannot reference any views that have definitions that contain the UNION operator.

An SQL DML statement:

• that references a view that has a UNION operator cannot itself use a UNION operator.
• cannot reference in a subquery a view that has a UNION operator.
• that references a view that has a UNION operator cannot use that view in a join expression. That is, no other view or table can be specified in the FROM clause of the query specification used in the DML statement.

An SQL SELECT statement:

• that references a view that has a UNION operator cannot apply the GROUP BY clause nor any of the aggregating functions (COUNT, AVG, MAX, MIN, SUM).

Views defined with GROUP BY, HAVING, or SELECT DISTINCT

Query against a view defined with

• GROUP BY, HAVING, or both, can have no other views or tables in the FROM clause, and cannot have a WHERE, GROUP BY, or HAVING clause.
• SELECT DISTINCT can have no other views or tables in the FROM clause, and cannot have a GROUP BY or HAVING clause. Such queries must specify SELECT *.
• GROUP BY along with SELECT DISTINCT cannot have WHERE, GROUP BY, or HAVING clauses.

Setting the schema and user context

This section describes how to determine, indicate, and change the schema and user context, and introduces the SET SCHEMA and SET USER statements.

Determining the default schema context

At the beginning of an SQL session, the default schema context (name) is the Model 204 user ID established at login (with trailing blanks removed). The default schema name is assigned to any SQL objects you indicate in your DDL for the entire session, unless you specify another schema name. This default context is in effect except when a CREATE SCHEMA transaction is active (see Indicating schema name and owner). You can change the default context with the SET SCHEMA statement.

Prefixing the schema name to an SQL object

After SQL objects are created, you may need to make adjustments to your schemas to accommodate new users, changes to privileges, or new data. To revise SQL objects already defined in the SQL catalog, you cannot use CREATE SCHEMA, because it can be used only once per schema, that is, when you initially create the schema. Issuing a CREATE SCHEMA that names an existing schema is an error.

To selectively modify SQL objects that reside in different schemas, you need to identify the schema to which the objects belong. You can do so by specifying the schema name as qualification along with the name of the object (table, view) you are adding or modifying. For example:

schemaname.tablename

You can qualify the name of a column with a table name or with a table name and a schema name. For example:

schemaname.tablename.columname

If you do not specify the schema name along with the object, the schema name assigned is the current default schema.

You can qualify a table or view name only with the schema name that is the current default, if you are issuing CREATE TABLE or CREATE VIEW as part of a CREATE SCHEMA transaction. This restriction does not apply to GRANT statements in a CREATE SCHEMA transaction.

You can reset or change the current default schema by using SET SCHEMA.

Using SET SCHEMA

You can change the default schema name in SQL DDL or DML by using the following Model 204 SQL extension statement:

SET SCHEMA schemaname

SET SCHEMA defines the current default schema context. Statements following SET SCHEMA are assumed to apply to this schema. This default remains in effect for the entire session or until reset by another SET SCHEMA statement.

SET SCHEMA allows you to avoid continual specification of the schema name with SQL objects you are modifying or using. If no schema name is appended to an SQL object you add or modify after issuing SET SCHEMA, the schema name of the object defaults to the current setting of SET SCHEMA.

Any user can issue SET SCHEMA, because it has no effect on a schema definition other than establishing the context. Once the schema context is set, however, permission to operate with DDL or DML on the SQL objects in the schema depends on the individual statement. The privileges required to issue individual Model 204 SQL DDL statements are summarized in DDL statement-level security.

Using SET USER

In Model 204, authority to issue commands is based on the user role or type. A system manager (determined by login ID) typically is the pivotal user with greatest authority. In SQL, authority is based on object ownership, the authorization ID of the schema to which an object belongs. Model 204 SQL statement security combines these characteristics: to create schemas and tables, you must be a system manager and your login ID must match the authorization ID for the schema to which the object belongs.

However, this approach has the following drawback: to create a schema that has an SQL authorization ID other than the system manager's, the system manager has to give system manager privileges to that SQL user. The Model 204 SQL extension statement SET USER resolves this drawback.

SET USER, available to system managers only, changes the current SQL user context. A system manager issues SET USER ABC and in effect acquires the SQL authorization ID ABC and its associated SQL privileges. At the same time, the system manager retains Model 204 login ID privileges.

SET USER thus enables a kind of superuser, who can issue SQL statements for another SQL user without having to log in as that user or give that user system manager privileges. The system manager gains immediate access to, and authority to change, all defined SQL objects.

The SET USER syntax is:

Syntax

SET USER authorization-id

SET USER can be used with SQL DML or DDL statements. For an example showing how SET USER is used, see SQL statement security example.

Altering SQL objects

ALTER TABLE allows you to change the definition of a table. New columns may be added with the ADD clause. Existing columns may be modified with the MODIFY clause. Columns may be removed using the DROP clause.

ALTER TABLE also lets you shift the relative positions of the columns in a table. To protect against losing track of the column positions, you should always specify the column names when issuing an SQL INSERT.

ALTER TABLE statement

Syntax

ALTER TABLE tablename ADD column-definition | DROP columname | MODIFY column-parameters

Parameters

Where:

• column-definition has the following syntax:

  columname <datatype> [ SYSNAME 'fieldname' ] [ <column-constraint> ] ...

  column-constraint has the following syntax:

  [ NOT NULL ] [ UNIQUE | PRIMARY KEY [ SYSTEM ] ] | REFERENCES parent-table-name [ <referential-triggered-action> ]

  Individual parameters are described in CREATE TABLE statement and Creating nested tables.

• column-parameters has the following syntax:

  columname [datatype] [SYSNAME 'fieldname'] [[NOT] NULL | [NOT] UNIQUE]

• MODIFY is discussed in Using MODIFY column.
• NOT NULL column must map to a Model 204 field that has a non-null, nonempty value on every record in the Model 204 file. The Model 204 SQL Server does not allow you to violate this rule in an SQL DML update. If you add a NOT NULL column to an existing table, be sure the corresponding Model 204 field has non-null, nonempty values in all the records in the file.

  For more information about Model 204 SQL handling of nulls, see How Model 204 SQL processes dirty data.

Syntax rule

As described in Prefixing the schema name to an SQL object, the name of the table can be optionally specified along with the schema name as qualification:

schemaname.tablename

Using ADD column

ADD adds a column to a table but does not update any view definitions that reference the table.

Use ADD for adding columns to a table when column position in the table is not important.

With ADD, all columns are added to a table in the last position of the column list. If you DROP a column and then ADD an updated version of that column, the updated column occupies a different position in the table than it did before you executed DROP and ADD. Such a change in order of the column data can introduce errors into queries that use SELECT * or INSERT (without a column list) and that depend on the correct position of the column data.

Using DROP column

Dropping a column deletes the column from the table and deletes any privileges granted for this column. It has no effect on any view definitions that reference this column.

Note: Because tables cannot be left empty, you cannot drop the last column in a table. Because nested tables cannot be left with only a foreign key column, you cannot drop the last nonforeign key column in a nested table.

If you want to drop a referenced primary key column, you must first drop the nested table that contains the REFERENCES clause; if not, you receive an error message.

You cannot DROP a column that is part of a multicolumn unique key.

Using MODIFY column

Use MODIFY for the following operations:

• To change the UNIQUE or NOT NULL status of a column.
• To change a column definition other than making an addition or a deletion, use MODIFY instead of using DROP and ADD.
• To change a column's data type or field mapping clause or attributes.
• When the position of the column data in a table must not be disturbed.

MODIFY affects only the column definition elements you specify; the rest of the definition remains as is.

For example, for a column originally defined as INTEGER NOT NULL that you want to change to DECIMAL (11,2) NOT NULL, specify only:

ALTER TABLE tablename MODIFY columname DECIMAL (11,2)

You cannot ALTER or MODIFY an SQL object that does not already exist. You cannot ALTER or MODIFY a column that is part of a multicolumn unique key.

Only modify the UNIQUE or NOT NULL status of a column if the table is empty. If the UNIQUE or NOT NULL status of a column is modified on a nonempty table, you must ensure that this definition is compatible with the data in the existing Model 204 file. If it is not, an error might occur when processing an SQL request against this file.

Note: If more extensive modification to a table definition is required, you can drop the table and redefine it using CREATE TABLE as you did initially to set up the table. Remember, the Model 204 SQL catalog is not active, so dropping a table does not affect the actual file data.

Dropping SQL objects

You can delete SQL objects from the SQL catalog with the Model 204 SQL DROP statements and clause listed below. These deletion statements are discussed in turn in this section.

DROP TABLE <tablename> DROP VIEW <view name> DROP SCHEMA schemaname

Reminder: You can avoid an inadvertent or unanticipated loss of data due to deletion of SQL objects by backing up CCACAT before executing a DROP or by using the catalog reporting utility (CCACATREPT). With CCACATREPT you can generate a copy of the catalog DDL before you execute a DROP.

CCACATREPT is discussed in SQL catalog reporting and querying. For more information about backing up CCACAT, see Backup and restore.

Dropping tables

DROP TABLE causes the following actions to occur:

• Catalog entry for the table and its columns is deleted.
• Any privilege and constraint records that reference this table are deleted.

Once DROP TABLE eliminates all entries for a table from the SQL catalog, the table no longer exists in the SQL catalog. However, the Model 204 file associated with the table remains unaffected.

To avoid catalog data inconsistencies, drop SQL objects that depend on other objects before you drop the objects that are depended upon. For example, you must drop nested tables that reference a parent table before you drop the parent. Otherwise, your DROP TABLE statement is rejected. Also, drop any views associated with a table before you drop the table.

For convenience, you can qualify the table name by prefixing the schema name.

Dropping views

Views are permanent objects but do not map to Model 204 files or fields directly. The view's definition in terms of other tables or views is stored in the catalog in the form of a view record.

When you drop a view, the view record and all privilege records (records of users granted access to the view) associated with the dropped view are deleted from the catalog. DROP VIEW has no effect on the base table(s) associated with the view.

The only time the Model 204 SQL Server deletes a view from the SQL catalog is when you explicitly drop it or drop the schema to which it belongs. You can render a view invalid by deleting objects referenced by the view, but the invalid view definition remains in the catalog. You are notified of the invalidity when you next attempt to access the data files through this view.

For convenience, you can qualify the view name by prefixing the schema name.

Dropping schemas

Dropping a schema deletes the SCHEMA record in the catalog and all TABLE, PRIVILEGE, and CONSTRAINT records that reference this schema.

Dropping a schema also deletes views belonging to the schema and all PRIVILEGE records associated with them.

Granting privileges for SQL objects

To perform an operation on an SQL object, you must hold the necessary privilege for that combination of operation and object. That privilege might result from ownership of the object or from being granted that privilege by another user (with the GRANT statement). You can change privilege assignments by adding privileges with subsequent GRANT statements or by deleting privileges with the REVOKE statement.

This section provides the statement syntax for GRANT and REVOKE and discusses elements of their use that are special to Model 204 SQL.

GRANTs are for adding privileges

GRANT statements always and only add privileges. If you want to change privilege assignments, you can add privileges with subsequent GRANT statements. To delete or diminish the current level of privileges you must use REVOKE, not GRANT. The Model 204 SQL Server allows a new GRANT for an object to replace an earlier GRANT for that object only to the extent that the new GRANT expands the current set of privileges.

For example, if you try to reduce the current privileges for an object by issuing a new GRANT that allows fewer privileges, the new GRANT is ignored. To reduce the scope of given privileges you must use REVOKE.

GRANT and REVOKE handle nearly all SQL security

SQL access to a Model 204 file is protected exclusively by Model 204 login security and Model 204 SQL GRANT and REVOKE statements. Existing Model 204 file access security is not enforced by the Model 204 SQL Server.

Since GRANT and REVOKE are the principal security sources, the final SQL file access safeguard is SQL statement security. That is, you can permit only certain users per SQL object to issue GRANT and REVOKE statements.

For more information about the privileges required for execution of the individual Model 204 SQL DDL statements, see DDL statement-level security.

GRANT statement

The GRANT statement is the privilege definition option of the CREATE SCHEMA statement. The syntax is:

Syntax

GRANT <privileges> ON <object-name> TO <grantee> [,<grantee> ] ... [ WITH GRANT OPTION ]

Parameters

where:

• privileges has the following syntax:

  ALL PRIVILEGES | action [,action ] ...

• action has the following syntax:

  SELECT | INSERT | DELETE | UPDATE [ ( columname [,columname ] ... ) ]

• object-name is the table or view name.
• grantee has the following syntax:

  PUBLIC | authorization-id

Usage notes

The privileges you can grant apply only to DML operations for specified SQL objects or to defining which users can issue additional GRANT statements for specified SQL objects. For information about privileges for issuing DDL statements, see DDL statement-level security.

Model 204 SQL DDL has no REFERENCES privileges.

Unlike the other privileges, the UPDATE option can be applied to a specified list of columns. If no column list is specified with UPDATE, by default it is assumed that all columns in the table are included.

You can use UPDATE to provide privileges for selective access to certain columns in the database. However, using UPDATE with a column list typically yields poorer performance than using a view of these columns and granting selective access through the view.

You cannot grant UPDATE privileges on the columns of a system-generated primary key, because such a key by definition cannot be updated.

Whether you specify UPDATE by itself or specify UPDATE followed by a list of the columns in the table, you can affect subsequent privilege assignments. This is discussed further in Granting and altering column UPDATE privileges.

REVOKE statement

The REVOKE statement is a Model 204 SQL extension with which you can revise the privileges given by the GRANT statement. A table's owner can REVOKE privileges for any authorization ID. Other users can REVOKE privileges for those rights they were granted with the WITH GRANT OPTION.

The syntax is:

Syntax

REVOKE [ GRANT OPTION FOR ] privileges ON object-name FROM grantee [,grantee ] ...

Parameters

where:

• privileges is the same as for the GRANT statement.
• object-name is the same as for the GRANT statement.
• grantee has the following syntax:

  PUBLIC | authorization-id

Usage notes

Each time you issue REVOKE, it revokes one of the following:

• Entire privilege (SELECT, UPDATE, INSERT, or DELETE) or list of privileges
• Ability of the specified user or users to grant this privilege to another user

Revocation of privileges does not cascade. That is, if your privileges to grant updates on a particular table are revoked, the update privileges for that table you can have granted to other users are not revoked. For example, USERA grants update privileges with grant option on TABLET to USERB, and USERB grants the same privileges to USERC. If USERA later revokes USERB'S TABLET privileges, USERC's TABLET privileges are not affected.

Similarly, if USERB's privileges to grant updates on TABLET are revoked, the revoking action does not cascade to VIEWV, which references TABLET. USERB can still use VIEWV, and USERC can still use VIEWV.

You receive an error message if you issue REVOKE against an unauthorized user (or against an authorized user whose name is misspelled).

Granting and altering column UPDATE privileges

The REVOKE and ALTER TABLE statements in Model 204 SQL DDL allow for changes over time to the columns of a table and to the privileges for updating those columns. To avoid unwanted effects from such changes over time, you need to understand how the Model 204 SQL Server handles grants of column updating privileges.

The Model 204 SQL catalog stores your column UPDATE privileges for a table in one of the following ways:

• Single marker that indicates you can update all the columns in the table
• List of the individual columns you can update

The method of storage depends on the form of the UPDATE clause used to assign your privileges. The first method results from a GRANT statement UPDATE clause that does not specify an individual column list. By default, all columns in the table are included in the privilege. The second method results from a GRANT statement UPDATE clause that specifies an individual column list.

The storage method is significant, because subsequent changes to the table's columns or privileges can produce different outcomes depending on the initial storage method.

Column UPDATE examples

This section has a series of examples showing how the effects on a user's table column privileges of subsequent ALTER TABLE, REVOKE, and GRANT statements can depend on the format of the UPDATE clause of the initial GRANT statement.

Effect of ADD and DROP in ALTER TABLE statement

Assuming table TABLEZ has columns COL_A, COL_B, and COL_C and proper grant authorization, consider the following GRANT statements, both of which grant UPDATE privileges to each of the columns in TABLEZ:

GRANT UPDATE ON TABLEZ TO JUAN

GRANT UPDATE (COL_A, COL_B, COL_C) ON TABLEZ TO MARIA

The Model 204 SQL Server stores this information approximately as follows, where * means all columns, and GRANT OPTION, which refers to the WITH GRANT OPTION of GRANT UPDATE, is N (no) unless specified in the GRANT statement:

USER	PRIVILEGE	COLUMNS	GRANT OPTION
JUAN	UPDATE	*	N

USER	PRIVILEGE	COLUMNS	GRANT OPTION
MARIA	UPDATE	COL_A	N
		COL_B	N
		COL_C	N

Now, note the effect of the following statement on Juan and Maria's UPDATE privileges:

ALTER TABLE TABLEZ ADD COL_D

Juan's stored UPDATE privilege information, though physically unchanged, now includes the ability to update the newly added COL_D. However, Maria's unchanged privileges do not include the ability to update COL_D.

Continuing, note the effect of the following statements on Juan and Maria's UPDATE privileges:

ALTER TABLE TABLEZ DROP COL_A ALTER TABLE TABLEZ ADD COL_A

Juan's stored UPDATE privilege information remains physically unaffected, and he can still update all the columns in TABLEZ: COL_A, COL_B, COL_C, and COL_D. However, Maria's UPDATE privilege for COL_A gets dropped when COL_A is dropped from TABLEZ. (See Using DROP column for the additional actions propagated when a column is dropped.) If COL_A is added back to the table, Maria's UPDATE privileges do not change and she can update only COL_B and COL_C.

Effect of REVOKE

Continuing the conditions of the previous example, note the effect of the following REVOKE statement. Remember, Juan can update all columns in TABLEZ, namely, COL_A, COL_B, COL_C, and COL_D.

REVOKE UPDATE ( COL_C ) ON TABLEZ TO JUAN

The Model 204 SQL Server must change the storage format and store Juan's privilege information in list format:

USER	PRIVILEGE	COLUMNS	GRANT OPTION
JUAN	UPDATE	COL_A	N
		COL_B	N
		COL_D	N

If the REVOKE statement had preceded the ALTER TABLE statements in the example, Juan's UPDATE privileges would have been affected like Maria's were. That is, instead of automatically expanding and contracting with changes to the table, they would apply only to the explicitly named original columns, and they could be dropped if a table column were dropped.

Effect of WITH GRANT OPTION

Return to the example situation for Juan after the first GRANT statements and before the ALTER TABLE statements. Juan's UPDATE privileges are stored as follows:

USER	PRIVILEGE	COLUMNS	GRANT OPTION
JUAN	UPDATE	*	N

Note the effect of the following statements on Juan's UPDATE privileges:

GRANT UPDATE (COL_A) ON TABLEZ TO JUAN GRANT UPDATE (COL_A) ON TABLEZ TO JUAN WITH GRANT OPTION

The first GRANT statement has no effect on Juan's privileges, which already include the privilege to update COL_A. But the WITH GRANT OPTION of the second GRANT statement introduces information that Juan's stored privileges do not include. Consequently, the Model 204 SQL Server stores Juan's privilege information in list format as follows:

USER	PRIVILEGE	COLUMNS	GRANT OPTION
JUAN	UPDATE	COL_A	Y
		COL_B	N
		COL_C	N

DDL statement-level security

The following table displays the privileges required for execution of the individual Model 204 SQL DDL statements. These privilege requirements are checked when you submit your DDL to the CVI utility.

System manager privileges are determined by the Model 204 login user ID.

This section also contains a discussion of view privileges and an example showing the application of statement security rules, especially for CREATE VIEW and GRANT.

Note: You can replace Model 204 SQL statement security with privilege checking by an external security package. You provide user exits to the security package in a Model 204-defined format, as described in the SQL security exits topic.

The SQL Server passes to the user exit all the information necessary to perform privilege checking identical to the Model 204 SQL privilege checking. The extent of the checking done is an option of the user exit.

DDL statement security

To issue...	You must have...	Comments
CREATE SCHEMA	Model 204 system manager privileges     - AND - The schema authorization ID must match the Model 204 login ID of the user issuing the CREATE SCHEMA statement	Ensure the match between login ID and authorization ID by issuing SET USER before CREATE SCHEMA. If no authorization ID is specified in the CREATE, the logon ID is used.
CREATE TABLE	Model 204 system manager privileges     - AND - A Model 204 login ID that matches the authorization ID for the schema containing the table	Ensure the match between login ID and authorization ID by using SET USER. If CREATE TABLE is part of a CREATE SCHEMA transaction, you cannot create a table that is qualified by a schema name other than the schema that is the current default. The table owner (containing schema's authorization ID) automatically gets all privileges (including WITH GRANT OPTION) for the table. The Model 204 file to which the table maps is not opened during the processing of the CREATE TABLE DDL.
CREATE VIEW	A Model 204 login ID that matches the authorization ID for the view's schema and an authorization ID that matches the authorization ID for every object referenced in the view (that is, you own the schema and all the objects referenced in the view)     - OR - A Model 204 login ID that matches the authorization ID for the view's schema and the authorization ID for the view's schema has at least SELECT privileges on every object referenced in the view	You cannot create a view that is qualified by a schema name other than the schema that is the current default. The view owner (containing schema's authorization ID) automatically gets view privileges that match the level of privileges for the objects referenced in the view. If the view is not logically updateable, the level of privileges the view owner automatically gets may not exceed SELECT privileges.
DROP SCHEMA	Model 204 system manager privileges     - AND - A Model 204 login ID that matches the authorization ID for the schema	Ensure the match between login ID and authorization ID by issuing SET USER before DROP SCHEMA.
DROP TABLE	Model 204 system manager privileges     - AND - A Model 204 login ID that matches the authorization ID for the schema containing the table	The authorization ID of the schema that contains the table must match the Model 204 login ID of the user issuing the DROP TABLE statement. Ensure the match between login ID and authorization ID by issuing SET USER before DROP TABLE.
DROP VIEW	A Model 204 login ID that matches the authorization ID for the schema containing the view
ALTER TABLE	Model 204 system manager privileges     - OR - A Model 204 login ID that matches the authorization ID for the schema containing the table	Ensure the match between login ID and authorization ID by issuing SET USER before ALTER TABLE.
GRANT (for a table)	A Model 204 login ID that matches the authorization ID for the schema containing the table on which the privilege is being granted     - OR - By the WITH GRANT OPTION clause of some previous GRANT statement, that specifically granted the right to GRANT privileges of the named type for this table	You can GRANT privileges for a table that is qualified by a schema name other than the schema that is the current default.
GRANT (for a view)	A Model 204 login ID that matches the authorization ID for the schema containing the view on which the privilege is being granted, and also the authorization ID for the view's schema must have the corresponding privilege with WITH GRANT OPTION on all tables and views referenced in any part of the view definition     - OR - By the WITH GRANT OPTION clause of some previous GRANT statement, that specifically granted the right to GRANT privileges of the named type for this view	You can GRANT privileges for a view that is qualified by a schema name other than the schema that is the current default.
REVOKE	A Model 204 login ID that matches the authorization ID for the schema containing the object on which the privilege is being revoked     - OR - By the WITH GRANT OPTION clause of some previous GRANT statement, that specifically granted the right to GRANT privileges of the named type for this object	REVOKE only deletes privileges from the object named in the REVOKE statement. The revoking action does not cascade to views that reference the object.
SET SCHEMA	Any Model 204 SQL user	The DDL statements you can issue within this schema context are determined at the specific statement level.
SET USER	Model 204 system manager privileges	Sets the current SQL authorization ID for the duration of the user session or until the next SET USER.

Model 204 SQL view privileges

View creation privileges are designed to uphold the security definition of the base tables and columns referenced in the view. These base objects require protection because privileges to use a view are checked when you issue a DML statement against the view, while the privileges for objects referenced by the view are checked when DDL is processed. Model 204 SQL DDL statement security, therefore, prevents you from using a view to circumvent the privileges for the base objects referenced by the view.

Three of the fundamental principles upheld by Model 204 SQL statement security are:

• You can only create views that satisfy the following rule: the authorization ID for the view's schema must have at least SELECT privileges on every object referenced in any part of the view definition. This rule holds whether you are a system manager or the owner of the view.
• You can neither create nor grant privileges for a view that makes the view base objects more accessible than your privileges for the base objects allow. For example, you cannot grant update privileges for a view if you have only the SELECT privilege on the objects referenced by the view.
• You can update a base object through a view only if you are the owner of the object or if you are granted the privilege to update from the owner of the object.

Since only the privilege records for the view and not for the base objects referenced by the view are checked when a DML request is processed, a view definition stands on its own. The view definition does not automatically reflect any changes to its base objects that occur after the view is created. If a base object referenced in a view is deleted, the view definition is not changed. You are responsible for the integrity over time of your view definitions.

SQL statement security example

This section contains an example of a sequence of SQL DDL transactions and CVI utility messages. Following the example are explanatory comments.

The example shows the effect of some of the basic rules governing DDL statement security, especially for view definition and the granting of view privileges. The example uses the demonstration database files (CLIENTS, VEHICLES and CLAIMS03).

In the example, an INSURANCE schema is created containing:

• Three base tables
• Three views, one per base table
• Three schemas, corresponding to agents
• Privileges for each object

The following SQL objects, contained in the agent schemas, are defined by the schema owners:

• Views against the base tables
• Views against other views
• Privileges for each object

MISMAN is the only system manager. MISDEPT and BOB are common users. JOHN and MARY are agents. Transactions are followed by messages with return codes indicating the validity of the transaction. Boldface numbers to the right of the example statements are used in the comments following the example.

Example

User MISDEPT logs in (not a system manager):

1

CREATE SCHEMA INSURANCE AUTHORIZATION MISDEPT) SQL Error -551: User MISDEPT does not have the privilege to perform operation CREATE on object SCHEMA.

System manager logs in (user MISMAN):

2

SET USER MISDEPT Completion Code: 0

3

CREATE SCHEMA INSURANCE AUTHORIZATION MISDEPT CREATE TABLE CLIENTS ··· GRANT SELECT ON CLIENTS TO BOB WITH GRANT OPTION CREATE TABLE VEHICLES ··· GRANT SELECT ON VEHICLES TO BOB CREATE TABLE CLAIMS03 ··· CREATE VIEW CLIENTS_V AS SELECT * FROM CLIENTS WHERE AGENT = USER GRANT ALL PRIVILEGES ON CLIENTS_V TO PUBLIC WITH GRANT OPTION CREATE VIEW VEHICLES_V AS SELECT * FROM VEHICLES WHERE AGENT = USER GRANT ALL PRIVILEGES ON VEHICLES_V TO JOHN WITH GRANT OPTION CREATE VIEW CLAIMS03_V AS SELECT * FROM CLAIMS03 WHERE AGENT = USER GRANT ALL PRIVILEGES ON CLAIMS03_V TO MARY Completion Code: 0

4

SET USER BOB Completion Code: 0

5

CREATE SCHEMA AUTHORIZATION BOB Completion Code: 0

6

SET USER JOHN Completion Code: 0

7

CREATE SCHEMA AGENT_J16 AUTHORIZATION JOHN Completion Code: 0

8

SET USER MARY Completion Code: 0

9

CREATE SCHEMA AGENT_M05 AUTHORIZATION MARY Completion Code: 0

User BOB logs in (not a system manager):

10

SET SCHEMA BOB Completion Code: 0

11

CREATE VIEW YOUNG_DRIVERS AS SELECT * FROM INSURANCE.CLIENTS WHERE DATE_OF_BIRTH > 671231 Completion Code: 0

12

GRANT SELECT ON YOUNG_DRIVERS TO PUBLIC Completion Code: 0

13

CREATE VIEW STOLEN_CARS AS SELECT * FROM INSURANCE.VEHICLES WHERE INCIDENT = 'ST' Completion Code: 0

14

GRANT SELECT ON STOLEN_CARS TO PUBLIC SQL Error -551: User BOB does not have the privilege to perform operation GRANT on object STOLEN_CARS.

15

CREATE VIEW COLLISIONS AS SELECT * FROM INSURANCE.CLAIMS03 WHERE CLAIM_TYPE = 'C' SQL Error -551: BOB does not have the privilege to perform operation SELECT on object CLAIMS03.

User JOHN logs in (not a system manager):

16

SET SCHEMA AGENT_J16 Completion Code: 0

17

CREATE VIEW MYVEHICLES AS SELECT * FROM INSURANCE.VEHICLES SQL Error -551: JOHN does not have the privilege to perform operation SELECT on object VEHICLES.

18

CREATE VIEW MYVEHICLES AS SELECT * FROM INSURANCE.VEHICLES_V Completion Code: 0

19

GRANT SELECT ON MYVEHICLES TO MARY Completion Code: 0

User MARY logs in (not a system manager):

20

SET SCHEMA AGENT_M05 Completion Code: 0

21

CREATE VIEW MYCLAIMS03 AS SELECT * FROM INSURANCE.CLAIMS03 SQL Error -551: MARY does not have the privilege to perform operation SELECT on object CLAIMS03.

22

CREATE VIEW MYCLAIMS03 AS SELECT * FROM INSURANCE.CLAIMS03_V Completion Code: 0

23

GRANT SELECT ON AGENT_J16.MYVEHICLES TO JOHN SQL Error -551: MARY does not have the privilege to perform operation GRANT on object MYVEHICLES.

24

CREATE VIEW MYCLIENTS AS SELECT * FROM INSURANCE.CLIENTS_V Completion Code: 0

System manager logs in (user MISMAN):

25

SET USER MISDEPT Completion Code: 0

26

DROP SCHEMA INSURANCE Completion Code: 0

27

SET USER BOB Completion Code: 0

28

DROP SCHEMA BOB Completion Code: 0

29

SET USER JOHN Completion Code: 0

30

DROP SCHEMA AGENT_J16 Completion Code: 0

31

SET USER MARY Completion Code: 0

32

DROP SCHEMA AGENT_M05 Completion Code: 0

Statement security example comments

• Statement 1 fails because MISDEPT is not a system manager.
• Statement 2 allows the system manager, in effect, to log in as user MISDEPT. This is necessary so the system manager can define the INSURANCE schema to have owner MISDEPT. SQL objects can be created only by their owners. That is, the login ID of the user issuing an SQL CREATE must match the specified or implied authorization ID of the current schema.
• Statement 3 makes MISDEPT the owner of the INSURANCE schema and all the objects in the schema. Note that although system manager MISMAN issued the CREATE SCHEMA for INSURANCE, the privileges to access INSURANCE are automatically granted only to the object's owner MISDEPT. MISMAN cannot access INSURANCE unless the system manager explicitly includes a GRANT of privileges to MISMAN for INSURANCE.
• Statements 4, 6, and 8 allow the system manager to create schemas owned by the specified users. Later in the example, the users define the SQL objects that belong to their schemas.
• Unlike statements 7 and 9, statement 5 does not include a schema name value, so the default name is the authorization ID, BOB.
• Statement 10 is necessary to change the schema context (from MARY to BOB) so that BOB can add objects to his schema. Statements 16 and 20 are similar.
• Statements 11 and 13 are valid, because view owner BOB was granted (by MISMAN) the SELECT privilege for the tables referenced in his views. BOB cannot create a view that references the CLAIMS03 table, for example, because he does not have any privileges for that table (see statement 15).

  Statements 11 and 13 also show the use of the schema name, INSURANCE, as a qualifier for the CLIENTS and VEHICLES tables. Without the qualifier, CLIENTS is assumed to belong to the default schema, BOB, and the statement fails.

• Statement 12 is valid, since BOB was granted the SELECT privilege WITH GRANT OPTION on the referenced table (CLIENTS). The SELECT privilege and WITH GRANT OPTION is implicitly granted to the YOUNG_DRIVERS view owner (BOB). That is, the maximum privileges available for the view are equal to those that the view owner has on the object referenced by the view, and these privileges are also implicitly granted to the view owner for the view.
• Statement 14 fails because the SELECT privilege on the referenced table VEHICLES was granted to BOB not including WITH GRANT OPTION. View STOLEN_CARS is created with only the SELECT privilege (not including WITH GRANT OPTION) implicitly granted to the view owner.

  STOLEN_CARS view is, therefore, viewable only by BOB. No one else (including MISMAN) can SELECT this view, and no one (including BOB) can update the view.

• Statements 15, 17, and 21 fail, because the view owners-to-be lack the SELECT privilege for objects they want to reference in these views.
• Statement 18 creates MYVEHICLES, a view of a view. All privileges are implicitly granted to the MYVEHICLES owner (JOHN), including WITH GRANT OPTION, because MYVEHICLES is logically updateable and JOHN has all privileges on the referenced view VEHICLES_V. If the view were not logically updateable, the implicit owner privileges would be SELECT WITH GRANT OPTION.
• Statement 19 succeeds because JOHN's privileges for MYVEHICLES (which equal his privileges for the referenced view VEHICLES_V) include WITH GRANT OPTION.
• Statement 22 creates MYCLAIMS03, a view of a view. All privileges are implicitly granted to the MYCLAIMS03 owner (MARY), not including WITH GRANT OPTION, because MYCLAIMS03 is logically updateable and MARY has all privileges on the referenced view VEHICLES_V.
• Statement 23 fails because MARY's privileges for MYVEHICLES (granted in statement 19) do not include WITH GRANT OPTION.
• Statement 24 creates MYCLIENTS, a view of a view. All privileges are implicitly granted to the MYCLIENTS owner (MARY), including WITH GRANT OPTION, because MYCLIENTS is logically updateable and all privileges on the referenced view VEHICLES_V are granted to PUBLIC WITH GRANT OPTION.
• Statements 25 through 32 show the system manager logging in as various users and then deleting from the SQL catalog (with DROP) the schemas owned by each user. This method is necessary because to drop a schema you must be the schema owner and a system manager. That is, the login ID of the user issuing an SQL DROP SCHEMA must match the specified or implied authorization ID of the current schema; and the user issuing an SQL DROP SCHEMA must be a system manager.

SQL DDL processing

Each DDL statement is committed upon successful execution without regard to the SQL Auto Commit setting for the data source.

See also

• SQL Server overview
• SQL Server installation
• SQL catalog
• SQL representation of Model 204 data
• SQL Data Definition Language (DDL)
• SQL DDL from the Table Specification Facility
• SQL catalog reporting and querying
• SQL Data Manipulation Language (DML)
• SQL DDL syntax
• SQL reserved words
• SQL DDL mapping of the demonstration database