Reference Table Runtime Commands

Synchronization Overview

This article discusses synchronization between any Fabric session and the external source from where the Reference table is populated. For information about the cross-nodes Synchronization process within a Fabric Cluster, refer to this article.

Use Cases

Case 1 - Background Sync

In this scenario a Fabric Session requests to update a Reference table from an external source according to a predefined interval. The ref_sync and ref_sync_wait commands are triggered automatically in the background according to the method selected in Fabric Studio (Time Interval or Decision Function).

Case 2 - Pro-Active Sync

In this scenario, for example, a customer service operative needs to get the most updated list of new services subscribed by a customer in real-time. The Web Service or Job request triggers the ref_sync and ref_sync_wait commands.

Case 3 - Scheduled Sync

In this scenario a system needs to operate a synchronization process every day at 2 AM as a result of a maintenance task - i.e. get all new customers, or new transactions created over the last 24 hours. A recurring background sync can be scheduled for a specific day and time.

Synchronization Commands

The following commands are available from the Fabric Command Line.

Reference Tables Synchronization Statuses Commands

Reference Tables Runtime Examples

Status per table, on all tables

fabric>REF_STATUS TABLES='ALL' SCOPE='table';

|table name               |status|node        |backlog|offset|offset duration (min)|num of messages|transaction id|transaction type|sub status|current session transaction|sync error|notes|
+-------------------------+------+------------+-------+------+---------------------+---------------+--------------+----------------+----------+---------------------------+----------+-----+
|common.ref_Asset         |READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |
|common.ref_Asset_Stations|READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |
|common.ref_Asset_Type    |READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |

• table_name: the name of the Reference Table.

• status: one of the statuses defined here.

• node: the ID of the node operating the Reference table's synchronization.

• backlog: number of messages still to be processed during the synchronization process.

• offset: total number of message retrieved during the synchronization.

• offset duration: time to process all messages.

• Transaction Id: current processed transaction id.

• Transaction Type:

  • LONG_SNAPSHOT
  • SHORT_SNAPSHOT
  • SHORT_TRANSACTION
  • LONG_TRANSACTION
  • IDLE

• Sub_status:

  • In process

  • Index rebuild

  • Done

• Current Session Transaction values:

  • Started: the transaction has started on the current session, but is not committed yet

  • Commit in progress

  • Completed - commit is completed

  • Rollback in progress

  • Rolled back

  • Rollback is completed

    ​

Status per population, on all tables

fabric>REF_STATUS TABLES=’ALL’ SCOPE=’population’

|table name               |population     |verified time          |start sync time        |end sync time          |start write time       |last write time        |next planned sync      |sync error|node        |notes|
+-------------------------+---------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------+------------+-----+
|common.REF_ASSET         |population.flow|2022-03-16 17:55:32.324|2022-03-16 11:59:13.048|2022-03-16 11:59:13.631|2022-03-16 11:59:13.639|2022-03-16 11:59:13.743|1970-01-01 02:00:00.000|null      |fabric_debug|     |
|common.REF_ASSET_STATIONS|population.flow|2022-03-16 17:55:32.346|2022-03-16 11:59:13.048|2022-03-16 11:59:14.460|2022-03-16 11:59:13.774|2022-03-16 11:59:14.476|1970-01-01 02:00:00.000|null      |fabric_debug|     |
|common.REF_ASSET_TYPE    |population.flow|2022-03-16 17:55:32.361|2022-03-16 11:59:13.049|2022-03-16 11:59:13.238|2022-03-16 11:59:13.293|2022-03-16 11:59:13.295|1970-01-01 02:00:00.000|null      |fabric_debug|     |

• table_name: the name of the reference table
• population: the name of the population querying the external sources
• verified time: timestamp when last sync was verified
• start/end sync time: Sync time start and end times
• start/last write time: Write time of first and last message
• next planned sync: Timestamp for next sync
• sync error: error message
• node: The ID of the node operating the sync
• notes: details

Sync Wait

fabric>REF_SYNC_WAIT TABLES='ALL';

|Table name|Required sync time|Current session transaction|
+----------+------------------+---------------------------+
|REF_USAGE |null              |                           |
|T1        |null              |                           |
|T2        |null              |                           |

Reference Table Data Manipulations

• To select and modify data in Reference Tables set the common_local_trx flag to TRUE, before running a commit. fabric>set COMMON_LOCAL_TRX=true;

  • Using the example defined earlier, and the DEVICESTABLE2017 Reference Table:
  • Enable Reference Table modification: fabric>set COMMON_LOCAL_TRX=true;
  • Use the select command to view the row to be modified:

  fabric>select TAC, BRANDMODEL  from DEVICESTABLE2017 where TAC=35156209;
  |TAC     |BRANDMODEL             |
  +--------+-----------------------+
  |35156209|GALAXY J3 2016 SM-J320F|
  

  • Start a transaction: fabric>begin;
  • Using the update command, operate a change in the table: fabric>update DEVICESTABLE2017 set BRANDMODEL='GALAXY J3--2016 SM-J320F' where TAC=35156209;
  • Check that the change has been committed:

  fabric>select TAC, BRANDMODEL  from DEVICESTABLE2017 where TAC=35156209;
  |TAC     |BRANDMODEL              |
  +--------+------------------------+
  |35156209|GALAXY J3--2016 SM-J320F|
  

  • Close the transaction: fabric>end;
  • Note that if you forget to close the transaction, write sessions to the Reference Table (such as a scheduled Sync) will not work.