LIST INDOUBT TRANSACTIONS command

The LIST INDOUBT TRANSACTIONS command provides a list of transactions that are indoubt. The user can interactively commit, roll back, or forget the indoubt transactions.

The two-phase commit protocol comprises:

The PREPARE phase, in which the resource manager writes the log pages to disk, so that it can respond to either a COMMIT or a ROLLBACK primitive
The COMMIT (or ROLLBACK) phase, in which the transaction is actually committed or rolled back.

Forgetting a transaction releases resources held by a heuristically completed transaction (that is, one that has been committed or rolled back heuristically). An indoubt transaction is one which has been prepared, but not yet committed or rolled back.

Scope

This command returns a list of indoubt transactions on the executed node.

Authorization

None

Required connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command syntax

Command parameters

WITH PROMPTING

Indicates that indoubt transactions are to be processed. If this parameter is specified, an interactive dialog mode is initiated, permitting the user to commit, roll back, or forget indoubt transactions. If this parameter is not specified, indoubt transactions are written to the standard output device, and the interactive dialog mode is not initiated.

Interactive dialog mode permits the user to:

List all indoubt transactions (enter l)
List indoubt transaction number x (enter l, followed by a valid transaction number)
Quit (enter q)
Note: The transaction numbers are not persistent, so if you quit the interactive session and reissue the LIST INDOUBT TRANSACTIONS command, the transaction might be numbered differently.
Commit transaction number x (enter c, followed by a valid transaction number)
Roll back transaction number x (enter r, followed by a valid transaction number)
Forget transaction number x (enter f, followed by a valid transaction number).
Display help for the interactive session (enter h)

A blank space must separate the command letter from its argument.

Before a transaction is committed, rolled back, or forgotten, the transaction data is displayed, and the user is asked to confirm the action.

The LIST INDOUBT TRANSACTIONS command returns type information to show the role of the database in each indoubt transaction:

TM: Indicates the indoubt transaction is using the database as a transaction manager database.
RM: Indicates the indoubt transaction is using the database as a resource manager, meaning that it is one of the databases participating in the transaction, but is not the transaction manager database.

Usage notes

An indoubt transaction is a global transaction that was left in an indoubt state. This occurs when either the Transaction Manager (TM) or at least one Resource Manager (RM) becomes unavailable after successfully completing the first phase (that is, the PREPARE phase) of the two-phase commit protocol. The RMs do not know whether to commit or to roll back their branch of the transaction until the TM can consolidate its own log with the indoubt status information from the RMs when they again become available. An indoubt transaction can also exist in an MPP environment.

If LIST INDOUBT TRANSACTIONS is issued against the currently connected database, the command returns the information about the indoubt transactions in that database.

Only transactions whose status is indoubt (i), or missing commit acknowledgment (m), or missing federated commit acknowledgment (d) can be committed.

Only transactions whose status is indoubt (i), missing federated rollback acknowledgment (b), or ended (e) can be rolled back.

Only transactions whose status is committed (c), rolled back (r), missing federated commit acknowledgment (d), or missing federated rollback acknowledgment (b) can be forgotten.

In the commit phase of a two-phase commit, the coordinator node waits for commit acknowledgments. If one or more nodes do not reply (for example, because of node failure), the transaction is placed in missing commit acknowledgment state.

Indoubt transaction information is valid only at the time that the command is issued. After you are in interactive dialog mode, transaction status might change because of external activities. However, the output is not refreshed on the client if the status changes on the server. If this happens, and you attempt to process an indoubt transaction which is no longer in an appropriate state, an error message is displayed. You need to quit the interactive session and reissue the LIST INDOUBT TRANSACTIONS command with the WITH PROMPTING option to obtain the most current status.

In the following sample interactive session, the user commits an in-doubt transaction:

$  db2 list indoubt transactions with prompting

1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919165159     sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:51:59 auth_id: SMITH
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F93DD A92F8C4FF3000000
0000BD
 
Enter in-doubt transaction command or 'q' to quit.
e.g. 'c 1' heuristically commits transaction 1.
c/r/f/l/q: c 1
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919165159    sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:51:59 auth_id: SMITH
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F93DD A92F8C4FF3000000
0000BD

