Skip to main content

Synchronization

Introduction​

The transfer of data from the ConSol CM database to the DWH database is called synchronization. The synchronization process can be started by a manual action performed by an administrator in the Web Admin Suite, or be triggered automatically when the DWH is running in live mode. You can check the status of a synchronization action on the Administration page in the DWH menu of the Web Admin Suite. The Monitor page provides additional information about the status and content of the DWH.

Synchronization mode​

The synchronization mode is determined when activating the data warehouse on the Administration page of the DWH menu in the Web Admin Suite. The following values are available for the DWH mode:

  • Off: The data warehouse is disabled.
  • Admin: The data warehouse is enabled, but the data is not updated automatically. The administrator needs to create an update action to start synchronization.
  • Live: The data warehouse is enabled and the data is updated automatically. This means that the required update actions are created and executed automatically. Therefore, the data is always up-to-date without administrator actions.

If you run the DWH in LIVE mode, you can choose whether tasks, which are created after configuration changes, should be executed automatically or manually.

Components involved in transfer​

Tables in the CM database​

  • cmas_dwh_operation_status: The DWH log service writes into this table to document the DWH status. These are the info and error log entries which you can also find in the log file cmrf.log.
  • cmas_dwh_ser_sync_object: This table is only used in LIVE mode. ConSol CM inserts an entry into this table for a transaction which was performed in the CM database. The entry contains a serialized package which needs to be sent to CMRF. The DWH live service reads from this table.
  • cmas_dwh_synchronization: When a DWH operation is performed using the Web Admin Suite, an entry is created in this table. The column type contains one of the possible values: INITIALIZATION, REINITIALIZATION, TRANSFER, UPDATE, TASK. The DWH transfer service reads from this table.
  • cmas_dwh_task: An entry in this database is created when a setting which is relevant for DWH transfer has been changed, i.e. Transfer to DWH and Field value omitted from DWH history. Tasks listed in this table are visible on the Tasks page under the DWH menu in the Web Admin Suite. When a task is started (automatically or manually), a new entry in cmas_dwh_synchronization is created. When the data has been processed successfully, the entry from cmas_dwh_task is deleted.

Tables in the DWH database​

  • INT_CONTROL_QUEUE: INT_CONTROL_QUEUE is used to control the processing in CMRF. Two actions are available: pause and resume. Only the JMX components write into INT_CONTROL_QUEUE. Only the CMRF service reads and deletes entries from INT_CONTROL_QUEUE. It is done during processing of other data (e.g. transfer). The CMRF service pauses processing after pause action and continues after resume action.
  • INT_LIVE_QUEUE: The DWH live service writes serialized packages into this table. CMRF reads from this table to process the LIVE data.
  • INT_TRANSFER_QUEUE: The DWH transfer service writes serialized packages into this table. CMRF reads from this table to process the TRANSFER data.
  • INT_LOG_QUEUE: This table is used for log entries written by the CMRF. Based on those entries, the DWH log service creates entries in the table cmas_dwh_operation_status. See DWH log service.
  • hlp_parameter: This table is not directly involved in transfer but provides some useful information, most importantly:
    • parameter_namelast_data_transfer: The timestamp of the end of the last DHW update or transfer in the local time of the CMRF server.
    • parameter_namedwh_status: The possible values are explained in the following table.
      • 0 (DWH uninitialized): This is the status after application startup, when the DWH sees that the database has not been initialized. This is a theoretical value, because when the DWH is uninitialized, it does not yet exist.
      • 1 (DWH waiting for initial transfer): This status is set during (re)initialization. After the start of the DWH application, the DWH sees that there has not been a data transfer yet. When transfer messages are sent, the DWH switches to the status 2.
      • 2 (DWH: data transfer in progress): The DWH enters this status when the transfer action is executed. The CMRF is working on transfer messages, and the DWH ignores all incoming non-transfer messages (there may have been problems with the transmission). The DWH remains in this status until either a fatal error occurs (subsequent status will be 3) or all transfer messages have been handled (subsequent status will be either 3 or 4, depending on whether they were errors).
      • 3 (DWH: data transfer finished unsuccessfully): All data transfer messages have been handled, but, there were some errors.
      • 4 (DWH operational): The DWH is operational and will handle all incoming update messages from CM. This status is reached when all data transfers or updates have finished successfully.
  • hlp_transfer_error: Keeps a copy of the error message from exceptions of the CMRF live mode since the last REINITIALIZE. Here you can look up the object_uid for which the exception occurred.
  • int_parameter: This table show technical details about the data transfer applied to the DWH database.
    • live-serial-number: The last applied serial number during live mode.
    • live-error: Boolean. If it is "false", the live mode did not abort.
    • last-success-id: Epoch time stamp in milliseconds of the last successful live mode update
    • last-success-live-id: Epoch time stamp in milliseconds of the last successful live update of a transfer or update operation. This value can be lower than last-success-id, when a transfer or update task is being executed.

