Using sql file to insert data in the table.

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
rumu
Participant
Posts: 286
Joined: Mon Jun 06, 2005 4:07 am

Using sql file to insert data in the table.

Post by rumu »

Hi All,

Have a requirement to load around 10 source data(from sybase) to oracle staging layer as a direct dump.
Want to create one single generic job to load the data and use multiinstance to invoke them in sequence level.
Enabled RCP at job level.
Job Design:
ODBC stage--------Transformer stage------ODBC stage
Providing source extraction query as a file through a parameter at db stage and want to pass the insert query through a file in target stage.
When trying to pass the insert query at target stage through a file as parameter, job failed with an error "column not found", when I changed the target stage as generated query yes and passing the table name as parameter, the job is running fine but as the target stage is ODBC, auto generated option is taking longertime and job took 3 mins to finish. As our source has 100K data on daily basis, can not afford to use autogenerated option.
We are using ODBC to connect to oracle as oracle client is not installed on ETL server due to security issue and tech team could not resolve that.
Sample insert statement is below that I am trying to pass through a file.

INSERT INTO BBCR.TEST_FACILITY(LAST_UPDATED_USERID,FACILITYID,PARTYID,PRIMARYPARTYINDICATOR) VALUES ('SYSTEM',ORCHESTRATE.FACILITYID,ORCHESTRATE.PARTYID,ORCHESTRATE.PRIMARYPARTYINDICATOR)

Could you please help how to pass the insert statement through file?
Rumu
IT Consultant
rumu
Participant
Posts: 286
Joined: Mon Jun 06, 2005 4:07 am

Re: Using sql file to insert data in the table.

Post by rumu »

Hi All,

This issue is resolved. Thanks.
Rumu
IT Consultant
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Re: Using sql file to insert data in the table.

Post by PaulVL »

Perhaps you can post how you resolved it so that future generations digging into the archives can also find the solution. :lol:
Post Reply