Informix DBA

Sharded Collections

noreply@blogger.com (Anonymous) — Mon, 21 May 2018 11:00:00 +0000

Sharding allows you to seamlessly distribute rows of a table over multiple instances while keeping this implementation detail hidden from the client.

This can be an extremely powerful tool that allows you distribute your work load over many smaller/cheaper instances, achieve greater parallelism for queries and scale up your environment as you grow.

When people think of sharding, they typically think of MongoDB and the sharding of JSON documents over multiple nodes, which Informix can also do, but Informix can also shard a relational table over multiple nodes and that seems like a fun exercise. Let's try it!

What we will do

Create a second HCL Informix AWS instance
Configure both of our instances to allow sharding
Create a sharded collection for our relational stock_trans table
Create a third instance and add it to the sharded collection
Remove the second instance from our sharded collection
Do some performance testing along the way

Create a second HCL Informix AWS instance

Follow the steps from the last two blog posts (this and this) to setup a second AWS instance. Some steps you won't have to do because we will use the same Security Groups and Key Pair we created earlier.

1. Create an new Elastic IP

2. Configure and launch a second HCL Informix EC2 instance using the Security Group and Key Pair we created earlier

3. Assign the Elastic IP to this new instance

Configure both instances to allow sharding

We're going to follow steps 1 through 8 in the AWS instance changes section of the previous post to change the DBSERVERNAME from ol_aws to informix_1 and informix_2 and we're going to use the number in the DBSERVERNAME to map to a shard id. (informix_1 is shard 1, informix_2 is shard 2, etc).

We also need to make some additional ONCONFIG changes on both instances. Make the following changes to informix_1 and bounce the engine. For informix_2, you can make these changes when you change the other ONCONFIG parameters.

SHARD_ID <shard id>
CDR_SERIAL 20,<shard id>
CDR_AUTO_DISCOVER 1

The table we are going to shard has a BIGSERIAL field and Informix sharding is build on Enterprise Replication so we want to ensure that serial values are unique across all shards and that is where CDR_SERIAL comes into play. When CDR_SERIAL is defined as 20,1 for shard 1 we will generate serial values of 1, 21, 41, 61, 81,... when rows are inserted on shard 1 and 2, 22, 42, 62, 82,... when rows are inserted on shard 2. I'm limiting myself to a maximum of 20 shards with this configuration because I know I'm not going to create more than that.

CDR_AUTO_DISCOVER will allow us to use the cdr autoconfig server command that simplifies setting up Enterprise Replication

We can also add the informix_2_ssl information to our client's sqlhosts file and test the connection from our client when informix_2 comes online.

informix_1_ssl onsocssl <elastic ip> 9089
informix_2_ssl onsocssl <elastic ip> 9089

2. Security Group

We need to open up port 9088 for all of the servers in our private AWS network. First we need to figure out what the CIDR for our private network is. In the AWS console click Services in the top menu and search for VPC and open the VPC Dashboard. Click on Your VPCs in the left hand pane to bring up details for your VPCs. Find the value under IPv4 CIDR and remember it. It will be something like 172.31.0.0/16

In the EC2 Dashboard, modify our Security Group again and add an inbound rule for TCP port 9088 with a Source of your IPv4 CIDR.

3. Create a new Storage Pool

For reasons unbeknownst to me, the cdr autoconfig server command just doesn't like the storage pool that comes with the engine. No problem, we can add a new storage pool to informix_1 and informix_2 with ease by running the following on both instances.

> mkdir /data/storagepool_2
> chmod 755 /data/storagepool_2
> dbaccess sysadmin

execute function task('STORAGEPOOL ADD','/data/storagepool_2',0,0,'64MB',1);
execute function task("modify config persistent", "SBSPACENAME", "sbspace1");
execute function task('CREATE SBSPACE FROM STORAGEPOOL', 'sbspace1', '100MB', 1);

4. Trusted Connections

All of our shard servers need to connect to each other without supplying a password. We can do this by creating trusted hosts with the sysadmin:task function.

Run the following sysadmin tasks on informix_1 only.

execute function task("cdr add trustedhost", "<informix_1 internal DNS name>");
execute function task("cdr add trustedhost", "<informix_2 internal DNS name>");

5. Define the initial shard server with the cdr autoconfig server

informix_1> cdr autoconfig server

This command is going to setup some ER dbspaces in our storage pool, automatically change our sqlhosts file to create an ER group and initialize our first ER server on informix_1

6. Define the second shard server with cdr autoconfig server

informix_2> cdr autoconfig server -H <informix_1 internal DNS name> -P 9088

This command does some nice things for us.

Not only does it create our ER dbspaces like before, it add our new informix_2 server and group to all sqlhosts files in the ER network, propagates the trusted host information from informix_1 to all servers in the ER network and starts ER on informix_2.

Created sharded collection

We're going to drop the old stocks database we created earlier and recreate the stocks database and stock_trans table on both servers.

database sysmaster;

drop database if exists stocks;

create database stocks in dbs_1 with buffered log;

create table stock_trans (
   id            bigserial,
   account_id    integer,
   stock_id      integer,
   action        char(1),
   shares        integer,
   price         float,
   timestamp     datetime year to second
) in dbs_1 extent size 20000 next size 20000 lock mode row;

create unique index stock_trans_pk on stock_trans(id);
alter table stock_trans add constraint primary key (id)
        constraint stock_trans_pk;

grant connect to client;

grant select, insert, update, delete on stock_trans to client;

The next step is to run the cdr define shardCollection command, but I think we need to pause for a second and talk about what's going to happen when we do and some steps our clients are going to need to do to take to take advantage of sharding.

When the shard is created for the stock_trans table and sharding is enabled by the client and we insert a row into the stock_trans table the row will eventually end up physically living on informix_1 or informix_2, but the client doesn't know or care. The same goes for select, update and delete, the sharding is invisible to the client. To the client the stock_trans table just looks like a regular table with the exception that it can be accessed from either informix_1 or informix_2.

We will tell the shard collection to determine which shard to put a row on based on a hash of the id field which will give us a pretty even distribution of data over all our shards.

cdr define shardCollection stock_trans_collection stocks:informix.stock_trans --type=delete --key=id --strategy=chash --partitions=3 --versionCol=id g_informix_1 g_informix_2

Now that the shard collection has been defined, the stock_trans tables on informix_1 and informix_2 will effectively been joined into one table when the client sets the use_sharding session environment to on via 'set environment use_sharding on'.

After connecting to informix_1, setting 'use_sharding on' and inserting 10,000 records I can see that the collection is working.

set environment use_sharding on;

select 'informix_1', count(*) from stocks@informix_1:stock_trans; -- table only
select 'informix_2', count(*) from stocks@informix_2:stock_trans; -- table only
select 'collection', count(*) from stock_trans;                   -- collection

(constant)       (count(*))

informix_1             6742


(constant)       (count(*))

informix_2             3258


(constant)       (count(*))

collection            10000

select first 5 account_id, sum(shares) from stock_trans group by 1 order by 2 desc;

 account_id            (sum)

       5803             7376
       6637             6985
       5232             6714
       1432             6596
       2315             6485

The first 2 selects query the stock_trans table that is physically located on each instance, but the third and fourth select query the collection. Behind the scenes both stock_trans tables are queried in parallel and the results are merged and send back to the client.

Add a new shard to the collection

Over time our stock business has grown and and 2 shards isn't enough for our workload, so we would like to add a third.

You can follow the steps we used to create informix_2 to create informix_3, making sure to run the "cdr add trustedhost" sysadmin:task on informix_1 to add informix_3 to the list of trusted hosts and run the same cdr autoconfig serv command on informix_3 that we used to add informix_2. Don't forget to create the client user, stock database and stock_trans table on informix_3.

When informix_3 has been added to the ER network, run the cdr change shardCollection command to add a new informix_3 shard and automatically rebalance data over the 3 shards

cdr change shardCollection stock_trans_collection --add g_informix_3

We can verify the collection rebalanced and is working properly

set environment use_sharding on;

select 'informix_1', count(*) from stocks@informix_1:stock_trans; -- table only
select 'informix_2', count(*) from stocks@informix_2:stock_trans; -- table only
select 'informix_3', count(*) from stocks@informix_3:stock_trans; -- table only
select 'collection', count(*) from stock_trans;                   -- collection


(constant)       (count(*))

informix_1             2876


(constant)       (count(*))

informix_2             3258


(constant)       (count(*))

informix_3             3866


(constant)       (count(*))

collection            10000

Taking advantage of the sharded collection

In the last post we saw that going over the public internet slowed things down considerably, but we can use our new sharded setup to cut that time down.

