Database storage reclaiming program

• US 9,588,999 B2
• Filed: 04/27/2015
• Issued: 03/07/2017
• Est. Priority Date: 04/27/2015
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chirayath, Vinod P., Gong, Gary T., Ledezma, Salvador
• Primary Examiner(s): Hoang, Son T
• Application Number:
  US14/696,639
• Publication Number:
  US 20160314148A1
• Time in Patent Office: 680 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 12/0253 Garbage collection, i.e. reclamation of unreferenced memory
• CPC Class Codes: G06F 16/1727 Details of free space management performed by the file system saving storage space on storage systems G06F3/0608; management of blocks in storage devices G06F3/064
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 2212/163 Server or database system
• CPC Class Codes: G06F 2212/7205 Cleaning, compaction, garbage collection, erase control

Abstract

The computer retrieves a list of the data elements contained in a database. The computer performs a static and dynamic analysis on the list to determine which data elements have been active in a static or dynamic Structured Query Language (SQL) statement, then removes active data elements from the list. The computer performs a dependency test to determine which data elements on the list are depended on by data objects not on the list and removes those data elements which are depended on. The computer analyzes application code to extract SQL statements and determine whether data elements on the list are active in application code SQL. Any data elements on the list which are active in application code SQL are removed from the list and the remaining data elements on the list are sorted in descending order based on size.

Claims

1. A method for reclaiming database storage, the method comprising:

retrieving, by a computer, a list of data elements for deletion from a database catalog, wherein the list of data elements for deletion details one or more data elements contained in a database;

determining, by the computer, whether first data elements of the one or more data elements on the list of data elements for deletion have been active in one or more static Structured Query Language (SQL) statements, wherein the one or more static SQL statements are persistent and created before runtime;

removing, by the computer, the first data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more static SQL statements;

determining, by the computer, whether second data elements of the one or more data elements on the list of data elements for deletion have been active in one or more dynamic SQL statements, wherein the one or more dynamic SQL statements are non-persistent and created at runtime;

removing, by the computer, the second data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more dynamic SQL statements;

determining, by the computer, whether third data elements of the one or more data elements on the list of data elements for deletion are associated with one or more data elements not on the list of data elements for deletion;

removing, by the computer, the third data elements of the one or more data elements from the list of data elements for deletion that are determined to be associated with the one or more data elements not on the list of data elements for deletion;

determining, by the computer, whether fourth data elements of the one or more data elements on the list of data elements for deletion are used in a source code of one or more applications; and

removing, by the computer, the fourth data elements of the one or more data elements from the list of data elements for deletion that are determined to be used in the source code of the one or more applications.

2. The method of claim 1, wherein a database management system associated with the database registers usage of one or more data elements in the one or more static SQL statements.

3. The method of claim 2, wherein the determining whether the first data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more static SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with the one or more registered data elements.

4. The method of claim 1, wherein the determining whether the second data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more dynamic SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with one or more capture files, and wherein the capture files detail where and when the one or more data elements on the list of data elements for deletion are active in the one or more dynamic SQL statements.

5. The method of claim 1, wherein the determining whether the third data elements of the one or more data elements on the list of data elements for deletion are associated with the one or more data elements not on the list of data elements for deletion includes cross referencing the one or more data elements on the list of candidates for deletion with the database catalog, wherein the database catalog details relationships of the one or more data elements contained in the database.

6. The method of claim 1, wherein the determining whether the fourth data elements of the one or more data elements on the list of data elements for deletion are used in the source code of the one or more applications includes analyzing one or more application source codes and workload logic in order to extract the one or more SQL statements and cross reference the extracted one or more SQL statements with the one or more data elements on the list of data elements for deletion.

7. The method of claim 1, further comprising:

sorting, by the computer, an updated list of data elements for deletion in descending order based on corresponding size of the one or more data elements, wherein the updated list of data elements for deletion includes the one or more data elements of the list of data elements for deletion and excludes any removed data elements of the one or more data elements.

8. A computer program product for a database storage reclaiming program, the computer program product comprising:

one or more computer-readable storage media and program instructions stored on the one or more computer-readable storage media, the program instructions comprising;

program instructions to retrieve a list of data elements for deletion from a database catalog, wherein the list of data elements for deletion details one or more data elements contained in a database;

program instructions to determine whether first data elements of the one or more data elements on the list of data elements for deletion have been active in one or more static Structured Query Language (SQL) statements, wherein the one or more SQL statements are persistent and created before runtime;

program instructions to remove the first data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more static SQL statements;

program instructions to determine whether second data elements of the one or more data elements on the list of data elements for deletion have been active in one or more dynamic SQL statements, wherein the one or more dynamic SQL statements are non-persistent and created at runtime;

