generate surrogate keys with SQL Server 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
emma
Premium Member
Premium Member
Posts: 95
Joined: Fri Aug 08, 2003 10:30 am
Location: Montreal

generate surrogate keys with SQL Server sequence

Post by emma »

How do I generate surrogate keys using a SQL server sequence ; and use it with the slow changing dimension stage ?
Thanks,
Emma
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Set up your Surrogate Key Generator stage to get the next value from the SQL Server 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.
emma
Premium Member
Premium Member
Posts: 95
Joined: Fri Aug 08, 2003 10:30 am
Location: Montreal

Post by emma »

Hi Ray ...thank you , but I don't have this option.
I have only DB2 and Oracle in the list. I use DS 11.5
Thanks,
Emma
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Unfortunately the answer to your question isn't a simple straightforward one. How you go about solving it depends greatly on the number of records you need to process or, in other terms, what your performance expectations are. Overall I would prefer to use the DataStage sequences unless the surrogate keys are also being generated by some other application and you need to synchronize those values with the ones from DataStage.
emma
Premium Member
Premium Member
Posts: 95
Joined: Fri Aug 08, 2003 10:30 am
Location: Montreal

Post by emma »

I'm sorry I wasn't clear about the issue I have ....
I'm looking here to have the confirmation if our installation has an issue or is simply like that, because in the <Database Type> drop down list, I have options only for DB2 and Oracle ?

I build the classic , standard job to create a sequence (the sequence is a db object that the stage should connect too)

ODBC ----- > Surrogate Key Generator -----> stat file

In the drop down list of Surrogate Key Generator Stage
|---- > Key Source
| ------> Database Type =( here I have only 2 options : DB2 and Oracle )
| ----- > Source Type = Database Sequence

How can we get the MS SQL Server option to be displayed in the Surrogate Key Stage??
Thanks,
Emma
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

There was no reason to ask this twice so I removed the stand-alone post on the subject.

All the documentation I see only lists two possibilities for the source as you note: DB2 and Oracle. AFAIK, you'll have to take another tack for SQL Server, either just let DataStage generate them (if that is a viable option for you) or use another mechanism to access the surrogate. From what I recall, a sparse lookup could be done to 'get' the next value... but I'm sure people will chime in with other thoughts.
-craig

"You can never have too many knives" -- Logan Nine Fingers
emma
Premium Member
Premium Member
Posts: 95
Joined: Fri Aug 08, 2003 10:30 am
Location: Montreal

Post by emma »

The right answer is that with SQL Server we don't really need a Sequence Generator or State File or a database sequence , we just need to add to the table the IDENTITY column which will auto increment for every record inserted.
And to use that with the SCD stage just let the Surrogate Key option blank .
Of course in the case you don't want to load the Fact table at the same time.

The same is used with TERADATA.
Thanks,
Emma
arsh
Participant
Posts: 9
Joined: Thu Jan 18, 2007 10:44 pm
Location: Kolkata, India

Post by arsh »

We can use a table with IDENTITY column only if the Surrogate keys are unique across a table. What if the requirement is to have a unique value across the schema? I am using version 2008 and my dilemma is exactly this. I cannot use a state file and want the best possible alternative to use a squence generated in the database
-AA
Post Reply