When we just had 1 stand alone table, it took 384.8 seconds to insert 10,000 stock_trans rows. What if we loaded 3 sets of data in parallel, 3333 rows to informix_1, 3333 rows to informix_2 and 3334 rows to informix_3?

Splitting the data set and loading in parallel cuts the load time down to 163.4 seconds, over twice as fast as before.

Removing a shard from the collection

Let's say we no longer want informix_2 to be a shard. Hey, it happens. Removing it is easy.

cdr change shardCollection stock_trans_collection --drop g_informix_2

When we take a look under the covers, we see that the informix_2 stock_trans table has been emptied and the rows redistributed among informix_1 and informix_3.

set environment use_sharding on;

select 'informix_1', count(*) from stocks@informix_1:stock_trans; -- table only
select 'informix_2', count(*) from stocks@informix_2:stock_trans; -- table only
select 'informix_3', count(*) from stocks@informix_3:stock_trans; -- table only
select 'collection', count(*) from stock_trans;                   -- collection

(constant)       (count(*))

informix_1             6769


(constant)       (count(*))

informix_2                0


(constant)       (count(*))

informix_3            13231


(constant)       (count(*))

collection            20000

Well, that's enough fun for today. I hope you've enjoyed taking shards and Informix on AWS for a test drive.

I have a couple of other ideas for this technology. We definitely need to get some HDR Secondaries going. Not only because we like to have redundancy, but a secondary can also be used to satisfy selects and I'd like to see how that improves response times. I also want to see how Updateable Secondaries work with shard collections.

Don't forget to check out the IIUG World 2018 Informix event in Washington, DC October 28 to November 1, 2018.

Early Registration is now open (save $275) and we are now accepting Speaker Proposals (save 100%) and we even have a new website from this century and a Twitter account you can follow @IIUGWorld2018 for the latest updates.

Encrypted Database Connections to HCL Informix on AWS

noreply@blogger.com (Anonymous) — Mon, 14 May 2018 11:00:00 +0000

In my last post we launched an HCL Informix AWS instance and verified the engine was running, now I want to connect to this instance from my data center over the public internet.

I can't just send data back and forth without encrypting it because I don't want my data intercepted by someone in between. I'll enable TLS encryption to keep my data secret and I'll also create a transaction table, insert some data and do some basic performance testing. In later posts, I will shard this transaction table over multiple AWS Informix instances and I'll do a little prep work to make that step easier.

If you don't plan to connect to your Informix instance over the public internet and keep all of your communications between servers in the same VPC then this encryption step is unnecessary, but it is still good to know how to do it.
There is one rub. You're going to need to get your hands on a later version of the CSDK that comes with GSKit 8 (I know version 4.10 of the CSDK works) and that is currently kind of a challenge. As of this writing, you can contact HCL and ask for it or if you have an IBM Passport Advantage login, you can download it there. I'm going to assume you have an appropriate CSDK installed on the client. If you need help installing the CSDK on a client, leave a note in the comments and I'll add them to the post.

Prerequisites

HCL Informix on AWS engine setup and running
Informix CSDK 4.10 installed on the client
I will use the informixdb Python module to connect to Informix from my client, you can use whatever you like

What we are going to do

Open up a port on our AWS instance for TLS communications
AWS instance changes
Use the HCL Informix certificate to create a TLS/SSL key store on the client
Connect over the public internet and do some testing

Open up a port on AWS instance for TLS communications

In our Security Group we only let connections from port 22 for ssh into our AWS instance and if we're going to connect to Informix we need to open up a port.

In Security Groups on the AWS console, select our Security Group and click Actions and then click Edit inbound rules.

In the dialogue that is opened up click the Add Rule button and add a rule of:

Type: Custom TCP Rule
Protocol: TCP
Port Range: 9089
Source: Custom and the public IP address or range of your client OR you can use Anywhere if you don't (just for the purposes of testing)

Click Save and your AWS firewall will now allow traffic on port 9089 from your client over the public internet.

AWS instance changes

I'm going to make some changes to my ONCONFIG and sqlhosts that will really be more useful in future posts when I setup sharding, but I'll do them now.

1. ssh to your AWS instance and sudo -u informix bash

2. ONCONFIG changes

I'm going to change the name of my instance to something more unique so I can start multiple instances later.

Change DBSERVERNAME from ol_aws to informix_1

Change DBSERVERALIASES to informix_1_ssl

3. sqlhosts changes: replace contents with the following. You can find the internal DNS name for an EC2 instance in the details of the instance in the AWS console.

informix_1     onsoctcp <internal DNS Name>   9088 
informix_1_ssl onsocssl *                     9089

4. Edit /etc/profile.d/informix.sh and change INFORMIXSERVER to informix_1

5. Shutdown the engine via onmode -ky

6. Exit back to the centos user, sudo -u root bash and rename the engine's SSL files

The engine looks for these ssl files in the $INFORMIXDIR/ssl/<dbservername>.extension. We changed the DBSERVERNAME, so we need to rename these.

cd $INFORMIXDIR/ssl/
mv ol_aws.rdb informix_1.rdb
mv ol_aws.crl informix_1.crl
mv ol_aws.sth informix_1.sth
mv ol_aws.kdb informix_1.kdb

7. Exit back to the centos user, sudo -u informix bash and start the engine with oninit -v

8. Create a database user named 'client' that we will use to connect

echo "create user client with password 'your_password' account unlock properties user nobody authorization(dbsa)" | dbaccess sysmaster

Create TLS key store on the client

1. On the AWS instance, copy the TLS certificate to /tmp

cp /home/informix/client_ssl/selfsigned_ssl.cert /tmp

2. Create a client_ssl directory on the client server and get the selfsigned_ssl.cert file

I'm doing this as user informix and I've decided to create /home/informix/client_ssl to mirror HCL's setup. Feel free to put this directory wherever you see fit.

mkdir /home/informix/client_ssl
scp -i ~/.ssh/informixdba.pem centos@<elastic ip>:/tmp/selfsigned_ssl.cert .

3. Create a TLS key store

/usr/local/ibm/gsk8_64/bin/gsk8capicmd_64 -keydb -create -db informix_ssl.kdb -pw <a password> -type cms -stash

4. Add the AWS certificate to the key store

/usr/local/ibm/gsk8_64/bin/gsk8capicmd_64 -cert -add -db informix_ssl.kdb -pw <the password> -label selfsigned_ssl -file selfsigned_ssl.cert -format ascii

5. Create a conssl.cfg file on the client

Create $INFORMIXDIR/etc/conssl.cfg and add

SSL_KEYSTORE_FILE   /home/informix/client_ssl/informix_ssl.kdb   # Keystore file
SSL_KEYSTORE_STH    /home/informix/client_ssl/informix_ssl.sth   # Keystore stash file

and change the permissions of informix_ssl.kdb and informix_ssl.sth via chmod 644 <filename>

6. Add the encrypted connection to the client's sqlhosts file

informix_1_ssl          onsocssl          52.0.114.137          9089

You will now be able to connect to informix_1_ssl with dbaccess as the user 'client' and create a database and a table that we will use for some testing.

create database stocks in dbs_1 with buffered log;

create table stock_trans (
   id            bigserial,
   account_id    integer,
   stock_id      integer,
   action        char(1),
   shares        integer,
   price         float,
   timestamp     datetime year to second
) in dbs_1 extent size 20000 next size 20000 lock mode row;

create unique index stock_trans_pk on stock_trans(id);
alter table stock_trans add constraint primary key (id)
        constraint stock_trans_pk;

I created a simple Python script to generate and insert 10,000 records into the stock_trans table and I ran 3 tests.

The first test is to run the script locally on the AWS instance to get a baseline of how long it takes the engine to process the inserts without any encryption and without going over the public internet.

Average per row 0.00011 seconds

The second test is to perform the inserts over the public internet, but without any encryption enabled. This will show me how much overhead is added by the public internet.

Average per row: 0.03660 seconds

That's not a terribly unexpected result, but the internet does add some considerable overhead. The final test is to perform the inserts over the public internet using encryption to see how much overhead it adds.

Average per row : 0.03848 seconds

We can see that encryption is relatively inexpensive, only costing a couple milliseconds per row insert.

That's all for today. Next time I will create a sharded collection of our stock_trans table over multiple AWS instances, add and remove shards from the collection and do some more performance testing.

Don't forget to check out the IIUG World 2018 Informix event in Washington, DC October 28 to November 1, 2018.

Early Registration is now open (save $275) and we are now accepting Speaker Proposals (save 100%) and we even have a new website from this century and a Twitter account you can follow @IIUGWorld2018 for the latest updates.

Getting Started with HCL Informix on AWS

noreply@blogger.com (Anonymous) — Sun, 06 May 2018 01:44:00 +0000

It's Saturday night and I've got a few cold ones in the fridge so let's do this. Let's get an HCL Informix instance up and running on AWS.

Prerequisites:

You have an AWS account (sign up)
Your OS is some flavor of Unix/Linux (not really that important)

What are we going to do?

Create an AWS Elastic IP address
Create an AWS Security Group
Create a ssh key pair
Configure and launch an EC2 instance running HCL Informix
Connect to our EC2 instance and verify the engine is up and running

Create an AWS Elastic IP address (optional)

When you launch an AWS EC2 instance, the instance is given a private IP address and private DNS name that are accessible from all of the EC2 instances in your AWS Virtual Private Cloud (VPC) and these never change for as long as the EC2 instance exists, even if you shut it down and restart it. Nice.

Additionally, when you launch an EC2 instance, it is given a public IP address and public DNS name that is accessible from the public internet, BUT this changes each time you restart the EC2 instance and that's not so nice.

An Elastic IP address is a public IP address and DNS name that you can assign to an EC2 instance so it always has the same IP when you stop/start the EC2 instance and just makes things a little easier. Elastic IPs are free as long as they are attached to a running instance and cost just a penny an hour when they aren't. If that's a little too pricey for you, you can skip this step and connect to the public IP address that's automatically given to your EC2 instance. Just remember that you'll have to go and figure out what the new public IP/DNS name is for your instance from the console each time you stop/start your EC2 instance.

First, open up the AWS console, click on EC2 and then click Elastic IPs in the left hand pane under NETWORK & SECURITY.

Now click the Allocate new address button and then the Allocate button. You should see a message of New address request succeeded and the new Elastic IP address. Make note of this IP and click the Close button.

Learn more about Elastic IP addresses

Create an AWS Security Group (optional, but go ahead and do it)

An AWS Security Group is like a firewall where you define which ports are open to certain IP addresses. We're not going to do anything fun with the Security Group now, we're just going to open up the SSH port to the world so we can get into the box. It'll come in handy later though.

In the left hand pane of the AWS EC2 Console, click Security Groups under NETWORK & SECURITY.

Now click the Create Security Group button. This will give you the Create Security Group pop up where you can give the Security group a name of "Informix DBA SG" and a description of whatever you like. You can leave the VPC as the prepopulated default value.

Now click the Add Rule button to add an inbound rule. For Type, select SSH and Source select Anywhere then click the Create button in the lower right corner.

Learn more about Security Groups

Create a SSH Key Pair

All logins into AWS EC2 instances are passwordless so you need a SSH key pair to login via some command that will look a little like this

ssh -i <public ssh key file> centos@<ip address or DNS name>

Click Key Pairs on the left hand pane under NETWORK & SECURITY and then click the Create Key Pair button.

