11.17.5 Checking the status and amount of use of system tables

(1) Checking the status and amount of use of system tables (base tables)

To check the status and amount of use of system tables (base tables), execute the adbdbstatus command to output the table summary information.

For details about the adbdbstatus command, see adbdbstatus (Analyze the Database Status) in the manual HADB Command Reference.

Example of the command to be executed

In this example, summary information of system table (base table) STATUS_CHUNKS is output.

adbdbstatus -d summary -c table -n "HADB"."STATUS_CHUNKS" -S M --shared-lock

If you execute the command, summary information of system table HADB.STATUS_CHUNKS is output to the standard output. Check the following items in the output result of the adbdbstatus command.

When checking whether data before reorganization remains

Check the value output to Pending_delete_chunks. If the value output to Pending_delete_chunks is 1, the data before reorganization (deletion-pending chunk) remains.

Re-executing the adbreorgsystemdata command deletes the data before reorganization.

When checking the amount of use of system tables (base tables)

Check the value output to MB_Used_pages. You can check the size (in megabytes) of the area used by the system tables (base tables) that are stored in the system-table DB area.

Note that, if the --shared-lock option is specified, the value output under MB_Used_pages does not contain the area used by the deletion-pending chunk. If, in the value output under MB_Used_pages, you want to include the area used by the deletion-pending chunk, do not specify the --shared-lock option.

To Page Top

(2) Checking the storage efficiency of a system table (base table)

To check the storage efficiency of a system table (base table), you need to use SQL tracing.

Procedure:

1. Specify the settings so that SQL trace information is output.

   Use SQL tracing so that the following SQL trace information is output:

   • Access path information

   • Access path statistical information

   For details about output of the SQL trace information, see 10.11.5 Preparations for outputting SQL trace information.

   Note

   If Y is specified for the following operands in the server definition, access path information and access path statistical information are output:

   • adb_sql_trc_out operand

   • adb_sql_trc_accesspath operand

   For details about the operands in the server definition, see 7.2.5 Operands related to SQL statements (set format).

   If the HADB server has started, use the adbchgsqltrc command. For details about the adbchgsqltrc command, see adbchgsqltrc (Start or Stop Output of SQL Trace Information) in the manual HADB Command Reference.

2. Use an SQL statement to check the number of rows in a system table.

   On the system table for which you check the storage efficiency, execute an SQL statement used for outputting the number of rows by an HADB user with the DBA privilege. The number of rows that is output as the result of execution of the SQL statement is the number of rows in the system table.

   The following shows SQL statements corresponding to individual system tables.

   ▪ SQL statement used to output the number of rows in the STATUS_TABLES table

   SELECT COUNT(COLLECT_TIME)
     FROM MASTER.STATUS_TABLES

   ▪ SQL statement used to output the number of rows in the STATUS_COLUMNS table

   SELECT COUNT(DATA_TYPE_CODE)
     FROM MASTER.STATUS_COLUMNS

   ▪ SQL statement used to output the number of rows in the STATUS_INDEXES table

   SELECT COUNT(COLLECT_TIME)
     FROM MASTER.STATUS_INDEXES

   ▪ SQL statement used to output the number of rows in the STATUS_CHUNKS table

   SELECT COUNT(NVL(CREATE_TIME,CURRENT_TIMESTAMP))
     FROM MASTER.STATUS_CHUNKS

   ▪ SQL statement used to output the number of rows in the STATUS_SYNONYM_DICTIONARIES table

   SELECT COUNT(CREATE_TIME) 
     FROM MASTER.STATUS_SYNONYM_DICTIONARIES

3. Check the number of rows for the invalid row data.

   From the access path information in the SQL trace information output by execution of the SQL statement in step 2, check the number of rows for the invalid row data in the system table. Check the value for Data_deleted_rows_cnt in the access path information that satisfies the following conditions:

   • The schema name is HADB.

   • The table name is the system table name specified in the SQL statement executed in step 2.

   The value for Data_deleted_rows_cnt that satisfies the preceding conditions is the number of rows for the invalid row data in the relevant system table.

4. Determine the storage efficiency of the system table (base table).

   Determine the storage efficiency of the relevant system table (base table) based on the results of steps 2 and 3. The following shows the formula:

   Formula

   If the value obtained from the formula is close to 0, the storage efficiency of the system table (base table) can be determined to be poor. Execute the adbreorgsystemdata command to reorganize the system table (base table).

To Page Top