Not enough memory

Posted on 2003-03-17
Medium Priority
Last Modified: 2007-12-19
1) I am having db2 EE 7.2 server on Win NT4/2000.

2) When I was doing: insert into local_db_table select * from nickname (to remote server), I get the error:
SQL1022C There is not enough memory available to process the command.

3) I failed to do anything from what was suggested as user response:

a) Change the MEMMAN NO SWAP, NO MOVE option in the CONFIG.SYS file to SWAP, MOVE. I could not find CONFIG.SYS anywhere. Can you tell me where it is located or if on windows it has a different name.

b) Remove background processes - How to do this?. First of all what all are the db2 background processes and what all can we remove.

c) Decrease the values of the configuration parameters that define allocation of memory, including udf_mem_sz if UDFs are involved in the failing statement.
This is a parameter of type db manager configuration. UDFs were not involved in the failing statement.
What all parameters define allocation of memory.

d) Install more random access memory (RAM). We are on the way.

e) If a remote procedure was invoked, ensure that the remote procedure uses a local variable space that is less than or equal to 4K. Even though remote procedure was not involved, I would like to know how to accomplish this.

f) If you are using Remote Data Services, increase the Remote Data Services heap size (rsheapsz) in the server and client configuration because at least one block is used per application. This is the one that fits I think. I could not find rsheapsz in list of db and dbm cfg parameters. When can I find and set this both in client and in server.

g) On OS/2 systems, increase the value of the min_priv_mem database manager configuration parameter. This will cause the database manager to reserve more private memory space at db2start time. I found this parameter in dbm cfg parameters. Can I set this and will it take effect for my windows system.

Question by:k_murli_krishna
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
  • 2
  • 2
LVL 17

Author Comment

ID: 8150352
4) This was there after all user response points:
NOTE: This is applicable only for releases of DB2 prior to Version 2. Does this point to all curatives or just for the last one i.e. 3) g).

LVL 18

Accepted Solution

BigSchmuh earned 500 total points
ID: 8150758
I am almost certain that you can NOT apply those old "curratives" to your 7.2 release.

Memory shortages may come from many points:
-Client configuration: rare case if you use the default installation value
-DB and DBM on your local or remote DB2: please get a close look at the DB_HEAP, APP_CTL_HEAP_SZ and QUERY_HEAP_SZ as they are often underestimated

Cf. "Calculating Database Shared Memory" Ref# 1053589 on the ibm support site

Good luck
LVL 13

Expert Comment

ID: 8154633
One thing you can try while you are waiting to install more memory is to shut down all un necessary programs that are running in the background. This means do not keep Micorsoft Office open for example, or any other program other than DB2.
Then retry your script. If you still get this message, you need more physical memory in the machine, or increase the swap file space under Windows, but it will slow down dB2 if you do. If your script runs, you can start opening those previously closed programs one by one and retrying your script until you egt the memory error. Then you will know how many programs (background processes) you can keep open while running DB2.
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

LVL 17

Author Comment

ID: 8203766
Experts: You have replied nicely but what remains is:
3) a) CONFIG.SYS e) and g)
LVL 18

Expert Comment

ID: 8204937
According to you, the suggested user response was only for DB2 prior to release 2.
a) CONFIG.SYS has NOTHING to do with DB2 on WINNT or Win2000

e) You are not using remote procedures and this was although related to your CONFIG.SYS as it claims for more space on environment variable (An old DOS related problem !!!)

g) You are not using OS2 at all

==> I agree that, sometimes, editor's posts related to old release may help to answer current release troubles but not that old (Those was DOS or OS2 related...)

By the way, did you check your DB_HEAP, APP_CTL_HEAP_SZ and QUERY_HEAP_SZ parameters according to the "Calculating Database Shared Memory" Ref# 1053589 IBM post ?
LVL 13

Expert Comment

ID: 8205266
Actually, that is true, you can recalculate your memory requirements and therefore REDUCE the values that DB_HEAP, APP_CTL_HEAP_SZ and all the other memory related parameters hold.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 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