Hi All,
Netezza as Target Database
Trying to design a job where I want to insert records to a target table from a staging table using the Netezza Connector Stage.
As part of this I need to get the Next Value for a Sequence for every record and then load to the table.
I found this article but how can we write the user defined statement as we do not know the name of the TWT (Temporary Work Table)
"insert into [[table]] SELECT next value for sequence_name, COL1, COL2, COL3 from [[twt]]"
Any suggestions on how to do this in Datastage?
Thanks,
NV
Get Netezza Sequence Number in Job
Moderators: chulett, rschirm, roy
It is a Datastage question as this needs to happen from the Netezza Connector stage
Is using the Surrogate Key Generator a recommended approach to avoid this scenario of using a Netezza Sequence?
My concern is since it File based (State File) and we could run the risk of file corruption and am also not sure of the performance impact?
Is using the Surrogate Key Generator a recommended approach to avoid this scenario of using a Netezza Sequence?
My concern is since it File based (State File) and we could run the risk of file corruption and am also not sure of the performance impact?
Just as an FYI, that part isn't really relevant. Your question to start with is how do you do it regardless of the tool, not how do you do it in DataStage. That aspect comes later and is usually not the tricky part. Do you not have a DBA to ask / willing to help?nvalia wrote:It is a Datastage question as this needs to happen from the Netezza Connector stage
Sure, the Sequence Generator is an option, that's what it is there for. Biggest question in my mind is concurrency - would you have multiple processes running simulataneously that would need access to that sequence?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Concurrency, No - I can create a separate State File for each dimension so only the associated job would access the corresponding Surrogate key and no other process would access that sequence at the same time
But again is this a good practice from Datastage perspective, to have say 10 State Files, one per Dimension?
But again is this a good practice from Datastage perspective, to have say 10 State Files, one per Dimension?
You do want separate state files for each target, yes, just like each would typically have its own unique / dedicated sequence generator in the database. Also, see if this post helps with your TWT question, the last reply specifically.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers