IIUG Insider (Issue #70) April 2006 Other Issues

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.

Contents:

• Editorial
• Highlights
  • IDUG/IIUG NA Conference, Tampa May 7-11, 2006
• Conference corner
  • IDUG/IIUG Europe Conference Rome, October 9-13, 2006
  • Informix Day @ IBM European Technical Conference
• IIUG Board of Directors corner
  • Paul Watson Profiled
  • Vendors who need "encouragement" to support IDS 10 - Follow up
• Education
  • Chat with the Lab
  • New features added to IDS 10.00xC3 and 10.00xC4
  • IIUG reminds you it offers News and more via RSS Feeds...
• Developer corner
  • Python API InformixDB-2.2 released
  • What's new on developerworks
  • developerWorks Articles
• Informix success story
  • One Point Solutions/Real Plus flash video
• News
  • IIUG Press Release
• Calendar of events
• Useful links
• Closing and credits

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.

Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors
gary@iiug.org

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

Q.E.D.

And even more...

Informix Spotlight Session
By Steve Martin
The Real Story of Informix Software
http://www.storyofinformix.com

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:

http://idugew.prod.web.sba.com/displaymod/GridDisplay_yaxis.cfm?conference_id=54

Convinced? Don't loose time register now at:

http://conferences.idug.org/na/Registration/RegisterOnline/tabid/200/Default.aspx

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,

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!

http://www.ibm.com/training/conf/europe/informix/

Special Informix Day pricing: 465.23€

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.

SRI Inc

You can add ESRI Inc to that list although they do intend to certify their ArcSDE product for IDS10 at some stage.

Informatica Corp

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!?

Actuate

Cognos

BEA Weblogic

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.

BMC

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!

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:

http://www.iiug.org/news/articles/BaanIV_HPInformix_Insert_20060224.pdf

Keep those emails coming!

Kate Tomchik
kate@iiug.org

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:

https://ww4.premconf.com/webrsvp/register?conf_id=4260120

Linda Spina
IBM Information Management Sales - Americas

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:

http://www.iiug.org/url/SQL_Guide.html

Gary Ben-Israel

Using the SKIP Option
The SKIP offset option specifies how many of the qualifying rows to exclude, for offset an integer in the SERIAL8 range, counting from the first qualifying row. The following example retrieves the values from all rows except the first 10 rows:

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
If a Projection clause with the SKIP offset option also includes FIRST or LIMIT, the result set begins with the row whose ordinal position is (offset + 1) in the set of qualifying rows, rather than with the first row. The row in position (offset + max) is the last row in the result set, unless there are fewer than (offset + max) qualifying rows. The following example ignores the first 50 rows from table tab1, but returns a result set of at most 10 rows, beginning with the fifty-first row: SELECT SKIP 50 FIRST 10 a, b FROM tab1; The next example uses in a query with SKIP and FIRST to insert no more than five rows from table tab1 into table tab2, beginning with the eleventh row:

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.

TRUNCATE
Use the TRUNCATE statement to quickly delete all rows from a local table and free the associated storage space. You can optionally reserve the space for the same table and its indexes. Only Dynamic Server supports this implementation of the TRUNCATE statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax:
TRANCATE [TABLE] table_name [DROP STORAGE] or
TRANCATE [TABLE] table_name [REUSE STORAGE]

Usage:
The TRUNCATE statement rapidly deletes from a local table all active data rows and the B-tree structures of indexes on the table. You have the option of releasing the storage space that was occupied by the rows and index extents, or of reusing the same space when the table is subsequently repopulated with new rows.

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
The TABLE keyword has no effect on this statement, but it can be included to make your code more legible for human readers. Both of the following statements have the same effect, deleting all rows and any related index data from the customer table:

TRUNCATE TABLE customer
TRUNCATE customer

The Storage Specification
By default, all of the partition extents that had been allocated to the specified table and to its indexes are released after TRUNCATE successfully executes, but you can include the DROP STORAGE keywords to achieve the same result, making the storage space available for other database objects. The first extent size remains the same as it was before the TRUNCATE operation released the storage.

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
The TRUNCATE statement is not equivalent to DROP TABLE. After TRUNCATE successfully executes, the specified table and all its columns and indexes are still registered in the database, but with no rows of data. In information management applications that require replacing all of the records in a table after some time interval, TRUNCATE requires fewer updates to the system catalog than the equivalent DROP TABLE, CREATE TABLE, and any additional DDL statements to redefine any synonyms, views, constraints, triggers, privileges, fragmentation schemes, and other attributes and associated database objects of the table. In contexts where no existing rows of a table are needed, the TRUNCATE statement is typically far more efficient than using the DELETE statement with no WHERE clause to empty the table, because TRUNCATE requires fewer resources and less logging overhead than DELETE:

• DELETE FROM table deletes each row as a separately logged operation. If indexes exist on the table, each index must be updated when a row is deleted, and this update is also logged for each row. If an enabled Delete trigger is defined on the table, its triggered actions must also be executed and logged.
• TRUNCATE table performs the removal of all rows and of the B-tree structures of every index on the table as a single operation, and writes a single entry in the logical log when the transaction that includes TRUNCATE is committed or rolled back. The triggered action of any enabled trigger is ignored.

These performance advantages of TRUNCATE over DELETE are reduced when the table has one or more columns with the following attributes:

• Any simple large object data types stored in blobspaces
• Any BLOB, CLOB, complex, or user-defined types stored in sbspaces
• Any opaque types for which a destroy support function is defined.

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:

DELETE customer;

The FROM keyword that immediately follows DELETE can be omitted, as in this example, only if the DELIMIDENT environment variable is set.

Restrictions

• The TRUNCATE statement fails if any of the following conditions exist:
• The user does not have Delete privilege on the table.
• The table has an enabled Delete trigger, but the user lacks the Alter privilege.
• The specified table or synonym does not exist in the local database.
• The specified synonym does not reference a table in the local database.
• The statement specifies a synonym for a local table, but the USETABLENAME environment variable is set.
• The statement specifies the name of a view or a synonym for a view.
• The table is a system catalog table or a system-monitoring interface (SMI) table.
• An R-tree index is defined on the table.
• The table is a virtual table (or has a virtual-index interface) for which no valid am_truncate access method exists in the database.
• An Enterprise Replication replicate is defined on the table.
• A shared or exclusive lock on the table already exists.
• One or more cursors are open on the table.
• A concurrent session with Dirty Read isolation level is reading the table.
• Another table, with at least one row, has an enabled foreign-key constraint on the specified table. (An enabled foreign key constraint of another table that has no rows, however, has no effect on a TRUNCATE operation.)

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:

http://www.iiug.org/rss

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:

• Support for BOOLEAN columns
• DECIMAL and MONEY columns can be fetched as decimal.Decimal instances if the decimal module is available
• autocommit mode for connections
• Bug fixes:
  • Output buffer allocation used pointer/int casts that don't work on most 64 bit platforms.
  • Selecting TEXT/BYTE column from an empty set of rows caused segmentation fault under certain circumstances.
  • datetime values with trailing double-zeroes were fetched incorrectly.

Downloads and info at:

http://informixdb.sourceforge.net.

Best regards,

Carsten Haese

The following articles can be found in the Informix section of developerworks:

http://www-128.ibm.com/developerworks/views/db2/libraryview.jsp

Enable "cdr check" functionality within IBM Informix Dynamic Server
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0604pruet/
Download a user-defined routine that will enable you to use "cdr check" functionality for synchronizing and performing a targeted repair of replicated tables. The cdr sync and cdr check functions are Enterprise Replication features that were enabled in Informix Dynamic Server, Version 10.00.xUC4. Walk through the required steps to enable cdr check, then try it out.

Update: DB-Access Utility for Windows allows connectivity to IDS Version 9.40.xC2
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0406chong/
Download the DB-Access tool, a user interface for entering, executing, and debugging SQL and stored procedures from your Windows machine. This utility has been newly updated.

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 mcohn@us.ibm.com

Download the IBM case study video featuring IDS v10 and One Point Solutions:

http://www.ibm.com/informix/realplus/

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!

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:

http://www.prweb.com/releases/2006/4/prweb369246.htm

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.

Sources:		IIUG Board of Directors IBM Corp.
Editors:		Gary Ben Israel Stuart Litel Jean Georges Perrin

For comments, please send an email to gary@iiug.org.