Shared Sequences are reusable sequences that can be used with multiple Sequence Generator transformations in Informatica Cloud (IICS). The Sequence Generator transformation inherits the properties of a shared sequence to generate unique values when a shared sequence is selected.
Use a shared sequence when you want to assign values with same sequence through multiple mapping tasks.
For example, if you have two separate mappings which load employee data into a table. Use a Shared Sequence in order to create a unique Employee ID value without any duplicates when any of the mappings run.
To create a shared sequence in Informatica Cloud Data Integration
- Click on New on home page.
- From the pop-up, click Components, select Shared Sequence and click Create.
- Enter Name and define the Sequence Properties and click save.
To use a Shared Sequence in a Sequence Generator transformation in a Informatica Cloud mapping.
- Open the mapping.
- Add a Sequence generator transformation to the mapping canvas and connect it to a downstream transformation.
- Navigate to Sequence tab and select Use a Shared Sequence.
- Select the Shared Sequence already available. Shared Sequence cannot be created on the fly from sequence generator.
- Configure other properties in Advanced tab to use the sequence as either connected or unconnected transformation.
The Sequence Generator transformation generates a non-shared sequence if you do not select Use Shared Sequence option and the sequence is local to the mapping used.
The following table lists the properties of Shared Sequence.
| Property | Description |
| Increment By | This is the number by which you want to increment the sequence values. The default value is 1. |
| End Value | It is the maximum value that the sequence generator transformation generates. Maximum is 9,223,372,036,854,775,807. |
| Initial Value | It is the first value that is generated by the sequence generator transformation. The default value is 0. If you reset the shared sequence, the sequence is reset to this value. |
| Cycle | If enabled, after reaching the end value, the transformation restarts from the initial value. By default, the option is disabled. If disabled and the sequence reaches the end value specified with rows still to process, the task would fail with overflow error. |
| Cycle Start Value | Start value you want the sequence generator to use when cycle option is enables. The default value is 0. |
| Number of Reserved Values | The number of sequence values the mapping task caches at one time during each task run. Must be greater than or equal to 1000. |
| Current Value | Displays the current start value in the sequence. |
The Number of Reserved Values property in Shared Sequence determines the number of sequence values the mapping task caches at one time during each task run.
By default, the number of reserved values is 1000. The number of reserved values cannot be less than 1000.
When multiple Sequence Generator transformations use the same Shared Sequence, multiple instances of the shared sequence can exist at the same time.
To avoid generating duplicate values, Data Integration service reserves a range of sequence values for each mapping using the value configured under number of reserved values.
For example, if you have a shared sequence configured as below
- Initial Value = 1
- Increment By = 1
- Number of Reserved Values = 1000
Two mappings are using the shared sequence.
- When the first mapping task runs, Data Integration reserves 1000 values (1-1000) for the mapping task and updates the current value in the repository to 1001.
- When the second mapping task runs, Data Integration reserves the next 1000 values (1001-2000) and updates the current value to 2001.
- When either mapping task uses all its reserved values, Data Integration reserves a new set of values and updates the current value.
The number of values that Data Integration reserves in a sequence is determined by multiplying the Number of Reserved Values by the Increment By value.
For example, if you have a shared sequence configured as below
- Initial Value = 5
- Increment By = 5
- Number of Reserved Values = 1000
Data Integration reserves 1000 values in the sequence i.e., numerical values 5-5000, and updates the current value to 5005.
The Sequence values that are reserved for a task run but not used in the task are lost when the task completes.
For example, if you have a shared sequence configured as below
- Initial Value = 1
- Increment By = 1
- Number of Reserved Values = 1000
The data you are processing has only 750 records. When the task runs, Data Integration reserves 1000 values for the task and updates the current value to 1001. When the next task runs, the sequence begins at 1001, and values 751-1000 are lost.
Increasing the number of reserved values increases the performance. This reduces the number of calls that Data Integration must make to the repository.
Sequence generator can be used in two different ways in Informatica cloud. One with Incoming fields disabled and the other with incoming fields not disabled.
The Shared sequence will inherit the properties of the sequence generator it is attached to i.e.
- Share Sequence used in sequence generator with incoming fields not disabled will generate same sequence of numbers for each downstream transformation.
- Shared Sequence used in sequence generator with incoming fields disabled will generate Unique sequence of numbers for each downstream transformation.
In order to reset a Shared Sequence to defined Initial value in properties
- Open the shared sequence.
- Click Edit.
- Click Reset Current Value in the Sequence Properties area.
- Save the shared sequence.
Reset Current Value option is not available while creating the shared sequence. It appears after the shared sequence is created and again opened in Edit mode.
8. Conclusion
Shared Sequences are a great way to generate unique sequence values from multiple mappings. But they have their own limitations.
Shared sequences comes with a reserved value property with a minimum value of 1000. So, if you are processing only 700 records in a mapping run, you will be losing the sequence values from 701 to 1000. This results in a gap in the sequence values in the table for which you are generating sequence values.
If the requirement is only to generate sequence values, shared sequence serve the purpose. But if you do not want to lose the sequence values in between, you will have to look for alternatives like database sequences.
