Saturday, July 31, 2010

Increasing Buffers and Reducing I/O with Informix

If you're following along with my posts on installing, initializing, adding space and creating databases and tables in Informix then you're ready to start doing some basic performance tuning. You can improve performance dramatically by making some simple changes to the ONCONFIG and bouncing the engine. The first and most important change to make is to increase Buffers, this is where Informix will be caching all of the data you are reading in from disk. More data cached means less I/O and less I/O has to mean better performance and since I/O will most likely be your current bottleneck, reducing I/O should result in the most performance gain.

How Informix Caches Data

Thursday, July 29, 2010

CWTL - Informix Post End of Service Support Options and Upgrade Best Practices

The next Informix Chat With The Labs has been scheduled for Thursday August 26, 11:30 AM Eastern Time.

Join in and listen to Albert Martin (Director, WW Data Management Support), Charles Gonsalves (Sr. Manager, WW Informix Support), Ron Privett (Sr. Engineer, Americas Down Systems and Diagnostics Team) and Chris Fender (Program Director, WW IM S&S Strateger and Sales) talk about your support options after your Informix version reaches End of Service and the best practices for upgrading to a new version of Informix.

Or as IBM puts it:

When a software version reaches its End of Service date - when support is no longer available through standard Software Subscription and Support – there is no need to tackle support issues on your own. Discover new flexible options available to you when your Informix software reaches End of Service. Available for Informix versions 7.3, 9.4 and 10, these options will provide you with access to expert IBM Support professionals and will help you maintain business continuity until you upgrade to the latest version of Informix. We will also cover best practices when upgrading from Informix 10 to Informix 11.50; including upgrading standalone and replicated environments.

Register for this free event today and grab your presentation materials before the webcast.

Wednesday, July 28, 2010

Creating Dbspaces, Databases, Tables and Indexes in Informix

I guess at some point you'd like to actually create a database and create some tables with indexes to hold some data.  This logging and backup stuff is fun and all, but what good is a database without data?  We know that you put database objects in dbspaces and we have 2 of those already, the rootdbs and llogdbs01, but we don't want to put our data in these dbspaces.  If we have multiple disks at our disposal we would put the chunks that make up these dbspaces on different disks to minimize I/O contention and increase parallelism, we would want to do the same thing with our dbspaces that hold our tables and indexes.  Even if you are limited to one or two disks it is a good idea to keep your data in separate dbspaces.  First of all our llogdbs01 dbspace only has 11 pages available, so we can't fit much data in there and secondly you want to avoid filling up the rootdbs to give Informix space for housekeeping.

Create a new dbspace for data called datadbs01 using the steps we used to create the llogdbs01 dbspace.  I'll create this dbspace with 1 4GB chunk, feel free to create a smaller or larger dbspace depending on the amount of storage you need/have available.  We will use the default page size of 2K, even though different sizes ranging from 2K to 16K are available, this is a topic for another day.

For performance and data integrity reasons do not use journaled filesystems (EXT3, EXT4, ZFS, etc.) for any of your cooked chunks, including the rootdbs and llogdbs01 dbspace chunks we created earlier.  Art Kagel explains why in his blog post New Journaled Filesystem Rant.

informix> touch /home/informix/chunks/DATADBS01.01
informix> chmod 660 /home/informix/chunks/DATADBS01.01
informix> onspaces -c -d datadbs01 -p /home/informix/chunks/DATADBS01.01 -o 0 -s 4194304
Verifying physical disk space, please wait ...
Space successfully added.

** WARNING **  A level 0 archive of Root DBSpace will need to be done.
Take a Level 0 backup like we're asked to and verify the dbspace was added by running onstat -d

informix> ontape -s -L 0 -d
informix> onstat -d
IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 01:06:04 -- 144148 Kbytes

address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
4ae5b808 1        0x40001    1        1        2048     N  B     informix rootdbs
4ae5bc80 2        0x40001    2        1        2048     N  B     informix llogdbs01
4be72410 3        0x40001    3        1        2048     N  B     informix datadbs01
 3 active, 2047 maximum

