Surrogate keys and DB2 sequence

buzzylee · Post by **buzzylee** » Tue Jul 20, 2010 4:48 am

Dear Experts,

For last couple of hours I'm facing strange issue related to surrogate keys of DB2 type.

No matter which access method I use (SCD/Transformer/Surrogate Key Generator stages) generated SK is always equal 0, jobs finish with no error.

What's strange DB2 sequence that I use gets updated when I run processing - I see it on database side (sequence counter is increased by the number of records I process via SCD/Transformer/Surrogate Key Generator stages).

I've IIS 8.1 FP1 installed with all latest fixes from Fix Central.

Moreover - one of my colleagues have the same problem with Windows installation (I use AIX).

Have any of you ever occurred this type of problem?

Regards
Buzz

ArndW · Post by **ArndW** » Tue Jul 20, 2010 4:53 am

No, I haven't seen this type of problem. What is the DB2 datatype of the key and how is it declared in the job? I assume you have no errors or warnings of any type at runtime, is that correct?
Have you used the surrogate keys successfully before? Can you try using a flat file as the source in the transform stage to see if that works for you (this is just a test)?

buzzylee · Post by **buzzylee** » Tue Jul 20, 2010 5:06 am

ArndW,

DB2 sequence is of INTEGER data type, in job I declare it by passing full sequence name prefixes with schema (schema.seq_name).

No errors and warnings are observed in job log - no matter which access method I use (Transformer, SCD, SK Generator).

Please keep in mind that underlying sequence is accessed by DataStage - the sequence itself get updated but no return values come back to DS...

I have used SKs before but only of File type. Even now if I replace DB sequence with file one it works perfectly...

Regards
Buzz

ArndW · Post by **ArndW** » Tue Jul 20, 2010 6:27 am

Since the sequence is being incremented, your sequence name is being passed correctly as are the connectivity values. For some reason the issue is on the return side, hence my question to you regarding having used sequences before. I can't check right now, but could it be that the surrogate might need to be a BIGINT?

buzzylee · Post by **buzzylee** » Tue Jul 20, 2010 12:25 pm

No, I haven't played with DB2 sequences before, only with Oracle ones and I'm pretty sure it worked.

Regarding BIGINT - I have tested it as well, with no effect.

buzzylee · Post by **buzzylee** » Tue Jul 20, 2010 12:40 pm

Issue solved!

It turned out that database sequence on DB2 side must of BIGINT type - thanks for suggestion ArndW

There is a section in IIS documentation that covers it:

"Generated keys are unsigned 64-bit integers. The key source can be a state file or a database sequence. If
you are using a database sequence, the sequence must be created by the Surrogate Key stage. You cannot
use a sequence previously created outside of DataStage."

What's misleading above paragraph is only present in Surrogate Key Generator stage section, no hint about it by Transformer or SCD documentation...

Also - you may use sequence created outside of DataStage but it must be explicitly pointing to BIGINT type.

Cheers
Buzz