• If TX_TYPE is SELECT, it means a transaction that has never performed a change statement.
• SELECT statements that do not contain LOB data will no longer look at the undo image after fetching data from the database.
  - Transactions in Autocommit-Mode disappear from the result when the fetch is complete. (Refer to TX_ID 1613185 in Session 5. The SCN output in DISK_VIEW_SCN means that LOB data is not included.)
  - In non-autocommit-mode transactions, both DISK_VIEW_SCN and MIN_DISK_LOB_VIEW_SCN are out as-after fetch is finished and until commit is executed. (TX_ID 376196 of session 9)
• SELECT statements including LOB data look at the undo image until commit or rollback is executed even when fetch is terminated.
  If DISK_VIEW_SCN-and SCN is output in MIN_DISK_LOB_VIEW_SCN, it means a transaction that queries LOB data. (TX_ID 7374720 of session 2)
  In the case of LOB data, there is information called lob cursor. When this is open, the undo image can be found. The lob cursor is closed only by commit or rollback.
  Transactions in this state can cause increased undo usage.

The following is the result showing a replication transaction using the undo area.

• The IP of the server that sent the transaction with CLIENT_IP can be checked.

• The name of the redundant object with SESSION_ID can be found.

• The value of REP'S TX means a value that cannot be checked locally. (Section and SQL information of a redundant transaction cannot be checked locally)

• The TX_ID of the remote server can be found with REMOTE_TX_ID output on QUERY.

ALTIBASE HDB 4.3.9, 5.1.5

Query for monitoring undo tablespace usage by transaction

Find and take action on transactions that are using or accessing undo with the monitoring query below.

The following information can be checked in the query result.

• Session information using or accessing the undo tablespace. (Autocommit mode, Client IP and Process id, UTRANS_TIMEOUT setting value)

• Whether or not there is a change transaction using the undo realm

• Whether there is a query transaction accessing the undo area

• Last SQL statement and execution time of transaction

• Whether to execute SQL statement

Output result

Below is an example output showing a change transaction using undo.

• If TX_TYPE is UPDATE, it means that a data change operation occurred at least once in that transaction.

• If SQL_STATUS is SQL_END, it means that the SQL statement executed in the transaction has ended, but the transaction has not. (TX_ID 61397121 of session 4 in the results below)

• If SQL_STATUS is SQL_ING, it means that the SQL statement executed in the transaction is being executed. (TX_ID 61397121 of session 6 in the results below)

• The start time of the last SQL statement executed in the transaction can be checked with LAST_QUERY_START_TIME. The difference from the current time allows you to determine how long the transaction is running.

• UTRANS_TIMEOUT means the maximum amount of time (in seconds) a change transaction can perform.

The following is the output result showing the inquiry transaction viewing the pre-change data created in the undo area in the change transaction.

• If TX_TYPE is SELECT, it means a transaction that has never performed a change statement.

The following is the result showing a replication transaction using the undo area.

• The IP of the server that sent the transaction can be checked with CLIENT_IP.

• The name of the replication object can be checked with SESSION_ID.

• The value of REP'S TX means a value that cannot be checked locally. (Section and SQL information of a redundant transaction cannot be checked locally)

• The TX_ID of the remote server can be checked with REMOTE_TX_ID printed on QUERY.

Solution

Increasing the size of the undo tablespace

The data file must be added for the undo tablespace or the size of an existing data file must be expanded.

One of the things the user can do when the undo usage is increasing.

Adding data file

After checking the existing data file name, add the data file according to the existing naming rules.

Extending the size of an existing data file

After checking the name and maximum size of the existing data file, set the maximum size larger than the current value.

Check the data file name and maximum size using the query above.

The blue italic part can be changed according to the user environment. For syntax explanation, refer to the SQL Reference at http://support.altibase.com/en/manual.

Forcibly terminating session

Terminate sessions that use the undo tablespace for a long time.

This is one of the things that can be done when the undo usage is increasing.

Even if the session is forcibly terminated, the transaction termination point may be different depending on the type of transaction.

• In the case of a query transaction, the transaction ends as soon as the session terminates.
• In the case of a change transaction, the transaction is terminated after the rollback is performed, so it may take as long as the minimum transaction progressed before the increase in undo usage is corrected.

How to terminate session

• Forcibly terminate session in ALTIBASE HDB

  SQL statement

  iSQL> connect as sysdba
  Write UserID : sys
  Write Password :
  Connect success.
  iSQL(sysdba)>
  iSQL(sysdba)> ALTER DATABASE database_name SESSION CLOSE session_id ;

  The above statement can only be executed by the sysdba user.

  For session_id, enter the SESSION_ID value from the result of 'Undo tablespace usage monitoring query per transaction', and check the database_name with the query below.

• Terminate the client process
  Terminate normally by the method provided by the client program, or by kill command.

  $ kill -9 process_id

Avoiding long-running transactions

Long-running transactions (bulk transactions) can cause the following problems.

1. Process by dividing into small units of transaction

Instead of processing a large amount of data in one transaction, divide it into smaller units.