Joined: 27 Aug 2010 Posts: 1 Location: Ontario, Canada
I work on a project that uses COBOL Stored Procedures to access DB2 Tables called within JAVA code. The project is in production; however, we have one canned report that retrieves data from 3 tables (not very large mind you, maybe max 20,000 rows for the one table). Anyhow the problem is that this report is hanging and as it turns out this report is now retrieving about 8000 records and as mentioned it is hanging. Now if we change the SQL to retireve say 1000-2000 records it comes back fairly quickly but anything over 2000 hangs the system. Now to solve the problem we just moved the SQL into the JAVA code for a str8 JDBC SQL call and didn;t use the Stored procedure and this solved the problem, now the question is why is the Stored Proc causing this issue or at least the communication between the java and the sp causing this issue. If anyone has experienced this issue and solved the issue or no why this is causing an issue it would be much appreciated
Joined: 11 Jun 2007 Posts: 80 Location: Columbus, Ohio
Odds are the stored procedure has abended on the mainframe side. Your app appears (to you) to be hanging because it is waiting for a return response of some kind and is not receiving anything back. Call your DB2 DBA support team. They should be able to help you.
Joined: 22 Apr 2006 Posts: 6258 Location: Mumbai, India
If memeory serves well, we solved it by requesting a dedicated-WLM region for our store-proceudres but ours were IMS-DB2 SP and our SPs were used to run under that WLM. I know, I'm not talking very correctly, from the technical per se. But in the schema we had mentioned about that WLM region. I'll check if I can find some documentation on this.