Informix DBA

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

eSeminar - Informix and Analytics/Data Warehouse

noreply@blogger.com (Andrew Ford) — Wed, 12 Dec 2012 14:20:00 +0000

IBM is sponsoring a eSeminar (webinar, webcast, whatever) titled "How to Lower Costs by Combining Analytical & Transactional Applications" on December 18, 2012 at 12 PM ET/9 AM PT.

If you read into the details, this is an Informix pitch, which is nice:

The holy grail of analytic technologies has long been the promise of predicting future outcomes by analyzing data "as things happen", allowing for rapid business insights and responsiveness. Challenges still remain, however, it an IT world faced with big data, cloud-hosted applications, and enterprise data warehouses.

Philip Howard, Research Director of Data Management for Bloor Research, has published many research papers, articles, and blog posts on the topics of analytics and analytic applications. His recent analysis of IBM Informix software led Philip to conclude: "… for hybrid environments, IBM Informix software has a number of unique capabilities that cannot be matched by either conventional data warehousing vendors or traditional data warehouses."

In this eSeminar, Philip will discuss various types of analytics and data warehouse environments and the facilities required to support each. There will also be an overview of Informix functionality from Kevin Brown, Informix Database Chief Architect, IBM.

I assume this presentation will cover IWA or TimeSeries and how awesome they are and will hopefully reach some people who aren't already in the know about Informix like yourself.

Register for this free event today

Informix User Group Meetings in North America

noreply@blogger.com (Andrew Ford) — Tue, 27 Nov 2012 21:12:00 +0000

If you are in North Texas, Chicago, Colorado or the Great Northwest and you are an Informix fanatic (why else would you be reading this?) then you need to plan to attend the Local Informix User Group meetings scheduled for the very near future.

Colorado Informix User Group - November 29

Great Northwest Informix User Group - December 4

Chicago Informix User Group - December 6

North Texas Informix User Group - December 11

Colorado, Great Northwest and North Texas all seem to have a similar tentative agenda:

Come and see how IBM is helping Informix evolve.

Come and meet peers around the area.

Come and hear from experts and each other.

Tentative Agenda

'Stories from Clients - the good, the bad and the ugly' - Mark Scranton, principal, Mark Scranton Group
Warehouse Accelerator review and demo - Carlton Doe, IBM
A Sneak Peek for Centaurus - the next major Informix release - IBM

Complimentary Lunch will be served

Chicago has a different (non tentative) agenda:

Come and see how IBM is helping Informix evolve.

Come and meet peers around the area.
Come and hear from experts and each other.

Agenda

Breakfast (provided by IBM) and networking
Upgrading to IDS 11.7, Best Practices, Dr. Pradeep Natarajan - IBM
Informix in the Cloud, Nagaraju Inturi - IBM
Lunch (provided by IBM)
Storage features in IDS, Dr. Pradeep Natarajan - IBM
Best Practices in High Availability. Nagaraju Inturi - IBM

I'm very excited to see some activity in the North American User Groups since I live in North America and there hasn't been a lot of activity outside of the Southeastern Informix User Group. Maybe I just don't hear about them down here in Austin, TX. Thanks to Tom Rieger and Rob Beal from IBM who I assume are helping to get these going since they are the contacts for questions about these meetings, sponsors Xtivia and Kazer for sponsoring the meetings and thanks to the speakers Mark Scranton, Carlton Doe, Pradeep Natarajan, Nagaraju Inturi for donating their time to these events.

Ok, This is Pretty Cool

noreply@blogger.com (Andrew Ford) — Mon, 12 Nov 2012 16:08:00 +0000

IBM Helps Oncor Tackle Big Data to Deliver Advanced Power Grid

The article talks about an Oncor, an energy company in Texas that services more than 3 million homes with electricity, that is using Informix (presumably TimeSeries, but not mentioned specifically) to do all kinds of bad ass things.

The smart grid is based on IBM hardware and software, including IBM POWER7 Servers and IBM Informix database software to manage the massive amounts of data produced by the smart meters. As a result of using Informix software instead of their prior data management solution:

Meter data was loaded 20 times faster, and query performance increased up to 30 times faster. This is significant because faster processing of meter data enables faster business processes that use that data (such as meter-to-cash), and faster data analysis.

Data storage needs were reduced by 70 percent and remained linear over time, allowing for predictable data storage requirements and significant cost savings.

