Solved

My procedure calling another procedure gives out error code SQL0954C after 10 executions.

Posted on 2008-06-12
5
502 Views
Last Modified: 2012-06-27
I have a Stored procedure with 2 IN parameters (dbname,schemaname) and 3 OUT parameters one for the count of calls to the 2nd Stored procedure and the other 2 to capture the error messages upon error handled. The procedure gets all the tables in the schema through a cursor and calls another Stored procedure by passing in the dbname,schemaname and table name as 3 parameters, which will insert one row per table into a metadata table.
I can see 10 rows getting inserted into the table and nothing happens after that. It is always 10 rows but the process should process around 150 rows.
I am either getting an error - SQL0954C Not enough storage is available in the application heap to process the statement.
Or at times it is just hanging up and no response. I had to ask the DBA to unlock the tables when ever it is getting struck.
0
Comment
Question by:boppanak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 21769894
Hi boppanak,

Both symptoms are potential results of the same cause.  Let's see if we can to where you're always getting the same error and deal with that.

The SQL0954C error can be resolved by increasing the size of the application heap.  The default value is often too low for complex or large queries.

If DB2 is on a unix/linux server, have the DBA try this:

  db2 connect to {my_database}
  db2 get db cfg|grep APPLHEAPSZ

DB2 will report something like:

 Default application heap (4KB)             (APPLHEAPSZ) = 230

Increase the value (perhaps 300) by having the DBA enter the following:

  db2 update db cfg using APPLHEAPSZ 300

I don't recall if that's a dynamic change or if DB2 must be restarted, but it should get you past the failure at 10 calls that you're seeing.


If that doesn't actually solve the problem, you'll need to take a detailed look at the cause.  Perhaps you can post the Stored Procedures.  It sounds like you're nesting Procedure Calls, in an almost recursive fashion, but I'd like to see the code.


Good Luck,
Kent




0
 

Author Comment

by:boppanak
ID: 21770061
The code is as follows ...
The execution procedure is edw_insert_all_tabs.db2 which will keep calling edw_insert_new.db2 once for each row retrieved through the cursor.
edw-insert-new.txt
edw-insert-all-tabs.txt
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 21770520

Try putting a COMMIT in edw-insert-new, right after the EXECUTE IMMEDIATE.

Kent
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question