ConSol CM processes and services​

The following processes and services are relevant for the data transfer from the ConSol CM database to the DWH. The details are explained in the following sections.

Manual operations​

When the administrator clicks one of the buttons of the Administration page, the corresponding action is created in the ConSol CM table cmas_dwh_synchronization and shown on the page. The actions are executed according to the timestamp of their creation in ascending order (oldest actions first).

The sequence for the initial setup of the DWH is:

  1. Initialize
  2. Transfer
  3. Update

If you do not follow the required order (e.g. perform an update without having performed a transfer first) or try to execute several actions of the same type (e.g. perform a transfer while another transfer is still running), warning messages are shown. If you have created an action by accident, you can remove it by clicking the Delete icon while the action is still in state NEW.

During the regular operation of the DWH, i.e. after the initial data transfer, the only actions which you need to execute are updates when the DWH is running in admin mode.

Initialize​

The initialization operation creates a database structure with all static tables and relations. This step is only performed once during the initial setup of the data warehouse.

It creates a new entry in cmas_dwh_synchronization with the type 'INITIALIZATION' and both the DWH status and the CMRF status 'NEW'.

If you want to reinitialize the data warehouse, i.e. rebuild it from scratch after an error, you must select the Delete existing data checkbox. This drops the whole database structure and creates it again. In this case, the entry in cmas_dwh_synchronization is created with the type 'REINITIALIZATION':

The (re)initialization is processed in another thread, by the DWH transfer service.

Transfer​

The transfer operation creates the dynamic tables and fills both the static and the dynamic tables with data from ConSol CM. This step is only performed once during the initial setup of the data warehouse. If the data warehouse tables are not empty, all existing data and all custom tables are removed during the transfer.

It creates new entry in cmas_dwh_synchronization with the type 'TRANSFER' and both the DWH status and the CMRF status 'NEW'. In addition, the entry contains the transfer settings provided in the WAS dialog.

The transfer is processed in another thread by the DWH transfer service.

Performance note

If the DWH already contains data, it will be removed. This is not done not very efficiently though. If you need to recreate the DWH, you should use the option to initialize the DWH with delete existing data instead.

Update​

The update operation adapts the DWH structure to configuration changes and transfers data which has been added or changed since the last update or transfer operation. Updates are required during the regular operation of the data warehouse. If the DWH is running in live mode, the required update actions are created and executed automatically. If the DWH is running in admin mode, you need to create the update action manually.

The update creates a new entry in cmas_dwh_synchronization with the type 'UPDATE' and both the DWH status and the CMRF status 'NEW'. In addition, the entry contains the update settings provided in the WAS dialog.

The update is processed in another thread by the DWH transfer service.

Tasks​

Tasks are used to transfer the data which is not transferred with the update operation because it is outside the time period covered by the update. Tasks are created automatically when needed because certain configuration changes were performed. They are saved in the CM table cmas_dwh_task and shown on the Tasks page of the DWH menu. If the DWH is running in admin mode, tasks need to be executed manually. In Live mode, they can be executed automatically if Administrative changes are applied automatically is selected.

When a task is executed, a new entry in cmas_dwh_synchronization is created with the type 'TASK' and both the DWH status and the CMRF status 'NEW'.

The tasks are processed in another thread by the DWH transfer service. Their execution status is visible on Administration page.

Enabling live mode​

If the live mode is enabled, the required update actions are created automatically. If Administrative changes are applied automatically is selected on the Tasks page, the tasks are also executed automatically.

When you enable the live mode, the system property cmas-dwh-server, live.start is created and set to the current date. If an update is needed, a new entry in cmas_dwh_synchronization is created with the type 'UPDATE' and both the DWH status and the CMRF status 'NEW'. In addition, the to_date is set to the current date + the value of the property cmas-dwh-server, time.buffer and the retry count is set to 3.

The update is processed in another thread by the DWH transfer service.

Automatic operations​

Automatic operations are performed by CM and by DWH services.

DWH transfer service​

The DWH transfer service works in the same way in admin and in live mode. It reads entries from the CM table cmas_dwh_synchronization with dwh_status = "NEW" or "ACTIVE" and executes the respective jobs in the order of the timestamp of the entry (in ascending order).

The value of dwh_status is changed during processing from "NEW" to "ACTIVE" to "SUCCESS" or "ERROR"). It is set to "ACTIVE" when the entry is processed (only one entry can be active). If the processing is finished successfully, it is set to "SUCCESS". Otherwise, it is set to "ERROR".

During the active period, the DWH transfer service inserts serialized packages into INT_TRANSFER_QUEUE. The package size can be set in the field CM package size when starting the transfer on the Administration page.

info

If this value is not set, e.g. because the job was created via the JMX operations, the CM system property cmas-dwh-server, batch-commit-interval is used (default 1000).

Processing of one entry:

  1. The entry is read from cmas_dwh_synchronization (dwh_status = ACTIVE or NEW)
  2. If dwh_status is NEW, it is set to ACTIVE
  3. Data is sent to CMRF in packages.
  4. Each package has a serial number. Sending of one package:
    • A portion of data is read from the CM database (table cmas_dwh_synchronization).
    • The package is created.
    • The serialized package is inserted into INT_TRANSFER_QUEUE. The value of the next serial number (next_serial_number) is incremented. The progress of the processing (position) is updated. These operations are done in one transaction.
  5. Processing in CM is finished, dwh_status is updated to SUCCESS or ERROR.

Error handling:

After an exception, the processing will be continued from the point where it was stopped (position) if:

  • the DWH transfer service is stopped
  • a database failure is detected
  • the exception is recoverable (system property cmas-dwh-server, recoverable.exceptions, see also FAQ about recoverable exceptions)
  • retry is configured (retry_count > 0, 1 is used if not set)
  • it is an error (e.g. OutOfMemoryError)

Otherwise, the processing will not be continued (dwh_status = ERROR).

CMRF service​

CMRF reads and processes TRANSFER data (types INITIALIZATION, REINITIALIZATION, TRANSFER, UPDATE, TASK), LIVE data and CONTROL data (pause and resume). TRANSFER data has a higher priority than LIVE data. LIVE data is processed only if there is no TRANSFER data available.

  • TRANSFER data is read from INT_TRANSFER_QUEUE.
  • LIVE data is read from INT_LIVE_QUEUE.
  • CONTROL data is read from INT_CONTROL_QUEUE.

TRANSFER data​

After the DWH transfer service has written entries into INT_TRANSFER _QUEUE, the CMRF service can process these entries. The processing of each initialization / reinitialization / transfer / update / task is split into multiple transactions. The current state of the processing is saved in the database at the end of each transaction.

