DB_TRANSACTION_JOURNAL_INFO table function
The DB_TRANSACTION_JOURNAL_INFO table function returns the status of journal resources under commitment control for a commitment definition.
The information returned is similar to the values shown by Display Journal 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.
- lock-space-id
- A character string that contains the lock space identifier of the commitment definition to be returned.
The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.
| Column Name | Data Type | Description |
|---|---|---|
| JOURNAL_LIBRARY | VARCHAR(10) | The name of the library that contains the journal. |
| JOURNAL_NAME | VARCHAR(10) | The name of the journal. |
| COMMIT_CYCLE | DECIMAL(21,0) | The commit cycle identifier of the current logical unit of work
(LUW) for this 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 this journal during this LUW. |
| RECORD_LOCKS | BIGINT | The number of record locks held by this commitment definition for files journaled to this journal. This number is kept current during the entire transaction. When a commit or rollback is requested, the number will decrease to show progress as locks are released. |
| PENDING_CHANGES | BIGINT | The number of changes that have yet to be committed or rolled back for the journal. This number is kept current during the entire transaction. When a rollback is requested, the number will decrease to show progress as the rollback occurs. |
| RECORD_ROLLBACK_STARTED | TIMESTAMP(0) | The timestamp when record level rollback started for the journal.
Contains the null value if a rollback has not started. |
| RECORD_ROLLBACK_PERCENT | INTEGER | The percentage of record level rollback processing that has
completed for the journal. A value of 100 means record level rollback has completed for this
journal, but other steps in the current commit or rollback operation may still be in progress for
this or other journals related to this commitment definition. Contains the null value if no rollback is in progress, or rollback has not yet started for this journal. |
| INDEX_ENTRIES | INTEGER | The number of index entries in internal indexes used by this commitment definition. This number is kept current during the entire transaction. When a rollback is requested, the number will decrease to show progress as the rollback occurs. |
| INDEX_CLEANUP_STARTED | TIMESTAMP(0) | The timestamp when the index cleanup started for the
journal. Contains the null value if cleanup has not started. |
| INDEX_CLEANUP_PERCENT | INTEGER | The percentage of index cleanup that has completed for the
journal. A value of 100 means cleanup has completed for this journal, but other steps in the current
commit or rollback operation are still in progress for this or other journals related to this
commitment definition. Contains the null value if no cleanup is in progress, or cleanup has not yet started for this journal. |
| RECORD_UNLOCK_STARTED | TIMESTAMP(0) | The timestamp when the record unlock processing started for the
journal. Contains the null value if unlock processing has not started. |
| RECORD_UNLOCK_PERCENT | INTEGER | The percentage of record unlock processing that has completed for
the journal. A value of 100 means unlock processing has completed for this journal, but other steps
in the current commit or rollback operation are still in progress for this or other journals related
to this commitment definition. Contains the null value if no record unlock processing is in progress, or it has not yet started for this journal. |
Example
- Show the status of journal resources under commitment control for a specific lock space ID.
SELECT * FROM TABLE(QSYS2.DB_TRANSACTION_JOURNAL_INFO('UDB_010000000000A07F'));
