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:


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:


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:

References

In addition to the references to specific procedure calls, see the following for more information:

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply