Informix

Hi to All

Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

1/ pages per extent 

dbspace with page size = 8k
table first extent : 32k means 4 pages,
table next extent : 32k means also 4 pages

2/ The table

{ TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

create table "user".table1
(
record_type char(2),
fil1 char(1),
proc_id char(6),
fil2 char(1),
inst_name char(50),
fil3 char(1),
inst_id char(11),
fil4 char(1),
settle_date char(8),
fil5 char(1),
bnumber char(4),
fil6 char(214),
unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
) in sgb_8k_da extent size 32 next size 32 lock mode page;



3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

output of sysmaster query:

database db
tabname table1
dbspace dbs_8k_da
partnum 35651595
row_size 300
num_columns 0
num_indexes 0
num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
rowfit Row smaller the pagesize
rows_per_page 27
free_rows 39396942
create_date 2021-01-28 14:11:43
lockreqs 0
lockwts 0
deadlks 0
lktouts 0
isreads 0
iswrites 0
isrewrites 0
isdeletes 0
bufreads 51
bufwrites 0
seqscans 0
pagreads 0
pagwrites 0
total_io 51
buff_read_percent 0,00000000000000
buff_write_percent 0,00000000000000
total_bytes_scann+ 0


4/ oncheck -pe (the extent size are not 32k) !

db:'user'.table1 3224579 1000000
db:'user'.table1 4097155 2456443
db:'user'.table1 3 6553596
db:'user'.table1 3 2097152
db:'user'.table1 122881 29680
db:'user'.table1 1020633 7500
db:'user'.table1 1036325 7500
db:'user'.table1 3629330 69271
db:'user'.table1 4205673 125000
db:'user'.table1 4712817 125000
db:'user'.table1 5829961 125000
db:'user'.table1 6457105 250000
db:'user'.table1 7209249 250000
db:'user'.table1 8473537 490000
db:'user'.table1 13704829 448000
db:'user'.table1 16316665 448000
db:'user'.table1 20166649 448000

thanks in advance

------------------------------
John Smith
------------------------------

#Informix

John:

The reason is extent compression. If a new extent is contiguous with an existing extent they are combined into a single extent. But the next size only doubles after 16 extentsat the current size. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Thu February 04, 2021 03:30 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck


Hi to All

Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

1/ pages per extent 

dbspace with page size = 8k
table first extent : 32k means 4 pages,
table next extent : 32k means also 4 pages

2/ The table

{ TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

create table "user".table1
(
record_type char(2),
fil1 char(1),
proc_id char(6),
fil2 char(1),
inst_name char(50),
fil3 char(1),
inst_id char(11),
fil4 char(1),
settle_date char(8),
fil5 char(1),
bnumber char(4),
fil6 char(214),
unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
) in sgb_8k_da extent size 32 next size 32 lock mode page;



3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

output of sysmaster query:

database db
tabname table1
dbspace dbs_8k_da
partnum 35651595
row_size 300
num_columns 0
num_indexes 0
num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
rowfit Row smaller the pagesize
rows_per_page 27
free_rows 39396942
create_date 2021-01-28 14:11:43
lockreqs 0
lockwts 0
deadlks 0
lktouts 0
isreads 0
iswrites 0
isrewrites 0
isdeletes 0
bufreads 51
bufwrites 0
seqscans 0
pagreads 0
pagwrites 0
total_io 51
buff_read_percent 0,00000000000000
buff_write_percent 0,00000000000000
total_bytes_scann+ 0


4/ oncheck -pe (the extent size are not 32k) !

db:'user'.table1 3224579 1000000
db:'user'.table1 4097155 2456443
db:'user'.table1 3 6553596
db:'user'.table1 3 2097152
db:'user'.table1 122881 29680
db:'user'.table1 1020633 7500
db:'user'.table1 1036325 7500
db:'user'.table1 3629330 69271
db:'user'.table1 4205673 125000
db:'user'.table1 4712817 125000
db:'user'.table1 5829961 125000
db:'user'.table1 6457105 250000
db:'user'.table1 7209249 250000
db:'user'.table1 8473537 490000
db:'user'.table1 13704829 448000
db:'user'.table1 16316665 448000
db:'user'.table1 20166649 448000

thanks in advance

------------------------------
John Smith
------------------------------

#Informix

Okay, so the right information is to have with oncheck
Thanks Art

------------------------------
John Smith
------------------------------

Original Message:
Sent: Thu February 04, 2021 05:35 AM
From: Art Kagel
Subject: nb of extents ?? dbschema vs oncheck

John:

The reason is extent compression. If a new extent is contiguous with an existing extent they are combined into a single extent. But the next size only doubles after 16 extentsat the current size. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Thu February 04, 2021 03:30 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck


Hi to All

Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

1/ pages per extent 

dbspace with page size = 8k
table first extent : 32k means 4 pages,
table next extent : 32k means also 4 pages

2/ The table

{ TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

create table "user".table1
(
record_type char(2),
fil1 char(1),
proc_id char(6),
fil2 char(1),
inst_name char(50),
fil3 char(1),
inst_id char(11),
fil4 char(1),
settle_date char(8),
fil5 char(1),
bnumber char(4),
fil6 char(214),
unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
) in sgb_8k_da extent size 32 next size 32 lock mode page;



3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

output of sysmaster query:

database db
tabname table1
dbspace dbs_8k_da
partnum 35651595
row_size 300
num_columns 0
num_indexes 0
num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
rowfit Row smaller the pagesize
rows_per_page 27
free_rows 39396942
create_date 2021-01-28 14:11:43
lockreqs 0
lockwts 0
deadlks 0
lktouts 0
isreads 0
iswrites 0
isrewrites 0
isdeletes 0
bufreads 51
bufwrites 0
seqscans 0
pagreads 0
pagwrites 0
total_io 51
buff_read_percent 0,00000000000000
buff_write_percent 0,00000000000000
total_bytes_scann+ 0


4/ oncheck -pe (the extent size are not 32k) !

db:'user'.table1 3224579 1000000
db:'user'.table1 4097155 2456443
db:'user'.table1 3 6553596
db:'user'.table1 3 2097152
db:'user'.table1 122881 29680
db:'user'.table1 1020633 7500
db:'user'.table1 1036325 7500
db:'user'.table1 3629330 69271
db:'user'.table1 4205673 125000
db:'user'.table1 4712817 125000
db:'user'.table1 5829961 125000
db:'user'.table1 6457105 250000
db:'user'.table1 7209249 250000
db:'user'.table1 8473537 490000
db:'user'.table1 13704829 448000
db:'user'.table1 16316665 448000
db:'user'.table1 20166649 448000

thanks in advance

------------------------------
John Smith
------------------------------

#Informix

John:

Your query has the correct information as well. 17 extents containing a total of 14,930,142 pages (FWIW: getting close to the 2^24 page limit), 13,469,338 of them are data pages containing 350,202,766 rows. The only thing there that is incorrect are the number of rows per page. Lester's query didn't add the four bytes per row for the slot table entry for the rows so it overestimated the number of rows per page. That should be 26 not 27. And if you do the math:

350202766 / 13469338 = 25.99999   # Probably some pages are not full.

Rows per page = (pagesize - 28) / (rowsize + 4) = (8092 - 28) / (300 + 4) = 8064 / 304 = 26.52631


num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
​...
rows_per_page 27

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Thu February 04, 2021 06:51 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck

Okay, so the right information is to have with oncheck
Thanks Art

------------------------------
John Smith

Original Message:
Sent: Thu February 04, 2021 05:35 AM
From: Art Kagel
Subject: nb of extents ?? dbschema vs oncheck

John:

The reason is extent compression. If a new extent is contiguous with an existing extent they are combined into a single extent. But the next size only doubles after 16 extentsat the current size. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Thu February 04, 2021 03:30 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck


Hi to All

Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

1/ pages per extent 

dbspace with page size = 8k
table first extent : 32k means 4 pages,
table next extent : 32k means also 4 pages

2/ The table

{ TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

create table "user".table1
(
record_type char(2),
fil1 char(1),
proc_id char(6),
fil2 char(1),
inst_name char(50),
fil3 char(1),
inst_id char(11),
fil4 char(1),
settle_date char(8),
fil5 char(1),
bnumber char(4),
fil6 char(214),
unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
) in sgb_8k_da extent size 32 next size 32 lock mode page;



3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

output of sysmaster query:

database db
tabname table1
dbspace dbs_8k_da
partnum 35651595
row_size 300
num_columns 0
num_indexes 0
num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
rowfit Row smaller the pagesize
rows_per_page 27
free_rows 39396942
create_date 2021-01-28 14:11:43
lockreqs 0
lockwts 0
deadlks 0
lktouts 0
isreads 0
iswrites 0
isrewrites 0
isdeletes 0
bufreads 51
bufwrites 0
seqscans 0
pagreads 0
pagwrites 0
total_io 51
buff_read_percent 0,00000000000000
buff_write_percent 0,00000000000000
total_bytes_scann+ 0


4/ oncheck -pe (the extent size are not 32k) !

db:'user'.table1 3224579 1000000
db:'user'.table1 4097155 2456443
db:'user'.table1 3 6553596
db:'user'.table1 3 2097152
db:'user'.table1 122881 29680
db:'user'.table1 1020633 7500
db:'user'.table1 1036325 7500
db:'user'.table1 3629330 69271
db:'user'.table1 4205673 125000
db:'user'.table1 4712817 125000
db:'user'.table1 5829961 125000
db:'user'.table1 6457105 250000
db:'user'.table1 7209249 250000
db:'user'.table1 8473537 490000
db:'user'.table1 13704829 448000
db:'user'.table1 16316665 448000
db:'user'.table1 20166649 448000

thanks in advance

------------------------------
John Smith
------------------------------

#Informix

Yes, 
Thank you Art :)

------------------------------
John Smith
------------------------------

Original Message:
Sent: Thu February 04, 2021 08:49 AM
From: Art Kagel
Subject: nb of extents ?? dbschema vs oncheck

John:

Your query has the correct information as well. 17 extents containing a total of 14,930,142 pages (FWIW: getting close to the 2^24 page limit), 13,469,338 of them are data pages containing 350,202,766 rows. The only thing there that is incorrect are the number of rows per page. Lester's query didn't add the four bytes per row for the slot table entry for the rows so it overestimated the number of rows per page. That should be 26 not 27. And if you do the math:

350202766 / 13469338 = 25.99999   # Probably some pages are not full.

Rows per page = (pagesize - 28) / (rowsize + 4) = (8092 - 28) / (300 + 4) = 8064 / 304 = 26.52631


num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
​...
rows_per_page 27

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Thu February 04, 2021 06:51 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck

Okay, so the right information is to have with oncheck
Thanks Art

------------------------------
John Smith

Original Message:
Sent: Thu February 04, 2021 05:35 AM
From: Art Kagel
Subject: nb of extents ?? dbschema vs oncheck

John:

The reason is extent compression. If a new extent is contiguous with an existing extent they are combined into a single extent. But the next size only doubles after 16 extentsat the current size. 

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com

Original Message:
Sent: Thu February 04, 2021 03:30 AM
From: John Smith
Subject: nb of extents ?? dbschema vs oncheck


Hi to All

Can anyone help me to understand please :) , why extents sizes is differents between table schema and oncheck -pe ?

1/ pages per extent 

dbspace with page size = 8k
table first extent : 32k means 4 pages,
table next extent : 32k means also 4 pages

2/ The table

{ TABLE "user".table1 row size = 300 number of columns = 12 index size = 86 }

create table "user".table1
(
record_type char(2),
fil1 char(1),
proc_id char(6),
fil2 char(1),
inst_name char(50),
fil3 char(1),
inst_id char(11),
fil4 char(1),
settle_date char(8),
fil5 char(1),
bnumber char(4),
fil6 char(214),
unique (record_type,proc_id,inst_name,inst_id,settle_date,batch_number) constraint "user".u_table1
) in sgb_8k_da extent size 32 next size 32 lock mode page;



3/ infos with lester sysmaster (2019) query tableinfo_upd.sql

nb of extents for the table : 17 ,   rows_per_page : 27  (see below)

so 17 extents (of 32ko according to tabel schema) * 4 pages/extents * 27 rows/page = 1836 rows

but num_rows according to sysmaster query is 350 202 766 (same as "select count(*) :)" )

output of sysmaster query:

database db
tabname table1
dbspace dbs_8k_da
partnum 35651595
row_size 300
num_columns 0
num_indexes 0
num_extents 17
page_size 8192
pages_total 14930142
pages_used 13470996
pages_data 13469338
pages_free 1459146
num_rows 350202766
rowfit Row smaller the pagesize
rows_per_page 27
free_rows 39396942
create_date 2021-01-28 14:11:43
lockreqs 0
lockwts 0
deadlks 0
lktouts 0
isreads 0
iswrites 0
isrewrites 0
isdeletes 0
bufreads 51
bufwrites 0
seqscans 0
pagreads 0
pagwrites 0
total_io 51
buff_read_percent 0,00000000000000
buff_write_percent 0,00000000000000
total_bytes_scann+ 0


4/ oncheck -pe (the extent size are not 32k) !

db:'user'.table1 3224579 1000000
db:'user'.table1 4097155 2456443
db:'user'.table1 3 6553596
db:'user'.table1 3 2097152
db:'user'.table1 122881 29680
db:'user'.table1 1020633 7500
db:'user'.table1 1036325 7500
db:'user'.table1 3629330 69271
db:'user'.table1 4205673 125000
db:'user'.table1 4712817 125000
db:'user'.table1 5829961 125000
db:'user'.table1 6457105 250000
db:'user'.table1 7209249 250000
db:'user'.table1 8473537 490000
db:'user'.table1 13704829 448000
db:'user'.table1 16316665 448000
db:'user'.table1 20166649 448000

thanks in advance

------------------------------
John Smith
------------------------------

#Informix