ALTER SCHEMA

The ALTER SCHEMA data description statement that modifies the definition of a schema in the dictionary. It is also a CA IDMS extension of the SQL standard.

Authorization

To issue an ALTER SCHEMA statement, you must hold the ALTER privilege on the schema named in the statement.

If you specify FOR NONSQL SCHEMA, you must have the USE privilege on the non-SQL schema.

If you specify DBNAME, you must have USE privilege on the database; if you do not specify DBNAME or specify a value of NULL, you must have DBADMIN privilege on DBNAME SYSTEM.

Syntax

►►─── ALTER SCHEMA schema-name ───────────────────────────────────────────────►

 ►─┬─────────────────────────────────────────────────┬────────────────────────►◄
   ├─ DEFAULT AREA ─┬─ segment-name.area-name ─┬─────┤
   │                └─ NULL ───────────────────┘     │
   ├─ DBNAME ─┬─ database-name ─┬────────────────────┤
   │          └─ NULL ──────────┘                    │
   ├─ FOR NONSQL SCHEMA nonsql-schema-specification ─┤
   └─ FOR SQL SCHEMA sql-schema-specification ───────┘

Expansion of nonsql-schema-specification (ALTER SCHEMA)

►►─┬────────────────────┬─ nonsql-schema-name ─┬──────────────────────────┬───►
   └─ dictionary-name. ─┘                      └─ VERSION version-number ─┘

 ►─┬──────────────────────────────┬───────────────────────────────────────────►◄
   └─ DBNAME ─┬─ database-name ─┬─┘
              └─ NULL ──────────┘

Expansion of sql-schema-specification (ALTER SCHEMA)

►►──────────────────────── sql-schema-name ───────────────────────────────────►

 ►─┬──────────────────────────────┬───────────────────────────────────────────►◄
   └─ DBNAME ─── database-name ───┘

Parameters

schema-name

Specifies the name of the schema being modified. Schema-name must identify a schema defined in the dictionary.

DEFAULT AREA

Modifies the default area specification for the named schema. This parameter is valid only for a schema that is not associated with a non-SQL-defined schema.

The named area is used by default for storing rows of tables subsequently defined in the named schema. It replaces any previous default area specification for the schema.

segment-name.area-name

Identifies the segment and area.

You do not need to define the named segment or area in the dictionary before issuing the ALTER SCHEMA statement.

NULL

Removes any previous default area specification for the named schema.

If the default area specification is removed, all subsequent CREATE TABLE statements that qualify the table name with the name of the schema being altered must include the IN parameter.

DBNAME

If the schema has been associated with a non-SQL-defined schema, you can add or change the specification of the database using this parameter.

Descriptions of the database-name and NULL parameters are presented under nonsql-schema-specification.

nonsql-schema-specification

Identifies the non-SQL-defined schema to associate with the SQL schema.

Expanded syntax for nonsql-schema-specification appears immediately following the statement syntax. Descriptions for these parameters are located at the end of this section.

sql-schema-specification

Identifies an existing SQL-defined schema to which the new SQL schema refers. Expanded syntax for sql-schema-specification appears immediately following the statement syntax.

Parameters for Expansion of nonsql-schema-specification

dictionary-name.

Names the dictionary that contains the non-SQL-defined schema.

If you do not specify dictionary-name, it is set to the dictionary to which your SQL session is connected.

nonsql-schema-name

Identifies the non-SQL-defined schema.

VERSION version-number

Identifies the version number of the non-SQL-defined schema. If VERSION version-number is not specified, version-number defaults to 1.

DBNAME

Specifies the database that the non-SQL-defined schema describes or removes a database specification.

For considerations about whether to specify a database when you create a schema for a non-SQL-defined schema, see the "Usage" section of CREATE SCHEMA.

database-name

Identifies one of the following:

The segment containing the areas described by the non-SQL-defined schema.
A database name that includes segments containing the areas described by the non-SQL-defined schema.
At runtime CA IDMS accesses the segments associated with the database name that contain areas with the same name as the areas in the non-SQL-defined schema.

NULL

Initializes the database name for the non-SQL-defined schema to blanks.

If no database-name is specified in the schema definition, at runtime the database name to which the SQL session is connected must include segments containing the areas described by the non-SQL-defined schema.

Parameters for Expansion of sql-schema-specification

sql-schema-name: Names the referenced SQL-defined-schema. this named schema must not itself reference another schema.
DBNAME database-name: Identifies the database containing the data described by the referenced SQL-defined schema. Database-name must be a database name that is defined in the database name table or a segment name defined in the DMCL.

Usage

System-owned Schema

You cannot modify the definition of the schema named SYSTEM.

Changing non-SQL-defined Schema Information

If you change the name or version number of the non-SQL defined schema associated with an SQL-defined schema or if you change the database name associated with the schema, you must recompile all affected access modules and drop and recreate all affected views.

To determine which access modules are affected, use the DISPLAY ALL ACCESS MODULE statement with the TABLE selection criteria.

To recompile an affected access module, use the ALTER ACCESS MODULE statement with the REPLACE ALL option.

Views must be dropped and recreated if the structure of one or more referenced records in the new non-SQL-defined schema is different than the structure at the time the view was created. Views are also invalid if a referenced record has been deleted from the non-SQL schema. To determine which views are affected, use the DISPLAY ALL VIEW statement with the REFERENCEd selection criteria. Before dropping the view, display its syntax by using the DISPLAY or PUNCH VIEW statement.

Restricted Changes

You cannot alter the type of a schema:

You cannot change a non-referencing schema to a referencing schema or a referencing schema to a non-referencing schema.
You cannot change the type of schema being referenced from SQL to non-SQL or from non-SQL to SQL.

Changing Referenced SQL Schema Information

If you change the name of the SQL schema that is referenced, you must drop and recreate all views that reference tables in the referencing schema, for example, the schema being altered. To determine which views are affected, use the DISPLAY ALL VIEW statement with the REFERENCED selection criteria. Before dropping the view, display its syntax by using the DISPLAY or PUNCH VIEW statement.

Example

Removing the Default Area Specification

The following ALTER SCHEMA statement removes the default area specification from the SALES schema:

alter schema sales
   default area null;

More Information

For more information about defining schemas, see CREATE SCHEMA and DROP SCHEMA.
For more information about non-SQL schemas, see Accessing Non-SQL-Defined Databases.
For more information about displaying access modules and views, see DISPLAY/PUNCH ACCESS MODULE and DISPLAY/PUNCH VIEW.