program instructions to remove the second data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more dynamic SQL statements;

program instructions to determine whether third data elements of the one or more data elements on the list of data elements for deletion are associated with one or more data elements not on the list of data elements for deletion;

program instructions to remove the third data elements of the one or more data elements from the list of data elements for deletion that are determined to be associated with the one or more data elements not on the list of data elements for deletion;

program instructions to determine whether fourth data elements of the one or more data elements on the list of data elements for deletion are used in a source code of one or more applications; and

program instructions to remove the fourth data elements of the one or more data elements from the list of data elements for deletion that are determined to be used in the source code of the one or more applications.

9. The computer program product of claim 8, wherein a database management system associated with the database registers usage of the one or more data elements in the one or more static SQL statements.

10. The computer program product of claim 9, wherein the determining whether the first data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more static SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with the one or more registered data elements.

11. The computer program product of claim 8, wherein the determining whether the second data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more dynamic SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with one or more capture files, wherein the one or more capture files detail where and when the one or more data elements on the list of data elements for deletion are active in the one or more dynamic SQL statements.

12. The computer program product of claim 8, wherein the determining whether the third data elements of the one or more data elements on the list of data elements for deletion are associated with the one or more data elements not on the list of data elements for deletion includes cross referencing the one or more data elements on the list of candidates for deletion with the database catalog, wherein the database catalog details relationships of the one or more data elements contained in the database.

13. The computer program product of claim 8, wherein the determining whether the fourth data elements of the one or more data elements on the list of data elements for deletion are used in the source code of the one or more applications includes analyzing one or more application source codes and workload logic in order to extract the one or more SQL statements and cross reference the extracted one or more SQL statements with the one or more data elements on the list of data elements for deletion.

14. The computer program product of claim 8, further comprising:

program instructions to sort an updated list of data elements for deletion in descending order based on corresponding size of the one or more data elements, wherein the updated list of data elements for deletion includes the one or more data elements of the list of data elements for deletion and excludes any removed data elements of the one or more data elements.

15. A computer system for a database storage reclaiming program, the computer system comprising:

one or more computer processors, one or more computer-readable storage media, and program instructions stored on one or more of the computer-readable storage media for execution by at least one of the one or more processors, the program instructions comprising;

program instructions to retrieve a list of data elements for deletion from a database catalog, wherein the list of data elements for deletion details one or more data elements contained in a database;

program instructions to determine whether first data elements of the one or more data elements on the list of data elements for deletion have been active in one or more static Structured Query Language (SQL) statements, wherein the one or more static SQL statements are persistent and created before runtime;

program instructions to remove the first data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more static SQL statements;

program instructions to determine whether second data elements of the one or more data elements on the list of data elements for deletion have been active in one or more dynamic SQL statements, wherein the one or more dynamic SQL statements are non-persistent and created at runtime;

program instructions to remove the second data elements of the one or more data elements from the list of data elements for deletion that have been determined to be active in the one or more dynamic SQL statements;

program instructions to determine whether third data elements of the one or more data elements on the list of data elements for deletion are associated with one or more data elements not on the list of data elements for deletion;

program instructions to remove the third data elements of the one or more data elements from the list of data elements for deletion that are determined to be associated with the one or more data elements not on the list of data elements for deletion;

program instructions to determine whether fourth data elements of the one or more data elements on the list of data elements for deletion are used in a source code of one or more applications; and

program instructions to remove the fourth data elements of the one or more data elements from the list of data elements for deletion that are determined to be used in the source code of the one or more applications.

16. The computer system of claim 15, wherein a database management system associated with the database registers usage of the one or more data elements in the one or more static SQL statements.

17. The computer system of claim 16, wherein the determining whether the first data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more static SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with the one or more registered data elements.

18. The computer system of claim 15, wherein the determining whether the second data elements of the one or more data elements on the list of data elements for deletion have been active in the one or more dynamic SQL statements includes cross referencing the one or more data elements on the list of data elements for deletion with one or more capture files, wherein the one or more capture files detail where and when the one or more data elements on the list of data elements for deletion are active in the one or more dynamic SQL statements.

19. The computer system of claim 15, wherein the determining whether the third data elements of the one or more data elements on the list of data elements for deletion are associated with the one or more data elements not on the list of data elements for deletion includes cross referencing the one or more data elements on the list of candidates for deletion with the database catalog, wherein the database catalog details relationships of the one or more data elements contained in the database.

20. The computer system of claim 15, wherein the determining whether the fourth data elements of the one or more data elements on the list of data elements for deletion are used in the source code of the one or more applications includes analyzing one or more application source codes and workload logic in order to extract the one or more SQL statements and cross reference the extracted one or more SQL statements with the one or more data elements on the list of data elements for deletion.