Create Tablespace error SQL0968 File system full

[kozlow]

Running UDB 7.2 on a RS6000 server running AIX4.3.3

df -k shows 46423068K free on /db2prod/data

ran

create tablespace DATA01 managed by database
using (file '/db2prod/data/data01.f01' 46423068K)

This brought the database to its knees. No one could log in and all the transactions where timing out. After about an hour... Received error message:
SQL0968C The File system is full. SQLSTATE=57011

It removed the data01.f01 file from /db2prod/data. Again showing 46423068K free.....

Does the database need more space then I allocated for overhead and how much. I read that it needs three extents for overhead, but I was assuming that was part of the 46423068K I assigned.

Also, why did the database hang on creating a new tablespace????

Thanks in advance....

 

[kozlow]

I ran a test on a development database. Started off with the complete unused size of a 64 Meg filesystem... backing off 1 page (4k) from each run until it was successful, completed after I subtracted 76K. I then created a 128 Meg filesystem and it worked at 68K off, Third test was a 192 Meg and it worked at 188K off... I can't make any guesses on how much to subtract in order to have it complete......

 

[MarcLodge]

kozlow,
I think that the number that you are specifying after the device and filespec refers to the number of 4k pages, not the actual space available.
hth
Marc

 

[kozlow]

When I read the man pages on it, it said you can express space in pages (4k) and K, M or G. If the test I ran where in pages not K, the numbers I would have been using would be 4 times the amount I specified, so it would not have worked until it was a 1/4 of the size I first entered. Unless I am reading your reply wrong. I also ran a test using pages (dividing the free space K by 4) and it did not work when I allocated all the free space in the filesystem.....

 

[MarcLodge]

kozlow,
The only examples I've ever seen have specified no of pages, but obviously from your experience 'k' seems acceptable. The only thing that occurs to me is that you appear to be attempting to use all the space 'available' rather than estimating the initial usage and growth, and building the database around that.

I did find a couple of articles that might be of use:

http://www7b.software.ibm.com/dmdd/library/techarticle/0205parlapalli/0205parlapalli.html

http://groups.google.com/groups?q="...207151052.426b10f7@posting.google.com&rnum=10

Sorry I can't be more help.
Marc

 

[kozlow]

Our MainFrame DB2 DBA is as confused as I am.... It may be something specific to UDB and/or UNIX. I have searched the IBM DB2 site and the internet with no results. Since this is a production system, I am asking for some downtime to run the command and quess-ta-mate how much space to allocate. Sometimes the answer is to fudge it and just have some wasted unusable space....

Thanks for the input...