|End of Support Dates|
Highlights: IDUG/IIUG NA Conference, Tampa May 7-11, 2006
Welcome to the International Informix Users Group (IIUG) Insider! Designed for IIUG members and Informix user group leaders, this publication contains timely and relevant information for the IBM Informix community.
The highlight of this Insider is the IDUG/IIUG conference in Tampa. We are looking forward to meet you.
The IIUG Annual General Meeting and the Informix party are just part of many social activities that make this conference much more then an educational experience. There is progress on the European front as well.
We have selected 34 presentations that will occupy two tracks for four days. The IIUG European CPC (Conference Planning Committee) met with the IDUG European CPC in Bucharest to select the abstracts and set the conference grid. Please go to our conference corner for more details.
IDUG/IIUG NA Conference, Tampa May 7-11, 2006
The equation is simple:
Excellent education + Networking + Vendor expo + Social events = You cannot miss
Now all we need is to prove the left side of the equation.
4 Informix Tracks with over 50 technical sessions on the Informix product line + Top speakers + 6 half day educational seminars + Ask the experts panel + User Feedback Opportunity sessions + Special Interest Groups + Vendor Solutions Presentations = Excellent education
Over 100 Informix users and Informix development staff meeting in sessions, meals, receptions and parties = Networking
About 70 vendors in a 3 day exhibition = Vendor expo
IIUDG Annual general session + Informix User Group Leadership Council Meeting + Informix party = Social events
And even more...
Informix Spotlight Session
The Real Story of Informix Software and Phil White chronicles the meteoric rise of Informix Software, How it Became a Billion Dollar Technology Company, and the Scandal that Ultimately Led to its Spectacular Fall.
You can see the full schedule at:
Convinced? Don't loose time register now at:
IDUG/IIUG Europe Conference Rome, October 9-13, 2006
IIUG is getting closer to Europe and the IIUG conference is now closer to our European members.
Thanks to all that submitted abstracts to the IIDG/IIUG conference. All presentations were good but we had room only for the 34 best. In addition to known speakers from IBM US like John Miller, Jonathan Leffler and Carlton Doe we have new stars from IBM Europe like John Ritson, Jan Musil and Simon David AKA Cosmo. In addition we will have 2 half day educational seminars and 2 Vendor Solutions Presentations. This is the first time such quality and quantity of Informix technical data is offered in Europe.
Cheers,Paul Watson and Gary Ben-Israel
IIUG Europe Conference Planning Committee
IIUG Board of Directors Informix Day @ IBM European Technical Conference
Register today for Informix Day @ IBM European Technical Conference
Join us for a special Informix Day on May 16 in The Hague, The Netherlands, for a one day intensive technical track on Informix Dynamic Server (IDS). This track is a superb opportunity for those interested in IDS to learn more about the powerful capabilities of IBM's premier data server for online transaction processing and industrial strength, embedded computing. The solutions-oriented track will cover a technical deep dive of IDS, techniques for advanced performance tuning, benefits of using IDS in your SOA environment, PHP and .NET application development with IDS, and practical applications of IDS extensibility. Sign up today to take advantage of a substantially reduced conference price for Informix users!
Special Informix Day pricing: 465.23€
Paul Watson Profiled
Here it is again -- time for a new board and new profiles... Let's discover together board member Paul Watson.
I got lots of emails about my article last month. Please continue to send me any vendors you would like to see on version 10, or other versions as is mentioned in one response. I will continue to work these issues throughout the year and provide the IIUG with additional updates.
The common issue identified was that IBM products are some of the worst offenders for not supporting Informix 10. The IIUG BOD is working with IBM on these issues, I promise! Keep sending me the information, I don't realize these things without your help.
Here are some of the member responses that I got this month with comments:
SunGard - BiTech in California
They used to support ONLY Informix (they're a competitor of Lawson and PeopleSoft, primarily for school districts) but now they are supporting Oracle and MS SQL Server thanks to the 2 years of product line uncertainty after the purchase by IBM.
Response - there was a period of time where everyone was uncertain about Informix's future, but that shouldn't be the case anymore. We have alerted IBM to this issue.
You can add ESRI Inc to that list although they do intend to certify their ArcSDE product for IDS10 at some stage.
Informatica has announced that it will no longer support IDS as a repository database, although it will continue to support it as a source/target for ETL mappings. The Informatica product manager made this decision BASED ON INPUT FROM DB2 SALESMEN. IBM is SCREWING Informix. Informatica is not the main company who needs to embrace Informix. IBM is!!!!
Response - All the sales force has been retrained and are not supposed to favor DB2 anymore. If you have any evidence to the contrary, please let your IIUG BOD know and we will follow up to make sure it doesn't happen again. Another member of the Board had this to say: Just speculating, but since IBM bought Ascential they are far more competitors than they are partners. Maybe Informatica is reading too much FUD and don't get that Informix is going to be around forever, and that there is a hugely loyal base of customers that will continue to buy and support the product. Or maybe we can tell them that it's no problem, they can all move to Ascential!?
Informatica (ETL Tool)
Rational Data Architect
Ironically I should reference IBM... IBM recently announced Rational Data Architect (a data modeling tool) which supports versions 9.2, 9.3 and 9.4! I've inquired the PM and he says we're going to support version 10 in an early fixpack, but he doesn't have plans for IDS 7 support... In fact he asked me if I had an idea of how many customers are using IDS 7...
Response - Doesn't everyone use 7? :) I mean, IBM extended that support for 5 years based on the input that there were still a very large number of users still on 7. Ask him to send me an email and I'll get someone to call him. As for IBM supporting 10, we have already contacted IBM about this and they are having conversations to make both version 7 and 10 available.
I'd like to mention that BMC has completely dropped DBReorg for Informix. While we are still on version 7.31, DBReorg is a critical component of our toolkit that we use to keep our Informix Performance at peak levels. Each table ends up being reorg'd monthly on average, and on 300GB+ databases, we regularly see a 5 - 10 gigabyte savings in space. The tool is a great way to rebuild indexes and get your tables to one extent in one fell swoop. In fact, its such a critical component of our maintenance routine, that no BMC DBReorg support for version 10 is one reason we have not even considered an upgrade to version 10! Unfortunately, no one else has a comparable tool. BMC had been great to us when they did support the tool, even adding some features we designed so that the reorgs wouldn't corrupt Baan Data!Anyway, the point of my E-mail was to encourage not just support of version 10, but for BMC (and other tools vendors) to once again start supporting Informix. Of course, I think if IBM stepped up its marketing and publicity efforts in regards to the database, this would probably happen on its own.
Response - YOU GET 'EM TIGER. I could not have said it better myself. The IIUG BOD is trying to brain storm ways to help increase the publicity for Informix. We already got IBM to listen, as we now have the first published Informix Benchmark in many years. If you missed this on the web page, here is the link for the BAAN running on Iinformix V10.0:
Keep those emails coming!
Chat with the Lab
Informix Chat with the Lab - Wednesday, May 17th 11 AM Eastern
"IDS Version 10 - Have you heard the latest?"
Since the initial release of IDS version 10, many new features have been delivered in xC1 - xC5, including the performance features implemented to support the successful Baan benchmark. Join this chat to learn about these capabilities as well as to review the steps to migrate your IDS application to version 10. Our speakers will be Jerry Keesee, Director of the Informix Lab and Kevin Brown, Senior Technical Staff Member, also from the Informix Lab.
You may RSVP to this event at:
I would like to briefly describe two new features that were added to IDS 10 after its first release.
The skip option added to the select statement and the truncate statement.
For full description refer to the IBM Informix guide to SQL: Syntax manual that can be downloaded with the rest of IDS 10.00xC4 documentation at:
Using the SKIP Option
SELECT SKIP 10 a, b FROM tab1;
You can also use a host variable to specify how many rows to exclude. In an SPL routine, you can use an input parameter or a local variable to provide this value. When you use the SKIP option in a query with an ORDER BY clause, you can exclude the first offset rows that have the lowest values according to the ORDER BY criteria. You can also use SKIP to exclude rows with the highest values, if the ORDER BY clause includes the DESC keyword. For example, the following query returns all rows of the orders table, except for the fifty oldest orders: SELECT SKIP 50 * FROM orders ORDER BY order_date Here the result set is empty if there are fewer than 50 rows in the orders table. An offset = 0 is not invalid, but in that case the SKIP option does nothing. You can also use the SKIP option to restrict the result sets of prepared SELECT statements, of UNION queries, in queries whose result set defines a collection-derived table, and in the events and actions of triggers. You can use the SKIP and the FIRST options together to specify which and how many qualifying rows are in the result set, as illustrated by examples in the section "Using the SKIP Option with the FIRST Option (IDS)" on page 2-485. The SKIP option is not valid in the following contexts: v In the definition of a view v In nested SELECT statements v In subqueries.
Using the SKIP Option with the FIRST Option
INSERT INTO tab2 SELECT SKIP 10 FIRST 5 * FROM tab1;
The following collection subquery returns only the eleventh through fifteenth qualifying rows as a collection-derived table, orders these five rows by the value in column a, and stores this result set in a temporary table. SELECT * FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a FROM tab3 ORDER BY a)) INTO TEMP The following INSERT statement includes a collection subquery whose results define a collection-derived table. The rows are ordered by the value in column a, and are inserted into table tab1. INSERT INTO tab1 (a) SELECT * FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a FROM tab3 ORDER BY a)); Queries that combine the FIRST or LIMIT and SKIP options with the ORDER BY clause can impose a unique order on the qualifying rows, so successive queries that increment the offset value by the value of max can partition the qualifying rows into disjunct subsets of max rows. This can support web applications that require a fixed page size, without requiring cursor management.
You can use these features in distributed queries only if all of the participating database servers support the SKIP and FIRST options.
To execute the TRUNCATE statement, you must be the owner of the table, or else hold DBA access privilege on the database. You must also hold Delete privilege on the table. If an enabled Delete trigger is defined on the table, the Alter privilege is also required, even though TRUNCATE does not activate triggers.
Although it requires the Delete privilege, TRUNCATE is a data definition language (DDL) statement. Like other DDL statements, TRUNCATE cannot operate on any table outside the database to which you are connected, nor on a table that a concurrent session is reading in Dirty Read isolation mode.
Dynamic Server always logs the TRUNCATE operation, even for a non-logging table. In databases that support transaction logging, only the COMMIT WORK or ROLLBACK WORK statement of SQL is valid after the TRUNCATE statement within the same transaction.
When you rollback a TRUNCATE statement, no rows are removed from the table, and the storage extents that hold the rows and index partitions continue to be allocated to the table. Only databases with transaction logging can support the ROLLBACK WORK statement.
After the TRUNCATE statement successfully executes, Dynamic Server automatically updates the statistics and distributions for the table and for its indexes in the system catalog to show no rows in the table nor in its dbspace partitions. It is not necessary to run the UPDATE STATISTICS statement immediately after you commit the TRUNCATE statement. If the table that the TRUNCATE statement specifies is a typed table, a successful TRUNCATE operation removes all the rows and B-tree structures from that table and from all its subtables within the table hierarchy.
The TRUNCATE statement does not reset the serial value of SERIAL or SERIAL8 columns. To reset the counter of a serial column, you must do so explicitly by using the MODIFY clause of the ALTER TABLE statement, either before or after you execute the TRUNCATE statement.
The TABLE Keyword
TRUNCATE TABLE customer
The Storage Specification
Alternatively, if it is your intention to keep the same storage space allocated to the same table for subsequently loaded data, specify the REUSE STORAGE keywords to prevent the space from being deallocated. The REUSE STORAGE option of TRUNCATE can make storage management more efficient in applications where the same table is periodically emptied and reloaded with new rows.
Whether you specify DROP STORAGE or REUSE STORAGE, any out-of-row data values are released for all rows of the table when the TRANCATE transaction is committed. Storage occupied by any BLOB or CLOB values that become unreferenced in the TRUNCATE transaction is also released.
Performance Advantages of TRUNCATE
These performance advantages of TRUNCATE over DELETE are reduced when the table has one or more columns with the following attributes:
Each of these features require the database server to read each row of the table, substantially reducing the speed of TRUNCATE.
If a table includes one or more UDTs for which you have registered an am_truncate( ) purpose function, then the performance difference between TRUNCATE and DELETE would reflect the relative costs of invoking the am_truncate interface once for TRUNCATE versus invoking the destroy support function for each row.
As listed in the next section, certain conditions cause TRUNCATE to fail with an error. Some of these conditions have no effect on DELETE operations, so in those cases you can remove all rows more efficiently with a DELETE statement, as in the following operation on the customer table:
The FROM keyword that immediately follows DELETE can be omitted, as in this example, only if the DELIMIDENT environment variable is set.
RSS (Really Simple Syndication) is used to distribute news, or news-like content to subscribers who use RSS readers to display the content. RSS allows you stay ahead of what's new without having to browse every site you normally visit. An RSS reader aggregates content so that you can view headlines from multiple RSS feeds. RSS feeds can also be parsed and presented in other ways in suit your needs.
IIUG provides a number of free RSS feeds to its membership. These include all the Special Interest Group (SIG) discussion forums, the IIUG.org home page highlights, Informix News, Informix Calendar of Events, and IIUG.org Press Releases. More RSS feeds are coming - including an Informix jobs feed.
All IIUG.org feeds follow the RSS 2.0 specification. To learn more about RSS at IIUG visit:
Python API InformixDB-2.2 released
I am pleased to announce a new release of InformixDB, the Python extension module for connecting Python applications to IBM Informix database engines.
Changes since version 2.1:
Downloads and info at:
Carsten HaeseWhat's new on developerworks
The following articles can be found in the Informix section of developerworks:
Enable "cdr check" functionality within IBM Informix Dynamic Server
Update: DB-Access Utility for Windows allows connectivity to IDS Version 9.40.xC2
Attention gurus! Publish your Informix technical success stories on IBM developerWorks!
IBM is looking for experienced Informix professionals to write technical articles for the Informix Zone on developerWorks. Share your expertise with the community by describing real-life scenarios where business problems were solved with an Informix implementation. This is your opportunity to help spread the word about IDS's blazing OLTP performance and legendary reliability. Of particular interest would be code samples, techniques for optimization and administration, or configuration processes. Don't miss this unique opportunity to get published on IBM.com, develop personal credentials, and of course, get paid!
Please send your article proposals to Michael Cohn at email@example.com
One Point Solutions/Real Plus flash video
Download the IBM case study video featuring IDS v10 and One Point Solutions:
An excellent piece of high-quality marketing collateral has been published by IBM featuring an IDS v10 customer solution built by One Point Solutions. In this video Ron Flannery discusses the version-to-version and platform migration and the customer discusses the enhanced performance and seamless integration of IDS v10. Make sure to download and share this video today!
IIUG Press Release
The IIUG has embarked on this new initiative to increase Informix visibility and reaching beyond our current membership. Our first press release was issued April 8, 2006 in PRWeb.
For the whole press release please go to:
In response to your input, we have created a page on the IIUG web site containing all the links we used to include. Please find it at: http://www.iiug.org/quicklinks.html
The International Informix Users Group (IIUG) is an organization designed to enhance communications between its worldwide user community and IBM. IIUG's membership database now exceeds 25,000 entries and enjoys the support and commitment of IBM's Data Management division. Key programs include local user groups and special interest groups, which we promote and assist from launch through growth.
For comments, please send an email to firstname.lastname@example.org.