Working with the Federated Data

This section describes how to access and update data at the data sources:

• Working with nicknames

• Transaction support in a federated system

• Selecting data in a federated system

• Modifying data in a federated system

Working with Nicknames

When you want to select or modify data source data, you query the nicknames using the SELECT, INSERT, UPDATE, and DELETE statements. You submit queries in DB2 SQL to the federated database. You can join data from local tables and remote data sources using a single SQL statement, as though all the data were local. For example, you can join data that is located in:

• A local DB2 for Windows table in the federated database, an Oracle table, and a Sybase view

• A DB2 for z/OS table on one server, a DB2 for z/OS table on another server, and an Excel spreadsheet

By processing SQL statements as though the data sources were ordinary relational tables or views within the federated database, the federated system can join relational data with data in nonrelational formats.

Tables and views that reside in the federated database are local objects. You do not create nicknames for these objects. You use the actual object name in your queries.

Remote objects are objects not located in the federated database. For example:

Before you query the data sources, make sure that you understand how to leverage the capabilities of the federated system effectively:

The SQL Statements You Can Use with Nicknames

A federated system is designed to make it easy to access data, regardless of where it is actually stored. This is accomplished by creating nicknames for all the data source objects (such as tables and views) that you want to access.

For example, if the nickname DEPT is created to represent the remote table HOST390.DBTTEST.DEPT, you would use the statement SELECT * FROM DEPT to query information in the remote table. You are querying the nickname instead of having to remember the underlying data source information. When you create a query, you do not have to be concerned with issues such as:

All the underlying metadata stored in the federated database catalog as part of the federated system setup and configuration provide the federated server with the information it needs to process your queries. After the federated system is set up, you can use the nicknames to query the data sources or further enhance the federated system configuration.

Accessing New Data Source Objects

Periodically, you will want to access data source objects that do not have nicknames. These might be new objects added to a data source, such as a newly created view, or existing objects that were not registered with the federated server when it was initially set up. In either case, these objects are new to the federated server. To access these new objects, you need to create nicknames for them, using the CREATE NICKNAME statement.

The federated system needs to be configured to access the data source. A server definition for the data source server on which the object resides needs to exist in the federated database. You create a server definition using the CREATE SERVER statement.

You must have one of the following authorizations to issue the CREATE NICKNAME statement:

• SYSADM or DBADM

• IMPLICIT_SCHEMA authority on the federated database, if the implicit or explicit schema name of the nickname does not exist

• CREATEIN privilege on the schema, if the schema name of the nickname exists

• The remote user ID in your user mapping must have SELECT privilege at the data source

Accessing Data Sources Using Pass-Through Session

You can submit SQL statements directly to data sources by using a special mode called pass-through. You submit SQL statements in the SQL dialect used by the data source. Use a pass-through session when you want to perform an operation that is not possible with the DB2 SQL/API.

Similarly, you can use a pass-through session to perform actions that are not supported by SQL, such as certain administrative tasks. However, you cannot use a pass-through session to perform all administrative tasks.

For example, you can run the statistics utility used by the data source, but you cannot start or stop the remote database. You can query only one data source at a time in a pass-through session. Use the SET PASSTHRU command to open a session. When you use the SET PASSTHRU RESET command, it closes the pass-through session. If you use the SET PASSTHRU command instead of SET PASSTHRU RESET, the current pass-through session is closed and a new pass-through session is opened.

Accessing Heterogeneous Data through Federated Views

A federated view is a view in the federated database whose base tables are located at remote data sources. The base tables are referenced in the federated view by nicknames, instead of by the data source table names. When you query from a federated view, data is retrieved from the remote data source.

The action of creating a federated database view of data source data is sometimes referred to as creating a view on a nickname. This is because you reference the nicknames instead of the data sources when you create the view.

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

Use the CREATE FEDERATED VIEW statement to create a federated view. You must have one of the following authorizations to issue the CREATE FEDERATED VIEW statement:

• SYSADM or DBADM

Or, for each nickname in any full select:

• CONTROL or SELECT privilege on the underlying table or view and at least one of the following:

  • IMPLICIT_SCHEMA authority on the federated database, if the implicit or explicit schema name of the view does not exist.

  • CREATEIN privilege on the schema, if the schema name of the view refers to an existing schema.

Privileges for the underlying objects are not considered when defining a view on a federated database nickname. Authorization requirements of the data source for the table or view referenced by the nickname are applied when the query is processed. The authorization ID of the statement may be mapped to a different remote authorization ID by a user mapping.

A federated view that is:

• Created from more than one nicknamed data source object is read-only view.

• Created from only one nicknamed data source object may or may not be read-only view.

