sql2310n when performing runstats on a large table

db2aix · #1 (**permalink**) 07-24-09, 16:36

Hello,

We have DB2 9.1 on AIX 5L. We're getting sql2310n with error -930 when performing runstats on a large table. The ulimit settings:

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

The interesting thing is that the same runstats command worked fine prior to reboot. Looking for some assistance. Thx

ARWinner · #2 (**permalink**) 07-24-09, 17:09

The runstats is getting this error:

Code:

SQL0930N  There is not enough storage available to process the
      statement.

Explanation:

A request was made to the database that required another memory page but
no more pages are available to the database manager.

The statement cannot be processed.

User response:

Possible solutions include:
*  Verify that your system has sufficient real and virtual memory.
*  Remove background processes.
*  If the error occurs during DUOW resynchronization, reduce the
   resync_interval database manager configuration parameter value.
*  If the error occurs on a statement which referenced a routine (UDF,
   stored procedure or method), it may be that the storage required to
   contain the arguments and return values was more than the memory
   available in the system. This might occur if the routine is defined
   with BLOB, CLOB, DBCLOB parameters or return values, having a large
   size (2GB for example).

If the possible solutions listed above do not solve the problem, it may
be necessary to consider changing the definition of the routine.

 sqlcode: -930

 sqlstate: 57011

You are out of memory...

Andy

db2aix · #3 (**permalink**) 07-24-09, 17:26

But vmstat shows I have plenty of memory.

n_i · #4 (**permalink**) 07-24-09, 18:20

Quote:

Originally Posted by db2aix

But vmstat shows I have plenty of memory.

You may have plenty, but it's not necessarily available for DB2. You know, for example, the limits to the shared memory segments on AIX.

db2aix · #5 (**permalink**) 07-24-09, 19:31

This is a 64 bit instance, 32GB of RAM. What is preventing db2 from using the memory?

db2girl · #6 (**permalink**) 07-24-09, 21:41

Check ulimit (data) of root. unlimited may not be in effect if the instance is started from root and ulimit of root is not unlimited. Try restarting the instance using the instance owner id.

db2aix · #7 (**permalink**) 07-27-09, 09:36

Can you explain why we need to check root's limit and also how to find out ulimit settings db2 is running with?

db2girl · #8 (**permalink**) 07-27-09, 18:00

The following technote should answer your question

db2girl · #9 (**permalink**) 07-27-09, 18:07

Sorry, don't know what happened to my window...

Technote:
IBM - Why are the instance owner ulimits ignored when the instance is started with the DB2 fault monitor?

The ulimits are inherited from the user ID that starts the instance. If the instance is started from the non-instance owner ID (ie. some admin or root), then the ulimits for that ID are in effect. Root's ulimit is applied when the instance is autostarted on reboot. The pstat command mentioned in the technote can be used to check the ulimits in effect (look at the "max data" value).

The technote mentions EFBIG (in case of "file" not being set to unlimited), You'll see ENOMEM if db2 is unable to allocate private memory (for "data" parameter). If pstat confirms that ulimit (data) is not unlimited, change root's ulimit to match the instance owner ID.

Internet Services

Web Development

Digital Marketing

Consumer Tech