Sequence Generator Transformation
Sequence generator is a passive and connected transformation, and it generates numeric sequence values such as 1, 2, 3, and so on. It does not affect the number of input rows.
The Sequence Generator transformation is used to create unique primary key values and replace missing primary keys.
For example, if we want to assign sequence values to the source records, then we need to use a sequence generator.
The sequence generator transformation consists of two output ports. We cannot edit or delete these ports, such as:
- CURRVAL
- NEXTVAL
NEXTVAL
The NEXTVAL port is used to generate sequence numbers by connecting it to a Transformation or target. The generated sequence numbers are based on the Current Value and Increment By properties.
If the sequence generator is not configuring to Cycle, then the NEXTVAL port makes the sequence numbers up to the set End Value.
We can connect the NEXTVAL port to multiple transformations to generate unique values for each row.
The sequence generator transformation creates a block of numbers at the same time. If the block of numbers is used, then it generates the next block of sequence numbers.
For example, we might connect NEXTVAL to two target tables in mapping to create unique primary key values.
The integration service generates a block of numbers 1 to 10 for the first target. When the first block of numbers has been loaded, only then another block of numbers 11 to 20 will be generated for the second target.
CURRVAL
The CURRVAL port is NEXTVAL plus the Increment By value.
We only connect the CURRVAL port when the NEXTVAL port is already linked to a downstream transformation.
If we combine the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
When we combine the CURRVAL port in a Sequence Generator Transformation, then the Integration Service processes one row in each block.
We can optimize performance by connecting only the NEXTVAL port in a Mapping.
Example: Suppose STUD will be a source table.
Create a target STUD_SEQ_GEN_EXAMPLE in the shared folder. Structure the same as STUD. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.
We can create a Sequence Generator transformation to use in a single mapping, or a reusable Sequence Generator transformation to use in multiple mappings.
A reusable Sequence Generator transformation maintains the integrity of the sequence in each mapping that uses an instance of the Sequence Generator transformation.
Properties of Sequence Generator Transformation
Below are the following properties to configure a sequence data object and a new sequence:
| Property | Description |
|---|---|
| Start Value | The start value of the generated sequence is the Integration Service when using the Cycle option. If we select Cycle, the Integration Service cycles back to this value when it reaches the end value. The default value is 0. Maximum value is 9,223,372,036,854,775,806. |
| End Value | The maximum value that the Integration Service generates. If the Integration Service reaches this value during the session, and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807. |
| Increment Value | Difference between two consecutive values from the NEXTVAL port. The default value is 1. And it must be a positive integer. Maximum value is 2,147,483,647. |
| Cycle | If enabled, the Integration Service cycles through the sequence range and start over with the start value. If disabled, the Integration Service stops the sequence at the configured end value. The Integration Service fails the session with overflow errors if it reaches the end value and still has rows to process. |
| Reset | If enabled, the Integration service resets the sequence data object to the start value when the mapping completely run. If disabled, the Integration Service increments the current value after the mapping run ends, and uses that value in the next mapping run. This property is disabled for reusable Sequence Generator transformations and for non-reusable Sequence Generator transformations that use a reusable sequence data object. |
| Tracing Level | Level of detail about the transformation that the Integration Service writes into the mapping log. We can choose terse, normal, verbose initialization or verbose data. Normal sets as a default level. |
| Maintain Row Order | Maintain the row order of the input data to the transformation. Select this option if the Integration Service should not perform any optimization that can change the row order. |
Example
In the below example, we will generate sequence numbers and store in the target in the following steps, such as:
Step 1: Create a target table.
Step 2: Import that created table in Informatica as the target table.
Step 3: Create a new mapping and import STUD source and STUD_SEQUENCE target table.
Step 4: Create a new transformation in the mapping,
- Select sequence transformation as the type.
- Enter transformation name such as seq_stud.
- Click on the Create
Step 5: Sequence generator transformation will be created, then click on the Done button.
Step 6: Link the NEXTVAL column of sequence generator to the SNO column in the target table.
Step 7: Link the other columns from source qualifier transformation to the target table.
Step 8: Double click on the sequence generator to open the property window, and then
- Select the properties tab.
- Enter the properties with Start value =1 and leave the other properties as default.
- Click on the OK
Now save the mapping and execute it after creating the session and workflow.
The SNO column in the target would contain the sequence numbers generated by the sequence generator transformation.