Start of change

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: 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.
Start of changeLOCAL_JOURNAL_CHANGES_PENDINGEnd of change Start of changeLOCAL_JRNEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeLOCAL_RECORD_CHANGES_PENDINGEnd of change Start of changeLOCAL_RCDEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeLOCAL_OBJECT_CHANGES_PENDINGEnd of change Start of changeLOCAL_OBJEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeLOCAL_API_CHANGES_PENDINGEnd of change Start of changeLOCAL_APIEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeLOCAL_RDB_CHANGES_PENDING End of change Start of changeLOCAL_RDBEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
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
         ;
End of change