I wrote a couple of blog posts in September 2011 after attending an IBM Smart Metering Summit in Austin, TX, if you'd like to check them out.

Smart Metering Data Summit
This Smart Meter Stuff is for Real

Now, the big question is: Will we see or hear about this outside of ElectricEnergyOnline.com and are there more TimeSeries deals in the pipeline? That would be great for Informix, like I said in 2011 this smart meter stuff is for real and IBM definitely has the superior product to handle this kind of data.

Dynamite Roll

noreply@blogger.com (Andrew Ford) — Fri, 09 Nov 2012 16:12:00 +0000

On my way to work this morning, there was a news story on the radio about a new cologne called Sushi. The product description explains this unique product:

Not what you think it would smell like! Demeter's Sushi is the fresh scent of just cooked sticky rice and straight from the seaside seaweed, laced with hints of ginger and lemon essences. Simply delectable... after all, if it smelledlike fish, would you wear it?

This reminded me of an old joke about Informix Software (the company) and how they marketed their products:

If Informix sold sushi, they would call it cold, dead fish.

Now, I know that joke has been told many times for many different companies, but the first time I heard the joke it was in reference to Informix so for me, they get to own the joke.

I then started to think about two troubling conversations I recently had.

The first was with someone who emailed me at informix-dba.com asking if I knew of any Informix consultants looking for a short term gig. During our conversation he wanted to ask some questions about Informix since he finally found a "real human in Informix-land" he could talk to. That statement itself is pretty sad to hear, I wonder if he ever has any trouble finding real life Oracle, SQL Server or MySQL humans to talk to? Doubt it, but Chetu Pap can't seem to find anyone to fill his Oracle positions since he keeps posting them in the Informix DB LinkedIn group, so maybe I'm wrong. Anyway, here are the 3 questions he asked.

Is Informix in active use today?

I answered yes and without naming names (can we name names?) I told him about how Informix was still being used all over the world from large retail stores down to smaller sized companies like the one I work for. Yes, Informix is in active use today.

Is IBM supporting it?

I answered yes and went into the major releases from 9.4 to 10.0 to 11.1 to 11.5 to vNext that is in the works and the new fix packs that are released every quarter and the emergency releases and patch releases that are available in between fix packs and the amazing job that Informix support does when I call with a question. Yes, IBM is very much supporting Informix.

Is it generally hard to find an Informix resource to deal with a legacy system?

I don't know, I am not usually looking for Informix resources, but I do know they come to our conferences and I see them on the Informix SIGs and there are companies out there that sell Informix consulting. Hey, what do you mean by LEGACY SYSTEM anyway? I threw up in my mouth a little bit.

The second conversation was with my twenty-something year old brother in law. He's a really smart kid finishing up his Computer Science degree at the University of Texas at Austin and had been working as a research assistant with a professor of his doing something with Oracle. We're talking about his job and my job and he flat out asks me, "Is Informix dead or what? I told my professor that you were an Informix DBA and he says nobody uses that anymore." More vomiting ensued.

I'm no longer on the IIUG Board of Directors, so I don't get to meet with the IBM executives and marketing people and hear about what is being done to fix these perceptions. I do know that IBM is a very generous Platinum Sponsor of the IIUG conferences and if I google Informix there was an Adwords ad for some Informix marketing stuff and if I go to ibm.com/informix there are a lot of things about TimeSeries, Genero, Warehouse Accelerator and Blink and there was/is the Academic Initiative to help promote Informix in the university and I get the newsletter from Tom, but what else is being done? Is it enough?

Maybe the push is in other areas of the world and I'm just not seeing it. Are you in another part of the world and seeing anything?

There is a comments section, leave one if you've seen something or email me at informix-dba.com. If you work for IBM Marketing/Advertising and would like me to talk about the good things you are doing, email me and I'll be glad to post them.

I know Informix is actively being used and I know Informix is continuously being improved, why doesn't the rest of the world know too?

IIUG 2013 Updates

noreply@blogger.com (Andrew Ford) — Thu, 08 Nov 2012 23:47:00 +0000

Some things to note about IIUG 2013:

Registration is now open - Register before January 17, 2013 to get the $225 Early Registration Discount.

Register as an IIUG member and receive and additional $100 discount.

The IIUG 2013 Cost Justification Document is now available if you need some help convincing your boss that IIUG 2013 is an effective way to spend your 2013 training dollars.

In previous years the Tutorials were held on Sunday before the conference started and cost extra, this year the Tutorials will be held on Thursday after the conference and are INCLUDED for all paid attendees.

The Tutorials are expected to fill quickly so reserve your spot as soon as possible, if you register early before the Tutorial schedule is released you will be able to return to your registration and make your Tutorial suggestions.

The Conference Planning Committee (CPC) is still accepting session abstracts if you would like to present at IIUG 2013 and receive a complimentary conference pass. Submit your idea to iiug2013.org/speakers before November 15, 2012.

It is going to be another great conference in beautiful California in 2013. The CPC is busy planning Sessions, Food, Parties, Exhibitors, Keynotes, Tutorials, Certifications and everything else, I hope to see you there!

Fix list for Informix 11.70.xC6

noreply@blogger.com (Andrew Ford) — Thu, 25 Oct 2012 18:15:00 +0000

As Fernando Nunes already pointed out, 11.70.xC6 was released October 23. New releases always pique my interest and the first thing I do is checkout the What's New page in the documentation. After I sift through the new features list and mentally classify them into "Yes! Finally", "Useful", "Meh" and "I'll Bet Someone Needs That, But I Don't" groups I like to take a look at the non feature additions to the release, the bug fixes.

You can check out the 11.70.xC6 fixes here.

I always have mixed emotions when I look at this list. First I quickly scroll down to see how long the list is, it is always a long list, and I wonder how in the world is my engine that is multiple fix packs behind is even still running, much less functioning, if I have all these bugs?!

Then I start to look at the list a little more closely and realize most of the bugs are cosmetic or pretty rare and my heart rate settles more as I check out my year long engine uptimes and try to think of the last time we had a major problem that was caused by an Informix bug (I want to say it was 2006).

What do you think? Is the long list of Informix bug fixes an indication of releasing features without adequate testing or is it a positive sign that IBM can devote resources to fixing minor problems like "THE IMPLICIT CAST BETWEEN CHAR AND DECIMAL IN UNION ALL SELECT WITH BINARY OPERATION PLUS 0 RETURNS ERROR -308"?

IIUG 2013 - Going Back to Cali

noreply@blogger.com (Andrew Ford) — Thu, 25 Oct 2012 15:24:00 +0000

IIUG 2013 - The Premier Technical Conference for Informix Fanatics - will return to beautiful Southern California and the San Diego Mission Valley Marriott April 21 - 25, 2013.

The IIUG 2013 Conference Planning Committee is currently accepting presentation abstracts until November 15, 2012. If you would like to speak at this year's conference and receive a complimentary conference pass you can submit your idea to iiug2013.org/speakers and find more information about speaking at iiug.org.

All that is required at this time is an idea for a session that you can put into a few words and the desire to speak, full presentations won't be due until much later so don't think you have to have the entire thing ready in the coming weeks.

There is an exciting change to the conference this year. Tutorials have usually been held on the Sunday before the conference and cost extra. This year the tutorials will now be held on the last day of the conference and are INCLUDED at no extra charge for all PAID attendees. Speakers who receive a conference pass will have the opportunity to purchase half or full day tutorials like previous years.

Keep up to date with all things IIUG 2013 at iiug2013.org, like IIUG 2013 on Facebook and follow IIUG 2013 on Twitter.

Using Data Distributions to Write Better Code

noreply@blogger.com (Andrew Ford) — Mon, 22 Oct 2012 21:48:00 +0000

I was recently asked to go back and look at some code to see if there was a way to speed things up because over time performance had decreased to unacceptable levels. Because I'm a DBA first and a developer second, I started with the SQLs, tables and indexes to see if there was anything obvious. There wasn't, but I did find the following query being run a lot and it looked funky so I started to investigate.

select first 1
   col1
from
   tab1
where
   col1 = "A" or
   col1 = "AB" or
   col1 = "ABC" or
   col1 = "ABCD" or
   col1 = "ABCDE" or
   col1 = "ABCDEF" or
   col1 = "ABCDEFG" or
   col1 = "ABCDEFGH" or
   col1 = "ABCDEFGHI"
order by
   length(col1) desc

First of all, what is going on here?

There is a table, called tab1 (all names have been changed to protect the innocent) that has a char(n) field named col1 that holds some strings of varying lengths. The SQL is trying to find the row in tab1 with the col1 field that is the longest substring of "ABCDEFGHI".

