DB_TRANSACTION_INFO view

The DB_TRANSACTION_INFO view returns one row for each commitment definition.

The values returned for the columns in the view are similar to the values returned by the Work with Commitment Definitions (WRKCMTDFN) CL command and by the Database Transactions and Global Transactions lists in ACS.

Authorization: Start of change None required when the creator of the commitment definition matches the effective user of the thread. End of change Otherwise, the caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage identifier.

The following table describes the columns in the view. The system name is TRANS_INFO. The schema is QSYS2.

Table 1. DB_TRANSACTION_INFO view
Column Name	System Column Name	Data Type	Description
COMMITMENT_DEFINITION	COMMIT_DFN	VARCHAR(10)	The name of the commitment definition.
COMMITMENT_DEFINITION_ DESCRIPTION	TEXT	VARCHAR(50) Nullable	The text description of the commitment definition. Contains the null value if there is no text description.
COMMITMENT_DEFINITION_ID	CMTDEF_ID	VARCHAR(10) FOR BIT DATA	The ID associated with the commitment definition. Can contain the following special values: DFTACTGRP This is the commitment definition for the default activation group. JOB The commitment definition is shared by activation groups within a job.
JOB_NAME	JOB_NAME	VARCHAR(28)	The qualified job name that is using commitment control.
THREAD_ASSOCIATION_STATUS	THD_STATUS	VARCHAR(11) Nullable	The thread association status of the TNSOBJ scoped commitment definition. ATTACHED There are threads attached to the TNSOBJ scoped commitment definition. UNATTACHED There are no threads attached to the TNSOBJ scoped commitment definition. Contains the null value if the commitment definition is not a TNSOBJ scoped commitment definition.
LOGICAL_UNIT_OF_WORK_ID	LUWID	VARCHAR(39)	The identifier (ID) of the current logical unit of work (LUW). This value can be used to find associated commitment definitions on this system and on other systems. The logical unit of work ID (LUWID) is a character string containing the network-qualified logical unit (LU) name, the instance number, and the sequence number. The network-qualified LU name consists of a network ID with a maximum of 8 characters, a period delimiter, followed by a LU name with a maximum of 8 characters. The instance number is a 12 character value, each character representing a single hexadecimal digit. The sequence number is a decimal value with values ranging from 1 through 65535.
LOGICAL_UNIT_OF_WORK_STATE	LUW_STATE	VARCHAR(20)	The current state of the logical unit of work (LUW). COMMIT IN PROGRESS A commit operation is in progress. The system is attempting to commit the resources for this commitment definition and all downstream resources associated with this LUW. COMMITTED A commit operation is in progress. The resources for this commitment definition and all downstream resources associated with this LUW have been committed. LAST AGENT PENDING A commit operation is in progress. The resources for this commitment definition have been prepared and the system is waiting for the last agent selected by this commitment definition to return the commit or rollback decision. PREPARE IN PROGRESS A commit or prepare operation is in progress. The system is attempting to prepare the resources for this commitment definition and to prepare all downstream resources associated with this LUW. PREPARED A commit operation is in progress. The resources for this commitment definition and all downstream resources associated with this LUW have been prepared. RESET A commit, rollback, or prepare operation is not in progress for this commitment definition. ROLLBACK IN PROGRESS A rollback operation is in progress. The system is attempting to roll back the committable changes for this commitment definition and for all the committable changes associated with this commitment definition. ROLLBACK REQUIRED A rollback operation is required. The resources registered to this commitment definition cannot be used until the LUW is rolled back. VOTE READ ONLY A commit operation is in progress. The commitment definition had no changes to commit, the Vote Read Only commitment option is set to 'Yes', and all downstream locations voted read only. This commitment definition and any downstream locations will not participate in the committed wave during this commit operation.
STATE_TIMESTAMP	STATE_TIME	TIMESTAMP(0) Nullable	The timestamp when the logical unit of work started or became undecided. Contains the null value if the commitment definition has performed no work and is not undecided.
LOCK_SPACE_ID	LOCKID	CHAR(20)	The lock space identifier for this commitment definition.
ACTIVATION_GROUP	ACTGRP	BIGINT Nullable	The activation group for this commitment definition. Contains the null value if the commitment definition is the job commitment definition (*JOB), an explicitly named commitment definition, or a commitment definition that represents an XA transaction branch with lock space scoped locks.
ASPGRP	ASPGRP	VARCHAR(10)	The ASP group for this commitment definition. If *SYSBAS, the commitment definition resides on the system auxiliary storage pool.
RESOURCE_LOCATION	RSC_LOC	VARCHAR(6)	Indicates whether local or remote resources are currently under commitment control. BOTH Both local and remote resources are under commitment control. LOCAL Local resources are under commitment control. NONE No resources are under commitment control. REMOTE Remote resources are under commitment control. API resources, and relational database resources with remote location name *ARDPGM, are considered to be local, even though they may represent objects on a remote system. Because these resources are accessed and updated by user programs, the system cannot determine whether these programs access objects on a remote system.
DEFAULT_LOCK_LEVEL	DFT_LCKL	VARCHAR(4)	The level of record locking on the records in each database file under commitment control for the commitment definition when files are opened using traditional system interfaces. For files opened using SQL interfaces, the effective isolation level for each SQL operation determines the level of record locking that is used. ALL Every record that is accessed in a file under commitment control is locked until the logical unit of work is committed or rolled back. CHG Every record that is changed in a file under commitment control is locked until the logical unit of work is committed or rolled back. *CS Every record that is changed in a file under commitment control is locked until the logical unit of work is committed or rolled back. Records that are accessed but not changed are locked only until a different record is accessed.
USER_NAME	USER_NAME	VARCHAR(10)	The user profile that started the transaction.
LOCAL_CHANGES_PENDING	LOCAL_PEND	VARCHAR(3)	Indicates whether this commitment definition has local changes pending. A local pending change means: One or more record level changes are pending. SQL statements run against the relational database might or might not have committable changes to the local database. One or more object level changes are pending. An API resource is registered that does not allow save-while-active requests to perform normally or does not allow independent ASPs to be quiesced. NO This commitment definition has no local changes pending. YES This commitment definition has local changes pending.
LOCAL_JOURNAL_CHANGES_PENDING	LOCAL_JRN	VARCHAR(3)	Indicates this commitment definition has journals with changes pending. This means that one or more local journals have changes pending. NO This commitment definition has no local journal changes pending. YES This commitment definition has local journal changes pending.
LOCAL_RECORD_CHANGES_PENDING	LOCAL_RCD	VARCHAR(3)	Indicates whether this commitment definition has records with changes pending. This means that one or more local records have insert, update, or delete changes pending. NO This commitment definition has no local record level changes pending. YES This commitment definition has local record level changes pending.
LOCAL_OBJECT_CHANGES_PENDING	LOCAL_OBJ	VARCHAR(3)	Indicates whether this commitment definition has objects with changes pending. This means that one or more local objects have changes pending. NO This commitment definition has no local object level changes pending. YES This commitment definition has local object level changes pending.
LOCAL_API_CHANGES_PENDING	LOCAL_API	VARCHAR(3)	Indicates whether this commitment definition has local API changes pending. This means that one or more API resources are registered that do not allow save-while-active requests to perform normally or do not allow independent ASPs to be quiesced. NO This commitment definition has no local API resource changes pending. YES This commitment definition has local API resource changes pending.
LOCAL_RDB_CHANGES_PENDING	LOCAL_RDB	VARCHAR(3)	Indicates whether this commitment definition has local RDB changes pending. This means that one or more record or object level changes are pending for a local RDB resource. SQL statements run against the relational database may or may not have committable changes to the local database. NO This commitment definition has no local RDB changes pending. YES This commitment definition has local RDB changes pending.
REMOTE_CHANGES_PENDING	RMT_PEND	VARCHAR(3)	Indicates whether this commitment definition has remote changes pending. A remote pending change means: One or more record or object level changes are pending for an RDB resource. SQL statements run against the relational database may or may not have committable changes to the remote database. One or more record or object level changes are pending for a TCP/IP connection resource. One or more record or object level changes are pending for a remote file resource. One or more record or object level changes are pending for a conversation resource. NO This commitment definition has no remote changes pending. YES This commitment definition has remote changes pending.
REMOTE_RDB_CHANGES_PENDING	RMT_RDB	VARCHAR(3)	Indicates whether this commitment definition has remote RDB changes pending. This means: One or more record or object level changes are pending for a remote RDB resource. SQL statements run against the relational database may or may not have committable changes to the remote database. One or more record or object level changes are pending for a TCP/IP connection resource. NO This commitment definition has no remote RDB changes pending. YES This commitment definition has remote RDB changes pending.
REMOTE_FILE_CHANGES_PENDING	RMT_FILE	VARCHAR(3)	Indicates whether this commitment definition has remote file changes pending. This means that one or more record or object level changes are pending for a remote file resource. NO This commitment definition has no remote file changes pending. YES This commitment definition has remote file changes pending.
REMOTE_CONVERSATION_CHANGES_PENDING	RMT_CONV	VARCHAR(3)	Indicates whether this commitment definition has remote conversation changes pending. This means that one or more record or object level changes are pending for a conversation resource. SQL statements run against the relational database may or may not have committable changes to the remote database. NO This commitment definition has no remote conversation changes pending. YES This commitment definition has remote conversation changes pending.
ROLE	ROLE	VARCHAR(19) Nullable	Indicates the role that this location is playing in the transaction program network. AGENT This location is a leaf in the transaction program network and it has no subordinate locations. CASCADER This location is an intermediate node in the transaction program network and it has subordinate locations. The commit or rollback operation did not start at this location. INITIATOR This location started the commit or rollback operation and is at the root of the transaction program network. LAST AGENT This location is a leaf in the transaction program network and it is the last agent, which decides whether to commit or to roll back the logical unit of work. LAST AGENT CASCADER This location is a middle node in the transaction program network and it is the last agent, which decides whether the logical unit of work is committed or rolled back. This location also can delegate another location of its choice to be the last agent. LOCAL This location is the only location with resources registered to the commitment definition. X/OPEN AGENT This location is a leaf in the transaction program network. The commit operation was initiated by an X/Open transaction manager. X/OPEN CASCADER This location is a middle node in the transaction program network. The commit operation was initiated by an X/Open transaction manager and this location has subordinate locations. Contains the null value if the role is not recognized.
RESYNC_IN_PROGRESS	RESYNCING	VARCHAR(3)	Indicates whether this commitment definition is resynchronizing resources that are associated with the logical unit of work. NO This commitment definition is not resynchronizing resources. YES This commitment definition is resynchronizing resources.
HEURISTIC_OPERATION	HEUR_OPER	VARCHAR(20) Nullable	The user-specified operation (resulting from a heuristic decision) performed against this commitment definition's resources and against all of the downstream resources associated with the logical unit of work. COMMIT IN PROGRESS The user forced a commit operation that has not yet completed. FORCED COMMIT The user forced a commit operation. FORCED ROLLBACK The user forced a rollback operation. ROLLBACK IN PROGRESS The user forced a rollback operation that has not yet completed. Contains the null value if the user has not specified a heuristic operation.
MIRROR_ACTIVE	MIRROR_ACT	VARCHAR(3)	Indicates whether the commitment definition has a Db2® Mirror resource registered under commitment control. NO The commitment definition has no Db2 Mirror resource registered under commitment control. YES The commitment definition has Db2 Mirror resource registered under commitment control.
MIRROR_ERROR	MIRROR_ERR	VARCHAR(19) Nullable	Indicates whether commitment control has detected a Db2 Mirror error. BLOCKED The Db2 Mirror state was BLOCKED when the error was detected. COMMUNICATION ERROR A communication error was detected. NO ERROR No error has been detected. TRACKING The Db2 Mirror state was TRACKING when the error was detected. Contains the null value if MIRROR_ACTIVE is NO.
MIRROR_ERROR_IASP	MIRROR_EI	INTEGER Nullable	The IASP number of the IASP where commitment control detected a Db2 Mirror error. Contains the null value if MIRROR_ACTIVE is NO or if commitment control has not detected a Db2 Mirror error for an IASP.
MIRROR_TRACKING_IASP	MIRROR_TI	INTEGER Nullable	The IASP number of the IASP where commitment control detected the Db2 Mirror state was TRACKING. Contains the null value if MIRROR_ACTIVE is NO or if commitment control has not detected an IASP where the Db2 Mirror state is TRACKING.
JOB_ACTIVE	JOB_ACTIVE	VARCHAR(6)	Indicates whether this commitment definition is part of an active job. NO This commitment definition is not part of an active job. The job was ended before all logical units of work were complete. SERVER The job that the commitment definition was part of has ended, and the commitment definition has been made part of a database server job. The SERVER_JOB_NAME column contains the name of the database server job. YES This commitment definition is part of an active job.
SERVER_JOB_NAME	SERVER_JOB	VARCHAR(28) Nullable	Qualified job name of the database server job of which this commitment definition is a part. Contains the null value if the commitment definition is not part of a database server job.
LOCK_SCOPE	LOCK_SCOPE	VARCHAR(7) Nullable	Indicates where the locks acquired on behalf of the commitment definition are scoped. ACTGRP The commitment definition has an activation group level scope. EXPL The commitment definition is explicitly named scope. JOB The commitment definition has a job level scope. TNSOBJ The commitment definition has a transaction object level scope (XA). Contains the null value if the lock scope is not recognized.
LOCK_WAIT_TIME	LOCK_WAIT	INTEGER	The maximum number of seconds that the system waits to acquire a lock requested on behalf of this commitment definition. Lock wait time values that are specified or defaulted using system interfaces other than the xa_open API are used if they are smaller than this value, or if this value is zero.
LOCK_LIMIT	LOCK_LIMIT	BIGINT	Indicates the maximum number of records which can be locked within a transaction. If multiple journals are involved in the transaction, this limit applies to each journal, not the transaction as a whole. The COMMITMENT_CONTROL_LOCK_LIMIT QAQQINI option can be used to configure this value before commitment control is started.
NESTING_LEVEL_DEPTH	NEST_LEVEL	INTEGER	The current nesting depth of all nested transactions for this commitment definition.
NUMBER_SAVEPOINTS	SAVEPOINTS	INTEGER	The number of active savepoints.
COMMIT_ROLLBACK_END	CR_END	VARCHAR(3)	Indicates if COMMIT, ROLLBACK, and ENDCMTCTL are allowed to be executed. NO COMMIT, ROLLBACK, and ENDCMTCTL are not allowed. YES COMMIT, ROLLBACK, and ENDCMTCTL are allowed.
EXPLICIT_COMMIT_ROLLBACK	EXPL_CR	VARCHAR(3)	Indicates if an explicit COMMIT or ROLLBACK was executed. NO An explicit COMMIT or ROLLBACK has not been executed. YES An explicit COMMIT or ROLLBACK has been executed.
SQL_DYNAMIC_COMMIT	SQL_DYNC	VARCHAR(3)	Indicates if an SQL Dynamic COMMIT was executed. NO An SQL dynamic COMMIT has not been executed. YES An SQL dynamic COMMIT has been executed.
SQL_DYNAMIC_ROLLBACK	SQL_DYNR	VARCHAR(3)	Indicates if an SQL Dynamic ROLLBACK was executed. NO An SQL dynamic ROLLBACK has not been executed. YES An SQL dynamic ROLLBACK has been executed.
SQL_HOLD_COMMIT	SQL_HOLD	VARCHAR(3)	Indicates the SQL HOLD value used on the call to COMMIT. NO SQLHOLD(NO) specified on COMMIT call. YES SQLHOLD(YES) specified on COMMIT call.
COMMIT_DURABLE	DURABLE	VARCHAR(3)	Indicates whether commit operations are guaranteed to be durable. NO The commit operation is not durable. Atomicity of the transaction is guaranteed, but one or more transactions may be lost in the event of a system failure. YES The commit operation is durable. When the transaction is committed, the transaction is guaranteed to persist on the system.
NUMBER_COMMITS	COMMIT_OPS	BIGINT Nullable	The total number of commit operations performed since this commitment definition was created. This number includes commit operations initiated by both the user and the operating system. Returns 2,147,483,648 if the number of commits has exceeded 2,147,483,647. Contains the null value if an IPL has been performed on the system since the commitment definition was created.
NUMBER_ROLLBACKS	ROLLB_OPS	BIGINT Nullable	The total number of rollback operations performed since this commitment definition was created. This number includes rollback operations initiated by both the user and the operating system. Returns 2,147,483,648 if the number of rollbacks has exceeded 2,147,483,647. Contains the null value if an IPL has been performed on the system since the commitment definition was created.
DEFAULT_JOURNAL_LIBRARY	DFT_JRNLIB	VARCHAR(10) Nullable	The name of the library in which the default journal is located. Contains the null value if there is no default journal.
DEFAULT_JOURNAL_NAME	DFT_JRN	VARCHAR(10) Nullable	The name of the default journal that was specified when the commitment definition was created. All logical unit of work (LUW) entries are placed in this journal. Contains the null value if there is no default journal.
NOTIFY_OBJECT_TYPE	NOTIFY_TYP	VARCHAR(7) Nullable	Type of notify object for the commitment definition. DTAARA The notify object is a data area. FILE The notify object is a database file member *MSGQ The notify object is a message queue. Contains the null value if there is no notification object.
NOTIFY_OBJECT_LIBRARY	NOTIFY_LIB	VARCHAR(10) Nullable	The name of the library in which the notify object for the commitment definition can be located. Contains the null value if there is no notification object.
NOTIFY_OBJECT	NOTIFY_OBJ	VARCHAR(10) Nullable	The name of the object to which notification is sent regarding the status of the commitment definition. Contains the null value if there is no notification object.
NOTIFY_OBJECT_MEMBER	NOTIFY_MBR	VARCHAR(10) Nullable	The name of the database file member that is the notify object for the commitment definition. Contains the null value if there is no notification object or NOTIFY_OBJECT_TYPE is not *FILE.
WAIT_FOR_OUTCOME	WAIT_OUTC	VARCHAR(17) Nullable	This commitment option indicates how this system handles resynchronization if a communications or remote system failure occurs during a commit or rollback operation. This value is set to WAIT when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. Note: If the commitment definition has a Wait for Outcome value of WAIT or inherits a value of WAIT, the value of the ACCEPT_VOTE_RELIABLE commitment option is ignored, and the system behaves as though the ACCEPT_VOTE_RELIABLE commitment option is NO. INHERIT OR NOWAIT When this system is the initiator of the commit or rollback operation, the INHERIT OR NOWAIT value has the same effect as the NOWAIT value. When this system is not the initiator and the initiator supports the presumed abort protocol, the Wait for Outcome value is inherited from the initiator. When this system is not the initiator and the initiator does not support the presumed abort protocol, the INHERIT OR NOWAIT value has the same effect as the NOWAIT value. INHERIT OR WAIT When this system is the initiator of the commit or rollback operation, the INHERIT OR WAIT value has the same effect as the WAIT value. When this system is not the initiator and the initiator supports the presumed abort protocol, the Wait for Outcome value is inherited from the initiator. When this system is not the initiator and the initiator does not support the presumed abort protocol, the INHERIT OR WAIT value has the same effect as the WAIT value. NOWAIT This system attempts to resynchronize one time before allowing the commit or rollback operation to complete. If the initial attempt fails, the resynchronization is completed in a database server job, and the application is not notified of the result of the resynchronization. Resynchronizations required with the last agent location always wait regardless of the current status of the wait for outcome option. WAIT This system completes resynchronization before allowing the commit or rollback operation to complete. Contains the null value if the Wait for Outcome option was not specified for the commitment definition.
PROBLEM_ACTION	ACTION	VARCHAR(8)	Indicates what this system does if another system, which controls whether the logical unit of work should commit or rollback, sends this system an unrecognized message or detects damage in the logical unit of work. This value is set to ROLLBACK when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. COMMIT The changes associated with this logical unit of work are committed. ROLLBACK The changes associated with this logical unit of work are rolled back.
VOTE_READ_ONLY_PERMITTED	VOTE_RO	VARCHAR(3)	Indicates whether this system can vote read-only in response to a commit operation started on another system. Note: If this system votes read-only, control is not returned to the application until this system receives a message from the starting system containing an indicator that the next logical unit of work has started. The length of the delay in regaining control depends on the application running on the starting system. This value is set to NO when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. NO This system cannot vote read-only. YES This system can vote read-only.
END_JOB_ACTION	ENDJOB_ACT	VARCHAR(8) Nullable	Indicates what this system does if the logical unit of work is in an undecided state and the job is ending abnormally. This value is set to WAIT when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. COMMIT This system commits the changes made to the logical of work. ROLLBACK This system rolls back the changes made to the logical unit of work. WAIT The system gets the commit or rollback decision from the system that started the operation. Based on that decision, this system commits or rolls back the changes made to the logical unit of work before ending the job. Heuristic operations can be performed if the time spent waiting is unacceptable. Contains the null value if the end job action is not known.
LAST_AGENT_PERMITTED	LAST_AGENT	VARCHAR(6) Nullable	Indicates whether a last agent can be selected when one is eligible to be selected during a commit operation. A last agent is eligible to be selected at the location that initiates a commit operation, and at locations that are selected as a last agent by the location that propagates the commit operation to that location. This value is set to SYSTEM when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. NO The system is not allowed to select a last agent. SYSTEM The system is allowed to select a last agent. Contains the null value if the last agent permitted value is not known.
LEAVE_OUT_OK	LEAVE_OUT	VARCHAR(3)	The commitment option that indicates whether it is OK to leave this system out of a commit operation initiated at another system. If this system indicates it can be left out, no communications flows are sent to this system during subsequent commit or rollback operations until a data flow is received from the initiator. Also, control is not returned to the application until the data flow is received. This value is set to NO when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. NO This system may not be left out of subsequent logical units of work. YES This system may be left out of subsequent logical units of work.
ACCEPT_VOTE_RELIABLE	ACCEPT_VR	VARCHAR(3)	Indicates whether this system accepts the vote reliable indicator if it is received from its agents during the prepare wave of a commit operation. The vote reliable indicator indicates that it is unlikely that a heuristic decision will be made at the agent if a failure occurs before the committed wave completes. If an agent sends the vote reliable indicator, and this location accepts it, performance is improved because one communications flow is eliminated and control is returned to the application before the committed wave is completed for that agent. However, if a heuristic decision is made at that agent which causes heuristic damage, the application at this location will not receive an error message if the Accept vote reliable commitment option is set to YES. This value is set to YES when commitment control is started and can be changed using the Change Commitment Options (QTNCHGCO) API. If the commitment definition has a Wait for outcome value of Wait or inherits a value of Wait, the value of the Accept vote reliable commitment option is ignored, and the system behaves as though the Accept vote reliable commitment option is No. NO This system does not accept the vote reliable indicator. YES This system accepts the vote reliable indicator.
RECYCLE_COUNT	RECYCLECNT	INTEGER	The number of times that this commitment definition has been recycled as a spare.
RECEIVED_DRDA_SYNCTYPE	RCV_SYNC	VARCHAR(17) Nullable	The two-phase commitment control synchronization type received by commitment control on the target-side of a DRDA connection. COMMITTED The application requester committed its unit or work. FORGET The application requester issued a forget request. MIGRATE The application requester issued a migrate request. NEW UNIT OF WORK The application requester is requesting a new unit of work. PREPARE TO COMMIT The application requester is requesting the unit of work be prepared to commit. REQUEST TO COMMIT The application requester is requesting the unit of work be committed. ROLLBACK The application requester is requesting the unit of work be rolled back. Contains the null value if no two-phase commitment control requests have been received.
RETURNED_DRDA_SYNCTYPE	RET_SYNC	VARCHAR(17) Nullable	The two-phase commitment control synchronization type returned by commitment control on the target-side of a DRDA connection. COMMITTED The application server committed its unit or work. FORGET The application server returned a forget request. MIGRATE The application server returned a migrate request. REQUEST TO COMMIT The application server is returning the unit of work be committed. ROLLBACK The application server is returning the unit of work be rolled back. Contains the null value if no two-phase commitment control requests have been returned.
XA_CONNECTION_TYPE	XA_TYPE	VARCHAR(8) Nullable	Indicates whether this commitment definition was created in an SQL Server Job for the purposes of running XA transactions. CLI The XA transactions are being performed in an SQL Server Job over a CLI connection. EMBEDDED The XA transactions are being performed in an SQL Server Job over an embedded SQL connection. NONE The XA transactions are not performed in an SQL Server Job. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_RMID	XA_RMID	INTEGER Nullable	The resource manager ID (RMID). The unique number is assigned by the transaction manager to identify this instance of the XA resource manager within the thread of control. This RMID is passed on subsequent calls to XA routines to identify the resource manager. The identifier remains constant until the transaction manager in this thread closes the resource manager. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_TRANSACTION_MANAGER	XA_MANAGER	VARCHAR(10) Nullable	The name of the transaction manager that started the XA transaction branch represented by this commitment definition. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_RDB_NAME	XA_RDB	VARCHAR(18) Nullable	The relational database name (RDB) associated with the XA transaction represented by this commitment definition. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_TRANSACTION_BRANCH_STATE	XA_STATE	VARCHAR(23) Nullable	Indicates the state of the XA transaction branch represented by this commitment definition. ACTIVE One or more threads of control are actively associated with the transaction branch. IDLE No threads of control are actively associated with the transaction branch. PREPARED The transaction branch has been prepared. ROLLBACK ONLY The transaction branch is required to roll back. HEURISTICALLY COMPLETED The transaction branch has been heuristically committed or rolled back. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_XID_FORMAT_ID	XA_FMTID	VARBINARY(4) Nullable	The hex value of the format identifier portion of the XA transaction identifier (XID). Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_XID_GLOBAL_TRANSACTION_ID	XA_GTRID	VARBINARY(64) Nullable	The hex value of the global transaction identifier (GTRID) portion of the XA transaction identifier (XID). Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_XID_BRANCH_QUALIFIER	XA_BQUAL	VARBINARY(64) Nullable	The hex value of the branch qualifier (BQUAL) portion of the XA transaction identifier (XID). Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_OPEN_SQL_HOLD	XA_SQLHOLD	CHAR(1) Nullable	Indicates how SQL cursors are affected by some XA operations. Specified on the db2xa_open() API xainfo string SQLHOLD keyword. A db2xa_commit() and db2xa_rollback(): Cursors are not affected since db2xa_end() already closed them. db2xa_end() : All cursors are closed. C db2xa_commit() and db2xa_rollback(): Cursors declared WITH HOLD are held open. Cursors not declared WITH HOLD are closed. db2xa_end(): All cursors are held open. E db2xa_commit(): Cursors declared WITH HOLD are held open. Cursors not declared WITH HOLD are closed. db2xa_rollback(): All cursors are closed. db2xa_end(): Cursors declared WITH HOLD are held open. Cursors not declared WITH HOLD are closed. L db2xa_commit(): If the relational database resides on an IBM® i, all cursors are left open. Otherwise, cursors declared WITH HOLD are left open and cursors not declared WITH HOLD are closed. db2xa_rollback(): If the relational database resides on an IBM i, all cursors are left open. Otherwise, all cursors are closed. db2xa_end(): All cursors are held open. N db2xa_commit(): Cursors declared WITH HOLD are held open. Cursors not declared WITH HOLD are closed. db2xa_rollback(): All cursors are closed. db2xa_end(): All cursors are held open. Y db2xa_commit() and db2xa_rollback(): If the relational database resides on an IBM i, all cursors are left open. Otherwise, the operation will fail. db2xa_end(): All cursors are held open. Contains the null value if the commitment definition is not a TNSOBJ scoped commitment definition or it is a TNSOBJ scoped commitment definition but not one where SQLHOLD was specified on the db2xa_open() API.
XA_OPEN_TBLCS	XA_TBLCS	CHAR(1) Nullable	Indicates whether loosely coupled threads of control (those working on transaction branches with the same global transaction identifier (GTRID), but different branch qualifiers (BQUALs)), share locks. This is what was specified on xa_open() API. N Locks are not shared. Resource deadlock may occur between the transaction branches. S Locks are shared. Resource deadlock will not occur between the transaction branches. Contains the null value if the commitment definition is not a TNSOBJ scoped commitment definition or it is a TNSOBJ scoped commitment definition but not one where TBLCS was specified on the xa_open() API.
XA_THREAD_OF_CONTROL	XA_THDCTL	VARCHAR(10) Nullable	The thread of control for the XA transaction branch represented by this commitment definition. CONNECTION The SQL connection is the XA thread of control. The XA transaction branch represented by this commitment definition was started by the SQLSetConnectAttr API or by a remote system that established an SQL connection to this system. All SQL work requested using the connection that started the transaction branch becomes part of the transaction branch regardless of requesting thread. THREAD The thread is the XA thread of control. The XA transaction branch represented by this commitment definition was started by the xa_start or db2xa_start API. All SQL work requested by the thread that started the transaction branch becomes part of the transaction branch regardless of which SQL connection is used to perform that work. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_THREAD_ASSOCIATION_COUNT	XA_THDCNT	BIGINT Nullable	The number of threads or SQL connections currently associated with the XA transaction branch represented by this commitment definition. The associations may be active or suspended. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_LOCK_SHARING	XA_LOCKSHR	VARCHAR(3) Nullable	Specifies whether locks are shared with loosely coupled transaction branches. A loosely coupled transaction branch is one with a transaction identifier (XID) that has the same global transaction identifier (GTRID) but a different branch qualifier (BQUAL). NO This commitment definition does not share locks with loosely coupled transaction branches. YES This commitment definition shares locks with loosely coupled transaction branches. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_LOCK_SPACE_HANDLE	XA_LOCKSP	INTEGER Nullable	The lock space associated space handle. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition.
XA_TRANSACTION_TIMEOUT	XA_TIMEOUT	INTEGER Nullable	The number of seconds that an XA transaction is allowed to exist before being automatically rolled back by the system. Contains the null value if the commitment definition is not a *TNSOBJ scoped commitment definition or if the number of seconds is 0.

Example

Show all jobs that have local work that has not been committed. The rows that are returned are not ordered.
```
SELECT *
  FROM QSYS2.DB_TRANSACTION_INFO
  WHERE LOCAL_CHANGES_PENDING = 'YES';
```

Show all jobs that have a commitment definition. Order the returned rows by job name, then job user, then by job number.

SELECT *
  FROM QSYS2.DB_TRANSACTION_INFO
  ORDER BY SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1), --job name
     SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8),'/')-1), -- job user
     SUBSTR(JOB_NAME,1,6) -- job number
     ;