address  chunk/dbs     offset     size       free       bpages     flags pathname
4ae5b968 1      1      0          1048576    518077                PO-B- /home/informix/chunks/ROOTDBS.01
4ae5bde0 2      2      0          1048576    11                    PO-B- /home/informix/chunks/LLOGDBS01.01
4be72570 3      3      0          2097152    2097099               PO-B- /home/informix/chunks/DATADBS01.01
 3 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always
Easy peasy lemon squeezy.

Now lets create an unbuffered logging database named 'blog' in datadbs01 using dbaccess, a curses based Informix utility for running SQL statements.  Typically I would use dbaccess in Menu mode for something like this, but to make the examples clearer I will use dbaccess in Interactive Non-Menu mode (by supplying a hyphen as the second command line argument to dbaccess).

informix> dbaccess - -
> create database blog in datadbs01 with log;

Database created.
I have successfully created the blog database and am currently connected to it.  When I told Informix to create blog in datadbs01 all of the housekeeping stuff for the blog database were put in datadbs01 and datadbs01 will be the default dbspace for any tables, indexes, etc. I create.

Now lets create some tables.  How about some tables to record blog entries for multiple blogs, call them blog and blog_post.  For this type of stuff, things that involve more typing and are a little more complicated, I like to have dbaccess execute a .sql file that I prepare before hand.

informix> vi create_tabs.sql

create table blog (
   id             serial not null,
   name           varchar(255)
) in datadbs01 extent size 256 next size 256 lock mode row;

create table blog_post (
   id             serial not null,
   blog_id        integer not null,
   title          varchar(255)
) in datadbs01 extent size 1024 next size 1024 lock mode row;

informix> dbaccess blog create_tabs.sql

Database selected.

Table created.

Table created.

Database closed.

If you're familiar with databases, create_tabs.sql should look familiar enough except for the Informix specific stuff.  The Serial data type is an auto-incrementing integer, in datadbs01 puts the table in the datadbs01 dbspace, extent size and next size specify the first and next extent sizes in KB and finally lock mode row enables row level locking on this table vs. page level locking.

Looks like we need some referential constraints and some indexes on those tables.  You could do this as part of the table creation, but if you do Informix will name the supporting indexes all funky and if you have to do something with them later it will be a pain.  Because of this I like to manually create the indexes and create foreign and primary keys afterwards.

informix> vi create_idx.sql

create unique index blog_pk on blog (id) in datadbs01;
alter table blog add constraint primary key (id)
    constraint blog_pk;

create unique index blog_post_pk on blog_post (id) in datadbs01;
alter table blog_post add constraint primary key (id)
    constraint blog_post_pk;

create index blog_post_fk1 on blog_post (blog_id) in datadbs01;
alter table blog_post add constraint foreign key (blog_id)
    references blog (id)
    constraint blog_post_fk1;

informix> dbaccess blog create_idx.sql

Database selected.

Index created.

Table altered.

Index created.

Table altered.

Index created.

Table altered.

Database closed.
I created 2 unique indexes with 2 primary keys on top of them and 1 non unique index with a foreign key on top of it.  This way requires a little bit more typing, but it is how I like to do it and if you want to put the underlying referential constraint indexes in a non default dbspace and give these indexes a name then you should do it this way too.

Time to put some data in these tables.  I'm going to use a INSERT INTO ... VALUES SQL to put data into the blog table and use a dbaccess LOAD FROM .. INSERT INTO SQL to load data from a pipe delimited file into blog_post.

informix> vi load.sql

insert into blog (id, name) values (0, "Informix DBA");
insert into blog (id, name) values (0, "Informix technology");

select * from blog;

informix> dbaccess blog load.sql

Database selected.

1 row(s) inserted.

1 row(s) inserted.

id    1
name  Informix DBA

id    2
name  Informix technology

2 row(s) retrieved.

Database closed.

informix> vi load.unl

0|1|Creating Dbspaces, Databases, Tables and Indexes in Informix|
0|1|ZOMG, FYI - IM Informix Tech Support FTW|
0|1|Informix Backup and Restore - The Bare Minimum|
0|2|A bug can undermine your troubleshooting|
0|2|Informix Editions revisited|
0|2|New Informix editions: Bargain time?|

informix> dbaccess blog -

