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
- Owner of the nickname, as recorded in the OWNER column of the
SYSCAT.TABLES catalog view
- DBADM authority
Syntax
>>-ALTER NICKNAME--nickname------------------------------------->
>--+-----------------------------------------------------------------------+-->
| .-,--------------------------------------------------. |
| V .-ADD-. | |
'-OPTIONS--(----+-+-----+--nickname-option-name--string-constant-+-+--)-'
| '-SET-' |
'-DROP--nickname-option-name---------------------'
.----------------------------------------------------------------------------------.
| .-,------------------------------------. |
V .-COLUMN-. (1) V | |
>----+-ALTER--+--------+--column-name--------+-LOCAL NAME--column-name----------+-+-+-+-><
| +-LOCAL TYPE--| local-data-type |--+ |
| | (2) | |
| '-| federated-column-options |-----' |
+-ADD--+-| unique-constraint |------+------------------------------------------+
| +-| referential-constraint |-+ |
| '-| check-constraint |-------' |
+-ALTER--+-FOREIGN KEY-+--constraint-name--| constraint-alteration |-----------+
| '-CHECK-------' |
+-DROP--+-PRIMARY KEY----------------------+-----------------------------------+
| '-+-FOREIGN KEY-+--constraint-name-' |
| +-UNIQUE------+ |
| +-CHECK-------+ |
| '-CONSTRAINT--' |
'-+-ALLOW CACHING----+---------------------------------------------------------'
'-DISALLOW CACHING-'
local-data-type
|--+-| built-in-type |------+-----------------------------------|
| (3) |
'-distinct-type-name-----'
built-in-type
|--+-+-SMALLINT----+-------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+--------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+---------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(1)-------------------. |
+-+-+-+-CHARACTER-+--+-----------------------+----------+--+--------------+-+-+
| | | '-CHAR------' '-(integer-+--------+-)-' | '-FOR BIT DATA-' | |
| | | '-OCTETS-' | | |
| | '-+-VARCHAR----------------+--(integer-+--------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' '-OCTETS-' | |
| | '-CHAR------' | |
| | .-(1M)------------------------. | |
| '-+-CLOB------------------------+--+-----------------------------+--------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+--------+-)-' |
| '-CHAR------' +-K-+ '-OCTETS-' |
| +-M-+ |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+----------------------------+
| | '-(integer-+-------------+-)-' | |
| | '-CODEUNITS16-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | '-CODEUNITS16-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ '-CODEUNITS16-' |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+-------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
'-+-DATE-------------------------+--------------------------------------------'
+-TIME-------------------------+
| .-(--6--)-------. |
'-TIMESTAMP--+---------------+-'
'-(--integer--)-'
federated-column-options
.-,------------------------------------------------.
V .-ADD-. |
|--OPTIONS--(----+-+-----+--column-option-name--string-constant-+-+--)--|
| '-SET-' |
'-DROP--column-option-name---------------------'
unique-constraint
|--+-----------------------------+--+-UNIQUE------+------------->
'-CONSTRAINT--constraint-name-' '-PRIMARY KEY-'
.-,-----------.
V |
>--(----column-name-+--)--| constraint-attributes |-------------|
referential-constraint
|--+-----------------------------+--FOREIGN KEY----------------->
'-CONSTRAINT--constraint-name-'
.-,-----------.
V |
>--(----column-name-+--)--| references-clause |-----------------|
references-clause
|--REFERENCES--+-table-name-+--+-----------------------+-------->
'-nickname---' | .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--| constraint-attributes |------------------------------------|
check-constraint
|--+-----------------------------+------------------------------>
'-CONSTRAINT--constraint-name-'
>--CHECK--(--| check-condition |--)----------------------------->
>--| constraint-attributes |------------------------------------|
check-condition
|--+-search-condition----------+--------------------------------|
'-| functional-dependency |-'
functional-dependency
|--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--|
| .-,-----------. | | .-,-----------. |
| V | | | V | |
'-(----column-name-+--)-' '-(----column-name-+--)-'
constraint-attributes
.-TRUSTED-----.
|--●--NOT ENFORCED--+-------------+--●-------------------------->
'-NOT TRUSTED-'
.-ENABLE QUERY OPTIMIZATION------.
>--+--------------------------------+--●------------------------|
| (4) |
'-DISABLE QUERY OPTIMIZATION-----'
constraint-alteration
.-------------------------------------------.
V (5) |
|--------+-+-ENABLE--+--QUERY OPTIMIZATION---+-+----------------|
| '-DISABLE-' |
| .-TRUSTED-----. |
'---NOT ENFORCED--+-------------+---'
'-NOT TRUSTED-'
Notes:
- You cannot specify both the ALTER COLUMN clause and an ADD,
ALTER, or DROP informational constraint clause in the same ALTER NICKNAME
statement.
- 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.
- The specified distinct type cannot
have any data type constraints and the source type cannot be an anchored
data type (SQLSTATE 428H2).
- DISABLE QUERY OPTIMIZATION is not supported for a unique
or primary key constraint.
- The same clause must not be specified more than once.
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
- Names a nickname option that is to be added or set.
- string-constant
- Specifies the setting for nickname-option-name as
a character string constant.
- 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.
- OPTIONS
- Indicates what column options are to be added, set, or dropped
for the column specified after the COLUMN keyword.
- ADD
- Adds a column option.
- SET
- Changes the setting of a column option.
- column-option-name
- Names a column option that is to be added or set.
- string-constant
- Specifies the setting for column-option-name as
a character string constant.
- DROP column-option-name
- Drops a column option.
- 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.
- For relational nicknames, 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
- For non-relational nicknames, the ALTER NICKNAME statement within
a given unit of work (UOW) cannot be processed under any of the following
conditions (SQLSTATE 55007):
- A nickname referenced in this statement has a cursor open on it
in the same UOW
- A nickname referenced in this statement is already referenced
by a SELECT statement in the same UOW
- Either an INSERT, DELETE, or UPDATE statement has already been
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.
Examples
- The nickname NICK1 references a DB2® for i table called
T1. Also, COL1 is the local name that references this table's first column, C1. Rename the local
name for C1 from COL1 to
NEWCOL.
ALTER NICKNAME NICK1
ALTER COLUMN COL1
LOCAL NAME NEWCOL
- The nickname EMPLOYEE references a DB2 for z/OS® table called EMP.
Also, SALARY is the local name that references EMP_SAL, one of this table's columns. The column's
data type, FLOAT, maps to the local data type, DOUBLE. Change the mapping so that FLOAT maps to
DECIMAL (10,
5).
ALTER NICKNAME EMPLOYEE
ALTER COLUMN SALARY
LOCAL TYPE DECIMAL(10,5)
- 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')
- 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')
DB2 10.5 for Linux, UNIX, and Windows