Generating UNIQUE sequence Number without using Sequence Generator

We generate Surrogate Key in DW environment to maintain history and we need a key other than Primary Key to uniquely identify the record. Example: If EMPNO is the key, we can keep only one record in target for the same Employee number and can’t maintain history, so we use Surrogate key as Primary key and not EMPNO.

Informatica Sequence Generator:

• Passive and Connected Transformation
• The Sequence Generator transformation generates numeric values
• Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers

Sequence Generator Ports:

The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL. We cannot edit or delete these ports. Likewise, we cannot add ports to the transformation.

Advantages of Sequence Generator:

We can make a Sequence Generator reusable, and use it in multiple mappings. We might reuse a Sequence Generator when we perform multiple loads to a single target.

For example, if we have a large input file that we separate into three sessions running in parallel, we can use a Sequence Generator to generate primary key values. If we use different Sequence Generators, the Informatica Server might accidentally generate duplicate key values. Instead, we can use the same reusable Sequence Generator for all three sessions to provide a unique value for each target row.

Maximum value of the Sequence Generator is 9,223,372,036,854,775,807. If the Integration Service reaches this value during the session and the sequence is not configured to cycle, the session fails.

There are other alternate methods used in place of a Sequence Generator Transformation, especially when we want to build some pattern for the unique id generation. Be aware that most of the time these alternate methods work when the target is updated by one session at a time. If multiple sessions were adding records and running concurrently, this option doesn’t work.

Following are some of the alternate ways

• By using expression transformation
• By using pre sql in source qualifier
• By using Lookup (Connected and Unconnected) Transformation

BY USING EXPRESSION TRANSFORMATION:

Create one variable port and one output port as follows.

v_temp: v_temp+1

o_seq : v_temp

Sample mapping by using Expression Transformation:

Output of the target table to get sequence values using Expression Transformation:

For the incremental load the sequence number need to be generated based on existing max sequence number in target, at that time use lookup to pick max from target and use that in exp as per requirement.

BY USING SOURCE_QUALIFER TRANSFORMATION:

Add rownum port to source qualifier and connected to empno again.  Then generate own query like “select rownum, all columns from table”.

Sample mapping by using Source Qualifier Transformation:

Output of the target table to get sequence values using Source Qualifier Transformation:

BY USING LOOKUP TRANSFORMATION:

a)Connected Look-Up

• We can achieve this with the help of Dynamic Look Up and expression.
• Have a lookup on target table and get the max(id), keep incrementing max(id) 1

Sample mapping by using connected Look up Transformation:

Output of the target table to get sequence values using Connected Look up Transformation:

b)Un-Connected Look-Up

• Create an unconnected lookup transformation and create only one return port in the lookup.
• Now overwrite the lookup query to get the maximum value of primary key from the target. The query looks as

• Now create an expression transformation and connect the required ports to it.
• Now we will call the unconnected lookup transformation from this expression transformation.
• Create the below additional port in the expression transformation:

Sample mapping by using Unconnected Look up Transformation:

Output of the target table to get sequence values using Unconnected Look up Transformation:

Thanks for reading; let us know have you followed other methods for unique sequence id generation in Informatica.
– By Muralidhar Reddy P & Niveditha N V