Surrogate Key producing one extra key

bkumar103 · Post by **bkumar103** » Thu Sep 12, 2013 5:35 am

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

priyadarshikunal · Post by **priyadarshikunal** » Thu Sep 12, 2013 7:12 am

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?

bkumar103 · Post by **bkumar103** » Thu Sep 12, 2013 10:38 pm

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.

bkumar103 · Post by **bkumar103** » Mon Sep 16, 2013 6:02 am

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

prasson_ibm · Post by **prasson_ibm** » Mon Sep 16, 2013 7:04 am

What blocksize you have selected?

prasson_ibm · Post by **prasson_ibm** » Mon Sep 16, 2013 7:09 am

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.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Mon Sep 16, 2013 8:03 am

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.

bkumar103 · Post by **bkumar103** » Tue Sep 17, 2013 2:43 am

The Block size is set to 1.