GB2459354A - Emulating a plurality of databases using a single physical database - Google Patents

Abstract

Emulating a plurality of databases (logical databases 205, 206, 207) using a single physical database 208. A database system (110, fig 1) comprising a plurality of databases (103, fig 1) on a plurality of servers, each database coupled to at least one application 201-203 (101, fig 1) of a set of applications. Consolidation of such a system to a single physical database comprises substituting all logical names from the plurality of databases with a unique physical name for all the plurality of databases. A mapping catalogue (106, fig 1; 316, fig 3) is created which comprises the logical names and their assigned unique physical names, the mapping catalogue being located in a physical layer. Each database is saved with its unique physical names into a different segment 209-211 of the single database 208, the single database being located in the physical layer. A consolidation layer 204 is provided which is coupled to each application 201-203. A separate consolidation layer (105, fig 1; 306-310, fig 3) is provided for each application and is coupled to the mapping catalogue and to the single database. The consolidation layer is adapted to receive queries from the applications, search the mapping catalog, rewrite the queries and forward the rewritten queries to the single database. The response from the single database may also be rewritten. The plurality of databases are simulated as logical databases and consolidation is transparent for the applications.

Description

DESCRIPTION

DATABASE CONSOLIDATION AND METHOD THEREOF

Technical field

The invention relates to a database consolidation method in a database system, to a database system and to a computer program product.

Background and related art

A database system is a system designed to manage a database and run operations and commands on the data requested by numerous users. The database management system is a set of programs that manages the organization, storage and retrieval of data in a database. The programs may include: a modeling language tO define the schema of each database located in the database management system; data structures as fields, records, files and objects optimized to deal with a very large amount of data stored on a device; and a database query language to allow users to interact with the database and analyze its data and update it.

A typical deployment of database systems may include small databases that are used for various purposes that include, for example, test systems, department systems, and prototyping of new functions, demo systems and hosting environments. The administrative overhead for these small databases may be expensive compared to the amount of data that is managed.

Therefore, a new database consolidation method, a database system and a computer program product is needed.

Sununary of the invention The invention relates to a method for substituting a plurality of databases with a single database, each database coupled to at least one application of a set of applications, the method comprising the steps of: -associating logical names from the plurality of databases with unique physical names; -saving the logical names and corresponding unique physical names in a mapping catalog of the single database; -saving each database including the unique physical names into a different segment of the single database; -providing each application with a consolidation layer coupled to the mapping catalog and to the single database, the consolidation layer being adapted for receiving queries from the applications, searching into the mapping catalog, rewriting the queries and sending the rewritten queries to the single database.

The set of applications send queries to and receive responses from the logical databases. The logical databases are emulated by the consolidation layer and the single database.

The main advantage of the embodiments is that by consolidating small databases into a single database, and using the database consolidation layer, the connection between the applications and the database is separated between a physical and a logical layer. This is achieved by mapping logical database objects from an application to physical database objects on the single database. This consolidation layer rewrites all instructions or queries and maps the information into a mapping catalog before handing it over to the physical database, or from the single database to the application. This allows an improved performance on several administrative tasks as all the databases can be handled as a single database.

In another aspect, the invention relates to a method for querying a logical database, the method comprising the steps of: -receiving a query from a first application by a consolidation layer for accessing the logical database, the request comprising a query object, the query object being a logical name, wherein the logical database is emulated by the consolidation layer; -searching a physical name associated with the logical name in a mapping catalog by the consolidation layer, wherein the mapping catalog comprises a list of physical names of the single database, a list of logical names and object types corresponding to the physical names; -substituting the logical name in the query by the corresponding physical name by a rewriting function in the consolidation layer, the physical name being a unique name in the single database; -sending the query with the physical name from the consolidation layer to the single database, the physical name being located into a first segment of the single database, the first segment assigned to the first application, and -receiving a response of the query from the first segment by the consolidation layer, the response comprising a second physical name. The method further comprises: searching a logical name assigned to the physical name in the mapping catalog by the consolidation layer; -substituting the physical name in the response by the assigned logical name by the rewriting function in the consolidation layer; -sending the response with the logical name to the first application by the consolidation layer (502) .The single database (505) is divided into a plurality of segments, and each segment In another embodiment, the single database (107) is stored in a single server or in a cluster of servers.

In another embodiment, the database consolidation layer 204 is located in a client with the applications. AlternativelY, the database consolidation layer 204 is located in an application server between the client and the database server.

Alternatively, the database consolidation layer 204 is located in the database server with the single database.

In another embodiment, the database system further comprises a map cache for each the consolidation layer (DBCL); wherein the map caches comprises a portion of the mapping catalog.

In a further embodiment, the map cache contains a complete Copy of the mapping catalog. Alternatively, the map cache contains only the information that has been requested through the database consolidation layer. Every time the rewriting function is used1 the assigned physical name and related information is stored in the map caches. Alternatively, the map cache is configured specifically for the coupled application and the portion of the mapping catalog related to the application IS stored in the map cache.

In a further embodiment, the map cache can be initialized after system start and all updates to the mapping catalog are propagated to each map cache. These updates can be triggered by consolidation layers running on other systems. Alternatively, the map cache is initialized upon request.

In a further embodiment, the map caches queries the mapping catalog for obtaining updates or changes of the mapping catalog.

Alternatively, the map caches are coupled to each other and wherein an update on one map cache is propagated to other map caches.

I

In a further embodiment, the consolidation layer handles statements of at least two types f languages: Data Manipulation Languages (DML) and Data Definition Language (DDL).

In another embodiment, a first DDL statement type requires rewriting the first DDL statements with the rewriting function and store it in the mapping catalog; wherein a second DDL statement type requires rewriting the second DDL statement type with the rewriting function, store it in the mapping catalog and in the single database, wherein a third DDL statement type requires storing in the mapping catalog.

In another embodiment, a create alias and a create schema of a DDL statement are the first DDL statement type and object types on the mapping catalog; wherein a create bufferpOOl and a create stogroup are the third DDL statement type and object types on the mapping catalog.

In another embodiment, a create: distinct type, function, index, method, procedure, sequence, table, transform group, trigger, type and view are the second DDL statement type and object types on the mapping catalog.

In a further embodiment, two different logical names of two different logical databases correspond to a common physical name; wherein the query and the response of the query are SQL statements. In the database system of the embodiments, it Is possible to have two different logical names of two logical databases that have assigned the same physical name, allowing a more efficient resource sharing.

In another embodiment, the consolidation layer (DBCL) uses JDBC or ODBC/CLI programming interfaces. JDBC stands f or Java Database Connectivity and ODEC/CLI stands for Open Database Connectivity at the Call Level Interface.

In another embodiment, the consolidation layer (DBCL) is implemented within the database system. In a further embodiment, a plurality of special registers of the database system are rewritten with the rewritten function and stored in the mapping catalog.

In another aspect, the invention relates to a database system being operable to perform in accordance with any of the preceding embodiments.

In another aspect, the invention relates to a computer program product stored on a computer usable medium, comprising computer readable program means for causing a computer to perform a method according to any of the preceding embodiments when said program is run on said computer.

Brief description of the drawings

In the following preferred embodiments of the invention will be described in greater detail by way of example only making reference to the drawings in which: Fig. 1 shows an example of a database consolidation method, Fig. 2 shows an example of a single database, Fig. 3 shows a further example of a database system with a single database, Fig. 4 shows an example of the interaction between the mapping catalog and the map cache, Fig. 5 shows an example of the querying in a logical database and Fig. 6 shows an example of the information stored in the mapping catalog.

Detailed description of the drawings

Fig. 1 shows an example of the database consolidation method in a database system, the database system 110 before the migration comprising an application 101, a connectivity mechanism 102 that is coupled to a database 103 and to a catalog 104. Fig. 1 also shows the database system 111 after the database consolidation method has been completed for the database 103, including the same application 101 coupled to a database consolidation layer 105. The database consolidation layer 105 includes the functions of the connectivity mechanism 102 and is coupled to a mapping catalog 106, the consolidation layer 105 also coupled to a single database 107 that includes a first segment 108.

In a typical deployment of database systems, small databases are used for various purposes that include test systems, department systems, prototyping of new functions, demo systems and hosting environments. The administrative overhead for these small databases may be expensive compared to the amount of data that is managed. The complexity of maintaining a potential big amount of small databases is time consuming. The required performance of these types of systems in many cases is uncritical compared to other deployed production systems. This as an area leads to an implementation of a significant amount of database systems and potentially hundreds of low cost servers, but that are very expensive to maintain.

