System and method for analyzing available space in data blocks

A system and method is provided for determining an available space in one or more data blocks. A physical data storage area associated with one or more portions of a database may be determined. An available space in each data block of one or more data blocks in the physical data storage area may be determined. A number of new data rows that can be added to each data block may also be determined.

TECHNICAL FIELD

The present disclosure relates to the field of relational database systems and products. More specifically, the present disclosure relates to determining available space in data blocks.

BACKGROUND

In file structures such as sequential tape or sequential disk, data rows are added into a data block until it is full, then the next data block is accessed and so on. A data block is considered “full” when data rows no longer will fit in the data block, is considered “partially full” when at least one data row will fit in the data block, and is considered “empty” when there are no data rows in the data block. As such, an estimated percentage of fullness of a data area is computed as (“full data blocks”+“partial data blocks”)/“total data blocks”. Because data rows are sequentially filled in such file structures, there should be only one partial block at any given time. The estimated percentage is therefore a close approximation of actual fullness or capacity.

However, various operations on databases may decrease the accuracy of the estimated percentage of fullness. For example, relational database management systems provide various data space reuse options that allow data rows to be deleted from data blocks so that the space created from the deleted rows may be reused at a later time. In these cases, there may be a number of partial blocks instead of only one. As such, the above formula provides the percentage of data blocks in use (i.e., data blocks with at least one data row in them) and not the overall fullness of the data area (i.e., the physical area containing one or more data blocks for a database table or groups of tables), thereby decreasing the accuracy of the estimated percentage.

In addition, relational database management systems may support various data row compression methods. These methods compress data rows before storing them in fixed length data blocks. The amount and size of data rows that are stored on a data block can widely vary. When adding this complexity in with the various reuse options, the formula described above becomes an increasingly less accurate calculation to determine fullness of the data area.

Furthermore, data rows may have been deleted but the deletions may not have been committed. For these data rows, the space in the data block is “reserved” until the transaction is completed. These data rows are considered logically deleted but physically present.

Thus, what is needed is an efficient and reliable way to determine how full a data area is and to determine an available space in a data area.

These and other drawbacks exist.

BRIEF SUMMARY

Various systems, computer program products, and methods for determining available space in one or more data blocks are described herein.

According to one aspect of the present disclosure, the method may include a plurality of operations. In some implementations, the operations may include receiving a request to determine an available space associated with a portion of a database. In some implementations, the operations may include determining a physical data storage area associated with the portion of a database in response to the request, wherein the physical data storage area comprises a plurality of data blocks that store data from the portion of the database. In some implementations, the operations may include determining an available space in each data block of the plurality of data blocks in the selected physical data storage area. In some implementations, the operations may include determining the available space associated with the portion of the database based on the available space in each data block.

DETAILED DESCRIPTION

FIG. 1is block diagram illustrating a database management system100, according to an aspect of the present disclosure. Database management system100may include, among other things, at least a database management server112that is configured to manage one or more relational databases and/or indexes for the relational databases; and an application server142that is configured to manage and run one or more applications. Database management server112and application server142may be communicatively coupled to one or more data storage access devices (DASD)120that may store/maintain one or more database tables associated with relational database(s), store/maintain one or more indexes for the tables in the database(s), and/or other data structures. In some implementations, database management server112and application server142may be communicatively coupled to a storage device130that stores at least one control file. In some implementations, database management server112and application server142may be communicatively coupled to a client device110(illustrated inFIG. 1as a plurality of client devices110A, . . . ,110N). Database management server112and application server142may be coupled to client device110via a network150. Network150may include a Local Area Network, a Wide Area Network, a cellular communications network, a Public Switched Telephone Network, and/or other network or combination of networks.

In some implementations, one or more data rows associated with at least one database table/group of database tables may be stored in one or more data blocks. The one or more data blocks may be stored in a database entity called a data area. In other words, the data area (hereinafter referred to as “physical data storage area”) may provide physical storage for data blocks associated with the at least one database table/group of database tables. In some implementations, the physical data storage area may correspond to a data set associated with an operating system, such as, z/OS operating system, z/VSE (Virtual Storage Extended) operating system, and/or other operating system as would be appreciated. In some implementations, DASD120may house the physical data storage area/data set.