Database selected.

> load from load.unl insert into blog_post;

6 row(s) loaded.

> select * from blog_post;

id       1
blog_id  1
title    Creating Dbspaces, Databases, Tables and Indexes in Informix

id       2
blog_id  1
title    ZOMG, FYI - IM Informix Tech Support FTW

id       3
blog_id  1
title    Informix Backup and Restore - The Bare Minimum

id       4
blog_id  2
title    A bug can undermine your troubleshooting

id       5
blog_id  2
title    Informix Editions revisited

id       6
blog_id  2
title    New Informix editions: Bargain time?

6 row(s) retrieved.

The last thing I want to do is show you how to add a chunk to an existing dbspace, something you might want to do if you need more space for your data.  This is very similar to creating a dbspace and is done via the same onspaces command

informix> touch /home/informix/chunks/DATADBS01.02
informix> chmod 660 /home/informix/chunks/DATADBS01.02
informix> onspaces -a datadbs01 -p /home/informix/chunks/DATADBS01.02 -o 0 -s 4194304
Verifying physical disk space, please wait ...
Chunk successfully added.

informix> onstat -d

IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 00:26:37 -- 144148 Kbytes

address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
4ae5b808 1        0x60001    1        1        2048     N  B     informix rootdbs
4ae5bb88 2        0x40001    2        1        2048     N  B     informix llogdbs01
4bd65d28 3        0x60001    3        2        2048     N  B     informix datadbs01
 3 active, 2047 maximum

address  chunk/dbs     offset     size       free       bpages     flags pathname
4ae5b968 1      1      0          1048576    518259                PO-B- /home/informix/chunks/ROOTDBS.01
4ae5bce8 2      2      0          1048576    11                    PO-B- /home/informix/chunks/LLOGDBS01.01
4bb51cc8 3      3      0          2097152    2095214               PO-B- /home/informix/chunks/DATADBS01.01
4bdb73e0 4      3      0          2097152    2097149               PO-B- /home/informix/chunks/DATADBS01.02
 4 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

Carlton Doe has a good book on Informix administration available on Amazon.

Tuesday, July 27, 2010

ZOMG, FYI - IM Informix Tech Support FTW

I work in a relatively small office where everyone is at most an 18 second walk away from each other.  Care to guess what the most common form of communication between us is?  Instant messaging.  Is it because we're too lazy to get up from our desks or we're nerdy introverts who resist human interaction (gasp)?  Well, that may be some of it, but the real reason is that IM is often the most effective form of communication for what we need to talk about.  Sure, if we need to talk about something that is going to involve a whiteboard then IM is not the best choice, but if someone is asking me about the command line arguments to start an application with verbose output and a user specified config file or the whereabouts of a log file on a server then I'm going to prefer to type that information out.

Informix Technical Support has a new support offering that can take advantage of the power of IM and render the Phonetic Alphabet obsolete.

Support over the Phone: Can you run onstat dash g as in Golf space r as in Romeo q as in Quebec m as in Mike?
Me: Did I catch a Niner in there?

Support over Chat: Can you run onstat -g rqm and email me the output?
Me: It is in your inbox.

The Informix Technical Support Chat web page describes the new offering like so:

Technical Support Chat allows you to quickly interact 1:1 with one of our Level 2 Support Professionals online instead of over the phone. You can discuss your request and conveniently share command strings and URLs. At any time during the chat, you or the Support Professional can choose to resolve the issue over the phone.

I recently had the opportunity to take advantage of this new service and the experience was as advertised.  My Support Enginer (shout out to my man Jay P., thanks for the help) was able to ask me to run commands and collect diagnostics info and I was able to give accurate feedback instantly either through the IM client or via email for the bigger output.  This was much easier than attempting to describe what I'm seeing over the phone to Jay and I'll definitely be using Technical Support Chat again.


Monday, July 26, 2010

Informix Backup and Restore - The Bare Minimum

Christian Szell: Is it safe?... Is it safe?

Babe: You're talking to me?

Christian Szell: Is it safe?

Babe: Is what safe?

Christian Szell: Is it safe?

Babe: I don't know what you mean. I can't tell you something's safe or not, unless I know specifically what you're talking about.

Christian Szell: Is it safe?

Babe: Tell me what the "it" refers to.

Christian Szell: Is it safe?

Babe: Yes, it's safe, it's very safe, it's so safe you wouldn't believe it.

Christian Szell: Is it safe?

Babe: No. It's not safe, it's... very dangerous, be careful.

- Marathon Man, 1976

Hopefully you will never need to restore from a backup, but chances are you will and it can be as scary as dental torture.  Disks fail, servers fail, disasters destroy data centers, users accidentally delete or update the wrong data and other generally sucky things happen to the databases we administer.  Day in and day out database backups are annoying little things that suck up I/O resources and storage space only to be discarded when the next backup comes along, but there is no better feeling than being able to restore the last backup and get your system back online with minimal data loss after a failure.  There is no worse feeling than needing a backup and not having one.  The good news is basic Informix backup and restore is pretty simple and straight forward in recent versions of Informix.

Saturday, July 24, 2010

Informix Quick Start Guide

I added a page to my blog called Informix Innovator-C Quick Start Guide to capture the steps required to install, initialize and administer Informix that I've been burying in my blog posts.

The information is relevant to most versions of Informix and not just Innovator-C as the name implies.

I'll continue to add to this page and hopefully it will be helpful to the people using Informix for the first time.

Friday, July 23, 2010

Blogging About Logging - Informix Physical and Logical Logging

What rolls down stairs alone or in pairs
Rolls over your neighbor's dog?

What's great for a snack and fits on your back?
It's Log, Log, Log!

It's Log, Log.  It's big, it's heavy, it's wood.
It's Log, Log.  It's better than bad, it's good!

Everyone wants a Log!  You're gonna love it, Log!
Come on and get your Log!  Everyone needs a Log!

- Ren and Stimpy

Logical Logging

Each time you insert, update or delete a row a logical log record containing information about what was changed and how is written to the logical log (if transaction logging is enabled for your database.  You do have this enabled, right?)  Each time you create or alter a database object (table, index, sequence, etc.) a logical log record is written to the logical log (even if transaction logging is disabled).  Each time you run a backup, a checkpoint happens, you add a chunk or dbspace or a new extent is allocated a logical log record is written to the logical log.

Thursday, July 22, 2010

IBM Redbooks - Migrating from Microsoft SQL Server to IBM Informix

Just noticed a new IBM Redbook was published today and is available for download here.

I just downloaded it, but have not read it yet.  I will be taking a look at it today.

Here is the abstract from the IBM Redbooks website

In this IBM® Redbooks® publication, we discuss considerations, and describe a methodology, for transitioning from Microsoft® SQL Server 2008 to the Informix® Dynamic Server. We focus on the topic areas of data, applications, and administration, providing information about the differences in features and functionality, including the data types, data manipulation language, data definition language, and stored procedures. Understanding the features and functionality of the two products assists you in developing a migration plan. 

Tuesday, July 20, 2010

The New Phone Books's Here! Informix Repackaging and Repricing - Part Duex

Page 73 - Johnson, Navin R.! I'm somebody now! Millions of people look at this book everyday! This is the kind of spontaneous publicity - your name in print - that makes people. I'm in print! Things are going to start happening to me now.

- The Jerk, 1979

IBM has tweaked the repackaging of Informix that they did on May 25th of this year and has put together a matrix showing how the newly priced Informix products stack up against similarly priced Microsoft and Oracle products.

The big news in repackaging is that Ultimate-C for Windows and Mac are gonzo and Innovator-C is now your free Informix engine of choice for all operating systems, not just Linux.

But remember, for almost everyone needing a free database engine this will be OK.  I broke down each feature that is excluded from Innovator-C here, here, here and here and there aren't any major show stoppers.

Monday, July 19, 2010

Initializing Informix Innovator-C on Linux

If you are new to Informix, say this over and over and over again.  Then say it one more time.

Engine initialization is something you do once.  Engine initialization is not the same as starting the engine.

When you initialize an engine you give birth to a new instance.  A previous instance that was occupying the same location as this new instance is overwritten with the data for the new instance.  If you have unintentionally reinitialized an engine, the only way to bring it back is from a restore of your last backup.

