Skip to end of metadata
Go to start of metadata

Check Database Structure

This page provides information about the possibilities to check the SAP MaxDB database structure.

What is a database structure check?
Can I run the database structure check during productive usage of my system?
Check database structure - options
How can I run the database structure check?
Database structure check in a SAP liveCache
Log files
How can I identify a running database structure check?
Further information


What is a database structure check?

During a database structure check the database system checks if the database structure is consistent - e.g. if the structure of the B* trees is O.K. - but not the semantics of the data model. Therefore you cannot find logical problems with this check.
If e.g. BAD PAGES are found in your database, you should check the hardware and perform a database structure check.

back to top

Can I run the database structure check during productive usage of my system?

To check the tables, indexes and BLOBs, the corresponding data pages must be read from the data volumes into the data cache. Therefore other data used by the applications might be paged from the data cache to the data volumes. More physical I/O is necessary during the check. That means that the performance of the applications might decrease. Therefore we recommend to do this check during times of low workload - or to run the check on a system copy.

back to top

Check database structure - options

CHECK DATA [EXTENDED] [EXCEPT INDEX]

With a CHECK DATA the complete data (tables and indexes) is checked in ONLINE operational mode. The structure of all B* trees (including BLOBs) is checked.
Whenever a data page is read from the data volume, a check sum is calculated, which is checked against the check sum stored on this page.
Furthermore it is checked if all pages of a B* tree can be reached (if the links are correct) and if the separators on the index level of the B* tree are OK.
If inconsistencies are found, an error is returned. CHECK DATA can not repair such inconsistencies.

If the option EXTENDED is used, further checks are performed: the length and the ascending order of the separators are checked.
As of version 7.6.01 the extended check is always performed - you don't have to specify the keyword EXTENDED anymore.

If the option EXCEPT INDEX is used, indexes are not included in the check. This option can be used to decrease the runtime of the CHECK DATA.

back to top

CHECK DATA [EXTENDED] WITH UPDATE

With a CHECK DATA WITH UPDATE the complete data (tables and indexes) is checked in ADMIN operational mode. The structure of all B* trees (including BLOBs) is checked in the same way as in ONLINE mode.
If no errors are detected, the system deletes all pages which are not referenced in the converter anymore. Such pages could still exist after a corrupted index was dropped and recreated. If the index was corrupted in a way that some pages of the B* tree were not linked correctly anymore, not all pages could be deleted. Such pages are removed during the CHECK DATA WITH UPDATE.
However, when BAD INDEXES still exist in the database, no pages are removed by this check. Therefore you should make sure that no BAD INDEXES exist before you start the CHECK DATA WITH UPDATE.
Other inconsistencies (like error -9026 BAD PAGE) can not be repaired by this check.

If the option EXTENDED is used, further checks are performed: the length and the ascending order of the separators are checked.
As of version 7.6.01 the extended check is always performed - you don't have to specify the keyword EXTENDED anymore.

back to top

CHECK TABLE <table_name> [EXTENDED]

The specified table is checked in ONLINE operational state. The same checks are performed for the specified table as during the CHECK DATA for all tables. Indexes of the specified table are not checked.

If the option EXTENDED is used, further checks are performed: the length and the ascending order of the separators are checked.
As of version 7.6.01 the extended check is always performed - you don't have to specify the keyword EXTENDED anymore.

back to top

CHECK TABLE <table_name> WITH LONG CHECK

Using the option WITH LONG CHECK the BLOBs of the specified table are checked additionally: it is checked if for each BLOB surrogate specified in the table actually a BLOB does exist. To verify this consistency, the table needs to be locked in SHARE mode.
Additionally as of version 7.6 the file directory counters are checked and updated for the specified table.

back to top

CHECK TABLE <table_name> CATALOG

With this check the catalog information of a table is verified. E.g. it is checked

  • if an altered column does have a date of change in the catalog
  • if a specified comment actually exists
  • which views depend on the table and
  • if all of these views actually exist.

If errors are found, these are corrected in the catalog.

back to top

CHECK INDEX <index_name > [ ON <table_name>] [EXTENDED]

This statement is only available as of version 7.8.00 Build 10.
The specified index of the specified table is checked in ONLINE operational state. The same checks are performed for the specified index as during the CHECK DATA.

back to top

How can I run the database structure check?

Database Manager GUI/Database Studio

In Database Manager GUI (DBMGUI) use menu Check -> Database Structure... to check the database structure. There you can choose between

  • Check database structure in operational state ONLINE
  • Check database structure and clear converter in operational state ADMIN
  • Check database structure for a selected table in operational state ONLINE

