I ran into a simple problem the other day: I got an error while creating an index because the key was too big to fit in my index. As you may remember, the maximum size of an index key on a standard Unix/Linux system is 387 bytes.

Why do we have this limit?

This is a function of the page size and the way a B-tree index works. With the limit of 387 bytes on a 2K page, we can have at least 5 keys per page. This way, we divide the data in at least 5 parts at each level. the end result is eliminating comparisons to get to our our result faster. If we had only one key per page, it would be the equivalent of doing a sequential scan so the index would be useless.

In IDS version 10.0 (2005), Informix introduced the configurable page size. from that point on, it is possible to create DBspaces with page sizes of up to 16KB in size. the page sizes available has to be a multiple of the basic page size: 2KB or 4KB.

These larger pages can provide better performance when you have a wide table where the row size could be, let say 12KB. This way, you can fit an entire row in a page instead of using page chaining to support these larger rows. The savings in I/O could make a noticeable difference in performance in many situations.

Coming back to my indexing problem, I can fix it by using a larger page size. According to the documentation, the maximum index key size is as follow for each page sizes:
 

page sizemax key size2048 (2KB)3874096 (4KB)7968192 (8KB)161512288 (12KB)243516384 (16KB)3245  If your key fits in a 2KB page (shorter than 387 bytes), you could still use a larger page size for your index. The difference is that more keys would fit in one page so the index will not be as deep so it could provide additional performance. Why not simply use the 16KB page size everywhere? The short answer is that you could waste space on the page used for a table. A page can include a maximum of 255 rows. If your page size is 16KB and your row contains only two integers (2 x 4 bytes), you could, in theory, have over 2000 rows in that page. Since we are limited to 255 rows, we are wasting over 14,000 bytes. Why not use four or five different page sizes? Each page size requires its own buffer pool. We have to decide how much memory to allocate for each of these pools. Our decision may not result in the optimal memory allocation. The result is that some pools will have too much memory and others would benefit from more. Bottom line, this would make system administration more complex. I would suggest to limit ourselves to two page sizes. The default page size and another one. The second page size depends on the environment requirements. I would also look at the size of the I/O on the particular machine and how many requests do multiple I/O on sequential data. If you haven't looked at the configurable page size in IDS, maybe it is a good time to do so now.

 Since I've been on a common driver kick lately, might as well keep on going...

There was a chat with the lab on Feb 25th that talked about the common Java JDBC driver (referred as the JCC driver): Top 10 reasons to consider IBM Data Server Driver for JDBC and SQLJ for IDS servers.

You can use the JCC driver with IDS when connecting using the DRDA protocol. Some of the benefits include:

• Better integration with WebSphere
• Ability to use the capabilities PureQuery
• Better tracing and debugging
• Full IDS clustering support
• Superior performance over the Informix JDBC driver

All this is significant:

• PureQuery can increase the performance of SQL statements by analyzing the usage and make changes transparently from the application. For example, it can detect the use of the same statement with different literals and convert that under the cover into a prepared statement.
• Full IDS clustering support includes working with the connection manager to automatically and transparently connect to an alternate server when the primary fails.
• Superior performance: It provides a 5% to 10% performance boost over the Informix JDBC driver.

If you are using Java, maybe it is time to start looking into the JCC driver. You can download it from the IBM site at (10MB):

http://www.ibm.com/services/forms/preLogin.do?source=swg-idsdjs

For more information on this chat with the lab:

Here's Where you can find information on this chat with the lab:

• Chat with the Lab Home

• Top 10 reasons to consider IBM Data Server Driver for JDBC and SQLJ for IDS servers Presentation download

• Top 10 reasons to consider IBM Data Server Driver for JDBC and SQLJ for IDS servers Audio replay

 Looks like I jumped to conclusion too quickly. I won't give you any details or attenuating circumstances. I simply did not check properly. It looks like we do hve something on the Windows platform but not on the others.

I simply have to statrt lobying for the data server drivers as part od CSDK on all platforms. In the meantime, you can download the common drivers starting at this URL:

http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217

The one you want is the IBM Data Server Driver Package (DS Driver). On Linux, it is a 24MB download.

More on how to use it later.

 There was a big change for me this year: I left the Informix CTE group to lead a new group. I am now a manager... and architect. My new group is called Application Development Services. This mean that my group looks at IDS from a programmer point of view. Let me give you an example of what that means. Let's look at the major features included in IDS 11.50.xC6:

• Backup from an RSS server
• Dynamic listener threads
• View event alarms
• Basic Text Search enhancements 
• MERGE statement enhancements
• Attach/detach enhancements 
• External tables
• ..more.. 

I care about these features but I my attention goes to a feature of the new Client SDK that deserved a one line mention in its release notice:

"When you install Client SDK or IConnect, you have the option to install IBM Data Server Driver version 9.7. For more information, see the Client Products Installation Guide."

As you may remember, the long term direction for client applications is to use the DRDA interface to IDS. With this one line statement, I can now write programs using CLI (ODBC) without having to have to figure out where to get the driver. Since IBM has multiple packages available, I could have easily made the mistake of thinking that I need to download the entire DB2 client (about 600MB) to get this functionality. In addition, this is all I need to build PDO_IBM for PHP applications or IBM_DB gem file for Ruby and Rails development. As far as what my group will do, we can start by figuring out and prioritizing what features will make Informix more attractive to developers/programmers. It's not just features in the server. It has to consider everything. Even documentation. I'm sure I'll have more to say about this later this year. Hopefully I'll have interesting results to report by the time I see some of you at the IIUG conference in April. 

 There's a children book that I used to read to my kids. It was about a boy that was laying around on the grass when he say a fly go by. What followed was a bunch of animals chasing each other.

Lately, I took a break from blogging (I hope you've noticed!), like a little boy laying on the grass, enjoying a sunny day. During that time, IDS 11.50xC6 came out. Here are a few interesting features:

• External table: an SQL interface to files to allow for very fast load and unload.
• XA transactions on secondary  servers
• Backup on the RSS server: You can make an archive of an instance on an RSS server
• Dynamic listener threads: You can start, stop, and restart listener threads for the soctcp or tlitcp protocols without interupting existing connections.

There are also enhancements to the MERGE statement and the attach/detach capability among other thing. You can find out more about the xC6 release in the release notice