The purpose of the embodiments is to consolidate all or a portion of these small databases into a single database by using the database access layer, that abstracts the databases by mapping logical database objects from an application to physical database objects on the single database. This consolidation layer acts as a gateway rewriting all instructions or queries by mapping the information into a mapping catalog before handing it over to the physical database, or from the single database to the application. This enables storing the multiple databases �fl a single database, and separating it from the logical layer.

This allows administrating all the databases as a single database.

The single database and the mapping catalog are located in the physical layer. In the logical layer, the logical databases are emulated by the consolidation layer that is coupled to the applications.

The first step on the database consolidation method comprises substituting the logical names from the database 103 with a unique physical name for all the databases that will be stored in the single database 107. All these logical names and their corresponding physical names are saved into the mapping catalog 106 that is located in the physical layer, as well as the single database 107. The database 103 with the assigned physical names is stored into a first segment 108 of the consolidation database 107.

In a further step, a database consolidation layer 105 is coupled to the application 10l so that from the application perspective it continues to interact with the same logical names. The database consolidation layer 105 simulates a logical database *and rewrites all the instructions or queries received by the application using the information stored in the mapping catalog before it is sent to the corresponding segment of the consolidating database. In the other direction it also rewrites the responses or information from the single database 107, rewrites according to the information stored in the mapping catalog 106 and sends it back to the application 101.

The database consolidation layer 105 includes all the functionS of a connectivity mechanism. Alternatively, the database consolidation layer 105 is coupled to a separated connection layer, as for example JDEC, and all the functions of the connectivity mechanism is implemented by this layer.

The recommended scenario for these types of consolidation databases include more than ten small database systems that do not require high performance services, as it can be non-standard backup. In this new single database system, the application still interacts with an exclusive logical database. The administration view is a shared physical database with a logical layer coupled to it, wherein several administrative tasks are handled with the single database directly, and where the logical layer can be also accessed as an entity1 for example in backup and restore tasks.

Fig. 2 shows an example of the single database in a database system 200 comprising a first application 201, a second application 202 and a third application 203 coupled to a database consolidation layer 204. This consolidation layer 204 simulates a first logical database 205 that is coupled to the first application 201, a second logical database 206 that is coupled to the second application 202, and a third logical database 207 coupled to the third application 203. The database consolidation layer 204 is coupled to the physical database 208 that includes three segments 209, 210, 211 that correspond to the previous databases that each application access.

On a first possible location for the database consolidation layer, the database consolidation layer 204 is stored in a client with the applications. Alternatively, the database consolidation layer 204 is stored in an application server between the client and the database server. Alternatively, the -10 -database consolidation layer 204 is stored in the database server with the single database.

Each of the former databases are simulated as logical databases so that the consolidation is transparent for the applications and no changes are required as the context of the consolidation is unknown to the application. The database objects of the databases are rewritten in order to avoid name collisions in the single database, in the case that for example two former databases had objects with the same name.

The logical databases contain the data in a segment of the single database and no other data from other logical databases is available to the application. Some of the advantages of consolidating all the small databases into a single database include having a single point of administration and better resources for all the databases, as balancing and performance tuning. Also, the sharing of data between the logical databases is possible in the single database and two different values in the logical databases may correspond to a single value at the single database.

Fig. 3 shows a further example of the single database in the database system 300 that comprises five applications 301-305, five database consolidation layers 306-310, each one of them coupled to an application 301-305. The database system further comprises five map caches 311-315, each one of the map cache is coupled to the database consolidation layer, to the mapping catalog 316 and to the single database 317. The single database 317 further comprises six segments 318-323 for each one of the applications 301-305. The database system 300 also shows the view from the user 329 of any of the applications 301-305 and the view from the administrator 325 of the single database 317.

on a first map cache implementation possibility1 the map cache contains a complete copy of the mapping catalog. Alternatively, the map cache contains only the information that has been requested through the database consolidation layer. Every time the rewriting function is used, the assigned physical name and related information is stored in the map caches. Another possibility is that the map cache is configured specifically for the coupled application and the portion of the mapping catalog related to the application is stored in the map cache.

The map cache can be initialized after system start and all updates to the mapping catalog are propagated to each map cache.

These updates can be triggered by consolidation layers running on other systems. Alternatively, the map cache is initialized upon request.

