| Table of Contents | ||
|---|---|---|
|
Overview
...
1. Overview
...
When undo tablespace usage continues to increase .
Monitoring item
If the undo tablespace usage increases, the user should check the following two cases.
- Whether there is a change transaction that changes the data in the disk table
- Whether there is a query transaction looking at the old image created in the change transaction
Monitoring method by version
Altibase 5.3.3 ~ 7.3.1
Query for monitoring undo tablespace usage by transaction
With the monitoring query below, TX_STATUS finds and takes action for long-running transactions with BEGIN or END status.
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
Undo usage in use by change transactions
Last SQL statement and execution time of transaction
Whether to execute SQL statement
...
or does not decrease, it is necessary to accurately identify the root cause and take appropriate actions accordingly.
In general, undo usage increases or is not released in the following situations:
- Long-running update transactions: When DML transactions on disk tables do not terminate and continue for a long time
- Uncommitted transactions: When SQL execution is completed but the transaction remains open because a COMMIT or ROLLBACK has not been performed
- Undo image reference: When a query session references pre-change data, preventing the undo area from being reused and causing it to be retained for a long time
- LOB resources not released: When LOB data is queried in non-autocommit mode and the LOB cursor or transaction is not properly closed in a timely manner
- Ongoing rollback: When a large transaction is canceled and the recovery process takes a long time
This document describes key monitoring items to diagnose the causes of increasing undo usage and provides guidance on corrective and preventive actions for each scenario.
2. Monitoring Query
...
If the undo tablespace usage does not decrease or continues to increase, it is necessary to identify the transactions that are using undo or accessing undo images, along with their status.
The following query can be used to identify such transactions.
Altibase 5.3.3 or later
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT RPAD(DECODE(TX.LOG_TYPE, 1, REP.REP_NAME, TX.SESSION_ID), 10)AS SESSION_ID -- Session ID that performed the transaction. Or the replication object name , RPAD(TX.ID, 20)AS TX_ID -- Transaction ID , RPAD(DECODE(ST.ID, NULL, 'REP''S TX', ST.ID), 20) AS STATEMENT_ID -- STATEMENT ID , RPAD(DECODE(TX.TSS_RID, 0, 'SELECT', 'UPDATE'), 7)AS TX_TYPE -- Transaction type. SELECT: A transaction with only query statements. UPDATE: Transaction that changed data such as DELETE, UPDATE, etc. , RPAD(DECODE(TX.STATUS, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END'), 16)AS TX_STATUS -- Transaction status , RPAD(DECODE(ST.EXECUTE_FLAG, NULL, 'REP''S TX', 1, 'SQL ING', 0, 'SQL END'), 10) SQL_STATUS AS SQL_STATUS -- SQL status , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME||' PID:'||S.CLIENT_PID), 40)AS CLIENT_IP -- Client IP , S.CLIENT_PID AS CLIENT_PID -- Client IP and process ID , RPAD(DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF', NULL, 'REP''S TX'), 10) AUTOCOMMIT -- AUTOCOMMITClient modeprocess ID , RPAD(DECODE(TXS.LOGAUTOCOMMIT_TYPEFLAG, 1, 'REPON''S TX', S.UTRANS_TIME_LIMIT)0, 15'OFF') UTRANS_TIMEOUTAS AUTOCOMMIT -- SessionAUTOCOMMIT UTRANS_TIMEOUTMODE setting value , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') CURRENT_TIMES.UTRANS_TIME_LIMIT AS UTRANS_TIMEOUT -- UTRANS_TIMEOUT setting value of the session -- Current time , DECODE(ST.LAST_QUERY_START_TIME, NULL, TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS'), ST.LAST_QUERY_START_TIME) AS LAST_QUERY_START_TIME -- SQL statement start time , RPAD(LTRIM(TXM.SYS_MIN_DISK_VIEWSCN), 10) SYS_MIN_DISK_VIEWSCNCASE WHEN TX.DISK_VIEW_SCN LIKE 'INFINITE%' THEN '' ELSE LTRIM(TX.DISK_VIEW_SCN) END AS DISK_VIEW_SCN -- Minimum SCN viewingvisible atto the undoquery areatransaction , DECODE(CASE WHEN TX.MIN_DISK_LOB_VIEW_SCN, LIKE 'INFINITE(INFINITE%' THEN '' 0)', '-', ELSE LTRIM(TX.MIN_DISK_LOB_VIEW_SCN)) DISK_VIEW_SCN END AS MIN_DISK_LOB_VIEW_SCN -- Minimum SCN seen in viewing transaction , DECODE(TX.MIN_DISK_LOB_VIEW_SCN, 'INFINITE( 0)', '-', LTRIM(TX.MIN_DISK_LOB_VIEW_SCN)) MIN_DISK_LOB_VIEW_SCN -- Minimum SCN viewedvisible into athe transaction querying LOB data , TO_CHARROUND(((UD_S.TOTAL_EXTENT_COUNT * UD_S.PAGE_COUNT_IN_EXTENT * 8192) / 1024), '999,999,999'2) AS 'UNDO_USED(_KB)' -- Undo usage of the update transaction , ST.UNDO_READ_PAGE + ST.UNDO_GET_PAGE AS UNDO_PAGE_COUNT_COUNT -- Size of the undo area used in the change transaction. -- Undo pages , DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, LTRIM(ST.QUERY)) AS QUERY -- Last query executed by the transaction FROM V$TRANSACTION TX LEFT JOIN (SELECT SESSION_ID , TX_ID -- Last query performed by a transaction accessed, orID using undo FROM V$TRANSACTION TX LEFT OUTER JOIN (SELECT SESSION_ID , TX_ID , ID , TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + (LAST_QUERY_START_TIME) / (60*60*24), 'YYYY-MM-DD HH:MI:SS') LAST_QUERY_START_TIME, EXECUTE_FLAG, UNDO_READ_PAGE , UNDO_GET_PAGE , SUBSTR(QUERY, 1, 60) QUERY , FROMEXECUTE_FLAG V$STATEMENT WHERE (SESSION_ID , UNDO_READ_PAGE , TX_ID , UNDO_GET_PAGE , LAST_QUERY_START_TIME) IN (SELECT SESSION_ID FROM V$STATEMENT WHERE (SESSION_ID, TX_ID, LAST_QUERY_START_TIME) IN (SELECT SESSION_ID, TX_ID, MAX(LAST_QUERY_START_TIME) LAST_QUERY_START_TIME FROM V$STATEMENT FROM V$STATEMENT GROUP BY SESSION_ID GROUP BY SESSION_ID, TX_ID)) ST ON TX.ID = ST.TX_ID LEFT OUTER JOIN V$SESSION S ON TX.SESSION_ID = S.ID LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID LEFT OUTER JOIN V$REPRECEIVER REP ON REP_TX.REP_NAME = REP.REP_NAME LEFT OUTER JOIN V$TXSEGS TX_S ON TX.ID = TX_S.TRANS_ID LEFT OUTER JOIN V$UDSEGS UD_S ON TX_S.ID = UD_S.TXSEG_ENTRY_ID , V$TRANSACTION_MGR TXM WHERE 1=1 AND (TX.TSS_RID <> 0 OR ST.UNDO_READ_PAGE <> 0 OR ST.UNDO_GET_PAGE <> 0) ORDER BY SESSION_ID /* , TX_ID , LAST_QUERY_START_TIME; |
Output result
Below is an example output showing a change transaction using undo.
| Code Block | ||
|---|---|---|
| ||
iSQL> / SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP modified transaction */ OR TX.MIN_DISK_LOB_VIEW_SCN NOT LIKE 'INFINITE%' /* LOB access transaction */ AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN OR ((ST.UNDO_READ_PAGE <> 0 OR ST.UNDO_GET_PAGE <> 0) /* transaction accessing undo pages */ AND MIN_TX.DISK_LOB_VIEW_SCN NOT UNDO_USED(KBLIKE 'INFINITE%') ORDER BY QUERY ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 4 61397121 262147 UPDATE BEGIN SQL_END TCP 127.0.0.1:45076 PID:1 OFF 0 2015-03-11 12:27:11 2015-03-11 09:30:40 800767 - - 256 UPDATE LOB_T SET C3 = 'UN 11410 _DO TEST' 6 586566273 393216 UPDATE BEGIN SQL_ING TCP 127.0.0.1:45044 PID:1 OFF 3600 2015-03-11 12:27:11 2015-03-11 09:30:30 800767 800767 800767 68,864 UPDATE EMP SET JOIN_DATE= 11242 SYSDATE 2 rows selected. |
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 can be checked in the transaction 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.
UNDO_USED(KB) shows the size of undo used in the transaction in kbytes.
The following is the output result showing the query transaction viewing the pre-change data created in the undo area in the change transaction.
...
| language | sql |
|---|
...
SESSION_ID, TX_ID; |
3. Key Items to Check During Monitoring
...
When interpreting the monitoring results, analyze the following items with a focus to identify the root cause.
Evaluation Criteria
| Item | Description | Remarks |
|---|---|---|
| SESSION_ID | Identifier of the session executing the transaction. | For replication transactions, the replication object name is displayed. |
| STATEMENT_ID | Identifier of the SQL statement within the transaction. | NULL indicates that the statement has been released. |
| TX_TYPE | Transaction type. | UPDATE: Transaction that has performed at least one DML operation. SELECT: Read-only transaction |
| TX_STATUS | Transaction status. | BEGIN: Transaction is active. ABORT: Transaction is undergoing rollback. |
| SQL_STATUS | Status of the SQL statement. | SQL_ING: Statement is in execution. SQL_END: Execution has completed. NULL indicates that the statement has been released. |
| CLIENT_IP | Information about client connection type, IP, PORT | For replication, displays the remote server IP and replication port. NULL indicates that the session has ended. |
| CLIENT_PID | Client process ID | Client process ID. NULL indicates that the session has ended. |
| AUTOCOMMIT | AutoCommit mode of the session. | NULL indicates that the session has ended. |
| DISK_VIEW_SCN | SCN of the undo image visible to the transaction. | Indicates that a previous version of the data is being queried. |
| MIN_DISK_LOB_VIEW_SCN | SCN of the undo image used by the LOB cursor. | Indicates that the LOB cursor is open. |
| UNDO_USED_KB | Size of undo currently used by the query | Size of the undo tablespace used by the update transaction. |
| UNDO_PAGE_COUNT | Number of undo pages accessed by the query | Indicates that undo pages are being accessed. |
4. Diagnostic Criteria by Scenario
4.1 Long-Running Update Transactions
Update transactions generate undo records for pre-change data, resulting in increased undo usage.
Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| TX_TYPE | UPDATE | Indicates a transaction that performs data modification |
| TX_STATUS | BEGIN | Transaction is active (not yet committed or rolled back) |
| SQL_STATUS | SQL_ING | SQL statement is currently executing |
| UNDO_USED_KB | Increasing | Indicates growth in undo usage |
Example Output
The following example shows a SQL statement that modifies data and is currently in progress:
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 2 STATEMENT_ID TX_TYPEID TX_STATUS : 8766080 SQL_STATUS CLIENT_IPSTATEMENT_ID : 131072 AUTOCOMMIT UTRANSTX_TIMEOUTTYPE CURRENT_TIME : UPDATE LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN -- Transaction with data modification MIN_DISK_LOB_VIEW_SCNTX_STATUS UNDO_USED(KB) QUERY : BEGIN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 SQL_STATUS 7374720 : SQL ING 131072 -- SQL statement is in SELECT BEGIN SQL_ENDprogress CLIENT_IP : TCP 127.0.0.1:4517332102 CLIENT_PID:1 OFF : 123203 0 AUTOCOMMIT : OFF 2015-03-11 12:30:25 UTRANS_TIMEOUT 2015-03-11 09:31:43 3600 800783 LAST_QUERY_START_TIME - : 2025-12-02 16:30:47 DISK_VIEW_SCN : 962767 800783 SELECT * FROM LOB_T MIN_DISK_LOB_VIEW_SCN : 962767 UNDO_USED_KB : 41728 -- Undo usage increases while the update statement is executing UNDO_PAGE_COUNT : 1589737 QUERY : UPDATE dt SET c10 = SYSDATE |
4.2 Long-Running Open Update Transactions
If a transaction is not terminated after the SQL statement has completed, the undo segments used by the transaction are not released.
Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| TX_TYPE | UPDATE | Indicates a transaction that performs data modification |
| TX_STATUS | BEGIN | Transaction remains active (not yet committed or rolled back) |
| SQL_STATUS | SQL_END / NULL | SQL_END: Statement execution has completed NULL: Statement has been closed (released) |
| UNDO_USED_KB | Stable | No further increase after the modifying statement has completed |
Case 1: Statement completed, transaction still open
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 2 TX_ID : 121238766080 STATEMENT_ID : 131072 TX_TYPE : UPDATE -- Transaction with data modification TX_STATUS : BEGIN -- Transaction remains open SQL_STATUS : SQL END -- Statement execution has 5 1613185 327681 SELECT BEGIN SQL_INGcompleted CLIENT_IP : TCP 127.0.0.1:4425132102 CLIENT_PID:4 ON : 123203 3600 AUTOCOMMIT 2015-03-11 12:30:25 OFF 2015-03-11 09:33:42 UTRANS_TIMEOUT 800783 : 3600 800783 LAST_QUERY_START_TIME : 2025-12-02 16:30:47 DISK_VIEW_SCN : 800783 -- SCN is cleared after statement completion SELECT ENO, C_DATE MIN_DISK_LOB_VIEW_SCN : -- SCN is cleared after statement completion UNDO_USED_KB : 71168 -- Undo usage no longer increases after completion UNDO_PAGE_COUNT : 2003444 QUERY : UPDATE dt SET c10 = SYSDATE |
Case 2: Statement released, transaction still open
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 3 0252 TX_ID : 1862209 STATEMENT_ID : TX_TYPE : UPDATE -- Transaction with data modification TX_STATUS : BEGIN -- Transaction remains open SQL_STATUS : -- Statement FROMhas EMPbeen released 2 rows selected. iSQL> / SESSION_ID TX_IDCLIENT_IP : TCP STATEMENT_ID127.0.0.1:11046 CLIENT_PID : 206764 TX_TYPE TX_STATUS AUTOCOMMIT : OFF SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT : 3600 CURRENT_TIME LAST_QUERY_START_TIME : 2026-01-15 10:51:50 SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN : MIN_DISK_LOB_VIEW_SCN : UNDO_USED(_KB) QUERY : 256 UNDO_PAGE_COUNT : ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2QUERY 7374720 131072 SELECT BEGIN SQL_END TCP 127.0.0.1:45173 PID:1 OFF 0 2015-03-11 12:35:12 2015-03-11 09:31:43 800783 - : |
4.3 Statements Accessing Pre-Change Data for an Extended Period
Undo segments are not released while a query continues to access undo images generated by update transactions.
Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| TX_TYPE | UPDATE / SELECT | UPDATE: Transaction that has performed at least one modification in the past SELECT: Read-only transaction |
| SQL_STATUS | SQL_ING | Statement is currently executing |
| DISK_VIEW_SCN | SCN value | Indicates that a pre-update version of the data is being accessed |
| UNDO_USED_KB | NULL or stable | No increase in undo usage for read-only transactions or queries |
| UNDO_PAGE_COUNT | Increasing | Indicates active access to undo data If unchanged or 0, undo is not being accessed |
Example Output
Case 1: Read-only transaction in progress
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 9 TX_ID 800783 : 53804352 SELECT * FROM LOB_TSTATEMENT_ID : 589824 TX_TYPE : SELECT -- Read-only transaction TX_STATUS : BEGIN SQL_STATUS : SQL ING 12123 -- Statement is in execution CLIENT_IP : 1 rows selected. iSQL> / SESSION_ID TX_IDTCP 127.0.0.1:17731 CLIENT_PID : 190617 STATEMENT_ID AUTOCOMMIT : TX_TYPEON TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT : 0 CURRENT_TIME LAST_QUERY_START_TIME : 2025-12-05 14:12:24 SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN : 322663 MIN_DISK_LOB_VIEW_SCN : 322663 UNDO_USED(_KB) QUERY -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9 376196 589824 SELECT BEGIN SQL END TCP 127.0.0.1:21792 PID:2 OFF 3600 2015-03-11 16:00:13No undo usage for 2015-03-11 15:54:19 read-only transactions UNDO_PAGE_COUNT 800951: 35207 - - -- Increasing value indicates active undo access QUERY SELECT DEP_LOCATION FROM : SELECT * FROM TEST_DISK_TBL; |
Case 2: Query within an update transaction accessing undo
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID 20176 DEPT WHERE DNO BETWEEN ': 1 TX_ID : 3393 STATEMENT_ID : 65537 TX_TYPE : UPDATE -- Transaction that has performed modifications TX_STATUS : BEGIN SQL_STATUS : SQL ING -- Statement is in execution CLIENT_IP : 001' AND ' 1 rows selected. |
- 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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID TX_IDTCP 127.0.0.1:46339 CLIENT_PID : 102737 STATEMENT_ID AUTOCOMMIT TX_TYPE : OFF TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT : 3600 CURRENT_TIME LAST_QUERY_START_TIME : 2026-01-15 11:13:24 SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN : 801111 MIN_DISK_LOB_VIEW_SCN : 801111 UNDO_USED(_KB) QUERY ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- REP1 4417 REP'S TX UPDATE BEGIN REP'S TX REP 192.168.1.146:55007 71168 REP'S TX REP'S TX 2015-03-10 12:35:12Undo usage remains stable during query execution 2015-03-10 11:37:40 800791 800791 800791 8,192 REMOTE_TX_ID : 58753 UNDO_PAGE_COUNT : 3024517 -- Increasing value indicates active undo access QUERY : SELECT * 1 row selected. |
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.
Monitoring query for continuous increase in undo tablespace usage
If long-running transactions are not being displayed and no transactions using the undo tablespace are identified, yet the undo tablespace usage continues to increase,
you can identify transactions that are not updating the SYS_MIN_DISK_VIEWSCN — the threshold for undo tablespace reuse — using the monitoring query below, and take appropriate action.
When recovering (rolling back) a transaction, the undo tablespace stores recovery data (data before the transaction execution) to ensure data consistency.
Since it cannot delete any recovery data generated after the SYS_MIN_DISK_VIEWSCN, it consumes additional space, causing continuous growth in undo tablespace usage.
From the query results, you can check the following information:
Whether there are transactions using the undo area
The SESSION ID causing increased undo tablespace usage
The Client Process ID causing increased undo tablespace usage
The last SQL statement executed by the transaction and its execution time
| No Format | ||||
|---|---|---|---|---|
| ||||
SET VERTICAL ON;
SELECT * FROM X$TRANSACTION_MANAGER; |
Query Output (Results may vary depending on the timing)
Below is the query result for checking the reference value SYS_MIN_DISK_VIEWSCN, which is used to identify problematic transactions.
If this value does not change even after executing the query multiple times in succession, it may indicate an issue.
| Code Block | ||
|---|---|---|
| ||
TOTAL_COUNT : 1024
FREE_LIST_COUNT : 32
BEGIN_ENABLE : 1
ACTIVE_COUNT : 3
SYS_MIN_DISK_VIEWSCN : 12345678 ---> If the value does not change, you need to identify the session(s) currently accessing that SCN
1 rows selected. |
| No Format | ||||
|---|---|---|---|---|
| ||||
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') NOW, a.ID SESSION_ID,FROM dt |
4.4 Open LOB Access in Non-Autocommit Mode
In non-autocommit mode, if a transaction is not completed after querying LOB data, or if only the statement is released without closing the LOB cursor, the associated undo segments are not released.
This can result in continuously increasing undo usage.Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| TX_STATUS | BEGIN | Transaction remains active (not yet committed or rolled back) |
| MIN_DISK_LOB_VIEW_SCN | SCN value | Indicates that a LOB cursor is still open |
Example Output
Case 1: Transaction remains open after querying LOB data
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID a.TRANS_ID, : 4 a.COMM_NAME, a.CLIENT_PID,TX_ID DECODE(a.IDLE_START_TIME, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.IDLE_START_TIME/(24*60*60), 'mm/dd hh:mi:ss')) IDLE_START_TIME, 900929 b.DISK_VIEW_SCN DISK_VIEW_SCN, b.MIN_DISK_LOB_VIEW_SCN MIN_DISK_LOB_VIEW_SCN,STATEMENT_ID b.COMMIT_SCN COMMIT_SCN: 589824 FROM V$SESSION a, V$TRANSACTIONTX_TYPE b WHERE a.ID = b.SESSION_ID AND b.MIN_DISK_LOB_VIEW_SCN LIKE '%12345678%' AND (b.DISK_VIEW_SCN >= (SELECT SYS_MIN_DISK_VIEWSCN : SELECT TX_STATUS : BEGIN FROM X$TRANSACTION_MANAGER) -- Transaction OR b.MIN_DISK_LOB_VIEW_SCN >= (SELECT SYS_MIN_DISK_VIEWSCNremains open SQL_STATUS : SQL END FROM X$TRANSACTION_MANAGER) -- OR b.COMMIT_SCN >= (SELECT SYS_MIN_DISK_VIEWSCNStatement execution has completed CLIENT_IP : FROM X$TRANSACTION_MANAGER)) ; |
Query Output (Results may vary depending on the timing)
Below is the query result used to identify problematic transactions based on SYS_MIN_DISK_VIEWSCN.
To free up space in the undo tablespace, you may need to forcefully terminate the session(s) identified in the result.
| Code Block | ||
|---|---|---|
| ||
NOW : 2025-09-01 16:40:06 SESSION_IDTCP 127.0.0.1:49199 CLIENT_PID : 63170256 AUTOCOMMIT ---> Execute the SESSION CLOSE statement using the identified value TRANS_ID : 555506432OFF COMM_NAMEUTRANS_TIMEOUT : TCP 127.0.0.1:59610 CLIENT_PID : 233110 IDLELAST_QUERY_START_TIME : 09/01 16:04:46 2025-12-05 14:12:24 DISK_VIEW_SCN : 1066960761 MIN_DISK_LOB_VIEW_SCN : 1066960761324055 COMMIT_SCN : INFINITE( 0 ) -- Presence of SCN indicates LOB data access UNDO_USED_KB : UNDO_PAGE_COUNT : QUERY : SELECT * 1 rows selected. |
Altibase 5.1.5 or earlier
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
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT RPAD(DECODE(TX.LOG_TYPE, 1, REP.REP_NAME, ST.SESSION_ID), 10) SESSION_IDFROM demo_clob; |
Case 2: Statement released, but transaction and LOB cursor remain open
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 4 -- Session ID that performed the transaction. , RPAD(TX.ID, 20) TX_ID : 900929 -- Transaction ID , RPAD(DECODE(ST.ID, NULL, 'REP''S TX', ST.ID), 20) STATEMENT_ID : -- STATEMENTNULL IDindicates statement has been released , RPAD(DECODE(TX.TSS_RID, 0, 'SELECT', 'UPDATE'), 7) TX_TYPE : SELECT TX_STATUS -- Transaction type. SELECT: A transaction with only query statements. UPDATE: Transaction that changed data such as DELETE, UPDATE, etc.: BEGIN , RPAD(DECODE(TX.STATUS, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END'), 16) TX_STATUS -- Transaction statusremains open , RPAD(DECODE(ST.EXECUTE_FLAG, NULL, 'REP''S TX', 0, 'SQL_END', 1, 'SQL_ING'), 16) SQL_STATUS : -- SQLStatement statementhas statusbeen , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME||' PID:'||S.CLIENT_PID), 40) CLIENT_released CLIENT_IP : -- Client IP and process ID , RPAD(DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF', NULL, 'REP''S TX'), 10) AUTOCOMMIT -- AUTOCOMMIT mode , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP''S TX', S.UTRANS_TIME_LIMIT), 15) UTRANS_TIMEOUTTCP 127.0.0.1:49199 CLIENT_PID : 170256 AUTOCOMMIT : OFF -- Session UTRANS_TIMEOUT setting value , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') CURRENT_TIME 0 -- Current time , DECODE(ST.LAST_QUERY_START_TIME, NULL, TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS'), ST.LAST_QUERY_START_TIME) LAST_QUERY_START_TIME -- SQL statement start time , DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, ST.QUERY) QUERY: DISK_VIEW_SCN : MIN_DISK_LOB_VIEW_SCN : 324055 -- LastPresence queryof performedSCN byindicates aLOB transactiondata accessed or using undo FROM V$TRANSACTION TX LEFT OUTER JOIN (SELECT SESSION_ID , TX_ID , ID , TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + (LAST_QUERY_START_TIME) / (60*60*24), 'YYYY-MM-DD HH:MI:SS') LAST_QUERY_START_TIME , UNDO_READ_PAGE , UNDO_GET_PAGE , EXECUTE_FLAG, SUBSTR(QUERY, 1, 60) QUERY FROM V$STATEMENT WHERE (SESSION_ID , TX_ID , LAST_QUERY_START_TIME) IN (SELECT SESSION_IDaccess UNDO_USED_KB : UNDO_PAGE_COUNT : QUERY , TX_ID , MAX(LAST_QUERY_START_TIME) LAST_QUERY_START_TIME FROM V$STATEMENT GROUP BY SESSION_ID , TX_ID)) ST ON TX.ID = ST.TX_ID LEFT OUTER JOIN V$SESSION S ON ST.SESSION_ID = S.ID LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID LEFT OUTER JOIN V$REPRECEIVER REP ON REP_TX.REP_NAME = REP.REP_NAME WHERE 1=1 AND TX.STATUS <> 6 AND (TX.TSS_RID <> 0 OR ST.UNDO_READ_PAGE <> 0 OR ST.UNDO_GET_PAGE <> 0) ORDER BY SESSION_ID , TX_ID , LAST_QUERY_START_TIME; |
Output result
Below is an example output showing a change transaction using undo.
| Code Block | ||
|---|---|---|
| ||
iSQL> / SESSION_ID TX_ID: |
4.5 Transactions Undergoing Rollback
During rollback, undo segments are used to restore data to its previous state. Undo usage may be retained until the rollback operation completes.
Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| STATEMENT_ID | Numeric / NULL | Numeric: Session is active NULL: Rollback continues after session termination |
| TX_TYPE | UPDATE | Transaction that performs data modifications |
| TX_STATUS | ABORT | Transaction is in rollback |
Example Output
Case 1: Explicit rollback execution
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 3 STATEMENT_ID TX_TYPEID TX_STATUS SQL_STATUS : 155968 CLIENT_IP STATEMENT_ID AUTOCOMMIT UTRANS_TIMEOUT : 196609 CURRENT_TIME LAST_QUERY_START_TIMETX_TYPE QUERY : UPDATE -- Update transaction ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 453698TX_STATUS 876975109 : ABORT 0 -- Rollback in progress SQL_STATUS UPDATE BEGIN : SQL ING SQLCLIENT_INGIP SOCKET-INET-SERVER : TCP 127.0.0.1:56757 CLIENT_PID OFF 0 : 185015 2015-03-11 14:21:38AUTOCOMMIT 2015-03-11 14:07:11 OFF UPDATE EMP SET JOIN_DATE = SYSUTRANS_TIMEOUT : 0 LAST_QUERY_START_TIME : 2025-12-05 12:03:18 DISK_VIEW_SCN : MIN_DISK_LOB_VIEW_SCN : UNDO_USED_KB : 285696 UNDO_PAGE_COUNT : 4045648 QUERY PID:12959: rollback |
Case 2: Session terminated during rollback
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : 3 TX_ID : 155968 STATEMENT_ID : TX_TYPE : UPDATE DATE -- Update transaction TX_STATUS : ABORT 453832 34379778 -- Rollback in progress 0 UPDATE BEGIN SQL_ENDSTATUS SOCKET-INET-SERVER : 127.0.0.1 OFF CLIENT_IP 0 : 2015-03-11 14:21:38 2015-03-11 14:06:32Session has been terminated UPDATE LOB_T SET C1 = 'T'CLIENT_PID : AUTOCOMMIT : UTRANS_TIMEOUT : LAST_QUERY_START_TIME : 2025-12-05 12:03:18 DISK_VIEW_SCN : MIN_DISK_LOB_VIEW_SCN : UNDO_USED_KB : 285696 UNDO_PAGE_COUNT PID:13015 4045648 QUERY 2 rows selected. |
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.
| Code Block | ||
|---|---|---|
| ||
iSQL> / SESSION_ID TX_ID: |
4.6 Replication Transactions
Undo may also be consumed by replication transactions.
Evaluation Criteria
| Item | Value | Description |
|---|---|---|
| SESSION_ID | Replication object name | Indicates a replication transaction |
| CLIENT_IP | Remote server IP | Displays the IP address and port of the remote server |
| TX_STATUS | BEGIN | Transaction is in progress |
| QUERY | REMOTE_TX_ID | Identifier of the source transaction |
Example Output
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID : REP STATEMENT_ID TX_TYPE TX_STATUS -- Replication object name is SQLdisplayed TX_STATUSID CLIENT_IP : 12354 AUTOCOMMIT UTRANS_TIMEOUT CURRENTSTATEMENT_TIMEID : LAST_QUERY_START_TIMETX_TYPE QUERY : UPDATE TX_STATUS : BEGIN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 462187 34470914 0 SELECT BEGIN SQL_INGSQL_STATUS SOCKET-INET-SERVER : 127.0.0.1 OFF 0 2015-03-11 16:31:28 2015-03-11 16:28:27 SELECT JOIN_DATE FROM EMP CLIENT_IP PID:16067 1 row selected. |
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.
| Code Block | ||
|---|---|---|
| ||
iSQL> / SESSION_ID TX_IDREP 192.168.1.145:53950 CLIENT_PID : STATEMENT_ID TX_TYPE TX_STATUS AUTOCOMMIT SQL_STATUS : CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME : LAST_QUERY_START_TIME QUERY ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- REP: 2025-12-05 17:22:31 DISK_VIEW_SCN 16617476 : 161183 MIN_DISK_LOB_VIEW_SCN : REP'S161183 TX UNDO_USED_KB : 19456 UPDATE UNDO_PAGE_COUNT BLOCKED REP'S TX REP 192.168.1.145:27206 18793 QUERY REP'S TX REP'S TX 2015-03-11 14:21:38 2015-03-11 14:21:34 REMOTE_TX_ID : 3875 1 rows selected. |
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
53835072 |
5. Corrective Actions
When undo tablespace usage reaches a critical level, immediate actions can be categorized into two approaches: expanding physical storage and removing the root cause (session/transaction).
5.1 Expanding the Undo Tablespace
If insufficient undo space may impact service availability, increase the undo tablespace capacity.
Before proceeding, ensure that sufficient free space is available in the underlying file system.
The following methods can be used:
Method A: Add a Data File
Additional space can be secured by adding a new data file to the existing tablespace.
First, check the current data file configuration:
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- 1. Check current data file configuration SELECT RPAD(T.NAME, 20) SPACE_NAME , RPAD(ROUND((D.INITSIZE*T.PAGE_SIZE)/1024/1024) AS 'INITSIZE(MB)' , ROUND((D.CURRSIZE*T.PAGE_SIZE)/1024/1024) AS 'CURRSIZE(MB)' , DECODE(D.AUTOEXTEND, 0, 'OFF', 1, 'ON'), 10) AS AUTOEXTEND 'AUTOEXTEND' , ROUND((D.NEXTSIZE*T.PAGE_SIZE)/1024/1024) AS 'NEXTSIZE(MB)' , DECODE(D.MAXSIZE, 0, ROUND((D.CURRSIZE*T.PAGE_SIZE)/1024/1024), ROUND((D.MAXSIZE*T.PAGE_SIZE)/1024/1024)) AS 'MAXSIZE(KBMB)' , D.NAME DATAFILE , DECODE(D.AUTOEXTEND, 0, 'OFF', 1, 'ON') 'AUTOEXTEND' FROM V$TABLESPACES T, V$DATAFILES D WHERE T.ID = D.SPACEID AND T.TYPE = 7 ORDER BY 1, 2 7; -- 2. Example: ofadd executiona resultdata iSQL>file /ALTER SPACE_NAME AUTOEXTEND MAXSIZE(KB) DATAFILE AUTOEXTEND ------------------------------------------------------------------------------------------------------------------------------ TABLESPACE SYS_TBS_DISK_UNDO ADD ON 2048 /data/heejung.lee/63138/dbs/undo001.dbf ON 1 row selected. | ||||||
| Panel | ||||||
| ||||||
| ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'datafile_name' AUTOEXTEND ON NEXT 1M MAXSIZE 2G; -- Example ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE ' DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 2G; |
| Panel | ||||
|---|---|---|---|---|
| ||||
ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'datafile_name' SIZE 2G ; iSQL> ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'undo002.dbf' SIZE 2G; |
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.
| title | How to add data file: When AUTOEXTEND is ON |
|---|---|
| borderColor | dashed |
-- Example
Method B: Resize an Existing Data File
Capacity can also be increased by extending the size or maximum size of an existing data file.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
-- 1. Example: When AUTOEXTEND is ON ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo001.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE4G; |
| Panel | ||||
|---|---|---|---|---|
| ||||
ALTER TABLESPACE 4G; -- 2. Example: When AUTOEXTEND is OFF ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE'datafile_name' SIZE 4G; -- Example ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE ' 'undo001.dbf' SIZE 4G; |
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
Panel title SQL statement iSQL(sysdba)> ALTER DATABASE database_name SESSION CLOSE session_id ; 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.
Code Block language sql SELECT DB_NAME FROM V$DATABASE;
Terminate the client process
Terminate normally by the method provided by the client program, or by kill command.Panel $ kill -9 process_id
Avoiding long-running transactions
5.2 Terminating Sessions
If storage expansion is not feasible, or if a specific session is identified as the cause of excessive undo usage, the session can be terminated.
Warning
Terminating a DML session processing a large volume of data may trigger rollback.
In such cases, undo space may not be released immediately and will only be reclaimed after the rollback completes.
Impact by Transaction Type
| Transaction Type | Impact on Termination | Resource Release Timing |
|---|---|---|
| SELECT | Can be terminated immediately | Immediately upon session termination |
| DML | Rollback is performed | After rollback completes |
Terminating a Session
After identifying the database name from V$DATABASE, terminate the target session using the SESSION_ID obtained from the monitoring query.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
ALTER DATABASE database_name SESSION CLOSE session_id ; |
Terminating a Client Process
If the database session cannot be terminated normally or the connection must be closed at the application level, terminate the client process at the OS level.
| Code Block | ||||
|---|---|---|---|---|
| ||||
$ kill -9 <process_id> |
The process_id can be obtained from the CLIENT_IP field in the monitoring query.
6. Preventing Undo Tablespace Growth
Long-running transactions (bulk transactions) can cause the following problems.
| Note | ||
|---|---|---|
| ||
Bulk transactions (transactions that run for a long time) should be avoided as much as possible, as they can cause the following problems.
|
are a primary cause of increased undo tablespace usage, as changes remain uncommitted for an extended period.
When large data modifications are processed within a single transaction, the following issues may occur:
- Rapid growth of undo space: Undo segments for before images can increase significantly in a short period
- Delayed space reuse: Undo cannot be reused until the transaction is completed (COMMIT or ROLLBACK)
- Increased rollback overhead: In case of failure or cancellation, a larger volume of undo must be processed, potentially resulting in rollback time exceeding the original execution time
To mitigate these issues, review the following recommendations and optimize transaction handling accordingly.
6.1 Splitting Large Transactions
Instead of processing a large amount volume of data in one a single transaction, divide it the workload into smaller units.
batches.
This approach improves undo reuse and reduces overall undo pressure.
In addition, it minimizes the scope of recovery required in case of rollback.
Example
Method 1: Using Range Conditions (BETWEEN ~ AND)
Divide the workload into ranges based on a primary key or indexed column, and commit after each batch.
| Code Block | ||||
|---|---|---|---|---|
| ||||
iSQL>-- SELECT COUNT(*) FROM EMP; COUNT ----------------------- 1000000 1 row selected. iSQL> DELETE FROM EMP ; iSQL> COMMIT: |
| Code Block | ||||
|---|---|---|---|---|
| ||||
DELETE FROM EMP WHERE ENO BETWEEN 1 AND 1000;
COMMIT;
DELETE FROM EMP WHERE ENO BETWEEN 1001 AND 2000;
COMMIT;
DELETE FROM EMP WHERE ENO BETWEEN 1001 AND 3000;
COMMIT;
…
|
2. UTRANS_TIMEOUT setting
Make sure to set UTRANS_TIMEOUT to a non-zero value.
Set this value to prevent problems that may occur as the execution time of a transaction that performs change operations (UPDATE, INSERT, DELETE) increases.
If the execution time is greater than the property value, the session is forcibly terminated and the transaction is rolled back.
How to change the session unit
Use the statement below,
Delete rows 1–1000 and commit
DELETE FROM emp WHERE eno BETWEEN 1 AND 1000;
COMMIT;
-- Delete rows 1001–2000 and commit
DELETE FROM emp WHERE eno BETWEEN 1001 AND 2000;
COMMIT;
-- Delete rows 2001–3000 and commit
DELETE FROM emp WHERE eno BETWEEN 2001 AND 3000;
COMMIT;
|
Method 2: Using Row Limiting (LIMIT)
Repeatedly delete a fixed number of rows that match a condition.
| Code Block | ||||
|---|---|---|---|---|
| ||||
-- Delete up to 1,000 rows per execution (repeat as needed)
DELETE FROM emp WHERE status = 'D' LIMIT 1000;
COMMIT; |
6.2 Configuring UTRANS_TIMEOUT
If update transactions remain active for an extended period, undo images are retained longer, which can lead to increased undo usage.
To mitigate this, configure UTRANS_TIMEOUT so that transactions exceeding a defined duration are automatically terminated and rolled back.
Session-Level Configuration
iSQL example
| Code Block | ||||
|---|---|---|---|---|
| ||||
ALTER SESSION SET UTRANS_TIMEOUT = 3600; |
-- |
Or, UTRANS_TIMEOUT property can be used in the connection string.
단위: 초 |
Connection string examples
Java
Code Block theme DJango language java
sProps.put(
"utrans_timeout",
"300"); Connection
con = DriverManager.getConnection(
url, sProps
);
APRE
Code Block
theme
DJango language cpp strcpy(conn_opt3, "DSN=localhost;CONNTYPE=1;UTRANS_TIMEOUT=300");
EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :conn_opt3;
How to change the system unit
If the following statement is executed during database operation, the changed value will be applied to the subsequent sessions.
System-Level Configuration
Executing the following statement applies the setting to all new sessions:
| Code Block | ||||
|---|---|---|---|---|
| ||||
ALTER SYSTEM SET UTRANS_TIMEOUT = 3600; |
-- |
unit |
The value changed to ALTER SYSTEM is initialized to the value set in altibase.properties when the ALTIBASE HDB server is restarted. In order to apply it permanently, the altibase.properties file must also be modified.
: seconds |
For persistent configuration, update the properties file:
| Code Block | ||||
|---|---|---|---|---|
| ||||
$ vi $ALTIBASE_HOME/conf/altibase.properties
UTRANS_TIMEOUT = 3600 |
3. Check whether a commit is performed
Transactions executed in6.3 Checking for Uncommitted Transactions
In applications using non-autocommit mode
must be committed or rolled back to end the transaction.In the application, the transaction is executed in the non-autocommit mode and check if there is a part that does not perform commit or rollback, and if there is a part that is handled incorrectly, it must be corrected.
, ensure that transactions are not left open (idle) due to missing exception handling or incomplete logic.
At the end of business logic, a COMMIT or ROLLBACK must always be explicitly issued.
6.4 Releasing LOB Resources
LOB cursors created during LOB operations retain undo data until the transaction is completed or the cursor is closed.
After processing LOB data, ensure that one of the following actions is performed:
- Close the LOB cursor
- Complete the transaction (COMMIT or ROLLBACK)
Warning
TheSQLFreeLob()function in SQLCLI does not immediately send a request to the server.
Instead, it is transmitted together with subsequent requests.
As a result, there may be a delay between the client-side call and the actual release of LOB resources on the server.If immediate resource release is required, completing the transaction is the most reliable approach.