Method for representing and recreating object dependencies from one database system to another

A method for recreating a plurality of database dependencies of objects from one database to another is disclosed. The method comprises categorizing the dependencies based upon at least one base object and formalizing the relationships of the dependencies based upon the categorization. The method includes determining the order of recreating the objects based upon the formalizing of the relationships of the dependencies. In a preferred embodiment, the method builds a Dependency Management Table (DMT) that describes the relationship of each object to a base-table. The DMT includes an entry for every object-to-object relationship, including the category of the relationship, and the nesting-level of the dependency from the base-table. Using this information, objects can be recreated in an order which ensures correct management of dependencies.

FIELD OF THE INVENTION

The present invention relates generally to a database system and more specifically to recreating objects with inter-dependencies from one database system to another.

BACKGROUND OF THE INVENTION

Database systems are utilized for a variety of environments. For example, a database system such as the IBM RDBMS system is utilized to track certain information. It is desirable to be able to translate this information to information with another database. Accordingly, it is important to be able to recreate database management objects (core objects such as tables, as well as business objects such as views) from one database system to another in an order that satisfies dependencies among these objects. For instance, in order to recreate a table, all the objects that this table depends on must first be recreated, but all objects that these dependent-objects depend on must be recreated first, and so forth.

Accordingly, there is a need to take a set of tables as well as business objects such as views from one database and recreate them (either into a different database, or into the same database). Therefore, what is needed is a method for recreating database objects from one database system to another while managing the dependencies between these objects, thereby accomplishing the above-mentioned functions. The method should be adaptable, cost effective and easily implemented on a database system. The present invention addresses such a need.

SUMMARY OF THE INVENTION

A method for recreating a plurality of database dependencies of objects from one database to another is disclosed. The method comprises categorizing the dependencies based upon at least one base object and formalizing the relationships of the dependencies based upon the categorization. The method includes determining the order of recreating the objects based upon the formalizing of the relationships of the dependencies.

In a preferred embodiment, the method builds a Dependency Management Table (DMT) that describes the relationship of each object to a base-table. The DMT includes an entry for every object-to-object relationship, including the category of the relationship, and the nesting-level of the dependency from the base-table. Using this information, objects can be recreated in an order which ensures correct management of dependencies.

DETAILED DESCRIPTION

FIG. 1illustrates an exemplary hardware and software environment that could be used with the preferred embodiment of the present invention. In the exemplary environment, a server computer100is connected to one or more client computers102or terminals. The server computer100executes a relational database management system (RDBMS) that manages user and system tables104and includes a system log106. In the preferred embodiment of the present invention, the RDBMS comprises the DatBase 2 (DB2®) product offered by IBM® Corporation, although those skilled in the art will recognize that the present invention has application to any RDBMS. The client computers102interface to the RDBMS via a client interface component108.

As illustrated inFIG. 1, the RDBMS includes three major components: the Resource Lock Manager (RLM)110, the Systems Services module112, and the Database Services module114. The RLM110handles locking services, because the RDBMS treats data as a shared resource, thereby allowing any number of users to access the same data simultaneously, and thus concurrency control is required to isolate users and to maintain data integrity. The Systems Services module112controls the overall RDBMS execution environment, including managing log data sets106, gathering statistics, handling startup and shutdown, and providing management support.

At the heart of the RDBMS is the Database Services module114. The Database Services module114contains several submodules, including a Relational Database System (RDS)116, Data Manager118, Buffer Manager120, and other components122such as an SQL compiler/interpreter. These submodules support the functions of the SQL language, i.e., definition, access control, retrieval, and update.

Generally, the RDBMS comprises logic and/or data that is embodied in or retrievable from a device, medium, or carrier, e.g., a fixed or removable data storage device, a remote driver coupled to the computer by a data communications device, etc. Moreover, this logic and/or data, when read, executed, and/or interpreted by the computer100, causes the computer100to perform the steps necessary to implement and/or use the present invention.

Of course, those skilled in the art will recognize many modifications may be made to this configuration without departing from the scope of the present invention. For example, those skilled in the art will recognize that any combination of the above components, or any number of different components, including computer programs, peripherals, and other devices, may be used to implement the present invention, so long as similar functions are performed thereby.

A method in accordance with the present invention provides a mechanism to represent object dependencies, and then allows for the objects to be recreated in the correct order. This mechanism involves describing the relationship of each object to a base-table. This description include an entry for every object-to-object relationship, including the category of the relationship, and the nesting-level of the dependency from the base-table. Using this information, objects can be recreated in an order which ensures correct management of dependencies.

To describe the features of the present invention in more detail refer now to the following description in conjunction with the accompanying Figures.

