Page 1 of 1
Surrogate Key producing one extra key
Posted: Thu Sep 12, 2013 5:35 am
by bkumar103
My job looks as follow:
Job 1
Dataset --> Surrogate Key Generator -> Oracle
Input dataset has 475 Records.
When I observed log there is something i could not understand.
In the log it shows Output 0 produced 476 records although in dataset only 475 Records are there.
can we do anything about this?
Thanks,
Birendra
Posted: Thu Sep 12, 2013 7:12 am
by priyadarshikunal
I do not think its surrogate key issue. Can you check from Dataset Management how many records are there and how many records made it to the database?
Posted: Thu Sep 12, 2013 10:38 pm
by bkumar103
In the dataset it is just 475 records and 475 records got loaded to the database. The message is flagged against the surrogate key stage. Also there is one extra surrogate key is getting generated. i.e for the first load say key is generated from 1 to 475 and in the next run it is generating from 477 instead of 476.
Posted: Mon Sep 16, 2013 6:02 am
by bkumar103
I have changed the Oracle to Sequential file.
SO my job is now
Dataset ->> Surrogate Key Stage -->> Sequential file.
I ran the Job twice. Surrogate key stage runs in the Sequential mode.
I could see that the in the First run the SK is generated from 1 to 475 and in the Next run it generated from 477 to 951. There is a gap between last generated value from Last value from the 1st Run and First value in the Second Run. Did anyone saw such case?
Thanks in Advance,
Birendra
Posted: Mon Sep 16, 2013 7:04 am
by prasson_ibm
What blocksize you have selected?
Posted: Mon Sep 16, 2013 7:09 am
by prasson_ibm
If the key source is a flat file, specify how keys are generated:
Code: Select all
■To generate keys in sequence from the highest value that was last used, set the Generate Key from Last Highest Value property to Yes. Any gaps in the key range are ignored.
■To specify a value to initialize the key source, add the File Initial Value property to the Options group, and specify the start value for key generation.
■To control the block size for key ranges, add the File Block Size property to the Options group, set this property to User specified, and specify a value for the block size.
Posted: Mon Sep 16, 2013 8:03 am
by asorrell
I have to ask - why do you care? Surrogate keys work regardless of "gaps" and according to industry best practices you are never supposed to assign meaning to them (ie: record counts). As long as every record gets a unique key, it shouldn't matter.
Posted: Tue Sep 17, 2013 2:43 am
by bkumar103
The Block size is set to 1.