| Author |
Message
|
| HIMAGAR |
 Posted: Thu Nov 05, 2015 11:23 am Post subject: Message broker - Insert |
 |
|
Novice
Joined: 05 Nov 2015
Posts: 18
|
I have to do bulk insert via message broker to IBM DB2...
Is this possible to call a procedure vi broker...
I have to pass array to my procedure..
Kindly suggest |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Nov 05, 2015 11:32 am Post subject: Re: Message broker - Insert |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| HIMAGAR wrote: |
| I have to do bulk insert via message broker to IBM DB2... |
Why? How did you arrive at message broker as the preferred tools instead of a more traditional ETL tool?
Where is the data coming from? File? MQ message? Other DB2 table?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| HIMAGAR |
| Posted: Thu Nov 05, 2015 11:44 am Post subject: |
|
|
Novice
Joined: 05 Nov 2015
Posts: 18
|
This is an already existing flow available...and a because of a change in business functionality...I have to do this...
Data is coming from a File...
Regards,
HIMAGAR |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Nov 05, 2015 11:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| HIMAGAR wrote: |
| Data is coming from a File... |
If the data's coming from a file, how are you accumulating it to pass it to the procedure as an array?
If the answer is "I've set the FileInput node to 'Whole File' and I'm iterating through it", how are you doing that without running out of memory for a file of more than medium size?
| HIMAGAR wrote: |
| This is an already existing flow available....a change in business functionality |
These statements are at odds; by definition an existing flow doesn't apply to a change in requirements. Is this code for "we already have broker so we're trying to just squeeze this in even though it doesn't fit that well"?
| HIMAGAR wrote: |
| Is this possible to call a procedure via broker |
Yes
| HIMAGAR wrote: |
| I have to pass array to my procedure |
That's going to be problematic. You certainly can't perform a bulk insert ('LOAD') in the way you can with other DB2 tooling (which I suspect is where this 'array' idea is coming from).
| HIMAGAR wrote: |
| Kindly suggest |
Use an ETL tool or other DB2 tooling that can do the kind of bulk-insert-from-a-file that you're looking for.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| HIMAGAR |
| Posted: Thu Nov 05, 2015 12:04 pm Post subject: |
|
|
Novice
Joined: 05 Nov 2015
Posts: 18
|
Hey,
I think we are confusing a bit here...
I recieve a file, which has n rows (say 50)..
each row will have n items (say 200)..
I have to insert this 200 different items each item..
So..if I use insert...it will be 200*50 inout/output calls to my DB..which will lead to certain performance issues...
So...I make array of similar items from my File...for each row...
and then want to call orocedure whih accepts array...
I cant use any other tool.....other than broker as of now...due to limitations..
Kindly suggest.. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Nov 05, 2015 12:10 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
ESQL doesn't support bulk loads.
You might be able to do what you want with JDBC.
Or you could write a DB procedure that would take a string of separated records (comma, dash, etc.) and parse that to do the bulk insert.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Nov 05, 2015 12:14 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| HIMAGAR wrote: |
| I think we are confusing a bit here... |
No, I think I have a fair idea what you're trying to do.
| HIMAGAR wrote: |
| So..if I use insert...it will be 200*50 inout/output calls to my DB..which will lead to certain performance issues... |
Yes it will.
| HIMAGAR wrote: |
| So...I make array of similar items from my File...for each row... |
I repeat my comments above about how you're doing that. For 200*50 elements that's probably not an issue, but what about when the file grows to 500 rows with 2000 items?
| HIMAGAR wrote: |
| want to call orocedure whih accepts array |
I know that's what you want to do. I'm guessing that you're posting on the forum because you've tried it ...
(it's possible you've succeeded with this design and are just posting to boast. I discount that possibility, but if that's the case well done)
| HIMAGAR wrote: |
| I cant use any other tool.....other than broker as of now...due to limitations |
What limitations? You don't have a command prompt?
| HIMAGAR wrote: |
| Kindly suggest.. |
Use a different tool.
You don't want a transformational engine like broker. You want an ETL solution.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