In some implementations, when a physical data storage area is created, a data block length may be specified. The length of the data block may be limited by the geometry of DASD120housing the data set. When the physical data storage area is initialized (i.e., made ready to use), one or more formatted data blocks of the specified size may be stored therein.

In some implementations, database management server112may include a processor114, a memory116, and/or other components that facilitate the functions of database management server112. In some implementations, processor114includes one or more processors configured to perform various functions of database management server112. In some implementations, memory116includes one or more tangible (i.e., non-transitory) computer readable media. Memory116may include one or more instructions that when executed by processor114configure processor114to perform functions of database management server112. In some implementations, memory116may include one or more instructions stored on tangible computer readable media that when executed at a remote device, such as client device110, cause the remote device to view information associated with one or more databases, as described herein.

In some implementations, application server142may include a processor144, a memory146, and/or other components that facilitate the functions of application server142. In some implementations, processor144includes one or more processors configured to perform various functions of application server142. In some implementations, memory146includes one or more tangible (i.e., non-transitory) computer readable media. Memory146may include one or more instructions that when executed by processor144configure processor144to perform functions of application server142. In some implementations, memory146may include one or more instructions stored on tangible computer readable media that when executed at a remote device, such as client device110, cause the remote device to display at least one report, as described herein.

Database administrators (or other users) may interact with the database management server112via client device110. In some implementations, client device110may include a computing/processing device such as a desktop computer, a laptop computer, a network computer, a wireless phone, a personal digital assistant, a tablet computing device, workstation, and/or other computing devices that may be utilized to interact with database management server112. In some implementations, client device110may comprise a user interface (not otherwise illustrated inFIG. 1) that may enable users to perform various operations that may facilitate interaction with database management server112including, for example, providing requests to retrieve information from database tables associated with a database, creating tables, adding/deleting/updating rows/columns in database tables, creating/deleting/updating/accessing one or more indexes associated with the database tables, and/or performing other operations. Client device110may include a processor (not otherwise illustrated inFIG. 1), circuitry, and/or other hardware operable to execute computer-readable instructions.

In some implementations of the present disclosure, a user may interact with application server142via client device110. In some implementations, client device110may comprise a user interface (not otherwise illustrated inFIG. 1) that may allows the user to perform various operations that may facilitate interaction with application server142including, for example, providing requests for determining available space in one or more data blocks, providing requests to generate one or more reports associated with database tables/data areas, receiving one or more reports associated with the database tables/data areas and displaying the reports, and/or performing other operations.

In some implementations, database management server112may manage various operations performed on the relational database(s) stored in DASD120and/or one or more database tables in the relational database(s). For example, database management server112may receive requests (for example, user requests, and/or other requests) to create table(s), add row(s)/column(s), delete row(s)/column(s), update row(s)/column(s), retrieve information from row(s)/column(s), and/or other requests. Database management server112may convert the requests to queries that may be run against the relational database(s) and may accordingly create one or more tables in the database(s), add one or more rows/columns to the tables in the database(s), delete one or more rows/columns from the tables in database(s), update one or more rows/columns in the tables in the database(s), retrieve requested information from the tables in the database(s), and/or perform other operations. In order to retrieve requested information from the database, database management server112may access one or more tables specified by the query (and/or request) to determine the information within the tables that matches a given request criteria specified in the query. Database management server112may then retrieve the determined information from the tables and provide it to the user.

In some implementations, database management server112may manage creation, deletion, updating, access, and/or other operations associated with one or more indexes for the tables in the relational database(s). Database management server112may create one or more indexes on one or more columns of one or more tables. An index entry may refer to one entry or index value that is in the index and references a given data row(s) of the database table. An index entry may consist of the value(s) contained in the column(s) being indexed for a given data row, and a pointer to the data row. The data row pointer may consist of a data block number/id within which the data row resides, and the unique row id. Most access requests within the relational database environment are based on index access. Index access typically refers to the pre-definition of a specific access path that is created using the value(s) of data column(s). Once created, the database can quickly retrieve data rows that have an index entry (data column value) that matches a given request criteria. In some implementations, the indices may be stored in a physical area in the DASD120.

