DB2 10.5 for Linux, UNIX, and Windows

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:

Syntax

Read syntax diagramSkip visual syntax diagram
>>-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:
  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.
  3. The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2).
  4. DISABLE QUERY OPTIMIZATION is not supported for a unique or primary key constraint.
  5. 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

Notes

Examples

  1. 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 
  2. 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)
  3. 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')
  4. 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')