Altering nickname column options

You can add, set, or drop nickname column options using the ALTER NICKNAME statement.

Before you begin

The authorization ID issuing the statement must include at least one of the following privileges:
  • 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

About this task

You specify column information in the CREATE NICKNAME and ALTER NICKNAME statements by using parameters called nickname column options. You can specify any of these values in either uppercase or lowercase letters.

Restrictions

See Restrictions on altering nicknames.

Procedure

To alter nickname column options from the command line prompt, use the ALTER NICKNAME statement.

The first time that you add an option, use the ADD keyword to add the option. If the option was added previously, use the SET keyword to change the option.

Example 1: Specifying the NUMERIC_STRING column option with relational data sources

The NUMERIC_STRING column option applies to character type columns (CHAR and VARCHAR). Suppose that a data source has a collating sequence that differs from the federated database collating sequence. The federated server typically would not sort any columns containing character data at the data source. It would return the data to the federated database and perform the sort locally. However, suppose that the column is a character data type and contains only numeric characters ('0','1',...,'9'). You can indicate this by assigning a value of 'Y' to the NUMERIC_STRING column option. This gives the UDB query optimizer the option of performing the sort at the data source. If the sort is performed remotely, you can avoid the overhead of sorting the data at the federated server.

The nickname ORA_INDSALES for an Oracle table called INDONESIA_SALES. The table contains the column POSTAL_CODE with the data type of VARCHAR. Originally the column contained only numeric characters, and the NUMERIC_STRING column option was set to 'Y'. However, the column now contains a mixture of numeric and non-numeric characters. To change the NUMERIC_STRING column option to 'N', use this statement:

ALTER NICKNAME ORA_INDSALES ALTER COLUMN POSTAL_CODE 
   OPTIONS (SET NUMERIC_STRING 'N')

Example 2: Specifying the VARCHAR_NO_TRAILING_BLANKS column option with relational data sources

The VARCHAR_NO_TRAILING_BLANKS column option can be used to identity specific columns that contain no trailing blanks. The SQL Compiler will factor in this setting when it checks for all operations (such as comparison operations) performed on columns.

The nickname ORA_INDSALES is for an Oracle table called INDONESIA_SALES. The table contains the column NAME with the data type of VARCHAR. The NAME column does not have trailing blanks. To add the VARCHAR_NO_TRAILING_BLANKS option to the nickname, use this statement:

ALTER NICKNAME ORA_INDSALES ALTER COLUMN NAME 
   OPTIONS (ADD VARCHAR_NO_TRAILING_BLANKS 'Y')

Example 3: Specifying the XPATH column option with nonrelational data sources

The nickname EMPLOYEE is for an XML data source. An XPATH was specified for the fnmae column. To set the XPATH column option to a different path, use this statement:

ALTER NICKNAME EMPLOYEE ALTER COLUMN fname 
   OPTIONS (SET XPATH './@first')