Last week was Part 2 of the discussion on DB2 Commit Scope considerations (found here) which followed Part 1 from the week before (here). This week the DB2 commit scope programming discussion continues with DB2 SQL considerations. The following addresses some of the DB2 SQL web and batch program requirements and considerations for determining the best DB2 commit scope practices and achieving the best performance within your DB2 applications.
- Commit scope SQL considerations
Within batch, web, or online programming there are a number of SQL commit scope coding considerations. The most important programming factor is handling DB2 SQL CURSOR result sets correctly through the DB2 DECLARE CURSOR and DECLARE CURSOR … WITH HOLD programming statements. DECLARE CURSOR … WITH HOLD will hold the data and the DB2 SQL fetch position within the DB2 result set in most processing scenarios after the commit process. There are several DB2 SQL factors that determine whether the DB2 result set and the DB2 fetch position are available to the program.
The first factor is the application operating environment which sometimes overrules the DECLARE CURSOR … WITH HOLD hold on the data and the DB2 SQL result set fetch position. For example, when an implicit sync-point is done or an explicit CICS RETURN is executed within a common pseudo-conversational CICS DB2 program, it causes the DB2 CURSOR Result Set to be closed and the processing cursor position nullified. The program’s implicit sync-point or explicit CICS RETURN stops the program execution, frees the DB2 CURSOR result set resources and potentially improves performance by releasing any locks.
Within Java processing architectures the DB2 CURSOR SQL is sometimes within a Hibernate framework, DAO, EJB or stateless memory object that can complicate DB2 commit scope and DB2 SQL CURSOR result set usage. Depending on the framework or the Java DB2 SQL objects used, the Java commit processing can take on many different and difficult challenges. It is best to design your Java DB2 SQL objects to use a simple DB2 JDBC interface environment to avoid all the complications of the frameworks and debugging different memory object type issues. By stripping the frameworks and other memory layers away, the DB2 Java process can deal directly with the DB2 database conventions making commit and restart-ability easier to understand and manage. This can be very important for future Java testing and debugging since each Java DAO or EJB object potentially has different memory object frameworks or configurations requiring deep debugging and maintenance research.
To handle DB2 web, mobile transactions, or CICS pseudo-conversational programming requirements the programming technique should be simple and straightforward. The web Java event process supervisor or CICS program retrieves fresh data for every new user interface display. The program processing gets control because of a user click or other event and uses DB2 SQL DECLARE CURSOR with WHERE clause keys to retrieve fresh new DB2 CURSOR result set data. During this process the DB2 CURSOR WHERE clause keys and potentially the top and/or bottom unique key values of a scrolling web page are captured within a Java memory object or within CICS screen COMMAREA variables. These Java memory object keys are then referenced and used when the web Java event process supervisor gets control back and the DB2 web transaction needs to understand the DB2 CURSOR WHERE clause keys to work on the appropriate data or retrieve the next or previous web page of DB2 data result set. The Java program or CICS program can then use the captured DB2 CURSORS’ WHERE clause keys to perform any desired subsequent processing. The programs can use the saved keys to re-DECLARE the CURSOR, OPEN and FETCH the appropriate rows for SQL UPDATE or SQL DELETE process, or grab the previous or next scrolling set of appropriate data.
At DB2 batch processing at commit points these DB2 SQL CURSOR WHERE clause keys, along with any flat file positioning information, need to be saved to facilitate commit/restart processing. These WHERE clause keys along with a flat file READ counter or positional information need to be stored within your company’s commit/restart third party product or standard practices DB2 commit/restart routine. These checkpoint routines or products will work within your batch processing logic to reposition your application within the flat file, re-DECLARE your DB2 CURSOR SQL, OPEN it, and then FETCH from the DB2 CURSOR result set to reposition your batch processing from its last commit.
The DB2 commit restart program design flow needs to guarantee transaction integrity. The sometimes complex processing transaction logic needs to reference the DB2 commit/restart routine saved keys and carry on processing the next transaction. Since every application processing can be unique, deep research is required for saving the correct information for repositioning in flat files, understanding the DB2 tables modified, and DECLARING and re-OPENING the DB2 CURSORs to get the correct result set data.
Within batch processes, DECLARE CURSOR … WITH HOLD programming is commonly used to OPEN CURSORS and FETCH the result set data for subsequent processing. Once the DB2 commit block of rows is processed, a DB2 commit is executed to save all the data to the database. Since the DECLARE CURSOR … WITH HOLD was specified the CURSOR’s result set data remains available to the program. The DB2 CURSOR is positioned before the next logical row of the DB2 CURSOR results set data. When the commit is performed, all DB2 table row processing locks are released. The DECLARE CURSOR … WITH HOLD result set locks, indicating the current cursor position is retained.
DB2 CURSORs can be defined FOR READ ONLY or FOR UPDATE to reflect your desired processing. It is recommended to identify your CURSORS as read-only by adding the FOR READ ONLY phrase when the DECLARE CURSOR is defined. This recommendation is because the DB2 database manager keys in on the FOR READ ONLY phrase and can then block the data retrieval process for additional performance efficiency. If the DB2 CURSOR is not specified, DB2 marks the DB2 CURSOR as ambiguous and cannot leverage the READ ONLY performance advantage.
The CURSOR can also be defined with the FOR UPDATE clause to enable SQL UPDATE and DELETE SQL statements at the current CURSOR’s data row. When the program logic identifies the retrieved row as the one they would like to update and then uses the DB2 SQL UPDATE <table name> SET <column name>… WHERE CURRENT OF <cursor name>, the SQL statement updates the information in the DB2 row. The DB2 CURSOR continues to be positioned at that same current row so the next DB2 SQL FETCH statement will get the next DB2 result set row.
If the current DB2 CURSOR result set row is identified for deletion and then the program uses the DB2 SQL DELETE FROM <table name> WHERE CURRENT OF <cursor name> the cursor is positioned at the empty space of the deleted row. A subsequent FETCH statement will move the cursor on to the next result set row to continue further processing.
The DB2 commit scope of every batch program, web transaction, or CICS transaction is important because a commit encourages application concurrency by closing all the SQL CURSOR result sets, releasing all its associated locks against the DB2 database table pages, and potentially releasing the processing thread for possible reuse by another application.
Application developers will always have difficulties with DB2 commit/restart and commit scope issues because the data continues to get bigger. Working with multiple local platforms, cloud and partner legacy environments is complex. Transaction integrity and database integrity is the key to satisfied business customers and hopefully, these DB2 Commit SQL tips helped you realize some of the considerations.
I will be giving a security seminar and two presentation at this year’s IDUG conference. Make your plans and sign up for the IDUG DB2 Technical Conference Austin, Texas coming up this May 22-26th 2016. Also plan on attending any of my sessions.
- “How to do a DB2 Security Audit”
Half-day seminar Tuesday 2:15-4:30 PM, Room: Frio - “Performance Enterprise Architectures for Analytic Design Patterns “
Presentation Wednesday, May 25th 1:00 – 02:00 PM, Room: Pecos - “DB2 Security Best Practices: Protecting Your System from the Legions of Doom”
Presentation May 26th 8:00-9:00,AM, Room: Trinity A
For more details on any of these items go to www.idug.org.
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.
Leave a Reply