Apparatus and method for transparent access of foreign databases in a heterogeneous database system

• US 6,226,649 B1
• Filed: 09/03/1999
• Issued: 05/01/2001
• Est. Priority Date: 06/23/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Mani, Raghu, Draaijer, Jacco, Voss, Eric, Bodamer, Roger
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Corrielus, Jean M.
• Application Number:
  US09/390,028
• Time in Patent Office: 606 Days
• Field of Search: 707/10, 707/2, 707/100, 707/3, 707/103, 707/104, 707/102, 709/203, 709/217, 709/245, 709/246
• US Class Current: 709/203
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: Y10S 707/971 Federated
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

An apparatus and method for accessing foreign processes in a heterogeneous database environment includes a local database server having heterogeneous services to selectively send requests to the foreign processes based on their respective capabilities. A client application sending a statement to the local database server is checked by the local server process to determine if the statement includes a reference to a foreign database system. The local server process selectively outputs a request to an agent process in communication with a foreign database via a generic Application Programming Interface (API). The request output to the foreign database is based on accessing a capabilities table specifying the operations that can be executed by the foreign database. Hence, the heterogeneous services within the local server process manages client statements involving foreign database systems having limited capabilities, and uses the agent process to manage interactions with the foreign database systems, including converting data, to preserve the integrity of the local server process and provide the appearance to the client application of a homogeneous distributed database system.

Claims

1. A method for processing a statement from a client, comprising the steps of:

receiving the statement at a local database system, said statement specifying a plurality of operations that need to be performed to execute the statement;

identifying at least one of the operations specified by the statement to be performed by a foreign database system, wherein said at least one of the operations includes an access to metadata in a structure supported by the local database system and not supported by the foreign database system;

generating a request for the foreign database system to perform said at least one of the operations based on the statement specifying said at least one operation, wherein the request includes an access to metadata in a structure supported by the foreign database system; and

sending the request to the foreign database system to perform said at least one of the operations.

2. The method of claim 1, wherein the step of generating a request for the foreign database system includes the steps of:

accessing a data dictionary translation table specifying data dictionary table names and respective translation instructions for data dictionary structures in the foreign database system; and

generating the request based on the data dictionary table names and respective translation instructions for the data dictionary structures in the foreign database system.

3. The method of claim 2, wherein the step of accessing a data dictionary translation table includes the steps of:

identifying said foreign database system based on the statement; and

identifying metadata stored in the foreign database system needed to process said at least one of the operations.

4. The method of claim 1, wherein the step of generating the request includes the step of converting the statement into a query to retrieve the metadata stored in the foreign database system needed to process said at least one of the operations.

5. The method of claim 4, further comprising the step of performing a group of said operations not performed by the foreign database system based on retrieval of the metadata stored in the foreign database system needed to process said at least one of the operations.

6. The method of claim 4 further comprising the steps of:

receiving a response to the request from the foreign database system, the response including the metadata stored in the foreign database system needed to process said at least one of the operations; and

performing a group of said operations not performed by the foreign database system based on the response to the request from the foreign database system.

7. The method of claim 6, further comprising the step of completing execution of said at least one of the operations based on the received metadata stored in the foreign database system needed to process said at least one of the operations.

8. The method of claim 1, wherein the step of sending a request includes the steps of:

generating the request in a first format readable by the database server;

sending the request to an agent process;

converting the request into a second format readable by the foreign database system in the agent process; and

sending the converted request having the second format from the agent process to the foreign database system.

9. A method for processing a statement from a client, comprising the steps of:

receiving the statement at a local database system, said statement specifying a plurality of operations that need to be performed to execute the statement;

identifying at least one of the operations specified by the statement to be performed by a foreign database system, wherein said at least one of the operations includes an access to metadata in a structure supported by the local database system and not supported by the foreign database system;

converting the statement specifying said at least one of the operations to be performed by a foreign database system into a request based on stored data describing data dictionary translations for the foreign database system; and

sending the request from the local server process to the foreign database system to perform said at least one of the operations for processing of the statement, wherein the request includes an access to metadata in a structure supported by the foreign database system.

10. A computer-readable medium having stored thereon sequences of instructions for responding to a request for performance of an operation received by a local server process, the sequences of instructions including instructions for performing the steps of:

