[Info-ingres] W_DM5066_ROW_SPANS_PAGE

[Paul White]

The Ingres 11.2 documentation has been updated. The maximum row size has
reduced further.

[image: image.png]

Check out Upgrade.pdf for recommendations about modifying tables to adjust
page size.

Here is an SQL to search for tables with a row width near the boundary

select t.system_use, t.number_pages, t.num_rows, t.row_width,
t.table_pagesize, t.table_name, t.table_owner, t.table_type, f.file_name,
t.storage_structure, t.overflow_pages, t.create_date
from iitables t
join iifile_info f on t.table_name = f.table_name and t.table_owner =
f.owner_name
where
(t.table_pagesize = 2048 and t.row_width >= 1928 ) or
(t.table_pagesize = 4096 and t.row_width >= 3976 ) or
(t.table_pagesize = 8192 and t.row_width >= 8072 ) or
(t.table_pagesize = 16384 and t.row_width >= 16264 ) or
(t.table_pagesize = 32768 and t.row_width >= 32648 ) or
(t.table_pagesize = 65536 and t.row_width >= 65416 )
order by system_use, t.table_owner, t.table_name

Paul





On Mon, Mar 22, 2021 at 3:13 PM <shift7solutions at gmail.com> wrote:

> Hi all,
>
> Can anyone say when the maximum row size per page has changed?
>
> I have several btree tables with warning messages in verifydb like this:
>
> verifydb -mreport -sdbname ctrust -otable newtable -n
> W_DM5066_ROW_SPANS_PAGE Table row size 2004 spans page size 2048.
>
> My normal approach is to increase the page size to 4K or 8K to suit MVCC requirements,
> so I am seeking to understand why this particular table is being reported as a warning when the row
> width is within the specified limits here:
>
> V10.2https://docs.actian.com/ingres/10.2/index.html#page/DatabaseAdmin/Maximum_Row_Size_Per_Page_Size.htm
>
> V11.xhttps://docs.actian.com/ingres/11.0/index.html#page/DatabaseAdmin%2FCalculate_Space_Requirements_When_Rows_Span_Page.htm%23ww1027421
>
>
>
>
>
> On further investigation, I found these to be the maximum row sizes per page before page spanning occurs.
>
>
> Page Old New
> 2K 2008 1932
> 4K 3988 3980
> 8K 8084 8076
> 16K 16276 16268
> 32K 32660 32652
> 64K 65428 65420
>
> Incidently, I found two Ingres catalog tables with 2K storage and row size > 1932: ii_encoded_forms and ii_encodings. I am pretty sure they are not used in any of the customer databases I'm investigating. It seems that upgradedb (V10/V11) has missed these.  In newly created databases, these tables are 8K.
>
>
> Paul
> &
>
>

-- 
Paul White
& Shift Seven Solutions
Mob 0414 681 799
Ph 07 5448 2137
Em paul.white at shift7solutions.com.au
Web www.shift7solutions.com.au
Mail PO Box 418 Pomona 4568
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/e863ebba/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ohljahfdinckgffj.png
Type: image/png
Size: 54101 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/e863ebba/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 17029 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/e863ebba/attachment-0003.png>