ALTER CATALOG

The ALTER CATALOG statement establishes the character set for character columns of the tables defined within the current dictionary. The character set is used to correctly sort additional national characters for the named language. It is also a CA IDMS extension of the SQL standard.

Authorization

To issue an ALTER CATALOG statement, you must have DBADMIN authority, and ALTER authority for the SYSTEM schema.

Note: This statement must be executed immediately following the execution of the TABLEDDL file that records the definition of the catalog tables themselves. The statement must precede the definition of any user SQL tables in that catalog.

Syntax

►►─── ALTER CATALOG DEFAULT CHARACTER SET alternate-character-set-name ───────►◄

Parameters

alternate-character-set-name: Identifies the character set whose collating scheme is to be used for the CHAR and VARCHAR columns defined in the catalog. Currently-supported values for alternate-character-set-name are DENMARK, FINLAND, NORWAY, and SWEDEN.

Usage

The ALTER CATALOG DEFAULT CHARACTER SET statement changes the collating sequence for all CHAR and VARCHAR columns defined in user tables in the dictionary, including non-SQL defined tables. It does not affect columns in the SYSTEM tables.

Swedish and Finnish

The additional national characters, the uppercase forms of which are represented by the symbols $, #, and &theta., sort at the end of the standard alphabet. The accented E and U sort the same as their unaccented equivalents, and are returned to application programs as their unaccented equivalents.

Norwegian and Danish

The additional national characters, the uppercase form of which is represented by the symbols #, &theta., and $, sort at the end of the standard alphabet. The accented U sort in the same sequence as its unaccented equivalent and are returned to application programs as an unaccented U.

Example

The following statement causes the data values of all CHAR and VARCHAR columns to collate according to the conventions of the Finnish alphabet.

alter catalog default character set FINLAND

ALTER CONSTRAINT

The ALTER CONSTRAINT statement changes the characteristics of an existing referential constraint. This statement is a CA IDMS extension to the SQL standard.

Authorization

To issue an ALTER CONSTRAINT statement, you must:

Either hold the ALTER privilege on or own the referencing table in the constraint being altered
Hold the REFERENCES privilege on the referenced table in the constraint being altered

Note: To issue an ALTER CONSTRAINT statement you must own or hold the ALTER privilege on the table on which the constraint is defined.

Syntax: ALTER CONSTRAINT

►►─ ALTER CONSTRAINT constraint-name ON ───────────────────────────►

 ►───────────┬────────────────┬─ referencing-table ────────────────►
             └─ schema-name. ─┘

   ┌───────────────────────────────────────────┐
 ►─▼─┬─ INDEX BLOCK CONTAINS key-count KEYS ─┬─┴───────────────────►◄
     ├─ DISPLACEMENT IS page-count PAGES ────┤
     └─┬───────┬─ UNIQUE ────────────────────┘
       └─ NOT ─┘

ALTER CONSTRAINT Parameters

This section describes the ALTER CONSTRAINT parameters:

constraint-name

Identifies the referential constraint to be changed. Constraint-name must be the name of a constraint on the table identified in the ON clause.

referencing-table

Specifies the name of the referencing table in the constraint to be changed.

schema-name

Identifies the schema associated with the referencing table.

Default: The default varies depending on where the statement is encountered.

If you do not specify schema-name, it defaults to:

The current schema associated with your SQL session when the statement is entered through the Command Facility or executed dynamically.
The schema associated with the access module used at runtime when the statement is embedded in an application program.

key-count KEYs

Establishes a new value for the maximum number of entries in each internal index record (SR8 system record).

Limits: Key-count must be an unsigned integer in the range 3 through 8180.

page-count PAGES

Specifies how far away from the referenced row the bottom-level index records are stored.

If the value of page-count is zero (0), the bottom-level internal index records are not displaced from the referenced row.

Limits: Page-count must be an unsigned integer in the range 0 through 32,767.

UNIQUE

Specifies that the sort-key value in any given row of the referencing table must be different from the sort-key value in all other rows that have the same non-null referencing key value.

NOT UNIQUE

Removes the restriction that all values of the sort-key with the same non-null foreign key value must be unique.

Example: Alter the DEPT_EMPL Constraint

In this example, the physical characteristics of the DEPT_EMPL constraint are changed. Each internal index record will have a maximum of 10 keys and the bottom level index records will be displaced 50 pages from the associated referenced row:

alter constraint dept_empl on emp.empl
      displacement is 50 pages
      index block contains 10 keys;