Archive | MashupNews RSS feed for this section

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 le…

Leave a comment Continue Reading →

Resolving In Place Alters – Part 3

In Resolving In Place Alters – Part 2 I decided that I want to try to identify only the pages with a pending IPA and only “fix” those data pages in an attempt to speed up the resolving of IPAs and limit the work load added to the system when doing so.

To do this, I will need to read the actual data pages stored on disk and use information in the page header and slot table to determine if a page needs to be fixed. More on this in the next blog, today I just want to talk about how to read in the raw data pages.

As far as I know, I only have 2 good options. Bypass the engine and open and read the data in the chunk directly or use the sysmaster:sysrawdsk table. I tried both options and did not notice a performance difference either way, so I decided to use sysrawdsk simply because it made things easier since I can use SQL to query and get the raw data pages.

sysmaster:sysrawdsk looks like this (for those concerned about the Internal Use Only warning, just don’t do any of this outside and you’ll be fine):

    create table informix.sysrawdsk             { Internal Use Only             }
(
pg_chunk integer, { physical page address - chunk }
pg_offset integer, { physical page address - offset}
offset smallint, { bytes into page }
loc char(23), { location representation }
hexdata char(40), { 16 bytes hexdumped from offset}
ascdata char(16) { 16 bytes ascii-dumped }
);
create unique index informix.sysrawdskidx on sysrawdsk (pg_chunk, pg_offset, offset);

Each row in the table represents 16 bytes of raw data. For example:

select 
he...

Leave a comment Continue Reading →

Resolving In Place Alters – Part 2

In Part 1 I talked about how I knew that the prescribed method of resolving the pending in place alters in my large table by simply issuing a single large dummy update statement to update each row won’t work for large tables. There are just too many problems with long transactions, locks and excessive I/O that can trash the engine.

I decided the first thing I would try would be to update every row in the table, but do it in multiple transactions to avoid long transactions and holding on to a lot of locks. Nothing too interesting, pretty standard stuff really. The logic looks like this:

set isolation dirty read;
select {+full (large_table)} primary_key from large_table;

updated = 0
for each row:
update large_table set primary_key = primary_key where prima...

Leave a comment Continue Reading →

Informix Newsletter Dezember 2014

(german content)

Neue Ausgabe des IBM/Informix Newsletters

Im Newsletter-Archiv befindet sich die neueste Ausgabe des INFORMIX-Newsletters.

Inhaltsverzeichnis der Ausgabe:

  • Aktuelles
  • TechTipp: Informix und NoSQL (5): NoSQL-Zugriff auf SQL-Daten
  • TechTipp: Reorganisieren von Indexen und Primary Keys
  • TechTipp: dbimport [-nv]
  • TechTipp: dbimport [-D]
  • TechTipp: Environment „NOVALIDATE“
  • TechTipp: dbaccess mit Shell-Commands
  • Anmeldung / Abmeldung / Anmerkung
  • Die Autoren dieser Ausgabe

Informix Newsletter Dezember 2014

Leave a comment Continue Reading →

I’m Back! Did You Think I was Gone Forever?

I Sure Did Miss You

Truth be told, I just haven’t had much to write about until now. I haven’t been doing much of anything interesting with Informix. Sure, I’ve been doing the day to day Informix DBA stuff, but nothing blog worthy. Mostly I’ve been working on some software development, data mining and business optimization projects for my company. But that all changed this week.

The Catalyst

I want to upgrade to v12.1, but I’m a big fat chicken and I’ve got a pending in place alter on my most importantest of important tables. The one that holds account information and balances for over 1 billion accounts and the manual …

Leave a comment Continue Reading →

China is standardizing all new database applications on Informix!

Here’s the “world” shaking news I promised. IBM and China have come to an agreement whereby IBM will share the code for Informix with a Chinese software house, GBASE, who will modify its security code to conform to Chinese government standards. In turn China will be building all future database projects using Informix.

The agreement allows for sharing innovations to the Informix code made by either party to be shared by both. If anyone wanted proof that Informix is here to stay and is NEVER going away, this is it. China is the world’s second largest market and probably the fastest growing technology market. The government of China does not want any software or hardware used for government projects or enterprises that it does not control, fearing Western spying through the…

Leave a comment Continue Reading →

News from the Informix World

And I do mean world. Well it’s been a long time since my last post. Over six months. I just got back from the IBM Insight Conference in Las Vegas NV (formerly the IBM Information On Demand Conference) and there is some big news. IBM announced their new vertical cloud development stacks called BlueMix. There is a BlueMix for timeseries data, BlueMix for mobile apps, …

Leave a comment Continue Reading →

Insight 2014: Wednesday October 29

The general session started with an example of context computing and an interview with Captain Phillips.

All that was pretty exciting but what stole the show is the announcement of the partnership
between IBM and Twitter for analytics.

Then I…

Leave a comment Continue Reading →

Insight 2014: Tuesday October 28

Another full day.

It started at 7:00 with a breakfast meeting and was followed by a conference call.
I then went to the conference bookstore for a book signing activity and moved on to a customer lunch.
As I mentioned in other blog entries, my new…

Leave a comment Continue Reading →

Insight 2014: Monday October 27

After walking by 3 different Starbucks, I arrived at the conference breakfast hall.
I thought I would have a quiet breakfast by myself when I saw Bruce Brown, a big data partner expert.
Soon after, I was sitting others joined us: They were long time…

Leave a comment Continue Reading →