Overview Subsystem

• transaction limit reached

• lock contention

• memory limit reached

• high data buffer miss rate

• log full condition

Transaction Limit Reached

If a session attempts to begin a transaction, but the number of active transactions is at the maximum, the session must wait until a transaction slot becomes available. How long the session waits depends on the timeout value in effect for the session.

In the example that follows, the ISQL session times out because it cannot begin a new transaction:

  isql=> SET USER TIMEOUT 3;

  isql=> BEGIN WORK;

  Timeout expired (3 seconds).  (DBERR 2825)

The following screen shows that the transaction limit, MAX XACT, and the number of active transactions, ACTIVE XACT, are both 5.

Therefore, you may need to raise the transaction limit. Use the Load subsystem for more information.

Lock Contention

The next screen shows that there are 5 active transactions (ACTIVE XACT) and 4 impeded transactions (IMPEDE XACT). This means that 4 out of 5 transactions are waiting to acquire a lock.

We also see that 25% of all lock requests are not granted immediately because other sessions hold incompatible locks.

This DBEnvironment definitely has a locking problem. You should use the Lock subsystem to get more information.

Memory Limit Reached

The runtime control block is an area of shared memory containing global runtime information for the DBEnviroment. All ALLBASE/SQL control blocks are allocated from the runtime control block, and the majority of control blocks are used for lock management. For more information, see the section "Shared Memory Considerations" in the chapter "Guidelines on Transaction Design."

In the next screen, the runtime control block is 95% full.

You can either

After you have identified the sessions that have the most locks, use the Lock TabSummary screen to identify the programs each session is running and the tables that have the greatest number of locks. You may wish to change some PUBLICROW tables to PUBLIC to reduce the memory overhead associated with them.

High Data Buffer Miss Rate

If the value of the DATA BUFFER MISS RATE field on the Overview screen is high, DBEnvironment performance degrades due to increased I/O. You can use the IO subsystem to identify the sessions and programs that are contributing to the data buffer miss rate.

Log Full Condition

You can monitor log file capacity by checking the LOG FULL field on the Overview screen. In the example that follows, the archive log is 99% full.

If you enter the following UPDATE statement to update the PurchDB.SupplyPrice table, it fails because the log is so full:

  isql=> UPDATE PurchDB.SupplyPrice SET UnitPrice = UnitPrice * 1.2;

  Log full.  (DBERR 14046)

  INSERT/UPDATE/DELETE statement had no effect due to execution errors.

  (DBERR 2292)

  Number of rows processed is 0

You can avoid this problem by using the Overview screen to monitor log file capacity and by adding log files before the logs are full. See the chapter "Backup and Recovery" in the ALLBASE/SQL Database Administration Guide for instructions.