The primary purpose of column options is to provide
information about nickname columns to the SQL compiler. Setting column
options for one or more columns to 'Y' allows the compiler to
consider additional push-down possibilities for predicates that perform
evaluation operations. See Administration Guide,
Performance for more information on push-down processing.
| Option
| Valid Settings
| Default Setting
|
| numeric_string
|
- 'Y'
- Yes, this column contains only strings of numeric data.
IMPORTANT: If this column contains only numeric strings followed by
trailing blanks, it is inadvisable to specify 'Y'.
- 'N'
- No, this column is not limited to strings of numeric data.
By setting numeric_string to 'Y' for a column, you are informing
the optimizer that this column contains no blanks that could interfere with
sorting of the column's data. This option is helpful when the
collating sequence of a data source is different from DB2. Columns
marked with this option will not be excluded from local (data source)
evaluation because of a different collating sequence.
| 'N'
|
| varchar_no_trailing_blanks
|
Indicates whether trailing blanks are absent from a specific VARCHAR
column:
- 'Y'
- Yes, trailing blanks are absent from this VARCHAR column.
- 'N'
- No, trailing blanks are not absent from this VARCHAR column.
If data source VARCHAR columns contain no padded blanks, then the
optimizer's strategy for accessing them depends in part on whether they
contain trailing blanks. By default, the optimizer "assumes" that
they actually do contain trailing blanks. On this assumption, it
develops an access strategy that involves modifying queries so that the values
returned from these columns are the ones that the user expects. If,
however, a VARCHAR column has no trailing blanks, and you let the optimizer
know this, it can develop a more efficient access strategy. To tell the
optimizer that a specific column has no trailing blanks, specify that column
in the ALTER NICKNAME statement (for syntax, see the SQL
Reference).
| 'N'
|
The primary purpose of function mapping options is to
provide information about the potential cost of executing a data source
function at the data source. If pushdown analysis determines that
either of two functions within a mapping can be called, the statistical
information provided in the mapping definition helps the optimizer to compare
the estimated cost of executing the data source function with the estimated
cost of executing the DB2 function.
| Option
| Valid Settings
| Default Setting
|
| disable
| Disable a default function mapping. Valid values are 'Y'
and 'N'.
| 'N'
|
| initial_insts
| Estimated number of instructions processed the first and last time that
the data source function is invoked.
| '0'
|
| initial_ios
| Estimated number of I/Os performed the first and last time that the data
source function is invoked.
| '0'
|
| ios_per_argbyte
| Estimated number of I/Os expended for each byte of the argument set
that's passed to the data source function.
| '0'
|
| ios_per_invoc
| Estimated number of I/Os per invocation of a data source function.
| '0'
|
| insts_per_argbyte
| Estimated number of instructions processed for each byte of the argument
set that's passed to the data source function.
| '0'
|
| insts_per_invoc
| Estimated number of instructions processed per invocation of the data
source function.
| '450'
|
| percent_argbytes
| Estimated average percent of input argument bytes that the data source
function will actually read.
| '100'
|
| remote_name
| Name of the data source function.
| local name
|
Server options are used to describe a server. In
addition to location information (such as the data source machine name),
options can specify security and performance attributes for a data
source. The security options provide control over password
communication (sent or not sent to data sources) and authentication
information case (uppercase and/or lowercase IDs and passwords). The
performance options help the optimizer determine if evaluation operations can
be done at data sources and the best cost model for completing queries that
retrieve data from data sources.
| Option
| Valid Settings
| Default Setting
|
| collating_sequence
|
Specifies whether the data source uses the same default collating sequence as
the federated database, based on the code set and the country
information. If a data source has a collating sequence that differs
from DB2's collating sequence, most operations depending on DB2's
collating sequence cannot be remotely evaluated at a data source. An
example is executing MAX column functions against a nickname character column
at a data source with a different collating sequence. Because results
might differ if the MAX function is evaluated at the remote data source, DB2
will perform the aggregate operation and the MAX function locally.
If your query contains an equal sign, it is possible to push-down that
portion of the query even if the collating sequences are different (set to
'N'). For example, the predicate C1 = 'A' could be
pushed-down to a data source. Of course, such queries cannot be
pushed-down when the collating sequence at the data source is
case-insensitive. When a data source is case-insensitive, the results
from C1= 'A' and C1 = 'a' are the same, which is not
acceptable in a case-sensitive environment (DB2).
Administrators can create federated databases with a particular collating
sequence that matches the data source collating sequence. This approach
may speed performance if all data sources use the same collating sequence or
if most or all column functions are directed against data sources that use the
same collating sequence.
- 'Y'
- Data source's collating sequence is the same as federated
database's.
- 'N'
- Data source's collating sequence is not the same as federated
database's.
- 'I'
- Data source's collating sequence is different from federated
database's and is case-insensitive (for example, 'TOLLESON' and
'TolLESon' are considered equal).
| 'N'
|
| comm_rate
|
Specifies the communication rate between a federated server and its associated
data sources. Expressed in megabytes per second.
| '2.0'
|
| connectstring
|
Specifies initialization properties needed to connect to an OLE DB
provider. For the complete syntax and semantics of the connection
string, see the "Data Link API of the OLE DB Core Components" in the
Microsoft OLE DB 2.0 Programmer's Reference and
Data Access SDK, Microsoft Press, 1998.
| None
|
| cpu_ratio
|
Indicates how much faster or slower a data source's CPU runs than the
federated server's CPU.
| '1.0'
|
| dbname
|
Name of the data source database that you want the federated server to
access. Required for DB2 family data sources; does not apply to
Oracle** data sources.
| None.
|
| fold_id (See notes 1 and 4 at the end of this table.)
|
Applies to user IDs that the federated server sends to data sources for
authentication. Valid values are:
- 'U'
- The federated server folds the user ID to uppercase before sending it to
the data source. This is a logical choice for DB2 Family and Oracle**
data sources (See note 2 at end of this table.)
- 'N'
- The federated server does nothing to the user ID before sending it to the
data source. (See note 2 at end of this table.)
- 'L'
- The federated server folds the user ID to lowercase before sending it to
the data source.
If none of these settings are used, the federated server tries to send the
user ID to the data source in uppercase. If the user ID fails, the
server tries sending it in lowercase.
| None.
|
| fold_pw (See notes 1, 3 and 4 at the end of this table.)
|
Applies to passwords that the federated server sends to data sources for
authentication. Valid values are:
- 'U'
- The federated server folds the password to uppercase before sending it to
the data source. This is a logical choice for DB2 Family and Oracle**
data sources.
- 'N'
- The federated server does nothing to the password before sending it to the
data source.
- 'L'
- The federated server folds the password to lowercase before sending it to
the data source.
If none of these settings are used, the federated server tries to send the
password to the data source in uppercase. If the password fails, the
server tries sending it in lowercase.
| None.
|
| io_ratio
|
Denotes how much faster or slower a data source's I/O system runs than
the federated server's I/O system.
| '1.0'
|
| node
|
Name by which a data source is defined as an instance to its RDBMS.
Required for all data sources.
For a DB2 family data source, this name is the node specified in the
federated database's DB2 node directory. To view this directory,
issue the db2 list node directory command.
For an Oracle** data source, this name is the server name specified in the
Oracle** tnsnames.ora file. To access this name on the Windows
NT platform, specify the View Configuration Information option of
the Oracle** SQL Net Easy Configuration tool.
| None.
|
| password
|
Specifies whether passwords are sent to a data source.
- 'Y'
- Passwords are always sent to the data source and validated. This is
the default value.
- 'N'
- Passwords are not sent to the data source (regardless of any user
mappings) and not validated.
- 'ENCRYPTION'
- Passwords are always sent to the data source in encrypted form and
validated. Valid only for DB2 Family data sources that support
encrypted passwords.
| 'Y'
|
| plan_hints
|
Specifies whether plan hints are to be enabled. Plan hints
are statement fragments that provide extra information for data source
optimizers. This information can, for certain query types, improve
query performance. The plan hints can help the data source optimizer
decide whether to use an index, which index to use, or which table join
sequence to use.
- 'Y'
- Plan hints are to be enabled at the data source if the data source
supports plan hints.
- 'N'
- Plan hints are not to be enabled at the data source.
| 'N'
|
| pushdown
|
- 'Y'
- DB2 will consider letting the data source evaluate operations.
- 'N'
- DB2 will retrieve only columns from the remote data source and will not
let the data source evaluate other operations, such as joins.
| 'Y'
|
| varchar_no_trailing_blanks
|
Specifies if this data source uses non-blank padded varchar comparison
semantics. For varying-length character strings that contain no
trailing blanks, some DBMS' s non-blank-padded comparison semantics
return the same results as DB2's comparison semantics. If you are
certain that all VARCHAR table/view columns at a data source contain no
trailing blanks, consider setting this server option to 'Y' for a
data source. This option is often used with Oracle** data
sources. Ensure that you consider all objects that can potentially have
nicknames (including views).
- 'Y'
- This data source has non-blank-padded comparison semantics similar to
DB2's.
- 'N'
- This data source does not have the same non-blank-padded comparison
semantics as DB2's.
| 'N'
|
User options provide authorization and accounting string
information for user mappings. Use them to specify the ID and password
used to represent a DB2 authentication ID when authenticating at a data
source.
