Sequence Generator Ports
The Sequence Generator transformation has pass-through ports and one output port, NEXTVAL. You cannot edit or delete the output port.
Pass-Through Ports
You can add a port to the Sequence Generator transformation as a pass-through port. Pass-through ports are input and output ports that receive input data and return the same data to a mapping without changing it.
You must add at least one input port to the transformation and connect it to an upstream source or transformation before you link the NEXTVAL output port to targets. To add a pass-through port to the transformation, drag a port from an upstream source or transformation in the mapping to the Sequence Generator transformation.
NEXTVAL Port
You can connect NEXTVAL to a transformation to generate unique values for each row in the transformation. Connect the NEXTVAL port to a downstream transformation or target to generate a sequence of numbers. If you connect NEXTVAL to multiple transformations, the Integration Service generates the same sequence of numbers for each transformation.
You connect the NEXTVAL port to generate the sequence based on the Start Value and Increment Value 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.
The following image shows a mapping with the Sequence Generator transformation NEXTVAL port connected to a source and two targets to generate primary and foreign key values:
When you configure the Sequence Generator transformation with a Start Value = 1 and an Increment Value = 1, the Integration Service generates the same primary key values for the T_ORDERS_PRIMARY and T_ORDERS_FOREIGN target tables.
Create Keys
You 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. You can also create composite keys to identify each row in the table.
To create a composite key, you can configure the Integration Service to cycle through a smaller set of values. For example, if three stores generate order numbers, configure a Sequence Generator transformation to cycle through values from 1 to 3, incrementing by 1. When you connect the ORDER_NO port to the Sequence Generator transformation, the generated values create unique composite keys.
The following example shows composite keys and order numbers:
COMPOSITE_KEY | ORDER_NO |
|---|
1 | 12345 |
2 | 12345 |
3 | 12345 |
1 | 12346 |
2 | 12346 |
3 | 12346 |
Replace Missing Values
When you use a Sequence Generator transformation to replace missing keys, you can also use a Router transformation to filter null values from columns that have values assigned. You connect the Router transformation to the Sequence Generator transformation, and use NEXTVAL to generate a sequence of numeric values to populate the null values.
For example, to replace null values in a CUSTOMER_NO column, you create a mapping with a source that contains customer data. You add a Router transformation to filter customers with customer numbers assigned from those with null values. You add a Sequence Generator transformation to generate unique CUSTOMER_NO values. You add customer targets to write the data to.
The following image shows a mapping that replaces null values in the CUSTOMER_NO column:
