System and method for the optimization of database access in data base networks

• US RE45,806 E1
• Filed: 02/24/2012
• Issued: 11/17/2015
• Est. Priority Date: 10/15/2002
• Status: Expired due to Fees

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Active-Base Ltd.
• Inventors: Boukobza, Eric
• Primary Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US13/405,176
• Time in Patent Office: 1,362 Days
• Field of Search: 707/674, 707/675, 707/676, 707/679, 707/682, 707/705, 707/713, 707/781, 709/203, 709/223, 709/238, 718/105
• US Class Current: 1/1
• CPC Class Codes: G06F 17/30002 Interfaces; Database management systems; Updating
• CPC Class Codes: G06F 17/30424 Query processing
• CPC Class Codes: G06F 17/30442 Query optimisation
• CPC Class Codes: G06F 17/30557 Details of integrating or interfacing systems involving at least one database management system
• CPC Class Codes: H04L 41/046 comprising network management agents or mobile agents therefor
• CPC Class Codes: H04L 41/0893 Assignment of logical groups to network elements

Abstract

A database network router (DNR) in a database network includes a client access module and a database resource manager that communicate with each other using a common software interface. The client access module communicates with clients including application servers and external database servers; and the database resource manager communicates with the database servers. The DNR also includes a protocol manager for handling multiple database protocols. The database network router also includes local data cache storage to store commonly used data objects, results of parsed query statement, non-optimized query statements, and optimized alternatives and assigns a priority to each client on log-in. The DNR provides a method for connections pooling, a method for balancing the load in a database networks and a method for synchronizing the data stored in cache memory with the database network and method for synchronizing the data stored in cache memory with the database to insure data reliability. The DNR also provides a method for monitoring and optimizing data queries to improve the performance of the database network. The DNR also provides a method to migrate from an old to a new version of the database.

Claims

1. A system for optimization of database access from a client device in a database network comprising:

(a) at least one client;

(b) at least one database server; and

(c) a database network router for managing data flow between said at least one client device and said at least one database server, wherein said database network router includes;

(i) a client access module for communicating configured to communicate with said at least one client device, and

(ii) a database resource manager for communicating configured to communicate with said at least one database server;

whereby said database network router may be used to optimize access to said at least one database server by said at least one client.

2. The system of claim 1, wherein said client access module and said database resource manager share a common software interface for said communicating.

3. The system of claim 2, wherein said software interface comprises a protocol manager for handling multiple database protocols.

4. The system of claim 1, further comprising:

(iii) a local data storage, wherein said database resource manager stores local data.

5. The system of claim 4, wherein said local data is chosen from the group consisting of commonly used data objects, results of parsed query statements, non-optimized query statements, alternatives for said non-optimized query statements, user defined objects and performance statistics.

6. The system of claim 1, wherein a portion of said database resource manager is installed as a database server agent.

7. A method for optimization of database access in a database network comprising the steps of:

(a) providing a database network router; and

(b) using said database network router for managing data flow between at least one client and at least one database server in the database network, wherein the step of using said database network router includes comprising the steps of;

(i) receiving, by a database network router, at least one client communication,

(ii) sending, by said database network router, said at least one client communication to said at least one database server, and

(iii) in response to said at least one client communication, said database network router receiving at least one database communication and sending said at least one database communication to said at least one client.

8. The method of claim 7, wherein said at least one client communication is selected from the group consisting of database queries and updates and wherein said at least one database communication is selected from the group consisting of database responses and data.

9. The method of claim 7, wherein the step of using said database network router further includes comprising,

(iv) prior to said receiving, said database network router assigning a priority to each said at least one client communication, and

(v) performing (i)-(iii) according to said priority.

10. The method of claim 7, wherein the step of using said database network router 9, further includes comprising, prior to said assigning:

(vi) identifying, by said database network router, at least one client device from which said at least one client communication is received, and

(vii) receiving, by said database network router, status information selected from the group consisting of a load of said at least one database server, a time of day of said assigning, a day of week of said assigning, a service level of said at least one client device, and a status of at least one communications line between said at least one client device and said at least one database server.

