Ensuring Data Integrity

This chapter describes how Essbase handles transactions and locking and other ways that Essbase protects data.

This chapter includes the following sections:

If you are migrating from a previous release of Essbase, see the Essbase Installation Guide.

Transactions

When a database is in read/write mode, Essbase considers every update request to the server (such as a data load, a calculation, or a statement in a calculation script) as a transaction. Essbase tracks information about transactions in a transaction control file (dbname.TCT).

The transaction control file contains an entry for each transaction and tracks the current state of each transaction (Active, Committed, or Aborted).

For more information, see How Essbase Handles Transactions.

Isolation Levels

Isolation levels determine how Essbase commits data to disk. When data is committed, it is taken from server memory and written to the database on disk. Essbase automatically commits data to disk. There are no explicit commands that users perform to commit data blocks. However, setting the isolation level for a database defines how Essbase automatically commits data blocks.

Essbase offers two isolation levels for transactions: committed access and uncommitted access (the default). You can optimize data integrity by using committed access.

For information about committed access, see Committed Access. For information about uncommitted access, see Uncommitted Access.

Data Locks

Essbase issues write (exclusive) locks for blocks that are created, updated, or deleted, and issues read (shared) locks for blocks that need to be accessed but not modified. By issuing the appropriate locks, Essbase ensures that data changed by one operation cannot be corrupted by a concurrent update.

Essbase locks data blocks, not objects. For information about locking and unlocking outlines and other objects, see Locking and Unlocking Objects.

This table explains the lock types:

Table 41: Basic Lock Types

Lock
Description

Write (exclusive) lock

Prevents any other transaction from accessing the locked data block. Used for all data block updates, including spreadsheet lock&send.

Read (shared) lock

Allows other transactions read-only access to the data block, but prevents other transactions from modifying the data block.



This table shows the locks that Essbase issues for various types of operations.

Table 42: Locking by Higher-Level Functions

Type of Operation
Lock Issued

Spreadsheet retrieve

Read (shared) lock on each data block.

Retrieve and lock

Write (exclusive) lock on all affected blocks. A subsequent send command commits the data.

Calculate derived block

Write lock on the block being calculated.

As a block is calculated, all blocks containing the block's children acquire read locks.

Data load

Write lock.

Restructure

Write lock.



How Essbase handles locking depends on whether committed or uncommitted access is enabled.

Committed Access

Committed access provides a high level of data consistency because only one transaction at a time can update data blocks. Under committed access, Essbase allows transactions to hold read/write locks on all data blocks involved with the transaction until the transaction completes and commits. However, you can still allow read-only access to the last committed data values.

Essbase provides options that determine when locks are issued on data blocks:

When you have pre-image access enabled, you are not limited to read-only access to data blocks; if you need write access to locked blocks, your transaction waits for write access or times out, depending on the wait or time-out setting. Your transaction gets immediate write access to data blocks that are not locked by another transaction.

If you do not have pre-image access enabled and if you need read or write access to locked blocks, your transaction waits for write access or times out, depending on the wait or time-out setting.

Caution: Under committed access, Essbase retains redundant data until a transaction commits. Allow disk space for double the size of the database to accommodate redundant data.

Locking Under Committed Access

Under committed access, Essbase locks blocks for read and write access:

Table 43 illustrates locking behavior under committed access when more than one transaction is contending for a lock on the same data. In the example in Table 43, transaction Tx1 is running, and transaction Tx2 is requesting access to the same data.

Note that access to locked blocks depends on what options are enabled. For a list of options, see Committed Access.

Table 43: Locking Behavior Under Committed Access

.
Tx1 holds read lock;
Tx2 requests read lock
Tx1 holds read lock;
Tx2 requests write lock
Tx1 holds write lock;
Tx2 requests read lock
Tx1 holds write lock;
Tx2 requests write lock

Pre-image access enabled

Wait (time-out) period specified (indefinite wait or a number of seconds wait)

Tx2 gets read lock.

Tx2 waits for Tx1 to release read lock.

Tx2 gets pre-image access.

Tx2 waits for Tx1 to release write lock.

No wait (time-out) period specified (immediate time-out)

Tx2 gets read lock.

Essbase issues time-out message and aborts the transaction.

Tx2 gets pre-image access.

Essbase issues time-out message and aborts the Tx2 transaction.

