Sequence Transformation

Chapter 19: Sequence Generator

• Passive and Connected. As it has to inputs we will consider it as passive. Sequence Generator cannot br unconnected.
• It contains two output ports that you can connect to one or more transformations.
• If you connect CURRVAL, the Integration Service processes one row in each block. When NEXTVAL is connected to the input port of another transformation, the Integration Service generates a sequence of numbers. When CURRVAL is connected to the input port of another transformation, the Integration Service generates the NEXTVAL value plus the Increment By value.
• You can make a Sequence Generator reusable, and use it in multiple mappings. This may be useful when we wish to load data to target from multiple mappings.

Ex: if you have a large input file that you separate into three sessions running in parallel, use a Sequence Generator to generate primary key values. If you use different Sequence Generators, the Integration Service might generate duplicate key values. Instead, use the reusable Sequence Generator for all three sessions to provide a unique value for each target row.

• Following are the common usages of Sequence Generator:

♦ Create keys.

♦ Replace missing values.

♦ Cycle through a sequential range of numbers.

Creating keys:

You can create primary or foreign key values with the Sequence Generator transformation by connecting the NEXTVAL port to a target or downstream transformation.

You can use a range of values from 1 to 9,223,372,036,854,775,807 with the smallest interval of 1.

To create a composite key, you can configure the Integration Service to cycle through a smaller set of values. For example, if you have three stores generating order numbers, you might have a Sequence Generator cycling

through values from 1 to 3, incrementing by 1.

Replacing Missing Values:

Use the Sequence Generator transformation to replace missing keys by using NEXTVAL with the IIF and ISNULL functions.

For example, to replace null values in the ORDER_NO column, you create a Sequence Generator transformation with the properties and drag the NEXTVAL port to an Expression transformation. In the Expression transformation, drag the ORDER_NO port into the transformation along with any other necessary ports. Then create an output port, ALL_ORDERS.

In ALL_ORDERS, you can then enter the following expression to replace null orders:

IIF( ISNULL( ORDER_NO ), NEXTVAL, ORDER_NO )

• Sequence generator ports: The Sequence Generator transformation has two output ports: NEXTVAL and CURRVAL.

You cannot edit or delete these ports.

Likewise, you cannot add ports to the transformation.

NEXTVAL: Use the NEXTVAL port to generate sequence numbers by connecting it to a downstream transformation or target. You connect the NEXTVAL port to generate the sequence based on the Current Value and Increment By properties.

Use the NEXTVAL port to generate sequence numbers by connecting it to a downstream transformation or target. You connect the NEXTVAL port to generate the sequence based on the Current Value and Increment By

properties.

For example, you configure the Sequence Generator transformation as follows: Current Value = 1, Increment By = 1. The Integration Service generates the following primary key values for the

T _ORDERS_PRIMARY and T_ORDERS_FOREIGN target tables:

If you want the same values to go to more than one target that receives data from a single transformation, you can connect a Sequence Generator transformation to that preceding transformation. The Integration Service

processes the values into a block of sequence numbers. This allows the Integration Service to pass unique values to the transformation, and then route rows from the transformation to targets.

CURRVAL:

CURRVAL=NEXTVAL + Increment By value

We typically connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.

For example, you configure the Sequence Generator transformation as follows: Current Value = 1, Increment By = 1. The Integration Service generates the following values for NETVAL and CURRVAL:

NEXTVAL CURRVAL

1 2

2 3

3 4

4 5

5 6

If you connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.

• Sequence Generator transformation properties:

Setting	Description
Start Value	Used when we use the cycle option. If you select Cycle, the Integration Service cycles back to this value when it reaches the end value. Default is 0. Maximum value is 9,223,372,036,854,775,806
Increment By	Difference between two consecutive values from the NEXTVAL port. Default is 1. Maximum value is 2,147,483,647.
End Value	Maximum value the Integration Service generates. If the Integration Service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807. If the cycle option is used end value indicates the value after which it should go back to Start Value.
Current Value	Current value of the sequence. . If you want to cycle through a series of values, the value must be greater than or equal to the start value and less than the end value.
Cycle	If enabled, the Integration Service cycles through the sequence range. If disabled, the Integration Service stops the sequence at the configured end value. The Integration Service fails the session with overflow errors if it reaches the end value and still has rows to process.
Number of Cached Values	Genarally used with reusable sequence generator where same sequence is shared by several sessoions. Number of sequential values the Integration Service caches at a time. Use this option when multiple sessions use the same reusable Sequence Generator at the same time to ensure each session receives unique values. The Integration Service updates the repository as it caches each value. When set to 0, the Integration Service does not cache values. Default value for a standard Sequence Generator is 0. Default value for a reusable Sequence Generator is 1,000. Maximum value is 9,223,372,036,854,775,807
Reset	If enabled, the Integration Service generates values based on the original current value for each session. Otherwise, the Integration Service updates the current value to reflect the last-generated value for the session plus one, and then uses the updated current value as the basis for the next session run. Might prove useful when testing a mapping and then setting the value of currval to something that was prior to testing
Tracing Level	Level of detail about the transformation that the Integration Service writes into the session log.

