Suroggate key generator error

[bdejumne]

Hello,

My plateform:
- DB2 Entreprise Edition V 9.1
- Information Server V 8.0.1 on UNIX


I use a surrogate key generator in a job...
I use it with a DB2 sequence.

The flow seems correct by datatstage put 0 for all rows in the output file.
When i retrieve the next val of the sequence in the DB directly with a query tool i see that the value has changed following number of rows managed by the job.

The data type surrogate key is BigInt, unsigned

Thanks a lot for you help...

Ben.

[ray.wurlod]

Did you initialize the state file?

[bdejumne]

Did you initialize the state file? ...

Hi ray,

With state file, the job

1.doesn't take into account the option :
'Generate Key from Last Highest Value =True' : After the compilation, the option is set to No.

2. Value generated as surrogate key start with 2463803454672472610.
The last value define in the state file = "1004"

Strange....

Ben.

[michaeld]

The issue is that your sequence is not defined with a BIG INT data type (in DB2). By default DB2 will assign a sequent to INT. You need to set it to BIGINT in order for it to work with DataStage.

CREATE SEQUENCE ACTNO_SEQ AS BIGINT START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10;

no charge:-)

[Nicole]

I found this post and it was the solution - thank you!

Question - other applications use the same sequence (ie. java applications) - would they have to modify their applications or does anyone know if java could handle this datatype as an Integer without having to modify their code.

Thank you.

Nicole

The issue is that your sequence is not defined with a BIG INT data type (in DB2). By default DB2 will assign a sequent to INT. You need to set it to BIGINT in order for it to work with DataStage.

CREATE SEQUENCE ACTNO_SEQ AS BIGINT START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 10;

no charge:-)

[ray.wurlod]

Don't know. Try it. There is, of course, a risk once the sequence values become too large to be represented as Integer. On that basis I would suggest that the other applications will need to be changed. That said, since you are creating this sequence, they presumably don't work already.