Using Surrogate key generator with db2 sequence

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Using Surrogate key generator with db2 sequence

Post by saxena_richa »

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
Participant
Posts: 428
Joined: Thu Aug 16, 2007 11:38 pm
Location: Manali

Post by arvind_ds »

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
Arvind
nagarjuna
Premium Member
Premium Member
Posts: 533
Joined: Fri Jun 27, 2008 9:11 pm
Location: Chicago

Post by nagarjuna »

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 .
Nag
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

Does this mean there is no way to directly use a db2 sequence :roll:
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

No, it doesn't mean that at all.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

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
Participant
Posts: 52
Joined: Tue Mar 06, 2007 9:08 am
Location: India
Contact:

Post by naveen19 »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply