System and method to determine database schema impact

• US 8,667,028 B2
• Filed: 09/28/2009
• Issued: 03/04/2014
• Est. Priority Date: 09/28/2009
• Status: Active Grant

• Current Assignee: AT&T Global Network Services Deutschland GmbH (AT&T, Inc.)
• Original Assignee: AT&T Global Network Services Deutschland GmbH (AT&T, Inc.)
• Inventors: Franz, Joern Amand
• Primary Examiner(s): PHILLIPS, III, ALBERT M
• Application Number:
  US12/568,460
• Publication Number:
  US 20110078113A1
• Time in Patent Office: 1,618 Days
• Field of Search: 707/803, 707/610, 707/620, 707/634, 707/809
• US Class Current: 707/803
• CPC Class Codes: G06F 16/213 with details for schema evolution support
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation

Abstract

Database management systems associate a user identifier with an application and monitor access by the application to a database replication. Any changes to a schema associated with the database replication or different instances of the database are identified based on the monitoring. Identified changes are verified and a usage parameter of the database or database replication is analyzed to determine usage data, which can be stored in a database usage repository. A determination regarding the expected impact of an identified change is determined from the usage data.

Claims

1. A method for database management, comprising:

storing, in memory, a replication of a source database associated with a schema;

assigning, by a processor, a unique database user identification to each downstream client system accessing the source database;

receiving database query statements, each database query statement identifying a software application accessing a name of a database table and the unique database user identification assigned to each downstream client system issuing each database query statement;

storing, in the memory, a dependency matrix for the replication of the source database, the dependency matrix associating the unique database user identification assigned to each downstream client system and to the name of the database table identified in each database query statement;

monitoring the dependency matrix for an impact to the software application;

identifying, based on the monitoring, a change to the schema of the replication of the source database;

verifying the change by repeatedly comparing database data for the replication of the source database to metadata for the source database; and

responsive to the verifying of the change, informing the software application of the impact due to the change to the schema of the replication of the source database.

2. The method of claim 1, further comprising comparing the impact to a threshold impact.

3. The method of claim 2, further comprising generating an alarm in response to the impact exceeding the threshold impact.

4. The method of claim 1, further comprising comparing the impact to the software application to the database query statements.

5. The method of claim 1, further comprising comparing the impact to the software application to one of the database query statements.

6. The method of claim 1, further comprising repeating the verifying verification of the change.

7. The method of claim 1, further comprising periodically repeating the verifying verification of the change.

8. The method of claim 1, further comprising associating one of the database query statements to a column of the replication of the source database.

9. The method of claim 1, further comprising parsing each database query statement to identify the software application, the name of the database table, and the unique database user identification.

10. The method of claim 1, further comprising performing network packet analysis to identify the software application, the name of the database table, and the unique database user identification.

11. The method of claim 1, further comprising source code analysis to identify the software application, the name of the database table, and the unique database user identification.

12. The method of claim 1, further comprising associating the software application to the unique database user identification.

13. A database management system, comprising:

a processor; and

memory storing instructions that, when executed by the processor, cause the processor to perform operations, the operations comprising;

synchronizing a database replication associated with a source database and associated with a schema;

assigning a unique database user identification to each downstream client system accessing the source database;

receiving database query statements, each database query statement identifying a software application accessing a name of a database table and the unique database user identification assigned to each downstream client system issuing each database query statement;

storing a dependency matrix for the database replication, the dependency matrix associating the unique database user identification assigned to each downstream client system and to the name of the database table identified in each database query statement;

monitoring the dependency matrix for an impact to the software application;

identifying, based on the monitoring, a change to the schema of the database replication;

verifying the change by repeatedly comparing database data for the database replication to metadata for the source database; and

responsive to the verifying of the change, informing the software application of the impact due to the change to the schema of the database replication.

14. The system of claim 13, wherein the operations further comprise synchronizing in substantially real-time.

15. The system of claim 13, wherein the operations further comprise comparing the impact to a threshold.

16. The system of claim 15, wherein the operations further comprise generating an alarm in response to the impact exceeding the threshold.

17. The system of claim 13, wherein the operations further comprise comparing the impact to the database query statements.

18. A memory storing instructions that, when executed by a processor, cause the processor to perform operations, the operations comprising:

storing a replication of a source database associated with a schema;

assigning a unique database user identification to each downstream client system accessing the source database;

receiving database query statements, each database query statement identifying a software application accessing a name of a database table and the unique database user identification assigned to each downstream client system issuing each database query statement;

storing a dependency matrix for the replication of the source database, the dependency matrix associating the unique database user identification assigned to each downstream client system and to the name of the database table identified in each database query statement;

monitoring the dependency matrix for an impact to the software application;

identifying, based on the monitoring, a change to the schema of the replication of the source database;

verifying the change by repeatedly comparing database data for the replication of the source database to metadata for the source database; and

responsive to the verifying of the change, informing the software application of the impact due to the change to the schema of the replication of the source database.

19. The memory according to claim 18, wherein the operations further comprise comparing the impact to a threshold.

20. The memory according to claim 19, wherein the operations further comprise generating an alarm.