• Creating a cycle of values
  1. Enter the lowest value in the sequence that you want the Integration Service to use for the Start Value.
  2. Enter the highest value to be used for End Value.
  3. Select Cycle.
  As it cycles, the Integration Service reaches the configured end value for the sequence, it wraps around and starts the cycle again, beginning with the configured Start Value.

• Increment value: The Integration Service generates a sequence (NEXTVAL) based on the Current Value and Increment By properties in the Sequence Generator transformation.

Imp: Think of current value as the value at which the Integration Service starts creating the sequence for each session.Thus when we start the session the currval becomes nextval and is output with the 1^st row.

By default, the Current Value is set to 1, and Increment By is set to 1.

For example, you might create a Sequence Generator transformation with a current value of 1,000 and an i ncrement of 10. If you pass three rows through the mapping, the Integration Service generates the following set of values:

1000 → currval value comes out as first nextval

1010

1020

• Current Value: To indicate which value you want the Integration Service to use the first time it uses the Sequence Generator transformation, you must enter that value as the current value. If you want to use the Sequence Generator transformation to cycle through a series of values, the current value must be greater than or equal to Start Value and less than the end value.
  At the end of each session, the Integration Service updates the current value to the last value generated for the session plus one
  For Example: value generated for the session plus one if the Sequence Generator Number of Cached Values is 0. For example, if the Integration Service ends a session with a generated value of 101, it updates the Sequence Generator current value to 102 in the repository. When the Integration Service starts another session using the Sequence Generator, the first generated value is 102.

• Number of Cached Values:

Number of Cached Values determines the number of values the Integration Service caches at one time.

When multiple sessions use the same reusable Sequence Generator transformation at the same time, there might be multiple instances of the Sequence Generator transformation. To avoid generating the same values for each session, reserve a range of sequence values for each session by configuring Number of Cached Values.

To increase performance when running a session on a grid, increase the number of cached values. This reduces the communication required between the master and worker DTM processes and the repository.

Number of cached values for non-reusable sequence generators:

> For non-reusable Sequence Generator transformations, Number of Cached Values is set to zero by default, and the Integration Service does not cache values during the session. When the Integration Service does not cache values, it accesses the repository for the current value at the start of a session. The Integration Service then generates values for the sequence. At the end of the session, the Integration Service updates the current value in the repository.

> When you set Number of Cached Values greater than zero, the Integration Service caches values during the session. At the start of the session, the Integration Service accesses the repository for the current value, caches the configured number of values, and updates the current value accordingly. If the Integration Service uses all values in the cache, it accesses the repository for the next set of values and updates the current value. At the end of the session, the Integration Service discards any remaining values in the cache.

Problem with using cached values with non-reusable sequence generator:

1. setting Number of Cached Values greater than zero can increase the number of times the Integration Service accesses the repository during the session.

2. It also causes sections of skipped values since unused cached values are discarded at the end of each session.

Example: you configure a Sequence Generator transformation as follows: Number of Cached Values = 50, Current Value = 1, Increment By = 1. When the Integration Service starts the session, it caches 50 values for the

session and updates the current value to 50 in the repository. The Integration Service uses values 1 to 39 for the session and discards the unused values, 40 to 49. When the Integration Service runs the session again, it checks the repository for the current value, which is 50. It then caches the next 50 values and updates the current value to 100. During the session, it uses values 50 to 98. The values generated for the two sessions are 1 to 39 and 50 to 98.

Number of cached values for reusable sequence generators:

When you have a reusable Sequence Generator transformation in several sessions and the sessions run at the same time, use Number of Cached Values to ensure each session receives unique values in the sequence.

By default, Number of Cached Values is set to 1000 for reusable Sequence Generators.

When multiple sessions use the same Sequence Generator transformation at the same time, you risk generating the same values for each session. To avoid this, have the Integration Service cache a set number of values for

each session by configuring Number of Cached Values.

Example:

you configure a reusable Sequence Generator transformation as follows: Number of Cached Values = 50, Current Value = 1, Increment By = 1. Two sessions use the Sequence Generator, and they are scheduled to

run at approximately the same time. When the Integration Service starts the first session, it caches 50 values for the session and updates the current value to 50 in the repository. The Integration Service begins using values 1

to 50 in the session. When the Integration Service starts the second session, it checks the repository for the current value, which is 50. It then caches the next 50 values and updates the current value to 100. It then uses values 51 to 100 in the second session. When either session uses all its cached values, the Integration Service caches a new set of values and updates the current value to ensure these values remain unique to the Sequence Generator.

To reduce the number of discarded values we can reduce Number of Cached Values.

However when you reduce the Number of Cached Values, you might increase the number of times the Integration Service accesses the repository to cache values during the session.

• Reset:

If you select Reset for a non-reusable Sequence Generator transformation, the Integration Service generates values based on the original current value each time it starts the session.

Example: For example, you might configure a Sequence Generator transformation to create values from 1 to 1,000 with an increment of 1, and a current value of 1 and choose Reset. During the first session run, the Integration Service generates numbers 1 through 234. Each subsequent time the session runs, the Integration Service again generates numbers beginning with the current value of 1.

If you do not select Reset, the Integration Service updates the current value to 235 at the end of the first session run. The next time it uses the Sequence Generator transformation, the first value generated is 235.

• naming convention: SEQ_Tr a n s f o r m a t i o n N a m e