| Author |
Message
|
| Shiva Jayaram |
 Posted: Tue Apr 03, 2012 6:48 am Post subject: DataBase Insert node Issue |
 |
|
Newbie
Joined: 14 Oct 2008
Posts: 6
|
Hi,
I have a scenario like below.
I need to insert the bulk of data(Eg. 50k records) at a time in DB (Oracle).
I have a flow like
Input ->Compute(SAP Request)->SAPRequest (Response will be 50k and above)->DatabaseInsert Node.
I have used compute node instead of DatabaseInsert node to write the record one by one but it takes lot of time when i am getting bulk record. So I am planning to use the Database insert node.
I have created the Database Definition (.dbm) file and source messageset to create the messageMap file for databaseInset node. Source messageset is able to parse the SAPResponse and I am mapping with Target database definition file.
When I am debugging the flow, I am getting the exception message in Database insert node like [DataDirect][ODBC lib] Data source name not found and no default driver specified. even I set the datasource name in Database insert node.
Could you please anyone help me to resolve the issue or please propose any otherway to complete this task.
Plese let me know if you need nay further information.
Thanks in advance. |
|
| Back to top |
|
 |
| Esa |
| Posted: Tue Apr 03, 2012 7:02 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
Writing a datasource name in the node configuration does not create a datasource. It will have to be configured by the adminis-trators.
Search the InfoCentre with keyword odbc.ini, for example. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Apr 03, 2012 7:07 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You are discovering a practical limitation of the Broker runtime. Broker database interactions work well for small data, small quanities. If you need high through put, you need to augment Broker database functionality.
There are several ways to do this. solidDb is my favorite way. Second, is to use SOAPRequest node to a Web Service that actually does the DB interfacing. A third way is multi-threading through the use of a Singleton.
WMB Database nodes are not mature enough yet to support high-throughput requirements. In my opinion. Thanks Vitor.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Apr 03, 2012 7:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| You are discovering a practical limitation of the Broker runtime. Broker database interactions work well for small data, small quanities. |
No, this is extremely unlikely. Shiva Jayaram has already demonstrated that he/she does not understand enough about Broker to know how to properly create an ODBC datasource.
It's therefore extremely likely that all of the performance issues that Shiva Jayaram has encountered are a result of how the flow is coded, rather than any fundamental limitation in the product.
Will you bet me 20% of your yearly commission on the sales of solidDB that Shiva Jayaram is *not* using [index] to walk through the 50,000 records?
| lancelotlinc wrote: |
| There are several ways to do this. solidDb is my favorite way. Second, is to use SOAPRequest node to a Web Service that actually does the DB interfacing. A third way is multi-threading through the use of a Singleton. |
Yes, clearly using three layers of webservice interfaces between every actual business function will solve ALL performance problems.
Because Web Services are MAGICAL. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Apr 03, 2012 7:46 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
I truly do enjoy all of our discussions. I often chuckle at our interactions. I'm glad I'm in the kitchen. Some other people may not stand the heat here.
I have so much fun implementing technology. I have often thought about switching over to marketing, I still have all my teeth and use a fancy deodorant.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 03, 2012 7:49 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| lancelotlinc wrote: |
| You are discovering a practical limitation of the Broker runtime. Broker database interactions work well for small data, small quanities. If you need high through put, you need to augment Broker database functionality. |
What the OP is discovering is that even if you want to use WMB for small amounts of data you need to correctly configure the product. Or you get the error the OP has got. The OP is also discovering that the development toolkit does not automagically configure the runtime so you can just not bother with the configuration steps for an ODBC.
| lancelotlinc wrote: |
| There are several ways to do this. solidDb is my favorite way. |
How does inserting into solidDB (I accept very quickly) allow you to update an Oracle database (the requirement here)? Are you proposing some trigger mechanism.
| lancelotlinc wrote: |
| Second, is to use SOAPRequest node to a Web Service that actually does the DB interfacing. |
Which would still need a DB connection (JDBC / ODBC) so the OP would be no further forward.
| lancelotlinc wrote: |
| A third way is multi-threading through the use of a Singleton. |
Again how does this get the data into the Oracle database the OP needs it to be in?
| lancelotlinc wrote: |
| WMB Database nodes are not mature enough yet to support high-throughput requirements. In my opinion. Thanks Vitor. |
You're welcome. How does this statement (the truth of which I'm determined not to debate) relate to the OP's problem?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Apr 03, 2012 7:53 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| lancelotlinc wrote: |
| I'm glad I'm in the kitchen. Some other people may not stand the heat here. |
I'd like your kitchen as previously discussed. The ability to swap out hardware platforms (e.g. migrate from z/OS to POWER7) with almost a wave of the hand, install and enforce automatic builds, use CI for the entire development process, rock solid source control & (apparently) even subsitute Oracle for solidDB without a murmur sounds more like Utopia than a kitchen.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Apr 03, 2012 10:45 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Vitor wrote: |
I'd like your kitchen as previously discussed. The ability to swap out hardware platforms (e.g. migrate from z/OS to POWER7) with almost a wave of the hand, install and enforce automatic builds, use CI for the entire development process, rock solid source control & (apparently) even subsitute Oracle for solidDB without a murmur sounds more like Utopia than a kitchen. |
Don't forget to add
Singleton's (with everything)
Take the training (if you have a problem)
Java and not Clapton is God {for those of us old enough to have seen Hendrix}
I am joking honestly, my kitchen is noce and hot at the moment. I'm baking some bread.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Tue Apr 03, 2012 11:27 am Post subject: Re: DataBase Insert node Issue |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Don't use the DB-Insert-Node.
Use an ESQL-compute node (there are 3 rules you should remember with ESQL !).
Use PASSTHRU... there are also 3 rules  :
1. Use a DB-Tool to create the select/update/delete-statement(s)
2. Do not use '?' in the passthru- string and
3. Create all (partial) strings / arguments with CAST ( ... AS CHAR ) probably with a FORMAT PATTERN... Then finally concatenate them to one string
Works with (a lot of) small msgs ...
... as well as with (some) large msgs.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Apr 03, 2012 11:45 am Post subject: Re: DataBase Insert node Issue |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqsiuser wrote: |
2. Do not use '?' in the passthru- string and
. |
Would you like to clarify the reasoning behind this statement. I am sure there are some readers who would like to know why you made this statement.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Apr 03, 2012 11:47 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
This advice is just plain wrong:
| Quote: |
| 2. Do not use '?' in the passthru- string |
There is nothing wrong with PASSTHRU, but it is no better or worse than using ESQL SELECT, INSERT etc for the typical case. Also, when you do use PASSTHRU you should ALWAYS try to use parameter markers '?' where possible. Otherwise you will make the cache of prepared statements much bigger than it needs to be.
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Tue Apr 03, 2012 11:51 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Sorry 
Thanks for clarifying.
I have rechecked, and actually I am really using (Oracle) "VALUES" and '(?),(?),(?),...' and the/a parameters-list (with the PASSTHRU-statement)!
I am not (for Oracle) using the ESQL native SELECT (and the '?' there).
@OP: Make sure that your datasource works
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
|
|