When a data row is added to an indexed table, database management server112may create an index entry in the index associated with the indexed table using the data value of the indexed column(s). Similarly, when a data row is deleted from or updated in an indexed table, database management server112may delete the appropriate index entry from the index or update the appropriate index entry in the index (for example, if the indexed column data value is changed/updated). When a request to retrieve particular information from a table in a database is received by database management server112(for example, in the form of search queries specifying an indexed column), database management server112may perform an index access to determine one or more index entries that include data values associated with the indexed column and/or that satisfy the request. Database management server112may identify data row pointers from the determined index entries that identify or serve as pointers to a specific data row(s) stored in DASD120. Database management server112may accordingly retrieve one or more data rows associated with the data row pointers from DASD120.

In some implementations, database management server112may manage the placement/storage of a plurality of data rows in at least one physical data storage area (i.e., in one or more data blocks of the physical data storage area) in DASD120. In some implementations, database management server112may access one or more data blocks to add, delete, and/or update one or more data rows.

In some implementations, database management server112may manage a control file associated with at least one database. In some implementations, the control file may identify the location of one or more physical data storage areas in DASD120. In some implementations, the control file may store one or more physical attributes associated with the one or more physical data storage areas. In some implementations, the physical attributes may be used to locate the physical data storage areas in DASD120. In some implementations, the one or more physical attributes may include, among other things, a physical data storage area name, a dataset name, volume identifier(s), number of tracks in the physical data storage area, block size, and/or other attributes.

In some implementations,FIG. 2depicts an exemplary entry in a control file. The control file identifies a physical data storage area (e.g., “ABC”). In some implementations of the present disclosure, as illustrated inFIG. 2, the control file identifies: i) the dataset associated with the physical data storage area by dataset name (e.g., “PROD.ABC”); ii) the volume(s) associated with the physical data storage area (e.g., “DCMSP1” and “DCMSP2”); iii) the number of tracks associated with the physical data storage area space (e.g., 30,000 tracks); and iv) the block size associated with the physical data storage area (e.g., 8192). As would be appreciated, the foregoing are non-limiting examples for illustrative purposes. The control file may include some or all of the foregoing example information as well as different values.

In some implementations, application server142may receive a request to determine an available space associated with one or more portions of a database. In some implementations, the request may include one or more parameters identifying the one or more portions of the database for which the available space is to be determined, reference group size, and/or other parameters. In some implementations, the one or more portions of the database may include one or more database tables associated with the database, one or more data blocks associated with the database, and/or other portions.

In some implementations, the request may identify at least one physical data storage area for which available space is to be determined. In some implementations, the request may include one or more parameters identifying the at least one physical data storage area, for example, the physical data storage area name, and/or other parameters.

In some implementations, application server142may determine at least one physical data storage area associated with the one or more portions of a database in response to the request. In some implementations, the physical data storage area may comprise one or more data blocks that store data (i.e., in the form of one or more data rows) from the one or more portions of the database.

In some implementations, application server142may determine the at least one physical data storage area associated with the one or more portions of a database based on the parameters included in the request. For example, the request may include a parameter identifying one or more database tables associated with the database. In some implementations, application server142may determine the physical data storage area(s) that comprises one or more data blocks that store data from the one or more database tables of the database. In some implementations, application server142may make the determination based on the control file. In some implementations, the control file may include an entry with table information that identifies the physical data storage area(s) that the database table(s) resides in. For example, the table information may include database table name and associated physical data storage area name(s), and/or other information. In some implementations, application server142may access the control file and identify an entry with table information associated with the database table(s) identified in the request. Application server142may determine the physical data storage area(s) based on the identified entry.

In some implementations, the request may include a parameter identifying the physical data storage area itself, for example, name of the physical data storage area. In these implementations, application server142may determine the at least one physical data storage area based on the name.

In some implementations, application server142may locate the determined physical data storage area in DASD120.FIG. 3illustrates an exemplary flow diagram for locating at least one physical data storage area in DASD120, according to various implementations of the present disclosure. In some implementations, application server142may locate the determined physical data storage area based on a control file. In some implementations, in an operation302, application server142may access the control file and identify an entry for the determined physical data storage area from among a plurality of physical data storage area entries in the control file. In some implementations, application server142may use a parameter identifying the determined physical data storage area, for example, name or other parameter, to identify the entry for the determined physical data storage area.

