13.10.8 Checking the usage rate and usage of a DB area file in a specific DB area (output of usage information for DB areas, tables, and indexes)

This example outputs usage information for a specific DB area (DBAREA01) and determines the following:

Usage rate of the DB area files in DBAREA01
Usage amount (in megabytes) of DBAREA01 for each DB area file
Usage amount (in megabytes) of a table stored in DBAREA01 for each DB area file
Usage amount (in megabytes) of an index stored in DBAREA01 for each DB area file

■ Executing the adbdbstatus command

adbdbstatus -d used               ...1
            -c dbarea             ...2
            -n DBAREA01           ...3
            -S M                  ...4
            --shared-lock         ...5

Explanation:

Specifies usage information as the information to be output.
Specifies DB area as the range of usage information collection.
Specifies the name of the DB area (DBAREA01) for which usage information is to be collected.
Specifies megabytes as the units for displaying usage amounts.
Specifies the --shared-lock option so that information can be output even while another SQL statement or command is updating the database.

Note that if the --shared-lock option is specified, the usage of deletion-pending chunks in a system table (base table) and indexes defined for the system table (base table) cannot be output.

When the adbdbstatus command is executed, the usage information for DB area DBAREA01 is output.

The following shows how to check the output results.

Organization of this subsection

(1) Checking the usage rate of a DB area in the DB area file
(2) Checking the usage amount (in megabytes) of a DB area for each DB area file
(3) Checking the usage amount (in megabytes) of a table for each DB area file
(4) Checking the usage amount (in megabytes) of an index for each DB area file

(1) Checking the usage rate of a DB area in the DB area file

[Figure]

To check the usage rate of the DB area files in DBAREA01:

Find the rows required for the calculation of the usage rate.

To check the usage rates of the DB area files of the DB area, find the rows that contain a value in the column headed MB_Total_segments (column 22 in the output results).
Verify the DB area file names.

Verify the DB area file names displayed in the column headed DBarea_filename (column 9 in the output results).
Obtain the usage rate for each DB area file in the DB area.

On each row (each DB area file), check the values of the columns headed MB_Used_segments (column 20 in the output results) and MB_Total_segments (column 22 in the output results). Set these values in the following formula to obtain the segment usage rate for each DB area file.

Segment usage rate for each DB area file (%)

MB_Used_segments ÷ MB_Total_segments × 100

Explanation of variables

MB_Used_segments: Value of the MB_Used_segments column (in megabytes)

MB_Total_segments: Value of the MB_Total_segments column (in megabytes)

In this example, the usage rate of DB area file /home/adbmanager/DBDIR/DBAREA01 is 1,152 megabytes ÷ 1,152 megabytes × 100 = 100%, and the usage rage of DB area file /home/adbmanager/DBDIR/DBAREA01.00001 is 984 megabytes ÷ 984 megabytes × 100 = 100%.

To Page Top

(2) Checking the usage amount (in megabytes) of a DB area for each DB area file

[Figure]

To check the usage amount (in megabytes) of DB area DBAREA01 for each DB area file:

Find the rows required for the calculation of usage values.

To check the usage values for the DB area files in the DB area, find the rows that contain a value in the column headed MB_Total_segments (column 22 in the output results).
Verify the DB area file names.

Verify the name of the DB area file names displayed in the column headed DBarea_filename (column 9 in the output results).
Obtain the usage amount (in megabytes) of the DB area for each DB area file.

Obtain the value of the MB_Used_segments column (col. 20 in the output results) for each row (each DB area file).

To Page Top

(3) Checking the usage amount (in megabytes) of a table for each DB area file

[Figure]

To check the usage amount (in megabytes) of a table for each DB area file:

Find the rows required for the calculation of usage values.

To check the usage values for the table in the DB area files, find the rows that contain table in the column headed Object_type (column 11 in the output results).
Verify the schema name and table identifier of the table whose usage values are to be checked.

Verify the table' schema name displayed in the column headed Schema_name (column 5 in the output results) and the table identifier displayed in the column headed Object_identifier (column 6 in the output results).
Verify the DB area file names.

Verify the names of the DB area files displayed in the column headed DBarea_filename (column 9 in the output results).
Obtain the usage amount (in megabytes) of the table for each DB area file.

Obtain the value of the MB_Used_pages column (col. 21 in the output results) for each row. Total the values obtained for each DB area file to obtain the amount of table usage in each DB area file.

In this example, the usage of DB area file /home/adbmanager/DBDIR/DBAREA01 for table ADBUSER01.TABLE0 is 36 megabytes + 0 megabytes = 36 megabytes, and the usage of DB area file /home/adbmanager/DBDIR/DBAREA01.00001 for table ADBUSER01.TABLE01 is 68 megabytes + 0 megabytes = 68 megabytes.

To Page Top

(4) Checking the usage amount (in megabytes) of an index for each DB area file

[Figure]

To check the usage amount (in megabytes) of an index for each DB area file:

Find the rows required for the calculation of usage values.

To check the usage values for the index in the DB area files, find the rows that contain index in the column headed Object_type (column 11 in the output results).
Verify the schema name and index identifier of the index whose usage values are to be checked.

Verify the index's schema name displayed in the column headed Schema_name (column 5 in the output results) and the index identifier displayed in the column headed Object_identifier (column 6 in the output results).
Verify the DB area file names.

Verify the names of the DB area files displayed in the column headed DBarea_filename (column 9 in the output results).
Obtain the usage amount (in megabytes) of the index for each DB area file.

Obtain the value of the MB_Used_pages column (col. 21 in the output results) for each row. Total the values obtained for each DB area file to obtain the amount of index usage in each DB area file.

In this example, the usage of DB area file /home/adbmanager/DBDIR/DBAREA01 for index ADBUSER01.INDEX01 is 8 megabytes + 4 megabytes = 12 megabytes, and the usage of DB area file /home/adbmanager/DBDIR/DBAREA01.00001 for index ADBUSER01.INDEX01 is also 8 megabytes + 4 megabytes = 12 megabytes.

To Page Top