Managing a Computerized Database Using a Volatile Database Table Attribute

Patent metadata

Field	Value
Publication number	US-2016171032-A1
Application number	US-201414566248-A
Country	US
Kind code	A1
Filing date	Dec 10, 2014
Priority date	Mar 26, 2014
Publication date	Jun 16, 2016
Grant date	—

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.

First claim

What is claimed is:

1 . A method for managing a computerized database, comprising the following executed by at least one computer system:
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; selecting a respective storage device from among a plurality of available storage devices for storing each at least a portion of a database table using the volatility attribute associated with respective at least a portion of a database table.

2 . The method for managing a computerized database of claim 1 , further comprising:
migrating at least one file from a first storage device of said plurality of available storage devices to a second storage device of said plurality of available storage devices, wherein the file being migrated is selected from among multiple files stored on the first storage device using a volatility attribute associated with the file.

3 . The method for managing a computerized database of claim 2 , further comprising:
determining, for each of a plurality of files stored in said first storage device, a respective priority for migration from said first storage device to said second storage device, wherein the respective priority is determine using a respective quantitative value expressing volatility data in the respective file.

4 . The method for managing a computerized database of claim 1 , further comprising:
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 method for managing a computerized database of claim 3 , further comprising:
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 method for managing a computerized database of claim 1 , wherein said computerized database supports the SQL database language and said at least volatility attribute comprises an SQL VOLATILE attribute.

7 . The method for managing a computerized database of claim 1 ,
wherein said plurality of available storage devices include at least one storage device of a first type and at least one storage device of a second type, a storage device of said first type being less suitable for storing data of a volatile database table than a storage device of said second type; and wherein said selecting a respective storage device from among a plurality of available storage devices for storing each at least a portion of a database table using the volatility attribute associated with respective at least a portion of a database table preferentially selects a storage device of the second type for storing data of a volatile database table.

8 . The method for managing a computerized database of claim 1 , wherein said selecting a respective storage device from among a plurality of available storage devices for storing each at least a portion of a database table using the volatility attribute associated with respective at least a portion of a database table selects a respective storage device to equally distribute volatile database table data among the plurality of available storage devices.

9 . The method for managing a computerized database of claim 1 , further comprising:
with respect to each of one or more at least a portion of a database table of said at least one database table of said computerized database, receiving a user input specifying whether a storage device for storing the respective at least a portion of a database table is to be manually specified or automatically determined by said at least one computer system; and wherein selecting a respective storage device from among a plurality of available storage devices for storing each at least a portion of a database table using the volatility attribute associated with respective at least a portion of a database table is performed only if said user input specifies that the storage device for storing the respective at least a portion of a database table is to be automatically determined.

10 . The method for managing a computerized database of claim 1 , wherein said volatility attribute comprises a quantitative value.

11 - 14 . (canceled)

15 . A method for managing a computerized database, comprising the following executed by at least one computer system:
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; selectively maintaining at least one metadata structure describing data in at least a first database table of said at least one database table using at least one volatility attribute associated with the first database table.

16 . The method for managing a computerized database of claim 15 , wherein said at least one metadata structure comprises a materialized query table.

17 . The method for managing a computerized database of claim 15 , wherein said at least one metadata structure comprises an index.

18 . The method for managing a computerized database of claim 15 , further comprising:
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.

19 . The method for managing a computerized database of claim 18 , further comprising:
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.

20 . The method for managing a computerized database of claim 15 , wherein said volatility attribute comprises a quantitative value.

Assignees

• IBM

Inventors

• Konik Rafal P
• Mittelstadt Roger A
• Muras Brian R
• Theuer Mark W

Classifications

• G06F17/30079

  Physics · mapped topic

• G06F17/30091

  Physics · mapped topic

• G06F17/30339 Primary

  Physics · mapped topic

• G06F17/30383

  Physics · mapped topic

• G06F16/13

  File access structures, e.g. distributed indices (arrangements of input from, or output to, record carriers G06F3/06) · CPC title