C.9 Searching system tables
You use the SELECT statement to search system tables. Searching system tables enables you to check cost information and chunk information. The authorization identifier for the system tables is MASTER.
After you have searched a system table, immediately execute a COMMIT statement to terminate the transaction. If you do not execute the COMMIT statement, the lock on the system table will not be released. Note that when you terminate the adbsql command after having searched a system table, the lock on the system table is released, and therefore you do not need to execute the COMMIT statement in this case.
If a system table is searched from a transaction for which the REPEATABLE READ transaction isolation level is set, information for an old, un-updated system table might be retrieved. To prevent this, use one of the following search procedures:
-
Search a system table from a transaction for which the READ COMMITTED transaction isolation level is set.
Search a system table after specifying READ_COMMITTED in #SET TRAN_ISOLV of the adbsql subcommand.
-
Execute a COMMIT statement immediately before the search.
If you plan to search a system table from a transaction for which the REPEATABLE READ transaction isolation level is set, search the system table after executing a COMMIT statement.
Information that can be searched depends on whether the HADB user who is searching the system table has the DBA privilege. For details, see (3) Scope of information in system tables that can be referenced by HADB users in C.1 System table overview.
The following subsections provide examples of specifying the SELECT statement to search system tables.
- Organization of this subsection
(3) Checking the information about all chunks in a table based on a table name
(5) Identifying the chunks that were created during a specified period
(6) Identifying the newest chunk ID based on the chunk creation date and time
(7) Identifying the oldest chunk ID based on the chunk creation date and time
(9) Identifying chunks whose data might have been stored on the specified date
(17) Finding out information relating to all synonym dictionaries
(18) Finding out information relating to specific synonym dictionaries
(1) Determining the names of all base tables from which cost information was collected and the collection dates and times
Use the following SELECT statement to search the STATUS_TABLES system table.
- Specification example
-
SELECT "TABLE_SCHEMA","TABLE_NAME","COLLECT_TIME" FROM "MASTER"."STATUS_TABLES"
(2) Determining the names of all indexes for which cost information was collected and the collection dates and times
Use the following SELECT statement to search the STATUS_INDEXES system table.
- Specification example
-
SELECT "TABLE_SCHEMA","INDEX_NAME","COLLECT_TIME" FROM "MASTER"."STATUS_INDEXES"
(3) Checking the information about all chunks in a table based on a table name
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID","CHUNK_COMMENT","CHUNK_STATUS","CREATE_TIME","SWAP_TIME" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=?
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.
(4) Identifying chunk information for a chunk ID
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT * FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CHUNK_ID"=?
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request, a table identifier for the second input request, and a chunk ID for the third input request.
(5) Identifying the chunks that were created during a specified period
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT * FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CREATE_TIME" BETWEEN ? AND ?
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request. Enter the start date and time of the period for the third input request, and enter the end date and time of the period for the fourth input request.
The SELECT statement above targets the following chunks as search items:
-
Chunks created during the specified period
-
Chunks that were created as wait status chunks with the background import facility, but then were changed to the normal status by using the adbchgchunkstatus command during the specified period
However, the following chunks are not included in the search:
-
Chunks created as wait status chunks with the background import facility, during the specified period
(6) Identifying the newest chunk ID based on the chunk creation date and time
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" TC WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CREATE_TIME"=(SELECT MAX("CREATE_TIME") FROM "MASTER"."STATUS_CHUNKS" WHERE TC."TABLE_SCHEMA"="TABLE_SCHEMA" AND TC."TABLE_NAME"="TABLE_NAME")
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.
For the above SELECT statement, the search targets are the chunks for which the following dates and times are the newest:
-
Date and time of chunk creation
-
Date and time of change to normal status chunk by using the adbchgchunkstatus command after being created as a wait status chunk with the background import facility
However, the following chunks are not included in the search:
-
Wait status chunks that have never had the normal status
(7) Identifying the oldest chunk ID based on the chunk creation date and time
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" TC WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CREATE_TIME"=(SELECT MIN("CREATE_TIME") FROM "MASTER"."STATUS_CHUNKS" WHERE TC."TABLE_SCHEMA"="TABLE_SCHEMA" AND TC."TABLE_NAME"="TABLE_NAME")
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.
For the above SELECT statement, the search targets are the chunks for which the following dates and times are the oldest:
-
Date and time of chunk creation
-
Date and time of change to normal status chunk using the adbchgchunkstatus command after being created as a wait status chunk with the background import facility
However, the following chunks are not included in the search:
-
Wait status chunks that have never had the normal status
(8) Identifying the chunk IDs of chunks that were created during the specified period, or the chunks that have been swapped out from current chunk
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND (("CREATE_TIME" BETWEEN ? AND ?) OR ("SWAP_TIME" BETWEEN ? AND ?))
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request. Enter the date and time when chunk creation started for the third input request, and enter the date and time when chunk creation ended for the fourth input request. Enter the date and time when swapping of the current chunk started for the fifth input request, and enter the date and time when swapping of the current chunk ended for the sixth input request.
The SELECT statement above targets the following chunks:
-
Chunks created during the specified period
-
Chunks swapped out from the current chunk during the specified period
-
Chunks that were created as wait status chunks with the background import facility, but then were changed to the normal status by using the adbchgchunkstatus command during the specified period
However, the following chunks are not included in the search:
-
Wait status chunks that have never had the normal status
(9) Identifying chunks whose data might have been stored on the specified date
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CREATE_TIME" <= ? AND ("SWAP_TIME" IS NULL OR "SWAP_TIME" >= ?)
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request, enter a table identifier for the second input request, and enter a specific date and time for the third and fourth input requests.
The SELECT statement above targets the following chunks:
-
Chunks created before the specified date and time
-
Chunks created before the specified date and time that were swapped out from the current chunk after the specified date and time
-
Chunks that were created as wait status chunks with the background import facility, but then were changed to the normal status by using the adbchgchunkstatus command on the specified date and time
However, the following chunks are not included in the search:
-
Wait status chunks that have never had the normal status
(10) Determining the number of chunks created
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT COUNT(*) FROM MASTER.STATUS_CHUNKS WHERE "SCHEMA_NAME" = ? AND "TABLE_NAME" = ?
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.
(11) Searching for chunks that included a specified comment
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID","CHUNK_COMMENT" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"= ? AND "TABLE_NAME"= ? AND "CHUNK_COMMENT" LIKE ?
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request, enter a table identifier for the second input request, and enter the comment that was set in chunks for the third input request.
(12) Checking for delete-pending chunks
Use the following SELECT statement to search the STATUS_CHUNKS system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CHUNK_STATUS"='Pending Delete'
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameters. Enter a schema name for the first input request and a table identifier for the second input request.
(13) Checking for chunks in normal status
Use the following SELECT statement to search the system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CHUNK_STATUS"='Normal'
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter a schema name; for the second input request, enter a table identifier.
(14) Checking for chunks in wait status
Use the following SELECT statement to search the system table.
- Specification example
-
SELECT "CHUNK_ID" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CHUNK_STATUS"='Wait'
If you execute the above SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. For the first input request, enter a schema name; for the second input request, enter a table identifier.
(15) Finding out whether a chunk is archived
Search the system table by using the following SELECT statement.
- Specification example
-
SELECT "ARCHIVE_STATUS" FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CHUNK_ID"=?
If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. The first time you are prompted to enter data, enter the schema name. The second time, enter the table identifier, and the third time, enter the chunk ID.
If Archive is output as the execution result of the SELECT statement, that means the chunk is archived.
(16) Checking the information about the current chunk
Search the system table by using the following SELECT statement.
- Specification example
-
SELECT * FROM "MASTER"."STATUS_CHUNKS" WHERE "TABLE_SCHEMA"=? AND "TABLE_NAME"=? AND "CREATE_TIME" IS NOT NULL AND "SWAP_TIME" IS NULL
If you execute the preceding SELECT statement in the adbsql command, the system asks you to enter data for the dynamic parameter. Enter a schema name for the first input request and a table identifier for the second input request.
(17) Finding out information relating to all synonym dictionaries
Search the system table by using the following SELECT statement.
- Specification example
-
SELECT * FROM "MASTER"."STATUS_SYNONYM_DICTIONARIES"
- Example search results
-
![[Figure]](GRAPHICS/F0000955.GIF)
For output items that do not have an explanation, see the explanation of the column information for STATUS_SYNONYM_DICTIONARIES in C.6 Content of STATUS_SYNONYM_DICTIONARIES.
Parts of the display format of the preceding search results might differ from the display format of the actual output results.
(18) Finding out information relating to specific synonym dictionaries
Search the system table by using the following SELECT statement.
- Specification example
-
SELECT * FROM "MASTER"."STATUS_SYNONYM_DICTIONARIES" WHERE "SYNONYM_DICTIONARY_NAME"=?
If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. Enter the name of the synonym dictionary.
- Example search results
-
![[Figure]](GRAPHICS/F0000956.GIF)
For output items that do not have an explanation, see the explanation of the column information for STATUS_SYNONYM_DICTIONARIES in C.6 Content of STATUS_SYNONYM_DICTIONARIES.
Parts of the display format of the preceding search results might differ from the display format of the actual output results.