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 tells me I should probably resolve all of my IPA's before performing an in place upgrade. Could I take a chance and upgrade the engine without following the manual's advice? Sure. Have I done it before without problems? Yeah, but I'm not proud of it and they weren't on our most precious table, so this time I'm going to follow the rules.

Part 1 - What to Expect and What I Knew Wouldn't Work

This will be a 89 part series (give or take a few) on how I accomplished this task without any downtime, without killing performance, without locking up the table and most importantly without updating every gosh darned one of those 1 billion rows like the manuals suggest you do. (Ok, full disclosure, I'm still working on the last little bit, but I think I've got this nut cracked.)

I have submitted a proposal for IIUG2015 on this topic, so if you don't feel like reading you can just wait for the movie. You will have to buy a ticket and they will be on sale soon. If you too would like to present something at IIUG2015 and receive a complimentary pass, there is still time to submit a proposal. This is the 20th anniversary of the first Informix conference in Chicago and it will be fun and educational as always.

What I knew wouldn't work - The manuals seem to assume we are all operating with tables with only a handful of rows and their simple solution to resolving IPA's is to just dummy update every row in the table.

update your_tiny_table set your_field = your_field where 1 = 1;

Well, we all know that will cause havoc on a system if you're updating a table with any substantial size. Locks, long transactions, evil I/O, oh my. There has to be a better way and there is. Next time I'll show you the first thing I tried. (Spoiler alert, it didn't work either.)