Informix DBA

Informix Port Delivers Unlimited Database Scalability for Popular SaaS Application

noreply@blogger.com (Andrew Ford) — Tue, 07 Feb 2012 14:53:00 +0000

Google Alerts has been depositing some interesting Informix news into my inbox lately, I thought this one was interesting. Glad to see open source applications being ported to Informix.

Informix Port Delivers Unlimited Database Scalability for Popular SaaS Application

Delivers enhanced ability to create and manage large SugarCRM databases without impacting performance even for extremely large users

London, UK (PRWEB UK) 7 February 2012
Oninit, the leading Informix services company, announces that SugarCRM, the world’s fastest-growing Customer Relationship Management (CRM) solution, has been ported onto IBM’s Informix, effectively eliminating some of the scalability concerns that can exist with this and many other Software as a Service (SaaS) applications. This brings together one of the world’s fastest and most flexible databases with a hugely popular open source application, and effectively demonstrates to application developers the suitability of Informix as a core database platform for SaaS applications.
SugarCRM, a leading open source CRM solution, is increasingly being used by larger enterprises and governments whose data management needs can increase exponentially as usage gathers momentum. With an Informix database in place SugarCRM users will be able to create and manage extremely heavily populated CRM systems without seeing a decrease in performance. Some databases which are traditionally used to build SaaS applications, or which are used as enterprise database servers to support such applications in-house, can struggle to maintain referential integrity and speeds of query response as the amount of information being stored and managed increases.
States John Baker, Managing Director of Oninit Group: “After several months of development in partnership with systems integrator i2Global, we are delighted to see SugarCRM running efficiently on Informix. With the demand for SaaS increasing, it made business sense to demonstrate Informix’s perfect suitability for the job. Delivering SugarCRM on Informix shows that it can easily power a major SaaS application. It offers unlimited growth, outstanding performance and is the ideal platform for creating and managing heavily populated databases.”
Using SugarCRM on Informix has also simplified and speeded up the data upload process. Recent tests by i2Global show that large amounts of records can be uploaded in minutes, as opposed to many hours as experienced with another often-used SQL database, representing significant user time savings.
Partnership of partners
The desire to prove Informix within large scale SaaS environments was stimulated in part by demand from China and other rapidly growing emerging markets which feature extremely large organisations supporting huge numbers of corporate users, for whom scalability is a real potential barrier.
The decision to port Sugar CRM onto Informix as the first implementation was validated by the groundbreaking partnership recently signed between Oninit and US IBM reseller, PreferredPartner, which is actively providing IBM-based information technology solutions to the Chinese market where there is high demand for efficient, extensible, and affordable CRM solutions based on world-class IBM database technologies, something that Sugar CRM on Informix uniquely provides.
Explains Craig Humphreys, Vice President of Technology, at PreferredPartner: “The opportunity in China for SugarCRM on Informix is quite substantial. With this ‘partnership of partners,’ combining our knowledge of the Chinese market, our development experience extending the capabilities of Sugar CRM, and Oninit’s network of Informix expertise such as i2Global, we have rapidly developed a solution to meet the demands of this massive emerging market. Within a short space of time, we are already conducting demonstrations in China, particularly in the financial services and distribution industries.”
SugarCRM is the leading Open Source CRM which users can deploy either in the cloud or on their own servers, and is becoming a highly popular platform for third party integration.
About Oninit
Oninit was founded in 1999 as a specialist Informix developer and consulting team, and since that time has grown into a leading Informix services company providing a range of Informix database and application development, support and training services to companies in the UK and across EMEA from its base in Cirencester, UK.
Informix is deeply embedded in the enterprise IT systems of many thousands of companies worldwide. Oninit provides the vital Informix technical support and development services which are required by these complex systems.
Oninit helps companies to develop new Informix applications as well as support, enhance, integrate and modernise applications running on older installations. It works with customers to upgrade and modernise these to take maximum advantage of the latest Informix technical enhancements and additions, ensuring a roadmap for their future IT or establishing an evolutionary path to more modern open or virtualised architectures.
More information can be found at http://www.oninitgroup.com
About PreferredPartner
Founded in 2000, PreferredPartner has a unique business model that has propelled the company into a top world-wide IBM Premier Business Partner. PreferredPartner provides full solutions in Passport Advantage and Application Specific Licensing models, as well as provisioning, implementation, and full-scale professional services. PreferredPartner provides end-to-end customer and ISV/Integrator solutions based on IBM Information Management, Tivoli, WebSphere, Lotus, and Rational, and Security software.

For more information visit http://www.PreferredPartner.com

IIUG 2012 - Registration Open and Conference Site is Live

noreply@blogger.com (Andrew Ford) — Tue, 22 Nov 2011 19:39:00 +0000

The IIUG 2012 Conference Planning Committee announced today that registration is open and the conference website is live.

Join the IIUG for the 2012 IIUG Informix Conference April 22-25, 2012 at their new sunny San Diego, CA location and be a part of the largest gathering of Informix fanatics in the world.

Take advantage of this one of a kind training and networking opportunity
Presentations by IBM Development and Support and Informix DBAs and Developers
FREE IBM Information Management Certification Testing
Reunite with old friends and colleagues
New content for all Informix versions and features
Optional Half and Full Day Tutorials to maximize your training dollars
Explore new technologies and services in the EXPO Hall
FREE Hands On Labs from IBM

The IIUG 2012 site is your one stop shop for everything you need to know about attending the conference including registration, keynote, session and tutorial information, how to become an IIUG 2012 sponsor, up to date news on latest conference details, conference hotel information, information for conference speakers and even non conference related activities in the San Diego area.

Be sure to follow IIUG 2012 on Facebook and Twitter @IIUG2012 for more information leading up to and during the conference.

Don't forget to take advantage of the IIUG 2012 Conference room rate at the San Diego Mission Valley Marriott to ensure you will be close to all the action during the conference.

Register before January 16, 2012 and receive $130 off the cost of regular registration and take advantage of the $100 IIUG Member discount. Not an IIUG member? No problem, membership is free and you can join before registering for the conference and claim your discount for a total savings of $230!

This Smart Meter Stuff is for Real

noreply@blogger.com (Andrew Ford) — Fri, 16 Sep 2011 18:56:00 +0000

