WO2000055767A2 - System and method for real-time interoperation between a database management system and multiple data sources - Google Patents

Abstract

The invention provides a method for modeling a first database associated with a first database management system, where the first database has at least one physical table divided into columns and rows and comprising at least one data file. The structure of the first database is determined and, based on the structure of the first database, at least one business object defined by a business definition is created. Each file in the first database is mapped to a corresponding business object in accordance with a mode definition, where the mode definition specifies how the corresponding business object can manage the data file. The invention is useful for extending and remapping legacy data from a relational database to an object oriented format.

Description

WO 00/55767 PCTtUSOO/00475

SYSTEM AND METHOD FOR REAL-TIME INTEROPERATION BETWEEN A DATABASE MANAGEMENT SYSTEM AND MULTIPLE DATA SOURCES

Cross-reference to Related Cases

This application claims priority to a provisional patent application filed March 18, 1999 and having a Serial No. 60/125,198, the entire contents of which are incorporated herein by reference.

A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

Technical Field This invention generally relates to the field of database systems. More particularly, this invention relates to extending and remapping legacy data from a relational database to an object- oriented format.

Background Known systems for managing information include object databases and relational databases. In a relational database, data items are organized according to relations. A relational database stores the data items in tables. The rows of each table represent records or collections of information regarding a particular item and the columns represent fields or attributes of the records. Relationships among the data items stored in the different tables are built by including corresponding fields in more than one table. For example, a data item of a record in one table may be linked to a data item of a record in another table by including an identical field in both data tables and having the information stored in the corresponding fields be the same for both records.

For example, a relational database for storing tire information may contain a separate table for storing the values of each of the tire characteristics (i.e. separate tables for radius, width, tread, etc.) and pointers defining the relationships between the tables. _ ₂ . PCTtUSOO/00475

In contrast, object databases store information as objects. An object includes a set of related data elements and a set of routines (termed "methods") for operating on the data elements. A variable is a named storage place within and object for a data element. The data element can be a built-in data type, such as a number or a character, or it can be a reference to another object. A class is a template for defining the methods and variables for particular types of objects. Typically, an object is a specific instance of a class. All objects of a given class are identical in form and behavior but contain different data in their variables.

For example, an object named "tire" may be a member of a class called "car parts." The "tire" object can include data elements such as radius, width and tread. The object named "tire" may also include a method such as a program that calculates the weight of the tire based on information known about the tire (i.e. radius, width, material). Object databases also allow for objects to "inherit" attributes from other objects. For example, if a user wanted to create objects named "bias tire" and "radial tire," the user could create these objects by having them inherit the attributes of the existing object "tire" and then provide additional attributes that make the objects distinct. Inheritance allows a user to create an object that has the qualities of another existing object without having to redefine all of the characteristics of the existing object.

In the past, relational databases were better known and accepted in commercial database environments than were object databases. However, object databases are gaining greater acceptance because of their strengths in real -world modeling, modularity, re-use, distributed computing, client/server computing, graphical user interface, and navigational advantages.

Despite the advantages of object oriented databases, however, some businesses are reluctant to convert their established relational databases ("legacy systems") to object oriented databases because of the expense, time, and error possibilities in manually migrating data from one type of database to another. Consequently, many businesses attempt to maintain access to their relational databases as much as possible.

In addition, many relational legacy databases still provide most of the functionality needed for some businesses, but often need minor enhancements that are expensive and time- consuming to implement while still maintaining a relational database format. For example, a relational database for a bank might list all customers using three fields, but changes to customer information might make it more desirable to store customer information using four fields. If the database is a relational database, addition of an additional field may require that the entire database be reconstructed, and all data must be migrated [re-entered, often manually] to the updated relational database. Converting the relational database to an object oriented database, which provides more future expandability, typically must be done manually, a time consuming, expensive, and error-prone process. Some conversion schemes even require that the relational data first be converted to a "neutral" format before being converted to an object-oriented format, which takes even more time.

Summary of the Invention In one aspect, the invention provides a database management system having a feature called Foreign Federations. The Foreign Federation feature allows virtually any data source to be modeled as a collection of objects in an object oriented database management system ("DBMS"), such as MATRIX, which is manufactured by MatrixOne, 2 Executive Drive,

Chelmsford, Massachusetts 01824. MATRIX is a world wide web ("WWW") based Product Development Management (PDM) system that can work with relational databases such as the ORACLE database, manufactured by ORACLE Corporation, 500 Oracle Parkway, Redwood Shores, CA 94065). Other data sources of particular interest are databases of PDM, and computer aided design ("CAD"), and ERP vendors. The present invention permits companies to use object-oriented clients to access/create/modify all data in their enterprise, extended enterprise, or supply chain. In some embodiments, the present invention permits adapters to be created to work with databases from various vendors.

This aspect of the invention is an object oriented database management system DBMS that permits one or more users to rapidly add data to and receive data from one or more foreign databases (such as legacy relational databases) without requiring manual entry of data to an object oriented database or conversion to a neutral format. Moreover, this is accomplished in real-time. To accomplish this, the object oriented DBMS first maps the data from the legacy database by modeling "foreign" data sources (i.e., legacy databases-those that are not already in the object-oriented format preferred by the DBMS) as a collection of objects. For each legacy database file that is mapped, a corresponding object is created. Next, the physical schema of the foreign data source is defined such that the foreign data is entered into the mapping format preferred by the object oriented DBMS. Next, the business models used with the object oriented DBMS are mapped to the foreign schema. Then, relationships are defined between the business objects in terms of the foreign schema. Each of these steps is explained more fully herein. According to this aspect the invention, a business is provided with an object oriented database as a front-end to one or more relational database systems. To satisfy businesses that want access to both legacy relational databases and newer object databases, applications and tools have been developed in accordance with the invention to support integrating a business' s existing information resources into a cohesive environment. These tools typically incorporate Structured Query Language (SQL) capabilities, such as used in most conventional database products.

It is an object of this aspect of the invention to provide a system for mapping the schema of a foreign, legacy, or relation database (e.g., the tables and the data from an ORACLE database) to the object oriented format of an object oriented DBMS.

In one embodiment of this aspect, the invention provides "read-only" access to legacy data. That is, a user can use the object oriented DBMS to query a foreign database (such as a relational database) and be able to view the data from the foreign database as if it were created in the object oriented format that is native to the object oriented DBMS. This aspect of the invention is termed "unidirectional" because data goes, via the object oriented DBMS, in only one direction: from the foreign database to the user. The user cannot write to the foreign database, directly or indirectly. Also, in this aspect of the invention, users will be able to view the actual data in the foreign database or proper subsets of it. For example, if a foreign database has a table called "Data_l" containing the customer information fields called "name," "title," "company," and "phone number," the user may view any one or more of those fields (or all four). It is another object of this aspect of the invention to provide an object oriented DBMS that permits a user to read data from and write data to a foreign database (such as a relational database). In one embodiment, changes can be made to the foreign database using the object oriented DBMS, and these changes will be reflected in both the actual foreign database as well as in a converted object oriented database created from the foreign database. In particular, in the embodiment called a "read/write mode," changes made to the foreign database while using the object oriented DBMS will be reflected in both the converted object oriented database created from the foreign database as well as in the original foreign database. Further, the changes will be reflected in the original foreign database even if the original foreign database is accessed using the corresponding foreign DBMS. Thus, the view of the data will be consistent between the foreign database and the converted object oriented database created from the foreign database. The constraint with this mode is that the attributes that can be modified are limited to those attributes/fields that already existed in the foreign database. Thus, in this aspect of the invention, the foreign database cannot be extended by the modifications made to it. Further, similar to the "read-only" mode described above, the user may view (or modify) only the actual data that is in the foreign database or proper subsets of it.

