surrogate key generator generating improper sequence
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore
surrogate key generator generating improper sequence
I have a job that runs on 2 nodes.I created the statefile through the surrogate key generator stage create option.
I tried to generate the sequence numbers for 107 source records.
The numbers were generated as
1 - 53 on node1
1001-1054 on node2
I was expecting that the sequence numbers would be from 1- 107.
Did I miss someoption somewhere?
I tried to generate the sequence numbers for 107 source records.
The numbers were generated as
1 - 53 on node1
1001-1054 on node2
I was expecting that the sequence numbers would be from 1- 107.
Did I miss someoption somewhere?
Last edited by srinivas.nettalam on Mon May 09, 2011 11:34 pm, edited 2 times in total.
N.Srinivas
India.
India.
Re: surrogate key generator generating improper sequence
If i am right, look into system variables. it will help you.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
But it doesn't matter.
A surrogate key provides uniqueness, just uniqueness, and nothing but uniqueness. There's neither guarantee nor requirement that the numbers will form any kind of sequence.
A surrogate key provides uniqueness, just uniqueness, and nothing but uniqueness. There's neither guarantee nor requirement that the numbers will form any kind of sequence.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
The Surrogate Key stage and the Slowly Changing Dimension stage keep the values of the latest surrogate key stored in a state file - a text file on the server. It's a good idea to set a high block value in order for performance. If you set a block value of 1 it retrieves and writes back a value in this file with every row of data - and this makes the job terribly slow. With a block value of 1,000 each node retrieves the latest value and adds 1,000 to it and writes it back and you only need to update the file every 1,000 rows rather than with every row. You lose surrogate key values but then surrogate keys are not supposed to be intelligent or a count field. If you have a dimension table with a small number of rows you can set a much smaller block size to avoid going through the numbers as quickly.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore