Cheetah in the wild!

No chat talk:


The Open Beta of Cheetah was officially announced. You can get in on:
http://www.ibm.com/software/data/informix/ids/new

IBM create a public web support forum at: Cheetah open Beta support
and there is a new infocenter at: Cheetah InfoCenter
As promised, IBM delivered a public open Beta product. This will give the chance to everyone of us to test it, view and work with the new incredible features that R&D have put in this release.

The feature specification is really impressive. Some of them are by far the deepest changes in IDS I've seen since I started working with it in 1998. It doesn't matter what is your role (DBA, Developer...). You'll find some features specifically directed to some of your major pain points.

A quick list of the features:

• Backup and Restore to Directories with ontape
  Adds flexibility specially if used with the next feature
  
• Continuous Logical Log Restore
  Besides HDR, you have another option to disaster recovery or to get a quick copy of your instance. The second instance will be on rollforward until you need it
  
• Improved Parallelism during Backup and Restore
  OnBar got a lot smarter. It's able to make faster backups and specially restores. Full system backups can be done in parallel now (as it should have been from the start if I may say it... :) )
  
• Named Parameters in a Callable Statement
• Indexable Binary Data Types
  Well... not really new. We've got it in 10.00.xC6. An evidence on how easy IDS features travel through product families
  
• Encrypted Communications for HDR
  Enterprise Replication (ER) already had it. Now you can use encryption on High availability Data Replication (HDR). It's not a must have in LAN, but very useful if you replicate across public or semi-public networks.
  
• RTO Policy to Manage Server Restart
  Killer... IDS can tune itself to assure a user defined fast recovery interval. You specify that want the server up again in 2 minutes... IDS will manage checkpoint intervals to assure this. It can also auto tune LRU cleaning and AIO VPs. The RTO is a big step in self in direction to a self managed system. Of course you may wonder why we need it, since it's normally up and running without issues? Well, IDS tends to have greater uptimes than the hardware... at least in my experience.
  
• Non-blocking Checkpoints
  Wow... Serial killer! Got your system tuned for everyday load and have seen it 'hang' in a very long checkpoint after some special maintenance (index, table moving....)? No more! Checkpoint locks will be a small (if ever noticeable) fraction of a second. LRU flushing will be done concurrently with normal transaction processing.
  It's easy to imagine that this kind of feature takes very serious thinking, working and testing. But R&D did it. Besides this you'll get a lot of very useful info about checkpoints with a new onstat -g ckp option...
  
• Trigger Enhancements
  One trigger for each type operation is not enough? Now you can have several triggers on the same table for each type of operation (Insert, Delete, Update... )
  
• Truncate Replicated Tables
  You can now use truncate on tables involved in ER
  
• Derived tables in the FROM Clause of Queries
  Ty
• Tired of writing "SELECT .... FROM TABLE (....)"?. Ok... Then use the general "SELECT ... FROM (SELECT ...). A usability feature that will facilitate application porting from other RDBMS
  
• Index Self-Join Query Plans
  Not new... Reviewed in the last article. Another example of code portability between product families
  
• Optimizer Directives in ANSI-Compliant Joined Queries
• Optimistic concurrency
  Serial killer part II... You don't like dirty reads (who likes?!) and your application keeps waiting on locks because of bad programming or due to high concurrency? No problem! Just set an ONCONFIG parameter, or an environment variable and either DIRTY READ or COMMITTED READ or both (or none) will work in the new mode: LAST COMMITTED READ. No more lock waiting. You'll get the image before commit. No need to change application code
  
• Enhanced Data Types Support in Cross-Server Distributed Queries
  Some improvements in distributed queries. More data types supported
  
• Improved Statistics Maintenance
  If like me, you're mad for having to scan a table to create statistics for some columns where you've just created an index (which makes a scan of the data and a sort...), than you can rest. Now, an index creation will automatically calculate column distributions and index and table data.
  
• Deployment Wizard
  New features. Much more control about want you want installed. Your installations can become smaller (not that they were big...)
  
• Performance Improvements for Enterprise Replication like trigger firing during synchronization (already in 10.00.xC6) and dynamically change ER configuration parameters and environment variables
  
• Schedule Administrative Tasks
  Database cron. You can schedule jobs inside the database
  
• Query Drill-Down to Analyze Recent SQL Statements
  Tracing SQL. Choose who you want to trace, how much etc. Turn it on, off and change it dynamically
  
• SQL Administration API
  Ever wanted to add a chunk through dbaccess? If you're like me, probably not, BUT... this will allow every maintenance to be done through client side tools. I think this will fuel a lot of innovation around IDS management environments.
  
