Apparatus and Method for Passing Statements to Foreign Databases by Using a Virtual Package

This application is related to commonly-assigned, application Ser. No. 08/880,326 filed Jun. 23, 1997, entitled "Apparatus and Method for Calling External Routines in a Database System," inventors Jacco Draaijer, Roger Bodamer, and Eric Voss, the disclosure of which is incorporated in its entirety herein by reference.

This application is related to commonly-assigned, application Ser. No. 08/880,333 filed Jun. 23, 1997, entitled "Apparatus and Method for Accessing Foreign Databases in a Heterogeneous Database System," inventors Roger Bodamer, Eric Voss, and Jacco Draaijer, the disclosure of which is incorporated in its entirety herein by reference.

This application is related to commonly-assigned, application Ser. No. 08/880,327 filed Jun. 23, 1997, entitled "Apparatus and Method for Transparent Access of Foreign Databases in a Heterogeneous Database System," inventors Roger Bodamer, Jacco Draaijer, Eric Voss, Raghu Mani, the disclosure of which is incorporated in its entirety herein by reference.

1. Field of the Invention

This invention relates to database systems and, more specifically, to an apparatus and method for interacting with foreign databases in a heterogeneous database environment.

2. Background of the Invention

Heterogeneous database environments are environments that contain different, and often incompatible, types of database systems. These different database systems are typically purchased independently by businesses to serve a particular need, function or use of the data. As a result, businesses may have information spread across multiple database management systems. Since every database management system vendor attempts to provide a competitive edge over the offerings of its competitors, the different database management systems are almost by definition incompatible.

Even database systems based on SQL language standards and the relational model will be incompatible due to differences in SQL implementations, database definition and communication mechanisms. Incompatibility problems are even more apparent between modern relational database management systems and legacy database management systems that use a hierarchical or network architecture. Hence, the task of managing interactions between incompatible database systems is extremely complex.

