Thursday, January 29, 2015

Resolving In Place Alters - Part 4, The Slot Table

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.

Slot Table

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:
   hexdata[19, 20] slots_hex, 
   ('0x' || hexdata[19, 20])::integer slots_int 
   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:

  1. Current slot entry we are examining is slot entry #1, number of slot entries checked is 0
  2. 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
  3. Extract the row length and row starting position from the current slot entry
  4. 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
  5. 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
  6. 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.


  1. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more home cleaning new york 넷마블 먹튀

  2. It is the best portal to pay bills online within minutes without any complications. It avails maximum possible flexibility to users to complete their tasks in real quick time. Apart from bill payments it also avails most popular and reputed brands products to customers in affordable prices. PayTM came into existence in 2010. บาคาร่า

  3. or it might keep on hitting the series of wins for in excess of multiple times, allowing you a chance to win enormous. situs poker terpopuler

  4. This is only the data I am discovering all over the place. A debt of gratitude is in order for your website, I simply subscribe your online journal. This is a decent blog.. 에볼루션카지노

  5. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. 서포터먹튀

  6. These you will then see the most important thing, the application provides you a website a powerful important internet page: buy dedicated server germany

  7. Good morning. Introducing my homepage. This is a live casino site where you can enjoy various games such as Blackjack, Toto, Powerball, Poker, Baccarat, and Speed ​​Game. Click the link and you'll be taken directly 안전카지노사이트

  8. > 바카라사이트 > 카지노사이트 > 온라인카지노 > 온라인바카라 > 인터넷카지노 > 모바일카지노 > 슬롯사이트 > 온라인슬롯머신게임 > 호게임카지노 > 마이크로게임카지노 > 에볼루션게임카지노 > 타이산게임카지노 > SA게임카지노 > RT게임카지노 > 바카라사이트 > 카지노사이트

  9. This was a truly awesome challenge and ideally I can go to the following one. It was alot of fun and I truly had a ball..  sexy gaming

  10. i am browsing this website dailly , and get nice facts from here all the time .

  11.  I think it takes some skill to wrap-up in an open-ended way and still maintain some conviction. Fortunately, it seems like one of those fun and rewarding skills. Thanks for the comon sense nudge — and for putting a question mark in the title. That’s become an epidemic.

  12. Thank You so much for sharing this values content so that bloggers can build more backlinks opportunity. It Was really great that i had found your website throw Google.

  13. subscribers can acquire scores and real time updates from the tournament at the press of a button. They can create picks and track their progress throughout the tournament. 스포츠중계

  14. My companion suggested this blog and he was thoroughly right keep up the phenomenal work! açilis organizasyonu

  15. Awesome article! I want people to know just how good this information is in your article. It’s interesting, compelling content. Your views are much like my own concerning this subject. ฟรีเครดิต

  16. Slot machines have been around for a long time, going from one maker to many. Today, my #1 online slots game is "I Love Lucy Slots." online casinos

  17. You can easily judge by the name that there are five rotating reels which can show the five numbers or you can also say the symbols which may match each other. If you get the five symbols or numbers which may match to each other then you can think that how interesting the game can be.

  18. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated. visit this page