Run out of space when re-locating table index

AIX V4.1.5, Online 7.2.3 UC1, IBM RS6000 R24 I needed to free up some space on a chunk while waiting for more physical disks to be delivered. I wanted to drop a large index (approx 70,000 blocks) and re-create it on a dbspace with more available space. Index was dropped successfully. Started to create the index and after about 10 mins of the SQL statement running in dbaccess got an error message stating that there was not enough space on the device. I ran the statement again - this time monitoring onstat -d -r to check space utilisation. The SQL again aborted after approx 10 mins with the same error, although there should have been plenty of space remaining in the chunk. During the create of the index it would have filled one chunk in the dbspace and then started to use a new chunk. The onstat -d display indicated that this was happening until it suddenly froze a short way into the second chunk. I tried the process again using a different dbspace which should also have had more than adequate free space, however the outcome was the same. Had to abandon this approach and let the original configuration restore. Fortunately this was not the production server. Has anyone seen this before or got ANY suggestions? Thank-you Nuala -- Nuala Donaldson

This has happened to me before. I was out of temp space in DBTEMPSPACE dbspace, and kept looking in the wrong place to see what filled up. If you don't want to change the size of DBTEMPSPACE, you can take out the dbspace names out of onconfig file. This way all the files will go into /tmp directory which should have plenty of space. Good luck

Yes we were out of tempdbspace the evidence is shown below. The index that I was trying to relocate is Index i_time2 on the timecard table. It is around 70,000 blocks so the system may have been just beyond the limit - if the rule of thumb to double the size for tempspace applies- does this hold? There is also another problem with the configuration shown below, as you sharp eyed readers will detect. It is that the system was intended to have tempdbs1, tempdbs2, tempdbs3, tempdbs4, tempdbs5 - however only tempdbs 4 and 5 are proper temp spaces (flagged as 'T') as the first 3 were not set up correctly (not me!) so they are not available as tempspace and must be redone. Many thanks to all for the fast and accurate diagnosis!!! Nuala R24 REP /> oncheck -pt son_db:timecard TBLspace Report for son_db:informix.timecard Table fragment in DBspace elitedbs1 Physical Address 60ff7b Creation date 06/14/97 09:22:36 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 151 Number of special columns 0 Number of keys 0 Number of extents 11 Current serial value 5232744 First extent size 25000 Next extent size 2500 Number of pages allocated 50000 Number of pages used 48374 Number of data pages 48368 Number of rows 1257558 Partition partnum 6291633 Partition lockid 6291633 Extents Logical Page Physical Page Size 0 61375b 25000 25000 65845f 2500 27500 a00dcb 2500 30000 6325fc 2500 32500 a1dd4b 2500 35000 a5faa8 2500 37500 a03241 2500 40000 a0a6a6 2500 42500 a20da6 2500 45000 a708b7 2500 47500 a25d3e 2500 Table fragment in DBspace elitedbs2 Physical Address 711d35 Creation date 06/14/97 09:22:36 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 151 Number of special columns 0 Number of keys 0 Number of extents 11 Current serial value 1 First extent size 25000 Next extent size 2500 Number of pages allocated 50000 Number of pages used 48546 Number of data pages 48540 Number of rows 1262023 Partition partnum 7340109 Partition lockid 6291633 Extents Logical Page Physical Page Size 0 71d13b 25000 25000 759abd 2500 27500 737268 2500 30000 73301b 2500 32500 734ee3 2500 35000 72a6e4 2500 37500 7421da 2500 40000 74f4cd 2500 42500 75b8ad 2500 45000 762bb3 2500 47500 1208239 2500 Table fragment in DBspace elitedbs3 Physical Address 800017 Creation date 06/14/97 09:22:36 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 151 Number of special columns 0 Number of keys 0 Number of extents 11 Current serial value 1 First extent size 25000 Next extent size 2500 Number of pages allocated 50000 Number of pages used 48416 Number of data pages 48410 Number of rows 1258649 Partition partnum 8388628 Partition lockid 6291633 Extents Logical Page Physical Page Size 0 810fe6 25000 25000 859a7f 2500 27500 82fa5e 2500 30000 82a10d 2500 32500 82d7be 2500 35000 80e946 2500 37500 844485 2500 40000 84b29e 2500 42500 84fa71 2500 45000 84cceb 2500 47500 858aa6 2500 Table fragment in DBspace elitedbs4 Physical Address 900011 Creation date 06/14/97 09:22:36 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 151 Number of special columns 0 Number of keys 0 Number of extents 11 Current serial value 1 First extent size 25000 Next extent size 2500 Number of pages allocated 50000 Number of pages used 48498 Number of data pages 48492 Number of rows 1260790 Partition partnum 9437198 Partition lockid 6291633 Extents Logical Page Physical Page Size 0 90d4fc 25000 25000 93fe04 2500 27500 925543 2500 30000 91746b 2500 32500 9240c1 2500 35000 90a482 2500 37500 91e711 2500 40000 9367fb 2500 42500 93f103 2500 45000 943045 2500 47500 948573 2500 Index i_time1 fragment in DBspace elitedbs5 Physical Address b00024 Creation date 10/10/98 15:35:27 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 151 Number of special columns 0 Number of keys 1 Number of extents 22 Current serial value 1 First extent size 2152 Next extent size 430 Number of pages allocated 23437@

In article <RAj$JCAp0xo4EwLp@editmode.demon.co.uk>, Nuala Donaldson <nuala@editmode.demon.co.uk> wrote: > Yes we were out of tempdbspace the evidence is shown below. The index > that I was trying to relocate is Index i_time2 on the timecard table. -- SNIP ---- M[sr] Donalson (A bit vague in the first name department.. ;) In the IIUG archives is a script called "monitor-space" (or did it get entered as monitor_space?). Part of that package is a shell script called dbspace-pages.sh, which filters and massages the output of onstat -d and produces a listing of how much space each dbspace hasfree. I think Clay Irving submitted a similar script written in perl. dbspace-pages.sh can help you keep an eye on dbspace free space while you are running this type of transaction. Just FYI. +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.

In article <881k0p$jme$1@nnrp1.deja.com>, Jacob Salomon <JSalomon@bn.com> writes >M[sr] Donalson (A bit vague in the first name department.. ;) >>Oops Trills sometimes do this... >>member of the family set up another e-mail account - messed up my defaults, it IS Nuala >In the IIUG archives is a script called "monitor-space" (or did it get >entered as monitor_space?). Part of that package is a shell script >called dbspace-pages.sh >>Will check this out, thank-you! -- Nuala Donaldson