| Author |
Message
|
| ethirajesh |
 Posted: Wed Feb 27, 2013 7:58 am Post subject: Not able to retrieve more than 4150 records from Database |
 |
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Requirement :
I am invoking a StoredProcedure from Compute node which potentially returns upto 1,00,000, retrieving this result set into Environment.Variables. I have an ESQl routine to convert it to XML which is the final output of my message flow.
Issue
My Flow throws an exception whenever there is more than ~4100 rows returned(SP luckily has limit parameter using which I can control no of records being returned), but when I run the SP from DB client directly it returned all the rows that is 17,163 which means no issues at DB end.
Most of the item I will be forced to invoke the SP without limit parameter, so my code should be capable of holding the whole result set.
Small workaround I did
Created simple message flow without any transformation logic in it where I declared ROW variable and stored result set, but not much difference. I was able to store till 6000 rows only and still getting the exact exception.
Exception details
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1073
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmWSInputNode
Name:CHARACTER:AsIsMessageFlow#FCMComposite_1_1
Label:CHARACTER:AsIsMessageFlow.HTTP Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/S700_P/src/WebServices/WSLibrary/ImbWSReplyNode.cpp
Line:INTEGER:924
Function:CHARACTER:ImbWSReplyNode::evaluate
Type:CHARACTER:ComIbmWSReplyNode
Name:CHARACTER:AsIsMessageFlow#FCMComposite_1_2
Label:CHARACTER:AsIsMessageFlow.HTTP Reply
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
ParserException
File:CHARACTER:/build/S700_P/src/MTI/MTIforBroker/GenXmlParser4/ImbXMLNSCParser.cpp
Line:INTEGER:786
Function:CHARACTER:ImbXMLNSCParser::refreshBitStreamFromElementsCommon
Type:CHARACTER:ComIbmWSInputNode
Name:CHARACTER:AsIsMessageFlow#FCMComposite_1_1
Label:CHARACTER:AsIsMessageFlow.HTTP Input
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:5010
Text:CHARACTER:XML Writing Errors have occurred
ParserException
File:CHARACTER:/build/S700_P/src/MTI/MTIforBroker/GenXmlParser4/ImbXMLNSCWriter.cpp
Line:INTEGER:889
Function:CHARACTER:ImbXMLNSCWriter::writeMisc
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:5016
Text:CHARACTER:Unexpected XML type at this point in document.
Insert
Type:INTEGER:5
Text:CHARACTER:Message
Insert
Type:INTEGER:5
Text:CHARACTER:folderType |
I wanted to know below things :
1) Why I am getting this exception
2) Is there any practical limit in size for Environment.Variables
3) What is the best way to store a result set
NOTE : The no of columns from DB is just 10 and content of each column is not more than 2 to 12 characters, so there is no chance of blaming DB saying your data is huge.
If you dont have answer for this, it would be really great if you share your project scenario on how you handled large result sets in your code.
[/code] |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Feb 27, 2013 8:28 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
1) Why I am getting this exception
Unexpected XML type at this point in document.
2) Is there any practical limit in size for Environment.Variables
No, only the amount of available memory to your processes. You should not be handling the full dataset; you should be paging the dataset, using a VIEW, using solidDb, using MbCache, or some other mechanism to feed the data into the transformation.
3) What is the best way to store a result set
See above: solidDb would be at the top of my list.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Wed Feb 27, 2013 8:41 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
So do you mean to say MB is not capable of retrieving more than 4150 rows with whatever driver(libdb2.so) i am using currently?
I really dont understand why should I go another product(solidDB) at this point.
And the reply you given for my question Why I am getting this exception did not answer it, I am not doing any XML/or any type of transformation as I mentioned earlier to get this exception. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Feb 27, 2013 8:56 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| ethirajesh wrote: |
| So do you mean to say MB is not capable of retrieving more than 4150 rows with whatever driver(libdb2.so) i am using currently? |
No, I did not say that at all. You asked what is the BEST way to handle 1,000,000 rows. If you don't want to use external methods, you can always use a CURSOR to page the data into your transformation.
| ethirajesh wrote: |
| And the reply you given for my question Why I am getting this exception did not answer it, I am not doing any XML/or any type of transformation as I mentioned earlier to get this exception. |
You asked what the reason you got the error. I'm not on your site, so I cannot debug your code. There is a reason, and once you find the true root cause, the reason will make sense. To find the true root cause, you must do detective work.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Wed Feb 27, 2013 9:03 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Ok, thanks. for adding CURSOR I need DB team's involvement. The number I posted 1000,000 is just hypothetical.
lets say it is just 17K rows, still I am not able to hold that in environment variable right. So, What is the best way in MB to hold that result set?
In other words, Normally how would you code your ESQL to hold the result set which is returning some 17K rows.
As far as my understanding 17K is not a BIG data but why MB is failing here  |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Feb 27, 2013 9:08 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| ethirajesh wrote: |
Ok, thanks. for adding CURSOR I need DB team's involvement. The number I posted 1000,000 is just hypothetical.
lets say it is just 17K rows, still I am not able to hold that in environment variable right. So, What is the best way in MB to hold that result set?
In other words, Normally how would you code your ESQL to hold the result set which is returning some 17K rows.
As far as my understanding 17K is not a BIG data but why MB is failing here |
The point is you DO NOT want to hold the full result set in the Environment. You want to parcel out the result set into manageable chunks. You can do this with no external tools using CURSOR. You can do this better using any one of several external tools which will make your life and the performance of the system much better.
MB is not failing. Your code is failing.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Wed Feb 27, 2013 9:29 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
ok, as you said I will have something to hold the result set and not let my code to fail.
For my understanding about Environment variables I need to explore how much it can hold, I am not agreed that there is no limit for it since I am seeing this is failing in all environment(DEV,TST STG and PRD) which has very good infrastructure. Do you an answer for this? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Feb 27, 2013 9:32 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| ethirajesh wrote: |
ok, as you said I will have something to hold the result set and not let my code to fail.
For my understanding about Environment variables I need to explore how much it can hold, I am not agreed that there is no limit for it since I am seeing this is failing in all environment(DEV,TST STG and PRD) which has very good infrastructure. Do you an answer for this? |
If I were writing the code, I would make the size of the page tunable at runtime through a UDP. You'll have to play with this value to see what different settings do to your performance.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Wed Feb 27, 2013 9:41 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Yes we have did all these workaround, we currently have this UDP to forcefully set it to less than 4000.
I played around this UDP with values from 1 to 4150, it failed continuosly when it is above 4000. With this can we conclude environment variable is not a good option to hold result set. (I will rather log a PMR to IBM asking to change their documentation which is misleading developers by giving sample code which uses Environment.Variable) |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Feb 27, 2013 9:44 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You call this a "workaround". Why?
What part of the documentation says you can retrieve more than one row from the database?
The fault of this is not the product. The fault is the developer's code. Fix the code, then the flow will process many more records.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Wed Feb 27, 2013 9:50 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
| What part of the documentation says 'how to hold the result set'? can you give me the link? I will try to fix the code. |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 27, 2013 9:55 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| Where is the exception being thrown, in the Compute node which invokes the stored procedure and populates the Environment tree ? Or in a downstream node ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 27, 2013 9:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The information center does not necessarily cover recommended development practices.
There are a number of factors that could be limiting the number of rows you get back. The first place you should start is with your database administrator to discuss the configuration of the ODBC datasource and the database client configuration behind that ODBC datasource.
I suspect you'll find that your DBA also recommends that you develop your flow to make better use of the power of the database to return the FEWEST rows you actually need, rather than returning ALL rows you *might* need.
Your specific business requirements for your flow may indeed require that you return all 17k rows. You should then discuss this requirement further with the administrator of your Broker so that they can help you tune the test environment and production environments to support this requirement. This could potentially lead to opening a PMR with IBM support to get a better idea of how to tune the necessary memory allocations.
Notice that all of these scenarios involve you working closely with someone local who knows more about the systems you are working with. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Feb 27, 2013 9:58 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ethirajesh wrote: |
| With this can we conclude environment variable is not a good option to hold result set. |
We can conclude that the way you're using the Environment variable is not a good way of holding the result set. You've said there's no transformation logic but you've not said in what format the result set records are returned, or at what point you obtain the exception. At what point does the stream of data comng back from the database turn into something WMB thinks is an XML document with an unexpected type? Given WMB's on demand parsing model, what's triggering the parsing?
| ethirajesh wrote: |
| I will rather log a PMR to IBM asking to change their documentation which is misleading developers by giving sample code which uses Environment.Variable) |
Firstly, if you think there's a problem with the product a PMR is your right as a license payer.
Secondly, if you don't think there's a problem with the product but the documentation is misleading there's a Feedback link on each page.
Thirdly, the Environment is a very good place in general to handle large amounts of data, which is why it's quoted in what you accurately describe as a sample - just a sample, no more, no less and is supposed to be used in the manner described in the documentation where that sample is provided. Maybe there's a problem in your code, maybe there's a problem with the Environment for the specific use case you have but that doesn't make it a bad general sample.
Fourthly, putting 17Mb of XML in Environment and parsing it works fine (I'm looking at code which does it) so clearly something else is at work here. Provide some more details as I outline above.
Better information, better advice.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Mar 01, 2013 2:44 am Post subject: Re: Not able to retrieve more than 4150 records from Databas |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| ethirajesh wrote: |
| I played around this UDP with values from 1 to 4150, it failed continuosly when it is above 4000. With this can we conclude environment variable is not a good option to hold result set. |
Copying elements into and out of the Environment.Variables tree does not involve an XML parser in any way. The error has nothing to do with the size of the Environment tree. Either the stored procedure is corrupting the result set in some way before this point, or the message flow is corrupting the resulting XML message after this point. |
|
| Back to top |
|
|
|
|
