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.
idmscu19
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.
This article describes the following information:
2
2
Authorization
To issue an ALTER SCHEMA statement, you must hold the ALTER privilege on the schema named in the statement.
If you specify FOR NETWORK SCHEMA, you must have privilege on the network 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 SQL SCHEMA 
sql-schema-name
 ─────────────────────────────────┤    ├─ FOR ─┬─ NETWORK ─┬─ SCHEMA 
network-schema-specification
 ────────┤    │       └─ NONSQL ──┘                                              │     ├─────── WITH VIRTUAL KEYS ────────────────────────────────────────┤    └─────── WITHOUT VIRTUAL KEYS ─────────────────────────────────────┘    
Expansion of network-schema-specification (ALTER SCHEMA)
  ►►─┬────────────────────┬─ 
network-schema-name
 ─┬──────────────────────────┬───►    └─ 
dictionary-name
. ─┘                       └─ VERSION 
version-number
 ─┘  
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 network-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 network-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
    network-schema-specification
    .
  • network-schema-specification
    Identifies the network-defined schema to associate with the SQL schema.
    Expanded syntax for
    network-schema-specification
    appears immediately following the statement syntax. Descriptions for these parameters are located at the end of this section.
    The keyword NONSQL is still supported for upward compatibility purposes.
  • 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 network-schema-specification
  • dictionary-name
    .
    Names the dictionary that contains the network-defined schema.
    If you do not specify
    dictionary-name
    , it is set to the dictionary to which your SQL session is connected.
  • network-schema-name
    Identifies the network-defined schema.
  • VERSION
    version-number
    Identifies the version number of the network-defined schema. If VERSION
    version-number
    is not specified,
    version-number
    defaults to 1.
  • DBNAME
    Specifies the database that the network-defined schema describes or removes a database specification.
    For considerations about whether to specify a database when you create a schema for a network-defined schema, see the "Usage" section of CREATE SCHEMA.
  • database-name
    Identifies one of the following:
    • The segment containing the areas described by the network-defined schema.
    • A database name that includes segments containing the areas described by the network-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 network-defined schema.
  • NULL
    Initializes the database name for the network-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 network-defined schema.
  • WITH VIRTUAL KEYS
    The list of visible columns of the tables representing the network records is extended with the ROWID pseudo-column and all virtual foreign key columns associated with the sets of which the record is a member. All virtual foreign key columns become referenceable in statement syntax. A SELECT * from a table, and the CA IDMS Server metadata functions returning the columns of a table, contain all the visible columns with the columns corresponding to the record elements listed first, in the order of the record definition, followed by the ROWID and the virtual foreign key pseudo-columns in alphabetical order.
  • WITHOUT VIRTUAL KEYS
    The list of visible columns of the tables representing the network records does not include the ROWID pseudo-column, nor any virtual foreign key columns. Virtual foreign key columns are not referenceable, but ROWID is referenceable according to the ROWID syntax rules in the ROWID Pseudo-column section.
    WITHOUT VIRTUAL KEYS must be explicitly specified to remove the WITH VIRTUAL KEYS attribute.
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 network-defined Schema Information
If you change the name or version number of the network 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 network-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 network 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 network or from network 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