| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: How to commit when using a mapping node, with db |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Tue May 27, 2014 4:34 am Post subject: IIB9: How to commit when using a mapping node, with db |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
I am using IIB9, the flow has 2 nodes, the first one is FileInput, which reads a file ( 120K records, reads a WholeFile ), and sends the messages to a MappingNode. The Mapping Node inserts into a database table. ( this is using the new graphical version, I've not written any code to perform the insert, just mapped the DFDL of the file to the database table's schema ).
To avoid the FileInput node from failing, I had to increase the maxHeapSize, which I have done to 6GB.
Now, I see that the flow is executing the entire set of 1.2 inserts in a single database transaction, and that is failing ( not sure of the exact reason of the failure, the trace files get huge so I am unable to find the reason ). I suspect it is the database ( Microsoft SQL Server ).
Could anyone let me know how to proceed further with my investigation?
-- in such scenarios how does one generate/view the trace file ? or are there better alternatives to find the error ?
-- Is there a way to set autoCommit at the JDBC provider level to commit? This can eliminate the possibility of a long running transaction filling up the transaction logs.
-- Is there any other way to configure / change the message flow to commit after N record are processed by the mapping node ?
Last edited by akil on Tue May 27, 2014 10:19 am; edited 1 time in total |
|
| Back to top |
|
 |
| Tibor |
| Posted: Tue May 27, 2014 7:38 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Without more information I assume your flow works on a repeating structure. In case of that you can change your flow:
| Code: |
| FileInput ---> Mapping1 to single message ---> Mapping2 to database ---> etc |
- on Mapping1 you can put your loop over output message level
- on Mapping2 you can switch on database transaction |
|
| Back to top |
|
|
| akil |
| Posted: Tue May 27, 2014 10:16 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Thank you @Tibor , I am sorry I should have been clear about the repeating structure in the question.
Yes, there is a repeating structure, and I did as per your suggestion and it works as you've mentioned, it is now committing after every db-insert (message).
Could you now guide me on how do I group messages to say 50 to have a commit batch size of 50? The optimal batch size is somewhere between 1 and 120K , I'll need this to be configurable so that we can try various combinations to have the best response time. |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed May 28, 2014 12:20 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| Because I am not a real master of IIB 9 mapping nodes, so I can only imagine a Mapping to single ---> Collecting construction or simply coding in ESQL ... |
|
| Back to top |
|
|
| akil |
| Posted: Wed May 28, 2014 10:32 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
| Thank you once again, I will try the ESQL coding option.. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
