ALTER NICKNAME statement

The ALTER NICKNAME statement modifies the nickname information associated with a data source object (such as a table, view, or file).

This statement modifies the information that is stored in the federated database in the following ways:
  • Altering the local column names for the columns of the data source object
  • Altering the local data types for the columns of the data source object
  • Adding, setting, or dropping nickname and column options
  • Adding or dropping a primary key
  • Adding or dropping one or more unique, referential, or check constraints
  • Altering one or more referential or check constraint attributes
  • Altering the caching of data at a federated server

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

  • ALTER privilege on the nickname specified in the statement
  • CONTROL privilege on the nickname specified in the statement
  • ALTERIN privilege on the schema, if the schema name of the nickname exists
  • SCHEMAADM authority on the schema, if the schema name of the nickname exists
  • Owner of the nickname, as recorded in the OWNER column of the SYSCAT.TABLES catalog view
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramALTER NICKNAMEnicknameOPTIONS(,ADDSETnickname-option-namestring-constantDROPnickname-option-name)ALTERCOLUMNcolumn-name1,LOCAL NAMEcolumn-nameLOCAL TYPElocal-data-typefederated-column-options2ADDunique-constraintreferential-constraintcheck-constraintALTERFOREIGN KEYCHECKconstraint-nameconstraint-alterationDROPPRIMARY KEYFOREIGN KEYUNIQUECHECKCONSTRAINTconstraint-nameALLOW CACHINGDISALLOW CACHING
Notes:
  • 1 You cannot specify both the ALTER COLUMN clause and an ADD, ALTER, or DROP informational constraint clause in the same ALTER NICKNAME statement.
  • 2 If you need to specify the federated-column-options clause in addition to the LOCAL NAME parameter, the LOCAL TYPE parameter, or both, you must specify the federated-column-options clause last.

Description

nickname
Identifies the nickname for the data source object (such as a table, view, or file) that contains the column being altered. It must be a nickname described in the catalog.
OPTIONS
Indicates the nickname options that are added, set, or dropped when the nickname is altered.
ADD
Adds a nickname option.
SET
Changes the setting of a nickname option.
nickname-option-name
The nickname option that is to be added or set. Which options you can specify depends on the data source of the object for which a nickname is being created. For a list of data sources and the nickname options that apply to each, see Data source options.
string-constant
The nickname option setting as a character string constant enclosed in single quotation marks.
DROP nickname-option-name
Drops a nickname option.
ALTER COLUMN column-name
Names the column to be altered. The column-name is the federated server's current name for the column of the table or view at the data source. The column-name must identify an existing column of the nickname (SQLSTATE 42703). You cannot reference the same column name multiple times in the same ALTER NICKNAME statement (SQLSTATE 42711).
LOCAL NAME column-name
Specifies a new name, column-name, by which the federated server is to reference the column to be altered. The new name cannot be qualified, and the same name cannot be used for more than one column of the nickname (SQLSTATE 42711).
LOCAL TYPE local-data-type
Specifies a new local data type to which the data type of the column that is to be altered will map. The new type is denoted by local-data-type.

Some wrappers only support a subset of the SQL data types. For descriptions of specific data types, see the description of the CREATE TABLE statement.