It is yet another object of this aspect of the invention to provide an object oriented DBMS that permits accessing, modifying, and extending a foreign database as if it were an object oriented database. In one embodiment of this aspect, the foreign database is mapped to the object-oriented format as described previously, so that a user may interact with it as if it were an object oriented database. The "mapped" version of the foreign database is saved to a server (which may or may not be separate from the computer system on which the object oriented DBMS is located). In this aspect, changes made to a foreign database are reflected at both the foreign database itself and at the "mapped" version of the foreign database. However, only the specific data that was requested is changed; the rest of the foreign database remains unchanged.

Thus, changes are maintained at both the foreign database and at the object and modifying the legacy relational database without requiring that the entire legacy database be converted to the object oriented format. That is, in this aspect, the invention permits foreign instance data to be moved/replicated to the object-oriented format in an "on-demand" manner. This mode, called "assimilation" mode, is thus ideal for situations that require replacement of the legacy system. In addition, this aspect of the invention permits changes to the legacy database that are not constrained by the fields/attributes that are available in the legacy database. Thus, the "useful life" of a legacy relational database can be extended. For example, a foreign data table containing only the fields "name" and "address" can, when mapped to the format of the invention, be extended to include the field "phone," but this change will be reflected only in the mapped foreign database, not in the original foreign database.

In still another embodiment of this aspect, the invention provides an object oriented DBMS that includes a transaction manager that supports distributed transactions (i.e., those occurring among multiple databases. The transaction manager ensures that simultaneous updates can occur on more than one foreign database. This permits simultaneous updates to occur on more than one database, i.e., on one or more original legacy databases and one or more the object oriented [converted] legacy databases.

In another aspect, the invention provides a method for modeling a first database associated with a first database management system, where the first database has at least one physical table divided into columns and rows and comprising at least one data file. The structure of the first database is determined and, based on the structure of the first database, at least one business object defined by a business definition is created. Each file in the first database is mapped to a corresponding business object in accordance with a mode definition, where the mode definition specifies how the corresponding business object can manage the data file. In one embodiment, the structure of the first database is determined by determining associations between columns and/or tables in the first database. In another embodiment, the business definition for the business object comprises parameters such as attributes, types, policies, relationships, owners, and states. In still another embodiment, each data file in the first database is mapped by describing each column of each table along with the type of data associated with the column and describing any associations existing between tables in the first database.

In yet another embodiment, the method of modeling the first database further comprises defining how the business objects associated with the first database relate to the schema of a second database management system, which can, in one embodiment, be a different type of database management system than the first database management system. In another embodiment, the method of modeling the first database is used to grant access to the first database from a second database management system. In this embodiment, a unique server object is created in the second database management system to represent the first database. A unique vault object is also created in the second database management system, where the vault object stores the business object associated with the first database. The vault object is accessed using a function relating to managing the data in the first database, which function is available in a library defined in the second database management system and responsive to the mapping of the first database. In accordance with this embodiment, accessing the unique server object and the unique vault provides access to data in the first database from the second database. Management of the data in the first database is accomplished using the function in the library. In still another aspect, the invention provides a method for managing data in a first database using a database management system associated with a second database, A library is defined that is accessible to the first and second databases, the library including at least one function relating to managing data in the first database. The structure of the first database is determined and, based on the structure, a unique object is defined for each file in the first database, where the unique object comprises at least one parameter characterizing all columns of data in the first database. The data in the first database is modeled as a plurality of objects, with - 7 - each object having parameters in accordance with an operating mode. When the function is run, this plurality of objects is passed to and used by the library.

In still another aspect, the invention includes a system for integrating information from a first database system into a second database system. The system comprises a first unique server object associated with a first database and an adapter for modeling the first database system. The adapter includes a schema map operable with the first and second server objects to define the correspondence between information in the first and second database systems, a business definition comprising information relating to the structure of the second database; and a library implementing, in accordance with the schema map and business definition, at least one function relating to managing the data in the second database system.

The foregoing and other objects, aspects, features, and advantages of the invention will become more apparent from the following description and from the claims

Brief Description of the Drawings This invention is described with particularity in the appended claims. The above and further advantages of this invention may be better understood by referring to the following description taken in conjunction with the accompanying drawings.

Figure 1 is a block diagram of a computer system in which the present invention may be embodied. Figure 2 is a block diagram of a client/server system in which one embodiment of the present invention may be implemented.

Figure 3 is a flow chart of the steps for modeling and mapping a foreign database to an object oriented schema.

Figure 4 is an example of computer code corresponding to the modeling step of the flow chart of Figure 3.

Figures 5A-5C are an example of computer code corresponding to the mapping steps of the flow chart of Figure 3.

Figure 6 is a block diagram of a system for integrating information from a first database system into a second database system, in accordance with an embodiment of the invention. Figure 7 is a flow chart of a method for modeling a database, in accordance with an embodiment of the invention.

Figures 8A-8C are examples of a database usable with an embodiment of the invention. - 8 -

Figure 9 is an example of a human resources employee table database usable in accordance with an embodiment of the invention.

Figure 10 is an example of a human resources department table database usable in accordance with an embodiment of the invention. Figure 11 is an example of a human resources salary table database usable in accordance with an embodiment of the invention.

Figure 12 is an example of a human resources data map with physical table definitions for the database of Figure 9, in accordance with an embodiment of the invention.

Figure 13 is an example of a human resources data map with object aggregate and extensions for the database of Figure 9, in accordance with an embodiment of the invention.

Figure 13 is an example of the resultant human resources data object that result using the data map of Figure 12.

Figure 15 is a flow chart for modeling data and managing data in a foreign database, in accordance with an embodiment of the invention. Figures 16A and B are examples of server and vault dialogs, respectively, in accordance with an embodiment of the invention.

Detailed Description of the Invention Figure 1 illustrates the setup of a computer system 10 in which at least one embodiment of the present invention can be embodied. Computer system 10 comprises central processor 12, main memory 14, input/output controller 16, input device 18 (e.g., a keyboard), pointing device 20 (e.g., a mouse, pen device, trackball, and the like), display device 22, and mass storage 24 (e.g., optical disk, magneto-optical disk, removable disk, hard disk, flash memory, Winchester disk, tape drive, and the like). In some embodiments, the computer system 10 may comprise one or more output devices 36, such as a printer. Central processor 12 is coupled to cache memory 28 (which may be integral with the processor 12). The cache memory 28 is used to store frequently accessed information. Each of the components of the computer system 10 typically communicates with each other over a system bus 30.

In some embodiments, one or more computer programs define the operational capabilities of the computer system 10. The programs can be loaded into the computer system 10 via the mass storage 24. Alternatively, the programs can reside in a permanent memory portion (e.g., a ROM chip) of the main memory 14. In some other embodiments, the computer system 10 can - 9 - include specially designed, dedicated, hard-wired electronic circuits that perform all functions described herein without the need for instructions from computer programs.

Figure 2 is a block diagram of a general structure of a client/server system, in accordance with one aspect of the invention, in which one embodiment of the invention may be implemented. A user (not shown interacts at a user interface 34 with a client 36. Typically, the user interface 34 receives user commands and data as input and displays results data as output. A client 36 may comprise one or more stand-alone terminals, such as workstations, personal computers (PC's) (such as the computer system 10 of Figure 1), personal digital assistants

(PDA's), palmtop, laptop, or notebook computers, dumb terminals, and the like. It should be understood that, although Figure 2 illustrates only one client 36 communicating over network 56 with the servers 46, 46' it should be understood that more than one client may communicate over the network 56 with more than one server 46.

When the user interface 34 submits a request to the client 36 for data from the server 46, the request can take two different paths, depending on whether the request requires data from a foreign database 47 or data from a database already in the DBMS format (e.g., database 48). First, the DBMS application interface 43 of the client 36 receives the request and transmits it the DBMS object layer 100, which determines to which of the objects available on the DBMS the request corresponds. For example, the objects may be displayed on a display of the computer 10 (see Figure 1). The object may contain data located on one or more databases, such as the foreign database 47. The DBMS object layer 100 then transmits the request to the business model metadata layer 102, which determines how the object models should look (e.g. fields, attributes). The business model layer 102 transmits information about the how the object models must look to both the object/relational mapping layer 104 and the foreign federation mapping layer 116. In addition, the business model layer determines, based on the data requested, whether the data is coming from a foreign database (and must, therefore, be mapped to the DBMS schema) or whether it is coming from a database already in the DBMS format (e.g., an object oriented database).

The object/relational mapping layer 104 defines how an object is stored. It transmits this information to the administrative cache 106 and the instance cache 108. The administrative cache stores administrative data, which data relates to the definition, setup and organization of data shared in a database. The instance cache 108 stores instance data, which refers to the data - 10 - itself. Typically, administrative data does not change a lot over time, whereas instance data changes more frequently.

The storage manager 110 converts the request from the user interface 24 (which has been processed through the other layers to obtain mapping and format information, as described above) to an SQL statement that is sent to a database (e.g., foreign database 47 or DBMS format database 48).

The transaction manager 112 controls the lifetime of an object and, thus, controls the instance cache 108. Note that any interaction of the user interface 24 with the client 36 is called a transaction. The foreign federation mapping layer 116 defines the business object model for mapping the foreign database 47 to the schema of the DBMS. Its modeling operation is explained more fully in Figures 3 and 4.

The foreign federation adapter 114 is the "heart" of the invention. It receives object lifetime information from the transaction manager 112, schema mapping information from the storage manager 110, and row and column format information from the foreign federation mapping layer 116, and uses this to convert the data from the foreign database to the format preferred by the DBMS. Its primary output is a relational table of DBMS objects that is transmitted to the storage manager 110.

Referring to Figure 3, the steps for mapping foreign data to relational data, in accordance with this aspect of the invention, are as follows. After a query is received (step 200), the object oriented DBMS maps the data from the legacy database by modeling "foreign" data sources (e.g. legacy databases, such as relational databases—those that are not already in the object-oriented format preferred by the DBMS) as a collection of objects (step 202). For each legacy database file that is mapped, a corresponding object is created. Next, the physical schema of the foreign data source is defined such that the foreign data is entered into the mapping format preferred by the object oriented DBMS (step 204). Next, the business models used with the object oriented

DBMS are mapped to the foreign schema (step 206). Then, relationships are defined between the business objects in terms of the foreign schema (step 208).

Figure 4 illustrates an example of computer code corresponding to the step 202 (the modeling step) of the flow chart of Figure 3. This example, as well as the example provided in

Figures 5A-5C, is for illustration purposes only; those skilled in the art will recognize other code that can accomplish this modeling. Figure 5 is an example of computer code corresponding to the mapping steps of the flow chart of Figure 3. The code labeled "A" corresponds to step 204. The code labeled "B" corresponds to step 206. The code labeled "C" corresponds to step 208.

In another aspect, the Foreign Federation feature of the client is essentially the creation of an API. Using the foreign federation API , developers of object-oriented DBMSs can write database 'adapters' that take data from non-object sources and convert it into something that the object-oriented DBMS can understand. The primary design goals of the API are as follows:

1) Make creation and maintenance of the adapter as easy as possible.

2) Easily accommodate read-only as well as read-write access to the foreign model. 3) Preserve the dynamic modeling aspect of the object-oriented DBMS when modeling foreign data. 4) Design the interface for high performance, production use

