hi all,

i have a history table of 4.5 million rows in DB2 V7.2.5 under AIX 4.3.3 ML 11, i got the follwoing error while inserting rows in table:

2004-02-18-06.15.19.739961 Instance:db2inst1 Node:000
PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
buffer_pool_services sqlbWritePageToContainer Probe:20 Database:MELODY

SMS Tablespace 2(USERSPACE1) is FULL or file is too large (at OS or user limit).
Detected on Container 0. ContPage= 512143 Obj=28 Type=0

2004-02-18-06.15.19.951587 Instance:db2inst1 Node:000
PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
buffer_pool_services sqlbfix Probe:1235 Database:MELODY

DiagData
ffff d60c

2004-02-18-06.15.20.161279 Instance:db2inst1 Node:000
PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
buffer_pool_services sqlbfix Probe:3 Database:MELODY
DIA3612C Disk was full.

ZRC=0xFFFFD60C


when i change the soft file limit in /etc/security/limits to bigger size, then stop and restart the db engine, this error stoped.

can anyone tell why db2 cannot use many files in SMS table space and have this problem with file size ??

another question is, why it take 2 minutes to count the rows in this table
db2 "select count(*) from history"
thakns in advance

With a select Count(*) from table_name, DB2 either has to read all the rows of the table, or read all the index entries in one of the indexes (if an index exists).

The other option, if you have executed runstats recently and you don't need an exact count, is to look at the CARD column of syscat.tables. See the DB2 catalog definition in the appendix of the SQL Reference Vol 1, for more information.

Hi hanyheggy,

you did just the right thing. But why would you want to have DB2 assign multiple containers to SMS table spaces - the OS can do that, in the case of AIX via LVM. Furthermore, it is the OS that detected a problem with the file size and prohibited further expansion for policy reasons, and DB2 had nothing to do with this..

Johann