generate surrogate keys with SQL Server sequence
Moderators: chulett, rschirm, roy
generate surrogate keys with SQL Server sequence
How do I generate surrogate keys using a SQL server sequence ; and use it with the slow changing dimension stage ?
Thanks,
Emma
Emma
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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??
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
Emma
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.
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
"You can never have too many knives" -- Logan Nine Fingers
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.
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
Emma
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