No pre-image access

Wait (time-out) period specified (indefinite wait or a number of seconds wait)

Tx2 gets read lock.

Tx2 waits for Tx1 to release read lock.

Tx2 waits for Tx1 to release write lock.

Tx2 waits for Tx1 to release write lock.

No wait (time-out) period specified (immediate time-out)

Tx2 gets read lock.

Essbase issues time-out message and aborts the Tx2 transaction.

Essbase issues time-out message and aborts the Tx2 transaction.

Essbase issues time-out message and aborts the Tx2 transaction.



For information about how to set concurrency parameters, see Specifying Data Integrity Settings.

Concurrency with Committed Access

Occasionally under committed access, a situation results when two transactions are locking or waiting for access to the same blocks, and neither transaction can complete under these conditions. This called a deadlock.

For example, if transaction Tx1 needs to update first data block B1 and then data block B2, it first locks B1 and then attempts to lock B2. Meanwhile, if transaction Tx2 needs to update first data block B2 and then block B1, Tx2 first locks B2 and then attempts to lock B1. Tx1 locked B1 and is waiting for B2, and Tx2 locked B2 and is waiting for B1.

Essbase transactions periodically perform deadlock detection while waiting to acquire a lock. If detected, Essbase issues an error message, and the transaction aborts.

If you try to update a block that is locked by another user, Essbase behaves as follows:

For information about how to set concurrency options, see Specifying Data Integrity Settings.

Rollback with Committed Access

Under committed access, if the server crashes, Essbase rolls back all database updates by transactions that were in progress when the server stopped. Thus, Essbase ensures that changes made by the aborted transactions are undone.

If a transaction is aborted due to a non-fatal error, all changes made by the transaction are rolled back.

For more information, see Recovering from a Crashed Database.

Uncommitted Access

With uncommitted access (enabled by default), the Essbase kernel allows transactions to hold read/write locks on a block-by-block basis; Essbase releases a block after it is updated but does not commit blocks until the transaction completes or until a specified limit (a "synchronization point") has been reached. You can set this limit, as described below.

Concurrent users accessing the same data blocks might experience unexpected results under uncommitted access, because Essbase allows read-only access to data at its last commit point.

With uncommitted access, you can control when Essbase performs an explicit commit operation by specifying synchronization point parameters:

If a user-defined threshold is exceeded during an operation, Essbase issues a synchronization point to commit the data processed to that point. Essbase performs as many synchronization points as are necessary to complete the operation.

Note: Essbase analyzes the value of Commit Blocks and Commit Rows during its analysis of feasibility for parallel calculation use. If Essbase finds the values set too low, it will automatically increase them.

For information about how to specify synchronization point parameters, see Specifying Data Integrity Settings.

Caution: Essbase retains redundant data to enforce transactional semantics. Allow disk space for double the size of the database to accommodate redundant data, particularly if both Commit Blocks and Commit Rows are set to 0.

Locking Under Uncommitted Access

Under uncommitted access, Essbase locks blocks for write access until Essbase finishes updating the block. This is in contrast to committed access, when Essbase holds locks until a transaction completes.

Table 44 illustrates locking behavior under uncommitted access when more than one transaction contends for a lock on the same data. In the example in Table 44, transaction Tx1 is running, and transaction Tx2 is requesting access to the same data.

Table 44: Locking Behavior with Uncommitted Access

Status When Tx2 Makes a Request
If Tx1 holds read lock
If Tx1 holds write lock

Read lock

Tx2 gets read lock.

Tx2 gets read lock.

Write lock

Tx2 gets write lock.

Tx2 waits for Tx1 to release the lock.



Concurrency with Uncommitted Access

With uncommitted access, blocks are released more frequently than with committed access, when all blocks are locked until the end of the transaction.

Rollback with Uncommitted Access

Under uncommitted access, if the server crashes, Essbase rolls back all database updates from the point of the last successful commit. Some of the updates from an aborted transaction may have committed. Whether transactions committed their updates the way users expected depends on the order in which overlapping transactions updated and committed data.

If a transaction is aborted due to a non-fatal error, Essbase commits only the data that the transaction finished processing prior to the abort of the transaction.

For more information, see Recovering from a Crashed Database.

Parallel Calculation and Uncommitted Access

