Sequence Generator Transformation in Infotmatica

  • Updated
  • Posted in Programming
  • 4 mins read


Sequence generator transformation is a passive and related transformation. The sequence generator transformation is used for

  • Generating distinctive major key values.
  • Replace lacking major keys
  • Generate surrogate keys for dimension tables in SCDs.
  • Cycle by means of a sequential vary of numbers.

Creating Sequence Generator Transformation:

Follow the beneath steps to create a sequence generator transformation:

  • Go to the mapping designer tab in energy heart designer.
  • Click on the transformation in the toolbar after which on create.
  • Select the sequence generator transformation. Enter the title after which click on on Create. Click Done.
  • Edit the sequence generator transformation, go to the properties tab and configure the choices.
  • To generate sequence numbers, join the NEXTVAL port to the transformations or goal in the mapping.

Configuring Sequence Generator Transformation:

Configure the next properties of sequence generator transformation:

  • Start Value: Specify the beginning worth of the generated sequence that you really want the combination service to make use of the cycle possibility. If you choose cycle, the combination service cycles again to this worth when it reaches the top worth.
  • Increment By: Difference between two consecutive values from the NEXTVAL port. Default worth is 1. Maximum worth you possibly can specify is 2,147,483,647.
  • End Value: Maximum sequence worth the combination service generates. If the combination service reaches this worth in the course of the session and the sequence is just not configured to cycle, the session fails. Maximum worth is 9,223,372,036,854,775,807.
  • Current Value: Current Value of the sequence. This worth is used as the primary worth in the sequence. If cycle possibility is configured, then this worth have to be higher than or equal to begin worth and fewer than finish worth.
  • Cycle: The integration service cycles by means of the sequence vary.
  • Number of Cached Values: Number of sequential values the combination service caches at a time. Use this selection when a number of classes use the identical reusable generator. Default worth for non-reusable sequence generator is 0 and reusable sequence generator is 1000. Maximum worth is ,223,372,036,854,775,807.
  • Reset: The integration service generate values primarily based on the unique present worth for every session. Otherwise, the combination service updates the present worth to replicate the last-generated worth for the session plus one.
  • Tracing degree: The degree of element to be logged in the session log file.

Sequence Generator Transformation Ports:

The sequence generator transformation incorporates solely two output ports. They are CURRVAL and NEXTVAL output ports.

NEXTVAL Port:

You can join the NEXTVAL port to a number of transformations to generate the distinctive values for every row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is just not configure to Cycle, then the NEXTVAL port generates the sequence numbers as much as the configured End Value.

The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the following block of sequence numbers. As an instance, let say you related the nextval port to 2 targets in a mapping, the combination service generates a block of numbers (eg:1 to 10) for the primary goal after which one other block of numbers (eg:11 to twenty) for the second goal.

If you need the identical sequence values to be generated for multiple goal, then join the sequence generator to an expression transformation and join the expression transformation port to the targets. Another possibility is create sequence generator transformation for every goal.

CURRVAL Port:

The CURRVAL is the NEXTVAL plus the Increment By worth. You not often join the CURRVAL port to different transformations. When a row enters a change related to the CURRVAL port, the combination service passes the NEXTVAL worth plus the Increment By worth. For instance, once you configure the Current Value=1 and Increment By=1, then the combination service generates the next values for NEXTVAL and CURRVAL ports.

NEXTVAL CURRVAL
---------------
1 2
2 3
3 4
4 5
5 6

If you join solely the CURRVAL port with out connecting the NEXTVAL port, then the combination service passes a relentless worth for every row.

Recommended Reading:

Reusable vs Non Reusable Sequence Generator
SQL Transformation
Informatica Real Time Scenarios – Solutions
Stored Procedure Transformation in Informatica
Lookup Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply