DSXchange

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

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

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

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!!!