If Essbase is using parallel calculation, it will check the commit threshold.

Committed Versus Uncommitted Access

Consider these issues when choosing an isolation level:

Table 45: Issues Affecting Selection Of An Isolation Level  

Issue
Explanation

Database Performance

Uncommitted access always yields better database performance than committed access. When using uncommitted access, Essbase does not create locks that are held for the duration of a transaction but commits data based upon short-term write locks.

Data Consistency

Committed access provides a higher level of data consistency than uncommitted access. Retrievals from a database are more consistent. Also, only one transaction at a time can update data blocks when the isolation level is set to committed access. This factor is important in databases where multiple transactions attempt to update the database simultaneously.

Data Concurrency

Uncommitted access provides better data concurrency than does committed access. Blocks are released more frequently than they are during committed access. With committed access, deadlocks can occur.

Database Rollbacks

If a server crash or other server interruption occurs while there are active transactions running, the Essbase kernel rolls back the transactions when the server is restarted. With committed access, rollbacks return the database to its state before any transactions began. With uncommitted access, rollbacks may result in some data being committed and some data not being committed.

For information about actions to take when a transaction does not complete, see What to Expect If a Server Interruption Occurs.



How Essbase Handles Transactions

Essbase tracks transactions from start to finish, swapping data blocks in and out of memory as needed and committing data blocks when a transaction completes. The following list describes how Essbase handles a transaction: all list items apply to both committed and uncommitted access (see Isolation Levels).

  1. A user or batch program begins an operation.
  2. The OLAP engine notifies the Essbase kernel that a transaction is to begin.
  3. The Essbase kernel begins the transaction.
  4. The OLAP engine requests data from the Essbase kernel.
  5. The Essbase kernel locates the requested data. It passes the data, and some associated control information, to the OLAP engine. If you are using Spreadsheet Add-in, you see this data displayed on the sheet.
  6. If you are using Spreadsheet Add-in, when you modify data, you issue the Send command.
  7. The Essbase kernel associates the transaction with an entry in its transaction control table.
  8. After the operation is complete on the OLAP engine side, the OLAP engine notifies the Essbase kernel about the update, and the Essbase kernel updates internal data structures accordingly.
  9. Steps 4 through 8 repeat as often as necessary to complete the operation.
  10. The transaction ends. If Essbase encounters an error during transaction processing, it aborts the transaction. If no errors are encountered, Essbase commits the transaction. See Isolation Levels for details on the differences in commit behavior under committed and uncommitted access.
  11. Essbase issues a message to notify the client that the transaction is complete; for example, "TOTAL CALC ELAPSED TIME..."

Under uncommitted access, it is possible to access uncommitted data when multiple transactions are active and are accessing the same data. Transaction results are unpredictable under uncommitted access.

Under uncommitted access, if you have defined a commit threshold, Essbase may need to break down a single database operation into multiple synchronization points. See Uncommitted Access for information on commit thresholds.

Specifying Data Integrity Settings

You can specify isolation level, synchronization point parameters, and concurrency parameters using Application Manager, the Transactions tab of the Database Properties window in Administration Services, or ESSCMD. Changes to isolation level settings take effect the next time there are no active transactions. For information about deciding which settings to choose, see Isolation Levels.

Specifying Settings with Application Manager

To specify isolation level settings in Application Manager use this procedure:

  1. Select Database > Settings and click the Transaction tab.
  2. In the Isolation Level group, select Committed or Uncommitted.
  3. Specify Synchronization Point parameters or Concurrency parameters:
  4. Click OK.

For example, suppose you want committed access, pre-image access, and a 60-second wait for locked data blocks. Specify settings thus:

Figure 509: Example of Setting Committed Isolation Level

As another example, suppose that you want uncommitted access and that you want Essbase to perform a commit operation after 10 blocks are updated, or after 10 rows are loaded. Specify settings as in Figure 510:

Figure 510: Example of Setting Uncommitted Isolation Level

Specifying settings with ESSCMD

To specify isolation level settings using ESSCMD, enter SETDBSTATEITEM 18 in ESSCMD and either follow the prompts or supply the required values on the command line.

Choose 1 (committed access) or 2 (uncommitted access, the default). Depending on the type of access that you specify, ESSCMD prompts you for other parameters (or you can supply the values on the command line).

If you choose 1 (committed access), ESSCMD prompts for the following information:

If you choose 2 (uncommitted access), ESSCMD prompts for the following values. See Uncommitted Access for important details about these options.

You can also specify isolation level parameters (pre-image access and so on) by specifying parameters 19-22 on SETDBSTATEITEM. Enter SETDBSTATEITEM with no parameters; ESSCMD displays a list that includes each parameter by number, with a description.

Here is an example of using SETDBSTATEITEM to set an isolation level. This example enables committed access and pre-image access and specifies indefinite wait time.

SETDBSTATEITEM 18 "JILLAPP" "JILLDB" "1" "Y" "-1" 

For more syntax information, see the Technical Reference in the docs directory.

Specifying Settings with MaxL

To specify isolation level settings using MaxL, use this MaxL statement:

alter database dbs_name enable committed_mode  

For more information, see the Technical Reference in the docs directory, list of MaxL statements

Accommodating Data Redundancy

To ensure data integrity, the Essbase kernel temporarily retains redundant (duplicate) information. To accommodate redundant information, allow disk space for double the size of your database.

Essbase maintains a file called dbname.ESM, in which it stores crucial control information.

Caution: The dbname.TCT file, dbname.ESM file, the index files, and the data files contain information crucial for data recovery. Never alter or delete these files.

Checking Structural and Data Integrity

To validate database integrity and to check for database corruption, use one of the following methods:

If errors occur during any of these checks, you need to restore the database from backups. For more information, see Backing Up and Restoring Data.

Using VALIDATE to Check Integrity

The VALIDATE command performs many structural and data integrity checks:

As Essbase encounters mismatches, it records error messages in the VALIDATE error log file. You can specify a file name for error logging; Essbase prompts you for this information if you do not provide it. The VALIDATE utility continues running until it has checked the entire database.

You can use the VALIDATE command in ESSCMD to perform these structural integrity checks. See the Technical Reference in the docs directory for information about the command. See Automating the Production Environment for information about ESSCMD.

During index free space validation, the VALIDATE command verifies the structural integrity of free space information in the index. If integrity errors exist, Essbase records them in the VALIDATE log file. The file that you specified on the VALIDATE command holds the error log.

If VALIDATE detects integrity errors regarding the index free space information, the database must be rebuilt. You can rebuild in any one of three ways:

Even if you do not use VALIDATE, Essbase automatically performs certain validity checking whenever a read operation is performed, to ensure that the index is properly synchronized with the data.

For every read operation, Essbase compares the data block key in the index page with the data block key in the corresponding data block and checks other header information in the block.

If Essbase encounters a mismatch, it displays an "Invalid block header" error message.

Recovering from a Crashed Database

After a server interruption such as a crash, Essbase recovers a database, rolling back all transactions that were active when the interruption occurred. Recovery time depends on the size of the index. The larger the index, the longer it takes to recover the database. Essbase also recovers and consolidates free fragments (unused addressable units in the data blocks).

Essbase recovers data as soon as the server is started after a server interruption. Recovery consists of the following phases:

  1. Transaction recovery rolls back all transactions that were active when the interruption occurred.
  2. Index file recovery truncates files to their previously committed sizes.
  3. Data free space recovery rebuilds the data free space tables. The size of the index determines the duration of this phase.

Only a media failure (faulty disk, disk failure, or head crash) requires you to restore data from backups. For more information, see Backing Up and Restoring Data.

Caution: Do not move, copy, modify, or delete any of the following files: ESSxxxxx.IND, ESSxxxxx.PAG, dbname.IND, dbname.ESM, dbname.TCT. Doing so can result in data corruption.

The Essbase kernel uses fatal error handling to display appropriate messages and to shut down the server, depending on the error encountered. For more information, see Understanding Fatal Error Handling.

For information about how transactions are rolled back after a crash, see Committed Versus Uncommitted Access.

What to Expect If a Server Interruption Occurs

This table lists types of server interruptions and their results:

Table 46: Essbase Recovery Handling

Type of Interruption
Result

  • Power loss on server machine
  • Operating system crash
  • Server stopped with Ctrl + C keys

Server stops. When you restart the server, Essbase recovers the database.

  • Operation cannot complete due to system limitations
  • Memory shortage
  • Out of disk space
  • Application stopped with Ctrl+C keys

