Stage Variable manupilation query
Moderators: chulett, rschirm, roy
Stage Variable manupilation query
hi
I am trying to use ETL to insert and update an existing data warehouse dimension table with surrogate keys.
Data would be extracted from the existing table to a csv file where amendments would be made and this would be the input to the job.
What I have done is retrieve the maximum surrogate key into a stage variable say VarCitzCnt. During the transformation stage, I would check using the code, if code found use the same surrogate key, update the record else use the Variable VarCitzCnt + 1 as the surrogate key and insert into the table
The update is not a problem but I found that during multiple inserts, only the last insert was successful. I do not know why but have also tried to change the 'TRANSACTION HANDLING' for that table to be 1 but that also did not work;
If instead I chose to use @OUTROWNUM, I got the correct number of records inserted; The reason for that using this that we want to be sure that the surrogate key generated is correct as there might be deleted rows in the output file.
Any advise ??
Thank you
Regards
SLL
I am trying to use ETL to insert and update an existing data warehouse dimension table with surrogate keys.
Data would be extracted from the existing table to a csv file where amendments would be made and this would be the input to the job.
What I have done is retrieve the maximum surrogate key into a stage variable say VarCitzCnt. During the transformation stage, I would check using the code, if code found use the same surrogate key, update the record else use the Variable VarCitzCnt + 1 as the surrogate key and insert into the table
The update is not a problem but I found that during multiple inserts, only the last insert was successful. I do not know why but have also tried to change the 'TRANSACTION HANDLING' for that table to be 1 but that also did not work;
If instead I chose to use @OUTROWNUM, I got the correct number of records inserted; The reason for that using this that we want to be sure that the surrogate key generated is correct as there might be deleted rows in the output file.
Any advise ??
Thank you
Regards
SLL
When did you ever increment your stage variable?
Did you try this on your update derivation:
Code: Select all
Variable VarCitzCnt + 1
Code: Select all
Variable VarCitzCnt + @OUTROWNUM
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Alright Kim, I'm supposed to be the sleepy one.
I simply quoted the original text verbatim. I figured the stage variable was being misused, and somehow the poster thought it auto-incremented, as the followup then stated.
Since @OUTROWNUM will introduce gaps (ohh, the horror) in assignment, because the row is either an insert or and update, @OUTROWNUM gaps one the update because the value is not used.
My recommendation would be to fork the stream and send updates up one path and inserts down another and handle it that way. Nobody listens to me so the other solution is to involve another stage variable that increments only when an update is seen that that variable is added to the seed variable.
![Wink :wink:](./images/smilies/icon_wink.gif)
I simply quoted the original text verbatim. I figured the stage variable was being misused, and somehow the poster thought it auto-incremented, as the followup then stated.
Since @OUTROWNUM will introduce gaps (ohh, the horror) in assignment, because the row is either an insert or and update, @OUTROWNUM gaps one the update because the value is not used.
My recommendation would be to fork the stream and send updates up one path and inserts down another and handle it that way. Nobody listens to me so the other solution is to involve another stage variable that increments only when an update is seen that that variable is added to the seed variable.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
And to follow up on Ken and his Shakespeare "Oh Horrible, Horrible, Most Horrible" comment...
If all this is to ensure that there are no gaps in the surrogate key sequence, don't worry about. They are by definition, after all, meaningless and you shouldn't burn too many brain cells worrying about "wasting" any. Typically. IMHO.![Smile :)](./images/smilies/icon_smile.gif)
If all this is to ensure that there are no gaps in the surrogate key sequence, don't worry about. They are by definition, after all, meaningless and you shouldn't burn too many brain cells worrying about "wasting" any. Typically. IMHO.
![Smile :)](./images/smilies/icon_smile.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Yeah, until you get a client with an Oracle table with a primary surrogate key of decimal(3).
AHHHHHHHHH. Kill me, kill me now.
bang
AHHHHHHHHH. Kill me, kill me now.
bang
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Ok, except then.
Horde them well, Grasshopper.
[bang!]
Sorry, thought I saw you move.
![Laughing :lol:](./images/smilies/icon_lol.gif)
[bang!]
Sorry, thought I saw you move.
Last edited by chulett on Mon Nov 17, 2003 4:10 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
someone please put Ken out of his misery
that's pretty bad. I just got through with a slowly changing dimension in which it was closing in on time to increase the key from decimal(6) to decimal(7). They kept insisting they wanted it to be decimal(10) to be safe. I projected, even with accelerated growth, that it would be 70 years before it would need to even be decimal(8). At least erring on the large size doesn't create the pain of erring in the low side.kcbland wrote:Yeah, until you get a client with an Oracle table with a primary surrogate key of decimal(3).
AHHHHHHHHH. Kill me, kill me now.
bang
Re: someone please put Ken out of his misery
Oh, witnessing the durability of Cobol, and its constant persistency, requiring Ascential to provide tools to continue to improve on Cobol...datastage wrote:that's pretty bad. I just got through with a slowly changing dimension in which it was closing in on time to increase the key from decimal(6) to decimal(7). They kept insisting they wanted it to be decimal(10) to be safe. I projected, even with accelerated growth, that it would be 70 years before it would need to even be decimal(8). At least erring on the large size doesn't create the pain of erring in the low side.
I wouldn't be surprised when our grandkids wonders if we need to increase it to Decimal(20) to be safe.
![Wink ;-)](./images/smilies/icon_wink.gif)
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ohhhh yeah! That would be a HUGE problem due to the amount of C++ programs out there and the fact that they're all compiled. "Hey, where's the source code for this?"ray.wurlod wrote:You'll hit the Y10K problem in ISO 8601 dates before you'll need Decimal(20)!
At least with the Y2k problem, you had the Cobol source code there.
We'll be so filthy rich, and so filthy bald at that time.
![Wink ;-)](./images/smilies/icon_wink.gif)
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
I think the upcoming date problems that might happen have more to do with storage than format. If the system can store dates as 64 bit signed then you are correct. If the storage is 32 bit then we have a problem that will happen around 2037 and it really won't matter how the date is formatted.You'll hit the Y10K problem in ISO 8601 dates before you'll need Decimal(20)!
Regards,
Michael Hester
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com