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.

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

2007
 
2013

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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.

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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.

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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.

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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"

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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.

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.

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

Comment on this post and read more about Informix at informix-dba.com.

Contact the author at andrew@informix-dba.com.