• LBAC
  Label Based Access Control. Just like in DB2. This is powerful and clean. But my first impression is that it will require lots of thinking before use. You have to structure you access policies. The tooling is there, but has to be correctly used.
  
• XML
  XML publishing, XML extraction... The type of data centric XML use that we need. DB2 on the other side (Viper release, v9) has an XML engine inside it. The perfect choice for document centric applications.
  
• Node datablade
  Indexable hierarchical data. It was available, but now is part of the product and fully supported
• sysdbopen/sysdbclose
  Ah!!! It was rumored to appear in 10.00.xC1, but never did it. Now it's here. Do you want to change lock mode or isolation level for blackbox applications? No problem. Create a sysdbopen procedure and the user(s) will run whatever you put in there right after connect and before anything else that the application sends.
  

So... I've already tested some of this features and I got really impressed. Now it's possible for everybody to test them. Bare in mind a few thoughts:

1. This is a Beta product. It's not supposed to be perfect. There are issues and a few features aren't already there. (stay tuned for more Beta drops)
2. Don't use it in production. The product should be able to migrate a supported version to the 11.10 release. But it's not guaranteed that you can migrate between Beta drops. Use it for test only purposes and to inspect the new features.
3. The product has a time limitation... Again don't use it for serious work. Use it for serious testing
4. Feedback, feedback... IBM is surely making a big effort to push this. It's the first time I recall seeing an Informix product in open beta testing. The R&D and support personal are very motivated by this effort. If you can spare a few moments, please report any issues you find, give suggestions and ask questions. Your response will be the first reward these people will feel, and believe me... They deserve it.
   

A final non technical note:

Ever since the acquisition in 2001, we've been listening FUD from competitors saying that IBM would kill Informix. It wouldn't make sense to keep two competitive RDBMS systems. According to this FUD customer should move. Of course, people launching the FUD were expecting customers to migrate in their directions. Now... What do we all know about FUD? We all know it stands for Fear, Uncertainty and Doubt. It doesn't stand on Facts, it's Unfounded and we must Doubt it. Let's recall the facts:

1. Since 2001, IBM released 3 major IDS releases:
1. 9.40 with major changes in scalability (Big chunks), usability (manageabilty) and security (PAM)
2. 10.0 with major code changes (configurable page size, table level restore, ER enhancements, default roles, better partitioning....)
3. 11.1 with all of the above
2. IBM kept support for IDS 7.31. In fact 7.31 was enhanced with the new btree cleaner and other minor features. Some intermediate release levels were discontinued (9.21, 9.30)
   
3. Meanwhile, since 2001 an RDBMS vendor name O.... has discontinued two products (every release levels): version 8 and version 9 (this year, more or less at the same time Cheetah will be released) forcing it's customers to migrate. And let me remind you how easy is to migrate IDS...
   

So, thinking as a customer, whom do you think will protect your investment? If there was still any doubt about the IBM commitment to Informix, Cheetah should be enough to clear it.
But as we all know, FUD will always be around. Hopefully we'll all be too busy playing with this beloved "cat", exploring new ways to bring value to our organizations and customers to pay any attention to it.

Regards.

1st safari tour on cheetah territory: FULL

Accordingly to Informix-Zone, the first public customer workshop to show the upcoming IDS release is fully booked.

Our German friends will have the opportunity to see one of Cheetah's first appearance on 15, February in Munich. Munich has been for a long time a technical center of expertize in Informix technology, so it's not surprisingly to see this happen there.

So, this city, well known for it's beer festival will also have the privilege to be one of the first to see our beloved animal outside of it's habitat (that being the IBM labs).

I really hope they'll enjoy it and that it fulfils our expectations. I would love to get my hands on this feline, but I'll have to wait...

IDS 10.00.*C6 new features and some thoughts...

I finally found a bit of spare time and decided to look at IDS 10.00.*C6 which was recently released. I must confess that I don't do this as often as I should, but given the current rate of IDS improvements I must say that this is a rewarding exercise.
Let me say I haven't read the *C5 release notes, so I did read them too.

If you're like me, and still haven't read them, you can find a shortcut here.

The first feature you'll notice is the Index Self Join. After reading a bit I recall I've already seen a description of this feature in an Oracle article. At the time I thought that although theoretically useful it should be difficult to find a situation where this would give real life benefits. Well, after testing it I was surprised.
So, what is this Index Self Join Feature? Putting it in an easy way, it's a way to scan an index where the Where clause may or may not include the index head column, and where the first index column(s) have very low selectivity.
Previously, the optimizer would scan all the keys which fullfill the complete set of key conditions, or it would make a full table scan if the leading key had no conditions associated. With this feature it will find the unique leading keys (low selectivity), and will make small queries using this unique keys and the rest of the index keys provided in the WHERE condition of your query. Err... I wrote "easy" above? Maybe an example will make it clearer:

