APPX and Oracle - Commit/Rollback
A commit is how tables in Oracle are updated. Nothing is saved until the commit has been executed. Every process in APPX has a commit mode. The various commit modes allow the designer to control when commits are executed. As of release 4.1 APPX supports true transaction processing by use of the commit modes and allowing rollback of transactions.
APPX, by design, writes records immediately when requested, either by a WRITE, REWRITE, or DELETE in ILF or the PCF file processing. When the file is an Oracle table that means the commit is done at that time. In large updates this can create performance problems. One of the enhancements in APPX 4.1 is the implementation of designer selected commit modes. These allow you to determine the best time to commit records.
The four modes are COMPATIBLE, END, MANUAL, and PCF CYCLE. The default is COMPATIBLE mode. This mode tries to follow the APPXIO rules when deciding to commit. APPXIO will immediately update a record on a write or rewrite. If compatible mode is selected a commit will be attempted on a write/rewrite. The commit will be successful if there are no held records. That is any hold, not just a hold on the record you are writing.
When a commit is executed, all commits are performed. That means any record ready to be written will be, and all locks on records will be released. In the case of a two level process where the child is performing a write on one record while the parent still has a lock on the PCF record, the parent's lock will also be lost. In order to prevent early release on locks in this manner, the commit will not be done until the last lock is released.
END mode will execute the commit when the process ends. MANUAL mode will never perform a commit. All commits are left to the designer and must be executed through the ILF code using the COMMIT statement. PCF CYCLE indicates to APPX that each PCF record processed is to be treated as a transaction. Just before the read of the next PCF record, APPX will perform all commits that are pending.
It is important to be aware of the different modes and how they act, especially if you decide to control the commits by mixing modes. For example, on a two level process with the parent process set to PCF Cycle, it may be best to set the mode on the child to Manual. That way the child will never attempt to commit and the parent will control when the commit occurs. In a long update there may be performance improvements if the commit mode is set to manual and ILF code is used to execute the commit based on a calculated number of records.
APPX 4.1 has a new PDF called --- COMMIT MODE. You can see what mode your process is operating in by checking the value of the PDF. --- COMMIT MODE is modifiable so that it can be set in ILF as well. A change of mode in ILF will not take effect until after the next commit is completed. You can code a COMMIT into ILF if you want to force a commit so the new mode will take effect immediately. Remember, though, that a hard-coded commit will release all locks, ready or not.
To help with transaction processing Appx has added some new ILF commands as well.
COMMIT - executes all pending updates and releases all held resources.Note: Oracle keeps a lock on any records that have been changed but not committed. For example, If you change several customer records, but don't commit any of the changes, then all those customer records will be held until you either commit or rollback. To try this out, change commit mode on the CUSTOMER input to MANUAL, and in the End of Process Event Point, test to see if OPTION is END. If true, issue a COMMIT, if false issue a ROLLBACK. When the process is modified this way, all changes to the customer record will be held until the user ends the process. If they end it normally (i.e. OPTION=END), then all changes are written to the RDBMS. If they CANCEL, all changes are rolled back.
SAVEPNT - sets an intermediate rollback point so that a partial rollback can be performed. This acts like a label and can accept a constant or field name.
ROLLBACK - rolls back the transaction to the indicated point. If a field name or constant is entered that matches a previously executed SAVEPNT, the transaction is rolled back to that save point. If left blank, the entire transaction is rolled back.
In order to correct the performance problems associated with very wide tables, APPX has modified the READ, READNEXT, BEG READ, END READ, WRITE, and REWRITE statements. Prior to release 4.1, you specified a file name as the first operand to these statements. Now, you can specify either a file name or a field name. If you specify a file name, APPX will operate on the entire record. If you specify a field name, the above I/O statement will operate on the named field - if the field is a group header, the I/O will be performed on all members of the group.
APPX has also added support for partial-record I/O to the process-control-file for each process. You can tell APPX which PCF fields to deal with by setting the Partial-Record I/O field in Additional Attributes for each process. You can use this new capability to limit the amount of data transferred between APPX and an RDBMS. For example, if you know that a particular section of ILF code needs to update only one or two fields within a record, you can change the I/O statements to refer only to those fields.
BEG READ TPA EMPLOYEE HOLD 1 KEY EMPLOYEE KEY COMPUTE TPA EMPLOYEE SALARY * 1.1 REWRITE TPA EMPLOYEE END READ TPA EMPOYEEAfter
BEG READ TPA EMPLOYEE SALARY HOLD 1 BY EMPLOYEE KEY COMPUTE TPA EMPLOYEE SALARY * 1.1 REWRITE TPA EMPLOYEE SALARY END READ TPA EMPLOYEE SALARYUsing the partial-record form of the I/O statements can greatly improve performance when storing data in an RDBMS.
When you WRITE a single field (or a group field), APPX will write NULL values into the remainder of the record. When you READ (or READNEXT, or BEG READ) a partial record, the remainder of the record will contain NULL values. If you REWRITE a partial record, the remainder of the record will not be modified.
The READNEXT statement allows you to enter a field name (rather than a file name), however, that field name is not really used to restrict the set of fields retrieved from the RDBMS server. Consider that APPX is generating SQL statements on the fly - when you execute a BEG/END READ statement, APPX generates a "select from table where and ". Executing that select statement causes the RDBMS to generate a recordset - the recordset contains all of selected columns for each of the selected records. The READNEXT statement (and in fact the END READ statement) cannot change the recordset - it can only fetch the next row from that recordset. So, the fieldname supplied to a READNEXT statement (or an END READ) statement is for documentation purposes only: you cannot use a READNEXT statement to change which columns are present in the recordset.
Even thought the END READ statement now allows specification of a field name, this is for documentation and consistency purposes only and is not actually used other than to indirectly identify the file name so that the END READ can be associated with the appropriate corresponding BEG READ statement.
REWRITE statements also allow specification of a file name or a field name. If you specify a field name, only the named field or group of fields will be updated. All other values in the record being updated will be unchanged. The field name specified on a REWRITE statement does not need to be the same as the field name specified on the READ statement which read the record.
So, you could read the entire record with hold by specifying a file name on the read statement and then update a single field by specifying that field name on the REWRITE statement. Or, you could read a group of fields and then rewrite a different group of fields (presumably a smaller group of fields within the group originally read but not necessarily).
Partial-Record I/O will read only the field or group of fields specified. The key field specified on the READ statement will not be read unless the key field is included in the group of fields specified on the READ statement. If you need the key field to be read, remember that a SYNONYM for the key field can easily be included in any group that you may define in the Data Dictionary. The primary purpose of implementing Partial-Record I/O is to increase performance by only reading and writing the fields which are actually needed. If APPX always included the key field values on a READ, then we would be slowing down performance in those cases where the key fields are not specifically needed by the process.
Note: In release 4.1, AppxIO files will not support partial record I/O. The partial record I/O statements will still function, but they will function on the entire record. Also, a partial record READ-type statement will not set the remainder of the record to NULL values.
Do you have a tip you want to pass on? Contact Us.