oninit -iy instructs Informix to initialize the engine without asking if you're sure you'd like to wipe out any existing instances.  I don't recommend you use the -y option when running oninit, your brain will get used to typing this and you can accidentally wipe out your whole instance without realizing it until it is too late.  I feel queasy just typing that command, even in a blog.

More Free Stuff - Training by Art Kagel from Advanced DataTools

In the spirit of Informix and free stuff Advanced DataTools continues their
Informix Performance Tuning Webcasts Series by Art Kagel.  If you want to attend this free 2 hours webcast you have to act fast because the event is tomorrow, July 20 at 2:00 PM EST and registration is open now.

The focus is on performance tuning historical data collection:
  • What is worth collecting over time?
  • Collection frequency
  • Now that I have the data, what do I do with it?
Again, this is a free webcast but you have to register here.  I registered earlier today and it was quick and painless, only my name and email address were required.

Thursday, July 15, 2010

Informix Chat with the Labs Replay

IBM hosted the monthly Informix Chat with the Labs call a few days ago.  The topic was Best Practices for Running Informix in VMWare vSphere Virtual Environments.

If you missed it you can find the audio replay and presentation materials for this and other CWTL from the past at developerWorks

Some of the topics you will find there:
  • Best Practices for Using Auto-Update Statistics
  • What's New in Informix 11.50.xC6
  • HDR Best Practics and Performance Tuning
  • Informix Availability Best Practices
  • Update Anywhere Clusters in 11.50
  • Informix 11.50 Continuous Availability Feature
IBM has also been translating the CWTL to Spanish. Que bueno!

If there is a topic you would like to see chatted about but you don't know who to ask, let me know and I'll pass along the request.

Wednesday, July 14, 2010

You Know What Else is Free?

Membership to the International Informix Users Group is free.

If you are new to Informix and are not a member of the IIUG I strongly suggest you join today.
What you get for a $0 investment and 10 minutes of your time:
  • Monthly newsletter to keep you up to date with everything Informix
  • Access to the Software Repository where you can download Member supplied DBA utilities, scripts and applications and Free IBM Software
  • Access to the SIGs, a collection of message boards dedicated to Informix where you can ask the community for answers and technical advice
  • Discounts on conference registration
  • Access to presentation materials from previous conferences
  • Access to the Job Board

Tuesday, July 13, 2010

Installing Innovator-C on Linux

You want to take advantage of this new free for production database engine, Innovator-C, but you have no experience with Informix and don't know where to begin.

If the above statement describes you, then you have come to the right place as I will attempt to describe in great detail (maybe too much detail) the Informix engine installation process.

What you will need:

  • A machine running a supported version of Linux as documented in the machine notes. Most major and recent Linux distributions are supported (RHEL4 and 5, SLES 10 and 11, Ubuntu 6.06.01 and Debian 4.0). I am using 64 bit Red Hat Enterprise Linux 5
  • root access. The install is performed by root and root will be needed to create the informix user and informix group
  • Innovator-C (32 bit version for 32 bit OS, 64 bit version for 64 bit OS), found here
Get the system ready

As root, create the informix group, create the informix user as part of the informix group and set the password for the informix user.

root> groupadd informix
root> useradd -g informix informix
root> passwd informix

Sunday, July 11, 2010

Innovator-C Excluded Features - The Final Installment

Here is the last installment of the features that are not available with the Innovator-C free version of Informix.

No Multiple triggers and views

I'll admit it, I don't really understand what this feature is. Triggers and views are certainly available in Innovator-C, but what does it mean to disallow multiple triggers and views. Does it mean you can't have multiple Insert triggers on the same table? Probably. If so, work around this limitation by creating a stored procedure that does what you want on an Insert and create an Insert trigger that calls this procedure. I can't find anything in the Informix docs that define Multiple Triggers or Multiple Views. When I find out which feature is excluded here I'll let you know.

No Web feature service

I'm assuming this is the Web Datablade. Never had a need for it and I'm assuming people needing an Innovator-C type database won't miss it either.

No Node DataBlade

Same goes for the Node Datablade, which allows you to use the Node datatype (right?). I won't cry about this feature being left out.