Criteria for Determining the Commit Scope Performance (Part 1)

A couple of weeks ago I discussed three critical programming performance criteria, where I talked about the importance of DB2 commit scope, data integrity, and programming languages. This brought in some interesting email comments/questions about what DB2 commit scope criteria needs to be evaluated and what factors come into consideration. The evaluation of a DB2 commit scope can open a Pandora ’s Box of issues. The following information addresses some of the requirement discussions that are needed to determine the best DB2 commit scope size for achieving the best performance for your DB2 application.

1. What DB2 commit scope criteria need to be evaluated?

   DB2 commit scope is dependent on a number of things, the first and foremost is the program itself. There are many different considerations based on the type of program being executed.
   
   Is it a batch program? Is the process a stored procedure? Is the program a Java program?  Is it a main Java program or a sub program? Is the process a CICS program or a MQ program?  Is it a non-terminal CICS batch process? Does the program use Cursors WITH HOLD? How does the program attach to the DB2 database?  Does the program use a JDBC or DB2 Connect driver?  How many database(s) does the process connect to? Does the process commit scope sync with other files? Does the program execute on a single operating system? Or does the program update items on Windows, UNIX, and z/OS within its commit scope?
   
   With these many different types of considerations the new or inexperienced programmer can get easily confused about what to do about their program’s commit scope. Unless standards and guidelines are published and enforced, most of the time the commit scope is an afterthought or totally forgotten. Only when the process tries to do a big data update and performs billions of Inserts/updates before it abends and locks everyone out of the database tables while the DB2 system rolls back the hours of work does the lack of a proper commit scope come into focus.
   
   

2. Commit scope is about data integrity

   Data integrity is the main issue that matters when discussing commit scope. Within batch programs the commit scope is usually linked to a long running batch process that needs the ability to be restarted. The commit scope provides the opportunity to set all the various tables’ related data to a known integrity state or a completed transaction.
   
   The same type of data integrity analysis is needed for any online CICS, MQ, or Java web process. The commit scope needs to be taken at the end of a completed transaction or set number of transactions. The online type of transaction environments, such as CICS, MQ, or web, add the extra consideration of transaction persistence because of the interim data persistence suspensions from pseudo conversational, conversational or web transactions. Within these CICS and web transactions the interface conversation provides the transaction transmission and needs to be analyzed when money, promises, or obligations are realized.
   
   Stored procedures and Java web subprograms can sometimes be used to track transaction progress, record database tables, flat files or other objects changed, and manage commit scope against the data assets. Most of the time these stored procedures are smaller scope type processes best used as stand-alone modules that provide distinct functionality. Since it is good to develop, standardize, and incorporate commit scope standards these types of modules can be shared easily and incorporated in all types of processing.
   
   

3. Online web/CICS DB2 commit scope considerations

   It is always best to start any type of online transaction with freshly retrieved data regardless whether it is through a CICS or web pseudo conversational transaction. This program commit scope design principle provides the latest data from the database tables so the end user interaction reflects the most current balances, seat availability, or other current state of data at the start of every Unit-of-Work (UOW) transaction interaction.
   
   This pseudo conversational UOW design principle fits together nicely with the CICS technical considerations and also for stateless web interfaces using standard TCP/IP protocols. When working with CICS programs it is important to also remember that an implicit sync point is invoked every time a CICS RETURN is performed and when a CICS SYNCPOINT is executed. With web and Java transactions it is important to always work with the latest retrieved database table data and not from previously retrieved memory cached data such as Hibernate or other DAO or EJB objects that might be stale and not reflect the current bank balance or items available. Working with stale old data can lead to inaccurate, duplicate, unbalanced transactions, and referential integrity issues.
   
   When working with Java JDBC connections, it is also very important to verify the JDBC connection properties since they will impact your commit scope performance and behavior. As I detailed in this blog last year, the AutoCommit, ResultHoldability, and queryCloseImplicit JDBC property settings can play an important role in determining when database results are locked and released within the processing commit scope. Make sure to verify your JDBC settings and understand their behavior impacts for these important DB2 commit scope Java connection properties.

Next week we’ll discuss batch commit scope, coding correct SQL and other important considerations concerning commit scope.

Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.