Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53

    Unanswered: sql2310n when performing runstats on a large table

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    But vmstat shows I have plenty of memory.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    This is a 64 bit instance, 32GB of RAM. What is preventing db2 from using the memory?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    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.

  7. #7
    Join Date
    Jul 2009
    Location
    USA
    Posts
    53
    Can you explain why we need to check root's limit and also how to find out ulimit settings db2 is running with?

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    The following technote should answer your question

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •