Consistency Checks for Multistore and Extended Store Tables in Dynamic Tiering
Beginning with version 2.0, dynamic tiering lets perform a range of consistency check actions on catalog objects and database tables for both multistore and extended store tables:
- Catalog consistency checker – use CALL CHECK_CATALOG to check the consistency of metadata in both multistore and extended store tables.
- Extended store database consistency checker – use CALL CHECK_ES to check the consistency of the current database in the extended store.
- Table consistency checker – use CALL CHECK_TABLE_CONSISTENCY to check the column store partition of a multistore table.
Catalog consistency checker
Dynamic tiering 2.0 has expanded the ability of the CALL CHECK_CATALOG procedure call so that you can now perform consistency checks of metadata for both multistore and extended store tables.
Syntax
CALL CHECK_CATALOG ('<action>'), '<object_type>',
'<schema_name>', '<object_name>')
This procedure call lets you:
- Check the metadata of a multistore or extended store table for inconsistencies like missing tables, columns, and indexes, as well as fix these inconsistencies (by deleting those orphaned objects).
- Check the consistency of value domains in the catalog object such as table type, and field types. When you perform this action on a multistore table, it is only applied to the column store partitions of the multistore table; checks are not performed on extended storage partitions.
- Checks the consistency of references in the catalog object. When you perform this action on a multistore table, it is only applied to the column store partitions of the multistore table; checks are not performed on extended storage partitions.
You can specify whether you want to perform these checks on tables, indexes, columns, or all catalog objects. Depending on what action you specify, performing this on a multistore table checks only the column store partitions of the multistore table, rather than on extended store partitions.
Note: CHECK_CATALOG only checks the metadata of objects, and not check the actual structure or data of tables and indexes.
For detailed information on CALL CHECK_CATALOG, see:
- “Catalog Consistency Checker” in the SAP HANA Dynamic Tiering: Administration Guide for how to use this in multistore and extended store tables.
- “Catalog Consistency Check” in the SAP HANA Administration Guide for how to use catalog consistency checks in SAP HANA
- “CALL CHECK_CATALOG Statement (Multistore Table) [Dynamic Tiering]” in the SQL HANA SQL and System Views Reference for reference information.
Extended store database consistency checker
This new procedure was introduced in version 2.0 of SAP HANA dynamic tiering, and lets you check the validity of the current database in the extended store.
Syntax
CALL CHECK_ES ('<action>', '<object_type>', '<object_name>',
'<resource_percentage>')
Using this procedure call, you can:
- Verify that all database pages can be read for the entire database, main cache, specific index, specific index type, specific table, or specific dbspace.
- Verify the content of indexes for the entire database, or a specific index, table, or dbspace to detect keys or rows that are either missing, or are extras.
- Check allocation with blockmap information for the entire database, a specific index, a specific index type, specific table, or a specific dbspace. [Note that this does not check index consistency.]
- Detect leaked blocks (allocated blocks unclaimed by any object you specify in object_name) for database or dbspace targets, and returns the block numbers for leaked blocks.
- Detect duplicate blocks (blocks for which two or more objects claim ownership) or extra blocks (unallocated blocks owned by an object), and returns the block numbers for duplicate blocks.
- Check allocation for the entire database, and returns block numbers for unallocated blocks.
- Reset the allocation map for the entire database or dbspace.
You can perform consistency checks on indexes, databases, tables, partitions, columns, as well as dbspaces.
Example
This example checks the consistency of block allocation for the entire database, allocating a resource percentage of 100 percent (creating one thread per CPU):
Call CHECK_ES ( 'allocation', 'database', '', '100' )
Statistic Value Flag ========================================================= DBCC Allocation Mode Report ========================================================= DBCC Status No Errors Detected ========================================================= Allocation Summary ========================================================= Blocks Total 25600 Blocks in Current Version 5917 Blocks in All Versions 5917 Blocks in Use 5917 % Blocks in Use 23 ========================================================= Allocation Statistics ========================================================= Marked Logical Blocks 8320 Marked Physical Blocks 5917 Marked Pages 520 Blocks in Freelist 2071196 Imaginary Blocks 2014079 Highest PBN in Use 1049285 Total Free Blocks 19683 Usable Free Blocks 19382 % Total Space Fragmented 1 % Free Space Fragmented 1 Max Blocks Per Page 16 1 Block Page Count 165 3 Block Page Count 200 4 Block Page Count 1 10 Block Page Count 1 16 Block Page Count 153 2 Block Hole Count 1 3 Block Hole Count 19 6 Block Hole Count 12 7 Block Hole Count 1 10 Block Hole Count 1 15 Block Hole Count 1 16 Block Hole Count 1220
For more information on CALL CHECK_ES, see:
- “Extended Store Database Consistency Checker” in the SAP HANA Dynamic Tiering: Administration Guide for how to use this in extended store tables.
- “CALL CHECK_ES Statement (Extended StoreTable) [Dynamic Tiering]” in the SQL HANA SQL and System Views Reference for reference information.
Table consistency checker
With the introduction of SAP HANA dynamic tiering 2.0, you can now use this procedure call to perform consistency checks in the column store partition of a multistore table.
Syntax
CALL CHECK_TABLE_CONSISTENCY ('<action>', '<schema name>',
'<table name>')
With this procedure call, you can:
- Perform all available checks
- Perform a check restricted to default (column) store and multistore tables.
- Check the consistency of the primary key in the default store.
- Check the consistency of the internal $rowid$ column only for the column store partition of a multistore table.
- Check the consistency of partitioning-related metadata.
- Check the assignment of rows to partitions.
- Repair the assignment to rows to partitions.
You can specify schema, object, or tables.
Example
This procedure checks the consistency for a table named “ABC/abc” in the SYSTEM schema:
CALL CHECK_TABLE_CONSISTENCY ('CHECK', 'SYSTEM', '"ABC/abc"')
For more information on CALL CHECK_TABLE_CONSISTENCY, see:
- “Table Consistency Checker” in the SAP HANA Dynamic Tiering: Administration Guide for how to use this in a multistore table.
- “Table Consistency Check” in the SAP HANA Administration Guide for how to use table consistency checks in SAP HANA.
- “CALL CHECK_TABLE_CONSISTENCY Statement (Multistore Table) [Dynamic Tiering]” in the SQL HANA SQL and System Views Reference for reference information.
References
In addition to the references to specific procedure calls, see the following for more information:
- “Performing Consistency Checks” in the SAP HANA Dynamic Tiering: Administration Guide
- “Table and Catalog Consistency Checks” in the SAP HANA Administration Guide
- “Call Statement (Procedural)” in SAP HANA SQL and System Views Reference
- SAP Note 1977584 – Technical Consistency Checks for SAP HANA Databases