11. The method of claim 7, wherein said at least one client communication includes a query and wherein the step of using said database network router further includes method further comprises, prior to said sending, at least one action selected from the group consisting of analyzing said query, modifying said query, checking a cost estimate of said query, parsing said query and storing said query in a local storage.

12. The method of claim 11, wherein said modifying of said query includes blocking of said query, and wherein said at least one database communication includes an error message.

13. The method of claim 7, wherein the step of using said database network router includes further comprising:

(i) receiving, by said database network router, a log-out and disconnect request from a first client device that is initially connected to one of said at least one database server by a database connection, and

(ii) reporting, by said database network router, said log-out to said one database server while leaving said database connection available for a second client.

14. The method of claim 13, wherein the step of using said database network router further includes comprising:

(iii) receiving, by said database network router, a log-in and connection request from said second client;

(iv) reporting, by said database network router, said log-in to said database server; and

(v) using, by said database network router, said database connection for said second client device.

15. The method of claim 14, wherein the step of using said database network router further includes:

comprising, prior to said reporting;

(vi) responding, by said database network router, to said first client log-out and disconnect request with a message confirming log-out and disconnect.

16. The method of claim 14, wherein the step of using said database network router includes 7, further comprising:

(i) sending, by said database network router, at least one query to one of the database servers; and

(ii) monitoring, by said database network router, a processing of said at least one query by said one database server, said monitoring including receiving information, regarding said at least one query, selected from the group consisting of a CPU time of said database server, a response time of said database server, a read/write load of said database server and an execution load of said database server.

17. The method of claim 16, wherein the step of using said database network router further includes comprising:

(iii) subsequent to said receiving, said database network router storing said queries in a log of popular non-optimized queries.

18. The method of claim 17, wherein the step of using said database network router further includes comprising:

(iv) reading, by said database network router, said queries from said log.

19. The method of claim 18, wherein the step of using said database network router further includes comprising:

(v) said database network router analyzing and testing said queries.

20. The method of claim 19, wherein the step of using said database network router further includes comprising:

(vi) said database network router building optimized alternatives for said queries.

21. A method of load balancing in a database network that includes a plurality of database servers, comprising the steps of:

(a) providing a database network router

(b) detecting, by said database network router, a failed one of the database servers; and

(c) using said database network router for transferring to transfer connections of said failed database server to at least one other server, wherein the step of using said database network router includes;

(i) monitoring said failed database server for a recovery thereof, and

(ii) transferring at least one new connection to said failed database after said recovery.

22. A system for optimization of database access from a client device in a database network comprising:

at least one database server; and

a database network router for managing data flow between at least one client device and said at least one database server, wherein said database network router includes;

(i) a client access module configured to communicate with at least one client device,

(ii) a database resource manager configured to communicate with said at least one database server, and

(iii) a local data storage, wherein said database resource manager stores local data and wherein said local data is chosen from the group consisting of commonly used data objects, results of parsed query statements, non-optimized query statements, alternatives for said non-optimized query statements, user defined objects, and performance statistics;

whereby said database network router may be used to optimize access to said at least one database server by said at least one client.

23. A method for managing data flow between at least one client and at least one database server in the database network comprising the steps of:

(i) receiving, by a database network router, at least one client communication,

(ii) sending, by said database network router, said at least one client communication to said at least one database server,

(iii) in response to said at least one client communication, said database network router receiving at least one database communication and sending said at least one database communication to said at least one client,

(iv) prior to said receiving, said database network router assigning a priority to each said at least one client communication, and

(v) performing (i)-(iii) according to said priority.

24. A method for managing data flow between at least one client and at least one database server in the database network comprising the steps of:

(i) receiving, by a database network router, at least one client communication,

(ii) sending, by said database network router, said at least one client communication to said at least one database server,

(iii) in response to said at least one client communication, said database network router receiving at least one database communication and sending said at least one database communication to said at least one client,

(iv) receiving, by said database network router, a log-out and disconnect request from a first client device that is initially connected to one of said at least one database server by a database connection, and

(v) reporting, by said database network router, said log-out to said one database server while leaving said database connection available for a second client.