Imagine you have table "test" lile this:

create table test(
a smallint,
b smallint,
c smallint,
t varchar( 255)
)
in dbs1 extent size 1000 next size 1000 lock mode row;
create index ix_test_1 on test ( a, b, c);

and you populate it with the result of (bash script):

#!/bin/bash

for ((a=1;a<=15;a++))
do
for ((b=1;b<=2000;b++))
do
for ((c=1;c<=100;c++))
do
echo "$a|$b|$c|dummyt"
done
done
done

If you make a query like this:

select t from test where b = 100 and c = 1;

You'll get a sequential table scan.
If you include a condition on column "a" you'll get an index scan, but the performance won't be nice...


Now, in *UC6, if you make a query with an optimizer hint like the one mentioned in the release notes:

select --+ INDEX_SJ ( test ix_test_1 )
t from test where b = 100 and c = 1;

You will get a what is called the Index Self Join query plan, and believe me, a much quicker response. If you don't believe me, and I suggest you don't, please try it yourself. You'll need the bash script above (if you're not using bash adapt the script to your favorite SHELL). Run the script and send the results to /tmp/test.unl. Then execute the SQL below (queries have a condition on column "a"):

cat <<eof >/tmp/test.sh
#!/bin/bash

for ((a=1;a<=15;a++))
do
for ((b=1;b<=2000;b++))
do
for ((c=1;c<=100;c++))
do
echo "$a|$b|$c|dummyt|"
done
done
done
eof

/tmp/test.sh > /tmp/test.unl


dbaccess stores_demo <<eof

-- use a raw table to avoid long tx
create raw table test(
a smallint,
b smallint,
c smallint,
t varchar( 255)
)
-- choose the right dbspace for you
in dbs1 extent size 1000 next size 1000 lock mode row;

-- locking exclusively to avoid lock overflow or table expansion
begin work;
lock table test in exclusive mode;
load from /tmp/teste.unl insert into test;
commit work;
create index ix_test_1 on test ( a, b, c);

-- dsitributions must be create for optimizer to know about field selectivity
update statistics high for table test (a,b,c);

select "Start INDEX_SJ: ", current year to fraction(5) from systables where tabid = 1;
unload to result1.unl select --+ EXPLAIN, INDEX_SJ ( test ix_test_1 )
* from test where a>=1 and a<=15 and b = 100 and c = 1;
select "Start FULL: ", current year to fraction(5) from systables where tabid = 1;
unload to result2.unl select --+ EXPLAIN, FULL ( test )
* from test where a>=0 and a<=15 and b = 100 and c = 1;
select "Start INDEX: ", current year to fraction(5) from systables where tabid = 1;
unload to result3.unl select --+ EXPLAIN, AVOID_FULL ( test )
* from test where a>=0 and a<=15 and b = 100 and c = 1;
select "Stop INDEX: ", current year to fraction(5) from systables where tabid = 1;

eof

In my system (a vmware machine runing Fedora Core5), the results were (only useful for comparison between query plans):


Start INDEX_SJ: 2007-01-28 19:18:29.77990
Start FULL: 2007-01-28 19:18:29.88101
Start INDEX: 2007-01-28 19:18:34.67570
Stop INDEX: 2007-01-28 19:18:41.92104


So, from about 5 or 6 seconds to about 0.1. Not bad hmmm?
Take a look at the query plan for more details:

QUERY:
------
select --+ EXPLAIN, INDEX_SJ ( test ix_test_1 )
* from test where a>=1 and a<=15 and b = 100 and c = 1

DIRECTIVES FOLLOWED:
EXPLAIN
INDEX_SJ ( test ix_test_1 )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 42
Estimated # of Rows Returned: 15

1) informix.test: INDEX PATH

(1) Index Keys: a b c (Serial, fragments: ALL)
Index Self Join Keys (a )
Lower bound: informix.test.a >= 1
Upper bound: informix.test.a <= 15

Lower Index Filter: informix.test.a = informix.test.a AND (informix.test.b = 100 AND informix.test.c = 1 )


QUERY:
------
select --+ EXPLAIN, FULL ( test )
* from test where a>=0 and a<=15 and b = 100 and c = 1

DIRECTIVES FOLLOWED:
EXPLAIN
FULL ( test )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 118847
Estimated # of Rows Returned: 15

1) informix.test: SEQUENTIAL SCAN

