| Author |
Message
|
| fjb_saper |
 Posted: Fri May 20, 2016 10:25 am Post subject: Performance and Bulk inserts into the DB from IIB |
 |
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Hi,
We have a number of messages that carry x rows and to speed up the process we are required to do a bulk insert into the DB instead of an insert per row.
I have searched the net (MR. Google) for bulk insert and could not find an example in ESQL, or in SQL that did not involve a file. Java allows for me to use a jdbc connection, a prepared statement with ps.addBatch and ps.executeBatch... so I could implement this in a JCN.
Does anybody know if the Mapping node in its DB insert capability does a bulk insert in the forEach DB transform?
The DB is quite a ways from the broker and my DB team is telling me that the amount of traffic I generate on SQL Net is killing me...
Does anybody have a different view on this? Any other ideas are welcome.
Thanks 
_________________
MQ & Broker admin |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Fri May 20, 2016 10:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
No, there really isn't a bulk insert in Broker - afai remember. Certainly not in ESQL. Mapping node *might* do it in v10, but I doubt it.
An ODBC trace would show you what a mapping node was doing...
Presumably you could do something with a PASSTHROUGH statement and a big long string of concatenated data...
... otherwise you could write a jMS or MQ procedure on the database end... 
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| maurito |
| Posted: Mon May 23, 2016 2:48 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| I have done something like that in the past by calling a stored procedure that takes as IN param a REFERENCE. Then you do the mass insert in the SP |
|
| Back to top |
|
|
| inMo |
| Posted: Mon May 23, 2016 5:27 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| Is it possible to prep the data in ESQL with the intent of a handoff to a stored procedure where the bulk insert is performed? If DBAs are complaining/citing the root cause, perhaps they'd be willing to write the stored procedure? |
|
| Back to top |
|
|
| nmaddisetti |
| Posted: Mon May 23, 2016 10:20 am Post subject: |
|
|
Centurion
Joined: 06 Oct 2004
Posts: 145
|
Hi Maurito,
Have you done this using ESQL ?
I have done something like that in the past by calling a stored procedure that takes as IN param a REFERENCE. Then you do the mass insert in the SP
I just posted my question and saw ur reply in this post.
Thanks,
Venkat. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon May 23, 2016 1:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
There are 17 fields that are extracted out of each row. There are about 24 fields on each row. The (E)SQL is an INSERT statement passing the specific fields of the row into the VALUES clause. I know nothing about the size of the fields on the DB, or for that matter on the message.
I believe I will have to go the JCN way, using the prepared statement's batch capabilities.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| maurito |
| Posted: Mon May 23, 2016 6:51 pm Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
You don't necessarily need to use the JCN. I've done is in ESQL,built the message tree on the Environment. Then used a REFERENCE to the message tree as IN parameter to the stored procedure. The SP was in Oracle, but I guess it could be any supported DBMS.
I think the ESQL call to the SP was a PASSTHRU. |
|
| Back to top |
|
|
|
|