In some implementations, in an operation304, application server142may retrieve one or more physical attributes associated with the determined physical data storage area from the control file. In some implementations, in an operation306, application server142may locate the determined physical data storage area in DASD120based on the one or more physical attributes. In some implementations, in an operation308, application server142may process one or more data blocks in the located physical data storage area. In some implementations, application server142may process each data block to determine an available space in the data block and the number of new rows that can be added to the data block.

For example, the request may identify a database table by name. Application server142may determine that physical data storage area “ABC” stores the database table. Application server142may access the control file to identify an entry for physical data storage area “ABC”. Application server142may retrieve physical attribute(s) associated with the physical data storage area “ABC” from the control file. For example, application server142may retrieve volume identifiers associated with the physical data storage area. Application server142may locate the physical data storage area in DASD120based on the retrieved volume identifiers. Application server142may then process data block(s) in the located physical data storage area.

In some implementations, application server142may determine an available space in each data block of the one or more data blocks in the located physical data storage area. In some implementations, application server142may process each data block separately. In some implementations, the processing may be done without accessing portions of the database for which available space is determined. In other words, application server142may process data blocks without utilizing the resources of database management server112. Instead, application server142may use (i.e., directly access) the physical data storage area to determine available space/space usage. In this manner, space usage of the database may be determined without using database resources and obtaining an estimate of space usage based on actual, physical, storage blocks. In one implementation, since the control file identifies the physical location and format (block size, tracks, etc.) of the physical data storage area, the application server142can use this information to directly access the physical DASD120using the underlying operating system commands for accessing DASD. The application server142can use other physical data storage area and database table attributes in the control file (along with an understanding of the data block construction for this specific database) to analyze the physically accessed data block(s) and determine the amount of space that is “in-use” and the amount of space that is “available”. This is an exemplary implementation that allows the space usage to be calculated without using the resources of database management server112, however, other implementations may also be utilized without departing from the scope of the present disclosure.

FIG. 4is a block diagram illustrating a structure of a data block400to be processed. Data block400may include a certain number of bytes that store data and meta-data. For example, in some implementations of the present disclosure, portion402of data block400may indicate an amount of free space (such as a number of free bytes) in data block400. In some implementations, the free space may represent an amount of unused space in data block400. In some implementations, portion402may be updated as data rows are added, deleted, updated, compressed, or otherwise altered so that the amount of free space is indicated by portion402. In some implementations, portion402may be represented by the first two bytes of data block400. In some implementations, portion404-1of data block400may indicate a first row length of a first row and a table identifier identifying the database table the first row belongs to. In some implementations, the length portion of404-1may be represented by the third and fourth bytes of data block400while the table identifier portion may be represented by the fifth and sixth bytes of data block400. In some implementations, portion406-1of data block400may indicate the first row. Similarly, portion404-2may indicate a second row length and a table identifier identifying the database table the second row indicated by portion406-2resides in, portion404-3may indicate a third row length and a table identifier identifying the database table that the third row indicated by portion406-3resides in, and so on. In some implementations, portion410of data block400may include free space in data block400. In other words, portion410may accommodate one or more additional data rows. In some implementations, the amount of space in portion410corresponds to the free space indicated by portion402.

In some implementations, application server142may process data block400. In some implementations, application server142may determine an amount of free space in data block400based on portion402.

In some implementations, application server142may determine an amount of deleted space by processing each data row406-1,406-2,406-3, . . . ,406-nuntil portion410is reached. In some implementations, application server142may access portion404-1to determine the row length of the first data row in portion406-1. Application server142may process data row in portion406-1. Application server142may skip to portion404-2which is located at the end of portion406-1and process data row in portion406-2. Application server142may similarly skip to portions404-3, . . . ,404-nand process data rows in portions406-3, . . . ,406-nuntil portion410is reached. In some implementations, while processing the data rows, application server142may identify deleted data rows. In some implementations, the deleted row(s) may be marked in data block400by changing the associated table identifier(s) from a valid table identifier(s) to binary zeros, The amount of “deleted space” created by one or more deleted data rows in the data block may be represented in bytes. In some implementations, a data row may be considered deleted when it is physically deleted from the data block. In some implementations, a data row may be considered deleted when it is logically deleted (i.e., data row is deleted but not yet committed) from the data block.

