Identifying database triggers

• US 9,053,137 B2
• Filed: 10/14/2008
• Issued: 06/09/2015
• Est. Priority Date: 03/23/2006
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Medicke, John A., Russell, Feng-wei Chen, Smith, Michael William, Tan, Ray Zhong
• Primary Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US12/251,292
• Publication Number:
  US 20090037460A1
• Time in Patent Office: 2,429 Days
• Field of Search: 707/702, 707/802, 707/806, 709/201, 709/313
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A system to identify database triggers in a data processing system. A request is received to create a database monitor for a specific application event and in response to receiving the request to create the database monitor, the database monitor is created. Then, a request is received to monitor the specific application event within a database and in response to receiving the request to monitor the specific application event, a replicate database of the database is created. Subsequently, the database is compared to the replicate database after the specific application event occurs to identify changes in the database associated with the specific application event. A best candidate is identified for a database trigger based upon the identified changes in the database associated with the specific application event.

Claims

1. A computer-implemented method to identify a database trigger in a data processing system, comprising:

creating a database monitor for a specific application event responsive to receiving a request to create the database monitor for the specific application event;

responsive to receiving a request to monitor the specific application event within a database, creating a replicate database of the database;

identifying changes in the database associated with the specific application event by comparing, after the specific application event, the database to the replicate database; and

identifying a best candidate for a database trigger based upon the identified changes in the database associated with the specific application event.

2. The method of claim 1, wherein

the comparing is performed using an intelligent algorithm invoked by a database comparator.

3. The method of claim 1, wherein

the identified changes are changes in

a column and

a value within an affected table.

4. A data processing hardware system configured to identify a data base trigger, comprising:

a processor, wherein

the processor is configured to perform;

creating a database monitor for a specific application event responsive to receiving a request to create the database monitor for the specific application event;

responsive to receiving a request to monitor the specific application event within a database, creating a replicate database of the database;

identifying changes in the database associated with the specific application event by comparing, after the specific application event, the database to the replicate database; and

identifying a best candidate for a database trigger based upon the identified changes in the database associated with the specific application event.

5. The data processing hardware system of claim 4, wherein

the processor includes a database comparator configured to perform the comparing using an intelligent algorithm.

6. The data processing hardware system of claim 4, wherein

the identified changes are changes in

a column and

a value within an affected table.

7. The data processing hardware system of claim 4, wherein the processor is further configured to perform:

monitoring the data processing system for the specific application event;

recording the identified changes in the database associated with the specific application event;

identifying each table, column, and value affected by the specific application event;

listing recommended database triggers for the specific application event in a table;

creating a user-defined function stub for the recommended database triggers; and

performing a statistical analysis of the recorded identified changes in the database associated with the specific application event.

8. The data processing hardware system of claim 7, wherein the recording step is performed by a trigger information repository.

9. The data processing hardware system of claim 7, wherein

the processor includes a database analyzer configured to perform the listing and the creating, and

the database analyzer invokes an intelligent algorithm to list the recommended database triggers and create the user defined function stubs.

10. The data processing hardware system of claim 7, wherein

the processor includes a database monitor configured to perform the monitoring using an intelligent algorithm.

11. The data processing hardware system of claim 4, wherein

the specific application event is at least one of an insert, a delete, or an update structured query language statement.

12. The data processing hardware system of claim 4, wherein the processor is further configured to perform:

identifying all foreign keys in tables affected by the specific application event, and

the foreign keys identify all other tables associated with the specific application event.

13. The data processing hardware system of claim 12, wherein

the identified all other tables are used to identify other trigger candidates.

14. A computer program product comprising a computer usable storage medium having stored therein computer usable program code for identifying a database trigger in a data processing hardware system, the computer usable program code, which when executed by the data processing hardware system, causes the data processing hardware system to perform:

creating a database monitor for a specific application event responsive to receiving a request to create the database monitor for the specific application event;

responsive to receiving a request to monitor the specific application event within a database, creating a replicate database of the database;

identifying changes in the database associated with the specific application event by comparing, after the specific application event, the database to the replicate database; and

identifying a best candidate for a database trigger based upon the identified changes in the database associated with the specific application event.

15. The computer program product of claim 14, wherein

the comparing is performed using an intelligent algorithm invoked by a database comparator.

16. The computer program product of claim 14, wherein

the identified changes are changes in

a column and

a value within an affected table.