DB2 NEXTVAL

[JPalatianos]

Hi,
I am currently loading to many DB2 Tables who use a Sequence to extract the Key field.
I am using user-defined SQL for all these inserts ==>
INSERT INTO PRFWSTG.JPTEST(JP_KEY, JP_NOTE) VALUES (NEXTVAL FOR PRFWSTG.SQC_JPTEST,?);

Is there anyway to use the NEXTVAL function in the TRANSFORMER derivation area? It's no big deal to use the User-defined SQL but I figured if it could be done......

Thanks - - John

[NBALA]

Hi John,

You can not use the NEXTVAL function directly to access the DB2 Sequence, but you can use the transform KeyMgt.

You can refer this post for more details
[url]viewtopic.php?t=84670&highlight=DB2+SEQUENCE[/url]

-NB

[chulett]

You could if you used a Reference Lookup to retrieve it first. Not saying that's a good way, but it is a way.

[vmcburney]

If you were confident you running the only job updating the sequence for the duration of the job you could call it prior to the start of the job (from a sql script run by a sequence job) and pass it in as a job parameter, increment it in a transformer, update it at the end of the job (via the after-sql tab) to the counter value.

This is one of the reasons why I recommended ETL surrogates in Why database generated surrogate keys drive me nuts!!!