TSM 6.1, DB2 Buffer Pools and SQL1218N

[kreucher]

Hello,

I've upgraded to TSM 6.1 and am running into a problem where the server is frequently running into SQL1218N: There are no pages currently available in bufferpool "4097". SQLSTATE=57011

Running TSM 6.1.2 / Linux x86_64 on a box with 4G of ram. After hours of reading docs, I am basically at a loss on how to fix it

TSM docs seem to say DB2 memory parameters are auto tuning? hmm... DB2 docs say when this error occurs to increase the buffer pool sizes, so I look and there are several defined:

db2 SELECT BPNAME FROM SYSCAT.BUFFERPOOLS
  IBMDEFAULTBP
  IDXBUFPOOL1
  LARGEBUFPOOL1
  LARGEIDXBUFPOOL1

But when I look at db2mtrk -d, I only see the system buffer pools (S32K, S16K, S8K and S4K):

db2mtrk -d
Tracking Memory on: 2009/12/19 at 01:28:40

Memory for database: TSMDB1  

   utilh       pckcacheh   other       catcacheh   bph (S32K)  bph (S16K)  
   64.0K       19.2M       192.0K      1.1M        832.0K      576.0K      

   bph (S8K)   bph (S4K)   shsorth     lockh       dbh         apph (383)  
   448.0K      384.0K      2.2M        9.2M        20.6M       64.0K       

   apph (381)  apph (380)  apph (377)  apph (375)  apph (373)  apph (371)  
   64.0K       64.0K       64.0K       64.0K       64.0K       64.0K       

   apph (369)  apph (366)  apph (364)  apph (363)  apph (360)  apph (359)  
   64.0K       64.0K       64.0K       64.0K       64.0K       64.0K       

   apph (357)  apph (349)  apph (345)  apph (344)  apph (317)  apph (290)  
   64.0K       64.0K       64.0K       64.0K       64.0K       64.0K       

   apph (289)  apph (287)  apph (286)  apph (284)  apph (243)  apph (117)  
   64.0K       64.0K       64.0K       64.0K       64.0K       64.0K       

   apph (102)  apph (19)   apph (10)   apph (9)    apph (8)    appshrh     
   64.0K       128.0K      64.0K       64.0K       64.0K       2.7M

db2top also shows just the tiny system buffer pools in use... no surprise they are overflowing!

I've tried to create my own buffer pools, but they don't get used. I even tried to db2 ALTER tablespace IDXSPACE1 et al to use my new buffer pools, but still nothing. Actually, when I restart dsmserv, any buffer pools I manually define seem to disappear everywhere (db2top et al) except SYSCAT.BUFFERPOOLS. But really, I'd think I would not have to create buffer pools manually...

I even tried applying the latest fixpack to DB2 (now at v9.5.0.5), hoping that would somehow help. nada.

If it's not obvious, I'm pretty new to DB2.

BTW, my DBMEMPERCENT is set to AUTO.

Help!

Thanks,
- nick

[kreucher]

I think I may have actually solved it

My kernel shared memory parameters were a bit wonky for my 4G machine:
kernel.shmmax = 1073741824
kernel.shmall = 8388608

I changed to match the 4G I have, and loads of buffer pool pages appeared after a restart of TSM:
kernel.shmmax = 4294967296
kernel.shmall = 1048576

WOW, things are much, much faster now, and no more SQL1218N errors!