Give the Key Pair a name as instructed (I'm using informixdba) and click the Create button.

This will create and download an informixdba.pem file to your browser, save it somewhere nice like ~/.ssh because you'll need to reference it later and change the file permissions to 600 via chmod 660 ~/.ssh/informixdba.pem

Configure and launch an EC2 instance running HCL Informix

Finally, the prep is done and we're ready to get into the good stuff.

Click Instances on the left hand pane under INSTANCES and click the Launch Instance button.

Step 1 is to Choose the Amazon Machine Image (AMI) we want to have running on our EC2 instance. We would like the HCL Informix AMI that is found in the AWS Marketplace, so click AWS Marketplace in the left hand pane and type Informix in the Search AWS Marketplace Products bar.

The first item in the returned list should be Informix sold by HCL Technologies. Find it and click the Select button.

You'll be presented with a bunch of information about pricing and the product details. Take it all in and then click Continue.

Step 2 is to Choose an Instance type. Here we get to decide how much CPU/RAM we want our EC2 instance to have. Select a t2.small instance for now and click Next: Configure Instance Details. It is super easy to upgrade later if you want.

Step 3 is Configure Instance Details. We aren't going to do anything here except maybe check the Protect against accidental termination box which will prevent us from destroying the EC2 instance unless we explicitly turn this feature off. This of it like the FULL_DISK_INIT ONCONFIG parameter for EC2 instances. Click Next: Add Storage.

Step 4 is Add Storage. Here we can increase the amount of disk space initially allocated to the EC2 instance. For our purposes, the default of 8GB is enough. You can always extend this later if you need more space. Click Next: Add Tags.

Step 5 is Adding tags, meh. Click Next: Configure Security Group.

Step 6 is Configure Security Group where we'll specify the Security Group we created earlier that opens SSH up to the public. Click the Select an existing security group radio button, click on the Informix DBA SG Security Group we created earlier and then click the Review and Launch button.

Step 7 is review where you review your settings and pricing. Enough already, let's roll. Click the Launch button.

Not so fast...after clicking Launch we've got to setup what SSH key pair we want to use to connect to this instance. Select the informixdba key pair we created earlier, click the I acknowledge box and click the Launch Instances button.

We're given a Launch Status screen, our instance is now launching and we're almost ready to connect to it. Scroll down and click the View Instances button on the bottom right hand side.

Most likely you'll see our t2.small instance in an Instance State of running (and Status Checks of Initializing). The last thing we want to do is assign that Elastic IP address we created earlier to this instance.

Click Elastic IPs in the left hand pane under NETWORK & SECURITY, select the Elastic IP we created earlier (if you only have one, it will already be selected), click the Actions button and select Associate address from the drop down menu.

In the Instance drop down, select the EC2 instance we just launched and click the Associate button.

After you get the Associate address request succeeded message, click the Close button and we're ready to ssh to this instance using the public IP/DNS name that won't change if we stop/start this instance.

Connect to our EC2 instance and verify the engine is up and running

Well, here we are, ready to take our new HCL Informix instance for a test drive. I hope it took longer to type all of that up than it did for you to get to this point. Let's connect to it and see what's what.

ssh -i ~/.ssh/informixdba.pem centos@<elastic IP address or DNS name>

Answer yes to the question about continuing to connect and you should be connected to your brand new EC2 instance running HCL Informix. The centos user is the only account that can ssh into this instance and can only authenticate with that informixdba.pem file so don't lose it.

The centos user doesn't know anything about Informix, but the informix user does so we need to switch to informix after logging in via

sudo -u informix bash

Now we should be able to see the engine and what's the first thing an Informix DBA does instinctively when logging into a box?


[informix@ip-111-11-19-178 centos]$ onstat -

HCL Informix Dynamic Server Version 12.10.FC10ME -- On-Line -- Up 00:13:43 -- 347700 Kbytes

Ain't that sumthin'?

That's all for now, next time I'll setup encrypted communications between a client and this instance over the public internet and do some performance testing.

Don't forget about IIUG World 2018 in Washington, DC October 28 - November 1, 2018.

Register before July 15, 2018 for a $275 discount and submit to present for a 100% discount at iiugworld.com.

Hello Blogger, My Old Friend...

noreply@blogger.com (Anonymous) — Thu, 03 May 2018 19:07:00 +0000

...it's time for me to write something on you again.

I haven't been writing here because I only want to write when I have something new and interesting to say and...well...I haven't had anything new or interesting to write about Informix because at this point my engines just behave themselves and run and run and run and run so I've been off in development land doing things with Python, some Javascript and a little bit of NoSQL (calm down).

That all changed with the return of the Chat with the Labs last week where Nick Geib from HCL spend a good hour and a half showing us the new HCL Informix on Amazon Web Servics (AWS) offering and my interest was piqued.

I've done some basic playing around with it and in future posts I want to tell you about some basic performance tests I've run, setting up encrypted communication so you can safely talk to these AWS instances over the public internet, how to use AWS instances to shard a relational table over multiple instances and how to dynamically (mostly) scale the number of shards up or down. It's preeeeetty cool stuff.

For now, here's a quick introduction to HCL Informix on AWS.

HCL Informix is available as an Amazon Machine Instance (AMI), meaning when you launch an EC2 AWS instance (i.e. a virtual server) HCL Informix is already installed and running (it's also configured to automatically shutdown safely when you stop the instance).

This is great for developers and DBAs with no Informix experience because it removes one barrier to entry for getting to know Informix, the installation, configuration and initialization which has always been a lot harder than say other engines like MySQL.

You pay by the hour and you don't pay when the EC2 instance is stopped. If you just want to play around with the technology, you can be up in running in minutes and shut it down when you're done.

The HCL Informix AMI is available on (almost?) every EC2 instance type, so you can try it out on a t2.small instance (1 core/2GB RAM) for $0.10/hour or on the HCL recommended m4.xlarge instance (4 cores/16GB) for $2.351/hour or you can get crazy and run on a x1e.32xlarge (128 cores/3904GB) for $95.505/hour.

You can see for yourself at the AWS Marketplace.

Here's another cool thing. Everything is included in that price. You get the Enterprise Edition with all of the add ons for one price. Partitioning, PDQ, no CPU or disk limits, compression, IWA, MACH11. HCL calls this the ME version. Nick says it stands for "My Edition", I say it stands for "Most Everything™"

That's all for now, but I would be remiss if I didn't take this opportunity to plug the IIUG World 2018 Informix event in Washington, DC October 28 to November 1, 2018.

Early Registration is now open (save $275) and we are now accepting Speaker Proposals (save 100%) and we even have a new website from this century and a Twitter account you can follow @IIUGWorld2018 for the latest updates.

Audience Participation! Benchmark Edition

noreply@blogger.com (TGirsch) — Fri, 19 Aug 2016 18:35:00 +0000

My company recently purchased new servers with dedicated storage, and any time this happens (and I have the luxury of time to do it), I take the opportunity to re-test my old assumptions concerning what storage configuration(s) work best. The tests are ongoing, and will be for several days now, but I thought it would be fun to ask you all to guess what you think the results will be.*

First, the storage array details:

It's equipped with 13x 15K RPM spinning drives with 12 Gbit/sec SAS interfaces. The storage is not shared, meaning the host to which it's connected is the ONLY one that's using it. One drive is reserved as a hot spare, leaving 12 usable drives. I'm testing the following configurations for performance:

One Giant RAID-10 ("1x12R10"): Stripe and mirror all 12 drives in a single 12-drive stripeset
One Giant RAID-6 ("1x12R6"): Stripe all the drives in a single 12-drive stripeset with two parity drives
Two big RAID-10s ("2x6R10"): Two sets of six drives, each mirrored and striped as RAID-10
Two big RAID-6s ("2x6R6"): Two sets of six drives, striped with two parity drives per set
Three small RAID-10s ("3x4R10"): Three sets of four drives, each mirrored and striped as RAID-10
Plain RAID-1 ("6xR1"): Drives are mirrored but not striped

In all cases, I've set up 6 equally-sized LUNs. In the 6xR1 case, each LUN corresponds neatly to a mirrored pair; In the 3x4R10 case, there are two LUNs on each stripeset; for the two-stripeset cases, there are three LUNs per set; and in the one-big-stripe case, obviously, all LUNs on the single set. In all cases, I have disabled the storage array's controller cache, because I want to emulate times of very high activity and test the disk, not the cache.

I'm using two different tests as benchmarks: The first (call it the "OLTP test") is the AGS TPM-C test, which you're familiar with if you've ever participated the Advance Data Tools "Fastest DBA" contest. For this test, all of the large tables are fragmented by hash across six dbspaces, one on each LUN. There are six index dbspaces, also one per LUN, across which the indexes have been distributed in round-robin fashion. I did not fragment/partition any of the indexes. In this test, I have the buffer pools tuned very small, because to repeat, I'm trying to test the disk, not the rest of the system.

The second test (call it the "batch test") is a very large purge job that sequentially scans a table with two indexes and deletes about 2 million rows from that table. The table itself is fragmented using a hash across three dbspaces, each on a separate LUN; there's a BYTE column that's stored in a multi-chunk blobspace; the chunks for the blobspace are distributed across all six LUNs, but no single chunk spans more than one LUN. And both of the indexes are fragmented six ways, with one fragment on each LUN. Three of these batch jobs are run simultaneously, one for each table fragment.

In both cases, there's a rootdbs, a dbspace for the physical log, and two logical log dbspaces. All four of these dbspaces are on separate LUNs from one another, and the logical logs alternate between the two log dbspaces (so that log #2 is not in the same dbspace as log #1).

So place your bets! Which storage configuration will work best for the OLTP test? Which will work best for the batch test? I'm curious to hear what you think. I'll post the complete results when I have them. Feel free to ask questions if you have any.

* - This will also be a good test to see if anybody's actually bothering to read these posts. :)

Public Service Announcement: Smart BLOBs Still Suck

noreply@blogger.com (TGirsch) — Fri, 12 Aug 2016 16:16:00 +0000

The last time I tried to mess around with Smart BLOBs was probably in 2007 or 2008. I found that they were buggy and excruciatingly slow. But that was years ago, most likely IDS 10.0. I've got a pretty good use case for them now, and I'm using the latest, greatest IDS 12.10.FC7, so I thought I'd have another look at them.

They're still buggy and excruciatingly slow.

Buggy how? I ran out of space in the main dbspace while trying to populate a test table in a non-logged DB with non-logging Smart BLOBs. The result is that the Smart BLOBs appear to have been created with a ref count of 0, and I was left with records that I couldn't delete, and tables I couldn't drop. And this is apparently a common enough occurrence that there's an option for onspaces that allegedly deals with it. (Since I was only testing, I found it easier just to restore the engine to its baseline.)

But maybe that was a fluke; just don't run out of space, or at least not in those circumstances, and it should be fine. Which brings us to performance.

Excruciatingly slow how? I tested the same purge process using the BYTE data type and the BLOB data type. With BLOB, the process took nearly three times as long. With BYTE, a 3 million row purge took just over 13 minutes, on average. With BLOB, over 36 minutes.

Perhaps others of you have had better luck, but my experience with Smart BLOBs was bad then, and it's still bad now.

Major Linux I/O Bug? SPOLER: No

noreply@blogger.com (TGirsch) — Thu, 21 Jul 2016 14:37:00 +0000

Alternate title: Betteridge's Law Holds!

Last week, I posted on what I thought was an I/O bug in RHEL 7 and CentOS 7. I've now done enough testing to eliminate this possibility. So, as promised:

So what was happening, then, that led me to get such drastically different results?

First, and most importantly, as I noted in an update to the prior post, one system was using SSD and the other was using spinning disk. That accounted for the large bulk of the difference. It still leaves the somewhat unsolved mystery of my production performance problems that coincided roughly in time with when the OS was updated (which is what led me down that rabbit hole in the first place).

But there may also have been other configuration changes made around that time that accounted for this. In preliminary testing, I've found that I get better performance by turning kernel asynchronous I/O off, contrary to conventional wisdom (and to what I've found on other platforms). I've coupled this with also disabling DIRECT_IO, though I believe the latter may be a bad thing: it may be that the performance is buoyed by caching that's going on at the OS level, which would mean that data hasn't really been committed to disk even though the DB engine believes that it has. By making these two changes, I've gotten the performance on the production system back close to previous (good) levels, at least for the time being.

But anyway, false alarm, and egg on my face.

Major Linux I/O Bug? -- UPDATED 2016-07-19

noreply@blogger.com (TGirsch) — Fri, 15 Jul 2016 12:14:00 +0000

Update 19 Jul 2016: I still believe there's an issue here, but I've got reason to believe my tests were not as apples-to-apples as I'd originally thought. So I'm re-engineering the tests and trying again to get updated numbers. I'm also checking to see if I can replicate results with Informix completely removed from the mix, doing some testing with simple dd.

It is also possible that there's no problem at all, and I may have to cue Emily Litella.

Further Update: Not only were apples not apples, they were also not oranges or Buicks. Turns out a big part of the reason I saw the huge discrepancies was because I had SSD on one system and spinning disk on another. Needless to say, the numbers from the original post are meaningless because of that mistake. But there's still the mystery of the performance degradation in production that coincides with when updates were installed to CentOS.

As a sanity check, I re-ran the same test on various platforms and compared those results to one another and to results I got back in September when I ran a similar battery. In every case, the results were better today than in September EXCEPT on RHEL/CentOS, where they were considerably worse than they had been in September. AIX on old hardware (with several storage configurations) smoked all combinations on Linux. So I'm thoroughly confused, and still researching.
End Update

I believe I've stumbled upon a major bug in the Linux kernel I/O subsystem. I've been working on testing newly-acquired hardware to see how it compares against the hardware we're replacing, and in my initial tests, the results I was getting were terrible. Read performance looked pretty good, but write performance was abysmal.

At first, I was blaming it on the new DAS, thinking that there must be some kind of setting causing it to heavily prioritize reads over writes. I'd run the same test on AIX machines, and those tests lost on read performance but won easily on write performance. Then, as a sanity check, I decided to do the same test on a VM, and got the same abysmal results. I'd run the test on an identically-configured VM before, several months ago, and got much different results, so that tripped my inner, "Hey! Wait a minute!"

This reminded me of a lingering performance issue that we've got on a production Linux VM that seems to have started back in mid-May. So I went onto the VM in question and ran "yum history." That shows that I had done a "yum update" back on May 18, right about the time performance issues started to be reported. "Aha!" I thought. I've got a culprit now. In setting up the new hosts, I was doing a "yum update" almost immediately. So what happens if I re-install the system and don't install updates?

You can guess where this is going: performance looks fantastic.

I'm still trying to narrow it down. My hope is to figure out which particular package update causes the problem, and I'll report back if/when I find it. So far, here's what I can tell you:

The problem replicates on both RHEL 7 and CentOS 7.
If you install cleanly from the latest media (as of about 2 weeks ago), I/O performance looks great. (Kernel release: 3.10.0-327.el7.x86_64)
If you run "yum update" then after the updates have installed, I/O performance will go down the crapper: read performance drops by a little more than 10%; write performance drops by nearly 80%! (Kernel releases 3.10.0-327.18.2.el7.x86_64 and 3.10.0-327.22.2.el7.x86_64)
On systems, VMs where the updates have been installed, disabling kernel asynchronous I/O (KAIOOFF=1) seems to help, bringing read performance back to what I'd expect, and getting back about half the difference on write performance.

Note: the test I've been running is a large, single-threaded purge, starting from the same baseline and purging exactly the same records. To isolate read performance, I have a version of the purge that scans the table to find the records to be purged but doesn't do the actual purging. Here are the results of those tests:

Without updates, read-only: 18182.94 rows/second

With updates, read-only: 16170.75 rows/second
With updates, read-only, KAIOOFF=1: 18,389.99 rows/second

Without updates, full purge: 2010.49 rows/second
With updates, full purge: 438.46 rows/second
With updates, full purge, KAIOOFF=1: 1125.58 rows/second

[Still to test: Without updates, KAIOOFF=1]

Is This Thing On?

noreply@blogger.com (TGirsch) — Tue, 10 May 2016 19:34:00 +0000

In a move that should make all of you question Andrew's judgment, he's invited me to also contribute to the Informix DBA blog. So now you'll get erratic posting from two of the three fastest* Informix DBAs in the world.

Hopefully I'll be able to contribute something of value besides bad humor.

* - Fastest according to one extremely arbitrary contest several years and versions ago, in which only a small fraction of the world's Informix DBAs actually participated.

Resolving In Place Alters - Part 4, The Slot Table

noreply@blogger.com (Andrew Ford) — Thu, 29 Jan 2015 21:37:00 +0000

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:

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

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

Resolving In Place Alters - Part 3

noreply@blogger.com (Andrew Ford) — Fri, 23 Jan 2015 22:37:00 +0000

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 
   hexdata
from
   sysrawdsk 
where 
   pg_chunk = 4 and 
   pg_offset = 1000 and 
   offset = 64;

hexdata

34313037 30353632 36302020 20202020

Gives me the 16 bytes of data (in hex) that starts on the 64th byte of page 1000 in chunk 4.

We are making progress. Now that I can query the raw data to use to my advantage later, I need to figure out how to find the pages that belong to the table I'm trying to fix. sysmaster to the rescue yet again, this time in the form of the sysextents table.

select
   chunk,
   offset,
   size
from
   sysextents
where
   dbsname = "mydb" and
   tabname = "mytab"
order by
   offset asc;

      chunk      offset        size

          4      851706       77423
          4      929129       77423
          4     1006552       77423
          4     1083975       77423
          4     1161398       77423
          4     1238821       77423
          4     1316244       77423
          4     1393667       77423
          4     1471090       77423
          4     1548513       77423
          4     1625936       77423
          4     1703535      524288
          4     2227823      524288
          4     2752111      524288
          4     3276399      524288
          4     3800687      524288
          4     4324975      524288
          4     4849263      524288
          4     5373551      524288
          ....

In review, we now have:

A way to read the raw data pages from sysrawdsk
A way to find the pages that belong to our table

Now we need to find the data pages in these extents, back to good old sysmaster.

The sysmaster:sysptnbit table holds the partition bit maps, this data will tell us if a page we are looking at is a "Data Page with Room for another Row" (bitmap value of 4), a "Data Page without Room for another Row" (bitmap value of 12) or some page type we don't care about. Now would be a good time to mention that my row size is fixed with no variable length fields and one row fits completey on a page so there are no remainder pages. If you have either of these conditions, well things are going to be a little more difficult for you.

{ Partition Bit Maps }
    create table informix.sysptnbit
        (
        pb_partnum      integer,        { partnum for this partition    }
        pb_pagenum      integer,        { logical pagenum represented   }
        pb_bitmap       integer         { bitmap value for page         }
        );
    create unique index informix.sysptnbitidx on sysptnbit (pb_partnum, pb_pagenum);

Here things get a little annoying for us. The data in sysptnbit is referenced by logical page and the pages we are looking at in sysrawdsk are referenced by physical page. If you need a refresher course on how to convert a physical page reference (chunk and offset) to a logical page reference (partnum and page number) then here is my best attempt...

Logical page numbers in a partition are sequential starting with page 0 in the first extent. The second page in the first extent is page number 1, the third page in the first extent is page number 2 and so on and so forth. If the first extent has 1000 pages, the last page in the first extent will be logical page number 999 and the first page in the second extent will be logical page number 1000. The second page in the second extent will be page number 1001, the third page in the second extent will be 1002 and so on and so forth. We can use this logic and the sysmaster:sysptnext table to find the logical page number for any physical page.

Here is an example with chunk 4, page 5373552.

Step 1 - Find the first page of the extent that this page belongs to

select
   offset
from
   sysextents
where
   chunk = 4 and
   5373552 between offset and offset + size;
   
     offset

    5373551

Step 2 - Find the partnum and extent number that maps to this extent

select
   pe_partnum,
   pe_extnum
from
   sysptnext
where
   pe_chunk = 4 and
   pe_offset = 5373551; -- first page of the extent that page 5373552 of chunk 4 belongs to

 pe_partnum pe_extnum

    4194317         1

Step 3 - Find the total number of pages that belong to the extents created before this extent

select
   nvl(sum(pe_size), 0) pages
from
   sysptnext
where
   pe_chunk = 4 and
   pe_partnum = 4194317 and
   pe_extnum < 1;

           pages

           77423

Step 4 - We now know the logical page number for the first page of the extent that our page belongs to, just need to calculate our page number

Our Logical Page Number = 77423 + (5373552 - 5373551) = 77424

Taking our new found logical page address (partnum and pagenum) we can query sysptnbit and see if this is a page we are interested in (bitmap value 4 or 12)

select
   pb_bitmap
from
   sysptnbit
where
   pb_partnum = 4194317 and
   pb_pagenum = 77424;

  pb_bitmap

         12

One last thing to note before I conclude Part 3, if no bitmap row had been found then I would know that I am at the end of valid data for this extent and can stop looking at the pages it contains and move on to the next extent.

Next time - Part 4: How to figure out if the data page I just found has a pending IPA or not.

Now, have some ads. I insist.

Resolving In Place Alters - Part 2

noreply@blogger.com (Anonymous) — Thu, 15 Jan 2015 23:43:00 +0000

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 primary_key = row.primary_key
   updated = updated + 1

   if updated mod 1000 == 0:
      commit work;

if updated mode 1000 != 0:
   commit work;

Next I was able to find an SQL that could tell me how many pending in place alters a table had remaining. I used this information to stop updating when all of the pending in place alters had been resolved. Not the most exciting thing in the world, but it could be helpful if you have a bunch of IPAs only at the beginning of the table. I could see this happening with some kind of transaction table that get added to, but never updated. Anyway, that SQL looks like this:

select
   nvl(sum(pta_totpgs), 0)::integer pages
from
   sysmaster:sysactptnhdr h,
   sysmaster:systabnames t
where
   h.partnum = t.partnum and
   t.dbsname = "dbname" and
   t.tabname = "tablename"

The final and most interesting thing to come out of this failed experiment was throttling the updates if the server was getting busy. Before allowing the update loop to start a new transaction I checked to make sure the following conditions were met:

No Cleaner threads running
BTScanner is not scanning an index
No thread waiting on a log buffer
No backup (logical log or level 0, 1, 2) is running
Update Statistics is not running
The load average is below a certain threshold

Mostly we are trying to avoid overloading the I/O with these checks and I would pause all work until all of these conditions are met. Sure, I want to resolve these IPAs as quickly as possible, but not at the expense of overwhelming the engine and impacting production.

As you might of guessed the performance was crap. Apparently it takes a long time to update a billion rows one by one in a way that plays nice with the other SQLs. Even if I decided to not play nice and disable the throttling I averaged a rate of 547.3 rows updated per second, or just over 21 days of running non-stop before it was complete. Brute force was just not going to cut it and I'm doing a lot of work that doesn't need to be done. I don't need to update pages that don't have pending IPAs and I don't need to update every row on a page to fix pages with pending IPAs.

I wanted to find a better way. Specifically identifying only the data pages that have pending in place alters AND only updating the minimal number of rows to force the page to be rewritten.

In Part 3 I'll talk about reading the data pages directly from disk and how I plan to use this to my advantage. In the mean time, here is an advertisement to help keep the lights on.

For easier reading of informix-dba.com while on the can.

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

noreply@blogger.com (Anonymous) — Fri, 21 Nov 2014 20:08:00 +0000

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.)

