Schedule Batch Processing with the Application Timer

Previous Next

As an alternative to initiating batch processes by calling batch jobs, you can make the execution of all batch processes within the database dependent on the application timer.

You cannot call jobs from constraints. Therefore, with the application timer you can only schedule batch processing tasks that do not involve imports from, or exports to, external files.

This technique can be used to schedule SQL tasks as well as those parts of import tasks not relying on external files and sets, for instance, calculations.

From the point of view of system maintenance, batch processing is best written against the application timer whenever possible, because in that case, batch processing is defined in the same way as all other processing, i.e. in terms of business rules. No external files or schedulers are used. Batch processing is made as independent as possible from the environment in which USoft is deployed.

A disadvantage of this technique is that the application timer must run continuously. Another disadvantage is that you cannot commit batch processing on a record-per-record basis.

As an example, consider an import task in which for each imported record a number of calculated values must be generated on the basis of data in other tables. Instead of arranging this in import SQL statements, delay processing until midnight, when online processing on the database has stopped.

To schedule import-related batch processing against the application timer:

1.In the application table, include a status field able to distinguish between Imported (I) and Processed (P) records.
2.Define an import task that imports external data into the application table without performing any calculations, setting the status field to I.
3.Define one or more corrective constraints performing the calculations and setting the status field to P. The basis structure of the SQL statement is:

UPDATE  app-table
SET     computed-field [, ...] 
,       status-field =
   (       SELECT    computation [, ...]
           ,         'P'
FROM    table1 [, ...]
,       t_app_time tt
WHERE   tt.app_day <> OLD(tt.app_day)

The basic structure of transition settings is:

Transition Table: T_APP_TIME

Alias: TT

Fire On Insert: Never

Fire On Delete: Never

Fire On Update: Used Columns


4.In order to test this constraint, you need to add WHERE clauses for hours and minutes because you do not want to wait until midnight. Perform the following steps:

Change the constraint SQL definition so that the constraint will fire in a few minutes.

Add the Application Timer item to your USoft Binder file, using the New option from the Item menu.

Start the application by double-clicking the new item. If error message "Elapsed time more than allowed maximum" appears, you might be able to solve this by increasing the values at Max Time Since Last Update in the item properties.

Wait until the Application Timer has synchronized with the database time (this is automatic). If you left the Verbose item property set to On (the default), the Application Timer window will display where the synchronization is at.

Run the import task, e.g. from USoft Binder.

Inspect the data. The status of imported records should be 'I'.

After the scheduled time has expired, inspect the same data. The status should have changed to 'P' and calculated data should have appeared.