Browse Prior Art Database

High performance joining method using federated database and trigger function

IP.com Disclosure Number: IPCOM000130156D
Original Publication Date: 2005-Oct-14
Included in the Prior Art Database: 2005-Oct-14
Document File: 4 page(s) / 26K

Publishing Venue

IBM

Abstract

A program is disclosed that realize a high performance joining method across multi database management systems using federated database function and trigger function.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 49% of the total text.

Page 1 of 4

High performance joining method using federated database and trigger function

1. Background:

Federated database is a function of database management system (DBMS) that realize single Structured Query Language (SQL) statement across multi DBMS's as if all data is within single DBMS. Using this function, it is possible to build an application easily which use data of multi DBMS's. But, federated database make two types of processes internally as follows:
1. a select statement which get single row result specifying key items in where-clause. In this case, if multi rows are needed to be get, a loop process is made.
2. a select statement which get all rows result with no where-clause.

Therefore, it takes a long time to execute a SQL statement that join a table that has millions of rows such as a customer master table, and that has thousands of rows of result, because it needs thousands of loop processing. This disclosure solves the problem.

The solution has two component.

One solution is to have a copy table of a external table, which enable to realize high performance of a SQL statement which joins external high volume data.

Second solution is a method to synchronize an original external table and a copy internal data by combining federated database function and trigger function.

This program assume all DBMS's are DB2 Universal Database (UDB)*. It can be possible to extend other DBMS system, such as Oracle, but it's feasibility has not been assessed yet.

2. Summery of Invention:

To solve a performance problem of a SQL statement across multi DBMS's is to have a copy table of an external table. But this solution arise a new problem how to synchronize data between an original external table and a copy internal table. DB2 has a data replication function to synchronize data, but this function have following problems.
1. This method need to add components to administrate, which increases operational costs.
2. It is not necessary guaranteed data consistency between an original external table and a copy internal table (to some extent a delay arise to copy data).

This disclosure solve this problem by combining a federated a database function and a trigger function.

To put it concretely,
1. To define federated database table in DBMS which have copy source table. That is to define federated database table in the opposite direction.
2. To define trigger to copy source table for all event such as insert, update and delete. Processes of trigger are same actions (insert, update and delete) to a copy table as original events.

By this means, data consistency between a original table and a copy table is warranted (If update to copy table fails, then update to a original table is also rolled back).

3. Description:

A detailed description of "2. Summary of Invention" is as follows.

Assume following conditions to describe a invention:

A DB instance (DBMS) that have a referred original table such as customer master tables is "INSTANCE A", and it's table is "TAB...