Do you want to heuristically COMMIT this in-doubt transaction ? (y/n) y
 
DB20000I "COMMIT INDOUBT TRANSACTION" completed successfully
 
c/r/f/l/q: c 5
 
DB20030E "5" is not a valid in-doubt transaction number.
 
c/r/f/l/q: l
 
In-doubt Transactions for Database SAMPLE
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919165159      sequence_no: 0001  status: c
      timestamp: 09/19/2013 16:51:59 auth_id: SMITH
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F93DD A92F8C4FF3000000
0000BD
 
c/r/f/l/q: q

In the following sample interactive session, the user is unsuccessful at rolling back in-doubt transaction because it is unlisted, and then succeeds at rolling back a valid in-doubt transaction:

$  db2 list indoubt transactions with prompting

1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043       sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
 
Enter in-doubt transaction command or 'q' to quit.
e.g. 'c 1' heuristically commits transaction 1.
c/r/f/l/q: r 5
 
DB20030E "5" is not a valid in-doubt transaction number.
 
c/r/f/l/q: l
 
In-doubt Transactions for Database SAMPLE
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043       sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
c/r/f/l/q: r 1
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043        sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
 
Do you want to heuristically ROLLBACK this in-doubt transaction ? (y/n) y
 
DB20000I "ROLLBACK INDOUBT TRANSACTION" completed successfully
 
c/r/f/l/q: l 1
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043         sequence_no: 0001  status: r
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
 
c/r/f/l/q: f 1
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043          sequence_no: 0001  status: i
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
  
Do you want to FORGET this in-doubt transaction ? (y/n) y
 
DB20000I "FORGET INDOUBT TRANSACTION" completed successfully
 
c/r/f/l/q: l 1
 
1.   originator:  Db2 Enterprise Server Edition
     appl_id: *LOCAL.DB2.130919161043         sequence_no: 0001  status: f
      timestamp: 09/19/2013 16:10:43 auth_id: JONES
      log_full: n type: RM
      xid: 53514C2000000017 00000000544D4442 00000000002F95FE B62F8C4FF3000000
0000C1
 
c/r/f/l/q: q

In the following sample interactive session, the user attempts to roll back an in-doubt transaction whose status has changed:

$  db2 list indoubt transactions with prompting

 1.   originator: Db2 Enterprise Server Edition
      appl_id: *LOCAL.DB2.130919175827       sequence_no: 0001 status: i
      timestamp: 09/19/2013 13:58:35 auth_id: CASTELLE
      log_full: n type: RM
      xid: 00001D3400000008 0000000000010000 00000030

c/r/f/l/h/q: r 1

 1.   originator: Db2 Enterprise Server Edition
      appl_id: *LOCAL.DB2.130919175827       sequence_no: 0001 status: i
      timestamp: 09/19/2013 13:58:35 auth_id: SMITH
      log_full: n type: RM
      xid: 00001D3400000008 0000000000010000 00000030

Do you want to heuristically ROLLBACK this in-doubt transaction? (y/n) y

SQL1725N Could not perform the specified action because the status of the indoubt 
transaction changed after you issued the LIST INDOUBT TRANSACTIONS command.
c/r/f/l/h/q: q

$  db2 list indoubt transactions with prompting

 1.   originator: Db2 Enterprise Server Edition
      appl_id: *LOCAL.DB2.130919175827        sequence_no: 0001 status: m
      timestamp: 09/19/2013 13:58:35 auth_id: CASTELLE
      log_full: n type: RM
      xid: 00001D3400000008 0000000000010000 00000030

c/r/f/l/h/q: c 1

 1.   originator: Db2 Enterprise Server Edition
      appl_id: *LOCAL.DB2.130919175827       sequence_no: 0001 status: i
      timestamp: 09/19/2013 13:58:35 auth_id: SMITH
      log_full: n type: RM
      xid: 00001D3400000008 0000000000010000 00000030

Do you want to heuristically COMMIT this in-doubt transaction? (y/n) y

DB20000I "COMMIT INDOUBT TRANSACTION" completed successfully

c/r/f/l/h/q: q