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.

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


Powered by ComboStrap