Ideally, each client in a heterogeneous database environment interacts with a single database server (the client's "local" server), and the local database server interacts with other database servers ("foreign database servers") to the extent necessary to service the requests of the client. For example, a client could send a request for operations A and B to be performed to a first server, even though operation B has to be performed by a second server. The first server would have the second server perform the operation B, and send the results back to the first server. The first server would present the results of both operations A and B to the client.

One problem associated with heterogeneous database environments is that a local database server may receive a client request that specifies an operation to be performed by a foreign database server, where the statements in the request are in a syntax supported by the second database server but which is unrecognizable by the first database server. For example, the statement for performing operation B in the example given above may use a syntax that is not supported by the first server.

According to one approach, requests that include an unrecognizable statement may include an "escape clause" preceding the statement. The escape clause would signify to the local server that the escape clause's argument was a statement that is for execution by a foreign server (a "foreign statement"). The local server passes the foreign statement to the foreign database without any attempt to interpret or process the foreign statement. In addition, the server may assume that any unrecognizable statement is intended for the foreign database. Consequently, the foreign server could be inundated with foreign statements or any random statements (including erroneous client statements) that are not understood by the foreign server.

In addition, the attempt to pass foreign statements using an escape clause suffers from the disadvantage that the local server does not know if any results will be returned from the foreign database. For example, if the foreign statement is a query, the local server will be unable to identify the data type of the returned data, let alone that the local server will not know that any data will be returned at all. Hence, the local server is unprepared to accept the results from the foreign database.

There is a need for an arrangement that provides scalable integration of foreign databases in a heterogeneous database environment, where operations necessary for execution of a client statement and that cannot be performed by a database server are sent to a foreign database system based on a reference to the foreign database system.

There is also a need for an arrangement that enhances a local database system in a heterogeneous database environment by integrating operations of foreign databases, where a statement having a required operation that cannot be performed by the local server is executed in a foreign process, and where the local server process completes execution of the statement based on reception of expected results from the foreign process.

These and other needs are attained by the present invention, where an interface module sends operations that cannot be performed by the local server process to an agent process in communication with a foreign process, and a local server selectively establishes memory space for expected results from the agent process and completes execution of a client statement based on the required results received from the foreign process via the agent process.

According to one aspect of the invention, a computer system configured to respond to a statement includes a local server process configured for determining operations necessary for execution of the statement, the local server process selectively establishing memory space for expected results, and an interface module configured for sending one of the operations that cannot be performed by the local server process to an agent process in communication with a foreign process based on a corresponding reference in the statement. The local server process, having selectively established memory space for the expected results, completes execution of the statement based on reception of the expected results from the foreign process. The agent process thus provides a consistent virtual interface that controls the passing of foreign statements to a foreign process. Hence, the agent process can selectively collect a group of foreign statements before passing the statements to the foreign process. Moreover, the local server process is capable of anticipating results from the foreign process, providing coordination in the execution of the client statement within the heterogeneous database environment.

Another aspect of the present invention provides a method for processing a request from a client, including the steps of receiving the request by a database server, the request including a statement that can be performed by a foreign database server and a reference to the foreign database server, and executing a procedure in the database server to process the statement based on the reference to the foreign database server. The step of executing the procedure includes the steps of sending to an agent process in communication with the foreign database server at least one operation necessary for execution of said statement and that cannot be performed by the database server, and completing processing of the statement based on results received from the foreign database server via the agent process.

Additional objects, advantages and novel features of the invention will be set forth in part in the description which follows, and in part will become apparent to those skilled in the art upon examination of the following or may be learned by practice of the invention. The objects and advantages of the invention may be realized and attained by means of the instrumentalities and combinations particularly pointed out in the appended claims.

The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings in which like reference numerals refer to similar elements and in which:

FIG. 1 is a block diagram of a computer system that may be used to implement an embodiment of the invention;

FIGS. 2A and 2B are diagrams illustrating an database server architecture for a heterogeneous database environment according to an embodiment of the present invention;

FIG. 3 is a diagram illustrating an exemplary implementation of the database server architecture of FIG. 2 in a distributed environment;

FIG. 4 is a diagram illustrating the steps of registering a foreign process to the database server architecture of FIG. 2;

FIG. 5 is a diagram illustrating the method for processing a statement from a client according to an embodiment of the present invention.

FIGS. 6A and 6B are flow diagrams illustrating the passthrough operations of FIG. 5 for foreign non-select operations and foreign select operations, respectively.

A method and apparatus for processing statements by accessing foreign processes is described. In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, to one skilled in the art that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.

FIG. 1 is a block diagram of a computer system 100 upon which an embodiment of the present invention can be implemented. Computer system 100 includes a bus 101 or other communication mechanism for communicating information, and a processor 102 coupled with bus 101 for processing information. Computer system 100 further comprises a random access memory (RAM) or other dynamic storage device 104 (referred to as main memory), coupled to bus 101 for storing information and instructions to be executed by processor 102. Main memory 104 also may be used for storing temporary variables or other intermediate information during execution of instructions by processor 102. Computer system 100 also comprises a read only memory (ROM) and/or other static storage device 106 coupled to bus 101 for storing static information and instructions for processor 102. Data storage device 107 is coupled to bus 101 for storing information and instructions.

A data storage device 107 such as a magnetic disk or optical disk and its corresponding disk drive can be coupled to computer system 100. Computer system 100 can also be coupled via bus 101 to a display device 121, such as a cathode ray tube (CRT), for displaying information to a computer user. Computer system 100 further includes a keyboard 122 and a cursor control 123, such as a mouse. The present invention is related to the use of computer system 100 to process statements by accessing foreign processes. According to one embodiment, the processing of statements by accessing foreign processes is performed by computer system 100 in response to processor 102 executing sequences of instructions contained in memory 104. Such instructions may be read into memory 104 from another computer-readable medium, such as data storage device 107. Execution of the sequences of instructions contained in memory 104 causes processor 102 to perform the process steps that will be described hereafter. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the present invention. Thus, the present invention is not limited to any specific combination of hardware circuitry and software.

FIGS. 2A and 2B are diagrams summarizing an architecture for a database server process in a heterogeneous environment according to an embodiment of the present invention. FIG. 2A illustrates the overall architecture 150, and FIG. 2B illustrates a diagram of metadata definitions for use by the database server, described below. As shown in FIG. 2A, a client process 200, for example an application program, supplies a statement such as a SQL statement to a local server 202. The local server 202 includes routines for providing the basic services necessary for performing local server operations, for example memory services or context services. The local server 202 performs conventional query processing when processing native requests for data directly accessible by the local server 202, for example data stored in a local database 306. In a "homogeneous database system," the local server 202 can coordinate with a remote server from the same vendor in a distributed environment for coordinating commitments to respective transactions.

As described in detail below, the client statement may require an operation to be performed by a non-native (i.e., foreign) process, such as a foreign database server (FDS) 208 or an external routine. For example, the client statement may request data from a foreign database 308 under the control of a foreign database server 208. In this case, the local server 202 cannot complete execution of the statement without receiving the data from the foreign database server 208. Alternatively, the client statement may also include a request to be performed by the foreign database server 208 but without returning data, for example creating a new data table or updating data in the foreign database 308. The client statement may alternatively include a call to a procedure that is implemented by the foreign database server 208, but not by the local server 202.

The client statement may also call an external routine that extends the capabilities of the local server 202 beyond a standard set of commands. Specifically, users interact with many database systems by submitting SQL statements to the database systems. To supplement the functionality of a limited standard set of SQL commands, external routines (i.e., "extensions") can be coded, potentially by third parties, in a third generation language (3GL) such as C or C++. However, it is desirable to control the execution of the external routines to ensure that failures in the external routines do not adversely affect the local server 202. For example, a common bug in a C function de-references an invalid pointer and causes a segmentation fault, resulting in a crash.

The heterogeneous architecture of FIG. 2A includes heterogeneous services modules 311 and 311' that augment the distributed functionality of the local server 202 to enable non-native database servers 208 to be incorporated into the distributed architecture. The module 311 is executed in the local server process 311 and the module 311' is executed in an agent process 300. The local server process 202 communicates with the agent process 300 via a remote process mechanism in the foundation services 204. The foundations services 204 perform the basic services that are needed for processing heterogeneous operations. The basic services include memory handling, establishing the proper context (e.g., login and logoff), and the remote process mechanism. The remote process mechanism provides a framework that allows the local server 202 to operate in a heterogeneous environment (e.g., a distributed system having servers from different vendors).

The agent process 300 also enables external routines (e.g. C functions) stored in a dynamically-linked library (DLL) 314 to be executed without concern of crashing the local server 202. Thus, the agent process 300 provides an interface between the local server 202 and a foreign database server 208 that ensures that any operation of the foreign database server 208 will not adversely affect the local server 202. Furthermore, the agent process 300 may execute a driver 214, which has been provided by a third party such as the vendor of the foreign database server 208. Hence, the local server 202 is insulated from the routines executing in the agent process 300 to protect the local server 202. If the external routines executing in the agent process 300 are later deemed reliable or safe, the remote process mechanism can be removed and code for the agent process 300 can be linked directly into the local server 202.

The agent processes 300 are separate processes, started by the heterogeneous services 311 of the local server 202 and executed in an address space separate from the local server 202. Hence, the agent process 300 protects the local server 202 from crashes that may occur in the agent process 300 during execution of a non-native routine. In addition, the agent process 300 may be executed on a machine separate from the local server 202.

The heterogeneous services modules 311 and 311' enable the local server 202 to complete execution of client statements that require execution of non-native operations. Specifically, the local server 202, upon receiving a client statement having a database link to a remote process, will access a remote link table that is stored on the file system of the local server 202. The remote link table includes information that specifies how to a point to a communication link to a remote process based on the database link specified in the client statement. Specifically, the remote link table uses the database link as an alias to identify (1) the remote process executing on a remote machine, (2) link-related information such as network address, TCP/IP port number, etc., to find out exactly which process to connect to, and (3) whether the database link points to a foreign system. If the remote link table specifies that the referenced database link points to a foreign system, the local server 202 will invoke the heterogeneous services using the first heterogeneous services module 311 in the local server 202, which in turn invokes the second heterogeneous services module 311' in the agent process 300.

The first heterogeneous services module 311, which is built upon the foundation services 204, performs all possible operations that do not require the interaction of the external process. For example, the first heterogeneous services module 311 helps identify an operation necessary for execution of the client statement that must be executed by an external process (e.g., an external routine or a foreign database system), and forwards the identified operation to the appropriate agent process 300 via the identified remote path for execution. As described below, the first heterogeneous services module 311 includes specific services modules for controlling respective operations.

The agent process 300 includes foundation services 204' that complement the operations of the foundation services 204 in the local server 202. For example, the foundation services 204' maintains the communication path 310 with the local server 202 by handling the requests received from the local server 202 and sending results during execution of the agent process 300 to the local server 202. The communication path 310 can be a shared memory or other inter-process connection or a network connection. The second heterogeneous services module 311', which is built upon the foundation services 204', manages the operations requiring interaction with the third-party code, whether the third-party code is an external routine stored in the dynamically linked library 314, or a driver communicating with the foreign database server 208. Hence, the first heterogeneous module 311 will perform all operations that do not require an actual interface or interaction with third-party code, whereas the second heterogeneous module 311' will preferably perform only those operations associated with interacting with the third-party code, such as data translation between the conversion module 211 and the third-party driver 214 or execution of a third-party external routine from the library 314.

The first heterogeneous services module 311 includes a plurality of modules that enable the local server 202 to interact with the foreign processes via the appropriate agent process 300. Specifically, the heterogeneous services include a transaction services module 210a, a SQL services module 210b, a stored procedures module 210c, and an external procedures module 210d. As described below, the heterogeneous services in the local server 202 also include a passthrough SQL module (PSM) 207 that operates separately from the modules 210. Each of the modules 210a, 210b, 210c, and 210d, are built upon the foundation services 204, and are integrated with the local server 202 to use services within the local server 202, as shown by the dotted line 209 in the heterogeneous services.

The dotted line 209 refers to a native API interface, described below, used by the local server 202 to issue calls to a remote server. Instead of sending the API calls directly to the foreign database system 208, the heterogeneous services module 311 intercepts the calls to the foreign database system 208 and remaps the API calls by the local server 202 to a generic API 212. The heterogeneous services module 311 will then issue a remote procedure call to the agent process 300 to call a selected function in the driver 214. The agent process 300 will then call the selected function in the driver 214 (e.g., "parse").

Each of these modules 210 in the heterogeneous services module 311 is configured to map a particular database operation to a target foreign process based upon the operation specified in the client statement and based upon metadata definitions metadata for the heterogeneous services stored within a data dictionary 220, described below. As shown in FIG. 2B, the data dictionary 220 includes metadata definitions for use by the foundation services 204, as well as for the different modules 210 and 207.

There are four types of translations that may occur between the local server 202 and the foreign database server 208. The first type of translation relates to the Application Program Interface (API) of a given database system. Specifically, an API is a set of functions, procedures, or routines specific to a module, such as an application or library, that can be called by an external module to communication with the module. For example, Oracle has a set of API referred to as OCI API, where functions such as "oopen", "oparse", "oexec", "ofetch", and "oclose" correspond to the functions of opening a cursor, parsing a SQL statement, executing the cursor, fetching results, and closing the cursor, respectively. However, one database system (e.g., Oracle) will have its own API set, while another database system (e.g., Sybase) will have its own API set. Since the API for each database system can be completely different, it becomes particularly difficult to develop any systematic translation scheme.

Hence, the first type of translation, executed by the heterogeneous services module 311 in the local server 202, maps the native API protocol (e.g., Oracle API) onto a generic API, identified as the HS API 212 in FIG. 3A. The driver 214 for a specific foreign database server 208 can then map the generic API 212 to the API set of the foreign database server 208.

A second type of translation relates to SQL statements, which are highly structured. Hence, although a SQL statement in the format of the local server 202 may not be acceptable to the foreign database system 208, the structured nature of a SQL statement enables the heterogeneous services modules 311 to convert the SQL statement of the local server 202 to the format of the foreign database system 208 using a SQL services module 210b. In addition, API functions such as "parse" are performed by a majority of database systems. Hence, a generic function such as "parse" for a certain SQL statement can be passed to the foreign database system 208 using the modules 311 in the local server 202 by translating the SQL statement from the native (local server process 202) format to the format of the foreign database system 208. The translated SQL statement in foreign database system format can then be passed as an argument of a generic function (e.g., parse). For example, an SQL statement translated by the SQL services module 210b can be an argument for the Oracle-specific call "opiosq," which is then mapped onto the generic API 212 as "parse." Thus, the generic function is called at the HS API 212 having the translated SQL statement as an argument. The driver 214 then can map the generic function onto the foreign database system API, while including the translated SQL statement as the argument.

A third type of translation relates to data types, where the local server 202 represents data (e.g., dates) in a different format than the foreign database system 208. Hence, data must be converted in the agent process 300 if the local server 202 is fetching results from the foreign database system 208, or sending or receiving bind variables to and from the foreign database system 208. This data type translation is executed in the agent process 300 by a conversion module 211.

A fourth type of translation is data dictionary translation, executed in a SQL services module 210b within the heterogeneous services module 311 of the local server 202. Every relational database has its own set of data dictionary tables which store various kinds of information (known as "metadata") about the objects in the database created by its various users. This set of tables along with views defined on them are together called the "data dictionary". As a user makes modifications to his schema (for example, creating or deleting a table), the local server 202 will keep track of the modification by automatically adding or deleting entries in one or more tables of the data dictionary. The foreign database system 208, however, may include similar metadata that is organized differently. In order to make the foreign database system 208 appear to the client 200 as homogeneous relative to the local server process, the client must be given the impression that the metadata in the data dictionary of the foreign database system 208 is in the same format as in the data dictionary of the local server 202. For example, assume the data dictionary for the local server process 202 includes a table entitled "user.sub.-- catalog," and the client 200 issues a query on "user.sub.-- catalog @ FDS", where "FDS" is an identifier for foreign database system 208. However, if the foreign database system 208 does not have the table "user.sub.-- catalog", but rather the metadata is spread across different tables, the client 200 is given the appearance that the table exists at the foreign database system 208 in the structure perceived by the client.

Hence, the data dictionary translation gives the appearance to the client that the foreign database system 208 includes metadata in the format and structure as stored for the local server 202. Thus, the heterogeneous services modules 311 and 311' map the query to accommodate the data dictionary structures in the foreign database system 208, get the result back from the foreign database system 208, and return the results to the client 200 in the format of the local server 202.

In an example of a data dictionary translation from an Oracle server to a Sybase server, the heterogeneous services modules 311 and 311' convert the client statement "select table.sub.-- name, table.sub.-- type from user.sub.-- catalog @ link" to a Sybase-compatible query, for example:

This gives the client 200 the impression that there actually is a table called "user.sub.-- catalog" at the remote server and that it actually does have columns called "table.sub.-- name" and "table.sub.-- type," when actually the information is extracted from two Sybase data dictionary tables "sysusers" and "sysobjects".

Hence, the data dictionary for the local server 202 may include tables that are not found on in the data dictionary of the foreign database system 208. If the local server 202 receives a query from client 208 referencing one of these tables with respect to foreign database system 208, the local server 202 first attempts to use that table on the foreign database system 208. If the table does not exist at the foreign database system 208, an error is returned and, in response, the heterogeneous services module 311 checks a data dictionary translation table (DDX) 231. If translation information is found, the query is translated and sent to the foreign database system 208 via the agent 300. The results of the query from the foreign database system 208 are then translated before being back sent to the client 200.

Another aspect of data dictionary translation occurs when a data dictionary table for the local server 202 includes information that is not present in the referenced foreign database system 208. Assume that a client statement includes a reference to a data dictionary table at the foreign database system 208, in which the information does not exist at the foreign database system 208 according to the capability table 224 and the DDX 231. To give the client 200 the appearance that the table exists in the foreign database system 208, the DDX 231 may include a reference for the heterogeneous services module 311 to mimic the existence of the table in the foreign database system 208 by going to the local database and requesting from the local database the definition of the requested table. The heterogeneous services module 311 then returns to the client 200 (via the local server 202) the definition of the table. If the client 200 requests information, the local server 202 will return an error message that no rows are selected.

Hence, all the translations are performed by the heterogeneous services module 311 in the local server process, except for the data type conversion, which is performed in the agent process 300.

The agent process 300 includes services and routines to carry out prescribed operations for the local server 202. Specifically, a plurality of different agents 300 can be initiated by the heterogeneous services of the local server 202 to interact with respective foreign database servers 208. For example, one agent 300a may interact with a Sybase database system, and another agent 300b may interact with an Informix database system, etc. Each agent 300 includes the foundation services 204' and a conversion module 211, described below. Each agent also includes a corresponding set of services modules 210a', 210b', 210c' and/or 210d' that correspond to the capabilities of the corresponding foreign database system. Hence, if a foreign database server 208 does not have transaction services, the corresponding agent 300 interacting with that foreign database server 208 will not need a transaction services 210a'. Alternatively, the transaction services 210a' may be available in the compiled code for the agent process 300 but unused because the metadata stored in the data dictionary 220 does not identify that foreign database system as supporting that service.

The conversion module 211 provides the data translation services necessary for converting from the data format native to the local server 202 to the data format of the target system 208. Three types of data type conversion are possible. In the first type, the conversion module 211 contains all the necessary conversion routines to convert from the native data type of the local server 202 to the data type of the target system 208. In the second type, the conversion module 211 converts from the native data format based on routines supplied by a driver 214, where the conversion routines within the driver 214 may be supplied, for example, by the manufacturer of the target system 208. In the third type, the native data type is described in terms of an intermediate data type to perform an n-step conversion. For instance, one of the steps is performed by a conversion originally supplied with conversion module 211 and another of the steps is performed by a conversion function registered by the driver 214. Regardless of the conversion type, the data type translation is performed in the agent process 300, transparent to the local server 202.

In the case of the second and third type of data type conversion, the driver 214 registers conversion routines having a predetermined prototype with the conversion module 211. The predetermined prototype specifies the number and type of the arguments of a conversion routine as well as the type of the return value, if it exists. Thus, the conversion module 211 sends data (i.e., an array of numbers) invokes a registered conversion routine of the driver 214 for translating the data to the format of the foreign database system. Alternatively, the driver 214 may specify an intermediate n-step conversion, where the conversion module 211 translates from the native data type to an intermediate data format specified by the driver 214, enabling the conversion routine of the driver 214 to convert from the intermediate data format to the internal format of the foreign database system 208. The intermediate date format can be an industry-standard data format, such as the ISO date format, to simplify the development of the driver 214 by third parties.

Thus, the local server 202 uses the heterogeneous services to output an operation request (e.g., an API call or an SQL statement) to a target foreign database (e.g., foreign database 208) by sending the request to the agent process 300. The conversion module 211 in the agent process 300 converts the data types to the format of the foreign database server 208, using internal conversion routines or registered routines supplied by the driver 214. Use of the agent 300 to execute registered routines supplied by the driver 214 thus protects the local server process 202 from crashes. The translated data in the foreign format is output from the agent process 300 to the foreign database server 208 to process the operation request. Once the foreign database server 208 generates results based upon processing the operation request, the data results are translated by the conversion module 211 to the data type of the local server 202 in accordance with the appropriate services module 210'. The local server 202 can then complete execution of the client statement based upon the results received from the foreign process 208b.

FIG. 2B is a diagram illustrating a part of a data dictionary 220 providing metadata definitions for heterogeneous services. The data dictionary 220, stored in the local database 306, enables the local server 202 to determine how to process a received client statement. As described below, a foreign server 208 can be accessed either by referencing the specific foreign system, by referencing the heterogeneous services 210 configured to identify the appropriate foreign system, or by providing the local server 202 with definitions enabling the local server 202 to independently identify a call to a foreign server 208. The data dictionary 220 includes an initialization table 222 that specifies the information needed to startup and initialize an agent and connect it to a foreign database server 208. The initialization table 222 is used by all the services, including the foundation services 204, the transaction services 210a, the SQL services 210b, the stored procedures 210c, and the external procedures 210d. The values are defined partly by the heterogeneous services, and partly by the corresponding driver 214.

The data dictionary 220 also includes a capability table 224. The capabilities table 224 specifies the capabilities of the target system 208. For example, the capabilities table 224 includes a transaction services model 224a that describes the transaction model of the target system 208. Transaction models of target systems range from just read-only support at one end to full two-phase commit support at the other end. The SQL translations (SQL X) services model 224b provides SQL translation information on how to translate SQL functions in the local server 202 onto the functions of the target system 208 for the SQL services module 210b and the stored procedures module 210c, described below. The data type translations (DTY X) model 224c provides information on how to map foreign data types to data types native to the local server 202 for the SQL services module 210b and the stored procedures module 210c, as well as the conversion module 211.

The data dictionary 220 also includes a security table (SEC) 226 and a distributed external procedures metadata (DEP MD) table 228. The SEC table 226 stores information about users that have privileges to create, drop, alter, and/or execute an external procedure, and the DEP MD table 228 contains information to map a PL/SQL function onto an external procedure, such as a C function. The information in the DEP MD table 228 may include data types, number of arguments, the name of the function, indicator values, etc. Thus, the DEP MD table 228 stores metadata that is not relevant to the local server 202, but which is necessary to perform the distributed external procedure as it is passed to the agent process 300.

The data dictionary 220 also includes a data dictionary translation table (DDX) 231, described above, that contains a list of data dictionary table names and respective translation instructions for the data dictionaries in the foreign database system 208. The translation instructions will include the translation (mapping) information on how to map from the native data dictionary tables to the data dictionary tables of the foreign database system. Alternatively, if the foreign database system 208 does not include the corresponding metadata, the DDX 231 will include a mimic instruction for the corresponding mapping.

The data dictionary 220 enables the local server 202 to determine the relative capabilities of a target foreign server to process a statement in the agent process 300, and thus enables the local server 202 to call the appropriate services 210 to obtain the desired result.

The heterogeneous services in the local server 202 of FIG. 2A are performed by modules 210 that are built upon the foundation services 204 and integrated with the local server 202. The SQL services module 210b, also referred to as a query services module, controls translation of SQL statements identified for execution by a foreign database server 208 to the corresponding foreign format. Specifically, assuming that the client statement includes a SQL statement recognized by the local server 202 as referencing data in a foreign database (e.g., foreign database "DB.sub.-- A" 208a), the local server 202 parses the SQL statement into operations necessary for execution of the statement. The DTY X table 224c provides the data translations to translate the data object definitions. The SQL services module 210b obtains the mapping information from the DTY X table 224c, and sends the mapping information to the agent process 300 for the conversion module 211 to perform the data type translation.

Hence, all the translation information is stored in a table in the local database 308. The mapping information stored in the data type translation table 224c is then sent by the HS 311 to the agent 300 for data type translation.

The transaction module 210a is configured to coordinate transactions between the local server 202 and the corresponding foreign server 208, for example distributed transactions such as a two-phase commit transaction as performed in current commercially available Oracle database systems. Based on the capabilities specified in the capabilities table 224, the transaction module 210a can make transaction related calls, such as "begin transaction," "end transaction," and "commit," to the agent process 300. The corresponding module 210a' passes these generic transaction calls on to the driver 214, which maps the transactions calls into transaction calls appropriate for the target system 208.

The stored procedures module 210c is used to perform a "describe operation" dynamically for an operation within the client statement. Specifically, the local server 202 sends a request to the target system 208 to describe the specified operation, and will receive a response that describes the number and types of the parameters for performing the operation. For example, a procedure may be described as having five prescribed arguments. The response enables the stored procedures module 210c to determine how to use the SQL services module 210b for data type conversions. Thus, the stored procedures module 210c enables a function to be invoked at the target system 208 (via the agent process 300) by identifying the operation to be performed, specifying the arguments, and requesting that the operation be executed. This module is thus particularly helpful for executing stored procedures (e.g., fourth-generation (4GL) routines) in the target system 208.

The external procedures module 210d is configured to control execution of external procedures, also referred to as external routines, for which there is not a description of the function provided by a foreign database server 208, but rather is intended to be executed as an enhancement to the local server 202. In other words, the external routine is implemented as a C function that is incapable of describing itself. In this case, the metadata needs to be separately generated based on an analysis of the function and stored in the DEP MD table 228. Once the metadata for the external routine is stored in the DEP MD table 228 as described below, the external routine can be called by causing the agent process to call the external routine from a specified address in the DLL 314, followed by passing of any necessary parameters to the agent process 300.

According to the disclosed embodiment, the local server 202 may selectively use any of the services modules 210a, 210b, 210c, or 210d to perform a certain operation. For example, the transaction services 210a may be used in combination with the SQL services 210b for a database gateway. The external procedures 210d may be used with the transaction services 210a for distributed external procedures.

FIG. 3 is a diagram illustrating an exemplary implementation of the heterogeneous architecture of FIG. 2. Specifically, the agent process 300 may be executed in a second processing system 322 separate from the first processing system 304 executing the local server 202. In such an arrangement, the local server 202 accesses local data using solely the first processing system 304 by accessing a local database 306. However, if local server 202 needs to access foreign data in a foreign database 308, the local server 202 communicates with the agent process 300 via a network connection 310. For example, the statement from the client application 200 may be a SQL statement "select * from table @ FDS," where "FDS" is identified by the local server 202 as a reference to the foreign database system 208. If there is no prior connection with the remote computing system 302, the foundation services 204 will establish a connection 310 to a listener agent of the remote computing system 302, which will spawn the agent process 300. The foundation services 204 will then initialize the agent 300, log on for verification (e.g., log on using a user name and password), and send the statement to the agent process 300. The agent process 300 receives the statement from the local server 202, and uses its foundation services 204' and associated modules 210 and 211 (shown generically as "HS Generic" 311') to send the appropriate statement to foreign server 208. If the conversion module 211 does not have its own internal conversion routine, the agent process 300 will send the translated request to the foreign server 208 via the API 212 using the foreign database driver 214.

It may be desirable in certain instances for the client application 200 to be aware that a foreign system is accessed. For example, the client application 200 may wish to send an SQL statement to the foreign server 208, in the foreign SQL syntax, in an attempt to access or manipulate data in the foreign system. In this case, an attempt by the local server 202 to process the statement having the foreign protocol expression would otherwise result in an unrecognizable command. Hence, the architecture of FIG. 2A includes a passthrough module 207 that allows the client application 200 to send a foreign SQL statement to the foreign server 208 without processing by the local server 202. For example, the client application 200 may submit an SQL statement that invokes a procedure that is not implemented by the local server 202. As described below, a multiple calls to the passthrough module 207 can be bundled into a single foreign client statement provided to the API 212. For example, the foreign server 208 can receive a single call having multiple bind variables, described below, as opposed to multiple calls of single bind-variables made to the passthrough module 207.

Whereas the local server API 209 and the generic API 212 are based on C functions, the passthrough SQL API 265 for the passthrough module 207 includes a set of enhanced procedural SQL functions executable by the local server process, for example PL/SQL functions provided by Oracle Corporation. These passthrough SQL API 265 functions can be called by the client 200 by passing as an argument the foreign SQL statement to be executed by the foreign database system 208. As far as the local server 202 is concerned, the client 200 is issuing a call to a remote API function (e.g., using PL/SQL) that includes certain arguments. Thus, the local server 202 submits the call to the remote database system (in this case foreign database system 208) for execution, without examining the contents of the call. Hence, a "parse" call for a supplied foreign SQL statement can be made to the foreign database system 208, without the local server 202 knowing that the parse call has been made.

Hence, the client 200 executes a statement that calls a function of the passthrough SQL API 265. The local server 202 identifies the passthrough SQL API 265 statement as an operation to be performed at a remote site, because the statement includes a link name. Information associated with the link name identifies the heterogeneous services module 311 including the passthrough SQL module 207. The local server 202 then passes the client statement to the heterogeneous services module 311, which performs the necessary processing in the passthrough SQL module 207. As far as the local server process 202 is concerned, the client statement is a passthrough SQL API 265 function. Since the actual foreign SQL statement is an argument to the passthrough SQL API function, the foreign SQL statement will not be parsed by the local server 202.

The local server 202 submits the call by the client 200 to the heterogeneous services module 311, which intercepts the passthrough SQL API 265 call and maps the call onto the generic API 212. As a result, the number of relatively expensive network calls from the local server 202 to the foreign database system 208 can be minimized, especially where a passthrough SQL call by a client may include a bind value (e.g., a data value). Specifically, each individual passthrough SQL call invoked by the client 200 is not necessarily sent to the foreign database system 208, but collected into one multiple bind statement. After all bind calls from the client 200 (or from multiple clients 200) are received by the passthrough module 207, a single call is made from the collected bind variables to the generic API 212. Hence, the collection of bind variables by the passthrough SQL module 207 provides a much more efficient arrangement, since the single call to the generic API 212 is particularly efficient, especially for network traffic, resulting in considerable gains for performance.

FIG. 4 is a diagram illustrating steps to register a foreign server 208 with the local database server 202 for establishing the heterogeneous architecture of FIG. 2. A connection to the foreign server 208 is first established by registering an agent in step 400, enabling the foundation services 204 to set up a communication path 310 with the foreign system 208 via the corresponding agent process 300. As recognized in the art, commercially available listener agents can be configured to set up the agent process 302 based upon standardized API instruction sets. As described below, the listener agent (not shown) may control the initiation and connection of multiple agents to the local server process 202. Alternatively, the functions of the listener agent may be performed by the foundation services 204.

Once the listener agent (or the foundation services 204) has been configured in a way to start up the agent process 300, the data dictionary 220 is installed in step 402. Class information is then installed in the data dictionary table 220 in step 404. The data dictionary 220 stores class and instance information that identifies the capabilities of the foreign system and the objects corresponding to the foreign system 208, respectively. For example, the capabilities table 224 stores the foreign system capabilities, SQL translations, and the initialization table 222 stores initialization parameters, etc., for each foreign system as groups of classes to minimize unnecessary redundancy. Specifically, a class defines a type of foreign system. At the same time, instance specific information is necessary for each transaction performed by the local server 202. Accordingly, the capabilities table 224 can store both class and instance-level information. Hence, multiple instances can share the same class information, but each foreign system instance will have its own instance-level information. An example of a class is a foreign system from a particular vendor. Subclasses may also be subdivided within the class, for example, for individual versions of the vendor-specific database systems.

