DSXchange

Is it possible to excute database commits based on a captured key change? For example if my key is State I'd like to commit records for the current State and trigger an update to an availability table setting that State to "processed". Is this possible?

kld05 wrote:Is it possible to excute database commits based on a captured key change? For example if my key is State I'd like to commit records for the current State and trigger an update to an availability table setting that State to "processed". Is this possible?

No. you can not.

If I understood correctly, you would need few extra columns for setting up some flags in those columns so that you can always retrieve data based on those flags and reprocess the data.

But the nature of Database commit is not similar to the other operations like insert or update. Commit closes an entire logical unit or unit of work. It saves any changes made by your session to the database in the current transaction.

Best regards,

kris wrote:

kld05 wrote:Is it possible to excute database commits based on a captured key change? For example if my key is State I'd like to commit records for the current State and trigger an update to an availability table setting that State to "processed". Is this possible?

No. you can not.

If I understood correctly, you would need few extra columns for setting up some flags in those columns so that you can always retrieve data based on those flags and reprocess the data.

But the nature of Database commit is not similar to the other operations like insert or update. Commit closes an entire logical unit or unit of work. It saves any changes made by your session to the database in the current transaction.

Best regards,

The avail switches will reside in another table. Let my try and clarify.

Table A has columns State, Social, Tax
Table B has State, Avail Indicator.

The records being passed to the databse Table A contain all 50 states. I'd like to breakup the transactions by state, issue the commit and update the availability in Table B vs. committing all 50 states in one transaction and updating all 50 avail flags at once. There isn't a way to implement looping to accomplish this? This is currently being done in a cursor.

Table A has columns State, Social, Tax
Table B has State, Avail Indicator.

The records being passed to the databse Table A contain all 50 states. I'd like to breakup the transactions by state, issue the commit and update the availability in Table B vs. committing all 50 states in one transaction and updating all 50 avail flags at once. There isn't a way to implement looping to accomplish this? This is currently being done in a cursor.

Thanks for clarifying the situation here. This is something where procedural method is more suitable. But if you have to have something in datastage do this kind of task, sequencer job will do.

develop the job to do the actual task (I'd like to breakup the transactions by state, issue the commit and update the availability in Table B) which accepts few paramets and then call this job in a loop using sequencer job where you will have control over your job. The individual job does the task and commits only for one state and this job is being run multiple times in the loop with the next state and so on.

Basically you are building the same thing which is similar to SQL procedure.

Let us see if somebody else has a better idea.

Best regards,