In the next step you can specify the advanced options (EXTENDED, EXCEPT INDEX, WITH LONG CHECK, CATALOG) - depending on the selected check.

In Database Studio use Administration Tasks -> Check Database Structure (Checking Database Structures).

 

back to top

Database Manager CLI

You can use the following DBM commands to perform the requested check in operational state ONLINE:

dbmcli -d <database_name> -u <dbm_operator>,<password> db_execute CHECK DATA [EXTENDED] [EXCEPT INDEX]

dbmcli -d <database_name> -u <dbm_operator>,<password> db_execute CHECK TABLE <owner>.<table_name> [EXTENDED] [WITH LONG CHECK]

dbmcli -d <database_name> -u <dbm_operator>,<password> db_execute CHECK TABLE <owner>.<table_name> CATALOG

dbmcli -d <database_name> -u <dbm_operator>,<password> db_execute CHECK DATA SNAPSHOT <snapshot_ID> (starting with database version 7.9), for snapshot_ID a valid number of the snapshot has to be used . If you use value 0 the snapshot is implicitely created.

dbmcli -d <database_name> -u <dbm_operator>,<password> db_check data snapshot [<ID snapshot_ID>] (db_check), for snapshot_ID a valid number of the snapshot has to be used . If you do not use <ID snapshot_ID> the snapshot is implicitely created.

To perform the check in operational state ADMIN, use the following DBM command:

dbmcli -d <database_name> -u <dbm_operator>,<password> db_execute CHECK DATA [EXTENDED] WITH UPDATE

back to top

In the SAP system

In DBA planning calendar (transactions DB13, DB13C) and DBA Cockpit (transaction DBACOCKPIT) you can schedule a database structure check using the following actions: 
Check database structure checks the tables and indexes. 
Check database structure (tables only) checks only the tables.
Check database structure of the snapshot checks the objects in a snapshot (starting with database version 7.9).
Further information can be found in Scheduling a Database Structure Check.

back to top

Database structure check in a SAP liveCache

Before you run a database structure check in a SAP liveCache please check note 506981. In some liveCache versions you must not run a database structure check!
Note 521870 explains how the database structure check can be executed in a SAP liveCache.
For B* trees the same checks are performed as in an OLTP instance.
For class containers the following checks are performed:

  • is the linkage of the pages correct and do all pages of this page chain belong to the specified container?
  • have all pages of the page chain from the root page to the end a decreasing sequence number? This is important to prevent deadlocks in the object iterators.
  • are all pages with 'free' objects part of the free-page-chain?
  • for each page the object management of the page is checked (that is different counters and the chain of not used objects)
  • is the history for all objects which are marked as deleted correct (undo information and before image)? Only if this history is available the garbage collectors can release this object later on.
  • is the key entry in the index of an OmsKeyedObject available and does this entry contain the correct OID?

It is not yet possible to perform structure check for a single container.

back to top

Log files

In files dbm.prt and dbm.utl the return code of the check is logged. RETURNCODE 0 means that everything is OK.
If errors are found during the check, these are logged in file KnlMsg/knldiag and KnlMsgArchive/knldiag.err. There you can also find the root page number of the corrupted B* tree. Using this information, you can identify the corresponding table name: SELECT * FROM roots WHERE root = <root>

back to top

How can I identify a running database structure check?

In file KnlMsg/knldiag you can find the following entries, while the CHECK DATA is running:

53022 INDEX    Check Index: 15876 (Root)
53022 B*TREE   Check Table: 44919 (Root)
53022 INDEX    Check Index: 74733 (Root)
53022 B*TREE   Check Table: 104408 (Root)
53022 B*TREE   Check Long: 181 (Root)
53022 B*TREE   Check Table: 433 (Root)
53022 B*TREE   Check Long: 15844 (Root)
53022 B*TREE   Check Table: 30221 (Root)
53022 B*TREE   Check Table: 62955 (Root)

In file dbm.utl you can find the following entry:
REQ CHECK DATA EXCEPT INDEX
As long as there is no return code logged for the action, the CHECK DATA is still running.

In version 7.6 you can use command x_cons show active. If a user task is in state Wait for Job this user task has asked server tasks to perform the CHECK DATA. The server tasks, which actually perform the CHECK DATA, are in state CHKDATA.

back to top

Further information

For further information please see note 940420 and online documentation about Database Structure.

back to top

Relevant SAP Notes

SMP login required
940420 FAQ: Database structure check
1837280 Check Data Runtime Optimization
521870 Database structure check: liveCache 
852168 SAP Content Server: Attention using Database Structure check (CHECK DATA)
2262264 CHECK DATA SNAPSHOT can crash SAP MaxDB/liveCache, if more than 127 data volumes are configured