Stage Variable manupilation query

[seell]

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

[kcbland]

When did you ever increment your stage variable?

Code: Select all

Variable VarCitzCnt + 1 

Did you try this on your update derivation:

Code: Select all

Variable VarCitzCnt + @OUTROWNUM

[seell]

hi
I thought that the variable would be incremented each time the process is repeated like @OUTROWNUM ?

If not, then I could not use the @OUTROWNUM as I wanted a serial increment ie the next serial no.

Thank you
Regards

[kduke]

Kenneth

VarCitzCnt = VarCitzCnt + 1

should increment every row. The initial value should be 0 or max of current surrogate key.

It should work but I would not check to see if the surrgate key exists. You do need to find the max ans start from there.

Kim.

[kcbland]

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.

[chulett]

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.

[kcbland]

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

[chulett]

Ok, except then. Horde them well, Grasshopper.

[bang!]

Sorry, thought I saw you move.

[kduke]

Ken

I like the idea only counting each insert. Leaving gaps in the surrogate keys is much faster.

Sleep dude per chance to dream of surrogate keys.

Kim.

[Teej]

You're mad! You're all mad!



-T.J.

[datastage]

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

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.

[Teej]

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.

Oh, witnessing the durability of Cobol, and its constant persistency, requiring Ascential to provide tools to continue to improve on Cobol...

I wouldn't be surprised when our grandkids wonders if we need to increase it to Decimal(20) to be safe.

-T.J.

[ray.wurlod]

You'll hit the Y10K problem in ISO 8601 dates before you'll need Decimal(20)!

[Teej]

You'll hit the Y10K problem in ISO 8601 dates before you'll need Decimal(20)!

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?"

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.

-T.J.

[mhester]

You'll hit the Y10K problem in ISO 8601 dates before you'll need Decimal(20)!

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.

Regards,

Michael Hester