The main advantage of the embodiments is to disconnect the application view at a logical layer from the administration view of the databases, without rewriting the applications 301-305.

Through the database consolidation layers 307-310, which performs the mapping between the logical layer to the physical layer with the information stored in the mapping catalog 316 and with the support of the cache mapping catalog in the map cache 311-315, which allows an efficient access to the mapping information, the mapping between the layers is seamless to the application.

Many of the tasks of the administration are performed without the knowledge of the logical layer and this information of the logical layer is accessible to the administrator in case that it is required. This may be for example, a recovery of a single logical database that corresponds to a segment of the single database. Although the information regarding the single database access is contained in a single mapping catalog, the application -12 -data is still separated in different segments of database objects.

Fig. 4 shows a further example of a mapping catalog in accordance with an embodiment of the invention. The mapping catalog 401 is coupled to at least one map cache 402 and the other map caches 403 and 404. These map caches 402-404 are each coupled to the different database consolidation layer 405-407, each one of them stored on a different server 408-410. An application 411 is running on one server 412 and possibly in a second server 413 is coupled to the different consolidation layers 405-407.

There are two key elements that provide the required performance to the database system and these are the access to the mapping catalog and the rewriting function for the instructions or queries. These can be managed with cached portions of the mapping catalog 401 into map cache 402-404, through grid infrastructures between these elements. The applicatiOn 411 can use multiple instances of the consolidation layers 405-407, or from a single application server 412 to multiple DBCL or consolidation database layer instances. AlternativelY, it is possible to share a database consolidation layer between multiple application servers 412 and 413. In one embodiment, the application server 412 and the database consolidation layer 403 may be running on the same physical hardware, so that they can use a direct local communication. In this way, the mapping process and the rewriting function shares the required CPU processing that is necessary on an inexpensive hardware.

In order to access the information in the map cache 402 and 404, the information may be stored in parallel. If there is a modification of the information in the mapping catalog 401 or in the map cache, the map cache that is doing the update propagates the modification to the other elements in the grid. In this case, the other map cache automatically updates the information.

Alternatively, the invalidated part of the information can be accessed from the mapping catalog located in the physical database. Further cache synchronization techniques may also be used in these tasks.

In general, the application may use multiple instances of the consolidation layer by: -Connecting from multiple application servers to multiple servers running the consolidation layer. The consolidation layer server could be running on the same physical hardware, using a direct local communiCat3Ofl.

-Connecting from a single application layer to multiple consolidation layer instances.

-Sharing a consolidation layer between multiple application servers.

Fig. 5 shows a flowchart of the rewriting process between an application and the single database. The rewriting process 500 comprises an application 501, coupled to a database consolidation layer 502 that is coupled to a mapping catalog 503. The consolidation layer 502 is coupled to a rewriting function 504 and to a single database 505.

In the mapping process, the application 501 is connected to a simulated logical database 506 and makes a request for an object A' to the logical database 506. The consolidation layer 502 looks up for the information in the mapping catalog on how A' is called within the single database. The consolidation layer obtains the information from the mapping catalog that the logical name or logical database object A' is called X' within the single database. This information is sent to the rewriting function 504 that substitutes in the query from the application all reference to the logical database objects or logical names A' with the names of the physical database object X'. The -14 -rewriting query or instruction is then directly sent to the single database that is then able to resolve the requested objects.

The result of the request coming from the single database is not returned to the application directly and instead is handled by the consolidation layer that looks again in the mapping catalog 503 for the corresponding logical name from the physical name X'. The information obtained from the mapping catalog 503 is sent again to the rewriting function 504 that rewrites the response before being finally sent to the application 501. The application receives all information as requested without any information regarding the rewriting functions or the rewriting process.

Fig. 6 shows an example of a table 600 that is stored in the mapping catalog and contains the information on how to map database objects from the logical databases to database objects in the single database. The table 600 contains the names 601 of the logical databases, the object types 602 for this physical and logical object, the logical object name 603 and the physical object name 604.

In the table 600 the logical object name HUGO has a corresponding physical object name SCHM4711, with an object type schema that is stored in the logical database 1. From the table 600 it is important to notice that the logical object name HUGO.ORDERS from the logical database, database 2' has the same logical object name that a logical object stored in the database 1. However, this shared logical object name in two different databases have a different physical object name, the first being SCHM4711.TBLO815 and the second being SCHN000.TBL0456. This is important as objects originated from different former databases are different objects and must be separated in the single database.

