SQL Reference

ALTER NICKNAME

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

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:

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

Notes

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' )


[ Top of Page | Previous Page | Next Page ]