By the end of Resolving In Place Alters, Part 3 I was able to read the raw data pages that belong to the table with pending IPAs, but I wasn't able to see if a data page had a pending IPA or not. In this installment I will show you how I solved that problem using what I know about the Informix Data Page structure and the structure of my table before and after the in place alter was performed.
Anatomy of the Informix Data Page
When Informix stores data on disk, it doesn't just write your row data. It also writes some "housekeeping" data at the beginning of the page and at the end of the page that help the engine to know what actually is stored in this data page.
The first 24 bytes of a page contain the page header with all kinds of information that you're going to have a hard time finding documentation about. The last N bytes of a data page contain a timestamp and the slot table, which you may have better luck finding some documentation on. In between the page header and page footer (slot table + timestamp) you'll find your actual data. We will need information from each of the 3 parts of the data page.
The slot table defines where each row in the page starts and how long the row is. When the engine needs to get the data for a row stored in slot #3 on a data page it goes to the slot #3 slot table entry, grabs the starting position of the data in the page and the length of the row then reads the appropriate number of bytes (defined by the length of the row in the slot table) that start at the starting position.
For a 2K page, slot entry #1 is always stored in byte offsets 2040 to 2043, slot entry #2 is always stored in byte offsets 2036 to 2039, slot entry #3 is always stored in byte offsets 2032 to 2035, and so on and so forth.
Here is (hopefully) a better way to visualize it:
Byte Offset Hex Data Purpose 2032 b3009b00 Slot #3 2036 00009700 Slot #2 2040 18009b00 Slot #1 2044 099f9e6a Page Timestamp
Extracting Row Start and Length From Slot Entry
In the above example, slot entry #3 contains the following 4 bytes of data: b3009b00. The offset into the page that this row starts at is contained in the first 2 bytes and the length of the row is stored in the last 2 bytes. So, the row in slot entry #3 begins at byte offset 0x00b3 (converted from little endian) or byte 179 of the data page and the length of the row is 0x009b or 155 bytes.
If I were ready to get the raw data that lives in this row from sysrawdsk I could because I know where the raw data starts and how much raw data to read from the starting position. However, I'm not ready to do this yet.
How Many Slot Entries Are There?
When the engine adds a row to a brand new and never before used data page the number of slot entries will be 1. When another row is added the number of slot entries will be 2. What happens when you delete a row from the page? The number of slot entries remains 2, but the starting position of the deleted row in the slot entry will be zero'd out with '0000' to indicate the row has been deleted, but there still are 2 slot entries.
Since we will be reading data from the slot table it is important that we know how many slot entries there are so we don't accidentally read garbage. This useful byte of information is stored at byte offset 8 in the data page in the page header.
See for yourself:
select hexdata, hexdata[19, 20] slots_hex, ('0x' || hexdata[19, 20])::integer slots_int from sysrawdsk where pg_chunk = 4 and pg_offset = 14893989 and offset = 0; hexdata slots_hex slots_int a543e300 0400d5b6 0d000148 5c076c00 0d 13 >oncheck -pP 4 14893989 addr stamp chksum nslots flag type frptr frcnt next prev 4:14893989 1788780297 b6d5 13 4801 DATA 1884 108 1000000 0 slot ptr len flg 1 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
In the oncheck output you can see that there are 13 slot entries in the slot table (nslots). You will also notice that only 12 slot entries are described because slot #2 contains a row that has been deleted. 12 rows on the page, but 13 slot entries in the slot table.
How To Tell If a Page Has a Pending IPA
The schema change I made that resulted in pending in place alters was adding an integer field to the end of my table. This changed my row size from 151 bytes to 155 bytes (remember, no varchars so my row length is fixed). I can use this knowledge to look at my slot table and see if the IPA has been resolved (row length of 155 in the slot table) or if the IPA is pending (row length of 151 in the slot table).
Here is the logic:
- Current slot entry we are examining is slot entry #1, number of slot entries checked is 0
- If the number of slot entries we have checked equals the number of slot entries in the page header stop looking at slot entries and do nothing with this data page
- Extract the row length and row starting position from the current slot entry
- If the row starting position is '0000' this row has been deleted. Increment the current slot entry we are looking at and go back to step 2
- If the row length is '9b00' (aka 155 bytes) then this row has already been updated with the new row format. Stop looking at slot entries and do nothing with this data page
- If the row length is '9700' (aka 151 bytes) then this row has not been updated with the new row format. Stop looking at slot entries and do something with this data page to resolve the IPA
Rinse and repeat for all data pages.
Next time I will explain how I extracted the primary key data from the raw data and used this to force an update to the data page I'm examining and resolve the IPA.
If you are travelling to IIUG2015 this year, pick up some of these luggage tags to keep someone from accidentally taking your luggage at the airport.