PowerCenter - Sequence Generator Transformation

About

A Sequence Generator PowerCenter - Transformations generates unique values, such as primary keys, for a target in a mapping. You can also use it to cycle through a closed set of values.

Many relational databases include sequences, which are special database objects that generate values. The Sequence Generator transformation functions like a sequence object in a database.

However, in PowerCenter, you do not need to write SQL code to create and use the sequence in a PowerCenter - Mapping.

The Sequence Generator transformation has the following properties:

• The starting number (normally 1).
• The current value stored in the repository.
• The number that the Sequence Generator transformation adds to its current value for every request for a new ID.
• The maximum value in the sequence.
• A flag indicating whether the Sequence Generator transformation counter resets to the minimum value once it has reached its maximum value.

The Sequence Generator transformation has two output ports, NEXTVAL and CURRVAL, which correspond to the two pseudo-columns in a sequence. When you query a value from the NEXTVAL port, the transformation generates a new value.

Articles Related

How to

To create the Sequence Generator transformation

• Create a Sequence Generator transformation and name it SEQ_TransformationName.
• Open the Sequence Generator transformation.
• Click the Ports tab. The two output ports, NEXTVAL and CURRVAL, appear in the list.

Note: You cannot add any new ports to this transformation or reconfigure NEXTVAL and CURRVAL.

• Click the Properties tab.

The properties for the Sequence Generator transformation appear. You do not have to change any of these settings.

• Click OK.
• Connect the NEXTVAL column from the Sequence Generator transformation to the ID column in the target table.
• Click Repository > Save.

Push the sequence to the database

To know all about how to push the sequence transformation logic into a database, see the doc PC_861_AdvWorkflowGuide.pdf Chapter 4 Working with sequence and view