CommitWork function

Syntax

CommitWork()

Description

Use the CommitWork function to commit pending changes (inserts, updates, and deletes) to the database.

Returns

A Boolean value, True if data was successfully committed, False otherwise.

Example

The following example fetches rows and processes them one at a time, committing every 100 iterations. Because restart is disabled, you must have a marker indicating which rows have been processed, and use it in a conditional clause that filters out those rows. 

Local SQL &SQL; 
Local Record &REC; 
Local Number &COUNT; 
&REC = CreateRecord(RECORD.TRANS_TBL); 
&SQL = CreateSQL("%SelectAll(:1) WHERE PROCESSED <> 'Y'"); 
&COUNT = 0; 
&SQL.Execute(&REC); 
While &SQL.Fetch(&REC) 
   If (&COUNT > 99) Then 
      &COUNT = 0; 
      CommitWork();   /* commit work once per 100 iterations */ 
   End-if; 
   &COUNT = &COUNT + 1; 
   /* do processing */ 
   ... 
   /* update transaction as "processed" */ 
   &REC.PROCESSED.Value = 'Y'; 
   &REC.Update(); 
End-While;

Considerations for Using CommitWork

The following are the considerations for using CommitWork.

  • This function is available in Application Engine PeopleCode, the FieldChange and SavePreChange events. If you use it anywhere else, you'll receive a runtime error.

  • When used with an Application Engine program, this function only applies to those Application Engine programs that run in batch (not online). If the program is invoked using the CallAppEngine function, the CommitWork call is ignored. The same is true for commit settings at the section or step level.

  • This function can only be used in an Application Engine program that has restart disabled. If you try to use this function in a program that doesn't have restart disabled, you'll receive a runtime error.

  • Component interfaces that rely on CommitWork to save data cannot be used in the Excel to Component Interface utility.

  • When CommitWork is called in the context of a component interface (such as, during a SavePreChange PeopleCode program that's associated with the component), if the caller of the component interface already has an open cursor (such as an active SQL object) the Commit does not take effect immediately, but only when the last cursor is closed.

See CallAppEngine function.

FieldChange and SavePreChange Considerations

The following are the FieldChange and SavePreChange considerations:

  • All updates done in FieldChange (including those using CallAppEngine) should be considered a single database transaction. This is a fundamental change: previously, a single transaction was represented by a page or a component.

  • A consequence of this is that a message requiring a reply, or any other think-time action, causes a fatal error if located in FieldChange after a database update that has not been committed to the database using the CommitWork function. So it is possible for an application to update the database in FieldChange, then do a think-time action, by preceding the think-time action with a call to CommitWork.

  • CommitWork commits the updates and closes the database transaction (that is, the unit of work). The consequence of using CommitWork is that because it closes the database transaction, any subsequent rollback calls will not rollback the committed updates.

  • Just as any database updates in FieldChange required careful application design to ensure that the transaction model is appropriate, so too does the use of CommitWork.

  • When using CommitWork in the Component Processor environment (as opposed to using it in an Application Engine program) CommitWork produces an error if there are any open cursors, such as any open PeopleCode SQL objects.

See PeopleCode Developer’s Guide: FieldChange Event.

Application Engine Considerations

The CommitWork function is useful only when you are doing row-at-a-time SQL processing in a single PeopleCode program, and you must commit without exiting the program. In a typical Application Engine program, SQL commands are split between multiple Application Engine actions that fetch, insert, update, or delete application data. Therefore, you would use the section or step level commit settings to manage the commits. This is the recommended approach.

However, with some types of Application Engine programs that are PeopleCode intensive, it can be difficult to exit the PeopleCode in order to perform a commit. This is the only time when the CommitWork function should be used.

See Application Engine: Restarting Application Engine Programs.

Restart Considerations

Disabling restart on a particular program means that the application itself is intrinsically self-restartable: it can be re-run from the start after an abend, and it performs any initialization, cleanup, and filtering of input data to ensure that everything gets processed once and only once, and that upon successful completion, the database is in the same state it would have been if no abend occurred.

Set-based applications should always use Application Engine's restart. Only row-by-row applications that have restart built into them can benefit from disabling Application Engine's restart.

Consider the following points to managing restarts in a self-restarting program:

  • Locking input transactions (optional).

    If the input data can change, and if it's important not to pick up new data during a restart, there should be logic to lock transactions at the start of the initial run (such as updating rows with current Process Instance). The program should first check whether any rows have the current Process Instance (that is, is the process being restarted from the top after an abend?). If no rows found, do the update.

    In some cases it is acceptable for a restarted process to pick up new rows, so that locking is not necessary. It depends on your application.

    Also, if you do not lock transactions, you must provide some other way to manage concurrent processing of the same program. You do not want two simultaneous runs of the same program to use the same data, so you must have some strategy for dividing up the data such that there is no overlap.

  • Filtering input transactions (required).

    After an input transaction is processed, the row should be updated accordingly (that is, setting a "processed" flag). The SELECT statement that drives the main processing loop should include a WHERE condition to filter out rows that have already been processed.

Related Topics