SQL Reference

ALTER NICKNAME

The ALTER NICKNAME statement modifies the federated database's representation of a data source table or view by:

• Changing the local names of the table's or view's columns

• Changing the local data types of these columns

• Adding, changing, or deleting options for these columns

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

• SYSADM or DBADM authority

• 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

• Definer of the nickname as recorded in the DEFINER column of the catalog view for the nickname

Syntax

>>-ALTER NICKNAME------nickname--------------------------------->
 
      .--------------------------------------------------------------------------------.
      |                                    .-,--------------------------------------.  |
      V         .-COLUMN-.                 V                                        |  |
>--------ALTER--+--------+--column-name------+-LOCAL NAME--column-name-----------+--+--+>
                                             +-LOCAL TYPE--data-type-------------+
                                             |                              (1)  |
                                             '-| federated-column-options |------'
 
>--------------------------------------------------------------><
 
federated-column-options
 
                .-,----------------------------------------------------.
                V   .-ADD--.                                           |
|---OPTIONS--(----+-+------+---column-option-name--string-constant--+--+---)-->
                  | '-SET--'                                        |
                  '-DROP--column-option-name------------------------'
 
>---------------------------------------------------------------|
 

Notes:

1. If the user needs to specify the federated-column-options clause in addition to the LOCAL NAME parameter, the LOCAL TYPE parameter, or both, the user must specify the federated-column-options clause last.

Description

nickname

Identifies the nickname for the data source table or view that contains the column specified after the COLUMN keyword. It must be a nickname described in the catalog.

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 data source table or view referenced by nickname.

LOCAL NAME column-name

Is the new name by which the federated server is to reference the column identified by the ALTER COLUMN column-name parameter. This new name must be a valid DB2 identifier.

LOCAL TYPE data-type

Maps the specified column's data type to a local data type other than the one that it maps to now. The new type is denoted by data-type.

The data-type cannot be LONG VARCHAR, LONG VARGRAPHIC, DATALINK, a large object (LOB) data type, or a user-defined type.

OPTIONS

Indicates what column options are to be enabled, reset, or dropped for the column specified after the COLUMN keyword. Refer to Column Options for descriptions of column-option-names and their settings.

ADD

Enables a column option.

SET

Changes the setting of a column option.

column-option-name

Names a column option that is to be enabled or reset.

string-constant

Specifies the setting for column-option-name as a character string constant.

DROP column-option-name

Drops a column option.

Rules

• If a view has been created on a nickname, the ALTER NICKNAME statement cannot be used to change the local names or data types for the columns in the table or view that the nickname references (SQLSTATE 42601). The statement can be used, however, to enable, reset, or drop column options for these columns.

Notes

• If ALTER NICKNAME is used to change the local name for a column in a table or view that a nickname references, queries of the column must reference it by its new name.

• 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.

• 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.

• The ALTER NICKNAME statement cannot be processed within a unit of work (UOW) if the nickname referenced in this statement is already referenced by a SELECT statement in the same UOW (SQLSTATE 55007).

Examples

Example 1: The nickname NICK1 references a DB2 Universal Database for AS/400 table called T1. Also, COL1 is the local name that references this table's first column, C1. Change the local name for C1 to NEWCOL.

   ALTER NICKNAME NICK1
      ALTER COLUMN COL1
      LOCAL NAME NEWCOL 

Example 2: The nickname EMPLOYEE references a DB2 Universal Database for OS/390 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)
      

Example 3: Indicate that in an Oracle table, a column with the data type of VARCHAR doesn't 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' )

[ DB2 List of Books | Search the DB2 Books ]