| Author |
Message
|
| Partha.Baidya |
 Posted: Sun Feb 12, 2017 11:54 am Post subject: Selecting row by row from a Table and use the row in ESQL |
 |
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
Is there any way to select row by row from Table and process each row in ESQL?
It should work the same way File Input node's record by record processing works.
In DB2 i found the below query can be used to read a Table giving row num.
| Quote: |
| SELECT T1.* FROM ( SELECT row_number() over(order by IncomeTable) as rn, L.* FROM INTSTG.CHARTSINCOME L) AS T1 WHERE T1.rn =10; |
|
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sun Feb 12, 2017 12:40 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Is the query valide ESQL?
if not then you have to use the PASSTHRU function.
_________________
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 |
|
|
| Partha.Baidya |
| Posted: Sun Feb 12, 2017 1:16 pm Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
This is a DB2 valid query, not in ESQL. PSSTHRU should be used in ESQL.
Is there any other option anybody had used already to retrieve records row by row from data base table? |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Feb 13, 2017 5:41 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| This is a DB2 valid query, not in ESQL. PSSTHRU should be used in ESQL. |
It may be syntactically valid, but I've met DB2 admins who'd give you a hard time if you tried to run it. It's going to burn resource on the DB2 server.
| Partha.Baidya wrote: |
| Is there any other option anybody had used already to retrieve records row by row from data base table? |
So first you need to explain why you can't perform the traditional retrieval pattern, which is to return the result set and process that row by row (like a FileInput node processes a file). Now admittedly the FileInput node does that out of the box and you'd need to write half a dozen lines of ESQL (I think 4, but I've not tried it) which doesn't sound too onerous to me.
If you actually want to pull one row at a time from a database, the most resource effective way it to use a server side stored procedure and a cursor. You should also be aware that if the DB2 admins don't come for you waving pitchforks because of resource usage, the rest of the database users might because what you're describing sounds like a good way to get a table lock in place.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Mon Feb 13, 2017 8:35 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
| Quote: |
| So first you need to explain why you can't perform the traditional retrieval pattern, which is to return the result set and process that row by row (like a FileInput node processes a file). Now admittedly the FileInput node does that out of the box and you'd need to write half a dozen lines of ESQL (I think 4, but I've not tried it) which doesn't sound too onerous to me. |
If I use traditional retrieval pattern and write ESQL to process row by row then the entire result set is hold in memory. For large result set it may be problem.
That's why I was checking if there is some SQL query can be used to read data row by row and Propagate each record to be processed by subsequent IIB nodes. In this way IIB memory usage can be reduced. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Feb 13, 2017 8:48 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Partha.Baidya wrote: |
| That's why I was checking if there is some SQL query can be used to read data row by row and Propagate each record to be processed by subsequent IIB nodes. In this way IIB memory usage can be reduced. |
Yes. At least two.
Use a DB store procedure that lets you page through results.
Use ESQL to read a page of rows. Propgate that set. When you get to the end of the propagation, use DELETE to clear out the necessary trees being held in memory.
You'll have to figure out how to do pagination in ESQL, though. this would require passing an additional field to the database.
Passing an additional field to the database would require a stored procedure, likely. And you'd still want to DELETE message trees to free up pages of memory.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Feb 13, 2017 8:51 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| If I use traditional retrieval pattern and write ESQL to process row by row then the entire result set is hold in memory. For large result set it may be problem. |
Define "large". Also explain why the "handling large message" methodology doesn't fit your needs. Especially as...
| Partha.Baidya wrote: |
| That's why I was checking if there is some SQL query can be used to read data row by row and Propagate each record to be processed by subsequent IIB nodes. In this way IIB memory usage can be reduced. |
....that's mostly how the large message methodology reduces memory usage.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Mon Feb 13, 2017 9:12 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
1. I need to create a file. Each record had 3600 characters. The table max will contain 35000 row in the table. The entire result set will be 126 MB of data assuming 3 KB for each record.
2. In another case the each record had 520 chars and the Table will have 2,50,000 rows. The entire result set will be 250 MB of data assuming 1 KB for each record.
I am assuming that 250 MB will not be a huge data for IIB but processing 2,50,000 records in a flow could be a performance issue.
Let me know if my thinking is wrong. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Feb 13, 2017 10:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Partha.Baidya wrote: |
| I am assuming that 250 MB will not be a huge data for IIB but processing 2,50,000 records in a flow could be a performance issue. |
How will anything you're talking about affect processing speed? If you're worried about that, you should be actively trying to get the entire result set in memory in one block.
And no, 250Mb isn't a lot of data.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
