having problem with extents
Hi,
When inserting data into a table w_standard using Informix Dynamic
Server 9.30.FC2 running on a Sun E3000 with Solaris 8 OS, the database
returns the following errors
XIX000:-271:Could not insert new row into the table.
XIX000:-136:ISAM error: no more extents
The table w_standard is defined as
create table w_standard (
....
)
in wsc
extent size 2097000
next size 2097000;
The table is in dbspace wsc. There are two indices defined for the
table. The indices are also in dbspace wsc.
The error code in the online documentation suggests checking the
partnum field for this table in the systables table. The entry for
this table in systables is
tabname w_standard
owner informix
partnum 10485762
tabid 489
rowsize 2125
ncols 20
nindexes 2
nrows 190387989
created 2003-05-08
version 32309269
tabtype T
locklevel P
npused 15047963
fextsize 2097000
nextsize 2097000
flags 0
site
dbname
type_xid 0
am_id 0
The partnum value corresponds to a00002 in hexadecimal. The most
significant 2 digits is a0. Running onstat -t this corresponds to the
second to last row output.
Tblspaces
n address flgs ucnt tblnum physaddr npages nused npdata
nrows nextns resident
1 31327b10 0 1 100001 10000e 250 223 0 0
1 0
4 313a11e0 0 1 200001 200004 50 2 0 0
1 0
5 313a1898 0 1 300001 400004 100 100 0 0
2 0
15 313a2028 0 1 400001 500004 100 100 0 0
2 0
25 313a26e8 0 1 500001 600004 100 100 0 0
2 0
35 313a3028 0 1 600001 700004 100 100 0 0
2 0
45 313a33a0 0 1 700001 800004 450 449 0 0
9 0
82 313a3ce8 0 1 800001 900004 200 182 0 0
4 0
83 313a5028 0 1 900001 1200004 50 2 0 0
1 0
84 313a5550 0 1 a00001 1300004 50 5 0 0
1 0
88 313a5a78 0 1 b00001 2900004 50 2 0 0
1 0
11 active, 88 total
I don't understand why it shows nextns as 1. In any case, from
running onmonitor, we find wsc has 25 chunks,
19 0 1048575 1048575 /dev/md/rdsk/wsc1
PO-
20 0 1048575 1048575 /dev/md/rdsk/wsc2
PO-
22 0 1048575 1048575 /dev/md/rdsk/wsc4
PO-
21 0 1048575 1048575 /dev/md/rdsk/wsc3
PO-
23 0 1048575 1048575 /dev/md/rdsk/wsc5
PO-
24 0 1048575 1048575 /dev/md/rdsk/wsc7
PO-
25 0 1048575 1048575 /dev/md/rdsk/wsc8
PO-
26 0 1048575 1048575 /dev/md/rdsk/wsc6
PO-
27 0 1048575 1048575 /dev/md/rdsk/wsc9
PO-
28 0 1048575 1048575 /dev/md/rdsk/wsc10
PO-
29 0 1048575 1048575 /dev/md/rdsk/wsc11
PO-
30 0 1048575 1048575 /dev/md/rdsk/wsc12
PO-
31 0 1048575 1048575 /dev/md/rdsk/wsc13
PO-
32 0 1048575 1048575 /dev/md/rdsk/wsc14
PO-
33 0 1048575 1048575 /dev/md/rdsk/wsc15
PO-
34 0 1048575 1048575 /dev/md/rdsk/wsc16
PO-
35 0 1048575 1048575 /dev/md/rdsk/wsc17
PO-
36 0 1048575 1048575 /dev/md/rdsk/wsc18
PO-
37 0 1048575 1048575 /dev/md/rdsk/wsc19
PO-
38 0 1048575 1048575 /dev/md/rdsk/wsc20
PO-
44 0 1048575 1048575 /dev/md/rdsk/wsc21
PO-
45 0 1048575 1047126 /dev/md/rdsk/wsc22
PO-
46 0 1048575 286563 /dev/md/rdsk/wsc23
PO-
47 0 1048575 3 /dev/md/rdsk/wsc24
PO-
48 0 1048575 3 /dev/md/rdsk/wsc25
PO-
A page is 2k.
Chunk number 46 has allocated space for the index. Shouldn't the
table allocate space in chunk 47 for the next extent, or is there some
other problem?
Thanks,
Steve
Try running:
ALTER FRAGMENT ON TABLE w_standard INIT IN wsc;
This should reorganize (defragment) table w_standard. Don't wory if your
table is not fragmented. You can use any dbspace including the original
(wsc) one.
You can do the same on index:
ALTER FRAGMENT ON INDEX <index name> INIT IN <dbspace name>;
... to defragment indexes.
Gorazd
"Steven Kurlander" <skurlander@yahoo.com> wrote in message
news:4f7c9960.0309280830.578d2260@posting.google.com...
> Hi,
>
> When inserting data into a table w_standard using Informix Dynamic
> Server 9.30.FC2 running on a Sun E3000 with Solaris 8 OS, the database
> returns the following errors
>
> XIX000:-271:Could not insert new row into the table.
> XIX000:-136:ISAM error: no more extents
>
> The table w_standard is defined as
>
> create table w_standard (> ....
> )
> in wsc
> extent size 2097000
> next size 2097000;
>
> The table is in dbspace wsc. There are two indices defined for the
> table. The indices are also in dbspace wsc.
>
> The error code in the online documentation suggests checking the
> partnum field for this table in the systables table. The entry for
> this table in systables is
>
> tabname w_standard
> owner informix
> partnum 10485762
> tabid 489
> rowsize 2125
> ncols 20
> nindexes 2
> nrows 190387989
> created 2003-05-08
> version 32309269
> tabtype T
> locklevel P
> npused 15047963
> fextsize 2097000
> nextsize 2097000
> flags 0
> site
> dbname
> type_xid 0
> am_id 0
>
> The partnum value corresponds to a00002 in hexadecimal. The most
> significant 2 digits is a0. Running onstat -t this corresponds to the
> second to last row output.
>
> Tblspaces
> n address flgs ucnt tblnum physaddr npages nused npdata
> nrows nextns resident
> 1 31327b10 0 1 100001 10000e 250 223 0 0
> 1 0
> 4 313a11e0 0 1 200001 200004 50 2 0 0
> 1 0
> 5 313a1898 0 1 300001 400004 100 100 0 0
> 2 0
> 15 313a2028 0 1 400001 500004 100 100 0 0
> 2 0
> 25 313a26e8 0 1 500001 600004 100 100 0 0
> 2 0
> 35 313a3028 0 1 600001 700004 100 100 0 0
> 2 0
> 45 313a33a0 0 1 700001 800004 450 449 0 0
> 9 0
> 82 313a3ce8 0 1 800001 900004 200 182 0 0
> 4 0
> 83 313a5028 0 1 900001 1200004 50 2 0 0
> 1 0
> 84 313a5550 0 1 a00001 1300004 50 5 0 0
> 1 0
> 88 313a5a78 0 1 b00001 2900004 50 2 0 0
> 1 0
> 11 active, 88 total
>
> I don't understand why it shows nextns as 1. In any case, from
> running onmonitor, we find wsc has 25 chunks,
>
> 19 0 1048575 1048575 /dev/md/rdsk/wsc1
> PO-
> 20 0 1048575 1048575 /dev/md/rdsk/wsc2
> PO-
> 22 0 1048575 1048575 /dev/md/rdsk/wsc4
> PO-
> 21 0 1048575 1048575 /dev/md/rdsk/wsc3
> PO-
> 23 0 1048575 1048575 /dev/md/rdsk/wsc5
> PO-
> 24 0 1048575 1048575 /dev/md/rdsk/wsc7
> PO-
> 25 0 1048575 1048575 /dev/md/rdsk/wsc8
> PO-
> 26 0 1048575 1048575 /dev/md/rdsk/wsc6
> PO-
> 27 0 1048575 1048575 /dev/md/rdsk/wsc9
> PO-
> 28 0 1048575 1048575 /dev/md/rdsk/wsc10
> PO-
> 29 0 1048575 1048575 /dev/md/rdsk/wsc11
> PO-
> 30 0 1048575 1048575 /dev/md/rdsk/wsc12
> PO-
> 31 0 1048575 1048575 /dev/md/rdsk/wsc13
> PO-
> 32 0 1048575 1048575 /dev/md/rdsk/wsc14
> PO-
> 33 0 1048575 1048575 /dev/md/rdsk/wsc15
> PO-
> 34 0 1048575 1048575 /dev/md/rdsk/wsc16
> PO-
> 35 0 1048575 1048575 /dev/md/rdsk/wsc17
> PO-
> 36 0 1048575 1048575 /dev/md/rdsk/wsc18
> PO-
> 37 0 1048575 1048575 /dev/md/rdsk/wsc19
> PO-
> 38 0 1048575 1048575 /dev/md/rdsk/wsc20
> PO-
> 44 0 1048575 1048575 /dev/md/rdsk/wsc21
> PO-
> 45 0 1048575 1047126 /dev/md/rdsk/wsc22
> PO-
> 46 0 1048575 286563 /dev/md/rdsk/wsc23
> PO-
> 47 0 1048575 3 /dev/md/rdsk/wsc24
> PO-
> 48 0 1048575 3 /dev/md/rdsk/wsc25
> PO-
>
> A page is 2k.
>
> Chunk number 46 has allocated space for the index. Shouldn't the
> table allocate space in chunk 47 for the next extent, or is there some
> other problem?
>
> Thanks,
> Steve
Can you post the output from oncheck -pt databasename:w_standard?
My top suspects would be:
1. You've hit the maximum number of pages for a tablespace in a single
fragment (16,777,215) or
2. your dbspace is so fragmented that you've hit the maximum number of
extents for a tablespace (derived from an impenetrable formula, but usually
around 250).
--
Neil Truby t:01932 724027
Director m:07798 811708
Ardenta Limited e:neil.truby@ardenta.com
"Steven Kurlander" <skurlander@yahoo.com> wrote in message
news:4f7c9960.0309280830.578d2260@posting.google.com...
> Hi,
>
> When inserting data into a table w_standard using Informix Dynamic
> Server 9.30.FC2 running on a Sun E3000 with Solaris 8 OS, the database
> returns the following errors
>
> XIX000:-271:Could not insert new row into the table.
> XIX000:-136:ISAM error: no more extents
>
> The table w_standard is defined as
>
> create table w_standard (> ....
> )
> in wsc
> extent size 2097000
> next size 2097000;
>
> The table is in dbspace wsc. There are two indices defined for the
> table. The indices are also in dbspace wsc.
>
> The error code in the online documentation suggests checking the
> partnum field for this table in the systables table. The entry for
> this table in systables is
>
> tabname w_standard
> owner informix
> partnum 10485762
> tabid 489
> rowsize 2125
> ncols 20
> nindexes 2
> nrows 190387989
> created 2003-05-08
> version 32309269
> tabtype T
> locklevel P
> npused 15047963
> fextsize 2097000
> nextsize 2097000
> flags 0
> site
> dbname
> type_xid 0
> am_id 0
>
> The partnum value corresponds to a00002 in hexadecimal. The most
> significant 2 digits is a0. Running onstat -t this corresponds to the
> second to last row output.
>
> Tblspaces
> n address flgs ucnt tblnum physaddr npages nused npdata
> nrows nextns resident
> 1 31327b10 0 1 100001 10000e 250 223 0 0
> 1 0
> 4 313a11e0 0 1 200001 200004 50 2 0 0
> 1 0
> 5 313a1898 0 1 300001 400004 100 100 0 0
> 2 0
> 15 313a2028 0 1 400001 500004 100 100 0 0
> 2 0
> 25 313a26e8 0 1 500001 600004 100 100 0 0
> 2 0
> 35 313a3028 0 1 600001 700004 100 100 0 0
> 2 0
> 45 313a33a0 0 1 700001 800004 450 449 0 0
> 9 0
> 82 313a3ce8 0 1 800001 900004 200 182 0 0
> 4 0
> 83 313a5028 0 1 900001 1200004 50 2 0 0
> 1 0
> 84 313a5550 0 1 a00001 1300004 50 5 0 0
> 1 0
> 88 313a5a78 0 1 b00001 2900004 50 2 0 0
> 1 0
> 11 active, 88 total
>
> I don't understand why it shows nextns as 1. In any case, from
> running onmonitor, we find wsc has 25 chunks,
>
> 19 0 1048575 1048575 /dev/md/rdsk/wsc1
> PO-
> 20 0 1048575 1048575 /dev/md/rdsk/wsc2
> PO-
> 22 0 1048575 1048575 /dev/md/rdsk/wsc4
> PO-
> 21 0 1048575 1048575 /dev/md/rdsk/wsc3
> PO-
> 23 0 1048575 1048575 /dev/md/rdsk/wsc5
> PO-
> 24 0 1048575 1048575 /dev/md/rdsk/wsc7
> PO-
> 25 0 1048575 1048575 /dev/md/rdsk/wsc8
> PO-
> 26 0 1048575 1048575 /dev/md/rdsk/wsc6
> PO-
> 27 0 1048575 1048575 /dev/md/rdsk/wsc9
> PO-
> 28 0 1048575 1048575 /dev/md/rdsk/wsc10
> PO-
> 29 0 1048575 1048575 /dev/md/rdsk/wsc11
> PO-
> 30 0 1048575 1048575 /dev/md/rdsk/wsc12
> PO-
> 31 0 1048575 1048575 /dev/md/rdsk/wsc13
> PO-
> 32 0 1048575 1048575 /dev/md/rdsk/wsc14
> PO-
> 33 0 1048575 1048575 /dev/md/rdsk/wsc15
> PO-
> 34 0 1048575 1048575 /dev/md/rdsk/wsc16
> PO-
> 35 0 1048575 1048575 /dev/md/rdsk/wsc17
> PO-
> 36 0 1048575 1048575 /dev/md/rdsk/wsc18
> PO-
> 37 0 1048575 1048575 /dev/md/rdsk/wsc19
> PO-
> 38 0 1048575 1048575 /dev/md/rdsk/wsc20
> PO-
> 44 0 1048575 1048575 /dev/md/rdsk/wsc21
> PO-
> 45 0 1048575 1047126 /dev/md/rdsk/wsc22
> PO-
> 46 0 1048575 286563 /dev/md/rdsk/wsc23
> PO-
> 47 0 1048575 3 /dev/md/rdsk/wsc24
> PO-
> 48 0 1048575 3 /dev/md/rdsk/wsc25
> PO-
>
> A page is 2k.
>
> Chunk number 46 has allocated space for the index. Shouldn't the
> table allocate space in chunk 47 for the next extent, or is there some
> other problem?
>
> Thanks,
> Steve
I've run oncheck -pt on the table. Unfortunately, I had already
deleted the two indices on the tables. I wanted to place them in
separate dbspaces and to increase the chunk sizes beyond 2gb so I will
have larger single extents. I think in total I had allocated no more
than 75 extents.
Looking at the results below I have the maximum number of allocated
pages for table w_standard, as the table is not fragmented. I will
initialize a multi-fragmentation scheme.
Thanks.
TBLspace Report for ts_db:informix.w_standard
Physical Address 1300005
Creation date 05/08/2003 20:11:15
TBLspace Flags 901 Page Locking
TBLspace contains
VARCHARS
TBLspace use 4 bit
bit-maps
Maximum row size 2125
Number of special columns 1
Number of keys 0
Number of extents 17
Current serial value 1
First extent size 1048500
Next extent size 1048575
Number of pages allocated 16777215
Number of pages used 16777215
Number of data pages 16728746
Number of rows 211194145
Partition partnum 10485762
Partition lockid 10485762
Extents
Logical Page Physical Page Size
0 1300035 1048500
1048500 1400003 1048500
2097000 1600003 1048500
3145500 1500003 1048500
4194000 1700003 1048500
5242500 1800003 1048500
6291000 1900003 1048500
7339500 1a00003 1048500
8388000 1b00003 1048500
9436500 1c00003 1048500
10485000 1d00003 1048500
11533500 1e00003 1048500
12582000 1f00003 1048500
13630500 2000003 1048500
14679000 2100003 1048500
15727500 2c7908f 552816
16280316 2d6f033 496899
"Neil Truby" <neil.truby@ardenta.com> wrote in message news:<bl7aj0$8r2lm$1@ID-162943.news.uni-berlin.de>...
> Can you post the output from oncheck -pt databasename:w_standard?
>
> My top suspects would be:
>
> 1. You've hit the maximum number of pages for a tablespace in a single
> fragment (16,777,215) or
> 2. your dbspace is so fragmented that you've hit the maximum number of
> extents for a tablespace (derived from an impenetrable formula, but usually
> around 250).
>
> --
> Neil Truby t:01932 724027
> Director m:07798 811708
> Ardenta Limited e:neil.truby@ardenta.com
>