Configuring instances and partitions (default implementation)

FTM provides a default implementation for configuring generated ID sequence values.

You can implement the ID configuration in a different manner if you want. Change the configuration to suit your specific requirements by editing or re-creating the following file for your database.
  • 17-ConfigIDGen.sql. The sequence max values are defined in 02-NewID.sql, which might also need to be updated.
For an ID configuration, the parameters in the following table need to be provided for custom requirements (multiple instances and partitions). Or, you can choose to leave the default values, which support one instance and one partition.
Table 1. Parameters to be provided for a default implementation of the ID configuration.
Parameter Description
ID_SEQ_INCREMENT Specify how many IDs the cache implementation needs to manage. Configure this value in the following file for your database.
  • 02-NewID.sql. The default value is 1000.
Note: It is recommended that you decide on a value for this parameter after a performance tuning investigation. A higher number means less frequent database calls, but with performance gains subject to diminishing returns. Any IDs that are generated but unallocated are permanently lost when the message flow, execution group, or broker is shut down.
EVENT_ID_SEQ_INCREMENT Specify how many IDs the cache implementation needs to manage for event IDs. Configure this value in the following file for your database.
  • 02-NewID.sql. The default value is 1.
PRIMARY_ID_GRP_SIZE In the context of the example in ID range adjustment, this parameter maps to the total number of database instances that you have. Configure this value in the following file for your database.
  • 17-ConfigIDGen.sql. The default value is 1.
PRIMARY_ID_GRP_NUM In the context of the example in ID range adjustment, this parameter is the specific instance number of this database instance. Ensure that you are not reusing a PRIMARY_ID_GRP_NUM that is already being used by another database instance in the group. Configure this value in the following file for your database.
  • 17-ConfigIDGen.sql. The default value is 1.
SECONDARY_ID_GRP_SIZE In the context of the example in ID range adjustment, this parameter maps to the number of partitions. Configure this value in the following file for your database.
  • 17-ConfigIDGen.sql. The default value is 1.
SEQ_MAX The sequences need to be defined with the correct Max Value (working ID range). Calculation of this value is demonstrated in Sequence MAXVALUE calculations. Configure this value in the following file for your database.
  • 02-NewID.sql. The default value is 36028797018963968.

Sequence MAXVALUE calculations

The MAXVALUE can be calculated by using the following formula: 
MAXVALUE = 2^63 / ( PRIMARY_ID_GRP_SIZE * SECONDARY_ID_GRP_SIZE)
For reference, the MAXVALUE of a sequence is used to generate the ID offset by using the following formula.
IDOffset = ((PRIMARY_ID_GRP_NUM * SECONDARY_ID_GRP_SIZE) + SECONDARY_ID_GRP_NUM) * MAXVALUE

Decimal ranged example for sequence MAXVALUE

For 10 instances and 20 partitions (200 ID regions), the result of the formula is:

MAXVALUE = 2^63 / (10 * 20) = 46116860184273879

For convenience, you might want to round down to 46000000000000000 or 40000000000000000.

Binary ranged example for sequence MAXVALUE

For 8 instances and 32 partitions (256 or 2^8 ID regions), the result of the formula is:

MAXVALUE = 2^63 / 256 = 36028797018963968

Or

MAXVALUE = 2^63 / 2^8 = 2^55

This example assumes that you want to make full use of the ID range, therefore the result is not rounded down.