10.3. Interface Facility -

Technical Information

Interface Control Using the Batch Scheduler #

A common SICS framework has been developed for the inbound/outbound interface packages, driven by the standard SICS Batch Scheduler. (You can access the batch scheduler by clicking the Scheduled Jobs icon in the Periodic Functions folder of the System Administration folder on the SICS Desktop.)

The Batch Scheduler has been extended to allow the administrator to define a batch process for an interface main type.

The Batch Scheduler allows the administrator to determine the frequency of batch runs for each individual interface main type.

The batch process triggers an interface process, which searches for appropriate data to be transferred to or from the Interface Data Transfer Medium for the specified interface main type.

Interface Data Transfer Medium #

There are many possible ways of transferring/converting data between computer systems.

The Interface uses a relational database as the transfer medium. That is, SICS and the external system must place their interface data on a database in a fixed format.

For outbound interfaces, SICS collects the relevant data for the given interface main type and deposits it in records on the interface database. These records are then available for any external system.

For the inbound interfaces, SICS searches for relevant data for the given interface main type on the interface database, and if data exists and is valid, converts and imports the data.

This database is independent of the SICS application database. It can and should be on a different server.

The use of a database as a transfer medium has the advantage of being robust and reliable. It may be slower than a direct communications link, but the SICS Interface is not real-time critical. The interface framework has been designed to allow the development of alternative media/formats (e.g. XML, CSV files), if necessary.

Interface Data Transfer Format #

The interface format consists of a small number of generic database tables based on the Accordion or Handorgel principle. This presupposes the use of a database as the transfer medium.

Data being transferred in either direction must be stored in the predefined format in 4 main tables:

• Header.
• Header_lock_id
• Object
• Attribute

The structure of these tables is described later in this document.

The linked ‘packet’ of header, header_lock_ids, objects and attributes is called a transaction (inbound and outbound).

Each interface transaction must consist of only one Header, with one or more Header_Lock_Id, at least one Object and at least one Attribute

Header_Lock_Ids and Objects must have the relational link to their Header.

Attributes must have the relational link to their Object.

The exact structure is part of the definition of each individual interface main type, which is described in the technical specification for that interface.

A fifth table, Error, is used by the Interface to store error messages encountered during processing.

Interface Error Handling #

Errors occurring on the interface are also written to a database table. The error information should enable the user to identify the source of an error.

For the outbound interfaces, only system crashes will result in an error message.

For the inbound interfaces, syntax, semantic or business validations may also generate an error message. Once an inbound transaction has been set to error status, any subsequent transactions with the same lock_id (see chapter on Interface Security) will also be set to error, in order to avoid integrity problems.

In this case the error description will include the object_id of the original failed transaction.

Interface Security #

Interface security controls concurrency and dependency issues.

Concurrency #

Concurrency problems can occur when the same object (if the object is an insert or an update object) arrives within two or more inbound transactions on the interface database.

For example, assume the client is running with more than one SICS batch scheduler for processing the same main type.

Scheduler 1 starts a job to process the first transaction.

Scheduler 2 starts a job to process the second transaction.

In this scenario, scheduler 2 may finish processing before scheduler 1 in which case SICS will no longer be synchronized with the client side and may result in invalid data. Such concurrency problems are solved using the Interface_Lock_Id table.

Data Dependency #

Data Dependency exists when a transaction M must be processed before another transaction N because transaction N requires data that is created and/or updated by transaction M.

For example, transaction M might be creation or update of Business SICS30 over the inbound business interface, and transaction N might be creation of Claim 123 on Business SICS30 over the inbound claim interface. In this case, transaction N has a data dependency on the transaction M.

Note that data dependency can also exist within one main type. The Data decency is checked and maintained across the systems. Hence any blocked interface record in LIFE can prevent any entries in P&C to be executed (provided that they have the same lock id objects) and vice versa.

Data dependency issues can be solved using the Interface_Lock_Id table, and/or the client can schedule their different interface batch jobs to process dependent data in the right order.

Concurrency and Data Dependency Responsibilities #

Concurrency is implemented as follows for all kinds of inbound transactions:

A.Client responsibilities

The client who writes the inbound transactions to the interface tables, must adhere to these constraints:

Inbound transactions must be written to the interface tables in chronological order, that is, by increasing entry timestamp (ENTRY_TS). This rule applies across all main-types.

No two inbound transactions can have the same entry timestamp AND the same lock id object(s). This rule applies across all main-types.

For example, if the client wants to create an assumed business with lock id ‘SICS30’ by putting three inbound transactions to the interface tables (one for the basic creation of the business, one for adding the premium condition and one for adding the deduction condition), then all of these transactions should include the lock id ‘SICS30’.