After the capabilities table 224 has been installed with the class information, the foreign system instance 208 is registered in step 406 with a new instance name and the class to be used to access the foreign system.

Instance-specific initialization parameters are then set in step 408 for each instance, for example a domain (virtual path) for a foreign system, a database name for the foreign system, an internal unique name used by the local server 202 for creating distributed transaction identifications, language settings for the foreign system, the maximum number of cursors (i.e., assigned memory spaces) that can be open, and the ability of the foreign system to become a commit point site in a distributed transaction. Agent-specific initialization parameters may also be required, for example, connection information required to connect to a foreign system.

Once the instance-specific initialization parameters have been set in step 408, then desired external routines are installed in the DLL 314 in step 410. The connection is then established in step 412 (e.g., a communication path 310) to test the access to the foreign processes 208.

The arrangement for heterogeneous statement processing for selectively passing foreign statements to foreign processes, embodied as the "passthrough SQL functionality", will now be described. FIG. 5 is a flow diagram illustrating a method for processing a statement from the client 200 according to an embodiment of the present invention. The steps of FIG. 5 illustrate the transparent SQL and passthrough SQL functionality of the architecture of FIG. 2.

As shown in FIG. 5, the local server 202 receives the statement, for example a SQL statement, in step 500 from the client 200. The local server 202 determines in step 502 whether the client statement includes a reference to a foreign database system, for example if the client statement includes a database link. A database link is an identifier that points to the foreign database system as specified in the remote link table. An example of a database link occurs in the SQL statement "select * from table @ DB.sub.-- A," where the remote link table identifies the database link "@ DB.sub.-- A" as a reference for the foreign database system 208. The reference to a foreign database system 208 by the database link implicates the use of heterogeneous services 210.

If the local server 202 determines the client statement includes a reference to a foreign database system 208, the local server 202 submits the client statement to the heterogeneous services 311. Heterogeneous services 311 determine whether the statement includes a call to the passthrough SQL module 207 from the user's schema and the data dictionary 220, step 504. If the client statement is a call using the passthrough SQL module 207, the client statement is intercepted and mapped onto the generic API 212 to the agent 300 corresponding to the referenced foreign database system 208 in step 506, as described above. Specifically, the heterogeneous services 311 identifies the appropriate communication path 310 based on the reference to the remote node (e.g., the database link "@ FDS" for the foreign database system 308). The passthrough SQL module 207 determines a generic operation to be performed for the statement by the remote node in step 506a. Such generic operations include: opening a cursor (a memory space), closing a cursor (close a memory space), parsing a statement, binding input variables, binding output variables, executing a non-query statement, fetching rows from query, and getting a column value from a select statement (or retrieve output bind parameters). The passthrough SQL module 207 collects bind values from the client 200 before making a call to the generic API 212. The generic operations carrying foreign statements as arguments are sent by the passthrough SQL module 207 to the agent process 300 to be mapped onto the API 212. The bind variables in the generic operations are bundled by the passthrough SQL module with the respective bind values and enable the local server 202 to anticipate the results from the foreign process. Hence, the local server 202 can allocate memory space to complete execution of the statement after receiving the expected results from the foreign process 208.

The passthrough SQL module 207 also accesses the mapping information in the data dictionary in step 506b to supply the agent process 300 with the mapping information between the passthrough SQL API and the generic API 212.

The foundation services 204 establishes the communication path 310 with the agent process 300 in step 506c. The passthrough SQL module 207 then maps the passthrough SQL API 265 carrying the foreign statement and bundles bind variables as arguments onto a single API 212 call by making a list of bind values corresponding to a set of bind variables. The foundation services 204 also passes any strings (in the format for foreign database system 208) included with the statement from the client 200. The agent process 300 receives the mapped passthrough SQL API call and its arguments (e.g., the foreign statement and any foreign parameters passed via the communication path 310). The generic API 212 submits the bundled passthrough SQL call to foreign server 208, thus minimizing the number of calls to the foreign server 208 for execution.

The agent process 300 receives the results from the foreign server 208 in step 508 into buffers of the heterogeneous services 311', and passes the results back through communication path 310, in step 510. The results are presented in step 512 to the client 200 in a set of bind variables, each having a corresponding set of bind values. For example, the statement from the client 200 may include the expression "foo(. . . , FDSX, FDSY, . . . )" where the expression "foo(args)" is a function performed by the local server process 202, and the expressions "FDSX" and "FDSY" are expressions specific to the foreign server 208. The expressions "FDSX" and "FDSY" would be passed onto the generic API 212 as "foo(. . . , FDSX, FDSY, . . . )" via the agent 300 in step 506 for processing by the foreign server 208. The results of the processing by foreign server 208 would be received from the agent process 300 in step 508. If the local server 202 recognizes the procedure "foo", the local server 202 would recognize that the enclosed (but unread) arguments "FDSX" and "FDSY" would cause the referenced foreign process to return results having a specified data type. Accordingly, the local server 202 is able to anticipate the results of the unrecognizable expressions "FDSX" and "FDSY" to be received from the foreign server 208. The local server 202 completes execution of the client statement in 510, and presents the results of the statements to the client in step 512.

As described above, a particular advantage of utilizing the passthrough SQL module 207 and the agent process 300 for the passthrough SQL operations of steps 506, 508, and 510 is that calls for sending data to the foreign server 208 need not be arbitrarily output to the foreign database system 208, thereby increasing the communication between the local server process 202 and the target system 208. Rather, data can be collected, and once a number of requests have been collected, a single call can be made to the target system 208. For example, bind variables enable the same SQL statement to be used multiple times with different values. Hence, if execution of a client statement requires inserting one hundred rows of one hundred columns into a particular table, the SQL statement needs to be parsed only once, and then the database process binds and executes the SQL statement for each row. Hence, the agent process 300 can send a single statement to the foreign server 208 including the bind variables, as opposed to sending a plurality of bind calls.

FIGS. 6A and 6B are flow diagrams illustrating in further detail execution of a client process 200 for non-select statements and select statements using the passthrough SQL function, respectively. Non-select statements do not retrieve information, but change data structures (i.e., data definition language (DDL) statements) or the data itself (e.g., insert, delete, and update).

Referring to FIG. 6A, a client process 200 in step 600 opens a cursor by making a call to passthrough module 207 with a database link reference specifying foreign server 208. Since the open cursor call was made through the passthrough SQL API 265 instead of a transparent SQL API, the cursor is actually opened locally, at the local server 202, not remotely. In step 602, the client process 200 issues a parse operation with a database link referring to the foreign server 208, which is performed remotely. In step 604, the client process 200 performs a series of bind variable statements, which are performed locally by passthrough module 207. In step 606, the client process 200 submits an execute command to passthrough module 207, which bundles all the bound variables and send the bound variables with the comment to foreign server 208 via communication path 310. The results of execution are presented to client process 200 in OUT bind variables, which can be retrieved through passthrough SQL API 265 in a series of locally performed operations. Finally, the client process 200 closes the cursor, also performed locally. The cursor can also be reused to parse new statements, eliminating the necessity of opening and closing the cursor.

Bind variables may also be used by the agent process 300 to execute the same SQL statement multiple times with different values. IN bind variables have a known value before the SQL statement can be executed, and are bundled prior to being sent by agent process 300 to the foreign server 208. In some cases, the foreign system 208 may also support OUT bind variables, in which the value of the bind variable is not known until after the SQL statement is executed. Although OUT bind variables are populated after the statement is executed by the foreign server 208, the foreign server 208 needs to know that the particular bind variable is an OUT bind variable before executing the statement. IN-OUT bind variables are bind variables where the value is known before the statement is executed, but can be changed by the execution of the statement.

If bind variables are used, then the foreign server 208 gets the next IN bind variable in step 604 for each execution. Similarly, the foreign server 208 determines the next value for the OUT bind variable in step 608.

FIG. 6B illustrates the step for using the passthrough SQL module 207 for a query statement. Steps 600 through 604 are performed as described above with reference to FIG. 6A. At step 612, however, the query statement to fetch a row by client process 200 results in the data from a number of rows of a table at foreign database 308 to be fetched. Accordingly, values from the columns of several rows can be obtained by client process 200 locally, without causing traffic to occur over communication path 310. When data from the number of rows is exhausted by client process 200, another batch of data is fetched from foreign database 308 via communication path 310. The cursor is closed in step 610 after all the rows are fetched, although the cursor can be closed at any time after opening the cursor. Use of passthrough SQL for queries results in the reduction of network traffic by batching many individual requests for data into a single remote operation.

The architecture 150 also is configured to perform "transparent SQL" functions, where database operations are performed in a foreign server 208 and completed in the local server 202 in a manner that is transparent to the client application 200. As described above, the local server process 202 will determine in step 502 whether the client statement includes a reference to a foreign database system. If the client statement includes the foreign database system reference and the local server process 202 determines in step 504 that the client statement is not a call to the passthrough SQL module 207, the local server process 202 parses the client statement in step 514 to determine a plurality of operations necessary for execution of the client statement.

The local server 202 then identifies at least one of the operations to be performed by the foreign server 208 by accessing the capabilities table 224 in step 516 to compare the relative capabilities of the identified foreign system 208 relative to the operation to be performed. For example, the local server 202 will control the SQL services module 210b to access the capabilities table 224 in order to determine whether the foreign server 208 can perform a particular query. Alternatively, the local server 202 will control the transaction services module 210a to access the capabilities table 224 to determine whether the foreign server 208 can perform an operation such as a two-phase commit. If the capability table 224 indicates that the target foreign process 208 is capable of performing the prescribed operation, the heterogeneous services module 311 performs the necessary translations in steps 518 and 520 (e.g., API translation, SQL statement translation, data dictionary translation) and sends the converted operation and the data type translation information to the agent 300 in step 522 for data type translation by the conversion module 211 and the corresponding module 210'.

As an example of the translation, the local server 202 accesses the data dictionary translation table 231 to determine whether a data dictionary table specified in the statement exists for the foreign server 208. For example, a client statement may include the expression "select * from allusers @ FDS", where the data dictionary table "allusers" is defined in the local database 306 but not the foreign database "FDS" 308. The DDX 231 may specify that the database "FDS" 308 has two tables A and B, and that the joining of tables A and B from the foreign database 308 provides the same information as in the "allusers" table defined in the local database 306. Hence, the appropriate module 210 generating the request performs a substitution to convert the SQL statement from "select * from allusers @ FDS" to "select * from (select * join A, B) @ FDS", which is then passed to the FDS database in step 522.

If the DDX 231 does not include a reference to a specified table, then the local server 202 checks to see if the user's schema defines the unknown table. If there is no definition for the unknown table in the user schema, then the local server 202 checks its internal data dictionary generally. The DDX 231 includes table definitions that are perceived to be in the foreign database system 208, even though the foreign system 208 may not have the specified table, or the foreign database system may not store the requested information in a compatible database structure. For example, the foreign database may not support object definitions using data dictionary tables. In this case, the DDX 231 table specifies that the unknown table is a mimicked table.

If the DDX 231 indicate in step 519 the objects specified in the client statement need to be mimicked, then the local server 202 will process the request locally in step 521 as a "describe" function, as described above. In such a case, the local server 202 would return to a table with zero rows in step 512. Hence, the mimic function enables the local server 202 to return at least the structure of the table specified in the client statement, as opposed to a global error message without any results to the client statement.

Another type of translation is function translation. For example, suppose that the client 200 issues the statement "select foo(a, b) from table @ FDS," where foreign database system "FDS" does not implement the function "foo," but there are one or more other functions at the foreign database system what will produce the same result. In this example, assume that foreign database system has two functions, "f1" and "f2," which can indirectly implement the functionality of "foo." In this case, function translation results in a statement, such as "select f1(a)+f2(b) from table @ FDS."

Execution is completed in step 528 by the local server 202 before sending the results to the client 200. For example, assume the function "foo" is not supported by the foreign system, as specified by the capabilities table 224. In such a case, the statement "select foo(A, B) from table @ FDS" would be parsed by the local server process 202 in step 514, and the SQL services module 210b would generate a request in step 520 as "select A, B from table @ FDS". Upon receiving the contents of tables A and B from the foreign process database system "FDS" 308 in step 524, the local server 202 completes execution in step 528 by performing the operation "foo" on the results of the query "select A, B from table @ FDS". Thus, the process of executing the function in the local server 202 using data accessed from the foreign database system 308, also referred to as "post-processing," appears to the client application 200 to be fully executed within the local database server 202, even though agent process 300 extracts the data from the foreign database system 208.

Another kind of post-processing is "post where-clause filtering" in which the where-clause cannot be executed by the foreign system 208. In the above example, the where-clause of query "select A from table @ (a) FDS where A=foo(B)" cannot be executed by the foreign system 208, because "foo" is not an operation implemented on foreign system 208. In this case, the query "select A, B from table @ FDS" is submitted to the foreign system 208 via agent process 300, and the local server 202 completes the execution of the query by calling the local "foo" operation with the values of A and B received via agent process 300. Thus, post-processing helps make a heterogeneous distributed database system look like a homogeneous database system (e.g., the client process 200 need not know that "foo" is unavailable for foreign system 208), promoting transparency.

The passthrough SQL feature as described with respect to steps 506, 508, and 510 and the transparent SQL feature described with respect to steps 514 through 528 provide an arrangement where at least a portion of a statement received by the local server 202 is executed at the foreign server 208 in a manner such that the fact that the remote operation is performed on a foreign database is transparent to the user. In the case of passthrough SQL, the database link used as part of the identifier, and the agent process 300 acts as a virtual interface for the foreign server 208, since a plurality of foreign statements can be collected by the passthrough module 207 prior to sending the statements to the foreign server 208. The collection of statements is particularly beneficial for bind calls.

In the case of passthrough SQL, the database link used as part of the identifier, and the heterogeneous services communicates with an agent process that acts as a virtual interface for the foreign server. A plurality of client statements for the foreign server can be collected by the passthrough module and sent to the foreign system in a single call, reducing network traffic over the communication path. The collection of statements is particularly beneficial for bind calls.

The present invention provides an architecture providing scaleable, efficient, transparent access to data in a heterogeneous database environment. The agent process provides a virtual interface that controls the passing of foreign statements foreign systems. Moreover, the agent process enables the foreign statements to be collected and passed to the foreign systems, enabling distributed processing in a coordinated manner.

In addition, the disclosed architecture provides reliable execution of processes without risking the local server process operation by providing a plurality of agents executing in separate address spaces that communicate with the local server process via a database link. Hence, if a fault occurs in a foreign process, such as a foreign server instance or an agent executing an external routine, the link with the faulty process can be terminated without adversely affecting the local server process.

In the foregoing specification, the invention has been described with reference to specific embodiments thereof. It will, however, be evident that various modifications and changes may be made thereto without departing from the broader spirit and scope of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense.