Getting Started

10.5.1
- 10.5.6
- 10.5
- 10.4.0

Back Next

Creating a Sequence Generator Transformation

A Sequence Generator transformation 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 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.

In the new mapping, you add a Sequence Generator transformation to generate IDs for the fact table F_PROMO_ITEMS. Every time the Integration Service inserts a new row into the target table, it generates a unique ID for PROMO_ITEM_ID.

Create a Sequence Generator transformation and name it SEQ_PromoItemID.

Open the Sequence Generator transformation.

Click the

Ports

tab.

The two output ports, NEXTVAL and CURRVAL, appear in the list.

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