How to view pending database commits using SQL

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

This next part is for those who have not used commitment control and do not know how to set it up. If you are familiar with commitment control you may want to skip the next few paragraphs.

I am going to create a schema, SQL speak for a library, as when I do a journal, QSQJRN, and journal receiver, QSQJRN0001, are automatically created. All SQL tables I create in this schema/library will be journaled, unless I state they are not to be.

CREATE SCHEMA MYTEST

The SQL table I will be using in this example has only one column:

CREATE TABLE MYTEST.TESTTABLE ( COLUMN1 VARCHAR(10) )

I can confirm that my table is being journaled using the OBJECT_STATISTICS table function:

SELECT OBJLIB,OBJNAME,JOURNALED, JOURNAL_LIBRARY,JOURNAL_NAME FROM TABLE (QSYS2.OBJECT_STATISTICS('MYTEST','*FILE')) JOURNAL JOURNAL OBJLIB OBJNAME JOURNALED _LIBRARY _NAME ------ --------- --------- -------- ------- MYTEST TESTTABLE YES MYTEST QSQJRN

Now I can start commitment control with the STRCMTCTL command:

STRCMTCTL LCKLVL(*ALL) NFYOBJ(MYTEST/TESTTABLE (*FILE))

I wrote this program to be responsible for the unapplied commit:

01 **free 02 dcl-f TESTTABLE disk usage(*output) 03 extfile('MYTEST/TESTTABLE') 04 rename(TESTTABLE:TESTTABLER) 05 commit ; 06 COLUMN1 = '1' ; 07 write TESTTABLER ; 08 *inlr = *on ;

Line 2 - 4: Rather than use SQL for the file I/O I have defined the table to use native RPG I/O. The table is defined for output only. I have hard coded the location of the file using the EXTFILE to my test library. And I have to rename the record format as I did not give a record format name when defining my table.

Line 5: As I want to commit all writes to the table I need the COMMIT keyword.

Lines 6 and 7: Move a value to the column in the table, and then write to the table.

There is no COMMIT operation, therefore, this database transaction remains uncommitted.

I can see this uncommitted transaction using the DB_TRANSACTION_INFO View. Below is the statement that includes the columns I found most interesting:

01 SELECT NOTIFY_OBJECT_LIBRARY AS "LIBRARY", 02 NOTIFY_OBJECT AS "OBJECT", 03 NOTIFY_OBJECT_MEMBER AS "MEMBER", 04 WAIT_FOR_OUTCOME, 05 LOCK_SCOPE, 06 COMMITMENT_DEFINITION AS "COMMITMENT DEF", 07 COMMITMENT_DEFINITION_ID AS "DEF ID", 08 LOGICAL_UNIT_OF_WORK_STATE AS "LUW STATE", 09 DEFAULT_LOCK_LEVEL AS "DFT LEVEL", 10 LOCAL_CHANGES_PENDING AS "LOCAL PENDING", 11 NOTIFY_OBJECT_TYPE AS "OBJ TYPE" 12 FROM QSYS2.DB_TRANSACTION_INFO 13 WHERE LOCAL_CHANGES_PENDING = 'YES'

Before I describe what the columns contain I want to show the results as that will help explain their use:

WAIT_FOR LOCK COMMITMENT LIBRARY OBJECT MEMBER _OUTCOME _SCOPE DEF ------- --------- --------- -------- ------- ---------- MYTEST TESTTABLE TESTTABLE WAIT *ACTGRP *DFTACTGRP LUW DFT LOCAL OBJ DEF ID STATE LEVEL PENDING TYPE -------------------- ----- ----- -------- ----- 5CC4C6E3C1C3E3C7D9D7 RESET *ALL YES **FIL

Lines 1 – 11: The columns from the View I thought would be interesting are:

LIBRARY/NOTIFY_OBJECT_LIBRARY:  Name of the library that contains the table.

OBJECT/NOTIFY_OBJECT:  Name of the object (table) that this transaction is outstanding for.

MEMBER/NOTIFY_OBJECT_MEMBER:  Name of the file/table member.

WAIT_FOR_OUTCOME:  When the value is WAIT commitment control is started for this object.

LOCK_SCOPE:  At what "level" the locks were acquired, in this case it was at the activation group level.

COMMITMENT DEF/COMMITMENT_DEFINITION:  Name of the commitment definition. As my program was in the default activation group the definition is *DFTACTGRP.

DEF ID/COMMITMENT_DEFINITION_ID:  The unique id for this commitment definition.

LUW STATE/LOGICAL_UNIT_OF_WORK_STATE:  Current state of this logical unit of work. RESET means that a commit or rollback is not in progress.

DFT LEVEL/DEFAULT_LOCK_LEVEL:  Level of record locking on the record/row in the table. As I used LCKLVL(*ALL) when I started commitment control *ALL is displayed in this column.

LOCAL PENDING/LOCAL_CHANGES_PENDING:  YES means the this is a pending change in the local database (partition).

OBJ TYPE/NOTIFY_OBJECT_TYPE:  This tells me that the type of object that this pending database transaction is for, in this example it is for a file. No matter how many times I ran this I always received **FIL in the results. This definitely not right as the documentation says it should be *FILE, but knowing what this means I can live with this bug for now.

Line 12: The DB_TRANSACTION_INFO View is found in the QSYS2 library.

Line 13: I am only interested in pending transactions in the local database.

If you are going to use this View I encourage you, at least once, to remove line 13 to see the results of all the pending database transactions in your partition.

 

You can learn more about the DB_TRANSACTION_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.