Federated Database Performance (Part-2)

In part-1 of Federated Database Performance, we discussed what federation is, how it works and a few tips at the query level to improve the federated database performance.

In part-2, we will discuss few configuration parameters that will help us in performance tuning of the federated systems.

The performance of the query which includes data access on the remote databases mainly depends on the PUSHDOWN mechanism. Almost all parameters that IBM provides for improving the performance of the SHIP operation is dependent on this mechanism.

PUSHDOWN Mechanism:

Using pushdown mechanism, DB2 optimizer tries to push as many operations as possible to the remote database server so that filtering of data can be done at the remote server and only the required data is transferred to the federated server. So using pushdown, we can filter the data remotely instead of bringing all data to the local DB2 server and then perform filtering. This will improve the performance of the database system by reducing the network traffic and data transfer cost and time.

SYSCAT.SERVEROPTIONS view has some interesting parameters which can be helpful in federated environments. SYSCAT.SERVEROPTIONS view catalogs the federated server settings for each remote data source. Each row specifies the server-specific options and corresponding values.

Few basic and important parameters are discussed below.

Continue reading


Federated Database Performance (Part-1)

IBM’s Federated Database Technology:

In large organizations, usually there are many units working independently on different modules of an application. Each unit may use different relational or even a non-relational databases for the development. There must be a technology that addresses the communication issues between these different database management systems. This is exactly where IBM’s Federated Database Technology comes into the picture.

Formally, federation technology can be described briefly as:

1. Federation allows the users and applications to access data from more than one RDBMSs in a single request.

2. Its a special type of distributed database management system that allows the distributed request across the multiple databases at the same time.

3. Federation consists of a DB2 instance, a database or federated database and one or more remote databases of same or different type.

To get more insights on federation, developerWorks article IBM Federated Database Technology is recommended.

Continue reading