INFORMATICA SEQUENCE GENERATOR is a simple transformation. Informatica sequence generator transformation can’t do much apart from creating sequence which can be used somewhere. Yeah this seems very true until we get into details. I am kind of free now days so thought why not to write up about complex aspect of simple things. 🙂
Estimated time to read this post -10-15 Mins. Apology if this post looks stretched.
We have divided this post in three broader parts for ease (1) Definition, (2) nut and bolts, (3) Industry usage. Again, it is for informatica advanced users who have good hands on the tool.
Basic definition of Informatica sequence generator transformation: Informatica power center object which produces numeric sequences.
Some industry USAGE for INFORMATICA SEQUENCE GENERATOR
Scenario 1: Credit: Informatica help Document
# I have mapping 1, mapping 2, mapping 3. All three mappings feeding data in a table named as target1. How do I need to use informatica sequence generator transformation to create primary keys in this scenario. Moreover these mappings are feeding data in parallel.
Answer: You might need to keep one things in mind while you implement it. Since target is same you need to ensure that keys are not being duplicated. This is one typical scenario which helps us to understand reusable sequence generator transformation need. Reusable will ensure that keys are not being duplicated.
Scenario 2: Credit: Not Informatica help Document (Self-created)
# I have mapping 1 and mapping 2. These two mappings feeding the data in table named as target1 and target2 respectively. What needs to be taken care in case this scenario.
Answer: In above case we need not to ensure about duplicate keys because target tables are different. This is a typical scenario which we use to understand usage of Non reusable transformation. Sort of monotonous but ok if we get good understanding.
# You have a project where you are migrating ETL code from one technology to Informatica. Let say you are migrating ETL code written in SQL and unix to Informatica. In this case how you will configure Informatica sequence generator transformation?
Answer: Little simple. Helpful to understand one of the properties of Informatica sequence generator. It will be one time activity and you will be needing to setup Start Value.
How you can handle if sequence generator reaches it’s limit? Is there any improved method to configure sequence generator so it never reaches it’s upper limit?
Answer: First and most important is that sequence generator has very big limit and it can rarely cross that limit. Moreover in any of the database transactional or analytical we purge the database in 2 years or at most in 5 years.
STILL IF WE WANT TO MAKE IT REALLY FULL PROOF than we need to create CUSTOM SEQUENCES with sequence generator. Yes, one can create CUSTOM SEQUENCES to achieve never ending sequence.
create a sequence which gets reinitialized before every load. Append the sequence key with the current date. This way it will be a never ending sequence. Please see example below.
Hope this might help , Do let us know if it requires some correction or modification. 🙂