receiving the statement at a local database system, said statement specifying a plurality of operations that need to be performed to execute the statement;

identifying at least one of the operations specified by the statement to be performed by a foreign database system, wherein said at least one of the operations includes an access to metadata in a structure supported by the local database system and not supported by the foreign database system;

generating a request for the foreign database system to perform said at least one of the operations based on the statement specifying said at least one operation, wherein the request includes an access to metadata in a structure supported by the foreign database system; and

sending the request to the foreign database system to perform said at least one of the operations.

11. The computer-readable medium of claim 10, wherein the sequences of instructions further comprise sequences of instructions for performing the step of:

completing execution of the statement based on results received from the foreign database.

12. A computer-readable medium having stored thereon sequences of instructions for responding to a request for performance of an operation received by a server, the sequences of instructions including instructions for performing the steps of:

receiving the statement at a local database system, said statement specifying a plurality of operations that need to be performed to execute the statement;

identifying at least one of the operations specified by the statement to be performed by a foreign database system, wherein said at least one of the operations includes an access to metadata in a structure supported by the local database system and not supported by the foreign database system;

converting the statement specifying said at least one of the operations to be performed by a foreign database system into a request based on stored data describing data dictionary translations for the foreign database system; and

sending the request from the local server process to the foreign database system to perform said at least one of the operations for processing of the statement, wherein the request includes an access to metadata in a structure supported by the foreign database system.

13. A computer system configured to respond to a statement, comprising:

a computer-readable medium storing a data dictionary that includes a data dictionary translation table specifying mapping information on how to map from a local data dictionary table to a foreign data dictionary table of a foreign database system; and

a local server process configured for receiving the statement from the client and determining operations necessary for execution of the statement,

wherein the local server process includes a heterogeneous services module configured for;

identifying at least one of said operations specified by the statement to be performed by a foreign database system, wherein said at least one of the operations includes an access to metadata in a structure supported by the local server process and not supported by the foreign database system;

converting the statement specifying said at least one of the operations to be performed by a foreign database system into an operation request based on stored data describing data dictionary translations for the foreign database system; and

sending the operation request to the foreign database system to said at least one of the operations, wherein the operation request includes an access to metadata in a structure supported by the foreign database system.

14. The system of claim 13, wherein the heterogeneous services modules is further configured to control function translation of a function referenced in a query from a first format readable by the local server process to the format readable by the foreign database system.

15. The system of claim 14, wherein the heterogeneous services module is further configured for mapping the operation request from a native Application Programming Interface (API) protocol and a generic API.

16. The system of claim 15, wherein the heterogeneous services module is further configured for post-processing the results received from the foreign database system into a format suitable for the local server process completing execution of the statement.

17. The system of claim 13, wherein the data dictionary further includes a capabilities table specifying operations executable by the foreign database system, the local server process sending the operation request based on accessing the capabilities table.

18. The system of claim 17, wherein the local server process is further configured for translating a use of the local data dictionary table specified in the operation request to a corresponding use of the foreign data dictionary table in response to accessing the data dictionary translation table.

19. The system of claim 13, further comprising an agent process in communication with the foreign database system and configured for receiving the operation request from the local server process, converting the operation request to a format corresponding to the foreign database system, and outputting the converted operation request to the foreign database system, the agent process forwarding the results from the foreign database system to the local server process;

wherein the data dictionary further includes a capabilities table specifying operations and transaction capabilities executable by the foreign database system; and

the heterogeneous services modules further includes a transaction module configured to coordinate transactions between the local server process and the foreign database system, the transaction module generating a transaction message to the agent process based on corresponding transaction capabilities.

20. The system of claim 19, wherein the agent process includes a second transaction module for mapping the transaction message from a first format readable by the local server process onto an Application Programming Interface (API) for translation by a foreign database driver, the foreign database driver translating the transaction message from the API to a second format readable by the foreign database system for executing the transaction message.

21. The system of claim 20, wherein the second transaction module maps a transaction response, generated by the foreign database system in response to executing the transaction message, from the API to the first format, the foreign database driver having converted the transaction response from the second format to the API.

22. The system of claim 21, wherein the transactions coordinated between the local server process and the foreign database system include a distributed transaction between the local server process and the foreign database system.

23. The system of claim 22, wherein the distributed transaction is a two-phase commit-distributed transaction.