There is a unique index on col1 and the query runs fast enough, but this SQL is being run a lot in a tight loop for many different strings and the total run time for the routine that contains this SQL is 25 minutes. Even the smallest improvement should give us noticeable performance gains.

As Art Kagel has shown many times, there usually are multiple ways to write the same SQL. So that is what I tried first using the matches function, even though it basically has the same query plan as the original SQL and probably won't improve things.

select first 1
   col1
from
   tab1
where
   "ABCDEFGHI" matches(trim(col1) || "*")
order by
   length(col1) desc

Well, unfortunately I was right. Even though the SQL is a little cleaner (debatable) it took almost twice as long to complete the entire routine.

So now we have to actually apply some thought to this problem, oh well.

If you were able to look at the data in this table and the strings that we are trying to match, you would see that more often than not the match is long and not short. This is nice and we can use this to our advantage if we break the single SQL into multiple SQLs for each "or" in the where clause

select col1 from tab1 where col1 = "ABCDEFGHI";
select col1 from tab1 where col1 = "ABCDEFGH";
select col1 from tab1 where col1 = "ABCDEFG";
select col1 from tab1 where col1 = "ABCDEF";
select col1 from tab1 where col1 = "ABCDE";
select col1 from tab1 where col1 = "ABCD";
select col1 from tab1 where col1 = "ABC";
select col1 from tab1 where col1 = "AB";
select col1 from tab1 where col1 = "A";

and then run the SQLs in this order until we execute a query that returns a result, stop right there and that will be our longest match.

I can't fault the thought process behind the original SQL, without knowing what the data looks like you could assume that a single SQL would run faster than the same SQL split up into 9 pieces because of the networking overhead and SQL parsing and optimizing the engine has to do is 9 times as much, but when you look at the data and see that more often than not you are only going to be executing 1 to 3 of those 9 SQLs then the performance gain outweighs the overhead.

Making this code change reduced the routine run time down to 3 minutes and that was the end of this problem.