Filters: (((informix.test.b = 100 AND informix.test.c = 1 ) AND informix.test.a <= 15 ) AND informix.test.a >= 0 )


QUERY:
------
select --+ EXPLAIN, AVOID_FULL ( test )
* from test where a>=0 and a<=15 and b = 100 and c = 1

DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_FULL ( test )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 114798
Estimated # of Rows Returned: 15

1) informix.test: INDEX PATH

(1) Index Keys: a b c (Key-First) (Serial, fragments: ALL)
Lower Index Filter: informix.test.a >= 0 AND (informix.test.b = 100 ) AND (informix.test.c = 1 )
Upper Index Filter: informix.test.a <= 15
Index Key Filters: (informix.test.b = 100 ) AND
(informix.test.c = 1 )

Is there a catch? Well, yes and no.
Currently this feature is disabled by default. To use it you'll need to use the optimizer directives or you'll have to change an ONCONFIG "hidden" parameter.
The parameter in question is called INDEX_SELFJOIN. A value of 1 enables it, and 0 disables it.
You can also change this at any time using:

onmode -wm INDEX_SELFJOIN=<1|0>

This information is not clearly explained in the release notes, but you can find it in the performance guide 10.00.*c6 release notes.
This is a feature planned for Cheetah that was backported to version 10. Probably in Cheetah (and in future 10.00 versions) it will be activaded by default. If you plan to use it, be careful and monitor the results... It's still a fresh feature.

So, what other good news do we have in the later versions? Well, one of them was used in the scripts above. Some of you may have noticed I created a raw (non-logged) table, and after loading it I created an index on it. Older versions wouldn't allow this, but we can use it since 10.00.xC5. There is also several enhancements that I won't review in detail:

• Control the trigger fireing on replicated tables during synchronization
  This enables the control of triggers in replicated tables when we synchronize them
  
  
• It's unecessary to copy oncfg file into target server when doing a imported restore
  When doing and imported restore (restore on a different server, not the one where we make the backups) we don't need to copy the oncfg files as we used to.
  
  
• New binary data types
  There are two new datatypes: BINARYVAR and BINARY18. These datatypes provide indexable binary encoded strings and were created to improve compatibility with WebSphere. They are provided by a new free Datablade (binaryudt). In fact this is a showcase of IDS extensibility. These datatypes support some new specific functions (bit_and(), bit_or(), bit_xor() and bit_complement() ) as long as some standard functions like length(), octet_length(), COUNT DISTINCT(), MAX() and MIN().
  
  When I read about this I imagined a scenario where this could be used to create functional indexes, based on table fields which values could be represented in binary form by a function. I mean creating an index based on a function that given a list of attributes would generate a binary string. We could represent a true/false with just one bit. The attributes could be marketing fields about your customers (sex, married/single/divorced, has car, has children... etc.). Then you could create a bit representation of this fields and index your tables with it. A search could check all the fields with a bit comparison to the function generated index.
  I couldn't prove to myself that this was a good ideia, neither with search time comparisons neither with flexibility comparisons against the traditional index methods. But I leave here the idea. If someone manages to use it efficiently, please give me some feedback.
  
  
• View folding
  This optimization permits that in certain cases there is no need to materialize a view (by creating a temp table). Instead the optimizer will make the join not against the resulting temp table but against the view base tables.
  
  
  

Well, that's it for now. The main message of this post is: keep up to date with the new versions release notes... They contain precious information that can help you decide if it's time to upgrade your systems or not.

Mark.... mark.... what "mark" thing? OH!!! You mean Marketing!

Well, friday night, and a lot of rain out there... I was looking for the last webcast replay (got the presentation but couldn't get the sound yet). It's still not available but something on the page side caught my attention: A link to some customer and partner success stories... Got curious and cliked. You can to the same, either directly or by checking the IBM Informix webcasts page.

I looked at the video, which is presented by Bernie Spang, the Director of Data Server Marketing, and contains some interviews with clients and partners about their experiences with IBM Informix. Not surprinsingly, they all talk about the features we all know and love about Informix (efficiency, simplicity, reliability, scalability etc.). So what is the real interest of this? What made me write this lines? Er... Step a few lines back... the begining of this paragraph... "Director, Data Server Marketing"... customer and partner interviews... On the IBM site...
Well, I still consider myself a young guy... But when was the last time we've seen something like this? Marketing and Informix together?

Oh... regarding the stability statements... I'm feeling bad for forgeting to congratulate the sysadmin team... I was doing some onstats recently and I noticed they have manage to run their servers for more than an year without stop! Onstat revealed an IBM Informix Dynamic Server uptime greater then 365 days!!! Just to show you all that I really understand those customers when they mention stability and reliability :)