• Created from one non-relational data source is read only.

• Created from a relational data source might allow updates, depending on what is included in the CREATE FEDERATED VIEW statement.

Transaction Support in a Federated System

Before you submit transactions to the federated database, it is important that you understand the type of transactions supported in a federated system.

Single-Site Update and Two-Phase Commit

A transaction is commonly referred to in DB2 as a unit of work. A unit of work is a recoverable sequence of operations within an application process. A unit of work is used by the database manager to ensure that a database is in a consistent state. Any reading from or writing to the database is done within a unit of work. A point of consistency (or commit point) is a time when all recoverable data that an application accesses is consistent with related data.

A unit of work is implicitly begun when any data in the database is read from or written to. An application must end a unit of work by issuing either a COMMIT or a ROLLBACK statement. The COMMIT statement makes permanent all changes made within a unit of work. The ROLLBACK statement removes these changes from the database.

Changes made by the unit of work become visible to other applications after a successful COMMIT. If the application ends normally without either of these statements being explicitly issued, the unit of work is automatically committed.

Recommendation

Your applications should always explicitly commit or roll back units of work. If an application ends abnormally in the middle of a unit of work, the unit of work is automatically rolled back.

A transaction can involve one or more databases. A transaction that involves two or more databases is a distributed unit of work (DUOW). In a DUOW that involves reading from one or more databases to update another database, or in a non-distributed unit of work, each COMMIT is processed in one operation. Accordingly, the operation is called a one-phase commit.

In a DUOW involving updates of multiple databases, data consistency is important. The two-phase commit protocol is commonly used to ensure data consistency across multiple databases within a DUOW. Two-phase commit will be supported on federated systems in a future release.

Considerations with Transparent DDL

COMMIT or ROLLBACK statements need to be issued before and after transparent DDL transactions. Transparent DDL creates a table on a remote data source and creates a nickname in the local federated database for the remote table. Because transparent DDL is updating both local and remote objects at the same time, each transparent DDL statement has to be the only update within the transaction. If there is any update prior to the transparent DDL transaction, a COMMIT or ROLLBACK statement has to be issued before the transparent DDL transaction. Likewise, a COMMIT or ROLLBACK statement has to be issued after the transparent DDL transaction before any other update can occur.

Considerations with Pass-Through Session

All statements sent through PASSTHRU sessions are treated as updates by the federated server. The purpose of this is to ensure data integrity. If a statement set through a PASSTHRU session is successful, it is recorded as an update, regardless of the type of statement. This includes SELECT statements. If a statement is not successful, it is not recorded. Likewise, if a PASSTHRU session is empty, a statement following the empty PASSTHRU session will not be blocked.

Considerations with the Autocommit Option in the DB2 CLP

By default, the DB2 CLP will automatically commit each SQL statement executed. If you elect to turn the autocommit command option OFF, make certain you explicitly issue COMMIT and ROLLBACK statements at the end of each transaction.

Recommendation

Set the autocommit command option ON for distributed units of work whenever applicable. If you have set this command option OFF, you can turn it on by issuing this command:

UPDATE COMMAND OPTIONS USING c ON;

INSERT, UPDATE, and DELETE Privileges

The privileges required to issue INSERT, UPDATE, and DELETE statements on nicknames are similar to the privileges required to issue these statements on tables:

• You can grant or revoke SELECT, INSERT, UPDATE, and DELETE privileges on a nickname.

• You must hold adequate privileges on the data source to perform SELECT, INSERT, UPDATE, or DELETE operations on the underlying object.

When a query is submitted to the federated database, the authorization privileges on the nickname in the query are checked. The authorization requirements of the data source object referenced by the nickname are applied only when the query is actually processed. If you do not have SELECT privilege on the nickname, you cannot select from the object to which the nickname refers. Likewise, just because you have a privilege on the nickname, such as UPDATE, this does not mean you will automatically be authorized to update the object that the nickname represents. Passing the privileges checking at the federated server does not imply you will pass the privilege checking at the remote data source. Through user mappings, a federated server user ID is mapped to the data source user ID. The privilege restrictions will be enforced at the data source.

Restrictions

You cannot perform INSERT, UPDATE, or DELETE operations on a nickname that uses the following wrapper: Sybase DBLIB, ODBC, DB2 Life Sciences Data Connect wrapper. Update of nicknames has the following restrictions:

• A nicknamed object whose data source does not permit update cannot be updated.

• A federated view with UNION ALL statements for multiple nicknamed objects is a read-only view. It cannot be updated.

Referential Integrity