built-in-type
See "CREATE TABLE" for the description of built-in data types.
ADD unique-constraint
Defines a unique constraint. See the description of the CREATE NICKNAME statement.
ADD referential-constraint
Defines a referential constraint. See the description of the CREATE NICKNAME statement.
ADD check-constraint
Defines a check constraint. See the description of the CREATE NICKNAME statement.
ALTER FOREIGN KEY constraint-name
Alters the constraint attributes of the referential constraint constraint-name. For a description of the constraint attributes, see the CREATE NICKNAME statement. The constraint-name must identify an existing referential constraint (SQLSTATE 42704).
ALTER CHECK constraint-name
Alters the constraint attributes of the check constraint constraint-name. The constraint-name must identify an existing check constraint (SQLSTATE 42704).
constraint-alteration
Provides options for changing the attributes associated with referential or check constraints.
ENABLE QUERY OPTIMIZATION
The constraint can be used for query optimization under appropriate circumstances.
DISABLE QUERY OPTIMIZATION
The constraint cannot be used for query optimization.
NOT ENFORCED
Specifies that the constraint is not enforced by the database manager during normal operations such as insert, update, or delete.
TRUSTED
The data can be trusted to conform to the constraint. TRUSTED must be used only if the data in the table is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint. This is the default option.
NOT TRUSTED
The data cannot be trusted to conform to the constraint. NOT TRUSTED is intended for cases where the data conforms to the constraint for most rows, but it is not independently known that all the rows or future additions will conform to the constraint. If a constraint is NOT TRUSTED and enabled for query optimization, then it will not be used to perform optimizations that depend on the data conforming completely to the constraint. NOT TRUSTED can be specified only for referential integrity constraints (SQLSTATE 42613).
DROP PRIMARY KEY
Drops the definition of the primary key and all referential constraints that are dependent upon this primary key. The nickname must have a primary key.
DROP FOREIGN KEY constraint-name
Drops the referential constraint constraint-name. The constraint-name must identify an existing referential constraint defined on the nickname.
DROP UNIQUE constraint-name
Drops the definition of the unique constraint constraint-name and all referential constraints that are dependent upon this unique constraint. The constraint-name must identify an existing unique constraint.
DROP CHECK constraint-name
Drops the check constraint constraint-name. The constraint-name must identify an existing check constraint defined on the nickname.
DROP CONSTRAINT constraint-name
Drops the constraint constraint-name. The constraint-name must identify an existing check constraint, referential constraint, primary key, or unique constraint defined on the nickname.
ALLOW CACHING or DISALLOW CACHING
Specifies whether the nickname can be referenced in a query that defines a materialized query table, which could be used to cache data from the data source at the federated server.
ALLOW CACHING
Specifies that the nickname can be referenced in a query that defines a materialized query table, which allows data from the data source to be cached in the materialized query table at the federated server. The refreshable options defined for the materialized query table specify how the cached data in the materialized query table is maintained.
DISALLOW CACHING
Specifies that the nickname cannot be referenced in a query that defines a materialized query table. DISALLOW CACHING cannot be specified for a nickname that is referenced in the fullselect of a materialized query table definition (SQLSTATE 42917).

Rules

  • If a nickname is used in a view, SQL method, or SQL function, or informational constraints are defined on it, the ALTER NICKNAME statement cannot be used to change the local names or data types for the columns in the nickname (SQLSTATE 42893). The statement can be used, however, to add, set, or drop column options, nickname options, or informational constraints.
  • If a nickname is referenced by a materialized query table definition, the ALTER NICKNAME statement cannot be used to change the local names, data types, column options, or nickname options (SQLSTATE 42893). Moreover, the statement cannot be used to disable caching (SQLSTATE 42917). The statement can be used, however, to add, alter, or drop informational constraints.
  • A column option cannot be specified more than once in the same ALTER NICKNAME statement (SQLSTATE 42853). When a column option is enabled, reset, or dropped, any other column options that are in use are not affected.
  • The ALTER NICKNAME statement within a given unit of work (UOW) cannot be processed under either of the following conditions (SQLSTATE 55007):
    • A nickname referenced in this statement has a cursor open on it in the same UOW
    • Either an INSERT, DELETE, or UPDATE statement is already issued in the same UOW against the nickname that is referenced in this statement

Notes

  • If the ALTER NICKNAME statement is used to change the local name for a column of a nickname, queries against that column must reference it by its new name.
  • When the local specification of a column's data type is changed, the database manager invalidates any statistics (HIGH2KEY, LOW2KEY, and so on) gathered for that column.
  • Caching and protected objects: For nicknames whose data source object is protected, specify DISALLOW CACHING. This ensures that each time the nickname is used, data for the appropriate authorization ID is returned from the data source at query execution time. This is done by restricting the nickname from being used in the definition of a materialized query table at the federated server, which might be being used to cache the nickname data.
  • BINARY and VARBINARY types are not supported in a Federated system.

Examples

  1. Indicate that in an Oracle table, a column with the data type of VARCHAR does not have trailing blanks. The nickname for the table is NICK2, and the local name for the column is COL1. 
       
   ALTER NICKNAME NICK2
     ALTER COLUMN COL1
     OPTIONS (ADD VARCHAR_NO_TRAILING_BLANKS 'Y')
  2. Alter the fully qualified path for the table-structured file, drugdata1.txt, for the nickname DRUGDATA1. Use the FILE_PATH nickname option and change the path from the current value of '/user/pat/drugdata1.txt' to '/usr/kelly/data/drugdata1.txt'.
       
   ALTER NICKNAME DRUGDATA1
     OPTIONS (SET FILE_PATH '/usr/kelly/data/drugdata1.txt')