- Sequence Generator is an Passive and Connected.
- The Sequence Generator transformation generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
- It contains two output ports that you can connect to one or more transformations. The Integration Service generates a block of sequence numbers each time a block of rows enters a connected transformation. If you connect CURRVAL, the Integration Service processes one row in each block. When NEXTVAL is connected to the input port of another transformation, the Integration Service generates a sequence of numbers. When CURRVAL is connected to the input port of another transformation, the Integration Service generates the NEXTVAL value plus the Increment By value.
Common Uses for Sequence Generator
You can complete the following tasks with a Sequence Generator transformation:
- Create keys.
- Replace missing values.
- Cycle through a sequential range of numbers.
Creating Keys
We can create primary or foreign key values with the Sequence Generator transformation by connecting the NEXTVAL port to a target or downstream transformation. You can use a range of values from 1 to 9,223,372,036,854,775,807 with the smallest interval of 1.
When we create primary or foreign keys, use the Cycle option to prevent the Integration Service from creating duplicate primary keys. You might do this by selecting the Truncate Target Table option in the session properties or by creating composite keys.
To create a composite key, you can configure the Integration Service to cycle through a smaller set of values. For example, if you have three stores generating order numbers, you might have a Sequence Generator cycling through values from 1 to 3, incrementing by 1. When you pass the following set of foreign keys, the generated values then create unique composite keys:
COMPOSITE_KEY
|
ORDER_NO
|
1
|
12345
|
2
|
12345
|
3
|
12345
|
1
|
12346
|
2
|
12346
|
3
|
12346
|
Sequence Generator Ports
The Sequence Generator transformation has two output ports: NEXTVAL and CURRVAL. You cannot edit or delete these ports. Likewise, you cannot add ports to the transformation.
NEXTVAL
Connect NEXTVAL to multiple transformations to generate unique values for each row in each transformation. Use the NEXTVAL port to generate sequence numbers by connecting it to a downstream transformation or target. You connect the NEXTVAL port to generate the sequence based on the Current Value and Increment By properties. If the Sequence Generator is not configured to cycle through the sequence, the NEXTVAL port generates sequence numbers up to the configured End Value.
Note: When you run a partitioned session on a grid, the Sequence Generator transformation skips values depending on the number of rows in each partition.
CURRVAL
CURRVAL is NEXTVAL plus the Increment By value. You typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation. When a row enters a transformation connected to the CURRVAL port, the Integration Service passes the last created NEXTVAL value plus one.
Note: When you run a partitioned session on a grid, the Sequence Generator transformation might skip values depending on the number of rows in each partition.
Sequence Generator Transformation Properties
The Sequence Generator transformation is unique among all transformations because you cannot add, edit, or delete the default ports, NEXTVAL and CURRVAL.
Start Value and Cycle
Use Cycle to generate a repeating sequence, such as numbers 1 through 12 to correspond to the months in a year.
To cycle the Integration Service through a sequence:
- Enter the lowest value in the sequence that you want the Integration Service to use for the Start Value.
- Enter the highest value to be used for End Value.
- Select Cycle.
As it cycles, the Integration Service reaches the configured end value for the sequence, it wraps around and starts the cycle again, beginning with the configured Start Value.
NOTE:
Start Value: By default it is 0, It starts sequence generating values up to Maximum value is 9,223,372,036,854,775,806.
Increment By: By Default is 1, It is difference between two consecutive values from the NEXTVAL port and Maximum value is 2,147,483,647.
End Value: Maximum value is 9,223,372,036,854,775,807.
Current Value: Integration Service to use as the first value in the sequence, the value must be greater than or equal to the start value and less than the end value.
b If enabled, the Integration Service cycles through the sequence range.
Number of Cached Values: Number of sequential values the Integration Service caches at a time.
Reset: Integration Service generates values based on the original current value for each session, Otherwise, the Integration Service updates the current value to reflect the last-generated value for the session plus one, and then uses the updated current value as the basis for the next session run. Disabled for reusable Sequence Generator transformations.