Yesterday IBM hosted a Smart Meter Summit in Austin. I attended not because I do anything with Smart Meters, but because I have seen and heard the IBM speak about Informix TimeSeries and how it is a perfect fit for Smart Meter data and wanted to hear more. I wanted to find out for myself if this was for real, to find out if there is there an opportunity for Informix to grow here or if it is just Smarter Planet marketing fluff.

It is for real.

The number of homes and businesses with a Smart Meter is growing with no sign of slowing down. What I found interesting was the reason for this growth. Before attending the Summit I imagined the push for these meters was because they give you a more granular view of your power usage giving the consumer the ability to see their power consumption in near real time instead of once per month so they can switch things off if they are using too much power. I was wrong. This is definitely one benefit of Smart Meters, but the real driving force behind the push for Smart Meters is something called a Truck Roll.

A Truck Roll is the industry term for the utility company sending a human to a house (presumably in a truck) to turn on or off power to a dumb meter. With a Smart Meter, this can be controlled remotely and the need for a person to physically turn something on or off is eliminated resulting in significant savings for the utility company. My town of Austin, TX converted to Smart Meters in 2009 and eliminating these Truck Rolls has saved Austin Energy $15 Million per year.

Eventually every meter will be smart. This is great and everything, but what does this have to do with Informix?

Informix is a relational database, just like Oracle, DB2, MySQL, SQL Server, etc., but it has the ability to store time series data in a non relational format on disk and the others don't. This results in reduced storage requirements and performance gains over the relational only competition. In one study we were shown, Informix TimeSeries reduced storage requirements by 50% and performance scaled linearly as the amount data grew vs. an exponential degradation in performance experienced when using a traditional RDBMS. This last part was very interesting to me. If you are a utility company, you know that your Smart Meter data is going to grow. Without TimeSeries when your data volume doubles in size you will have to purchase four times as much hardware to keep up if using a relational RDBMS, when your data quadruples in size you will have to purchase 16 times as much hardware to keep up. That's a lot of hardware.

Let's look at another benefit of Informix TimeSeries. Say I'm a utility company and I've made the horrible mistake of implementing my Smart Meter Grid using a traditional relational database. It worked great against the 10,000 meter pilot and we're rolling out to 100,000 meters and whoopsie-daisy, performance starts to degrade. OMG, I now know I should have used Informix, but I've got all this code written for a relational model and Informix TimeSeries uses its own special API. I can't rewrite everything I have to work with TimeSeries, I better buy some more hardware and just make what I have already work, for now.

Not true. TimeSeries DOES have an API that you can take advantage of if you want ultimate supreme performance, but if you would like to sacrifice some performance (approx. 10%, but still way better than the RDBMS model) you can take advantage of the Informix Virtual Table Interface (VTI) that makes non relational TimeSeries data LOOK LIKE A RELATIONAL TABLE! You can select, insert, update and delete TimeSeries data just like it was a normal table. Moving to Informix TimeSeries from a traditional RDBMS now involves minimal code changes.

There are some real things to get excited about here even if you don't care about Smart Meters. Informix is generating some buzz within IBM because it is the only engine that can handle this kind of data. You can bet that people will see an opportunity to sell solutions to the utility companies and they will be selling Informix. Also, what happens when these companies start using Informix for their Smart Meter data and realize Informix is reliable, fast and doesn't require a hoard of DBAs to run? They just might start to look at Informix for other DB duties, is what. We should also expect to see more and more Blue Dollars spent on marketing and promoting Informix, which is a good thing. I'll be at IOD this year and if I don't see some Smart Planet/Smart Meter/Informix stuff I will be more shocked than dissapointed (I'll still be dissapointed though).

For more information about Informix TimeSeries, check out these links:

IBM News talking about Informix TimeSeries
IBM Informix TimeSeries/Smart Meter White Paper
Why Informix Rules for TimeSeries Data Management

Smart Metering Data Summit

noreply@blogger.com (Andrew Ford) — Thu, 01 Sep 2011 21:30:00 +0000

Lots of activity surrounding Informix in my fair city of Austin lately. I like.

There was the IBM Informix sponsored booth at the Online Game Developer Conference last year, the 11.70 Informix Roadshow that came to Austin in July and now I see IBM is hosting a Smart Metering Data Summit (TimeSeries) on September 15th.

IBM Smart Metering Data Summit
Thursday, 15 September, 2011 8:00 AM - 3:00 PM
IBM Briefing Center - Austin, TX
11301 Burnet Road, Building 101, Austin, TX

Register for this Free Event Today

You Stay Classy San Diego

noreply@blogger.com (Andrew Ford) — Thu, 01 Sep 2011 18:01:00 +0000

2012 IIUG Informix Conference dates and location announced today.

I love LAIP.

User Defined Aggregates

noreply@blogger.com (Andrew Ford) — Fri, 19 Aug 2011 19:25:00 +0000

Until recently I had no use for User Defined Aggregates, that was until I was ridiculed by my MySQL counterparts over the fact that Informix does not have a group_concat aggregate function.

This will not stand, ya know, this aggression will not stand, man.

A quick search of the internets produced this Stack Overflow post which shows how to create a User Defined Aggregate in Informix that will duplicate the MySQL group_concat aggregate.

Oh, if you're wondering what group_concat does, it concatenates the string fields of a table like so:

id                 value
1                  ABC
2                  DEF
1                  GHI
1                  JKL
2                  MNO
3                  PQR

select
   id,
   group_concat(value)
from
   tab
group by
   id;

1     ABC, GHI, JKL
2     DEF, MNO
3     PQR

Creating a User Defined Aggreate involves creating 4 support functions in SPL, C or Java.

Initialize - Initializes the aggregate
Iteration - Merges results of previous iterations of the aggregate with the current value
Combine - Combines results of 2 iterations when the aggregate is run in parallel
Final - Returns the result of the aggregation

Once the support functions are created you define an aggregate with the CREATE AGGREGATE statement.

Here is an example that implements a User Defined Aggregate that returns the second smallest value in a group of values using SPL.

create function second_min_init (dummy float)
returning row(min1 float, min2 float);
   return row(NULL, NULL);
end function;

-- take 3 values, row.min1, row.min2 and value and return row.min1 and row.min2 that contain
-- the 2 smallest of the 3
create function second_min_iter(result row(min1 float, min2 float), value float)
returning row(min1 float, min2 float);
   if result.min1 is null then
      return row(value, NULL);
   end if;

   if result.min2 is null then
      if value <= result.min1 then
         return row(value, result.min1);
      else
         return row(result.min1, value);
      end if;
   end if;

   if value <= result.min1 then
      return row(value, result.min1);
   elif value <= result.min2 then
      return row(result.min1, value);
   else
      return row(result.min1, result.min2);
   end if;
end function;

-- take 2 sets of min1, min2 and return the minimum and second minimum values out of the 4 values
create function second_min_combine (partial1 row(min1 float, min2 float), partial2 row(min1 float, min2 float))
returning row(min1 float, min2 float);
   define min1 float;
   define min2 float;

   let min1 = partial1.min1;
   let min2 = partial1.min2;

   if min1 is null or partial2.min1 <= min1 then
      let min2 = min1;
      let min1 = partial2.min1;

      if min2 is null or partial2.min2 <= min2 then
         let min2 = partial2.min2;
      end if;
   elif min2 is null or partial2.min1 <= min2 then
      let min2 = partial2.min1;
   end if;

   return row(min1, min2);
end function;

-- return the second smallest value
create function second_min_final(final row(min1 float, min2 float))
returning float;
   return final.min2;
end function;

create aggregate second_min with (
   init = second_min_init,
   iter = second_min_iter,
   combine = second_min_combine,
   final = second_min_final
);

create temp table second_min_test (
   field1        integer
) with no log;

insert into second_min_test values (1);
insert into second_min_test values (2);
insert into second_min_test values (3);
insert into second_min_test values (4);

select
   second_min(field1)
from
   second_min_test;

    second_min

2.000000000000

insert into second_min_test values (1);

select
   second_min(field1)
from
   second_min_test;

    second_min

1.000000000000

Notice how adding a second row with field1 of 1 causes the second_min aggregate to return 1. This is because the smallest value is 1 and the second smallest value is also 1. If you would like the aggregate to return the second smallest distinct value you can change less than or equal comparisons in the support functions to less than.

Austin Informix 11.70 Roadshow - Day 2

noreply@blogger.com (Andrew Ford) — Thu, 14 Jul 2011 14:39:00 +0000

The second day was just as informative as the first. I am bummed we are done but I needed to get back to work anyway.

Some interesting things from Day 2:

create | drop if [not] exists

Long time coming, IMO. Gives you the ability to write SQL like the following

create database if not exists db with log;

create table if not exists tab1 (
   field1 integer,
   field2 char(2)
);

drop table if exists tab2;

Index Extent Sizing

You can now specify the first and next extent sizes for your indexes instead of relying on the engine to guestimate what your index extent sizes should be based on index key size and table size.

Deep Embed of DataBlades

A major barrier to entry for people playing around with DataBlades is you had to install them before you could use them. In 11.7 all DataBlades that are included at no additional charge (TimeSeries, Basic Text Search, Spatial, Node and others) are pre-install but not active until you attempt to use the functionality. Nice.

Enhancements to dbschema

Added ability to omit table owner from dbschema output, but more importantly dbschema can now be used to produce the onspaces, onlog, etc. steps necessary to recreate an instance.

Backup to the Amazon Cloud

I admit, I initially put this in the "Cool, but whatever. I have no use for this feature." Now that you can store up to 5TB in Amazon S3, the feature is simple to use and I can apply a backup or restore filter to compress the backup to minimize storage costs and network traffic it definitely deserves more consideration. Only works with ontape, get it to work with onbar and we are definitely in business.

Cloning an Instance

Now here is a feature I will be using. In fact, as soon as I complete this blog post I will be using the feature to initialize HDR in my test environment. Instance cloning is just what it sounds like, make an exact copy of Instance A over here and call it Instance B. If you use HDR this can be used to replace the backup Primary/restore to Secondary/start HDR steps and it should be much faster.

ER Enhancement CDR_LOG_LAG_ACTION

If you are running ER and your snoopy thread is running behind you can get into a log wrap condition. Instead of blocking the engine in DDRBLOCK, you can tell the engine to do something else. My favorite other action is logstage. Log stage will compress and temporarily copy the logical log the snoopy thread needs to a directory so the snoopy thread can read the logs it needs and the engine can overwrite the logs it needs to and life goes on. Nice.

High Availability Enhancements - Transaction Survival and DDL on Secondary

If you are executing a DML against a updatable secondary and the primary goes down in 11.7 the transaction will not fail immediately. The engine will retry for a configurable number of seconds assuming a new primary will be put into place and life will go on.

You can now execute create table, create index, drop table, etc. commands on a secondary. Still no update statistics on a secondary available...

Automatic Storage Provisioning

This was the focus of Day 2, complete with a Hands on Lab. Neat stuff, but may make the old timey Informix DBA scratch their heads a little bit. I fall into this category and the idea of defining a pool of unused disk space for the engine to dynamically allocate chunks (or extend existing chunks) from goes against everything I believe in. I'm not saying this feature doesn't have value, I'm just saying I am a control freak and I want to tell the engine when and where to add chunks.

I will admit that the ability to extend an existing chunk to increase the size of a dbspace vs. adding a new chunk was very cool.

Austin Informix 11.70 Roadshow - Day 1

noreply@blogger.com (Andrew Ford) — Tue, 12 Jul 2011 21:50:00 +0000

We're just wrapping up day 1 of the 2 day Informix 11.70 Roadshow in Austin, TX and I am glad I was able to finally attend one of these things. I hope another will come to my area soon. We've got a full room of people from all over the great state of Texas wanting to hear more about new 11.70 features and get hands on experience with Flexible Grid and Automatic Storage Provisioning.

Carlton Doe is a great speaker and as the agenda promised he discussed Informix Marketing and the plans for the future, talked about the new 11.70 features that improve performance and ease of administration, got a look at the Informix Warehouse Accelerator and then went into an intense presentation over Informix Flexible Grid. We finished up the day with a Flexible Grid Hands On Lab.

I look forward to tomorrow and the topics of Application Development Enhancements, Backup to the Cloud, Instance Administration Features and Automatic Storage Provisioning Hands on Lab.

If one of these are coming to your area I highly recommend you attend if the class hasn't filled up yet.

I really want to get back to posting some technical stuff soon and I will, I promise. I've got a suggestion to write about Fragmentation/Partitioning and I want to do a post about User Defined Aggregates to duplicate the group_concat() functionality in MySQL.

North American Informix 11.7 Roadshow Schedule

noreply@blogger.com (Andrew Ford) — Wed, 22 Jun 2011 03:20:00 +0000

Happy to see IBM scheduled 4 Informix Roadshows in my part of the world, especially the one for Austin, TX which will be walking distance from my house.

I've never been able to attend a Roadshow because it wasn't feasible for me to travel to San Jose or NYC but I've already reserved my spot for July 12 and 13. I hope to see some new faces and get a chance to talk about the greatness of this product with people getting to know Informix for the first time.

I hope there is one in your area that you can attend!

Montreal, Quebec, Canada

July 7 & 8 9 am - 5 pm

IBM 1360, boulevard Reni-Livesque Ouest Montrial, Quibec H3G 2W6 Room: 7th Floor, Room 629

Registration link: https://www.ibm.com/events/swgeer/swgeer06.nsf/signin?openform&seminar=kwospit8hyvqz&lang=en

Austin, TX

July 12 & 13 9 am - 5 pm NOTE: Registration closes July 7!!

IBM 11301 Burnet Road, Bldg. 101 Austin, Tx 78758-3493 Walnut Room, 02-2J008

Registration link: https://www.ibm.com/events/swgeer/swgeer06.nsf/signin?openform&seminar=kwospit8hzqcn&lang=en

Seattle, WA

July 26 & 27 9 am - 5 pm

IBM 1200 5th Ave Seattle, WA 98101 Room 9-9020

Registration link: https://www.ibm.com/events/swgeer/swgeer06.nsf/signin?openform&seminar=kwospit8hzqnm&lang=en

Minneapolis, MN

August 2 & 3 9 am - 5 pm

IBM 650 Third Avenue South, Minneapolis, MN 55402 Lake Superior Room A 04-429

Registration link: https://www.ibm.com/events/swgeer/swgeer06.nsf/signin?openform&seminar=kwospit8hzqbd&lang=en

Here are the details of the FREE 2 day technical deep dive and hands on labs

Day 1:

Introductions and logistics
Informix Marketing overview
New general database and Data Warehouse features
Informix Flexible Grid and Hands-on lab

Day 2:

Application development enhancements
Backup to Cloud
New Informix instance administration features
Informix Automatic Storage Provisioning and Hands-on lab

IBM will be providing a complimentary continental breakfast and lunch each day.

The Merge Statement

noreply@blogger.com (Andrew Ford) — Fri, 10 Jun 2011 02:00:00 +0000

Introduced in 11.50.xC5 and enhanced in 11.50.xC6 the Merge statement is a nice addition that I really didn't pay much attention to until I learned how it works in preparation for the 11.70 certification exam.

The Merge statement gives you the ability to perform the following SPL in pure SQL:

foreach with hold
   select 
      id, 
      price
   into 
      L_id, 
      L_price
   from 
      products

   update
      warehouse_products
   set
      price = L_price
   where
      id = L_id;

   if dbinfo('sqlca.sqlerrd2') = 0 then
      insert into warehouse_products (id, price) values (L_id, L_price);
   end if;
end foreach;

Here is the Merge statement accomplishing the same thing with only SQL:

merge into warehouse_products w using products p on
   w.id = p.id
when matched then 
   update set w.price = p.price
when not matched then 
   insert (w.id, w.price) values (p.id, p.price);

Check out the Informix Information Center for more information about deleting on a match, using external tables as the source table and using collection-derived tables as the source table.

Meter Data Management Pitfalls (and How to Avoid Them)

noreply@blogger.com (Andrew Ford) — Thu, 02 Jun 2011 20:16:00 +0000

Smart Grid News.Com is hosting a free "Lessons from the Real World" webinar featuring leading experts from IBM talking about how the underlying database solution you choose is critical to Meter Data Management (MDM) projects and the importance of getting this decision right from the beginning.

IBM, Smart Meters, Databases......are you thinking what I'm thinking? The IBM experts should talk extensively (if not exclusively) about Informix TimeSeries in this webinar.

Sign up for the FREE webinar to hear what IBM leading experts have to say about Informix TimeSeries and its roll in the MDM market or be there represent Informix if they don't.

Smart Grid News.Com Webinar
Meter Data Management Pitfalls (and How to Avoid Them)
Wednesday, June 8, 2011 1:30 PM Eastern (10:30 AM Pacific)
Register for FREE

2011 IIUG Informix Conference - Day 4

noreply@blogger.com (Andrew Ford) — Sat, 21 May 2011 00:52:00 +0000

Dr. Arvind Krishna Keynote

Mr. Rob Thomas Keynote

Swapping Stuart stories.

This only works in the movies, BTW.

David just realized there is a full day of Customer Advisory Council meetings tomorrow and he can't go home yet.

2011 IIUG Informix Conference - Day 3

noreply@blogger.com (Andrew Ford) — Sat, 21 May 2011 00:28:00 +0000

Informix fanatics enjoying some lunch on day 3

I had to do a little work in between conference activities

Prime Rib - Ho-Hooooooo!

What, you didn't have lobster at the Other database conference?

Khem, Val, Tom and Lester at the Advanced DataTools Fastest DBA contest. I was winning at this point, only to be beaten by Neil and Dmitri later

The hotel brought this guy in to calculate The Kernoal's bar tab.

Casino night. Poker, Blackjack, Craps and Roulette. Rob Jane, The Kernoal and I were winning at this point.

2011 IIUG Informix Conference - Day 2

noreply@blogger.com (Andrew Ford) — Tue, 17 May 2011 14:59:00 +0000

Hung out with Watson in the morning...

...and totally pwned him

Took advantage of the free testing and picked up my 11.7 certification

"ARRRRRGH, it's drivin' me nuts!"

Who is Dr. David Ferrucci, the leader of the IBM Watson/Jeopardy! project?

Game night at IIUG. Thanks to Chuck at All Points Amusement for making this happen.

You'll shoot your eye out

2011 IIUG Informix Conference - Day 1

noreply@blogger.com (Andrew Ford) — Mon, 16 May 2011 00:44:00 +0000

Sunday morning registration

One of the six Sunday AM Tutorials about to get started

Cindy and her son Joe getting the Exhibit Hall ready for the conference

All aboard, it is time to eat and drink at the Welcome Reception

Tostadas, not the ones from Taco Bell

Hand made and cooked to order empanadas

Gyros, falafel and other deliciousness from the Mediterranean

IBM Informix Genero Adds New Platform Support

noreply@blogger.com (Andrew Ford) — Thu, 12 May 2011 02:13:00 +0000

If you were excited about the IBM Informix Genero announcement, but were sad because only Linux and Windows were supported then you'll be happy to see IBM Informix Genero now supports the following platforms:

Linux PowerPC
AIX
HP
Solaris
Sun
Mac

The official announcement can be found here.

The announcement also adds a new pricing metric for Limited Use (LU) licensing when use with Informix Growth Edition purchased with Limited Use licensing.

Just a few days until the 2011 IIUG Informix conference, hope to see you there!

IBM Redbook: Informix Flexible Grid

noreply@blogger.com (Andrew Ford) — Thu, 28 Apr 2011 15:41:00 +0000

Draft version of Informix Flexible Grid: Extending Data Availability made available at redbooks.ibm.com.

Table of Contents

Chapter 1. Overview
Chapter 2. OpenAdmin Tool
Chapter 3. Configuring server for Flexible Grid and Enterprise Replication environments
Chapter 4. The Flexible Grid
Chapter 5. Administering Enterprise Replication
Chapter 6. Monitoring and troubleshooting Enterprise Replication and Flexible Grid
Chapter 7. MACH11 cluster
Chapter 8. High availability data replication
Chapter 9. RSS database server
Chapter 10. Shared Disk Secondary servers
Chapter 11. Connection management in an Informix environment
Chapter 12. Informix in cloud and virtualization environments
Chapter 13. Migration
Chapter 14. Using ifxclone

I am currently reading this to help prepare for the free 11.7 Certification Exam I'm going to take in 3 weeks at the IIUG Conference.

Page 1: When did HDR become Heuristic Data Replication and not High-Availability Data Replication?

Chef Diego on the 2011 IIUG Informix Conference Menu

noreply@blogger.com (Andrew Ford) — Wed, 27 Apr 2011 04:57:00 +0000

Close your eyes and think of the food served at the last conference you attended.

Did you relive nightmares of imitation gruel and grade D meat? You probably did. This is because for most conferences the food is an after thought, an added expense, something that just needs to be dealt with because people have to eat.

Not true for the IIUG Informix conference. We have a chef and he has a super cool name, Chef Diego. He also has a vision for the food he will prepare for this year's conference. Did the dude that cooked your last conference meal have a vision or did he have a budget?

During our three day cruise, I will take you to four different destinations: the Mediterranean, the Caribbean, the Mexican Riviera and Alaska before returning you to Kansas City. Welcome aboard and bon voyage!

- Chef Diego

The last thing you think about when deciding if you should attend a conference is the food. That is probably the right approach, which is why the CPC has put together a conference rich in technical content. The great food and open bar (no tickets ;) are just icing on the cake.

If you are not registered for the conference, it isn't too late to register and you can still take advantage of $100 off for IIUG members. Book now before airline prices increase! You can attend this 3 day event for less than the cost of one IBM Informix training class!

Innovator-C: Put Your Money Where Your Mouth Is

noreply@blogger.com (Andrew Ford) — Mon, 25 Apr 2011 21:57:00 +0000

I've been talking a pretty big game about Innovator-C and how you can use it in production even though some Growth Edition and Ultimate Edition features are not available and the amount of memory and CPUs you can use are limited.

2 weeks ago that talk stopped being just talk and became reality as we turned up our first Innovator-C HDR pair of production database engines.

The server ain't big (1 Quad Core CPU, 2GB SHMTOTAL and RAID10 over 4 disks), but it cranks out 4 million transactions and 500,000 new session requests per day (and the box is bored and typically 99% idle).

This database was a perfect fit for Innovator-C. The data set isn't so large that we need to worry about partitioning data and it is all OLTP so not having PDQ isn't show stopper. Recovery Time Objective would have been nice, but using the CKPTINTVL isn't a big deal. The only thing I'm really sad about not having is Direct I/O for cooked chunks, but whatever. I can live without it (just don't understand why this is disabled in Innovator-C).

Innovator-C can be used in production for free which makes for a pretty good ROI and a low cost per transaction for us.

$0.00 / 4 million transactions / day is a pretty good number and is sure to put a smile on the face of your company's bean counter(s).

After the migration we noticed performance improvements in the app that now runs on a dedicated engine and in the apps that run on the old server because the load is now shared by 2 servers.

As you might expect with Informix, the migration was painless. During our most recent meeting my boss announced to the team, "You may have not noticed, but we migrated the API databases to a new set of servers two weeks ago and there haven't been any issues. Is that right? None? That is surprising, you would expect something would come up with a migration this big. Good job guys."

2011 IIUG Informix Conference Schedule Now Available Online

noreply@blogger.com (Andrew Ford) — Thu, 14 Apr 2011 01:52:00 +0000

In about a month the 2011 IIUG Informix Conference and its 95 hours of Technical Sessions, 19 hours of Hands on Labs, 36 hours of Tutorials, 3 Keynotes, 3 days of Exhibit Hall, 3 Breakfasts, 6 Refreshment Breaks, 3 Lunches, 3 Evening Events and over 25 hours of Certification Exams will come together in a beautifully choreographed ballet of speakers, volunteers, attendees, hotel staff, laptops, projectors, food, drinks and friends.

To help you navigate the largest gathering of Informix fanatics and plan how you will best take advantage of all the conference has to offer the Conference Planning Committee has posted the Conference Schedule and Sunday Tutorial Schedule online for your reading pleasure.

UPDATE: Mobile device friendly link: iiug.org/pda

Upgrading an HDR Environment from 11.50.FC3 to 11.50.FC8W2

noreply@blogger.com (Andrew Ford) — Fri, 08 Apr 2011 18:22:00 +0000

I'm about to upgrade one of our HDR pairs from a relatively old version of 11.50 to the most recent drop to resolve a minor bug. Since an upgrade is something you may be interested in doing some day, I thought it would be a good idea to document my steps here.

Normally an upgrade from xC3 to xC8 would be trivial and low risk since we're only upgrading the minor versions, but with the addition of Compression in xC4 any upgrade from xC3 or lower must be treated as a major upgrade which includes a reinitialization of HDR.

I want to minimize downtime by doing as much of the work as I can outside of the maintenance window and I want to minimize my risk by testing the following migration and reversion steps on my test systems to ensure there are no gotchas and choreograph my upgrade in a way that always gives me a quick and safe way to abort mission and get production up and running if something goes wrong during the upgrade.

Lucky for you and me, the bulk of the upgrade work can be accomplished during normal business hours with no impact or downtime. Not only does this minimize the amount of late night/early morning maintenance window work you will have to do, it will minimize the mistakes that can happen when you are doing work during sleepy time.

A lot of my upgrade steps are based on INFORMIXDIR being a link to an actual directory, you did remember to do that didn't you? If not, you should invest the time and energy required to make INFORMIXDIR a link, you will be glad you did.

Get yourself some binaries

There are two types Informix tar balls, there is the bundle which includes everything (engine, CSDK, iConnect, etc.) which is typically downloaded from Passport Advantage or the Innovator-C download page and there are Fix Packs which are typically downloaded from Fix Central and only include the engine.

The bundle is installed via ids_install and the engine is installed via installserver if that helps.

If upgrading with a Fix Pack, you will need the bundle used for your current installation. For this upgrade I am using a Fix Pack for 11.50.FC8W2 and have the 11.50.FC3 bundle from my initial installation.

Install the new engine - Perform the following steps on the HDR Primary and HDR Secondary

Let us skip some of the boring stuff and say that I have downloaded my Fix Pack, located my initial bundle and unpacked each tar-ball into their own temporary folder.

I like to install Informix into a version specific directory, for example /opt/informix-ids.11.50.FC8W2, and use the soft link /opt/informix as my INFORMIXDIR. This allows me to pre install everything and then simply shutdown the engine, redirect the link and restart the engine when it is time to upgrade.

I will first install the FC3 bundle into the 11.50.FC8W2 directory by temporarily setting INFORMIXDIR to /opt/informix-ids.11.50.FC8W2 and running ids_install as root.

root:/tmp/ids-11.50.FC3> mkdir /opt/informix-ids.11.50.FC8W2
root:/tmp/ids-11.50.FC3> chown informix:informix /opt/informix-ids.11.50.FC8W2
root:/tmp/ids-11.50.FC3> export INFORMIXDIR=/opt/informix-ids.11.50.FC8W2
root:/tmp/ids-11.50.FC3> ./ids_install

After this is complete I will install the FC8W2 Fix Pack on top of the FC3 bundle as root.

root:/tmp/ids-11.50.FC8W2> export INFORMIXDIR=/opt/informix-ids.11.50.FC8W2
root:/tmp/ids-11.50.FC8W2> ./installserver

Copy configuration files from current install directory to new install directory - HDR Primary and Secondary

We will need to copy some files from the current INFORMIXDIR to our soon to be new INFORMIXDIR. This is what I need to copy, your needs may vary slightly.

# definitely will need the ONCONFIG file
cp /opt/informix/etc/$ONCONFIG /opt/informix-ids.11.50.FC8W2/etc/

# and we'll need this file
cp /opt/informix/etc/oncfg_* /opt/informix-ids.11.50.FC8W2/etc/

# I setup my HDR trusts in the Informix hosts.equiv file
cp /opt/informix/etc/hosts.equiv /opt/informix-ids.11.50.FC8W2/etc

# if you are using a storage manager for backups, don't forget this guy
cp /opt/informix/etc/sm_versions /opt/informix-ids.11.50.FC8W2/etc

# if your sqlhosts file lives somewhere in INFORMIXDIR, don't forget to copy it
cp /opt/informix/etc/sqlhosts /opt/informix-ids.11.50.FC8W2/etc

# and finally the alarmprogram if you have made any changes
cp /opt/informix/etc/alarmprogram.sh /opt/informix-ids.11.50.FC8W2/etc

Just one more thing to do to make sure we are protected

Take a Level 0 backup, it is better to have it and not need it than to need it and not have it.

We are now ready to upgrade

We will break HDR by converting the Primary and Secondary nodes to standard engines and we will shut down the Secondary node and not touch it until we are 100% satisfied the upgrade was successful. This gives us a quick way to back out of our upgrade attempt if something goes wrong. If there is a problem we can restart the Secondary (which is now a writable standard engine) and direct the clients to this node while we figure out what went wrong.

Convert the Primary to a standard engine and shutdown

informix@primary> onmode -d standard
informix@primary> onmode -ky

Convert the Secondary to a standard engine and shutdown

informix@secondary> onmode -d standard
informix@secondary> onmode -ky

Relink /opt/informix on the Primary

informix@primary> rm /opt/informix
informix@primary> ln -s /opt/informix-ids.11.50.FC8W2 /opt/informix

Start the upgraded engine on the Primary

informix@primary> oninit -v

If everything goes as planned the upgrade will complete successfully and your primary engine will only be down for a few minutes and your downtime will be minimized.

You can use this time to verify that everything with the upgrade has gone as planned. Check the online.logs for any strange errors and watch your applications for any unexpected behaviour. When you are confident that the upgrade was successful you should take another Level 0 backup, upgrade the Secondary and rebuild HDR.

Relink /opt/informix on the Secondary and reinitialize HDR

informix@secondary> rm /opt/informix
informix@secondary> ln -s /opt/informix-ids.11.50.FC8W2 /opt/informix

Follow the instructions for initializing HDR from a previous post.

Oh Noes! Something went wrong. REVERT!

So you thought everything was hunky dory with the upgrade but after reinitializing HDR you realize you need to revert back to your previous version. Gross.

Thankfully Informix has a utility to help with this, the -b switch of the onmode utility.

> onmode -b --
You may only revert IBM Informix Dynamic Server to
IBM Informix-OnLine Version 7.31.UC1 or later:
    Use 'onmode -b 7.3'  to revert to 7.3x
    Use 'onmode -b 9.2'  to revert to 9.2x
    Use 'onmode -b 9.3'  to revert to 9.3x
    Use 'onmode -b 9.4'  to revert to 9.4x
    Use 'onmode -b 10.00' to revert to 10.00x
    Use 'onmode -b 11.10' to revert to 11.10x
    Use 'onmode -b 11.50.xC1' to revert to 11.50.xC1
    Use 'onmode -b 11.50.xC2' to revert to 11.50.xC2
    Use 'onmode -b 11.50.xC3' to revert to 11.50.xC3
    Use 'onmode -b 11.50.xC4' to revert to 11.50.xC4
    Use 'onmode -b 11.50.xC5' to revert to 11.50.xC5
For more information refer to Section II of IBM Informix Migration guide

Convert the Primary to a standard engine

informix@primary> onmode -d standard

Convert the Secondary to a standard engine and shutdown

informix@secondary> onmode -d standard
informix@secondary> onmode -ky

Execute the onmode -b command and revert disk structures back to 11.50.xC3

Informix will be offline after a successful onmode -b

informix@primary> onmode -b 11.50.xC3

Relink /opt/informix to the old INFORMIXDIR

informix@primary> rm /opt/informix
informix@primary> ln -s /opt/informix-ids-11.50.FC3 /opt/informix

Start the engine in Quiescent mode and verify everything is Ok

informix@primary> oninit -s

Bring the engine into online mode

informix@primary> onmode -m

Finally you will want to complete a Level 0 backup and reinitialize HDR.

2011 IIUG Informix Conference - Only 10 Days Left for Early Bird Registration

noreply@blogger.com (Andrew Ford) — Fri, 21 Jan 2011 15:13:00 +0000

Register for the 2011 IIUG Informix Conference on or before January 31, 2011 and save $125

Early Bird registration ends in 10 days, register now before Regular Registration starts and the price increases. Also don't forget to use your free IIUG membership to receive an additional $100 off registration pricing.

The speaker selections have been made, the speakers have been notified and the speakers are diligently working on many new presentations that cover Informix 11.7 topics like Flexible Grid and Automatic Storage Provisioning.

There are also many new topics that cover all Informix versions if you haven't made the leap to the latest and greatest yet. We've got Performance Tuning, Troubleshooting and Developer sessions by the IBMers who actually implement the engine code and from DBAs and Developers who use the engine in the field to give you the most well rounded Informix training that money can buy for a fraction of the cost.

I'm also happy to report that the Conference Planning Committee has added more Free Half Day Hands on Labs sessions this year and added more Sunday Half and Full Day Tutorials to help you maximize your training budget.

After all of this Informix training you'll be ready to take advantage of the FREE IBM CERTIFICATION TESTING. Almost any Information Management test will be available for you to take, not just the Informix ones. Take as many as you like as many times as you need to, it don't cost nuthin'. The free certification testing is something the IIUG has been able to provide for many years now and I feel it adds tremendous value to the conference. When I first started attending these conferences, the free certification testing alone was enough to convince my boss and the bean counters at my company that the cost of the conference was worth it. Not sure if you've looked at the Average Joe cost for one of these tests, they ain't cheap and if you don't take advantage of this you are seriously missing out.

With all the Tutorials, Hands on Labs, Technical Sessions and Certification Testing you'll be seeing and doing you're going to need some food in your belly to give you the required energy to make it through the day. I can tell you that you won't go hungry at an IIUG Informix Conference. You'll get 3 meals a day and we're talking real food here, not your typical conference food by any stretch of the imagination.

Finally, in the evenings you can relax with friends and have cold drink or ten and get ready to do it all over again the next day. No drink coupons here, the complimentary beer and wine is usually all you can handle, bro.

Table Level Restore - Pretty Useful Stuff

noreply@blogger.com (Andrew Ford) — Wed, 15 Dec 2010 05:01:00 +0000

"Hello Andrew. Yeah, well, um we kind of ran an update with a bad where clause and messed up some of our data. Can you fix just the rows we updated?"

"Oh yeah, sure. We've got the backups and the logs so we'll be OK."

"How long will it take? This is production and it is kind of a big deal"

"You're in luck. Informix has a feature called Table Level Restore that allows me to extract only the rows we want from a backup in a few minutes. Without this feature we would have to perform a full restore to a test engine, which can take hours, and then extract the rows from the restored instance."

"Oh thank heavens we run Informix and not some other database engine that doesn't have this feature."

Table-Level Restore

You can use Table-Level Restore (TLR) to extract an entire table or a subset of rows in a table from your backups. The feature has been around since version 10, is easy to use and can come in really handy if you find yourself in the situation I described above.

To performa a TLR you'll need:

A Level 0 archive taken via onbar or ontape (I'll use onbar in the examples)
A schema command file
archecker (included with the engine)
Optionally, logical log backups

Physical Restore

If you do not have the logical logs backed up or only want to restore the data to the time of the Level 0 archive then you'll be performing a physical restore.

Logical Restore

If you have the logical logs and want to restore a table to a point in time then you'll be performing a logical restore. This is great because if someone does make a mistake, you can return the data to the state it was immediately before the mistake was made. A logical restore is performed by TLR first completing a physical restore and then traversing the logical logs converting the log records to SQL statements that are applied to the data from the physical restore. A logical restore can not process changes that exist in the current logical log. If you need to restore to a point in time that is contained in the current log you can shift to the next logical log via onmode -l.

Schema Command File

This is where the magic happens. In this file you will define the source table you want to restore from, the target table you want to restore to and what point in time to restore to.

The schema command file contains:

SET statements that can enable certain features of TLR
a DATABASE statement to declare what database we're pulling data from
CREATE TABLE statements to define the structure of the table we're restoring from and the table we're restoring to
an INSERT INTO statement that defines which table is the source, which table is the target and optionally what rows we want to restore
a RESTORE statement that defines if this is a physical or logical restore and what point in time to restore to

The optional SET statement can change the commit interval from the default of 1000 to set the number of rows that are inserted during the physical restore between commits. The SET statement can also be used to change the working dbspace from the default of rootdbs.

set commit to 10000;
set workspace to dbspace001;

The DATABASE statement defines which database in the archive we are working with.

database database_name;

The CREATE TABLE statement defines the structure of the source table and the dbspaces and partitions it lives in and defines the structure of the target table. The source table definition must be identical to how the table exists in the archive. The target table definition only needs to contain the columns you want to restore (I guess I forgot to mention that in addition to only restoring select rows you can also restore only select columns if you want.) If the target table does not exist, it will be created during the restore. If the target table does exist, the table will be appended to.

-- source table lives in 2 partitions in 2 dbspaces
create table source_table_name (
   column1           integer,
   column2           float,
   column3           char(17)
) fragment by expression
   partition p1 (column1 < 50) in dbspace001,
   partition p2 (column1 >= 50) in dbspace002;

-- target table
-- will be created if does not exists and appended to if it does exist
-- in this example the table only has column1 and column2 because those 
-- are the only columns to be restored
create table target_table_name (
   column1            integer,
   column2            float
) in dbspace001;

The INSERT INTO statement will define the source table (it will be the table we select from) and the target table (the table we are inserting into). This statement can optionally define the columns we want to restore (in the select projection list) and the rows we want to restore (via the where clause).

insert into target_table_name  -- identifies target table
select 
   column1,                    -- identifies the columns we
   column2                     -- want to restore
from
   source_table_name           -- identifies the source table
where
   column1 between 30 and 100; -- identifies the rows to restore

The RESTORE statement will identify if this is a physical restore or a logical restore and when to restore to.

-- both a physical and a logical restore are included in this example
-- pick one or the other, don't include 2 restore commands in the same schema command file

-- physical restore includes the with no log clause
restore to current with no log;

-- logical restore to a specific time
restore to "2010-12-10 04:32:00";

Invoking TLR with the archecker Command

archecker used to be just for verifying that your backups weren't corrupt, it has now been extended to perform TLR.

To start TLR using onbar and your schema command file:

> archecker -bdvs -X -f your_schema.cmd

IBM Informix Dynamic Server Version 11.50.FC6W4
Program Name:   archecker
Version:        8.0
Released:       2010-04-26 22:13:21
CSDK:           IBM Informix CSDK Version 3.50
ESQL:           IBM Informix-ESQL Version 3.50.FC4
Compiled:       04/26/10 22:13  on Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006

AC_STORAGE               /tmp
AC_MSGPATH               /tmp/ac_msg.log
AC_VERBOSE               on
AC_TAPEBLOCK             62 KB
AC_IXBAR                 /opt/informix/etc/ixbar.0
Dropping old log control tables
Extracting table database_name:source_table_name into database_name:target_table_name
........................................
........................................
........................................
.........................
Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: st_switch:scuvr_dnis_restore produced 171721 rows.
Creating log control tables
Staging Log 18013
Staging Log 18014
Switching to log 18014
Staging Log 18015
Switching to log 18015
Staging Log 18016
Switching to log 18016
Staging Log 18017
Switching to log 18017

Logically recovered database_name:target_table_name Inserted 120 Deleted 79 Updated 38

After the TLR completes the target_table_name table will contain the rows from source_table_name with a column1 value between 30 and 100. You can now use the data in the target table to correct the data in the source table.

Promoting the 2011 IIUG Informix Conference

noreply@blogger.com (Andrew Ford) — Sun, 12 Dec 2010 05:41:00 +0000

Bear with me for one more "Non Technical Pimp the 2011 IIUG Informix Conference" post. After this we'll get back to the good stuff. I had to take advantage of Table Level Restore last night and will be writing about that experience soon.

One of the things the IIUG Conference Planning Committee is doing to promote Informix and the conference this year is to run a Google Ads campaign. We're running text ads world wide that are displayed when you search for Informix related terms and on web sites containing content relevant to Informix. We're also running banner ads across the globe that appear on the web sites we think Informix users frequent.

This is a very inexpensive way for us to promote the conference, which we like since our main objective is to provide a great conference to Informix users while keeping the registration fee very affordable (you can register for the 3 day conference for as low as $525 between now and January 31!). The jury is still out as to how effective this form of advertising will be for us, but it is worth every penny to see Informix appear in an advertisement when I'm online.

I have two favors to ask:

If you come across one of the text ads, please refrain from clicking it. I know the temptation to click on an ad about Informix will be strong, as this is something you haven't seen a lot of. It is like me asking you to not take a picture of Bigfoot if he walked right up to your campfire and started making S'mores for everyone. We are charged by the click for text ads and we're trying to reach the most people who are not aware of the conference for the least amount of advertising dollars. If you need to click on something, the following link will take you to the landing page for the text ads: 2011 IIUG Informix Conference. Click on the banner ads all you want, we're charged based on the number of times they're displayed, just don't sit there and hit the refresh button all day.

If you have a web site or blog and have 728x90, 468x60, 250x250 or 200x200 pixels sitting there just displaying white space and want to donate it to one of the following 2011 IIUG Informix Conference ads, well that would be just awesome and greatly appreciated. The following images are being hosted on the IIUG server and you can display them on your web site or blog by adding the HTML text below. If you would like me to send you the files so you can host them on your own server, please email me at andrew@informix-dba.com and I'll gladly send them to you.

Large Banner - 728x90 - 50KB

Large Banner HTML

<a href="http://www.iiug.org/conf/2011/iiug/welcome.php" target="_blank"><img alt="2011 IIUG Informix Conference" border="0" height="90" src="http://www.iiug.org/conf/2011/iiug/images/iiug_large_banner.gif" width="768"/></a>

Small Banner - 468x60 - 36KB

Small Banner HTML

<a href="http://www.iiug.org/conf/2011/iiug/welcome.php" target="_blank"><img alt="2011 IIUG Informix Conference" border="0" height="60" src="http://www.iiug.org/conf/2011/iiug/images/iiug_small_banner.gif" width="498"/></a>

Large Square - 250x250 - 40KB

Large Square HTML

<a href="http://www.iiug.org/conf/2011/iiug/welcome.php" target="_blank"><img alt="2011 IIUG Informix Conference" border="0" height="250" src="http://www.iiug.org/conf/2011/iiug/images/iiug_large_square.gif" width="250"/></a>

Small Square - 200x200 - 35KB

Small Square HTML

<a href="http://www.iiug.org/conf/2011/iiug/welcome.php" target="_blank"><img alt="2011 IIUG Informix Conference" border="0" height="200" src="http://www.iiug.org/conf/2011/iiug/images/iiug_small_square.gif" width="200"/></a>

2011 IIUG CPC - Day 2, Matrigrid Continues to Take Shape

noreply@blogger.com (Andrew Ford) — Sat, 04 Dec 2010 15:55:00 +0000

Making the Final Decisions on Sessions and Scheduling them to Avoid Conflicts


Pooped