Eric Vercelletto Asks and Answers the Question of "Where is Informix?"

noreply@blogger.com (Andrew Ford) — Fri, 21 Mar 2014 13:06:00 +0000

You may have already read this article by my friend and "Benchmark Guy", Eric Vercelletto, but it is so well done and contains so many great thoughts and observations that I think it needs to be posted here for you to read again (or for the first time).

Eric's Blog

Where is Informix?

IIUG2014 Conference Update

noreply@blogger.com (Andrew Ford) — Tue, 17 Dec 2013 14:03:00 +0000

I posted this on the IDS SIG yesterday. Here is it again in case you missed it and are interested.

The IIUG2014 CPC met at the JW Marriott in Miami, FL this weekend to prepare for the 2014 conference. I thought I would give an update on where we are at and pass along my experience with the city and hotel since this will be a new location for a lot of us in April.

The hotel is beyond nice. The place is 98.34% Marble and Mahogany and smells terrific. Yes, it is weird to compliment a hotel on their smell, but I thought this to myself each time I entered the lobby. It is the nicest hotel I have ever stayed in and unless I hit the Florida Lotto, it will be the nicest hotel I ever stay in. The hotel is not undergoing a remodel nor are they planning on any major construction during the conference (for those that attended the Overland Park conferences, this is important).

In terms of number of rooms, the hotel is smaller than we are used to. It is very possible that we will have more conference attendees than we have rooms. If you know you will attend IIUG2014 you should register ASAP to guarantee a room in the hotel and take advantage of the Early Registration pricing. We do not have an overflow hotel and other hotels in the area are at least $50 more per night.

In terms of conference space, the hotel is larger than we are used to. There are more session rooms and meeting space which will give us the ability to offer more sessions and tracks than we have in the past. Additionally, all of the conference space is contained on the 5th floor of the hotel and not spread out like it was in San Diego.

The hotel has 3 restaurants and a bar. Isabela’s is open for breakfast only, La Terraza Café is open for lunch and dinner (very good, except for the chicken sandwich) and the Trapiche Room which is a super small 4 or 6 table intimate fine dining restaurant that is open for lunch and dinner. Perfect if you want to propose or you are spending your 20 year wedding anniversary at the IIUG conference. The bar is called Drake’s and is reminiscent of a classic British pub.

The hotel is surrounded by non-conference activities if you want to get out at night or want to do something before or after the conference. Restaurants and shopping are within walking distance and there is a free trolley that runs every 15 minutes (approximately) that stops close to the hotel if you want to explore the city.

The hotel has a Spa if you are in to that kind of thing. You can get a massage, a facial, mani/pedis and paraffin hand treatment among other things. There is also a fitness center complete with a sauna in the locker room. Fancy. The pool is heated and is stainless steel (you will just have to see for yourself) and there is a hot tub/whirlpool. All of this is on the 7th floor.

The weather was beautiful and will be again in April with an average low of

70 F and average high of 85 F and very little chance of rain.

I took a cab to/from the Miami International Airport. The cost was about $28 (not including tip) and took about 30 minutes with no traffic.

We met with the Head Chef to address some of the complaints we have had with our Vegetarian and Kosher food offerings and to ensure that our high standard for meals is met.

That is about it for the hotel and city. We have a good idea of what sessions will be offered this year. If you submitted a proposal, you will be hearing from an IIUG2014 CPC member in the next week or so. As far as content goes, here is an idea of what you can expect:

Troubleshooting and Performance Tuning
IWA
TimeSeries
NoSQL/JSON
Development
Cloud (ER, HDR/SDS/RSS)
Security
Hands on Labs
Thursday Tutorials (Full and Half Day)

We have also left a few session spots open and we want you to tell us what to put there. There will be an email blast from the IIUG soon asking you to submit any requests for sessions that you would like to see at IIUG2014 to conference@iiug.orgwith a subject of SESSION REQUST and we will do what we can to find a willing presenter on the most requested topics that aren’t already covered. We can’t make any promises that your suggestion will be presented, but we will certainly try our best.

Very tentative schedule, similar to last year:

Sunday – Welcome Reception with food and open beer and wine bar
Monday – Breakfast and Lunch, AM Keynote, Conference Sessions, Hands on Labs, PM Event/Party with food and open beer and wine bar
Tuesday – Breakfast and Lunch, AM Keynote, Conference Sessions, Hands on Labs, PM Event/Party with food and open beer and wine bar
Wednesday – Breakfast and Lunch, Conference Sessions, Hands on Labs, No PM Event
Thursday – Breakfast and Lunch, Tutorials

Registration is open now at iiug2014.org. Early registration at a discount of $225 over the Regular conference price is open through January 15, 2014. Don’t forget that all IIUG members can save an additional $100 on Early and Regular registration through April 7, 2014 and that IIUG membership is free at www.iiug.org.

IBM Informix Request for Enhancement Tool

noreply@blogger.com (Andrew Ford) — Fri, 03 May 2013 18:56:00 +0000

I have no idea how IBM decides what new features to add to Informix, but I do know that we can now be part of the discussion by using the new Request for Enhancement tool (RFE Tool).

I took this for a spin today and I must admit this is a pretty interesting thing that you should check out. Not only can you submit your own requests for new features, you can view what everyone else has suggested and vote for what you want to have added.

There are a lot of good ideas in there and I really hope to see some of these feature requests in later releases.

Take the RFE for a spin today. Submit a request, it is fun.

To see the Informix specific RFEs, search under Brand: Information Management, Product Family: Informix.

IBM Informix Marketing: You've Come A Long Way, Baby

noreply@blogger.com (Andrew Ford) — Mon, 08 Apr 2013 19:38:00 +0000

2007

2013

When You Need to Store a String of Numbers in a CHAR Column

noreply@blogger.com (Andrew Ford) — Fri, 05 Apr 2013 20:44:00 +0000

Sometimes you have to store a string of numbers in a CHAR column. Probably because the string of digits represents an account number or something similar and storing as an INTEGER or BIGINT doesn't really make sense. The account number could have leading zeros that would be lost if stored as an integer. Parts of the account number could store special information, like positions 2,3 and 4 identify what department an account belongs to and it might be useful to be able to select digit_string[2,4]. There are plenty of reasons to store numerical data in a string.

What is the best way to ensure that all of the characters in the string are actually numbers?

This is what I do, is there a better way to do it?

alter table my_table add constraint check (replace(rtrim(digit_string), " ", "x")::bigint >= 0) constraint my_table_ck1;

insert into my_table (digit_string) values ("123456");

1 row(s) inserted.

insert into my_table (digit_string) values ("abc123");

1213: A character to numeric conversion process failed

insert into my_table (digit_string) values ("   123456");

1213: A character to numeric conversion process failed

update my_table set digit_string = "xyzpdq" where digit_string = "123456";

1213: A character to numeric conversion process failed

The constraint will try to cast the digit string to a BIGINT, if this works then all of the characters in the string are numbers. If it doesn't work we get an SQL error and the bad data never makes it into our database.

The replace(rtrim()) stuff attempts to capture leading white space in the digit string that would not cause the cast to a BIGINT to fail.

There are plenty of other ways to accomplish the same thing, but I like this way.

You could rely on the application to check the digit string before it inserts/updates the database, but I'm pretty sure this isn't the best way.

You could write a stored procedure that is run by insert/update triggers, but I don't think that is more efficient than the check constraint/cast to BIGINT method. This would have the benefit of being able to raise a user defined SQL error instead of the odd -1213 error, though.

For My 100th Post: IBM Informix 12.1 - It's Simply Powerful

noreply@blogger.com (Andrew Ford) — Tue, 26 Mar 2013 16:59:00 +0000

11:58 AM: Waiting for the IBM Informix It's Simply Powerful Webcast to start and on my screen I see IBM Informix 12.1, so I guess it is officially announced.

12:00 PM: Moderator is giving the rules and regulations of the Webcast. Questions will be answered after the Webcast.

12:01 PM: Chad Gates from Pronto Software, John Miller Informix Lead Architect and Sally Hartnell from IBM Marketing filling in for Jerry. Where's Jerry? He is unavoidably detained.