You cannot define a constraint on a nickname. In the federated environment, DB2 does not compensate for referential integrity differences between data sources. DB2 does not interfere with referential integrity enforcement at the data sources. However, referential integrity constraints at a data source can affect nickname updates.

For example, suppose an insert into a table at a data source violates a referential integrity constraint at that data source. DB2 maps the resulting error to a DB2 error. Referential integrity between data sources is the responsibility of the applications.

LOBs

There are three types of LOBs:

• character large objects (CLOBs)

• double-byte character large objects (DBCLOBs)

• binary large objects (BLOBs)

Using DB2 UDB ESE Version 8 for Linux, UNIX, and Windows, you can perform read operations against LOBs located in all the relational data sources. Additionally, you can perform write operations against LOBs located in Oracle (Version 7.3 or higher) data sources using the NET8 wrapper.

Application Savepoint

To protect statement-level atomicity for INSERT, UPDATE, or DELETE against a nickname, enhancement is made in the federated system to guard against potential data inconsistency. Application savepoints at the data sources are incorporated in the global design.

If a data source does not support application savepoints, the federated system is unable to ensure statement-level atomicity at run time in the event of an error. A new SQL error code, SQLCODE-20190, is returned to the users when the federated system detects potential exposure of data inconsistency on any INSERT, UPDATE, or DELETE operation against nicknames residing in this data source.

To open up insert, update, or delete against nicknames on such a data source, the user may turn off the blocking logic via ALTER SERVER command to set server option IUD_APP_SVPT_ENFORCE to N. Some data sources, such as Informix, do not support application savepoints. If you are accessing data sources that do not support cursor application savepoints, you need to change your server definitions. Add the IUD_APP_SVPT_ENFORCE server option and set the option to N. This will enable you to update the data source using nicknames. Use the ALTER SERVER statement to add this option to the server definition.

Selecting Data in a Federated System

Use the SELECT statement to select data from data sources.

To select data using a nickname, the privileges held by the authorization ID of the statement must include SELECT privilege on the nickname (for the federated database to accept the request) and the SELECT privilege on the underlying table object (for the data source to accept the request).

Some of the types of distributed requests used with a federated system are requests that query:

• A single remote data source

• A local data source and a remote data source

• Multiple remote data sources

The federated database is a local data source. Tables and views in the federated database are local objects. You do not create nicknames for these objects; you use the actual object name in your SELECT statement.

Remote data sources include: another DB2 for the Linux, UNIX, and Windows database instance on the federated server; another DB2 for the Linux, UNIX, and Windows database instance on another server; and data sources other than DB2 for Linux, UNIX, and Windows.

Modifying Data in a Federated System

With a federated system, you can perform INSERT, UPDATE, and DELETE operations on nicknamed objects. The following sections include examples for performing these operations.

Inserting/Updating/Deleting Data into/to/from Data Source Objects

There are two types of data source objects:

• Local data source objects are objects that reside in the federated database. You do not create nicknames for these objects; you use the actual object name in your INSERT, UPDATE, and DELETE statements.

• Remote data source objects are any objects that do not reside in the federated database, including objects that reside on the federated server.

To insert, update, delete using a nickname, the privileges held by the authorization ID of the statement must include INSERT, UPDATE, DELETE privilege on the nickname (for the federated database to accept the request) and the INSERT, UPDATE, DELETE privilege on the underlying table object (for the data source to accept the request).

Restrictions

INSERT, UPDATE, DELETE is not available through the ODBC wrapper, the DBLIB wrapper, or the wrappers that are provided from DB2 Life Sciences Data Connect. Suppose you have an Informix table that has been created as follows:

CREATE TABLE infx_table (c1 INTEGER, c2 VARCHAR(20));

You can use the following SQL to configure the federated server to access this table:

CREATE WRAPPER informix;

CREATE SERVER infx_server
  TYPE Informix
  VERSION 9.3
  WRAPPER Informix
  OPTIONS(ADD NODE 'inf93',
    ADD DBNAME 'inf_db',
    ADD IUD_APP_SVPT_ENFORCE 'N');

CREATE USER MAPPING FOR USER SERVER infx_server
  OPTIONS(ADD REMOTE_AUTHID 'infx_authid',
    ADD REMOTE_PASSWORD 'infx_pswd');

CREATE NICKNAME infx_table_nn FOR infx_server."infx_authid"."infx_table;

You can issue INSERT, UPDATE, and DELETE statements using the infx_table_nn nickname. For example:

INSERT INTO infx_table_nn VALUES(1,'Heather');
INSERT INTO infx_table_nn VALUES(2,'Jenny');
UPDATE infx_table_nn SET c2='JENNIFER' WHERE c1=2;
DELETE FROM infx_table_nn WHERE c1=1;