| Author |
Message
|
| coolparsi21 |
 Posted: Thu Jun 09, 2016 10:12 am Post subject: Fetching millions of records |
 |
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
I have to work on a flow where we have to fetch records in Database thorugh a select statment (can be upto 2 million records) and write those to Output files.
How can i do that . Right now i am just firing a select statment in compute node and directly building the tree .
will this work ? Is there any better way to handle large data from DB |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Jun 09, 2016 10:40 am Post subject: Re: Fetching millions of records |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| coolparsi21 wrote: |
| ? Is there any better way to handle large data from DB |
Use a dedicated ETL tool?
How much transformation is needed to get the result set row into a file record format? If it's not much, that strengthens the case for ETL.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| coolparsi21 |
| Posted: Thu Jun 09, 2016 10:43 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
I have just message broker , cannot use any other tool , have to do through message broker ver 7.0.
The select query i am triggering is giving me 2 Million records . how many records can i fetch from database in compute node at a time ? can it handle 2 million records at once. Transformation is like I just have to manipulate two column values for each record .
So i have to iterate over 1 million records , can WMB handle that ?
Appreciate any help |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 09, 2016 11:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need to use a stored procedure to give you a page of records.
If you try to read 2 million records, you will crash your EG because it will run out of memory.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| coolparsi21 |
| Posted: Thu Jun 09, 2016 11:13 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
| mqjeff wrote: |
You need to use a stored procedure to give you a page of records.
If you try to read 2 million records, you will crash your EG because it will run out of memory. |
Let me try to do some Research on Stored Procedure to give me page of records . This looks a better idea .
Man 2 million records i can't build a tree . |
|
| Back to top |
|
|
| coolparsi21 |
| Posted: Thu Jun 09, 2016 12:19 pm Post subject: |
|
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
Can i do something using message broker . The database lies remotely and we cant ask them to write a stored procedure.
can somebody give me some examples how to fetch a million record using esql or java (Not preferable ) and process them to output file |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jun 10, 2016 4:37 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| coolparsi21 wrote: |
| Can i do something using message broker . |
Yes - watch it crash when it runs out of memory.
| coolparsi21 wrote: |
| can somebody give me some examples how to fetch a million record using esql or java (Not preferable ) and process them to output file |
Any number of possible examples, most of which will run the EG out of memory of most of the possible platforms you could be running on.
You might also mention to your management that in addition to not having an ETL tool capable of meeting their requirements, they don't have a supported integration platform either. WMBv7 is out of support and has been for a while now.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| coolparsi21 |
| Posted: Fri Jun 10, 2016 5:48 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
yeah man , it ran out of memory , EG Crashed  ( can i try increasing JAVA HEAP SIZE ? will it work .
Can i use some free ETL tool in conjuction with message broker ? can you sugges the name of ETL tool ? |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jun 10, 2016 6:25 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| coolparsi21 wrote: |
| yeah man , it ran out of memory , EG Crashed ( can i try increasing JAVA HEAP SIZE ? will it work . |
It will make it run longer before it falls over. I doubt (as I said) you're going to fit a result set of that size in any plausible platform.
| coolparsi21 wrote: |
| Can i use some free ETL tool in conjuction with message broker ? |
No, and certainly not with an out of support broker.
| coolparsi21 wrote: |
| can you sugges the name of ETL tool ? |
No. They tend to be licensable.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jun 10, 2016 7:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
You should be able to do this in a Java Compute node using cursors to grab 'x' rows at once to propagate as a message then looping grabbing another block of rows and propagating a new message until you are done... Or use a different tool as was suggested above...
_________________
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 |
|
|
| Vitor |
| Posted: Fri Jun 10, 2016 7:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mgk wrote: |
| You should be able to do this in a Java Compute node using cursors to grab 'x' rows at once to propagate as a message then looping grabbing another block of rows and propagating a new message until you are done... |
....or use the same principle to write a stand alone program or a WAS hosted piece of Java which would be easier to maintain and removes the WMB overhead.
What you're describing isn't using WMB to grab millions of records, it's running user code to grab millions of records with WMB as an application platform.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| coolparsi21 |
| Posted: Fri Jun 10, 2016 9:51 am Post subject: |
|
|
Newbie
Joined: 07 Mar 2014
Posts: 9
|
Thanks guys Java compute node is a gud idea , i will see what i can do on that ,
Good news is that db admin has agreed to include a stored procedure.
now can it be done . if he can give me records through pagination , i can scroll it. i am assuming i have to use java compute node to do that ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Jun 13, 2016 4:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
If he can give you a stored procedure that paginates, you can use any of the transformation nodes that let you loop over statements, use database connectivity, and provide different arguments to the stored procedure each time through the loop.
If you want to use a JCN and a cursor rather than the stored procedure (which will also isolate you from changes to the underlying database), this can also be done.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| inMo |
| Posted: Mon Jun 13, 2016 8:18 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
Given the constraints, it might be worth your time to explore ordering and limiting the result set. Order on a field that somehow increments. Lets say you find a field called "counter". On the initial query, order lowest to highest where counter is >= 0. Limit the result set to some number that protects the EG. Work that result set, go back and query again where counter > last value of counter in previous result set.
Where is "IBM Global Blockchain Enablement" HQ'd? Hursley? |
|
| Back to top |
|
|
|
|