Another important example is that the two different logical object names HUGO.ORDERS in the database 1 and the logical object name TEST.ORDERS in the database 2 share the same physical object name, allowing a simple sharing of objects or data between the logical databases without the application knowing that the real table may be used by other applications working with other databases, simplifying the overall sharing of the data.

The database system handles at least two types of statement languages: Data Manipulation Languages (DML) and Data Definition Language (DDL). An example of a DDL statement is create a table', where the DDL statement or instruction needs to be rewritten and the new mapping information, including the logical and the physical name is inserted in the table 600 of the mapping catalog. Another example is create alias' DDL statement that is mapped in the mapping catalog and used to rewrite the statements, which may be SQL statements. A creation within the physical database is not necessary. A further example is create bufferpool' statement, which does not create any objects in the single database or is rewritten. This command is used only for administrative purposes.

One type of DDL statements creates an. entry in the mapping catalog only. This type may be of declarative or informative character. The declarative type is stored in the mapping catalog and used when rewriting queries. The informative is stored in the mapping catalog but not used for rewriting. The informative type is used by the administrator of the single database as information that supports the maintenance of the database.

There is a type of registers called special registers stored in the mapping catalog that provides specific information to the applications. One example is current timestamp' that returns exact timestamp when the register is used in a query. Some of these special registers are specific to a logical database.

Another type of these special registers can be updated by an application and the single database does not handle it. In general, the statements that change the value of special registers are handled by the consolidated layer and the values are mapped with the information from the mapping catalog.

The special register current server' returns the name of the database the application is connected to. As the application should only receive information regarding the logical database, this register is simulated in the consolidation layer. Each occurrence of the register is mapped with a string that includes the name of the logical database.

Some of the commands are administration commands, as backup and restore, and not SQL statements. As the administration contains the information of the mapping catalog, the administration can backup data related to a single logical database, to multiple logical databases or to the complete single database.

One of the utilities available for single databases iS auto index. In auto index all statements are received by the consolidated layer and statistical information regarding responses of queries are recorded. Another utility is automatic *rebalance but ferpool, where the administration of the database queries but ferpool and receives but ferpool definitions that are used as an input when deciding what buffer pool to create at the physical level. The administration can divide the lQgical databases in logical database classes. The administration assigns specific resources, as a specific amount of memory, to the different logical database classes. The physical buff erpool can be shared among different logical databases.

Another utility is shared data, where complete tables from the single database are shared among multiple logical databases. A further possibility is to copy logical databases as a system of its own. In lazy cloning utility, complete duplication of the mapping catalos is obtained and the duplication of data is deferred to the time when a data write occurs.

While the foregoing has been with reference to particular embodiments of the invention, it will be appreciated by those skilled in the art that changes in these embodiments may be made without departing from the principles and. spirit of the invention, the scope of which is defined by the appended claims.

List of Reference Numerals 101 I Application 102 Connectivity mechanism 103 Database 104 catalog

DBCL

106 Mapping catalog 107 Single database Database system 111 Database system Database system 201 First application 202 Second application 203 Third application 204 Consolidation layer 205 First logical database 206 Second logical database 207 Third logical database 208 Physical database 209 First segment 210 Second segment 211 Third segment 300 Database system 301 Application 302 Application 303 Application 304 Application 305 Application 306 Consolidation layer 307 Consolidation layer 308 solidation layer 309 Consolidation layer 310 Consolidation layer 311 Map cache 312 Map cache -19 - 313 Map cache 314 Map cache 315 Map cache 316 Mapping catalog 317 Single database 318 Segment 319 Segment 320 Segment 321 Segment 322 Segment 323 Segment 325 AdministratOr 329 User 401 Mapping catalog 402 Map cache 403 Map cache 404 Map cache 405 Consolidation layer 406 consolidatiOn layer 407 ConsolidatiOn layer 408 Server 409 Server 410 Server 411 Application 412 Server 413 Second server 500 Rewriting process 501 Application 502 ConsolidatiOn layer * 503 Mapping catalog 504 Rewriting function 505 Single database 506 Logical database

600 Table

601 Logical database names 602 Object types 603 Logical object name 604 Physical object name