Database storage reclaiming program

• US 9,558,219 B2
• Filed: 07/15/2016
• Issued: 01/31/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:
  US15/211,168
• Publication Number:
  US 20160314150A1
• Time in Patent Office: 200 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 one or more data elements contained in a database;

detecting, by the computer, utilization of a first data element on the list of one or more data elements in a static Structured Query Language (SQL) statement, wherein static SQL statements are persistent and being created before runtime;

based on the detecting utilization of the first data element on the list of one or more data elements in the static SQL statement, creating, by the computer, a record associated with the first data element detailing a time at which the first data element was utilized in the static SQL statement;

detecting, by the computer, utilization of a second data element on the list of one or more data elements in a dynamic SQL statement, wherein dynamic SQL statements are non-persistent and being created at the runtime;

based on the detecting utilization of the second data element on the list of one or more data elements in the dynamic SQL statement, creating, by the computer, a capture file associated with the second data element detailing a time at which the second data element was utilized in the dynamic SQL statement;

receiving, by the computer, a retroactive time period from a user, wherein the retroactive time period comprises a starting point of time in the past;

determining, by the computer, whether the first data element has been utilized in the static SQL statement within the retroactive time period by comparing the time detailed by the record with the retroactive time period;

based on determining that the first data element has been utilized in the static SQL statement within the retroactive time period, removing, by the computer, the first data element from the list of one or more data elements;

determining, by the computer, whether the second data element has been utilized in the dynamic SQL statement within the retroactive time period by comparing the time detailed by the capture file with the retroactive time period;

based on determining that the second data element has been utilized in the dynamic SQL statement within the retroactive time period, removing, by the computer, the second data element from the list of one or more data elements;

receiving, by the computer, a list of one or more applications from the user;

analyzing, by the computer, application code associated with each of the one or more applications to extract one or more application code SQL statements;

determining whether a third data element on the list of one or more data elements has been utilized in the extracted one or more application code SQL statements within the retroactive time period;

based on determining that the third data element on the list of one or more data elements has been utilized in the one or more application code SQL statements within the retroactive time period, removing the third data element from the list of one or more data elements.