11.70 data page slot table
Andrew Ford dumped a 2K data page after an in-place ALTER that grew rows from 151 to 155 bytes, and was puzzled that the page still showed 13 slot entries (one with a zeroed offset and the old 151 length) when only 12 rows now fit. Madison Pruet explained slot 1/that entry is simply empty, and that slot tables aren't compacted because rowids are referenced by indexes and long-row pointers. Andrew's own testing confirmed the behaviour: on the first update the displaced row is moved to a new page and its slot offset zeroed (indexes updated accordingly), and only a later update rewrites the page with the new 155-byte row length to complete the IPA. He also derived an adjusted rows-per-page formula for altered tables.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Can anyone tell me what I'm looking at and why? 11.70 2K data page, row size is fixed at 155 bytes after in place alter (12 rows per page), previous row size was 151 bytes (13 rows per page), page has been updated after in place alter. last 64 bytes of the 2K data page: 00000000 00000000 c1069b00 26069b00 8b059b00 f0049b00 55049b00 ba039b00 1f039b00 84029b00 e9019b00 4e019b00 b3009b00 00009b00 00009700 bd165df6 Last 4 bytes of the page is the checksum and I would expect the previous 4 bytes to be slot #1 in the slot table entry, but it looks like it is storing the pre IPA size of the row (0x0097 = 151). Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slot #13, first row in the page at slot #2 has been deleted) I guess my question is, when the page was rewritten to finalize the row alter why wasn't the slot table rewritten to be just 12 slots vs. keeping 13 slots and storing what appears to be the previous page size in slot #1. Thanks, Andrew
It looks to me like slot 1 and slot 2 are empty.... From: "Andrew Ford" <andrew@informix-dba.com> To: ids@iiug.org Date: 11/19/2014 01:23 PM Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.org Can anyone tell me what I'm looking at and why? 11.70 2K data page, row size is fixed at 155 bytes after in place alter= (12 rows per page), previous row size was 151 bytes (13 rows per page), pag= e has been updated after in place alter. last 64 bytes of the 2K data page: 00000000 00000000 c1069b00 26069b00 8b059b00 f0049b00 55049b00 ba039b00 1f039b00 84029b00 e9019b00 4e019b00 b3009b00 00009b00 00009700 bd165df6 Last 4 bytes of the page is the checksum and I would expect the previou= s 4 bytes to be slot #1 in the slot table entry, but it looks like it is storing the pre IPA size of the row (0x0097 =3D 151). Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= t #13, first row in the page at slot #2 has been deleted) I guess my question is, when the page was rewritten to finalize the row= alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= ng 13 slots and storing what appears to be the previous page size in slot #1.= Thanks, Andrew ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Oh yes --- about not compressing the slots as part of the page update..= . If we did that, then all of the links to this page would be invalid (i.e. = long rows, indexes, etc...) From: "Andrew Ford" <andrew@informix-dba.com> To: ids@iiug.org Date: 11/19/2014 01:23 PM Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.org Can anyone tell me what I'm looking at and why? 11.70 2K data page, row size is fixed at 155 bytes after in place alter= (12 rows per page), previous row size was 151 bytes (13 rows per page), pag= e has been updated after in place alter. last 64 bytes of the 2K data page: 00000000 00000000 c1069b00 26069b00 8b059b00 f0049b00 55049b00 ba039b00 1f039b00 84029b00 e9019b00 4e019b00 b3009b00 00009b00 00009700 bd165df6 Last 4 bytes of the page is the checksum and I would expect the previou= s 4 bytes to be slot #1 in the slot table entry, but it looks like it is storing the pre IPA size of the row (0x0097 =3D 151). Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= t #13, first row in the page at slot #2 has been deleted) I guess my question is, when the page was rewritten to finalize the row= alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= ng 13 slots and storing what appears to be the previous page size in slot #1.= Thanks, Andrew ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Unless I'm confused about this page being updated (or how slots and data
pages work), then why would there be 13 slots if only 12 rows fit in a page.
Also, why would slot #3 indicate the row starts at byte 179 (0x00b3 = 179)
when you'd expect the deleted row in slot #1 to start @ byte 24 right after
the header and the row in slot #2 to start at byte 179 (row size 155 + 24 =
179).
Here is the last 64 bytes of a page that does not have a row delete and has
been updated after the IPA
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 18009b00 00009700 daff72f6
and some oncheck -pP
addr stamp chksum nslots flag type frptr frcnt next
prev
4:55 -160235558 99b 13 6801 DATA 1884 108
1000000 0
slot ptr len flg
2 24 155 0
3 179 155 0
4 334 155 0
5 489 155 0
6 644 155 0
7 799 155 0
8 954 155 0
9 1109 155 0
10 1264 155 0
11 1419 155 0
12 1574 155 0
13 1729 155 0
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 1:47 PM
To: ids@iiug.org
Subject: Re: 11.70 data page slot table [34192]
It looks to me like slot 1 and slot 2 are empty....
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 01:23 PM
Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.org
Can anyone tell me what I'm looking at and why?
11.70 2K data page, row size is fixed at 155 bytes after in place alter=
(12
rows per page), previous row size was 151 bytes (13 rows per page), pag= e
has been updated after in place alter.
last 64 bytes of the 2K data page:
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 00009b00 00009700 bd165df6
Last 4 bytes of the page is the checksum and I would expect the previou= s 4
bytes to be slot #1 in the slot table entry, but it looks like it is storing
the pre IPA size of the row (0x0097 =3D 151).
Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= t
#13, first row in the page at slot #2 has been deleted)
I guess my question is, when the page was rewritten to finalize the row=
alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= ng
13
slots and storing what appears to be the previous page size in slot #1.=
Thanks,
Andrew
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Ah, that makes sense then. Does this mean that I need to adjust my "how many rows fit in a page" calculation to take this extra 4 byte slot (or slots) when altering a table? For a 2K page on an unaltered table it is: trunc((2048 bytes - 24 bytes in the header - 4 byte timestamp) / (bytes in a row + 4 byte slot entry)) For a 2K page on an altered table it is: trunk((2048 bytes - 24 bytes in the header - 4 byte timestamp - (4 bytes * original number of slot entries)) / new row size bytes) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Wednesday, November 19, 2014 1:49 PM To: ids@iiug.org Subject: Re: 11.70 data page slot table [34193] Oh yes --- about not compressing the slots as part of the page update..= .. If we did that, then all of the links to this page would be invalid (i.e. = long rows, indexes, etc...) From: "Andrew Ford" <andrew@informix-dba.com> To: ids@iiug.org Date: 11/19/2014 01:23 PM Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.org Can anyone tell me what I'm looking at and why? 11.70 2K data page, row size is fixed at 155 bytes after in place alter= (12 rows per page), previous row size was 151 bytes (13 rows per page), pag= e has been updated after in place alter. last 64 bytes of the 2K data page: 00000000 00000000 c1069b00 26069b00 8b059b00 f0049b00 55049b00 ba039b00 1f039b00 84029b00 e9019b00 4e019b00 b3009b00 00009b00 00009700 bd165df6 Last 4 bytes of the page is the checksum and I would expect the previou= s 4 bytes to be slot #1 in the slot table entry, but it looks like it is storing the pre IPA size of the row (0x0097 =3D 151). Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= t #13, first row in the page at slot #2 has been deleted) I guess my question is, when the page was rewritten to finalize the row= alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= ng 13 slots and storing what appears to be the previous page size in slot #1.= Thanks, Andrew ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. = **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
OK, I think I've figured this out and Madison's comment about not rewriting
the slot table tweaked my memory. Here's what I think happened Andrew:
- When the table was altered adding 4 bytes to every row, 13 rows no longer
fit on the page, but this page was either full or had a deleted row in slot
#1 (most likely).
- If slot 1 was deleted then it's slot entry's offset was zero'd out
indicating an empty slot but the length byte was left alone. Done.
- If slot 1 had a row in it, then that row would have been moved to another
page to free up space for expanding the other 12 rows on the page. The
slow entry would have been replaced with a forwarding pointer. But then the
offset would be non-zero (pointing to the forwarding pointer's location on
the page) and the length would be negative IB. I don't know, though, what
would happen if later the migrate row was subsequently deleted to the
original slot entry. Maybe it's offset just gets zero'd as you found it.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Wed, Nov 19, 2014 at 3:02 PM, Andrew Ford <andrew@informix-dba.com>
wrote:
> Unless I'm confused about this page being updated (or how slots and data
> pages work), then why would there be 13 slots if only 12 rows fit in a
> page.
> Also, why would slot #3 indicate the row starts at byte 179 (0x00b3 = 179)
> when you'd expect the deleted row in slot #1 to start @ byte 24 right after
> the header and the row in slot #2 to start at byte 179 (row size 155 + 24 =
> 179).
>
> Here is the last 64 bytes of a page that does not have a row delete and has
> been updated after the IPA
>
> 00000000 00000000 c1069b00 26069b00
> 8b059b00 f0049b00 55049b00 ba039b00
> 1f039b00 84029b00 e9019b00 4e019b00
> b3009b00 18009b00 00009700 daff72f6
>
> and some oncheck -pP
>
> addr stamp chksum nslots flag type frptr frcnt next
> prev
> 4:55 -160235558 99b 13 6801 DATA 1884 108
> 1000000 0
>
> slot ptr len flg
>
> 2 24 155 0
>
> 3 179 155 0
>
> 4 334 155 0
>
> 5 489 155 0
>
> 6 644 155 0
>
> 7 799 155 0
>
> 8 954 155 0
>
> 9 1109 155 0
>
> 10 1264 155 0
>
> 11 1419 155 0
>
> 12 1574 155 0
>
> 13 1729 155 0
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Madison Pruet
> Sent: Wednesday, November 19, 2014 1:47 PM
> To: ids@iiug.org
> Subject: Re: 11.70 data page slot table [34192]
>
> It looks to me like slot 1 and slot 2 are empty....
>
> From: "Andrew Ford" <andrew@informix-dba.com>
> To: ids@iiug.org
> Date: 11/19/2014 01:23 PM
> Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.org
>
> Can anyone tell me what I'm looking at and why?
>
> 11.70 2K data page, row size is fixed at 155 bytes after in place alter=
> (12
>
> rows per page), previous row size was 151 bytes (13 rows per page), pag= e
> has been updated after in place alter.
>
> last 64 bytes of the 2K data page:
>
> 00000000 00000000 c1069b00 26069b00
>
> 8b059b00 f0049b00 55049b00 ba039b00
>
> 1f039b00 84029b00 e9019b00 4e019b00
>
> b3009b00 00009b00 00009700 bd165df6
>
> Last 4 bytes of the page is the checksum and I would expect the previou= s
> 4
> bytes to be slot #1 in the slot table entry, but it looks like it is
> storing
> the pre IPA size of the row (0x0097 =3D 151).
>
> Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= t
> #13, first row in the page at slot #2 has been deleted)
>
> I guess my question is, when the page was rewritten to finalize the row=
>
> alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= ng
> 13
> slots and storing what appears to be the previous page size in slot #1.=
>
> Thanks,
>
> Andrew
>
> ***********************************************************************=
> ********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> =
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c35482806c1805083bd666
0x18 + 0x9b =3D 0xb3
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 02:03 PM
Subject: RE: 11.70 data page slot table [34194]
Sent by: ids-bounces@iiug.org
Unless I'm confused about this page being updated (or how slots and dat=
a
pages work), then why would there be 13 slots if only 12 rows fit in a
page.
Also, why would slot #3 indicate the row starts at byte 179 (0x00b3 =3D=
179)
when you'd expect the deleted row in slot #1 to start @ byte 24 right a=
fter
the header and the row in slot #2 to start at byte 179 (row size 155 + =
24 =3D
179).
Here is the last 64 bytes of a page that does not have a row delete and=
has
been updated after the IPA
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 18009b00 00009700 daff72f6
and some oncheck -pP
addr stamp chksum nslots flag type frptr frcnt next
prev
4:55 -160235558 99b 13 6801 DATA 1884 108
1000000 0
slot ptr len flg
2 24 155 0
3 179 155 0
4 334 155 0
5 489 155 0
6 644 155 0
7 799 155 0
8 954 155 0
9 1109 155 0
10 1264 155 0
11 1419 155 0
12 1574 155 0
13 1729 155 0
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 1:47 PM
To: ids@iiug.org
Subject: Re: 11.70 data page slot table [34192]
It looks to me like slot 1 and slot 2 are empty....
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 01:23 PM
Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.o=
rg
Can anyone tell me what I'm looking at and why?
11.70 2K data page, row size is fixed at 155 bytes after in place alter=
=3D
(12
rows per page), previous row size was 151 bytes (13 rows per page), pag=
=3D e
has been updated after in place alter.
last 64 bytes of the 2K data page:
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 00009b00 00009700 bd165df6
Last 4 bytes of the page is the checksum and I would expect the previou=
=3D s
4
bytes to be slot #1 in the slot table entry, but it looks like it is
storing
the pre IPA size of the row (0x0097 =3D3D 151).
Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo=
=3D t
#13, first row in the page at slot #2 has been deleted)
I guess my question is, when the page was rewritten to finalize the row=
=3D
alter why wasn't the slot table rewritten to be just 12 slots vs. keepi=
=3D ng
13
slots and storing what appears to be the previous page size in slot #1.=
=3D
Thanks,
Andrew
***********************************************************************=
=3D
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=3D
***********************************************************************=
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Did some additional testing if anyone is interested.
After an IPA, when a page on the older version is found and we have to move
a row to a new page because all rows no longer fit on a page we zero out the
slot table entry for the row being updated and move that row to a new page
and keep the original page unchanged (except for the change to the slot
table). If you update the original page again, only then is the page is
rewritten to reflect the new row length and complete the IPA.
Another question. Will Informix update indexes, etc. to reflect just the one
row being moved to a new page? It looks like it since there doesn't seem to
be any forwarding info stored in the slot table for that row, just the first
2 bytes are zero'd out.
step 1
create table ipa_test (f1 char(151));
insert into ipa_test values ("1");
insert into ipa_test values ("2");
insert into ipa_test values ("3");
insert into ipa_test values ("4");
insert into ipa_test values ("5");
insert into ipa_test values ("6");
insert into ipa_test values ("7");
insert into ipa_test values ("8");
insert into ipa_test values ("9");
insert into ipa_test values ("10");
insert into ipa_test values ("11");
insert into ipa_test values ("12");
insert into ipa_test values ("13");
last 64 bytes of page: 13 slots
20202000 00000000 2c079700 95069700
fe059700 67059700 d0049700 39049700
a2039700 0b039700 74029700 dd019700
46019700 af009700 18009700 7a2973f6
step 2
alter table ipa_test add (f2 char(4));
update ipa_test set f1 = f1 where f1 = "7";
last 64 bytes of original page: 13 slots, slot 7 is zero'd out indicating
the row is not there. Otherwise the page is unchanged (i.e. row len in slot
table is still 151) and the original page is still flagged as the old
version, i.e. an in place alter is still pending.
20202000 00000000 2c079700 95069700
fe059700 67059700 d0049700 39049700
00009700 0b039700 74029700 dd019700
46019700 af009700 18009700 3a2a73f6
row "7" was moved to a new page with a row len of 155 in the slot table and
is flagged as the new version, i.e. not IPA pending
00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000
00000000 00000000 18009b00 3e2a73f6
step 3
update ipa_test set f1 = f1 where f1 = "6";
last 64 bytes of original page: 13 slots, slot 7 still zero'd out but row
len in slot table changed to 155 and page flagged as the new version
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
00009700 1f039b00 84029b00 e9019b00
4e019b00 b3009b00 18009b00 db2c73f6
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 2:17 PM
To: ids@iiug.org
Subject: RE: 11.70 data page slot table [34198]
0x18 + 0x9b =3D 0xb3
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 02:03 PM
Subject: RE: 11.70 data page slot table [34194] Sent by:
ids-bounces@iiug.org
Unless I'm confused about this page being updated (or how slots and dat= a
pages work), then why would there be 13 slots if only 12 rows fit in a page.
Also, why would slot #3 indicate the row starts at byte 179 (0x00b3 =3D=
179)
when you'd expect the deleted row in slot #1 to start @ byte 24 right a=
fter
the header and the row in slot #2 to start at byte 179 (row size 155 + =
24 =3D
179).
Here is the last 64 bytes of a page that does not have a row delete and= has
been updated after the IPA
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 18009b00 00009700 daff72f6
and some oncheck -pP
addr stamp chksum nslots flag type frptr frcnt next prev
4:55 -160235558 99b 13 6801 DATA 1884 108
1000000 0
slot ptr len flg
2 24 155 0
3 179 155 0
4 334 155 0
5 489 155 0
6 644 155 0
7 799 155 0
8 954 155 0
9 1109 155 0
10 1264 155 0
11 1419 155 0
12 1574 155 0
13 1729 155 0
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 1:47 PM
To: ids@iiug.org
Subject: Re: 11.70 data page slot table [34192]
It looks to me like slot 1 and slot 2 are empty....
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 01:23 PM
Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.o= rg
Can anyone tell me what I'm looking at and why?
11.70 2K data page, row size is fixed at 155 bytes after in place alter= =3D
(12
rows per page), previous row size was 151 bytes (13 rows per page), pag= =3D
e has been updated after in place alter.
last 64 bytes of the 2K data page:
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 00009b00 00009700 bd165df6
Last 4 bytes of the page is the checksum and I would expect the previou= =3D
s
4
bytes to be slot #1 in the slot table entry, but it looks like it is storing
the pre IPA size of the row (0x0097 =3D3D 151).
Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo= =3D
t #13, first row in the page at slot #2 has been deleted)
I guess my question is, when the page was rewritten to finalize the row= =3D
alter why wasn't the slot table rewritten to be just 12 slots vs. keepi= =3D
ng
13
slots and storing what appears to be the previous page size in slot #1.= =3D
Thanks,
Andrew
***********************************************************************=
=3D
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=3D
***********************************************************************=
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
If the row has to move, then we update the index. But that is why we d=
on't
want to move rows unless we have to.
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 03:36 PM
Subject: RE: 11.70 data page slot table [34200]
Sent by: ids-bounces@iiug.org
Did some additional testing if anyone is interested.
After an IPA, when a page on the older version is found and we have to =
move
a row to a new page because all rows no longer fit on a page we zero ou=
t
the
slot table entry for the row being updated and move that row to a new p=
age
and keep the original page unchanged (except for the change to the slot=
table). If you update the original page again, only then is the page is=
rewritten to reflect the new row length and complete the IPA.
Another question. Will Informix update indexes, etc. to reflect just th=
e
one
row being moved to a new page? It looks like it since there doesn't see=
m to
be any forwarding info stored in the slot table for that row, just the
first
2 bytes are zero'd out.
step 1
create table ipa_test (f1 char(151));
insert into ipa_test values ("1");
insert into ipa_test values ("2");
insert into ipa_test values ("3");
insert into ipa_test values ("4");
insert into ipa_test values ("5");
insert into ipa_test values ("6");
insert into ipa_test values ("7");
insert into ipa_test values ("8");
insert into ipa_test values ("9");
insert into ipa_test values ("10");
insert into ipa_test values ("11");
insert into ipa_test values ("12");
insert into ipa_test values ("13");
last 64 bytes of page: 13 slots
20202000 00000000 2c079700 95069700
fe059700 67059700 d0049700 39049700
a2039700 0b039700 74029700 dd019700
46019700 af009700 18009700 7a2973f6
step 2
alter table ipa_test add (f2 char(4));
update ipa_test set f1 =3D f1 where f1 =3D "7";
last 64 bytes of original page: 13 slots, slot 7 is zero'd out indicati=
ng
the row is not there. Otherwise the page is unchanged (i.e. row len in =
slot
table is still 151) and the original page is still flagged as the old
version, i.e. an in place alter is still pending.
20202000 00000000 2c079700 95069700
fe059700 67059700 d0049700 39049700
00009700 0b039700 74029700 dd019700
46019700 af009700 18009700 3a2a73f6
row "7" was moved to a new page with a row len of 155 in the slot table=
and
is flagged as the new version, i.e. not IPA pending
00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000
00000000 00000000 18009b00 3e2a73f6
step 3
update ipa_test set f1 =3D f1 where f1 =3D "6";
last 64 bytes of original page: 13 slots, slot 7 still zero'd out but r=
ow
len in slot table changed to 155 and page flagged as the new version
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
00009700 1f039b00 84029b00 e9019b00
4e019b00 b3009b00 18009b00 db2c73f6
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 2:17 PM
To: ids@iiug.org
Subject: RE: 11.70 data page slot table [34198]
0x18 + 0x9b =3D3D 0xb3
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 02:03 PM
Subject: RE: 11.70 data page slot table [34194] Sent by:
ids-bounces@iiug.org
Unless I'm confused about this page being updated (or how slots and dat=
=3D a
pages work), then why would there be 13 slots if only 12 rows fit in a
page.
Also, why would slot #3 indicate the row starts at byte 179 (0x00b3 =3D=
3D=3D
179)
when you'd expect the deleted row in slot #1 to start @ byte 24 right a=
=3D
fter
the header and the row in slot #2 to start at byte 179 (row size 155 + =
=3D
24 =3D3D
179).
Here is the last 64 bytes of a page that does not have a row delete and=
=3D
has
been updated after the IPA
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 18009b00 00009700 daff72f6
and some oncheck -pP
addr stamp chksum nslots flag type frptr frcnt next prev
4:55 -160235558 99b 13 6801 DATA 1884 108
1000000 0
slot ptr len flg
2 24 155 0
3 179 155 0
4 334 155 0
5 489 155 0
6 644 155 0
7 799 155 0
8 954 155 0
9 1109 155 0
10 1264 155 0
11 1419 155 0
12 1574 155 0
13 1729 155 0
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Madison Pruet
Sent: Wednesday, November 19, 2014 1:47 PM
To: ids@iiug.org
Subject: Re: 11.70 data page slot table [34192]
It looks to me like slot 1 and slot 2 are empty....
From: "Andrew Ford" <andrew@informix-dba.com>
To: ids@iiug.org
Date: 11/19/2014 01:23 PM
Subject: 11.70 data page slot table [34190] Sent by: ids-bounces@iiug.o=
=3D rg
Can anyone tell me what I'm looking at and why?
11.70 2K data page, row size is fixed at 155 bytes after in place alter=
=3D
=3D3D
(12
rows per page), previous row size was 151 bytes (13 rows per page), pag=
=3D
=3D3D
e has been updated after in place alter.
last 64 bytes of the 2K data page:
00000000 00000000 c1069b00 26069b00
8b059b00 f0049b00 55049b00 ba039b00
1f039b00 84029b00 e9019b00 4e019b00
b3009b00 00009b00 00009700 bd165df6
Last 4 bytes of the page is the checksum and I would expect the previou=
=3D
=3D3D
s
4
bytes to be slot #1 in the slot table entry, but it looks like it is
storing
the pre IPA size of the row (0x0097 =3D3D3D 151).
Preceding 12 sets of 4 bytes are the slot table entries (slot #2 to slo=
=3D
=3D3D
t #13, first row in the page at slot #2 has been deleted)
I guess my question is, when the page was rewritten to finalize the row=
=3D
=3D3D
alter why wasn't the slot table rewritten to be just 12 slots vs. keepi=
=3D
=3D3D
ng
13
slots and storing what appears to be the previous page size in slot #1.=
=3D
=3D3D
Thanks,
Andrew
***********************************************************************=
=3D
=3D3D
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=3D3D
***********************************************************************=
=3D
*****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
***********************************************************************=
=3D
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=3D
*************************************