| Author |
Message
|
| Deepali |
 Posted: Wed May 29, 2013 1:14 am Post subject: Problem with "Commit" in esql |
 |
|
Novice
Joined: 05 Mar 2013
Posts: 19
|
Hi All,
We are using WMB V8 and DB2. In our message flow we are inserting ~1000 rows in compute node. We want to commit after every 50 inserts. We tried with transaction property set to"commit". It's not working. Also we tried PASSTHRU 'commit'. But no use! 
Can anybody help us? |
|
| Back to top |
|
 |
| Tech1621 |
| Posted: Wed May 29, 2013 2:00 am Post subject: Re: Problem with "Commit" in esql |
|
|
Novice
Joined: 29 May 2013
Posts: 23
|
|
| Back to top |
|
|
| Deepali |
| Posted: Wed May 29, 2013 2:07 am Post subject: |
|
|
Novice
Joined: 05 Mar 2013
Posts: 19
|
Thanks for reply..
We are not using PROPAGATE. And we are inserting in one table only. But not a single row is getting inserted. (We have tested it without commit. Rows are getting inserted.) Now we want to try with 'commit'. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed May 29, 2013 2:09 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
Make a transactional preprocessor flow split the message into 50 record parts and send the messages to the database flow via an intermediate queue. Ande let the database flow commit transcationally. Don't try to commit programmatically.
Because you don't want to have the 1000 record message backed out and you not knowing which records were committed and which not. |
|
| Back to top |
|
|
| Tech1621 |
| Posted: Wed May 29, 2013 2:18 am Post subject: Re |
|
|
Novice
Joined: 29 May 2013
Posts: 23
|
| Deepali wrote: |
Thanks for reply..
We are not using PROPAGATE. And we are inserting in one table only. But not a single row is getting inserted. (We have tested it without commit. Rows are getting inserted.) Now we want to try with 'commit'. |
I can not understand the use of commit as even if the transition mode is Automatic and you are able to insert the table to the DB , if the message flow completes successfully, the updates are committed.
Is it only by changing the transition mode from Automatic to commit you are not able to get the tables inserted?
_________________
Anything is easy if you believe in yourself.. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed May 29, 2013 2:36 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
One approach is to use Stored Procedures to do this for you. You can have the logic of "Commit" in your Stored Procedure.
But the design suggested by Esa would be the best one I guess. Because its always better to "Commit" transactionally than through your code (as pointed out).
But why do you want to commit for every 50 records? |
|
| Back to top |
|
|
| Esa |
| Posted: Wed May 29, 2013 3:28 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| Deepali wrote: |
| But not a single row is getting inserted. (We have tested it without commit. Rows are getting inserted.) Now we want to try with 'commit'. |
I think trying to commit programmatically (with PASSTHRU) causes an exception and that is the reason why now rows get inserted.
Do you have exception handling in your flow? Can you tell if you get an exception? |
|
| Back to top |
|
|
| Deepali |
| Posted: Wed May 29, 2013 4:52 am Post subject: |
|
|
Novice
Joined: 05 Mar 2013
Posts: 19
|
Thanks All for the reply.
We had given wrong Data Source Name in property of compute node. The flow is working fine. 
We have tested all the scenarios of committing database inserts.
@Esa : Trying to commit programmatically (with PASSTHRU) does not cause any exception. We tried it!!
Also if you set Transaction property of compute node to commit, the inserts are not getting rolled back if any exception occurs in the flow later. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed May 29, 2013 5:02 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| Deepali wrote: |
@Esa : Trying to commit programmatically (with PASSTHRU) does not cause any exception. We tried it!!
|
Thanks for the information.
| Deepali wrote: |
Also if you set Transaction property of compute node to commit, the inserts are not getting rolled back if any exception occurs in the flow later. |
And you are sure this is worth a ?
What do you do with the records that were not committed? |
|
| Back to top |
|
|
| shrutiagarwal228 |
| Posted: Mon Jan 20, 2014 12:26 am Post subject: |
|
|
Newbie
Joined: 20 Jan 2014
Posts: 3
|
| Deepali can you share the code you used for explicit commit. |
|
| Back to top |
|
|
|
|
