Using Surrogate key generator with db2 sequence

saxena_richa · Post by **saxena_richa** » Wed May 27, 2009 1:23 am

Hi,
I am using surrogate key generator stage to generate incremental number. For this i tried to use a DB2 database sequence. I have specified the sequence name in the 'Source Name' property. The jobs runs fine but the no. doesnt get incremented.. How can we specify 'next value' type of clause to this sequence?
I tried to search the posts, but couldnt find. Request help.
Thnks

arvind_ds · Post by **arvind_ds** » Wed May 27, 2009 2:47 am

Use a surrogate key stage in your job. Retrieve the max value of the key column from DB2 table and write the same into a file. Fetch that value from the file, increment by desired number and then push the same into surrogate key column.

HTH

nagarjuna · Post by **nagarjuna** » Wed May 27, 2009 5:53 am

You can directly use a statefile instead of going with a database sequence .In this we have setting where we can specify to increment by 1 for every row and store the last value within a file .

saxena_richa · Post by **saxena_richa** » Wed May 27, 2009 6:43 am

Does this mean there is no way to directly use a db2 sequence

ray.wurlod · Post by **ray.wurlod** » Wed May 27, 2009 4:43 pm

No, it doesn't mean that at all.

saxena_richa · Post by **saxena_richa** » Wed May 27, 2009 10:50 pm

I wud like to know how can we use a db2 sequence in surrogate key generator and increment it. As stated earlier, I am facing prob. in incrementing it. Would be grateful if anyone can help on this.
Thanks

naveen19 · Post by **naveen19** » Wed May 27, 2009 11:16 pm

saxena_richa wrote:I wud like to know how can we use a db2 sequence in surrogate key generator and increment it. As stated earlier, I am facing prob. in incrementing it. Would be grateful if anyone can help on this.
Thanks

Make ur table column like this and aviod this column in the mapping area so it populated from 0 and it will incremented by 1

CREATE TABLE DB2_EDW.DIM_CONTRACT (
DIM_CONT_SKEY INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE) NOT NULL,
DIM_CONT_SDATE DATE NOT NULL DEFAULT '1900-01-01',
DIM_CONT_EDATE DATE DEFAULT '1900-01-01',
DIM_CONT_FLAG CHARACTER(1) NOT NULL DEFAULT 'N')

This is work and i am using that in my project

Regards...

Naveen.K

chulett · Post by **chulett** » Thu May 28, 2009 7:11 am

Right, an IDENTITY column would make this whole conversation moot but unfortunately doesn't help answer the question that was asked. And before anyone says anything, no this is not something I know (yet) or I would be more helpful.