Consequently, these three transactions MUST have unique entry timestamps, and they must be written to the interface tables in order of increasing entry timestamp.

B.SICS interface job responsibilities

The interface job(s) will choose which transaction to process, according to the following algorithm.

Assume the batch job is set up to handle all transactions of main type X.

1. Get a sorted collection with all transaction headers with status TO_BE_PROCESSED and main type X, ordered by the entry timestamp (oldest first).
2. From this collection find the first transaction header that satisfies this criterion:

For the ROOT_OBJECT_ID of each lock id object A belonging to the header, there does not exist any other header lock id object B that satisfies all of these criteria:

B has the same ROOT_OBJECT_ID as A

B does not belong to the same transaction header as A

B belongs to a transaction header with status “TO_BE_PROCESSED”, “BEING_PROCESSED” or “ERROR” B belongs to a transaction header with an entry timestamp which is older than A’s entry timestamp. Note that the main type of the transaction header to which B belongs, is NOT checked. 3. If a header was found in step 2 (if any), read this header (for update) with it’s DATA OBJECT(S) and DATA ATTRIBUTE OBJECT(S) and continue at step 4.
If no header was found in step 2, the batch job ends.

4. Check the header status.
   a) If the header status (still) equals “TO_BE_PROCESSED” then process the transaction. Whether processing succeeds (“SICS_PROCESSED”) or fails (“ERROR”), continue at step 5.
   b) However, if the header status is not equal to status “TO_BE_PROCESSED” then rollback the whole transaction and continue at step 5.

5. Empty certain memory-cached data.

Repeat from step 1. Note! Transactions that were skipped because they did not satisfy the criterion mentioned in step 2 above will be reconsidered the next time a batch job for main type X is started.

Note! If a transaction M is skipped because it has lock id data dependency on another transaction N, and transaction N was processed with an “ERROR” status, then transaction M will not be processed until the failed transaction N is investigated and reset to status ‘TO_BE_PROCESSED’ or ‘CORRECTED’.

Ensuring Up-to-Date Information during Processing #

When inbound or outbound transactions are processed, there is usually a need to access data that already exist on the database. This could for example be reference data, countries, currencies, business, claim, accounting, etc. After a piece of data is read from the database, it will be held in a memory cache controlled by SICS. Subsequent accesses to this data will fetch it from the memory cache, not from the database. This caching strategy is necessary to ensure good performance.

However, while it is beneficial to utilize data caching during the processing of one single transaction, problems occur if cached data is utilized across transactions. There is a risk that the cached data is no longer in synch with updates that other SICS processes may have carried out on the database. Therefore, memory caches will be emptied as follows:

After processing an inbound transaction (successfully or with error), all non-support data will be removed from cache.

Before processing an inbound transaction, there will be a check against the database to see if the ‘support_data_changed_ts’ timestamp (this is a new database field within SICS core) is later than the value it had at login time. If it is, then the support data will be re-cached.

Here, ‘support data’ refers to reference data, countries, states, currencies, entry codes, etc. These data are not static, but updates happen very seldom. Non-support data would then be business partners, businesses, claims, accounting, etc.

Interface Database Tables #

The interface database consists of four external (user-accessible) tables:

INF_HEADER, INF_HEADER_LOCK_ID, INF_OBJECT, INF_ATTRIBUTE

and 4 internal (SICS system) tables:

INF_TRANS, INF_TRAN_LOCK_ID, INF_TRANS_ITEM,

INF_ITEM_ATTR

and one table that is used as both internal and external: INF_ERROR

The external tables are used for storing the actual data being transferred over the database to and from the client’s external systems. The client needs complete knowledge of the structure and format of these tables to ensure correct interpretation of the transferred data.

The internal tables are used for storing intermediate interface outbound data before generation of the client’s external data.

The maintenance of all Interface Database Tables, internal and external, is the responsibility of the client.

Field description 2. Interface Header LockId Table (INF_ HEADER_LOCK_ID):

Field description 3. Interface Data Object Table (INF_OBJECT):

Field description 4. Interface Data Attributes Table (INF_ ATTRIBUTE):

Field description 5. Interface Data Header Table (INF_TRANS):

Field description 6. Interface Header LockId Table (INF_ TRAN_LOCK_ID):

Field description 7. Interface Data Object Table (INF_TRANS_ITEM):

Field description 8. Interface Data Attributes Table (INF_ ITEM_ATTR):

Field description 9. Interface Error Table (INF_ ERROR):