Managing a computerized database using a volatile database table attribute

• US 10,216,741 B2
• Filed: 12/23/2014
• Issued: 02/26/2019
• Est. Priority Date: 03/26/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Konik, Rafal P., Mittelstadt, Roger A., Muras, Brian R., Theuer, Mark W.
• Primary Examiner(s): Burke, Jeff A
• Assistant Examiner(s): Vu, Thong H
• Application Number:
  US14/582,167
• Publication Number:
  US 20160171033A1
• Time in Patent Office: 1,526 Days
• CPC Class Codes: G06F 16/116 Details of conversion of file system types or formats
• CPC Class Codes: G06F 16/119 Details of migration of file systems migration mechanisms in storage systems G06F3/0647
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A respective volatility attribute associated with each of one or more tables of a computerized database is used in any of various aspects to (a) determine how table data is stored in a physical storage device; (b) regulate the use of a materialized query table using database table data; and/or (c) influence circumstances under which indexes are created or advised by database analytic software. Various optional additional uses of a volatility attribute to manage a database are disclosed. Preferably, database parameters are automatically monitored over time and database table volatility state is automatically determined and periodically adjusted.

Claims

1. A non-transitory computer-readable media having stored thereon an executable computer program for managing a computerized database, executable computer program instructs at least one computer system to perform actions comprising:

storing at least one respective volatility attribute for each of at least one database table of said computerized database, each volatility attribute expressing volatility of at least a portion of a respective database table of said computerized database, said volatility of at least a portion of a respective database table being a property of the respective database table that is a function of changes to data recorded in said at least a portion of the respective database table with respect to time;

selectively determining whether to maintain at least one metadata structure, each metadata structure of the at least one metadata structure containing respective metadata describing data in at least a first database table of said at least one database table, the determining whether to maintain at least one metadata structure being performed using at least one volatility attribute expressing volatility of at least a portion of the first database table, wherein maintaining the at least one metadata structure comprises updating the metadata in the at least one metadata structure responsive to changes to the data in the at least a first database table; and

responsive to determining to maintain a first metadata structure of the at least one metadata structure, maintaining the first metadata structure, wherein maintaining the first metadata structure comprises updating the metadata in the first metadata structure responsive to changes to the data in the at least a first database table.

2. The non-transitory computer readable media of claim 1, wherein said at least one metadata structure comprises a materialized query table.

3. The non-transitory computer readable media of claim 1, wherein said at least one metadata structure comprises an index.

4. The non-transitory computer readable media of claim 1, wherein the instructions further cause the at least one computer system to perform:

monitoring at least one respective parameter of each said database table of said computerized database over at least one time interval and saving monitored parameter data with respect to the respective database table; and

determining a respective volatility state of each said database table using the saved monitored parameter data to automatically generate said at least one respective volatility attribute for each said database table.

5. The non-transitory computer readable media of claim 4, wherein the instructions further cause the at least one computer system to perform:

computing a respective figure of merit representing volatility of the at least a portion of the respective database table for each of said at least one time interval.

6. The non-transitory computer readable media of claim 1, wherein said volatility attribute comprises a quantitative value.

7. A computer system supporting a database, comprising:

a memory;

at least one processor executing instructions contained in said memory;

a database manager embodied as instructions stored in non-transitory media of said computer system and executable on said at least one processor, said database manager managing said database and providing access to data in at least one database table of said database;

wherein said database manager stores at least one respective volatility attribute for each said at least one database table, each volatility attribute expressing volatility of at least a portion of the respective database table, each said volatility of at least a portion of a respective database table being a property of the respective database table that is a function of changes to data recorded in the at least a portion of the respective database table with respect to time;

wherein said database manager further selectively determines whether to maintain at least one metadata structure, each metadata structure of the at least one metadata structure containing respective metadata describing data in at least a first database table of said at least one database table, the determining whether to maintain at least one metadata structure being performed using at least one volatility attribute expressing volatility of at least a portion of the first database table, wherein maintaining the at least one metadata structure comprises updating the metadata in the at least one metadata structure responsive to changes to the data in the at least a first database table; and

wherein, responsive to determining to maintain a first metadata structure of the at least one metadata structure, said database manager further maintains the first metadata structure by updating the metadata in the first metadata structure responsive to changes to the data in the at least a first database table.

8. The computer system of claim 7, wherein said at least one metadata structure comprises a materialized query table.

9. The computer system of claim 7, wherein said at least one metadata structure comprises an index.

10. The computer system of claim 7, wherein said database manager further:

monitors at least one respective parameter of each said database table of said database over at least one time interval and saves monitored parameter data with respect to the respective database table; and

determines a respective volatility state of each said database table using the saved monitored parameter data to automatically generate said at least one respective volatility attribute for each said database table.

11. The computer system of claim 10, wherein said database manager further computes a respective figure of merit representing volatility of the at least a portion of the respective database table for each of said at least one time interval.

12. The computer system of claim 7, wherein each said volatility attribute comprises a respective quantitative value.