Essbase performs fatal error handling. You may need to allocate more memory or disk space and restart the server.

Server crash

Essbase Exception Manager creates error log file of type .XCP. Server stops. When you restart the server, Essbase recovers the database.



Table 47 shows what you need to do if a server interruption occurs during a transaction. How Essbase recovers from an interruption depends on the transaction isolation level setting (committed or uncommitted access). See Isolation Levels for more information.

Table 47: Recovery Procedures for Server Requests

Type of Request
Recommended Action

Lock
(for spreadsheet update)

Issue the lock command again.

Send
(spreadsheet update)

If Essbase issues an error, repeat the last send operation.

If the spreadsheet has been lost or does not exist, and if you are using SSAUDIT spreadsheet logging, reload the dbname.ATX file. See Spreadsheet Update Logging.

Calculation

Check the server and application even logs to see where the calculation left off. Decide whether to start the calculation over. Repeat the last calculation.

Data load

Repeat the last data load (see Performing and Debugging a Data Load) or load the error log file (see Optimizing Data Loads).

Arithmetic data load (adding to or subtracting from values in the database)

If the database is set to committed access, reload the data. (The transaction has been rolled back.)

If the database is set to uncommitted access, the process is not as simple. Some of the data loaded. Therefore, if you reload all of the data, you receive incorrect results for the data values that loaded twice. Therefore, you must perform the following actions:

  • Clear the database.
  • Restore the database to its state before the load.
  • Rerun the load.

See Performing and Debugging a Data Load and Optimizing Data Loads.

Restructure

The restructure is not complete. First, delete the temporary restructure files: .pan, .inn, and .otn. Repeat the last operation that caused a restructure.



Note: If the UPDATECALC parameter is set to FALSE, Essbase recalculates the entire database if an interruption occurs during a calculation. (The parameter's default value is TRUE.) For more information on UPDATECALC, see the Technical Reference in the docs directory.

Spreadsheet Update Logging

For extra protection against data loss and for spreadsheet audit information, Essbase provides a spreadsheet update logging facility. Enable this facility by using the SSAUDIT or SSAUDITR parameter in the ESSBASE.CFG server file. You can specify SSAUDIT for all databases on the server or for individual databases. For information on the ESSBASE.CFG file and for syntax information, see the Technical Reference in the docs directory.

Essbase handles recovery under normal situations. However, sometimes you may want to load the spreadsheet update log file manually. For example, if you have restored from a recent backup and do not want to lose changes made since the backup was made or you experience a media failure, you can recover transactions from the update log file. To do so, use the Essbase command-line facility, ESSCMD, from the server console.

The following ESSCMD command sequence loads the update log file:

LOGIN hostnode username password 
SELECT application_name database_name 
LOADDATA 3 filepath:application_name.ATX 
EXIT 

To simplify the process of loading the update log file, prepare a batch file as described in Automating the Production Environment.

When SSAUDIT or SSAUDITR is specified, Essbase logs spreadsheet update transactions chronologically. Essbase uses two files:

Both files are stored on the server.

The spreadsheet update log can get quite large; even if you are using SSAUDITR, Essbase clears the log only after you back up data. If spreadsheet update activities are frequent in your application, you may want to manually delete the log file periodically.

When a database is started after a shutdown, if spreadsheet logging is enabled, Essbase writes the following message to the database log file:

Starting Spreadsheet Log 
volumename\application_directory\application_name\ 
database_name\database_name.atx For Database database_name  

For example:

Starting Spreadsheet Log \ESSBASE\app\app1\sample\sample.atx for database sample 

To ensure successful spreadsheet update logging, stop and restart your application after either of the following:

Essbase ensures that if you enable spreadsheet logging, updates cannot take place without being logged. If Essbase cannot write to the update log for any reason, Essbase stops the transaction and issues an error message.

SSAUDIT and SSAUDITR are available only from the ESSBASE.CFG file.

Hybrid Analysis

Hybrid Analysis offers a means of integrating a relational database with a multidimensional database so that lower level members and their associated data remain in the relational database while upper level members and their associated data reside in the Essbase database. This presents additional issues regarding data consistency and integrity.

For information on ensuring that your data is correct in all locations, see Managing Data Consistency in Accessing Relational Data with Hybrid Analysis.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com