In keeping with point (1), the API typically is very narrow. In one embodiment, a total of 42 entry points is all that is necessary to fully describe instance data (business objects and relationships). In another embodiment, the an adapter is intended to be read-only; for this embodiment, then as few as 9 of these entry points need to be implemented. Preferably, language support for the foreign federations API will be chosen with a "lowest-common- denominator" approach. In one embodiment, the API is available in a common C language binding. Non-instance data for foreign federations is still modeled in the object-oriented DBMS, as described above. This allows leveraging many features of a dynamic meta-schema on top of flat or even non-structured data, as well as keeping the creation of adapters as simple as possible.

In one embodiment of this aspect, most of the API resides at the object-oriented DBMS schema level. This fully leverages matrix caching and indexing technologies resulting in throughput for foreign data that nearly matches throughput of matrix-native data.

In another embodiment of this aspect, database adapters are packaged as shared libraries and schema maps. The shared library is a simply a code library that implements pre-defined API entry points for different matrix query/update operations. The library must be available on each platform on which the foreign federation described above is supported. The schema map is essentially a parameter file that is passed to the shared library at initialization time. Though the adapter is solely responsible for recognizing the contents of this file, it is presumed a common format will be XML, which can easily represent parameters as descriptive 'tags'.

A toolkit will be available for implementers of database adapters. This toolkit will contain source for a sample adapter as well as a stripped down version of the matrix schema and cache managers for inclusion into their drivers.

In another aspect, the present invention provides a method of modeling a database associated with a first type of database management system so that it can be accessed and/or managed using a second type database management system, which can be different than the first type. In accordance this aspect of the invention, virtually any data source may be modeled as a collection of objects through the use of data structures called "adaplets," which can be provided as part of a development toolkit associated with a database management system. These adaplets can be designed for use with very specific sources (e.g., customized to a particular type of database that a given database management system is accessing), or a more general adaplet may be created for use by several sources, each configured with a mapping file, as explained herein. In accordance with the invention, when an adaplet to a system, such as a first type of database management system, is in place, an application associated with a second type of database management system can be used to access, create, and modify all the data in databases created using the first type of database management system. For example, users of an object oriented database management system can use the present invention to access, create, and modify all the data in its enterprise, extended enterprise, or supply chain, even if the databases involved were created using a different type of database management system (e.g., relational, flat-file, inverted list, and the like). In another example, read only adaplets may also be used to publish legacy data. For example, a manufacturer can use a read only adaplet to permit potential customers In accordance with an embodiment of the invention, an adaplet can enable real-time interoperation between a first type of database management system and other (similar and/or different) types of database management systems. The adaplet, in accordance with the invention, can provide a database management system with integrated information from one or more other sources of data while causing no disruption of the other systems. Thus, the adaplet of the invention permits a user of a database management system (a "native" database management system), such as the aforementioned MATRIX system, to have access to information stored in other systems as if it were actually part of the native database management system. In this - 13 - example, the externally stored information is indistinguishable from information that in the native database management system. In another example, the adaplet can be designed so that operations that can be performed on the native database will also be available for external items. With adaplets in place, decision making can be improved because users of information sources can have access to more information and more consistent information. In addition, adaplets can enable business processes to operate across multiple systems in a consistent fashion.

Figure 6 illustrates a block diagram of a system 20 for integrating information created using a first database management system into a second database management system, in accordance with an embodiment of the invention. A user of the system 20 (which can itself be the second database management system) interacts with the system 20 via a user interface 34. For example, the user interface 34 can be an enterprise portal; —that is, a portal on a network or series of interconnected networks that can include a variety of different platforms, operating systems, protocols, and network architectures. Portals are locations on the World Wide Web ("WWW") that serve as gateways to the Internet. Typically, portals comprise a collection of links, content, and services arranged to guide users to information. The computer interface 34 also can be a computer system such as computer system 10 (see Figure 1). In this example, the computer interface 34 further comprises a graphical user interface ("GUI") 38 that displays a plurality of objects 39 representing data sources that can be accessed via the computer interface 34. This is explained more fully herein. The user interface 24 accesses data in one or more data sources via a client 36. The client

36 also has access to a database management system 37 (DBMS). As used herein, the DBMS 37 is a software interface between one or more sources of data (e.g., databases) and the user of the databases (which can be the client 36 and/or the user interface 24). The DBMS 37 can handle user requests for database actions (such as writing to and reading from a database) and can allow for control of security and data integrity and consistency. The DBMS 37 can be an independent program running at the client 36. It should be understood, however, that the DBMS also can be located outside of the client 36. For example, the client 36 can comprise a database engine (not shown) having one or more program modules that provide access to the DBMS 37.

