db2 storage error when running stored procs

Question

I am seeing the above error when running a small number of stored procs in our application. The actual error that we get is sqlcode=-930, which according to the db2 documentation is: 'There is not enough storage available to process the statement.'

Question: how can i increase the storage available to these procs? has anyone else encountered this error and if so did you find a solution?

thanks

Answer 1

Since you haven't answered as to which platform you're using, I'll take a stab at AIX for starters.

You need to have a look at the db2diag.log to get the specific cause but it's usually because AIX has run out of shared memory segments.

AIX has a total of 16 shared memory segments, of which 7 are available for the database. The amount actually used by your database can be calculated with a formula too complicated for me to remember without looking it up in the troubleshooting guide.

But there's a real possibility that a database will use 6 segments, leaving just one. I've seen situations where that happens then a memory-mapped I/O request happens at the same time as a stored procedure.

Both these need a shared memory segment and, if the I/O begins first, the stored procedure will fail as you are seeing.

The ways to fix this are to free up segments by doing one or more of:

• disable memory mapped I/O.
• reduce the segment usage by the database: you'll need that formula but I think it involves buffer pools, heap sizes and lock lists.
• turn off intra_parallel if it's not needed.