21 Feb 2013
3 MINS READ
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.
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.
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
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.
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:
Sample mapping by using connected Look up Transformation:
Output of the target table to get sequence values using Connected Look up 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
About the Author
BI & Analytics
05 Mar 2021
13 Nov 2020
07 Sep 2020
11 Jun 2020
28 May 2020
08 May 2020
24 Apr 2020
13 Apr 2020
06 Apr 2020
31 Mar 2020
26 Mar 2020
23 Jun 2017
06 Aug 2015
13 Jul 2015
28 Oct 2014
17 Apr 2014
24 Mar 2014
22 Jan 2014
20 Dec 2013
01 Nov 2013
26 Sep 2013
03 Sep 2013
26 Aug 2013
29 Apr 2013
04 Mar 2013
04 Feb 2013
03 Jan 2013
26 Nov 2010
19 Mar 2009
Digital Assurance
02 Jan 2012
17 Feb 2012
Infrastructure Mgmt. Services
02 Mar 2012
06 Feb 2013
Digital Assurance, Enterprise Solutions
14 Feb 2013
18 Feb 2013
27 Feb 2013
Others
01 Mar 2013
Enterprise Solutions
05 Mar 2013
18 Mar 2013
Digital Assurance, Enterprise Solutions, Others
22 Mar 2013
12 Apr 2013
26 Apr 2013
13 May 2013
11 Jun 2013
17 Jun 2013
25 Jun 2013
19 Aug 2013
27 Aug 2013
10 Sep 2013
19 Sep 2013
24 Sep 2013
30 Sep 2013
01 Oct 2013
03 Oct 2013
19 Nov 2013
Enterprise Solutions, Manufacturing and Consumer
28 Nov 2013
03 Dec 2013
03 Jan 2014
27 Jan 2014
31 Jan 2014
12 Feb 2014
13 Feb 2014
20 Mar 2014
11 Jun 2014
Manufacturing and Consumer
26 Jun 2014
30 Jun 2014
10 Jul 2014
15 Jul 2014
16 Jul 2014
18 Jul 2014
26 Aug 2015
28 Sep 2015
07 Oct 2015
26 Oct 2015
07 Mar 2016
22 Mar 2016
13 May 2016
23 May 2016
Application Transformation Mgmt.
11 Jul 2016
25 Aug 2016
03 Sep 2016
14 Sep 2016
15 Nov 2016
22 Nov 2016
25 Nov 2016
Business Process Services
25 Apr 2017
Banking and Financial Services
18 May 2017
30 May 2017
27 Jun 2017
18 Jul 2017
26 Oct 2017
Healthcare, Insurance
28 Nov 2017
11 Dec 2017
25 Jan 2018
21 Feb 2018
14 Mar 2018
( Mandatory field * )
The information you provide will be used in accordance with our terms ofPrivacy Policy
Please Check on "I Agree" to register for the blog.