Sequence generator is a passive transformation which is used to generate sequence numbers from informatica. It can also be used to generate primry key vlues.
Sequence Generator has two output ports
Nextval
Currval
Nextval:
NEXTVAL can be connected to multiple transformations to generate a unique value for each row/ transformation. NEXTVAL uses the Current Value and Increment By properties of the transformation to generate the next sequence number. Unless the transformation is set to recycle numbers, it would keep on incrementing sequence numbers till it reaches an end value of 9,223,372,036,854,775,807.
Currval:
Currval is connected when nextval is alredy connected to some other transformation in the map. Integration service generates CURRVAL is last created Nextval+increment by value.
Configuring properties
Start value: Specify the start value of the generated sequence that you want the integration service to use the cycle option. This is the first value sed by the transformation.
Increment by: Difference between two values from NEXTVAL port. The default value is 1. Maximum value we can specify is 2,147,483,647.
End Value: End value is the maximum value we can specified. The maximum value is 9,223,372,036,854,775,807.
Current Value: Current value is the value that integration service uses for the first time. In case, you’ve setup the transformation to cycle through a set of values, the current value must be greater than or equal to the ‘Start Value’ and less than then ‘End Value’.
Cycle: Cycle will repeat the sequence numbers between start and end values.
Number of Cached values: Cacheing can help store values in multiple sessions are using a reusable sequential generator. In case of non reusable sequence generator the default value is zero.
Reset: Reset will start from the current value.
Implementing Sequence Generator
Sequence Generator has two output ports
Nextval
Currval
NEXTVAL can be connected to multiple transformations to generate a unique value for each row/ transformation. NEXTVAL uses the Current Value and Increment By properties of the transformation to generate the next sequence number. Unless the transformation is set to recycle numbers, it would keep on incrementing sequence numbers till it reaches an end value of 9,223,372,036,854,775,807.
Currval:
Currval is connected when nextval is alredy connected to some other transformation in the map. Integration service generates CURRVAL is last created Nextval+increment by value.
Configuring properties
Increment by: Difference between two values from NEXTVAL port. The default value is 1. Maximum value we can specify is 2,147,483,647.
End Value: End value is the maximum value we can specified. The maximum value is 9,223,372,036,854,775,807.
Current Value: Current value is the value that integration service uses for the first time. In case, you’ve setup the transformation to cycle through a set of values, the current value must be greater than or equal to the ‘Start Value’ and less than then ‘End Value’.
Cycle: Cycle will repeat the sequence numbers between start and end values.
Number of Cached values: Cacheing can help store values in multiple sessions are using a reusable sequential generator. In case of non reusable sequence generator the default value is zero.
Reset: Reset will start from the current value.
Implementing Sequence Generator
- Drag source to the designer
- Create target adding with SNO column and drag it to the designer.
- Go to transformation->create->Sequence Generator->OK
- Connect NEXTVAL port to the SNO in the target.
- Connect Remaining all ports from Source qualifier to target.
- The output preview shown above.