12:03 PM: 12+ Years of Informix Innovation with IBM

12:03 PM: Over 190 new partners in 2012

12:04 PM: Overview of the new stuff in 12.1. Cloud, Easy of Use, Warehouse, Sensor Data Management and something else I missed

12:05 PM: TimeSeries for Sensor Data. 5x Performance using 1/5 the resources as the competition

12:07 PM: Compression: Reduces storage and improves performance.

12:08 PM: JM3 talking about compression now. NEW! Index compression. NEW! Blob compression.

12:10 PM: NEW! Automatic table compression

12:11 PM: NEW! Primary Storage Manager replaces ISM for more backup solution options

12:12 PM: Chad from Pronto Software now talking about their EVP experience.

12:13 PM: Pronto has an ERP product that embeds Informix and Cognos. Informix initially picked for the OLTP capabilities. Informix 12.1 improves OLTP performance and OLAP performance benefiting from Informix Team working closely with the Cognos Team.

12:26 PM: Pronto experiences massive performance gain when concurrently running OLTP and OLAP on 12.1 over 11.x

12:28 PM: "Informix Warehouse Accelerator gaining worldwide traction to accelerate warehouse queries up to 100+ times"

12:29 PM: Back to JM3 on IWA improvements. NEW! Trickle Feed (cool) can now have real time analytics vs. refreshing the entire warehouse. NEW! Automated Partition Refresh. NEW! IWA and OAT integration.

12:31 PM: NEW! IWA and TimeSeries integration. IWA analytics over TimeSeries data.

12:32 PM: Flexible Grid/ER - NEW! ER no longer requires a Primary Key.

12:34 PM: Execute SQL over the grid - Query Sharding, that's sharding with a D.

12:35 PM: Talking about Hypervisor edition for Virtual/Cloud based deployments.

12:36 PM: Informix Genero accelerates new generation of mobile and cloud-based apps.

12:36 PM: Sally: Informix integrated with the IBM Mobile Database. Sync mobile db data with Informix backend.

12:37 PM: JM3: NEW! Mobile OAT for your phone or tablet

12:38 PM: Improved OAT out of the box experience, OAT GUI deployed as part of Informix install

12:39 PM: Sally: Smart Choice of ISVs and OEMs. Small footprint, silent install, up and running in minutes, 0 administration, autonomics. NEW! Dynamic ONCONFIG, Self Healing, Self Optimizing

12:40 PM: About to wrap up? Already? Oh, right. Q/A at the end. I want MOAR new features :)

12:41 PM: Bundling of Cognos licenses with new Advanced (Worgroup/Enterprise) Editions

12:41 PM: IIUG 2013 April 21-25, 2013 San Diego, CA

12:43 PM: Q/A starts.

12:43 PM: "Is compression available in Workgroup?" Sally says Compression included in Advanced Enterprise, available for purchase in Enterprise.

12:44 PM: "64 bit OAT?" JM3 says currently only 32 bit, but you can run 32 bit version on Windows 64 bit. Looking to have a 64 bit version for Windows in the future.

12:45 PM: "Is OAT faster in 12.1?" JM3 says ability to run update stats on sysmaster will allow OAT to run faster

12:46 PM: "Is Pronto using compression?" JM3 says no, perf gains are without compression

12:47 PM: "New tools to migration FROM Oracle?" JM3 says yes, a lot of technology added to assist in migrations.

12:48 PM: "Will Mobile OAT work with my 11.x server?" JM3 says yes

12:48 PM: "Where can I find more info about the new editions?" Sally says go to ibm.com/informix and view the new brochure. More detail: google Carlton Doe Informix Editions or google ibm software announcement 213-156

12:50 PM: "Any plans to do a benchmark?" Sally says the prefer industry specific real world benchmarks with their customers. Soon to publish a Meter Data Management benchmark.

12:52 PM: "Is ontape still supported?" JM3 says ontape and onbar still supported in 12.1. onbar just improved with PSM.

12:53 PM: "Can I get Congnos express bundled instead of the full Cognos?" Sally says no.

12:54 PM: "What do I need to do to use the compression features?" Sally says compression included in Advanced Enterprise, add on for Enterprise.

12:54 PM: "What is the #1 thing to remember from this webcast?" JM3 says the great improvements in OTLP/OLAP performance.

12:55 PM: "Is OAT built using a new version of PHP?" JM3 says yes, OAT uses a later version of PHP.

12:56 PM: "Tell us more about IBM Mobile" Sally says it is included with all for-pay versions of Informix and is a secure persistent storage for data on a device that allows backend syncronization to an Informix DB.

12:57 PM: "Can 12.1 replicate TimeSeries data?" JM3 says, yes TimeSeries can now be replicated via HDR/SDS/RSS, etc.

12:58 PM: Sally notes the great attendance to this Webcast and gives a shout out to IIUG 2013 (thanks Sally)

12:59 PM: End of Webcast, perfectly timed. Replay of webcast will be made available online.

Psuedo strtok in SPL

noreply@blogger.com (Andrew Ford) — Thu, 07 Mar 2013 22:36:00 +0000

I needed a way to extract the individual words from a sentence stored in a single character field. After some failed google searches and no desire to install a Datablade or write a C UDR for something that doesn't need to have killer performance, I decided to write my own quick and dirty SPL function.

my_strtok(str, delim, token_num) will take a string, break it into individual tokens based the delimiter and return the Nth token of the string.

Running this:

execute function my_strtok("How now brown cow", " ", 3)

Would return the third token:

brown

Here is the code for my_strtok(), comments welcome on anything I might have missed in the logic. And when I say it is slow, I just mean it could be done in a different way and perform more efficiently, but for what I needed it works.

create function my_strtok (str lvarchar(2048), delim char(1), token_num smallint)
returning lvarchar(2048) as token;

        define str_len integer;
        define start_pos integer;
        define stop_pos integer;
        define cur_token_num integer;


        -- initialize start position and current token number to 1
        let start_pos = 1;
        let cur_token_num = 1;

        -- remove any leading delimiters from the input string
        let str = ltrim(str, delim);

        -- save the input string length so we don't have to recalculate it later
        let str_len = length(str);


        -- find the start of the token we want to return

        -- while there is still more string available to process
        while (start_pos <= str_len)
                -- if the current token number is the token we want, stop looking
                -- for a start position
                if (cur_token_num = token_num) then
                        exit;
                end if;

                -- increment the start position to the next character
                let start_pos = start_pos + 1;

                -- check to see if the current character in the string is a delimiter
                if (substr(str, start_pos, 1) = delim) then
                        -- we have found the next token
                        let cur_token_num = cur_token_num + 1;

                        -- advance the token start position past any repeating delimiters
                        while (start_pos <= str_len)
                                let start_pos = start_pos + 1;

                                if (substr(str, start_pos, 1) != delim) then
                                        -- there are no more repeating delimiters
                                        -- stop looking for repeating delimiters
                                        exit;
                                end if;
                        end while;
                end if;
        end while;


        -- we now either have the start position of the token we are looking for
        -- or we did not find the token we were looking for
        -- if we did not find the token, return NULL
        -- if we did find the token we were looking for, find the end of the token

        if (cur_token_num = token_num) then
                -- we found the token
                let stop_pos = start_pos;

                -- while there is still string to process try to find the end of our token
                -- if we run out of string before we find the next delimiter then
                -- our token ends where the string ends
                while (stop_pos <= str_len)
                        let stop_pos = stop_pos + 1;

                        if (substr(str, stop_pos, 1) = delim) then
                                -- we found the end
                                let stop_pos = stop_pos - 1;
                                exit;
                        end if;
                end while;

                -- return the found token
                return substr(str, start_pos, stop_pos - start_pos + 1);
        else
                -- the token was not found
                return NULL;
        end if;
end function;

execute function my_strtok("Simple test", " ", 1);

token  Simple

1 row(s) retrieved.


execute function my_strtok("Simple test", " ", 2);

token  test

1 row(s) retrieved.


execute function my_strtok("    Leading delimiters", " ", 1);

token  Leading

1 row(s) retrieved.


execute function my_strtok("Repeating       delimiters", " ", 2);

token  delimiters

1 row(s) retrieved.


execute function my_strtok("Token not found", " ", 4);

token

1 row(s) retrieved.


execute function my_strtok("Should have checked for invalid input", " ", -1);

token

1 row(s) retrieved.


execute function my_strtok("Invalid input works, but is unecessarily slow", " ", -1000);

token

1 row(s) retrieved.


execute function my_strtok("Empty delimiter defaults to space, convenient", "", 6);

token  convenient

1 row(s) retrieved.

Where Are They Now?

noreply@blogger.com (Andrew Ford) — Tue, 05 Mar 2013 18:11:00 +0000