The client 36 can also comprise a native database server object 40 and one or more server objects 42 and vaults 44 that correspond to data from foreign data sources that the client 36 can access (the server objects 42 and vaults 44 will be explained more fully herein). "Native," as used herein refers to data sources that are of the same type as data sources that the DBMS 37 can - 14 - manage. For example, if the DBMS is an object oriented DBMS such as MATRIX, databases and data sources will be "native" if they are the same type of database as the type managed by MATRIX. A database will be "foreign," if it is of a type different than that associated with the native DBMS. For example, a relational database would be a foreign database to an object oriented DBMS.

Often, foreign databases are legacy databases to an existing DBMS. Legacy data refers to data acquired by an organization that was compiled by another organization (e.g., another DBMS), and the term "legacy," is typically used to refer to documents, data, or hardware that require translation or conversion to a new system. In the business world, many databases that have been developed are relational databases, but many of the new DBMS being developed are object oriented databases. Because businesses have invested significant resources in developing their legacy databases (which often are relational databases), and because transferring legacy data into a new format (such as for an object oriented DBMS) is expensive, time-consuming, and error-prone, it is desirable to find ways to permit an objected oriented DBMS to access non- object oriented databases.

Software has been developed to interface object oriented software and DBMS to relational databases. For example, the software can include a schema mapper that permits a database programmer to map the object schema to the relational schema. There are various techniques known for mapping relational schema to objects schema. The present invention, however, includes systems and methods for mapping from a first type of schema to a second type of schema, and is lot limited to just mapping relational schema to object schema.

Referring again to Figure. 6, an adapter 50 permits the client 36 to interact with and manage data in foreign databases 54, 54', and 54" (note that, although just three foreign databases 54 are illustrated in Figure 6, this number should not be taken as limiting or as a minimum). Also, although Figure 6 illustrates a single adapter 50 providing the schema mapping for three different foreign databases 54, the adapter 50 can also comprise a plurality of adapters 50 (not shown), with each adapter 50 designated as providing the mapping for one or more databases. For example, in Figure 6, the adapter 50 could also comprise three different adapters 50, each associated with a respective foreign database 54, 54', 54". The adapter 50 comprises three components: a schema map 52, a set of business definitions 55, and a shared library 56. Each of these components is explained more fully below. - 15 -

The shared library 56 can be a code library that implements pre-defined Application Program Interface (API) entry points for different types of database management operations (e.g., queries, updates, and the like) used by the DBMS 37. The shared library 56 is available to each client machine 36 that will access the other data source, such as the foreign database 54. The shared library 56 for the adapter 50 can also be located on the client 36. A shared library 56 may be reused with multiple schema maps 52.

In one embodiment, the shared library 56 can further comprise elements that can help optimize performance of the DBMS 36. For example, the shared library 56 can include a universal interface 58, such as a bi-directional interface, to enable the DBMS 37 to communicate with many different types of foreign databases 54. The shared library 56 can also comprise one or more caches to speed access to data.

The schema map 52 comprises a parameter file that is passed to the shared library 56. The schema map 52 can be passed to the shared library 56 when the software associated with the DBMS 37 is initialized. It also can be passed to the shared library 56 whenever the client 36 attempts to access data stored in a foreign database 54. Multiple schema maps 52 can be used with the same shared library 56. In another embodiment, a schema map 52 can be "hard-coded" into the shared library 56.

The schema map 52 that indicates which metadata in the foreign data maps to what types of data in the native database in an important element in the adapter 50, but it should be understood that the adapter 50 can be designed such that a schema 52 is not required. In such cases the mapping is "hard- coded" when the functions are implemented in the shared library. Consequently, a "hard-coded" adapter is not reused with other data. Using "hard-coded" adapters can be acceptable for connecting to custom legacy applications. However, if the shared library can be reused with other, similar data sources (for example, as is the case with the standard "" adapter in eMATRIX), it can be designed to use a mapping file. The schema map 52 becomes part of the vault definition and can be referred to when accessing the data. The details of creating the schema map 52 are discussed more extensively herein.

The business definitions 55 comprise, in one aspect, the non- instance data in the foreign database that is to be modeled in the native DBMS. This means that the business definitions 54 (Attributes, Types, Relationships, Policies, etc.) for the foreign data should exist in the native DBMS. These business definitions 55 work in conjunction with the schema map to provide a structure to the foreign data that compatible with the structure of the native DBMS. The business definitions 55 are used to create objects to represent the data in the foreign database 55. - 16 - i -

In addition to the non-instance data (also called metadata) for the resulting business objects, each foreign data source that is connected is associated with a server and a vault, which are associated with the server object 42 and the vault object 44, respectively. A server object is required for each database that is connected to the client; thus, the system 20 of Figure 6 has server objects 42 , 42', 42" representing the foreign databases 55, 55', 55", respectively, and also has a native database server object 40 representing the native database 52. The server object 42 is used to connect the client 36 to the foreign database 54.

The vault object 44 is associated with a vault definition that includes the schema map and the shared library. The vault object 44 is associated with persistent storage of business objects. The adapter 50 can operate in different modes, depending on the type of access to legacy data that is being provided. For example, in one embodiment, the adapter can operate in one of the following modes:

READ ONLY: data in the external data source (e.g., a foreign database) can be viewed, but not altered. Attempts to modify the data can result in a system error. READ WRITE: changes made to the data in the external data source using the native

DBMS are written to the external data source, but storage (e.g., tables ) in the native DBMS is not updated. Both data sources (e.g., both the foreign data source and the storage at the native DBMS) are provided with a consistent view of the data. However, any functions that are specific to the native DBMS but not implemented in the adapter will not work on the foreign data source. Typically, the native DBMS can perform only those types of data managing functions that are available in the DBMS associated with the foreign data.

EXTEND: extends the basic READ WRITE capability of an adapter by letting the native DBMS handle functions that are not supported by the adapter (that is, functions that are not associated with the foreign DBMS). In this mode, changes are written to either the external data source or the storage associated with the native DBMS. In this embodiment, the native DBMS will be permitted to attempt to run its native functions even if the functions are not implemented in the adapter, but the outcome of the function (e.g., the changes that result) will be seen only in the native DBMS.

MIGRATE: Changes made to the data source are written to the native DBMS mode. In an example, MIGRATE mode can be used in situations that require legacy system replacement, where the legacy system is on-line but not in use. This permits legacy data to be transferred to the native DBMS environment on an "as needed" basis. The operating mode is generally determined when shared libraries 56 are designed and written. For example, software associated with the READ ONLY mode needs to implement only a small number of functions, whereas software designed to update the legacy system (e.g., EXTEND or MIGRATE) can be customized to implement the functions. In one embodiment, the adapter 50 can be created for a first mode but can be set to operate in another mode. For example, an adapter 50 designed to operate in the READ ONLY mode can be set to operate in EXTEND or MIGRATE mode, so that the adapter 50 can use the native DBMS 37 to perform operations that the adapter 50 itself has not been designed to execute. Thus, over time, the operating mode of the adapter 50 can be changed. This is explained more fully herein.

Before describing what the adapter 50 can do when used in the system 20 of FIG. 6, it is important to understand how the adapter 50 is created. Figure 7 is a flow chart illustrating the steps for modeling the data in a foreign database 54. In Figure 7, the foreign database corresponds to the "first database," (which is associated with a first DBMS), and the native DBMS corresponds to the "second database management system.)

First, the first database is evaluated to determine its structure (step 300) and to identify any associations between columns or tables. To understand these steps, it is helpful to refer to Figures 8A-8C, which correspond to a sample Human Resources database included with ORACLE installations. Figures 9-11 provide "screen shots" of the ORACLE schema manager used with the databases shown in Figures 8A-8C. Figure 9 illustrates the schema manager for the EMP table of Figure 8 A. Figure 10 illustrates the schema manager for the DEPT table of Figure 8B. Figure 11 illustrates the schema manager for the SALGRADE table of Figure 8C. In a typical ORACLE installation, the sample Human Resources database is known as "scott/tiger," and it is useful for illustrating the features of the invention. From the ORACLE Schema manager (Figures 9-11), it can be noted that a user "scott" has been defined and may be accessed with the password "tiger." As stated above, this is a human resources database, and it contains three tables (shown, respectively, in Figures 8A-8C) describing employees, their salary grades, and the departments to which they belong.