FIG. 2Ais a flow chart that shows generally the process of recreating object dependencies from one database system to another. First the dependencies are categorized based upon at least one base object, via step150. Next the relationships of the dependencies are formalized based upon the categorization via step152. Finally, the order of recreating the objects is determined based upon the formalizing of the relationships of the dependencies via step154.

FIG. 2Bis a diagram showing generally the process in accordance with the present invention of recreating object dependencies from one database system to another. In this embodiment there are two databases202and204. Each of the databases202,204includes tables206,208respectively. Each of the databases202,204also includes business objects210,212, respectively. The tables206,208have dependencies to each of their respective business objects210,212. Accordingly, what is desired is to recreate these dependencies from database202to database204. In this embodiment, dependencies are derived (214) from database202, the derived dependencies are depicted into categories (215), then those dependencies are categorized (216). Thereafter the relationships of those dependencies are related to a build order (217) and are provided (218). Thereafter build order for recreation are set forth (219) and object dependencies are recreated (220) in database204based upon the relationships. In order to accomplish this, using the example of core objects such as tables, the following issues need to be addressed:

(1) A table can depend on other objects.

(2) These other objects can depend on other objects, including other base-tables.

(3) In order to recreate the table, all of the dependent objects need to be recreated first. However, since dependencies can exist on many levels and in different forms, a system and method is needed to formally categorize and accrue these dependencies and is also needed to generate the correct order of reply.

(4) Additionally, it is also necessary to recreate all of the objects which depend on the above-mentioned tables.

To describe these features in more detail, refer now to the following description in conjunction with the accompanying Figures. First certain terms will be defined.

Tables

At the simplest level, a database system contains tables (objects which store records of information separated into different columns). Insertion of data into, and querying data out of tables is the fundamental basis for a database system.

Business Objects

Above this, a database system may contain business-objects (some entity that helps a user achieve something). For instance, a User Defined Type (UDT) is a business object that allows a user to define a new column data-type. For example, a customer might create a new UDT as a structure of several basic data-types, using pseudo code like:

CREATE TYPE Person_t AS

The user can then create a table using this type as a column:

CREATE TABLE tab1

and query data from this table:

FROM tab1;

Another type of business object is a VIEW. A view is a ‘named specification of a result table’. That is, a view can be declared as a specific query:

Then simply use the view in different ways

A database system will contain many different types of business objects. For the purpose of the disclosure of the invention it is not necessary to identify them in all permutations, but rather a basic principle that needs to be understood is that a base table might depend on business objects (i.e. in the example above, table tab1depends on UDT Person_t), and likewise a business object might depend on a base table (i.e., in example above, view v1depends on table tab1).

Also, in a database system, the relationship between objects is recorded (in DB2, the relationship is recorded in the system catalog tables. Various catalog tables can be queried to determine the dependencies for any particular object). Given an object, it can be determined what direct dependencies this object has, and what other objects depend on it.

A method in accordance with the present invention categorizes and accrues dependencies as well as how to use this information to generate the order which guarantee's objects are recreated successfully.

First a plurality of dependency types are categorized, and the method of categorization employed must handle these different classes of dependencies: direct dependencies, nested dependencies, multi-level dependencies and up level/down level dependencies. To describe each of these dependencies in more detail, refer now to the following discussion in conjunction with the accompanying figures.

Direct Dependencies

FIG. 3shows a direct dependency between objects. A direct dependency describes a relationship between two objects. In this example, a table300contains a generated-column which references a User Defined Function (UDF)302. Therefore there is a direct dependency between the table300and the UDF302. In order to recreate the table300, the UDF302must be recreated first.

Nested Dependencies

FIG. 4shows a depiction of a nested dependency between objects. A nested dependency describes a relationship between nested levels of objects. In this example, a table400contains a generated-column which references a User Defined Function (UDF)402, and this UDF402references a User Defined Type (UDT)404. Therefore there is a direct dependency between the table400and UDF402, a direct dependency between the UDF402and UDT404, and thus a nested dependency between the table400and UDT404(the level of recursion of this dependency will be referred to as the ‘nest-level’ value, which in this case is2). In order to recreate the table400, the UDT402must be recreated first, then the UDF504, then the table400.

FIG. 5shows a multi-level dependency between objects. A multi-level dependency describes a relationship between multiple objects, such that one particular object exists at multiple nest-levels. In this example, a table500contains a generated-column which references a User Defined Function (UDF)502, and this UDF502references a User Defined Type (UDT)504. Now, table500also contains another column which references the same UDT504directly. There is a direct dependency between the table500and the UDT504, a direct dependency between the table500and UDF502, and a nested dependency between the table500and UDT504(thus there are two different ‘nest-levels’ of recursion between the table500and the UDT504). In order to recreate the table500, the UDT504must be recreated first, then the UDF502, then the table500.

