[box type=”note” align=”” class=”” width=””]This article is an excerpt from a book by Rahul Malewar titled Learning Informatica PowerCenter 10.x. This book will guide you through core functionalities offered by Informatica PowerCenter version 10.x. [/box]
Our article covers how sequence generators are used to generate primary key values or a sequence of unique numbers. It also showcases the ports associated and the properties of sequence generators.
A sequence Generator transformation is used to generate a sequence of unique numbers. Based on the property defined in the Sequence Generator transformation, the unique values are generated. A sample mapping showing the Sequence Generator transformation in shown in the following screenshot:
As you can notice in the mapping, the Sequence Generator transformation does not have any input port. You need to define the Start value, Increment by value, and End value in the properties. Based on properties, the sequence generator generates the value. In the preceding mapping, as soon as the first record enters the target from the Source Qualifier transformation, NEXTVAL generates its first value and so on for other records. The sequence Generator is built for generating numbers.
Ports of Sequence Generator transformation
Sequence Generator transformation has only two ports, namely NEXTVAL and CURRVAL. Both the ports are output ports. You cannot add or delete any port in the Sequence Generator. It is recommended that you always use the NEXTVAL port first. If the NEXTVAL port is utilized, then use the CURRVAL port. You can define the value of CURRVAL in the properties of the Sequence Generator transformation.
Consider a scenario where we are passing two records to transformation; the following events occur inside the Sequence Generator transformation. Also, note that in our case, we have defined the Start value as 0, the Increment by value as 1, and the End value is default in the property. Also, the current value defined in properties is 1. The following is the sequence of events:
- When the first record enters the target from the Filter transformation, the current value, which is set to 1 in the properties of the Sequence Generator, is assigned to the NEXTVAL port. This gets loaded into the target by the connected link. So, for the first record, SEQUENCE_NO in the target gets the value as 1.
- The Sequence Generator increments CURRVAL internally and assigns that value to the current value, which is 2 in this case.
- When the second record enters the target, the current value, which is set as 2, now gets assigned to NEXTVAL. The Sequence Generator increments CURRVAL internally to make it 3.
So at the end of the processing of record 2, the NEXTVAL port will have value 2 and the CURRVAL port will have value set as 3. This is how the cycle keeps on running till you reach the end of the records from the source.
It is a little confusing to understand how the NEXTVAL and CURRVAL ports are behaving, but after reading the preceding example, you will have a proper understanding of the process.
Properties of Sequence Generator transformation
There are multiple values that you need to define inside the Sequence Generator transformation. Double-click on the Sequence Generator, and click on the Properties tab as shown in the following screenshot:
Let’s discuss the properties in detail:
Start Value:This comes into the picture only if you select the Cycle option in the properties. It indicates the integration service to start over from this value as soon as the end value is reached when you have checked the cycle option.
The default value is 0, and the maximum value is 9223372036854775806. Increment By: This is the value by which you wish to increment the consecutive numbers from the NEXTVAL port.
The default value is 1, and the maximum value is 2147483647.
End Value: This is the maximum value Integration service can generate. If the Sequence Generator reaches the end value and if it is not configured for the cycle, the session will fail, giving the error as data overflow. The maximum value is 9223372036854775807.
Current Value: This indicates the value assigned to the CURRVAL port. Specify the current value that you wish to have for the first record. As mentioned in the preceding section, the CURRVAL port gets assigned to NEXTVAL, and the CURRVAL port is incremented.
The CURRVAL port stores the value after the session is over, and when you run the session the next time, it starts incrementing the value from the stored value if you have not checked the reset option. If you check the reset option, Integration service resets the value to 1. Suppose you have not checked the Reset option and you have passed 17 records at the end of the session, the current value will be set to 18, which will be stored internally. When you run the session the next time, it starts generating the value from 18.
The maximum value is 9223372036854775807.
Cycle:If you check this option, Integration services cycles through the sequence defined. If you do not check this option, the process stops at the defined End Value.
If your source records are more than the End value defined, the session will fail with the overflow error.
Number of Cached Values: This option indicates how many sequential values Integration service can cache at a time. This option is useful only when you are using reusable sequence generator transformation.
The default value for non-reusable transformation is 0. The default value for reusable transformation is 1,000. The maximum value is 9223372036854775807.
Reset:If you do not check this option, the Integration services store the value of the previous run and generate the value from the stored value. Else, the Integration will reset to the defined current value and generate values from the initial value defined. This property is disabled for reusable Sequence Generator transformation.
Tracing Level:This indicates the level of detail you wish to write into the session log. We will discuss this option in detail later in the chapter.
With this, we have seen all the properties of the Sequence Generator transformation. Let’s talk about the usage of Sequence Generator transformation:
Generating Primary/Foreign Key:Sequence Generator can be used to generate primary key and foreign key. Primary key and foreign key should be unique and not null; the Sequence Generator transformation can easily do this as seen in the preceding section. Connect the NEXTVAL port to both the targets for which you wish to generate the primary and foreign keys as shown in the following screenshot:
Replace missing values: You can use the Sequence Generator transformation to replace missing values by using the IIF and ISNULL functions. Consider you have some data with JOB_ID of Employee. Some records do not have JOB_ID in the table. Use the following function to replace those missing values:
IIF( ISNULL (JOB_ID), NEXTVAL, JOB_ID)
The preceding function interprets if the JOB_ID is null, then assign NEXTVAL, else keep JOB_ID as it is. The following screenshot indicates the preceding requirement:
With this, you have learned all the options present in the Sequence Generator transformation.
If you liked the above article, checkout our book Learning Informatica PowerCenter 10.x to explore Informatica PowerCenter’s other powerful features such as working on sources, targets, performance optimization, scheduling, deploying for processing, and managing your data at speed.
