Calculating extent sizes
Hi,
I created a table using next command:
create table "abba".water2009test
(
awdate datetime year to minute,
statnam char(6),
statdev char(6),
flags smallint,
signs smallint,
awertf smallfloat
size 131072 lock mode page;
) in dynabbadbs extent size 131072
next size 131072 lock mode row;
Using dbschema, my table seems te be created like expected:
dbschema -ss -q -d abba -t water2009test{ TABLE "abba".water2009test row size = 27 number of columns = 6 index
size = 0 }
create table "abba".water2009test
(
awdate datetime year to minute,
statnam char(6),
statdev char(6),
flags smallint,
signs smallint,
awertf smallfloat
) in dynabbadbs extent size 131072 next
size 131072 lock mode row;
After filling the table with 328123409 lines of data, I checked the
extends by running next sql command
select * from systabnames, sysptnext
where partnum = pe_partnum
and dbsname = 'abba'
and tabname = "water2009test"
The sql returns the next result
partnum 3145742
dbsname abba
owner abba
tabname water2009test
collate en_US.819
pe_partnum 3145742
pe_extnum 0
pe_chunk 3
pe_offset 87598457
pe_size 4587520
pe_log 0
partnum 3145742
dbsname abba
owner abba
tabname water2009test
collate en_US.819
pe_partnum 3145742
pe_extnum 1
pe_chunk 3
pe_offset 103513199
pe_size 196608
pe_log 4587520
I really don't understand how the extend sizes I specified are related
to this result. Can anyone explain this to me?
The query is based on what I found on
http://www.informix.com.ua/articles/sysmast/sysmast.htm
Kind regards,
wimpunk
On 15/11/2010 13:35, wimpunk wrote:
> Hi,
>
> I created a table using next command:
>
> create table "abba".water2009test
> (
> awdate datetime year to minute,
> statnam char(6),
> statdev char(6),
> flags smallint,
> signs smallint,
> awertf smallfloat
> size 131072 lock mode page;
> ) in dynabbadbs extent size 131072
> next size 131072 lock mode row;
>
> Using dbschema, my table seems te be created like expected:
> dbschema -ss -q -d abba -t water2009test> { TABLE "abba".water2009test row size = 27 number of columns = 6 index
> size = 0 }
> create table "abba".water2009test
> (
> awdate datetime year to minute,
> statnam char(6),
> statdev char(6),
> flags smallint,
> signs smallint,
> awertf smallfloat
> ) in dynabbadbs extent size 131072 next
> size 131072 lock mode row;
>
> After filling the table with 328123409 lines of data, I checked the
> extends by running next sql command
>
> select * from systabnames, sysptnext
> where partnum = pe_partnum
> and dbsname = 'abba'
> and tabname = "water2009test">
> The sql returns the next result
>
> partnum 3145742
> dbsname abba
> owner abba
> tabname water2009test
> collate en_US.819
> pe_partnum 3145742
> pe_extnum 0
> pe_chunk 3
> pe_offset 87598457
> pe_size 4587520
> pe_log 0
>
> partnum 3145742
> dbsname abba
> owner abba
> tabname water2009test
> collate en_US.819
> pe_partnum 3145742
> pe_extnum 1
> pe_chunk 3
> pe_offset 103513199
> pe_size 196608
> pe_log 4587520
>
> I really don't understand how the extend sizes I specified are related
> to this result. Can anyone explain this to me?
>
> The query is based on what I found on
> http://www.informix.com.ua/articles/sysmast/sysmast.htm
>
> Kind regards,
>
> wimpunk
Extent size "doubling" ... i.e. you loaded a table which had an initial
extent size of 131072 ... extent 1 filled up and then the server
allocated a new extent and as it was able to allocate contiguous space
it concatenated the extent ... this happened a few times :P (I make it 35!).
Much easier to understand this using oncheck -pt output.