ORACLE Enterprise Stage

[rajeev_prabhuat]

Hi,

I am using Datastage 7.x and i am using Oracle Enterprise stage to insert the data into table, it populates millions of records daily, i want to do commit on certain intervals or on completion of 100 or 1000 rows how can it be done or what is the option to do the same.

Regards,
Rajeev Prabhu

[chulett]

Check for a Transaction Size option in the stage, that sets the number of rows to commit at any one time. At least on the server side.

You may also want to look for some $APT configuration parameters to set, I seem to remember reading about them here. Search the forum for 'commit' and see what turns up.

Edit: for example, a post like this.

[rajeev_prabhuat]

Hi Chulett,

Thank you. That was really great.

Regards,
Rajeev Prabhu

Check for a Transaction Size option in the stage, that sets the number of rows to commit at any one time. At least on the server side.

You may also want to look for some $APT configuration parameters to set, I seem to remember reading about them here. Search the forum for 'commit' and see what turns up.

Edit: for example, a post like this.

[rajeev_prabhuat]

Hi Crieg,

That was great, but i have a doubt. See i am having 42 jobs and all are independent jobs, and all are one to one direct load. But of these tables that i am loading one is huge table we will have 1 million rows populated per day, in case the jobs get aborted in between, i want to know till what it has commited and continue only from that point next time, is this possible by setting the parameters APT_ORAUPSERT_COMMIT_ROW_INTERVAL and APT_ORAUPSERT_COMMIT_TIME_INTERVAL.

Regards,
Rajeev Prabhu

Check for a Transaction Size option in the stage, that sets the number of rows to commit at any one time. At least on the server side.

You may also want to look for some $APT configuration parameters to set, I seem to remember reading about them here. Search the forum for 'commit' and see what turns up.

Edit: for example, a post like this.

[chulett]

Sorry, not a PX guru... just remember stuff I see posted. Someone else will have to help with the gory details. Or you could search through the forum again for those keywords, I doubt it's the first time the question has been asked.

[rajeev_prabhuat]

Hi Craig,

Thanks you , no prob's. What about in server jobs can this be done.

Regards,
Rajeev Prabhu

Sorry, not a PX guru... just remember stuff I see posted. Someone else will have to help with the gory details. Or you could search through the forum again for those keywords, I doubt it's the first time the question has been asked.

[mandyli]

Hi Rajeev,


Good question. You need to setup transaction type in each and every job. Any way before setting ORA APT parameters please discuss with ur ORA DBA or Ascential DBA.

Thanks
Man