[Info-ingres] W_DM5066_ROW_SPANS_PAGE

Fri May 28 08:09:11 UTC 2021

When upgrading from previous releases where Systemtables is on 2K-pages you can run a Sysmod
To move all Systemtables to 8K-pages (needed as well if you don’t want errors in Errlog.log 😊).

sysmod my_database -page_size=8192

Best regards

Henrik Georg Sørensen

Database Administration & Development

[kmd logo] FAK Development Team DK

Lautrupparken 42, DK-2750 Ballerup
E-mail hgo at kmd.dk  Web www.kmd.dk<http://www.kmd.dk/>
Direct +4544607626 Mobile +4541398500

Fra: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> På vegne af Paul White
Sendt: 28. maj 2021 03:52
Til: Ingres and related product discussion forum <info-ingres at lists.planetingres.org>
Emne: Re: [Info-ingres] W_DM5066_ROW_SPANS_PAGE

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

[cid:image003.png at 01D753A7.33D945C0]

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<mailto: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.2

https://docs.actian.com/ingres/10.2/index.html#page/DatabaseAdmin/Maximum_Row_Size_Per_Page_Size.htm<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.actian.com%2Fingres%2F10.2%2Findex.html%23page%2FDatabaseAdmin%2FMaximum_Row_Size_Per_Page_Size.htm&data=04%7C01%7CHGO%40kmd.dk%7C7ea6a925d3374f7ae7b608d9217b468a%7C1e2ad6d6274f43e889efd36d65bb83b5%7C1%7C0%7C637577635620657449%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=6tpuYjK773I9pML1Rlj09hYDGhOIjr4fFHZfMPtwYEE%3D&reserved=0>

V11.x

https://docs.actian.com/ingres/11.0/index.html#page/DatabaseAdmin%2FCalculate_Space_Requirements_When_Rows_Span_Page.htm%23ww1027421<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.actian.com%2Fingres%2F11.0%2Findex.html%23page%2FDatabaseAdmin%252FCalculate_Space_Requirements_When_Rows_Span_Page.htm%2523ww1027421&data=04%7C01%7CHGO%40kmd.dk%7C7ea6a925d3374f7ae7b608d9217b468a%7C1e2ad6d6274f43e889efd36d65bb83b5%7C1%7C0%7C637577635620667402%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=kAqwWoZwI3sDoajTOvD9j0hSCKUnE9i1%2FIG71aw6ErE%3D&reserved=0>

[cid:image006.png at 01D753A9.8069BA30]

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<mailto:paul.white at shift7solutions.com.au>
Web www.shift7solutions.com.au<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.shift7solutions.com.au%2F&data=04%7C01%7CHGO%40kmd.dk%7C7ea6a925d3374f7ae7b608d9217b468a%7C1e2ad6d6274f43e889efd36d65bb83b5%7C1%7C0%7C637577635620667402%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=sUnka4%2Bj6sNq%2FkR0GxaM8SxKOwusYCsll0rzW1DfBwk%3D&reserved=0>
Mail PO Box 418 Pomona 4568

Internal - KMD A/S

Beskyttelse af dine personlige oplysninger er vigtig for os. Her finder du KMD’s Privatlivspolitik<http://www.kmd.dk/Privatlivspolitik>, der fortæller, hvordan vi behandler oplysninger om dig.

Protection of your personal data is important to us. Here you can read KMD’s Privacy Policy<http://www.kmd.net/Privacy-Policy> outlining how we process your personal data.

Vi gør opmærksom på, at denne e-mail kan indeholde fortrolig information. Hvis du ved en fejltagelse modtager e-mailen, beder vi dig venligst informere afsender om fejlen ved at bruge svarfunktionen. Samtidig beder vi dig slette e-mailen i dit system uden at videresende eller kopiere den. Selvom e-mailen og ethvert vedhæftet bilag efter vores overbevisning er fri for virus og andre fejl, som kan påvirke computeren eller it-systemet, hvori den modtages og læses, åbnes den på modtagerens eget ansvar. Vi påtager os ikke noget ansvar for tab og skade, som er opstået i forbindelse med at modtage og bruge e-mailen.

Please note that this message may contain confidential information. If you have received this message by mistake, please inform the sender of the mistake by sending a reply, then delete the message from your system without making, distributing or retaining any copies of it. Although we believe that the message and any attachments are free from viruses and other errors that might affect the computer or it-system where it is received and read, the recipient opens the message at his or her own risk. We assume no responsibility for any loss or damage arising from the receipt or use of this message.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/eb4869e3/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 17029 bytes
Desc: image003.png
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/eb4869e3/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 896 bytes
Desc: image001.jpg
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/eb4869e3/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 225685 bytes
Desc: image006.png
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20210528/eb4869e3/attachment-0003.png>