DSXchange

Hi All

I got asked a question about PX and DS transaction control. If a job run and the connection is lost or the machine go down how do I recover my load and from what point do you start loading the data again. My commit is set to 1000 records.

If I reset the job will a transaction rollback be initiated on the database that I'm loading and to what point will the rollback go. Will it be the laast commit point or will it rollback all the records.

Another point on this if you use PX and you get a node going down that is busy with some processing how does PX handle the situation. Do you need to build the functionality in to keep track of the records loaded.

Something like DS best practices course is doing?

Hkotze,

The enviroment variable APT_RDBMS_COMMIT_ROWS can be set to a number equal to your desired commit rate. If you set it to 0, then PX will issue a single commit for all of the records being processed. I am fairly certain that this env var works with DB2, but I am not sure if it works with other databases.

If a processing node goes 'down' while PX is using it, the PX job will fail. You do need to keep track of the records loaded/written/received to reconcile inbound and outbound record counts.

Having said that, Orchestrate (the original PX) did have checkpoint restarting capabilities at the 'step' or job level. I am not sure if PX has retained or even exposes these capabilities.

Your best bet is to use the DS Job Sequencer to sequence and control the running of your PX jobs - similar to the way you would sequence and control Server jobs.

If this does not answer your question, please ask again.

Thanks,
BP

bigpoppa wrote:The enviroment variable APT_RDBMS_COMMIT_ROWS can be set to a number equal to your desired commit rate. If you set it to 0, then PX will issue a single commit for all of the records being processed. I am fairly certain that this env var works with DB2, but I am not sure if it works with other databases.

It will not work with Oracle.

It is a typical practice for our company (and other companies as noted by Support) to utilize a "pre-" job, where critical data are archived in a different location, and a reject job restoring those archived data.

Painful with large datasets, but the best option we can determine for this.

-T.J.

T.J.,

Thanks for the clarification regarding Oracle and the APT_RDBMS_COMMIT_ROWS option.

Perhaps Ascential should rename the evvironment variable to APT_DB2_COMMIT_ROWS to clear up any confusion.

- B.P.