In some implementations, application server142may determine the available space in each data block. In some implementations, the available space may be based on the amount of free space in the data block and the amount of deleted space in the data block.

In some implementations, the available space (“ASPACE”) in a data block may be determined by the following equation:
ASPACE=(FSPACE+DSPACE)  (1),
where FSPACErepresents the amount of free space in the data block and DSPACErepresents the amount of deleted space in the data block.

In some implementations, application server142may access portion402to determine the amount of free space in the data block. In some implementations, application server142may identify the deleted data rows while processing the data rows found in the data block based on the table identifier (i.e., modified table identifier). In some implementations, application server142may determine the deleted space created by the deleted data rows based on the row lengths of the individual deleted rows.

In some implementations, application server142may determine one or more parameters associated with the data block while processing the data block. The one or more parameters may include bytes in-use, bytes free space, number of active data rows (i.e., not deleted data rows), maximum row size, minimum row size, average row size, and/or other parameters.

In some implementations, application server142may determine bytes in-use per data block based on the following equation:
(BL−ASPACE)  (2),
where BLrepresents the length of the data block. The length of the block may be represented in bytes.

In some implementations, application server142may determine a number of new rows that can be added to the data block based on the available space in the data block. In some implementations, application server142may determine a number of new rows that can be added to the data block based on the available space in the data block and an average row size for the data block. In some implementations, application server142may determine the average row size for the data block.

In some implementations, application server142may determine a number of new data rows that can be added to the data block based on the following equation:
(ASPACE/Average row size)  (3)

In some implementations, application server142may determine a total available space associated with the one or more portions of the database based on the determined available space in each data block. In some implementations, application server142may determine the total available space in the one or more data blocks in the physical data storage area associated with the one or more portions of the database. In other words, application server142may determine the total available space by adding the available space determined for each data block of the one or more data blocks.

In some implementations, application server142may determine a total number of new rows that can be added to the one or more data blocks based on the determined number of new rows that can be added to each data block (i.e., by adding the number of new rows of each data block).

In some implementations, application server142may generate one or more reports. In some implementations, application server142may generate the report(s) in response to the request. A report may include, among other things, the available space in each data block, the number of new rows that can be added to each data block, the total available space in the one or more data blocks, and the total number of new rows that can be added to the one or more data blocks.

FIG. 5depicts an exemplary report500generated by application server142, according to various aspects of the present disclosure. In some implementations, the report500may be generated based on information in control file.FIG. 5and other figures illustrating an example of a report is for illustrative purposes only and should not be viewed as limiting. The report may include various formats and configurations while including or excluding some header items and values illustrated and adding other header items and values not otherwise illustrated in the figure as would be appreciated.