The performance and memory usage can be influenced by the cache size parameter. The cache size can be set in the field CMRF cache size when starting the transfer on the Administration page.

info

If this value is not set, e.g. because the job was created via the JMX operations, the default value 10000 is used.

After restart the state of the processing is loaded from the database and the processing is continued.

A package (BLOB entry in the data column) of the INT_TRANSFER_QUEUE table contains the complete new / modified object (e.g. case, user, contact, resource).

Error handling:

After an exception the processing will be continued from the point where it was stopped if:

  • a database failure is detected - in this case processing is paused
  • the exception is recoverable (system property cmas-dwh-server, recoverable.exceptions)
  • it is an error (e.g. OutOfMemoryError)
  • retry is configured (cmrf_retry_count > 0, 1 is used if not set)

Otherwise, the processing will be not continued but the remaining packages are extracted from the table and discarded.

LIVE data​

When the DWH live service has written entries into INT_LIVE _QUEUE, the CMRF service can process these entries.

Error handling:

The entry which caused the abort is logged in hlp_transfer_errors together with the error message.

CONTROL data​

Pause and resume entries written by JMX into INT_CONTROL_QUEUE are read by the CMRF service. They pause and resume the CMRF service.

DWH log service​

The DWH log service works in the same way in admin and live mode. It is responsible for providing log information. The service reads serialized packages from INT_LOG_QUEUE and writes them into the CM table cmas_dwh_log. This information is available on the Administration page of the Web Admin Suite by clicking the Show logs icon for an action. The log messages are retrieved from the operation_message column of the cmas_dwh_log table.

One iteration (transaction):

  1. Log entries are read and deleted from the INT_LOG_QUEUE.
  2. A new entry in cmas_dwh_log is created for each log entry.
  3. The system property last.success.live.timestamp is updated for each LIVE log
  4. cmas_dwh_synchronization.cmrf_status is updated for each log entry except for LIVE log.
tip

If you need to find out more details about the possible cause of an error, you can increase the log level in the CM properties file cm6-config.properties or cmrf.properties. See Configuring the log files for details.

DWH live service​

The DWH live service controls the just-in-time DWH updates, i.e. it is responsible for sending information about changes to data field values in CM to CMRF. It is only active if the synchronization mode is set to Live in the DWH configuration (i.e. if the system property cmas-dwh-server, live.start is set) and if all data older than the date of the property live.start were already sent to CMRF. If the live mode is not enabled and there is no data in cmas_dwh_ser_sync_object, the property live.start is deleted.

If the live mode is enabled and CMRF needs to be informed about changes done in a CM transaction, CM writes one entry for each transaction into the table cmas_dwh_ser_sync_object. The entry is written at the end of the transaction, before the commit operation is executed. Each entry contains a serialized package which needs to be sent to CMRF. The DWH live service reads the entries from the table and inserts the respective serialized packages into the INT_LIVE_QUEUE in the DWH.

One iteration (transaction):

  1. Packages are read from cmas_dwh_ser_sync_object (max. 100, in order of creation).
  2. The property live.serial.number is read.
  3. A serial number is set for each package.
  4. The packages are deleted from cmas_dwh_ser_sync_object.
  5. The property live.serial.number is updated.
  6. The serialized packages are inserted into INT_LIVE_QUEUE.

The packages from INT_LIVE_QUEUE will be processed by DWH transfer service.

Operations via JMX​

Some DWH-related operations can be executed via JMX.

ConSol CM​

Start transfer and update. The CM package size, CMRF cache size and retry counts cannot be set using the MBean. The default values will be used. A new entry in cmas_dwh_synchronization is created

consol.cmas:type=admin,topic=global,name=dwh.synchronizationService

CMRF​

Pause and resume processing in CMRF. A new entry in INT_CONTROL_QUEUE is created

consol.cmrf:name=cmrf.control