This made me remember another time when knowing what your data looks like can help you improve performance. If you have an application that needs to perform an upsert to a table (attempt to update a row, and insert a new row if it doesn't exist) then you can do this two ways.

First you can try to update the row and if no rows are updated you can insert the row or you can do the opposite and try to insert the row and if you get a primary key violation you can update the row.

Which option is best, if any? It depends on what is going to be in that table and what you're trying to upsert.

If more than half of the upserts are going to result in a row being updated, then try the update first and do the insert in the rarer cases and (duh) if more than half of the upserts are going to result in a row being inserted then try the insert first.

I know, it seems obvious, but I've seen the exact opposite implemented before simply because the author didn't know what the data would look like (or maybe the data distributions changed, who knows) so I thought I'd mention it.

Developers Do the Darndest Things - Episode 1

noreply@blogger.com (Andrew Ford) — Fri, 06 Jul 2012 04:26:00 +0000

We had an issue today where some code went crazy and started slamming one of engines with new connection requests, which led to all kinds of fun problems with dynamically allocated shared memory segments, out of memory problems and eventually an engine reboot.

I was taking a look at the code for the application to try and figure out what went wrong and I saw something like the following. It wasn't the cause of the problem, but I don't think it helped.

   for i = 0; i < 5; i++:
      ret = connect_to_database();

      if ret = SUCCESS:
         break;

   if ret != SUCCESS:
      print "unable to connect to database"

Well, it was something like that. Basically try to connect to the database and retry a few times before giving up and throwing some kind of error.

So, what's the big deal? It looks reasonable enough. In my opinion there really isn't much use in retrying the connection attempt in a loop like this unless you're going to go to sleep in between attempts.

Look at what happens, you just got told your attempt to connect failed. What are the chances the problem is resolved a nanosecond later when you loop back and try to connect again? Almost zero. All you've done is throw an additional 4 connection requests at the engine while it is having some kind of a problem, possibly making the problem worse (especially if you have multiple apps/clients doing the same thing).

I realize you can't sleep forever, but sleeping here for even 1 second would have spread the 5 connection attempts over 5 seconds instead of 5 nanoseconds and has a much better chance of actually doing what it was intended to do by recovering gracefully from an intermittent DB problem rather than causing more problems.

<p>&amp;amp;amp;lt;p&amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;lt;p&amp;amp;amp;amp;amp;amp;amp;gt;I have the same combination on my luggage&amp;amp;amp;amp;amp;amp;amp;lt;/p&amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;lt;/p&amp;amp;amp;gt;</p>

Ugh. Thanks for the Bad News, Kernoal

noreply@blogger.com (Andrew Ford) — Thu, 05 Jul 2012 16:54:00 +0000

Kernoal Stevens commented on another blog post that in the later versions of Innovator-C not only do we have the new 1 core limit, but Innovator-C will no longer support ER or HDR.

What a bummer, here is the README from the Innovator-C download page that confirms this new limitation:

Changes to Replication in Informix 11.70.xC5 Innovator-C edition

For 11.70.xC5, Informix Innovator-C edition no longer supports Enterprise Replication (ER) or High-availability data replication (HDR). If you were using replication in an older version of Innovator-C, you must disable replication after upgrading to 11.70.xC5.

If you want to continue using replication in Informix 11.70.xC5, you can purchase the Choice, Growth or Ultimate edition. 

Removing Enterprise Replication 

If Enterprise Replication was defined for the previous version of the server, remove replication by running the following command:  

cdr delete server  --force

Removing HDR

If  High-availability data replication was defined for the previous version of the server, convert the server to a standard server by running the following command:

onmode -d standard

This just makes me sad. I understand that IBM needs to make a profit and to do that they can't be giving away everything for free. I get that, I really do, but HDR was the kind of feature that made Innovator-C stand out when compared to other free RDBMS options and now it is gone. Oh well, thanks for the free parking while it lasted.

You must be the Monopoly Guy

IIUG Insider Issue #144 - June 2012

noreply@blogger.com (Andrew Ford) — Mon, 02 Jul 2012 14:40:00 +0000

If you don't get the IIUG Insider delivered directly to your inbox, you may not be aware that June Insider has just been published.

Here is the editorial, which I always find interesting and sometimes harsh, but fair.

Where is Informix Innovator-C?

If you are interested in installing Informix Innovator-C I have bad news for you. It has been removed for quite a while. The download link is still there but clicking on the download button issues the following error message:

The link which brought you here is invalid. Please reload the previous page and try again. message code: 57e

This is going on for weeks. Nagging IBM about it had no effect so far. One would expect an explanation as to why it was removed and will it be back and when. But when it is about Informix a generic error message seems to be sufficient.

I do know it was deliberately removed. I was promised it will be back but this promise was not kept so far. Rumors say it will be downgraded. What exactly will be taken out? I have no idea. Why take features out? Let's speculate together.

Is it because the Informix install base is too delighted and IBM is trying to cool us down? Or maybe it is because Informix is so powerful that the Innovator-C edition could actually run a business preventing companies from buying the paid editions? My favorite is the second. I believe that by now even IBM management realizes we are not delighted. And my guess is that IBM did not realize that unlike other free products they are offering this one really works.

Gary Ben-Israel
IIUG Insider Editor

Informix and the Fightin' Kangaroos

noreply@blogger.com (Andrew Ford) — Fri, 29 Jun 2012 22:05:00 +0000

Praveen Rao, Assistant Professor of Computer Science and Electrical Engineering at the University of Missouri-Kansas City, says the word Informix at 0:58 in the following video:

This is actually pretty satisfying for me. When I was on the IIUG Board we decided it would be a good idea to offer a free 1 day conference pass for the 2010 IIUG Informix conference in Overland Park to the Computer Science professors in the area. With help from IBM we were able to get Praveen and others to come and experience the Informix technology and interact with the Informix fanatics at IIUG2010.

I'm happy to see Praveen chose to include Informix in his curriculum and take advantage of the academic materials IBM provides and hope others will too.

Here is the original article.

Innovator-C: 446 Days of Continuous Uptime in Jeopardy

noreply@blogger.com (Andrew Ford) — Thu, 28 Jun 2012 14:44:00 +0000

This morning Hannes Visagie posted to the IIUG SIGs about a change to the Innovator-C limitations listed on the IBM Informix Innovator-C page that states Innovator-C will be limited to run on 1 core, down from the previous limitation of 4 cores. I sure hope this refers to CPUVPs and not hardware cores in the server, because who has a single core machine handy these days?

Is this the end of the line for our Innovator-C instance? Time will only tell. If the above limitation is real and not just a typo (edit: confirmed not a typo) we will have to reconfigure the instance down to 1 CPUVP and bounce the engine to see if a single CPUVP Innovator-C instance can meet our requirements and the uptime clock will reset to 0. Oh well, I've still got plenty of Growth and Ultimate engines with over a year of uptime.

Even if we do have to adhere to this new limitation, I'm hopeful that we will be just fine. Unlike our Growth and Ultimate engines, the Innovator-C engine doesn't run very hot. top shows the single socket, 4 core CPU running pretty idle and onstat -g glo shows our CPUVPs are running at a high efficiency.

So, how do I feel about IBM making this change? I guess I have mixed feelings. On one hand, I hate to have to give up 3 CPUVPs worth of performance, but on the other hand I still can't beat the price. We opt to pay for support, which is VERY affordable, but if we didn't IBM would still let us run Innovator-C in production for FREE. Think about that for a second before getting too terribly upset about this change and equating this to Darth Vader altering the deal he had with Lando.

Some of you may be thinking "DB2 Express-C, the free version of DB2, is allowed to run on 2 cores and use 4GB of memory. Shouldn't Innovator-C at least have the same limitations?" That would be nice, but I don't think the have to be the same on paper. I don't think this move by IBM to change the Innovator-C requirements is an attempt to play a favorite to DB2 and not promote Informix (they have other ways of doing that, ZING!!!) If anything this is saying that Free Informix runs so well that we need to limit the hardware it runs on to 1/2 of what Free DB2 runs on to level the playing field. Edit: I see people saying similar things in the IIUG SIGs so I'm not the only one thinking this.

Despite the recent changes to the Innovator-C limitations I still think Innovator-C is a viable option for a lot of production environments. I would be really interested to see the Fastest Informix DBA contest or Eric's TPC-C benchmark run against a 4 CPUVP and 1 CPUVP Innovator-C installation. On second thought, maybe I don't. Maybe I should just keep my big mouth shut about Innovator-C before the word gets out about how powerful it is and it is limited further. This post will self destruct in 5...4...3...2...1...

lockwt - My go to utility for finding locking problems

noreply@blogger.com (Andrew Ford) — Fri, 22 Jun 2012 15:37:00 +0000

If you haven't already added lockwt to your arsenal of Informix DBA tools, you need to immediately.

lockwt is a free utility written by Eric Herber of the The Informix Zone, planetIDS and Herber Consulting that can be downloaded here and is infinitely helpful when you get the midnight call to find out why a table is locked and everyone is screaming.

I recently received one of these calls and when I fired up the lockwt utility I quickly confirmed that there was a locking issue. A session had a hold of a row that other sessions needed and wouldn't let go which was causing all kinds of problems. I have a love/hate relationship with locking problems. I love the fact that they are usually not caused by a problem with the engine and it is the application is doing something screwy, but I hate the fact that they can be incredibly tricky to track down because you have to figure out why the application is holding on to locks longer than you would like.

Using lockwt I was able to see what session was holding the lock and therefore could find out which application was causing the problem and what SQL it was running. Curiously enough the application wasn't running any SQL at the moment, it wasn't doing anything with the engine. It was just sitting there greedily holding on to the resources everyone else wanted. Using the last SQL reported in onstat -g ses, the owner of the application was able to pin point where in the code we were having problems. It was somewhere in the following block of code, can you guess what was causing the problem?

try:
   BEGIN WORK;

   INSERT INTO TABLE A;

   UPDATE TABLE B;

   call_third_party_api();

   COMMIT WORK;

except:
   ROLLBACK WORK;

Normally the third party API call would complete in less than a second, but today they were experiencing some issues and requests were taking over 20 seconds to complete and causing that transaction to remain open way longer than normal.

There isn't much you can do as a DBA at this point to help, the engine is doing what the application asked it to do by preventing other sessions from messing with the data modified in the transaction before the transaction is committed or rolled back.

Lucky for us in this situation we could associate a timeout to the API request that would abort the call if it took longer than we like, rollback the transaction and free up the locked resources.

Informix Remix

noreply@blogger.com (Andrew Ford) — Mon, 18 Jun 2012 20:34:00 +0000

I didn't expect to find this in my Google Alerts for Informix today...

Informix Remix

For my Brazilian friends, they're coming to a club near you in July!