In some implementations, report500may be a summarized view of information regarding a physical data storage area (depicted in the right hand section of report500) and database table (depicted in the left hand section of report500). For example, the report indicates one or more parameters such as, without limitation: the table named “REO” is in physical data storage area “REO” (of database 1022); a total number of blocks in the physical data storage area—1800; a number of blocks in-use in the physical data storage area (i.e., with at least one data row in them)—890; a number of unused blocks in the physical data storage area—910; a percentage of blocks in-use in the physical data storage area—49%; a block length for data blocks in the physical data storage area—4,096 bytes; a dynamic extend option for the physical data storage area—turned off (i.e., the physical data storage area may not be dynamically extended; a DSOP (data space management option) mode for the physical data storage area that determines how new data rows are added and how deleted data row space is re-used—“RANDOM FOR REORGANIZATION”; a row length—112 bytes; a number of data rows in the physical data storage area—31,950; and/or other parameters

FIG. 5illustrates an exemplary report for the case where one database table resides in one physical data storage area. However, it will be appreciated that other cases may exist and the reports generated accordingly. For example, there may be a case where multiple database tables may have the same physical data storage area information (i.e., multiple database tables may reside in one physical data storage area). Another case may be where partitioned database tables may have ranges of data rows stored in different physical data storage areas.

FIG. 6depicts an exemplary report600generated by application server142, according to various aspects of the present disclosure. In some implementations, the report may have four sections602,604,606, and608. Section602may describe a summary of information associated with the physical data storage area being processed to determine available space in the data blocks associated with the physical data storage area. “REO” may indicate the name of the physical data storage area being processed. The block size for data blocks in the physical data storage area as illustrated is 4,096 bytes. The number of tracks as illustrated is 150. The number of data blocks in the physical data storage area is 1800. The database table name is REO. The row length for data rows in the data blocks as illustrated is 112 (i.e., the data rows have a fixed row length because there is no compression of data rows, for example). The number of active rows (i.e., not deleted) rows in the data blocks of the physical data storage area as illustrated is 31,950.

Section604may describe a report header. “GROUP” may indicate a particular reference group being processed. In some implementations, a reference group may include a group of blocks. “BLOCKS IN-USE” may indicate a number of data blocks in-use (i.e., have at least one data row in them) in the reference group. “BLOCKS IN-USE %” may indicate a percentage of the data blocks in-use in the reference group. “BLOCKS EMPTY” may indicate a number of data blocks that are empty (i.e., have no data rows in them) in the reference group. “BLOCKS EMPTY %” may indicate a percentage of the data blocks that are empty in the reference group. “KBYTES IN-USE” may indicate the amount of space (in kilobytes) that is in-use in the reference group. “KBYTES IN-USE %” may indicate a percentage of the amount of space that is in-use in the reference group. “KBYTES EMPTY” may indicate the amount of free space (in kilobytes) in the reference group. “KBYTES EMPTY %” may indicate a percentage of the amount of free space in the reference group. “ROWS IN-USE” may indicate a number of data rows that are in-use (i.e., are active and not deleted) in the reference group. “ROWS DELETED” may indicate a number of data rows that are deleted (i.e., physically and logically deleted) in the reference group. “ROWS AVAILABLE” may indicate a number of new data rows of average size that can be added to data blocks in the reference group. “FREESPACE MAX” may indicate a maximum amount of free space in any data block in the reference group. “FREESPACE MIN” may indicate a minimum amount of free space in any data block in the reference group. “FREESPACE AVG” may indicate an average amount of free space per data block in the reference group. “ROWS MAX” may indicate a maximum number of data rows found on any data block in the particular reference group. “ROW MIN’ may indicate a minimum number of data rows found on any data block in the reference group. “ROW AVG” may indicate an average number of data rows per data block. “ROWLEN MAX” may indicate a maximum data row length per data block in the reference group. “ROWLEN MIN” may indicate a min data row length per data block in the reference group. “ROWLEN AVG” may indicate an average data row length per data block in the reference group. “FREESPACE IN BLOCKS” may indicate amounts of free space (by size) in data blocks of the reference group. A field “FREESPACE” may be used in the report to represent the combined space of both the free space in the data block and the re-usable (available) deleted row space in the data block.

In some implementations, section606may provide detailed information regarding each reference group associated with the physical data storage area. As illustrated inFIG. 6, the reference group size is 500 blocks. Thus, a total number of 1800 data blocks in the physical data storage area is divided into 4 reference groups. Reference groups 1-3 each have 500 data blocks, and reference group 4 has the remaining 300 data blocks.

In some implementations, application server142may determine the values depicted in section606for parameters indicated in section604while processing the data blocks in the physical data storage area “REO”.

Reference group 1, for example, has all 500 data blocks in-use and no empty data blocks, which indicates that the blocks in-use percentage is 100%. The amount of space in-use in reference group 1 is 1970 kilobytes. Space in-use (i.e., kilobytes in-use) may be determined by adding the bytes used to store the row-length headers (for example,404-1,404-2, etc.) and the rows (for example,406-1,406-2, etc.) associated with data blocks in the reference group. The amount of free space in reference group 1 as illustrated is 30 kilobytes. The number of data rows in-use as illustrated is 18,000. The maximum, minimum, and average amount of free space per data block in reference group 1 is 62 bytes. The maximum, minimum, and average number of data rows per data block in reference group 1 is 36. The maximum, minimum, and average row length per data block in reference group 1 is 112 bytes. There are no deleted rows and no new data rows of average size can be added to the data blocks in this reference group. Since the maximum freespace for any data block in this group is 62 bytes and the average row length is 112 bytes, there is no freespace area in any data block that is large enough to add a new data row (to this group of data blocks). The free space in blocks section indicates that there a 500 data blocks in reference group 1 that have less than or equal to ¼K of free space in them. In some implementations, application server142may determine these values while processing the data blocks in reference group 1. Values for reference groups 2-4 may be similarly determined and displayed in section606, as depicted inFIG. 6.

In some implementations, section608may provide a summarization of results for all the reference groups which may indicate totals for the physical data storage area. For example, section608may indicate that out of 1800 total data blocks, 890 data blocks are in-use and 910 are empty. The total amount of space in-use as illustrated is 3,498 kilobytes and the total amount of free space is 3,694 kilobytes. The total number of data rows is 31,950 and the number of new data rows that can be added is 32,778. The maximum amount of free space per data block is 4,094 bytes, minimum amount of free space per data block is 62 bytes and the average amount of free space per data block is 2,104 bytes. The maximum number of data rows per data block is 36, the minimum number of data rows per data block is 0, and the average number of data rows per data block is 18. The maximum, minimum, and average row length per data block is 112 bytes. The free space in blocks section indicates that there are 887 data blocks with less than or equal to ¼K free space in them, 1 data block with greater than 2K but less than or equal to 3K of free space in it, and 910 data blocks with greater than 3K but less than or equal to 4K of free space in them. In some implementations, application server142may determine these total values for all the reference groups depicted in section608.

For purposes of simplicity,FIG. 6depicts values for reference groups (including a group of data blocks), however, values for each data block processed may also be depicted without departing from the scope of this disclosure.

In some implementations, data management server112may perform a database reorganization process for the reference group(s), the data blocks, and/or other portion of the database. In some implementations, the database reorganization process may move data rows among data blocks and may use empty data blocks to improve data row ordering. In some implementations, the percentage of data blocks in-use may increase after the database reorganization. In these implementations, a block in-use may be only partially full and may have space to add more data rows.

In some implementations, a user may attempt to extend a physical data storage area based on the blocks in-use percentage. However, the blocks in-use percentage may provide an inaccurate indication that the physical data storage area is full. In other words, the blocks in-use percentage may not provide a complete indication of space usage. For example, if most of the data blocks in the physical data storage have only a few data rows in them (i.e., are partially full), they would be considered as blocks in-use even though they may have additional space to accommodate more data rows. In this example, even though the block in-use percentage may be high, actual space usage may be low. As such, the user may attempt to extend the physical data storage area based on the blocks in-use percentage when doing so is unnecessary.

In some implementations, a physical data storage area extension may be performed offline or online. Online extensions pause all processing being performed on the physical data storage area while the extension is being performed. Reducing the number of database outages to do unnecessary physical data storage area extensions, and reducing the number of pauses caused by unnecessary online (on command) extensions, will improve the overall 24*7 availability of the database. Moreover, resource consumption (over allocated physical data storage area sizes) may be reduced.

By utilizing the reports (for example, reports500and600or other reports based on disclosures described herein) generated by application server142, the user may receive an accurate indication of the fullness of a physical data storage area (i.e., total kilobytes in-use in section608ofFIG. 6) and/or number of new data rows of average size that can be added to the data blocks in the physical data storage area (i.e., total rows available in section608ofFIG. 6). As such, based on the reports, users may make more informed decisions on whether a physical data storage area needs to be extended. In many cases, the reports may facilitate a reduction in the number of extensions performed, thereby reducing database outages or interruptions. The user may also avoid extending data areas when not needed reducing the requirements for physical DASD.

FIGS. 7 and 8depict exemplary reports700and800generated by application server142after a significant amount of the data rows have been deleted and re-added by database processing causing the data rows to be spread across more data blocks. Report700indicates that there are now 1241 data blocks in-use even though the same number of rows (31,950) exist, which is 251 more data blocks than prior to the database processing. The number of unused data blocks after the processing is 559 and the percentage of blocks in-use is 68% which is an increase of 19% even though the number of rows has not changed. The reason that the number of data blocks in-use increased (from 890 to 1,241) was that the database processing deleted a large number of data rows without committing the deletion and then added the same number of rows.

In some implementations, report800has sections802,804,806, and808that are similar to sections602,604,606, and608of report600. As such, after the database processing, while there are more data blocks in-use, the number of data rows in the physical data storage area remained the same (31,950) and the number of new data rows that can be added to data blocks in the physical data storage area also remained the same (32,778).

In some implementations, the reports ofFIGS. 5,6,7, and8may be generated while the database is online (i.e., open for processing).

In some implementations, database tables and/or data rows in database tables may be compressed by database management server112before storing them on DASD120in the form of, for example, compressed data rows in the data blocks. Thus, the various operations described above may be performed by application server142on the compressed tables/rows. Because the amount of compression found in each data row will vary, the average row length measurement provides a reasonable measurement for the compressed data row(s) size. As such, the number of new compressed data rows that can be added to a data block may be an estimate based on the average row sizes per data block and the available space in each block.

FIG. 9is a flowchart900depicting example operations performed by an application server112to determine an available space in one or more data blocks associated with a physical data storage area, according to various aspects of the present disclosure. The various processing operations depicted inFIG. 9are described in greater detail herein. The described operations for a flow diagram may be accomplished using some or all of the system components described in detail above and, in some implementations of the present disclosure, various operations may be performed in different sequences. In some implementations, additional operations may be performed along with some or all of the operations shown inFIG. 9. In yet other implementations, one or more operations may be performed simultaneously. In yet other implementations, one or more operations may not be performed. Accordingly, the operations described are exemplary in nature and, as such, should not be viewed as limiting.

In an operation920, process900may receive a request to determine an available space associated with one or more portions of a database. In some implementations, the request may include one or more parameters identifying the one or more portions of the database for which the available space is to be determined, reference group size, and/or other parameters.

In some implementations, in an operation930, process900may determine a physical data storage area associated with the one or more portions of the database in response to the request. In some implementations, the physical data storage area may comprise one or more data blocks that store data from the one or more portions of the database.

In some implementations, in an operation940, process900may determine an available space in each data block of the one or more data blocks in the determined physical data storage area. In an operation950, process900may determine a total available space associated with the one or more portions of the database based on the available space in each data block.

In some implementations, in an operation960, process900may generate at least one report, for example (report(s) depicted inFIGS. 5-8). In some implementations, the generated report may include the determined available space in each data block, the number of new rows that can be added to each data block, the total available space in the one or more data blocks, and the total number of new rows that can be added to the one or more data blocks.

In some implementations, one or more operations of process900may be performed in response to a user request (or other request) to determine an available space in one or more data blocks associated with a physical data storage area, or in response to a user request (or other request) to generate a report describing the available space in the physical data storage area and the number of new data rows that can be added to the physical data storage area.

In some implementations, one or more operations of process900may be performed while the database is online. In some implementations, the operations may be performed while the database is offline. In some implementations, the one or more operations may be performed without utilizing resources associated with the database management server112. In some implementations, application server142may use an efficient I/O process that has no effect on database processing being performed by database management server112(for example, managing and/or executing database requests).

Implementations of the present disclosure may be described as including a particular feature, structure, or characteristic, but every aspect or implementation may not necessarily include the particular feature, structure, or characteristic. Further, when a particular feature, structure, or characteristic is described in connection with an aspect or implementation, it will be understood that such feature, structure, or characteristic may be included in connection with other implementations, whether or not explicitly described. Thus, various changes and modifications may be made to the provided description without departing from the scope or spirit of the present disclosure.

Other embodiments, uses and advantages of the present disclosure will be apparent to those skilled in the art from consideration of the specification and practice of the present disclosure disclosed herein. The specification and drawings should be considered exemplary only, and the scope of the present disclosure is accordingly intended to be determined solely by the appended claims.