Referring again to steps 300 and 310 of Figure 7, the associations between the tables and columns in the "scott/tiger" human resources database can be determined. For example,

DEPTNO is a column in the EMP table of Figure 8 A an the DEPT table of Figure 8B. Each employee has and EMPNO and a manager, which is indicated as an EMPNO(that is, the EMPNO - 18 - of the manager for a given employee). In both of these cases, a relationship exists between the data. If a column in a table is a classification (each row is a "kind of something), then a type hierarchy should be built to reflect the association. For example, Clerk, Manager, and Salesman are all kinds of employees. In contrast, much of the data in the tables of Figures 8A-8C is descriptive. For example, the location of a department or the salary of an employee could be considered attributes. A date, such as the HIRED ATE can be an attribute, as well.

Referring again to Figure 7, based on the determinations made about the table, one or more business definitions are created (step 320). In one embodiment, the Business definitions for the human resources database can be created by running a script during the installation of the software used to develop the adaplet. For example, using the ORACLE scott/tiger database and the eMATRIX product, this can be accomplished using the scott . mql script which is placed in the INSTALL_DIR/api/ xf f directory during installation of the eMATRIX Adaplet Development toolkit. This file will add the attributes, types, policies, and relationships that are required for the ORACLE database to be compatible with eMATRIX, as well as a Person, scott, that will be the owner of all the objects in the human resources database. These administrative objects are defined with minimal parameters and can be enhanced to make real life use of this database. However, those skilled in the art will recognize how to enhance these minimal parameters. For example, all business objects can be governed by a policy. Thus, several policies are added with the sample script included with eMATRIX. However, only one state is defined in most of them. Also, the sample script creates no Formats or Stores, and so none are included in any of the policy definitions. To check files into any of the objects in the Human Resources database (that is, the scott/tiger database of this example), at least one format and store can be created and added to the applicable policy. An example of MQL script that can create business definitions to model the data is shown below:

add attribute "Department Number" type integer; add attribute Location type string; add type Department attribute "Department Number", Location;

add attribute "Low Salary" type real; add attribute "High Salary" type real; add type "Salary Grade" attribute "Low Salary", "High Salary" - 19 -

add attribute "Employee Number" type integer; add attribute "Hire Date" type date; add attribute Salary type real; add type Employee attribute "Employee Number", "Hire Date", Salary; add type Clerk derive Employee;

add type Manager derive Employee; add type Analyst derive Employee; add type President derive Employee;

add attribute Commission type real; add type Salesman derive Employee attribute Commission;

add policy Departments type Department state base; add policy "Salary Grades" type "Salary Grade" state base; add policy Employees type Employee state hired state trained state active state former; add relation Department from Type Department card one to type Employee card many; add relation "Salary Grade" from type "Salary Grade" card one to type Employee card many; add relation Manger from type Employee card one to type Employee card many; add person scott;

Referring again to Figure 7, after the business definitions are created, the metadata of the first database can then be mapped to the format of the second DBMS. It should be understood that the order in which each of the statements is added to the schema map (and, correspondingly, the order in which the steps are illustrated in Figure 7) is not important to the invention. The following description discusses the contents of the map file using the above- described standard adapter (scott/tiger) map as an example. It should be understood that the sections and keywords of this mapping file are applicable to adapters that can use this standard interface (rnxff). The standard mapping is used with, not instead of, modeling associated with the second DBMS, so attributes, types, and relationships associated with the second DBMS are referenced in the file, and can exist in the schema when the adapter is used. In the following examples, the ORACLE scott/tiger database and the MATRIX DBMS are used for illustrative purposes only. In these examples, all mappings are based on the relational data structure of the legacy system and a MATRIX definition. WO 00/55767 PCT/USOO/00475 - -

- 20 -

Referring again to Figure 7, to continue the mapping process, the location of the database is determined (step 330). The first line of the mapping file indicates where the database resides. In this example, a server object named "scott" is been created to point to the foreign data. server scott; If schema names instead of database links are to be used, (by setting

MX_USE_SCHEMA_NAMES to true in matrix.ini), then the schema NAME can be used instead of server NAME.

The operating mode for the adapter is determined (step 340) to tell the adapter the mode that is enabled. Recall that, in one embodiment of the invention, four different modes were possible: READ-ONLY, READ-WRITE, EXTEND, and MIGRATE. Any one of these modes can be enabled in the schema map. In this example, only one of the following modes (namely, EXTEND) lines is used, and the remaining unused modes are "commented out" with the "#": imode readonly; #mode readwrite; mode extend; #mode migrate

The operating mode set for an adapter may be changed over time. For example, it may become necessary to change a READ-WRITE adapter to EXTEND to adjust for changes to the business model. This can occur, for example, when a business using the first database and the second DBMS determines that it wants to perform additional operations on its legacy data.

After establishing the server location and operating mode, the schema map file describes each table in the data source (the first database), including the column and type (step 350). This can be done in a "physical tables" section. For example, Figure 12 is an example of a human resources data map with physical table definitions for the databases of Figure 8A-8C, in accordance with an embodiment of the invention.

In a physical table, all columns of the tables of a first database (e.g.. a legacy system) tables are defined, not just those that contain data that will be available to the second DBMS. Comments mapping file illustrated below show the syntax of the description:

#### physical tables

# COLUMN DATATYPE [primary] where:

COLUMN is the column heading; „

- 21 -

DATATYPE is the type of data contained in the column. A DATATYPE can be int , real, string, date, long, or raw . The first four (int , real, string, date ), in this example, correspond to attribute types available in the second DBMS. The latter two data types, in this example, correspond to definitions in the first database (in this example, ORACLE). In ORACLE, long means a long string datatype, and raw means a long raw datatype. For example, when mapping from ORACLE to MATRIX, a long can be used anywhere a string is needed (for a description, string attribute, etc.). A raw datatype could be used if the adapter supports icons/images. In that instance, the icon can be mapped to a raw column.

In a situation where the adapter is used with ORACLE, the adapter abides by ORACLE scale and precision definitions assigned to columns when the legacy schema was designed. Referring to the above physical table example,

Primary indicates that this column is to be used as the object's identifier. One column in a table is listed as primary. The primary column can be a name or integer column.

Each column has at least COLUMN and TYPE specified. In addition, each table has one primary column. If a table doesn't have a column that provides a unique identifier key to be used as primary, the table description can include the ORACLE column called ROWID. The value of RO ID evaluates to the internal ORACLE identifier for a table row. For example::

Table DEPT {

ROWID string primary ;

}

In one embodiment, if the primary column in a table is not an integer, or is an integer with a range greater than 16777216 or less than 0, the identifier is be "mapped" when used in a business definition in the mapping file. For example, referring to the EMP table of Figure 8A:. Table EMP {

EMPNO int primary;

ENAME string;

JOB string;

MGR int; HIREDATE date;

SAL real;

COMM real;

DEPTNO int;

} The EMP table of Figure 8 A has two string type columns, three integer type columns, two real number columns, and one date column. In this example, EMPNO is used as the object identifier ("OID") in the second DBMS. The ENAME column is used as the Employee business object's name. The tables of FIG 8B and 8C can be described similarly. - 22 -

Referring again to Figure 7, tables can also be defined to describe the associations between tables and/or columns (step 350). In one embodiment, a join table statement is used to describe "virtual" tables, which are tables that don't physically exist in the data source but may need to be described when there are associations between tables. In another embodiment, data from multiple tables can be accessed using the in TABLE ( COLUMN ) clause when defining type. For example, the following syntax can be used to create a joined table:

#table VIRTUALTABLENAME

#join TABLE, TABLE, TABLE...

#where EXPRESSION In the above example, the "where" statements describe the association between table columns by using

ORACLE/SQL expressions. Note that, in this example, can use operators with TABLE.COLUMN syntax when referring to values in a column.

In this example, joined tables are not READ-ONLY mode tables. If the adapter is in an operating mode that supports writing to the first database (e.g., READ- WRITE or EXTEND), joined tables may be updated. In the human resources database example, a virtual table that defines an employee's salary grade based on the data in the EMP and SALGRADE tables in Figures 8A and 8C, respectively can be described:

Table EMPGRADE {

Join EMP, SALGRADE; Where "EMP. SAL >= SALGRADE . LOSAL and EMP. SAL <= SALGRADE .HISAL" ;

}

This table provides the salary grade for each employee.

The next part of the mapping file indicates how the tables that have been described relate to the schema of the second DBMS (e.g., the native DBMS) (step 370). All types, attributes, policies, and persons (owners) referenced in this section of the schema map are created in the second DBMS to enable the adapter to be run. In the example described above, these administrative objects are defined in the provided scott.mql file. For example, the following syntax can be used (in this example, if only a COLUMN is specified, the previously stated TABLE is assumed): id TABLE (COLUMN) [mapped] type TABLE (COLUMN) [using TABLE (COLUMN) ] [alias name] name TABLE (COLUMN) [using TABLE (COLUMN) ] policy TABLE ( COLUMN) [using TABLE (COLUMN) ] owner TABLE (COLUMN) [using TABLE (COLUMN) ] default policy name default owner name next EXPRESSION; revision TABLE (COLUMN) [using TABLE (COLUMN) ] description TABLE (COLUM ) [using TABLE (COLUMN) ] icon TABLE (COLUMN) [using TABLE (COLUMN) ] locker TABLE (COLUMN) [using TABLE (COLUMN) ] created TABLE (COLUMN) [using TABLE (COLUMN) ] modified TABLE (COLUMN) [using TABLE (COLUMN) ] state TABLE (COLUMN) [using TABLE (COLUMN) ] attribute name TABLE (COLUM ) [using TABLE (COLUMN) ]

Depending on the second DBMS, certain required type statements (corresponding to required fields) are also described (step 380). For example, using the MATRIX DBMS, the required fields are:

id TABLE (COLUMN) name TABLE (COLUMN) [using TABLE (COLUMN) ] policy TABLE (COLUMN) [using TABLE (COLUMN)] OR default policy name owner TABLE (COLUMN) [using TABLE (COLUMN) } OR default owner name

Each of the above statements is described more fully below.

The Type section of the map file describes a Type or Relationship and how the instances in the foreign data source are displayed in the second DBMS (i.e., in MATRIX). In this example, the name of the MATRIX Type is indicated first: type NAME { The "{" indicates the start of a list, that is, the start of what will make up the named Type. For example: type EMPLOYEE {

The id statement defines the unique identifier for that type. In this example, because MATRIX uses object identification numbers (OIDs) for everything in the database, each object that will be represented in MATRIX from the first (foreign) database must also have an OID. MATRIX constructs the OID by concatenating the id field with other information about the type.

When an id statement is used, the integer is between 0 and 16777216. However, if there is no integer column in a table for an object type or the integers go beyond the acceptable range, the keyword mapped may be used after the i column specifications for a type. For example: type EMPLOYEE { id EMP (EMPNAME ) mapped;

}

Figure 13 also illustrates examples of type statements for the tables of Figures 8A-8C. It should be understood that objects of one type may be scattered between multiple tables. However, all mapped objects of one type have a common id column, either real or created through a join.

If a ROWID is used in a table description, these columns are mapped when referenced in a TYPE description. For example: type Department { id DEPT (ROWID) mapped;

}

If mapped is used on an id column, there are no restrictions on the range of its value.

String columns also can be used as id's , and you can also use string columns as ID's. Mapped ID's are supported, in one embodiment, only for READ-ONLY and READ-WRITE modes. In this embodiment, an adapter that uses mapped Ids cannot be used in MIGRATE or EXTEND mode, because these modes store object Ids in MATRIX.

The column in a table in the first (foreign) database correlates to the names of the business objects that will be displayed in the second DBMS (native DBMS). Accordingly, the names of the business objects are described (step 380). The name TABLE (COLUMN ) statement indicates that each row in the specified table will be an object of the type being defined, using the name in the indicated column. For example: name ENAME

Referring again to the example tables in Figures 8A-8C, the Employee type objects get their names from the ENAME column (Figure 8A). When the table containing the id is provided,

TABLE defaults to that table. Thus, ENAME is a column in the table named above in the id statement EMP. When the table containing the id is provided, the TABLE can be defaulted. Because business object instances have a policy and owner, a policy is indicated using either the policy statement, or the de ault policy statement (step 400). The same is true for owner; either the owner or default owner statement can be used. .The default policy and default owner statements are used when the adapter will not use anything in the first database to indicate required properties of the second DBMS. For example: default policy Employees; default owner sue; If there is data that can be used for owner or policy names, it can be mapped with the owner TABLE ( COLUMN ) or policy TABLE (COLUMN) statements. For example: owner ENAME;

Note that if owners (or policies) are defined in this manner, all of the persons (or policies) in the table are created in business models. For the example above, all objects were defined with a default owner, scott. This person is defined in the MQL script.

Additional optional statements can be added to the schema map being developed, although these steps are not necessarily illustrated in the flowchart of Figure 7. For example, the "next" statement specifies an expression used to calculate the id for the next object that is created of this type. It is applicable when the adapter is capable of creating objects, revisions, or clones in the first database (e.g., a legacy system) (e.g., READ-WRITE or EXTEND modes). For example: next "max (EMPNO) +1" ;

Many of the remaining fields of the example can be used to indicate the table and column in the outside source that will be used for the rest of the "basic" properties of the business objects, such as including revision, description, etc., as well as attributes. For example, for the EMP table of Figure 8 A: attribute " Hire Date" HIREDATE ;

In addition, data can be specified from multiple table. The [using TABLE (COLUMN) ] clause is used to supply an id in an alternate table and column without creating virtual, or joined tables. It may be used with any statement in a Type or Relationship described in the mapping file. For example, if there are two tables EMP and HIRE defined as: table EMP {

EMPNO int primary; ENAME string; JOB string; } and: table HIRE {

EMPNO int primary;

HIREDATE date; 1 To create an object type out of the data in both tables, a joined table could be created, then the joined table could be referred to in a Type description. For example: table EMPHIRE { j oin EMP, HIRE ; where EMP . EMPNO=HIRE . EMPNO;

}

type Employee { id EMPHIRE (EMP>EMPNO) ; name EMP (ENAME) ; attribute " Hire Date" HIRE . HIREDATE ; }

In another embodiment, the using clause can be used to get at the "Hire Date" attribute type Employee { id EMP (EMPNO) ; name EMP (ENAME) ; attribute "Hire Date" HIRE (HIREDATE) using HIRE (EMPNO) ; }

An example of an Employee type section for the scott.map file (for the scott/tiger database of this example) is shown below: type Employee { id EMP (EMPNO) ; next "max (EMPNO) +1 ; name ENAME; attribute "Employee Number" EMPNO; attribute Name ENAME; attribute "Hire Date" HIREDATE; attribute Salary SAL; attribute Commission COMM; attribute "Job Title" JOB; default policy Employees; default owner sue; }

To illustrate how attributes can be inherited from specific Types, a type hierarchy can be created (step 420). The use of type hierarchies can be understood by referring again to the tables of Figures 8A-8C. Notice that the JOB column of the EMP table are classifications (sub-types). Each different entry in this column can be specified as a type in the mapping file, the types can be created in the second DBMS. For example, in MATRIX, the deri e clause in the mapping file is used as in MATRIX to mean "inherit attributes from the specified type." In the mapping file, the type definition of a sub-type also includes the type TABLE (COLUMN) alias NAME entry - 27 - which indicates how the type is specified in the column, and how it should be used in the second DBMS. For example: type Manager { derive Employee; type JOB alias "MANAGER" ;

)

type Clerk { derive Employee; type JOB alias "CLERK";

}

type Salesman { derive EMPLOYEE; type JOB alias "SALESMAN"; attribute Commission COMM; }

The Employee table discussed previously (in connection with step 300) makes it clear that the JOB column can be used to determine which subtype a row's instance will be. Referring again to Figure 7, the relationships associated with the second DBMS system are defined (step 420) Relationships .are defined in a similar manner as Types. Descriptions, icons, attributes, etc. may all be included. The following example shows basic examples of relationships, with only the required clauses. Optionally, id's and attributes can be specified for relationships. If a relationship has attributes, a relationship id may be required. The syntax for this is identical to id's and attributes on business types.

#### matrix relationships

relationship Manager { from Employee in EMP (MGR) ; to Employee in EMP (EMPNO) ; } relationship "Salary Grade" { from "Salary Grade" in EMPGRADE ( SALGRADE . GRADE) ; to Employee in EMPGRADE (EMP. EMPNO);

}

Note that the reference on the relationship ends is to primary columns. This facilitates the connection mechanism in that use of OIDS provides better performance than use of type, name, and revision. In one embodiment, cardinality, allowed types, and other rules of relationships are enforced by the second DBMS and not by the adapter. Another optional step (not illustrated in Figure 7) is another way for mapping relationships, ids, and attributes. For example, the Salary attribute could be put on the on the Salary Grade relationship that exists between an Employee and a Salary Grade object. The following mapping could be used: relationship "Salary Grade" {

ID EMPGRADE (EMP. EMPNO); from "Salary Grade" in EMPGRADE (SALGRADE . GRADE) ; to Employee in EMPGRADE (EMP.EMPNO) ; attribute Salary EMPGRADE (EMP. SAL); }

The following example is an example of a complete schema map that can result from the method listed in Figure 7:

server scott;

imode readonly; #mode readwrite; mode extend

#mode migrate

#### physical tables tcolumn type [primary] #join table, table, table. #where . . .

table DEPT (

DEPTNO int primary; DNAME string; LOC string; }

table SALGRADE ( GRADE int primary; LOSAL real; HISAL real; }

table EMP {

EMPNO int primary; ENAME string; JOB string; MGR int; HIREDATE date; SAL real; COMM real; DEPTNO int;

table EMPGRADE ( join EMP, SALGRADE; where "EMP. SAL >= SALGRADE . LOSAL and EMP. SAL <= SALGRADE .HISAL" }

#### matrix types

#id TABLE (COLUMN)

#type TABLE (COLUMN) [using TABLE (COLUMN) ] [alias name] fname TABLE (COLUMN) [using TABLE (COLUMN)]

#revision TABLE (COLUMN) [using TABLE (COLUMN) ] #description TABLE (COLUMN) [using TABLE (COLUMN) ] #icon TABLE (COLUMN) [using TABLE (COLUMN) ] #owner TABLE (COLUMN) [using TABLE (COLUMN) ] tlocker TABLE (COLUMN) [using TABLE (COLUMN) ] #created TABLE (COLUMN) [using TABLE (COLUMN) ] #modified TABLE (COLUMN) [using TABLE (COLUMN) ] #policy TABLE (COLUMN) [using TABLE (COLUMN) ] tstate TABLE (COLUMN) [using TABLE (COLUMN) ] tattribute name TABLE (COLUMN) [using TABLE (COLUMN) ] #default policy name #default owner name

type Department { id DEPT (DEPTNO) ; next "max (DEPTNO) + 10"; name DNAME; attribute "Department Number" DEPTNO; attribute Location LOC; default policy Departments; default owner scott;

type "Salary Grade" { id SALGRADE (GRADE) ; next "max (GRADE) +1" ; name GRADE; attribute "Low Salary" LOSAL; attribute "High Salary" HISAL default policy "Salary Grades" default owner scott; type Employee { id EMP (EMPNO) ; next "max (EMPNO) +1" ; name ENAME; attribute "Employee Number" EMPNO; attribute Name ENAME; attribute "Hire Date" HIREDATE; attribute Salary SAL; default policy Employees; default owner scott; }

type Manager { derive Employee; type JOB alias "MANAGER" ; }

type Clerk { derive Employee; type JOB alias "CLERK"; }

type Analyst { derive Employee; type JOB alias "ANALYST" ; }

type President { derive Employee; type JOB alias "PRESIDENT"

type Salesman { derive Employee; type JOB alias "SALESMAN"; attribute Commission COMM; }

#### matrix relationships

relationship Manager { from Employee in EMP(MGR); to Employee; }

relationship Department { from Department in EMP (DEPTNO); to Employee; }

relationship "Salary Grade" { from "Salary Grade" in EMPGRADE (SALGRADE . GRADE) ; to Employee in EMPGRADE (EMP.EMPNO) ; }

Referring again to Figure 6, as a result of a schema map 52 such as the schema map illustrated above, the second DBMS 37(figure 6) will be able to display a plurality of objects 39 to the user interface 34 that represent the data in the foreign database 54. Figure 14 is a screen shot illustrating the objects that can be displayed to the user, in accordance with the invention. Now that the creation of the schema map 52 and business definitions 55 for the adapter 50 of the invention have been described, it is possible to illustrate the use of the adapter 50, including the shared library 56, as part of the system 20of Figure 6.

As a preliminary matter, when an adapter 50 is installed to the system 20, some preliminary configuration of the first database may be required. For example, the ORACLE server uses the init<siD> . ora file to set parameters and environment variables. When using adapters with ORACLE, in accordance with an embodiment of the invention, two settings are adjusted in this file: global_names = false . o_Pen_cursors = (12 * # of concurrent MATRIX users) + (12 * # of BOS threads) + 20 (if other ORACLE applications are in use).

The giobai_names setting disables the name uniqueness check across vaults (which are explained in greater detail below), which allows two vaults to contain business objects with the same Type, Name, and Revision. The number of open cursors required increases when using adapters. For example, when using a MATRIX ADAPLET each MATRIX client (thread) normally uses 8, but when adapters are in use this increases to 12. Furthermore, if a BOS is in use (that is, if MATRIX is being accessed over a computer network such as the Internet) each thread of the multi-threaded BOS requires the same number as other MATRIX clients.

Additionally, the setting allows for open cursors for other ORACLE applications that use the same server. The formula above can be used to formula above to determine the required number.

Figure 15 is a flowchart illustrating the method for installing and using the adapter 50 in the system 20 of Figure 6. The shared library 56 (Figure 6) is added to the system 20 when the adapter 50 is installed to the system 20 (step 500), and is stored in a location so that it, along with the schema map 52 and business definition 55, is accessible to the DBMS 37. For example, using the MATRIX system, the shared library mxf f . dii (or mxf f . so or mxf f . si for UNIX) ADAPLET is installed when the eMATRIX Adaplet Development toolkit is installed. It is placed in the INSTALL_DIR api/mxff directory, with the rest of the components of the open federations api , including the map file (scott .map ) . In this example, this interface is in place on all client machines, including BOSes and remote clients connected via a loose coupling. In this example, the business definitions are provided via an mql script (scott .mqi ) .

After the first database is modeled (step 510) (see Figure 7 and the associated description) the business definitions for the first database will have been created. For example referring to the MATRIX product and the ORACLE scott/tiger database, the Business definitions for the human resources database can be created by running the scott .mqi script which will add the required attributes, types, policies, and relationships, as well as a Person, scott, that will be the owner of all the objects. The example map resulting from this script was described previously. Because all databases (including the native database) that are connected in the system 20 are represented in the native DBMS 37 by server objects (e.g., 40, 42), server objects are created to represent each data source (step 520). In some embodiments of the invention, all federations (not just all data sources) that are connected to the system 20 are represented to the native DBMS 37 by server objects. It is preferred that the server object for the native database (i.e., native database server object 40) be created before adding server objects for the other data sources (i.e., server object 42).

The following steps can be used to create a server object in accordance with an embodiment of the invention. Figure 16A illustrates a dialog box, in accordance with an embodiment of the invention, that can be used to create the server.

A unique name should be assigned to each server object. In some instances the unique name of the server object can be constrained by the naming conventions of the data source to which the native DBMS 37 is being connected. For example, with ORACLE, the server name that is used is used as the name of the ORACLE link that is created, so the name conforms to ORACLE naming conventions (e.g., spaces not allowed, server name cannot begin with a number, but can contain numbers, etc.). The name of the database being connected can be included in the server name. For example:

SherpaServer Scott MainMatrixServer

To continue use of the scott/tiger example discussed previously, the name scott is used, to work with the exemplary MATRIX ADAPLET adapter.

A description of the server can also be provided, to give general information about the purpose of the server. For example:

Connects to the Oracle Human Resources database.

A username can also be defined. For example the username scott could be entered to be compatible with the mapping file described previously. A password associated with the username can also be added. In the example being used herein the password is tiger.

A connect string likewise can be added to the server definition. In the example of the ORACLE sample database, the default ORACLE database Alias can be used in the Connect String text box of Figure 16A. A timezone definition, as illustrated in Figure 16 A, can also be defined.

Referring again to Figures 6 and 15, a vault 44 is created for each database being connected. Figure 16B illustrates a dialog box, in accordance with an embodiment of the invention, that can be used to create a vault 44.

A unique name is assigned to the vault 44. In this example, the name can be up to 128 characters long and can contain spaces. Because of this flexibility in naming the vault 44, it is helpful to assign a name that has meaning to the system administrator and/or the users. For example, the name of the foreign database can be used as the name of the vault 44:

Human Resources vault Sherpa Vault

As with the server, a description of the vault 44 can also be provided, to give general information about the purpose of the vault 44. The description can also help point out subtle differences between vaults to the user. For example:

Contains Matrix objects for Human Resources data.

A type is also defined for a vault 44. In this example, the type of vault is Foreign. In the example dialog box of Figure 16B, the parameters in the New Vault window change depending - 34 - on the Type chosen. In this example, for foreign vaults, there are additional parameters for Interface and Mapping.

The interface 58 generally corresponds to the name of the shared library 56. For example, using the MATRIX ADAPLET adapter, the Interface can be specified as iNSTALL_DiR/api/mxf f /mxf f , with no extension. The .dll or shared library file is then used to access the vault 44, depending on whether the client 36 is a windows or a UNIX client. In this example, the INSTALL DIR directory is the same on every client 36, and contains the interface 58

The interface 58 may be used in more than one vault definition, and this can be done by making a copy of it with a different name. For example, if the interface 58 is used to link a second foreign database (presumably with a different schema map 52 associated with it) a copy of the interface can be created and renamed, and then referenced in the vault definition associated with that second foreign database.

Referring to Figure 16B, the mapping for the vault 44 corresponds to the schema map 52 described previously.

When the vault 44 is created, the system 20 assigns it table space for data and for indices. Initially, these tables are empty. As objects in foreign vaults 44 are updated, data will be placed in a vault's tablespace, if the adapter is working in MIGRATE or EXTEND mode. For example, if attributes are added to a type, or if attribute values are changed, this data is stored in the second DBMS 37 only. By printing the vault contents, a list of objects that have been updated can be viewed. These objects correspond to objects for which there is a table entry in both the first database and the second DBMS.

Once the adapter with all of its schema requirements is in place, clients 36 with the interface 58 installed may query the foreign vault and see all the data from the foreign database reconstructed in the format of the second DBMS 37 (step 540 of Figure 15). For example, as shown in Figure 16, the ORACLE scott/tiger database is reconstructed as MATRIX objects as defined in the mapping file.

In this example, the adapter 50 provides full READ- WRITE access to these mapped objects 39. This means that any operation associated with the native DBMS 37 (e.g., any MATRIX operation) may be attempted on objects in a foreign vault 44, such as modify attributes, promote, connect, etc. Mapped relationships, as well as connections made from within the second DBMS 37, may be navigated. - -

Nariations, modifications, and other implementations of what is described herein will occur to those of ordinary skill in the art without departing from the spirit and scope of the invention as claimed. Accordingly, the invention is to be defined not by the preceding illustrative description but instead by the spirit and scope of the following claims.

What is claimed is:

Claims

- 36 - 1. A method for modeling a first database associated with a first database management system, the first database having at least one physical table divided into columns and rows and comprising at least one data file, comprising the steps of: a) determining the structure of the first database; b) creating, based on the structure of the first database, at least one business object defined by a business definition; and c) mapping each data file in the first database to a corresponding business object in accordance with a mode definition, the mode definition specifying how the corresponding business object can manage the data file.

2. The method of claim 1 wherein the step of determining the structure of the first database further comprises determining associations between columns of each table in the first database.

3. The method of claim 1 wherein the step of determining the structure of the first database further comprises determining associations between tables in the database.

4. The method of claim 1 wherein the step of creating a business object further comprises creating a business object defined by a business definition comprising at least one parameter selected from the group comprising attributes, types, policies, relationships, owners, and states.

5. The method of claim 1 wherein the step of mapping each data file in the first database further comprises: c-a) describing each table in the first database, said description comprising a description of at least each column in the table and of the type of data associated with the column; and c-b) describing any associations existing between tables in the first database.

6. The method of claim 1, wherein the mode definition of step (c) corresponds to a mode selected from the group read-only, read-write, extend, and migrate.

7. The method of claim 1 , further comprising the step of d) defining how the business objects associated with the first database relate to the schema of a second database management system.

8. The method of claim 7, wherein step (d) further comprises describing a type having a unique identifier, the type defining how the data file in the first database is displayed in the second database management system.

9. The method of claim 7, wherein step (d) further comprises describing a relationship having a unique identifier, the relationship defining how the data file in the first database is displayed in the second database management system.

10. The method of claim 7 wherein step (d) further comprises associating the name of the business object with the corresponding column in the first database.

11. The method of claim 7 wherein step (d) further comprises defining a policy and owner for each instance of the business object.

12. The method of claim 7 wherein step (d) further comprises defining relationships between the business objects under the second database management system.

13. The method of claim 7, further comprising the steps of: e) creating a unique server object representing the first database in the second database management system; f) creating a unique vault object in the second database management system, where the vault object stores the business objects associated with the first database and where the vault object is accessed using a function available in a library; and g) defining a library in the second database management system, where the library is responsive to the mapping of the first database and includes at least one function relating to managing data in the first database;

whereby access to data in the first database from the second database management system is granted by accessing the unique server object and the unique vault, and whereby management of the data in the first database using the second database management system is accomplished using the function in the library.

14. The method of claim 1 wherein the first and second database management systems are different types of database management systems.

15. A method for managing data in a first database using a database management system associated with a second database, comprising the steps of: a) defining at least one library accessible to the first and second databases, the library including at least one function relating to managing data in the first database; b) determining the structure of the first database; c) defining, based on the structure of the first database, a unique object for each data file in the first database, the unique object comprising at least one parameter characterizing all columns of data in the first database; d) modeling the data in the first database as a plurality of objects, each object having parameters in accordance with an operating mode; and e) passing the plurality of objects to the library for use during the at least one function.

16. A system for integrating information from a first database system into a second database system, comprising: a) a first unique server object associated with a first database; b) a second unique server object associated with a second database; c) an adapter for modeling the first database system, the adapter comprising: (i) a schema map operable with the first and second server objects to define the correspondence between information in the first and second database systems; (ii) a business definition comprising information relating to the structure of the second database; and (iii) a library implementing, in accordance with the schema map and business definition, at least one function relating to managing the data in the second database system.