Generating Sequence number for primary key at Taget oracle T

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
srinivas Guduru
Premium Member
Premium Member
Posts: 38
Joined: Sat Dec 29, 2007 9:58 am

Generating Sequence number for primary key at Taget oracle T

Post by srinivas Guduru »

Hi

OracleEntstage---->Transformerstage----->OracleEntstag

I am using select query in OracleEnt stage.Selecting six columns.

I am using Transformer stage. In Tx stage, adding a new column(int (10)) Run_ID on output link of Transformer.

New column (in TX stage) is a primary key in the Target table.

I am using Oracle Ent stage to insert all seven columns into a target table.with options below

updsert mode = user defined update and insert

upsert order = insert then update

write method = upsert

For Run_ID column value is { VALUES (schema.SEQ_AUDIT_RUN.nextval,ORCHESTRATE.column2,ORCHESTRATE.column3......

Job is aborting and error is below

main_program: Field Run_ID is not in the input data set.

main_program: Creation of a step finished with status = FAILED.

Can any one advise me in this issue.

Thanks

Srini
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Don't include the RUN_ID column in your design. Mention it only in the user-defined SQL.

I presume you are using an Oracle sequence because there are likely to be other processes inserting rows into this table at the same time that you are. Otherwise consider using a surrogate key stage, which you can initialize from the target table itself.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply