SEQUENCE GENERATOR TRANSFORMATION 

1. WHAT IS A SEQUENCE GENERATOR TRANSFORMATION? 
2. DEFINE THE PROPERTIES AVAILABLE IN SEQUENCE GENERATOR TRANSFORMATION IN BRIEF. 
3. SCENARIO IMPLEMENTATION 1 
4. SCENARIO IMPLEMENTATION 2 
5. WHAT ARE THE CHANGES WE OBSERVE WHEN WE PROMOTE A NON-REUSABLE SEQUENCE GENERATOR TO A REUSABLE ONE? AND
WHAT HAPPENS IF WE SET THE NUMBER OF CACHED VALUES TO 0 FOR A REUSABLE TRANSFORMATION? 
6. HOW SEQUENCE GENERATOR IN THE MAPPING IS HANDLED WHEN WE MIGRATE THE MAPPING FROM ONE ENVIRONMENT TO
ANOTHER? 
7. SCENARIO IMPLEMENTATION 3 
8. HOW DO I GET A SEQUENCE GENERATOR TO “PICK UP” WHERE ANOTHER “LEFT OFF”? 

97. What is a Sequence Generator Transformation?

A Sequence Generator is a Passive and Connected transformation that generates numeric values.
It is used to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
This transformation by default contains two OUTPUT ports only, namely CURRVAL and NEXTVAL. We cannot edit or delete these ports neither we cannot add ports to this unique transformation. We can create approximately two billion unique numeric values with the widest range from 1 to 2147483647.

98. Define the Properties available in Sequence Generator transformation in brief.

99. Scenario Implementation 1
Suppose we have a source table populating two target tables. We connect the NEXTVAL port of the Sequence Generator to the surrogate keys of both the target tables.
Will the Surrogate keys in both the target tables be same? If not how can we flow the same sequence values in both of them.
When we connect the NEXTVAL output port of the Sequence Generator directly to the surrogate key columns of the target tables, the Sequence number will not be the same.
A block of sequence numbers is sent to one target tables surrogate key column. The second target receives a block of sequence numbers from the Sequence Generator transformation only after the first target table receives the block of sequence numbers.
Suppose we have 5 rows coming from the source, so the targets will have the sequence values as TGT1 (1,2,3,4,5) and TGT2 (6,7,8,9,10). [Taken into consideration Start Value 0, Current value 1 and Increment by 1]
Now suppose the requirement is like that we need to have the same surrogate keys in both the targets.
Then the easiest way to handle the situation is to put an Expression transformation in between the Sequence
Generator and the Target tables. The Sequence Generator will pass unique values to the expression
transformation, and then the rows are routed from the expression transformation to the targets.

 

100. Scenario Implementation 2
Suppose we have 100 records coming from the source. Now for a target column population we used a Sequence generator. Suppose the Current Value is 0 and End Value of Sequence generator is set to 80. What will happen?

End Value is the maximum value the Sequence Generator will generate. After it reaches the End value the session fails with the following error message:

TT_11009 Sequence Generator Transformation: Overflow error.
Failing of session can be handled if the Sequence Generator is configured to Cycle through the sequence, i.e. whenever 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.

101. What are the changes we observe when we promote a non-reusable Sequence Generator to a reusable one? And what happens if we set the Number of Cached Values to 0 for a reusable transformation?
When we convert a non-reusable sequence generator to reusable one we observe that the Number of Cached Values is set to 1000 by default.

And the Reset property is disabled.When we try to set the Number of Cached Values property of a Reusable Sequence Generator to 0 in the Transformation Developer we encounter the following error message:

The number of cached values must be greater than zero for reusable sequence transformation.

102. How Sequence Generator in the mapping is handled when we migrate the mapping from one environment to another?

While promoting the Informatica Objects using Copy Folder Wizard we have the option to choose to retain existing values or to replace them with values from the source folder.
Generally we Retain the current values for the Sequence Generator transformation in the destination folder, else we may end up having duplicate values for the sequence generated column and may result to session failure.

Find the below Informatica Metadata query which gives the list of the current value of Sequence Generator transform:
SELECT
OPB_SUBJECT.SUBJ_NAME AS “FOLDER NAME”,
OPB_MAPPING.MAPPING_NAME AS “MAPPING NAME”,
REP_WIDGET_INST.INSTANCE_NAME AS “SEQ NAME”,
OPB_WIDGET_ATTR.ATTR_VALUE AS “CURRENT VALUE”
FROM REP_WIDGET_INST
INNER JOIN OPB_MAPPING ON
(REP_WIDGET_INST.MAPPING_ID = OPB_MAPPING.MAPPING_ID)
INNER JOIN OPB_WIDGET_ATTR ON
(REP_WIDGET_INST.WIDGET_TYPE = OPB_WIDGET_ATTR.WIDGET_TYPE AND
REP_WIDGET_INST.WIDGET_ID = OPB_WIDGET_ATTR.WIDGET_ID)
INNER JOIN OPB_SUBJECT ON
(OPB_MAPPING.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID )
WHERE
REP_WIDGET_INST.WIDGET_TYPE_NAME like ‘Sequence%’
AND OPB_WIDGET_ATTR.ATTR_ID = 4 –Current Value
ORDER BY OPB_MAPPING.MAPPING_NAME

103. Scenario Implementation 3
Consider we have two mappings that populate a single target table from two different source systems. Both the mappings have Sequence Generator transform to generate surrogate key in the target table. How can we ensure that the surrogate key generated is consistent and does not generate duplicate values when populating data from two different mappings?

We should use a Reusable Sequence Generator in both the mappings to generate the target surrogate keys.

104. How do I get a Sequence Generator to “pick up” where another “left off”?
Use an unconnected lookup on the Sequence ID of the target table. Set the properties to “LAST VALUE”, input port is an ID. the condition is: SEQ_ID >= input_ID. Then in an expression set up a variable port: connect a NEW self-resetting sequence generator to a new input port in the expression. The variable port’s expression should read: IIF( v_seq = 0 OR ISNULL(v_seq) = true, :LKP.lkp_sequence(1), v_seq). Then, set up an output port. Change the output port’s expression to read: v_seq + input_seq (from the resetting sequence generator). Thus you have just completed an “append” without a break in sequence numbers.