DB_TRANSACTION_RECORD_INFO table function

The DB_TRANSACTION_RECORD_INFO table function returns the record level status of local database files under commitment control for a commitment definition.

The information returned is similar to the values shown by Display Record Level Status within the Work with Commitment Definitions (WRKCMTDFN) CL command.

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

The schema is QSYS2.

lock-space-id: A character string that contains the lock space identifier of the commitment definition to use.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.

Table 1. DB_TRANSACTION_RECORD_INFO table function
Column Name	Data Type	Description
LIBRARY_NAME	VARCHAR(10)	The name of the library that contains the file.
FILE_NAME	VARCHAR(10)	The name of the local database file under commitment control for the commitment definition.
MEMBER_NAME	VARCHAR(10)	The name of the member in the file.
FILE_CHANGES_COMMITTED	BIGINT	The total number of record level changes to the file that have been committed. Returns the value 32768 when more than 32767 changes have been committed. Contains the null value if an IPL has been performed on the system since the commitment definition was created.
FILE_CHANGES_ROLLED_BACK	BIGINT	The total number of record level changes to the file that have been rolled back. Returns the value 32768 when more than 32767 changes have been rolled back. Contains the null value if an IPL has been performed on the system since the commitment definition was created.
FILE_CHANGES_PENDING	BIGINT	The total number of record level changes to the file that are pending a commit or rollback for the commitment definition. Returns the value 32,768 when more than 32767 changes are pending. Contains the null value if an IPL has been performed on the system since the commitment definition was created.
LOCK_LEVEL	VARCHAR(5)	The level of record locking for this file. ALL Records that are read, updated, deleted, and inserted are locked until the logical unit of work is committed or rolled back. Uncommitted changes in other jobs cannot be seen. CHG Records that are updated, deleted, and inserted are locked until the logical unit of work is committed or rolled back. Uncommitted changes in other jobs can be seen. CS Records that are updated, deleted, and inserted are locked until the logical unit of work is committed or rolled back. A record that is read but not updated is locked until the next record is read. Uncommitted changes in other jobs cannot be seen CSKL Records that are updated, deleted, and inserted are locked until the logical unit of work is committed or rolled back. A record that is read but not updated is locked until the completion of the SQL statement, or the cursor is closed, or the logical unit of work is committed or rolled back. Uncommitted changes in other jobs cannot be seen. RR Records that are updated, deleted, and inserted are locked until the logical unit of work is committed or rolled back. Uncommitted changes in other jobs cannot be seen. All tables referred to in SELECT, UPDATE, DELETE, and INSERT statements are locked exclusively until the logical unit of work is committed or rolled back. RREL Records that are read, updated, deleted, and inserted are locked exclusively until the logical unit of work is committed or rolled back. Uncommitted changes in other jobs cannot be seen. All tables referred to in SELECT, UPDATE, DELETE, and INSERT statements are locked exclusively until the logical unit of work is committed or rolled back. *RSEL Records that are read, updated, deleted, and inserted are locked exclusively until the logical unit of work is committed or rolled back. Uncommitted changes in other jobs cannot be seen. Contains the null value if the file is closed.
STATUS	VARCHAR(13)	File status. CLOSED The file has been closed with pending changes that have not been committed or rolled back. OPEN The file is open under commitment control. PSEUDO-CLOSED The file has been pseudo-closed. This is an SQL cursor that is not currently in use, but remains open under commitment control for reuse to minimize performance impacts.
CONCURRENT_ACCESS_RESOLUTION	VARCHAR(7)	The concurrent access resolution setting for this file. CURCMT The database manager can use the currently committed version of the data for applicable scans when it is in the process of being updated or deleted. Rows in the process of being inserted can be skipped. SKIP The database manager will skip data on which incompatible locks are held by other transactions. *WAIT The database manager will wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows encountered that are in the process of being inserted are not skipped. Contains the null value if the file is closed.
JOURNAL_LIBRARY	VARCHAR(10)	The library that contains the journal.
JOURNAL_NAME	VARCHAR(10)	The journal in which changes to the file are recorded. Can contain the following special value: *MULTIPLE Logical files based on physical files are not all journaled to the same journal.
COMMIT_CYCLE	DECIMAL(21,0)	The commit cycle identifier of the current logical unit of work (LUW) for this file's journal. The commit cycle identifier is the sequence number of the journal entry that corresponds to the beginning of the current LUW for the commitment definition. Contains the null value if no changes have been made to local database files under commitment control, and no user journal entries have been sent to this journal on behalf of an API resource that is journaled to the file's journal during this LUW.

Example

View all the files associated with a given commit definition.

SELECT * FROM TABLE(QSYS2.DB_TRANSACTION_RECORD_INFO('UDB_0100000000000001'));

View all the files with pending changes for a given commit definition.

SELECT * FROM TABLE(QSYS2.DB_TRANSACTION_RECORD_INFO('UDB_0100000000000001'))
  WHERE FILE_CHANGES_PENDING > 0;