Sequence Generator Transformation in Informatica, is a Passive and connected transformation which let you generate numeric sequence unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers. In below article we will go through the details of Sequence generator transformation in Informatica.
Is Sequence generator Transformation Active or Passive?
Sequence Generator Transformation in informatica is a passive transformation as it is not changing no of rows. It is just adding extra port value.
Is Sequence generator Transformation Connected or Unconnected?
Sequence Generator transformation in informatica is a Connected Transformation.
Use of Sequence Generator Transformation in Informatica
The Sequence Generator transformation generates numeric values. You can use Sequence generator in following scenarios.
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.
When you 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:
Replacing Missing Values
Use the Sequence Generator transformation to replace missing keys by using NEXTVAL with the IIF and ISNULL functions.
For example, to replace null values in the ORDER_NO column, you create a Sequence Generator transformation with the properties and drag the NEXTVAL port to an Expression transformation. In the Expression transformation, drag the ORDER_NO port into the transformation along with any other necessary ports. Then create an output port, ALL_ORDERS. In ALL_ORDERS, you can then enter the following expression to replace null orders:
IIF( ISNULL( ORDER_NO ), NEXTVAL, ORDER_NO )
Check here for Normalizer transformation in informatica
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 Port in Sequence Generator
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.
For example, you might connect NEXTVAL to two targets in a mapping to generate unique primary key values.The Integration Service creates a column of unique primary key values for each target table. The column of unique primary key values is sent to one target table as a block of sequence numbers. The other target receives a block of sequence numbers from the Sequence Generator transformation after the first target receives the block of sequence numbers.
For example, you configure the Sequence Generator transformation as follows: Current Value = 1, Increment By = 1. The Integration Service generates the following primary key values for the T_ORDERS_PRIMARY and T_ORDERS_FOREIGN target tables.
If you want the same values to go to more than one target that receives data from a single transformation, you can connect a Sequence Generator transformation to that preceding transformation. The Integration Service processes the values into a block of sequence numbers. This allows the Integration Service to pass unique values to the transformation, and then route rows from the transformation to targets.
In below mapping with a Sequence Generator that passes unique values to the Expression transformation in informatica. The Expression transformation populates both targets with identical primary key values.
For example, you configure the Sequence Generator transformation as follows: Current Value = 1,Increment By = 1. The Integration Service generates the following primary key values for the T_ORDERS_PRIMARY and T_ORDERS_FOREIGN target tables.
When you run a partitioned session on a grid, the Sequence Generator transformation skips values depending on the number of rows in each partition.
Check here for Lookup transformation in informatica
CURRVAL Port in Sequence Generator Transformation
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.
For example, you configure the Sequence Generator transformation as follows: Current Value = 1,Increment By = 1. The Integration Service generates the following values for NEXTVAL and CURRVAL.
If you connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row. When you connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. You can optimize performance by connecting only the NEXTVAL port in a mapping.
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.
In next topic we will cover, how to use sequence generator transformation in informatica mapping and properties of sequence generator transformation.