Sunday, March 29, 2009

Sequence Generator Transformation

What is NEXTVAL
NEXTVAL - generates sequence numbers when you connect this port to a transformation.

If you want to load 2 targets with the same sequence number
- Connect the NEXTVAL port to a transformation and then connect that transformation to the 2 targets.

If you want to load 2 targets with the unique sequence number
- Connect the NEXTVAL port to two different targets or two different pipelines which has those 2 targets

What is CURRVAL
CURRVAL = NEXTVAL + Increment by Value property

If you connect CURRVAL port without connecting the NEXTVAL, then the CURRVAL will give you constant value.
If you connect CURRVAL, Informatica process one row at a time - can be a performance issue.

Properties :
Start Value : Start Value of the sequence. It will be used as the start value, once it reaches End Value and if the Cycle Option is enabled

Increment By :

End Value : End Value for the Sequence Generator

Current Value : Current Value of the Sequence. It will be updated either at the end of the session(Non-reusable and Number of Cached Values =0 ) or during the session (Number of Cached Value > 0 ) . If Reset Property is enabled, then Informatica resets the value to its original value of the session.

Cycle : Enable if you want to cycle through the values after it reaches the End Value.

Reset : If enabled, Informatica updates current value to the original value every time it runs the session. If disabled Informatica updates the current value to last generated value + 1.

Number of Cached Values : Non-Reusable - If the Value is 0 - It doesnt Cache Values. Informatica reads the start value form the repository and then keeps generating the sequence values. At the ene of the session it updates the current value to Start Value or Lastgeneratedsequence+1 (based on the Reset Option)

If the Value is > 0 then Informatica reads the start value and caches the number of values based on the Number of Cached Values and then updates the Current value in the repository. It will again go to the Repository if all the values are used. At the end of the session it throws away any unremaining Sequence Numbers which were not used.

Interview Questions :-
1) If the End Value is reached and if the Cycle option is not enabled then the session fails
2) Reset Option is disabled for Reusable Sequence Generator
3) Number of Cached Values has to be >= 1 for Reusable Sequence Generator
4) To avoid big gaps between sequence values, set the Number of cached values to 0 if it is non-reusable, set the Number of Cached values to less number if it is Reusable.

Performance Issues :-
1) Do not connect CURRVAL
2) Check Number of Cached Values
Set it to 0 if it is Non-Reusable, Set it to appropriate(default 1000) if it is Reusuable

ENDVALUE & Datatypes in Versions :-
In version 8.6 the datatype of NEXTVAL & CURRVAL is bigint(19) and the ENDVALUE is 9223372036854775807.

In version 7, 8.1. the datatypes are integer(10) and the ENDVALUE is 2147483647

7 comments:

  1. without sequence generator how to create sequence numbers in informatica

    ReplyDelete
  2. Watch a free lesson on "Sequence Generator" on YouTube: www.youtube.com/watch?v=sHDj0GCLh2U

    ReplyDelete
  3. if we are using a sequence generator in a map which is been used by two different non reusable sessions, then will both the sessions retain the latest curr value for the sequence generator ???

    ReplyDelete
  4. Is there a way to get current value of a sequence generator by querying the repository, without having to open the mapping.

    -Sandesh

    ReplyDelete
  5. Hi All,

    We have migrated Production folder to stage and we did the changes in stage. And again migrated stage folder back to production. Here sequence number in sequence genrator transformation will mismatch in prod. so how to overcome this problem?

    ReplyDelete
  6. How can I generate a,b,c,d... in seq generator ratherthan 1,2,3,... ?

    ReplyDelete
  7. hi this is teja i have a doubt regarding sequence generator transformation what is the difference between 8.1 and 8.6 any body knows forward the answer to me. In interview i faced this question

    ReplyDelete