Where did I find this picture, can you identify anyone in this picture and what is the guy on the left looking at on the ground?

Say "CHEESE"

From the SIGs - LTXHWM and LTXEHWM

noreply@blogger.com (Andrew Ford) — Mon, 04 Mar 2013 22:17:00 +0000

John Adamski posted a question to the IIUG SIGs about how to identify a session that caused the long transaction that eventually put his system in a Blocked:LONGTX state. A few of us came back with some responses, but it wasn't until John Miller III from IBM and Informix Fun Facts replied with "finding the session that caused your long transaction isn't very useful, you need to prevent this situation from happening with the LTXHWM and LTXEHWM ONCONFIG parameters" that I realized these config parameters are typically underutilized.

LTXHWM and LTXEHWM

LTXHWM is the percentage of logical log space a transaction can span before it is declared to be a long transaction and is rolled back by the engine.

John points out that this rollback only affects the session being rolled back (well, excluding any extra strain on the system rolling back a long transaction creates), nothing else is blocked.

LTXEHWM is the percentage of logical log space a transaction can span before the engine decides we are in a bad enough situation that we need to block all other modifications to try and ensure we have enough logical log space to complete the roll back. This does affect other sessions and if your system is in a Blocked:LONGTX your users will be very unhappy and you are likely going to have a very bad day.

Your day will get infinitely worse if even after blocking other sessions you do not have enough logical log space to complete the rollback of the long transaction. You can read about recovering from a long transaction hang in the manuals if you want. I warn you, it involves a point in time restore or a call to IBM Informix support.

The Default Values

Out of the box LTXHWM is 70 and LTXEHWM is 80. Are these too high? They probably are if you have significant logical log space.

On one of my instances I have 16 GB of logical log space. This means that one of my users can start a transaction that spans 11.2 GB of logical log space before he gets the boot and the engine starts rolling back his long transaction. That is going to be a lot of rolling back and if it took this long transaction 3 hours to get to the point it needed to roll back I can expect the rollback to last at least 3 hours.

Avoid the One Size Fits All LTXHWM/LTXEHWM Recommendations

I had always believed that the 70/80 defaults were too high,and I've always heard that a better alternative is 50/60 and admittedly that is what I have always set my values to.

I'm rethinking that now. I don't know why it took me this long to rethink this, probably because I haven't had any problems with long transactions lately so I've been thinking about other things. One thing is for sure, a LTXHWM of 50 is still way too high for my system.

This is an OLTP system with no long running transactions and a lot of logical log space configured so we can run for at least 48 hours without needing to back up a logical log in case we have a TSM outage.

My LTXHWM should be a lot lower, probably something as ridiculous sounding as 5. I know the kind of transactions that run on my system and no single transaction should span more than 1 GB of logical log space and if it does, something is wrong. I want to kill that transaction ASAP so I only have a performance hit while rolling back 1 GB of logical logs instead of 11 GB.

I think my LTXEHWM setting of 60 is OK, maybe it could even go a little bit higher. The only reason I would want to block my engine to allow a transaction to rollback is if I thought I was in danger of not being able to complete the rollback because of limited logical log space. Setting this ONCONFIG lower doesn't make any sense.

Hot Topic - SELECT FOR UPDATE/WHERE CURRENT OF

noreply@blogger.com (Andrew Ford) — Fri, 01 Mar 2013 21:02:00 +0000

"Holy Cow, two blog posts in one day!" - Harry Caray

Ben Thompson over at Informed Mix currently wrote about using "select for update/where current of" syntax and in the mother of all coincidences one of the developers that writes code that hits my Informix engines came over to tell me about the evolution of performance improvements he went through to speed up a bulk data delete application. Here is his story, from static SQL all the way to prepared statements using the "select for update/where current of" syntax.

First, the formalities. This particular developer is writing an application that will delete a bunch 'o data from a table. Since he is a nice guy he is writing the application in a way that will avoid long transactions and not hold too many locks at once.

Basically he is doing one of these:

select primary key fields of rows to delete

begin work

for each row:
   delete by primary key

   if time to commit:
      commit work
      begin work

commit work

In his first attempt, he built and executed a static delete sql string for each row to delete.

delete from table where field1 = "ABC" and field2 = "123";
delete from table where field1 = "DEF" and field2 = "456";
delete from table where field1 = "GHI" and field2 = "789";

Of course this works, but has the unpleasant side effect of being the worst way possible to do this since the engine has to parse, optimize and execute each statement every time.

This yielded a result of 463.18 deletes per second.

In his second attempt, he used a prepared statement for the delete which is better because the engine only has to parse and optimize the statement once.

delete from table where field1 = ? and field2 = ?;

This gave a result of 1016.50 deletes per second.

In his third (and currently, final) attempt he took advantage of the "select for update/update where current of" functionality.

declare cursor cur for select 1 from the_table where date_field < today - 180 for update

open cur

begin work

for each row in cur:
   delete from the_table where current of cur

   if time to commit:
      commit work
      begin work

commit work

This was a huge boost to performance since the engine already knows where the row we want to delete lives and we don't have to go through indexes and data pages to find it again.

Final results were 1923.00 deletes per second.

Informix News of the Weird

noreply@blogger.com (Andrew Ford) — Fri, 01 Mar 2013 16:21:00 +0000

Informix Engine Defies Laws of the Universe

I recently inherited an Informix engine from another department as part of a server migration and upgrade to Innovator-C. In my life long pursuit of finding unattended Informix engines with ridiculously long up times I did an 'onstat -' to see how many days this engine had been running.

[informix]$ onstat -

IBM Informix Dynamic Server Version 10.00.UC3R1   -- On-Line (Prim) -- Up 869 days 12:02:13 -- 2423888 Kbytes

Hey, 2.38 years without an engine bounce, not too shabby! When I started to brag about the resiliency of Informix to the previous owner of this engine who deals mostly with other database engines he came back with, "Well, that IS pretty amazing considering the server was rebooted a year ago"

[informix]$ uptime
 09:16:11  up 372 days,  9:01,  2 users,  load average: 0.30, 0.41, 0.43

Informix really is amazing. Show me any other engine that can continue to run when the server is offline. What is more impressive is this was version 10, I can only imagine what the Informix Developers have in store for us in Centaurus.

As Seen on TV

The marketing team is at it again securing some SWEET product placement on UK Television. Thanks to Mike Aubury for catching the Informix Umbrella Lady on last night's showing of "Hugh's Fist Fight" on Channel 4. Take that Iron Man 2.

Oh, Informix Umbrella Lady on Channel 4, what is thy name?

Database Administrators Gettin' Freaky

According to The Huffington Post, Database Administrators are the 9th most likeliest people to hook up (in the biblical sense) with co-workers.

I didn't believe it either, but it is on the internet so it must be true because the internet doesn't lie.

Record Number of People Hate Money

Quite possibly the weirdest thing of all, people not signing up for a FREE IIUG membership to save $100 USD on conference registration. I mean, hey, as a IIUG 2013 CPC member thanks for the $100 to help fund IIUG 2014, but c'mon. You should really take advantage of that discount. Unless of course you are not taking it on purpose so you can cleverly get your company to fund the IIUG for an extra $100.

Register for IIUG 2013 at iiug2013.org

Have You Heard? It's Simply Powerful.

noreply@blogger.com (Andrew Ford) — Tue, 05 Feb 2013 21:52:00 +0000

Of course I'm talking about the March 5th, 2013 IBM Informix Webcast that will talk about the latest release, but if you're reading this on Planet IDS you've already heard about it from Fernando, Eric and Gary and you can stop reading right now.

If this is the first you've heard of this event, then read on...

If you are not part of the EVP then you are likely wondering what will be included in the 12.10 release (I think that is what they're going to number it) that is code named Centaurus. If this describes you, then you will want to attend.

Here are the details:

The New IBM Informix: It's Simply Powerful
Date: Tuesday, March 5, 2013
Time: 10:00 AM PST

IBM Informix is exceptional database software that is well known for its superior
performance, high availability and efficiency, minimal complexity and lower computing
costs to power online transaction processing (OLTP) and decision support applications
for businesses of all sizes. Informix incorporates design concepts that are
significantly different from traditional relational platforms, resulting in extremely
high levels of performance and availability, distinctive capabilities in data
replication and scalability, and minimal administrative overhead.

The newest release offers clients and partners the ability to take their business into
the future, right now! Whether you are looking for help maximizing your daily business
activities through more efficient operational analytics; deploying applications to the
private cloud; working with sensor or meter data; or just looking to increase your
productivity and usability, the new release brings you a cost-effective, simply
powerful solution that addresses all your data management requirements.

Register Today