FIG. 6is a depiction of up-level and down-level dependencies between objects. Up-level/Down-level dependencies describe whether a dependency is referenced by a particular object (up-level), or whether a dependency references a particular object (down-level).

Choosing an arbitrary object, a table600for example, all down-level dependencies must be recreated in order to recreate the table600. But, a user might also wish to recreate up-level dependencies, for example a view602which references the table. The mechanism/method and system in accordance with the present invention must safely handle both down-level and up-level dependencies. Every up-level dependency can have an additional set of down-level dependencies which would need to be recreated appropriately.

Next, an object-type needs to be chosen that will be the base-object of the dependency relationships. In this embodiment the table object600is used as a base-object in this example.

After the dependencies are accrued, the dependencies between base-objects and other objects can be categorized as follows:

(3) Category C dependency: is a down-level dependency from a Category B object (object UDF2610from Category B object view1608inFIG. 6).

After the dependencies are categorized, next, the ‘nest-level’ of recursion of the dependency from some base-object are enumerated.FIG. 7is a diagram that depicts various nest level recursion of dependencies between objects. In this example, the dependency between table700and UDF1702would have a nest-level of 1, and the dependency between UDF1702and UDT1704would have a nest-level of 2.

In this embodiment using tables as the base-objects,

(1) Every Category A dependent object would have a nestlevel equal to the downward level of recursion from the base-object (TABLE).

(2) Every Category B dependent object would have a nest-level equal to the upward level of recursion from the base-object (table700).

For example, inFIG. 7the dependency between view1706and table700would have a nest-level of 1, the dependency between view2708and view1706would have a nest-level of 2.

iii) Every Category C object would have a nest-level equal to the level of recursion from the non-base-object.

For example, inFIG. 7the dependency between view2708and UDF2710would have a nest-level equal to 1, the dependency between UDF2710and UDF3712would have a nest-level of 2.

Using these categories and nest-levels, these dependencies can be informally described using a graph-like diagram.

FIG. 8is a diagram that illustrates various dependencies between objects. If these dependencies were listed on a chalk board, a diagram (900) depicting these dependencies might look likeFIG. 9which is a graph-like representation of the relationships shown in shownFIG. 8. For example, at910, the dependency relationship path as between T3and T2is set forth.

Proceeding one step further, a graph can be created for each category.

FIG. 10is a graph illustrating Category A dependencies for the diagram ofFIG. 8.

FIG. 11is a graph illustrating Category B dependencies for the diagram ofFIG. 8.

FIG. 12is a graph illustrating Category C dependencies for the diagram ofFIG. 8.

Next, the relationships depicted in the graphs can be formalized by using, in this embodiment, a Dependency Management Table.FIG. 13illustrates the layout of a Dependency Management Table derived from the above identified graphs.

Referring toFIG. 13, the fourth row in this table indicates “Table T1references UDF U1, at nestlevel1, and this is a Category A object”.

Finally, the guaranteed order of recreation of objects can be determined from the table. In this embodiment:

When duplicate entries exist for an object (in this example table T2exists with nestlevel0,2, and4since it is referenced by multiple objects) then the highest nestlevel takes precedence (the other entries are ignored).

Category A objects first are recreated, from highest nestlevel to lowest, by referencing the ‘REF_OBJNAME’ column of the Dependency Management Table.

Category C objects are recreated next, from highest nestlevel to lowest, by referencing the ‘REF_OBJNAME’ column of the Dependency Management Table, avoiding those objects which are already included in the Category A objects recreation.

5. Category B objects are recreated last, from lowest nestlevel to highest, by referencing the ‘OBJNAME’ column of the Dependency Management Tale, avoiding those objects which are already including in the category A object and the Category C object recreations.

So, given the example above, the correct order would be constructed in the following manner:

->Start with Category A objects from highest nest-level to lowest (letting higher nest-level's take precedence for duplicates, and choosing arbitrarily between equal nest-levels):

->Next add Category C objects from highest nest-level to lowest, (letting higher nest-level's take precedence for duplicates, choosing arbitrarily between equal nest-levels, and avoiding objects already listed (see font)):

->Nest add Category B objects from lowest nest-level to highest, using same criteria as above:

A method in accordance with the present invention provides a mechanism to represent object dependencies, and then allows for the objects to be recreated in the correct order. In a preferred embodiment, the method builds a Dependency Management Table (DMT) that describes the relationship of each object to a base-table. The DMT includes an entry for every object-to-object relationship, including the category of the relationship, and the nesting-level of the dependency from the base-table. Using this information, objects can be recreated in an order which ensures correct management of dependencies.