| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: Bulk/Batch Insert, Select in ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Sat Nov 14, 2015 3:32 am Post subject: IIB9: Bulk/Batch Insert, Select in ESQL |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Native ODBC has parameters such as SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_ROW_ARRAY_SIZE, which can be used to perform efficient bulk insert & select.
Is it possible to do this via ESQL?
Or do we have to fall back on executeBatch in a JCN?
Regards
Akhilesh
_________________
Regards |
|
| Back to top |
|
 |
| maurito |
| Posted: Sat Nov 14, 2015 3:48 am Post subject: Re: IIB9: Bulk/Batch Insert, Select in ESQL |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
Hi
Native ODBC has parameters such as SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_ROW_ARRAY_SIZE, which can be used to perform efficient bulk insert & select.
Is it possible to do this via ESQL?
Or do we have to fall back on executeBatch in a JCN?
Regards
Akhilesh |
no. |
|
| Back to top |
|
|
| akil |
| Posted: Sat Nov 14, 2015 4:40 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
So JCN is it then..
_________________
Regards |
|
| Back to top |
|
|
| akil |
| Posted: Sat Nov 14, 2015 7:38 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
What about the Mapping Node ? If we have a for-each in the mapping node, does it result in a bulk-insert or does it fire individual statements?
_________________
Regards |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Nov 14, 2015 8:02 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Depending on the DB, you may have an ESQL option if you want it as some DB's such as SQLServer do support a multi-row insert directly.
As an example, with a simple table defined with two columns like this:
| Code: |
| CREATE TABLE table1(i1 int, i2 int); |
Then this kind of PASSTHRU statement does work with SQLServer for a multi-row INSERT:
| Code: |
DECLARE dbStatement CHAR 'INSERT into dbo.table1 VALUES (?, ?), (?, ?), (?, ?)';
PASSTHRU dbStatement VALUES(1,2,3,4,5,6); |
In the above example, three rows are inserted, (each pair of (?,?) maps to one row). In this example, I've used literal values to insert (1 through 6), but you could use ESQL variables or tree references just as easily to make this much more dynamic. Note, that this syntax will not be supported by all DB's and I've only tested the above with SQLServer...
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
