Application Development Guide
This section provides information to help you access and use data source
tables and views. The topics covered are:
- Nicknames that you assign to the tables and views, so that the federated
server can reference them.
- Isolation levels that help you to maintain data integrity at the data
source when you access the tables and views
A nickname is an identifier by which an application can
reference a data source table or view. This section:
- Explains how the global catalog can be supplied with information about
tables that you create nicknames for
- Lists considerations and restrictions to remember when you work with
nicknames
- Describes parameters that you can set to optimize queries
- Discusses ways to use views referenced by nicknames
When a nickname is created for a data source table or view, DB2 updates the
global catalog with information that the optimizer can use in planning how to
retrieve data from the table or view. This information includes, for
example, the name of the table or view and the names and attributes of the
table's or view's columns.
In the case of a table, the information also includes:
- Statistics (for example, the number of rows and the number of pages on
which the rows exist). To ensure that DB2 obtains the latest
statistics, it is advisable to run the data source's equivalent of the
RUNSTATS command against the table before you create the nickname.
- Descriptions of any indexes that the table has. If the table has no
indexes, you can nonetheless supply the catalog with metadata that an index
definition typically contains--for example, which column or columns in
the table have unique values, and whether any rows are unique. You can
generate this metadata, which is collectively called an index
specification, by running the CREATE INDEX statement against the
table's nickname. Be aware that in this case the statement
produces only the index specification; it does not create an actual
index. For documentation on this statement, see the SQL Reference.
To find out what information about a data source table is stored in the
global catalog, query the SYSCAT.TABLES and SYSCAT.COLUMNS
catalog view. To find out what information about a table's index
is stored in the catalog, or what a particular index specification contains,
query the SYSCAT.INDEXES catalog view. For descriptions of these
views, see the SQL Reference. For further discussion about updating the global catalog with
information about tables and indexes, see the Administration Guide: Implementation.
There are several considerations and restrictions to bear in mind when
you:
- Define, change, and drop nicknames
- Reference tables and views by their nicknames
- Perform operations on tables and views that are referenced by nicknames
-
To define a nickname for a table or view, use the CREATE NICKNAME
statement. In this statement:
- You reference an Oracle table or view by its name.
- You can reference a DB2 family table or view by its name, or, if it has an
alias, by this alias.
- You can define more than one nickname for the same table or view.
You can also define an alias for a nickname with the CREATE ALIAS
statement.
- To change a nickname, you must drop it and then replace it. To drop
it, use the DROP NICKNAME statement; to replace it, use the CREATE
NICKNAME statement.
- Dropping a nickname causes any views defined using the nickname to be
inoperative and invalidates any plans that are dependent upon it.
For documentation on the CREATE NICKNAME, CREATE ALIAS, and DROP NICKNAME
statements, see the SQL Reference.
- After a data source table or view has been given a nickname, you can
reference the table or view by that nickname only (except in a pass-through
session). For example, if you define the nickname DEPT to represent a
table called DB2MVS1.PERSON.DEPT, the statement SELECT * FROM
DEPT is allowed, but SELECT * FROM DB2MVS1.PERSON.DEPT is not
allowed. In a pass-through session, however, you must access a table or
view by its data source name.
- You cannot reference a nickname in the CREATE TRIGGER statement.
- If you reference a nickname in the summary-table-definition clause of the
CREATE TABLE statement, you must also specify the DEFINITION ONLY keywords in
this clause.
- The COMMENT ON statement is valid against a nickname and columns that are
defined on a nickname. This statement updates the global catalog;
it does not update data source catalogs.
- GRANT and REVOKE statements are valid against a nickname for certain
privileges and for all users and groups. However, DB2 does not issue a
corresponding GRANT or REVOKE against the table or view that the nickname
references. For more information about nickname privileges, see the
Administration Guide: Planning.
- Data sources are read-only. Therefore:
- INSERT, UPDATE, and DELETE statements are not valid against
nicknames.
- A view that contains a UNION ALL clause for a nickname cannot be
updated.
- You cannot run the DB2 utilities (RUNSTATS, IMPORT, EXPORT and so on)
against nicknames.
When you define a nickname for a table or view, you can provide the global
catalog with information about particular columns in the table or view.
You specify this information in the form of values that you assign to
parameters called column options. You can specify any of
these values in either upper- or lowercase. Table 27 describes the column options and their values.
Table 27. Column Options and Their Settings
| 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.
| '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 guidelines, see the SQL Reference).
| 'n'
|
You set column options in the ALTER NICKNAME statement. For information
about this statement, see the SQL Reference.
You can use nicknames with views in two main ways:
- You can create nicknames for data source views. The federated
server treats the nickname of a data source view the same way it treats the
nickname of a data source table.
- You can create federated database views of data source tables and views
that have nicknames. For example, because the federated server can
accommodate a join of base tables at different locations, you can easily
define federated database views of base tables that reside at different data
sources. Such multi-location views offer a high degree of data
independence for a globally integrated database, just as views defined on
multiple local tables do for centralized relational database managers.
This global view mechanism is one way in which the federated server offers a
high degree of data independence.
The action of creating a federated database view of data source data is
sometimes called "creating a view on a nickname". This phrase
reflects the fact that for the view to be created, the CREATE VIEW
statement's fullselect must reference the nickname of each table and view
that the view is to contain.
Views do not have statistics or indexes of their own because they are not
actual tables located in a database. This statement is true even when a
view is identical in structure and content to a single base table. For
more information about statistics and indexes, see Administration Guide: Implementation.
You can maintain data integrity for a data source table by requesting that
the table's rows be locked at a specific isolation level. For
example, to ensure that you have sole access to a row, you would specify the
repeatable read (RR) isolation level for that row.
The federated server maps the isolation level you request to a
corresponding one at the data source. To illustrate this, Table 28 lists:
- The isolation levels that you can request. They are:
- CS
- Cursor stability
- RR
- Repeatable read
- RS
- Read stability
- UR
- Uncommitted read
- The Oracle isolation levels that the requested levels map to.
Table 28. Comparable Isolation Levels between the Federated Server and Oracle Data Sources.
| Federated Server (DB2)
| CS
| RR
| RS
| UR
|
| Oracle
| Default
| Transaction read-only
| Transaction read-only
| Same as cursor stability
|
[ Top of Page | Previous Page | Next Page ]