IIUG Insider (Issue #61) July 2005 Other Issues

Highlights: We have a new logo, Cool SMI queries, IDS Training, Chat wit the lab, Calendar

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:

1. Editorial...
2. IBM Informix Database Manages Jamaica's Fiscal Management System
3. New offering from Kazer
4. Advanced Informix IDS Performance Tuning and Optimization Course
5. Special offer for FourGen users with lapsed support
6. Chat with the Lab
7. SEIUG and WAIUG user group meeting
8. Scranton across America
9. A great Informix fan
10. Resequencing Serial Columns - By Jonathan Leffler
11. Using GUIDs with IDS 9.x
12. Calendar of events
13. Useful links
14. Closing and credits

Editorial...

In May we launched our IIUG Insider logo contest. It was thrilling to see the lively participation, the effort, the imagination and the talent. Taking part in our community activity, dedicating time and skills for the benefit of others was the most important part. This was my reason for joining the IIUG board. As you have all noticed we have a new logo. This means we have a winner. It was a tough choice. Thank you all for your efforts and special thanks to our winner:

Let's keep the momentum. Harness your enthusiasm and skill to supplying content for the Insider. Art Kagel wrote a brilliant article for our May issue. For this issue we have a personal letter by Ganesh. This kind of user contribution makes the difference. Take some time and be part of the IIUG Insider.

My email address is still: gary@iiug.org

Gary Ben-Israel
IIUG Insider Editor
IIUG Board of Directors

IBM Informix Database Manages Jamaica's Fiscal Management System

Informix Dynamic Server (IDS) has emerged as the de facto standard

Established 20 years ago by the Ministry of Finance of the Government of Jamaica, Fiscal Services Ltd. has a mandate to insure the compatibility of information systems across several revenue collecting agencies. It provides a wide range of information technology services including infrastructure and software development. With approximately 250 employees, the company has frequently moved Ministry of Finance offices using manual processes into the information age.

In 1993, when Lorenzo Grant, the managing director, arrived, he determined that Fiscal Services needed to modernize its own approach. "We needed to have a database management system and tools," he said. After an extensive analysis, the company determined that the Informix Dynamic Server (IDS) was the best platform for their needs.

Grant opted for IDS for several reasons. Perhaps the most compelling was the price/performance and price/capability ratio. "Based on what we needed to do, we thought it was the best deal around," Grant said.

In addition to price/performance, IDS had several other key features. Since the telecommunications infrastructure in Jamaica is typical for the Caribbean, and many government agencies operate regionally, "we have to make sure that the different regions can operate independently but we have to be able to synchronize the databases," Grant said. "IDS is way ahead in that regard."

Furthermore, IDS diagnostics are useful in pinpointing performance and applications problems. And, Grant noted, the database "pretty much runs itself. The DBAs just need to monitor it." Once the initial sizing parameters are set, there is little need for continuous monitoring. "IDS is very good in terms of administration," added Sujit Sakar, a project manager at Intelligroup, a worldwide consulting company that has worked extensively with Fiscal Services. Finally, IDS offered the best support services in the region and was willing to work hard to understand both Fiscal Services' needs and its clients' needs, helping to develop solutions.

First Implementations

The first IDS implementation was developed for the Stamp Office, which is responsible for transfer taxes when property and estates change hands. "They did not have any reconciliation between their documents and transaction processes with the funds collected." Grant said.

Working with the Intelligroup, Fiscal Services used IDS and the Informix development suite to put the appropriate automated systems in place. Now in its third version, revenue collected has increased sharply even though the number of transactions has remained steady. Moreover, the reports developed allowed managers to track events and provide better, quicker service to their customers. "They were quite pleased with that," Grant said.

Customs Office Project

From that success, Grant and his team launched a project to automate the processes associated with the Customs Office. Ultimately, the operation went from a completely manual system to one in which brokers and importers can complete their financial transactions online.

Originally developed with a client-server architecture, the application has been moved to a three-tier architecture using WebSphere running on an IBM pSeries server. "We just replaced the front-end tier based on the application requirements," Sakar said. The database layer remained the same. The system has around 1000 users and the response time has been outstanding, Sakar added.

IBM and Informix

Fiscal Services was also a long-term customer of IBM, so when Informix came under the IBM umbrella, "we saw that as confirmation that we had made the correct choice," Grant said. "IBM's resources and commitment to Informix means that expertise would be more available and at a lower cost."

The list of projects running on IDS is long. Indeed, Grant observed, the entire financial management system of the Government of Jamaica runs on the IDS platform. In total, Fiscal Services is operating close to 100 IDS instances with close to a terabyte of data. With some legacy systems still running, Fiscal Services is running several IDS versions. Fortunately, said Sakar, "They don't take much management. They are almost completely self-healing."

Current Efforts

Fiscal Services is currently working on a tax portal funded by the Inter-America Development Bank, as well as a one-stop shop for services that importers and exporters typically complete through a myriad of governmental agencies like licensing and inspections.

Given the large number of successes, IDS has become almost the de facto standard for government agencies in Jamaica. "We want to take advantage of the experience we have," Grant said. "And, it works from a cost perspective."

Grant has set his sights even wider. "We are the largest user of IDS in the region. We are embarking on sharing our software and services to others," he said. "Many of the applications and procedures are similar in other governments in the region. This is an inexpensive way for them to come up to speed."

Database Trends and Applications July 2005

New offering from Kazer

IDS 10.0 Poster Ready for Delivery

The popular IDS 9.4 poster from Kazer has been updated to include all of the new 10.0 information. Register now for the IDS 10.0 or IDS 9.4 poster. Free when shipping to the US.

http://www.kazer.com/poster.html

Thanks,

Thad Kelsey
The Kazer Corporation
888 886-7722 x108

Advanced Informix IDS Performance Tuning and Optimization Course

Advanced Informix IDS Performance Tuning and Optimization Course September 26-29 , 2005 In Annandale, VA USA

This course is for experienced Informix database administrators and application developers who will be responsible for managing, optimizing, and tuning IDS database servers. The focus is on practical skills, procedures, and scripts for improving the performance of your database server. In addition to new skills, the course will provide a toolkit of scripts and utilities to start monitoring and optimizing your Informix IDS database server.

The course is taught by Lester Knutsen, Database Consultant and President, Advanced DataTools. Lester has been developing databases with Informix software since 1983. He specializes in data warehouse development, database design, performance tuning, and Informix training and support, and is an IBM Gold Consultant.

For more information about this course please visit our web site at:

http://www.advancedatatools.com/Training/OutlineInformixIDS93PerfTuningOpt.html

For pricing and registration call Linda Knutsen at 703 256-0267 x101 or email linda@advancedatatools.com

Special offer for FourGen users with lapsed support

Under the FourGen Passport program, users with lapsed support can renew maintenance by paying a nominal lapsed support fee plus one year of the annual support fees to receive the current version of the software plus one year of support and maintenance.

Some of the benefits with FourGen Passport program include a low lapsed support fee, upgrades to the latest versions and ports and access to professional services for assistance with installation, customization, implementation and training.

The offer is valid until October 31, 2005. For more details, please contact Sales at 1 877 424-8500 or email at sales@gillani.com

Chat with the Lab

Our next chat will address the topic of performance beginning with a refresh of the steps needed to get started with performance tuning. Version 10 has delivered another increase in performance and we will focus on the features in 10 that impacted the performance improvement.

Our speakers are:

• Jerry Keesee, Director of the Informix lab
• Dan Wood, IDS Architect

Business Partners, Customers and IBMers RSVP for this conference call via the Web:

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

Wednesday August 3, 2005
11 a.m. Eastern
10 a.m. Central
9 a.m. Mountain
8 a.m. Pacific
For 90 minutes

Save the date: Our next Chat will be Wednesday, September 7, 2005 Replays of previous Chats may be found at:

http://www-1.ibm.com/partnerworld/pwhome.nsf/weblook/eac_index_biz.html

Your comments and suggestions for this series are always welcome at:

lspina@us.ibm.com

SEIUG and WAIUG user group meeting

The Southeast Informix Users Group (SEIUG), based in Atlanta, GA, and the Washington Area Informix User Group (WAIUG) based in Washington, DC, are teaming up to present Informix User Forum 2005, the premiere Informix regional user group technical conference in the eastern U.S.

The Informix User Forum, previously sponsored biennially by WAIUG, has long had a reputation for excellence in both value and technical content. Now, the two groups have joined forces to evolve the Forum into an annual two-day conference. This year's event will be held December 8-9 in Atlanta.

Forum 2005 promises to provide even more technical content, plus opportunities to network with fellow users and access to information on the latest vendor offerings. This, combined with a registration fee of US$ 50.00 for the entire conference, makes the value of this event hard to beat.

Call For Presentations

We are currently accepting presentation abstracts for the Forum on Informix-related topics in any of the areas listed below. Subjects related to all current Informix and Informix-heritage products are welcome, as well as products that operate in those environments such as other IBM products, plus third party, Open Source and cross-platform products.

Presenters will receive a complimentary all-conference pass.

The Forum will be organized into three general subject areas or tracks that will run concurrently.

The Application Development track will cover such topics as application development tools and methodologies, application and database architecture, and maximizing application and SQL performance.

The Database Engines and Administration track will feature sessions on topics including database and data warehouse design and administration, performance tuning, backup and restore, and security.

The Informix Edge track will contain talks on aspects of Informix that provide a distinct edge in application development and processing such as emerging technologies like XML and Web services, operation in cross-platform and heterogeneous environments, and support for Open Source and third-party tools.

Tracks will feature extended session lengths of 70 minutes, which conference goers have said they prefer.

Visit the Forum 2005 Web site http://www.iiug.org/~seiug/forum2005/ to find more details about the conference and an on-line application for submitting your presentation abstract.

The deadline for submitting presentation abstracts is August 15, 2005.

There are also a limited number of vendor sponsorship opportunities available. Please contact Lester Knutsen or Walt Hultgren for more information.

Whether you're interested in speaking or simply attending, we think you'll find that the Informix User Forum 2005 will continue the tradition of providing extremely high quality and dense technical content at very low cost. Visit the Forum site for more information. We hope to see you in Atlanta December 8-9!

Scranton across America

Mark Scranton is continuing his tour across the U.S. educating our local user groups. As you can see from the following list it is a great success.

A great Informix fan

Hi,

I am a great fan of Informix. I started my career with IDS 7.0 and was developing applications using Informix-4GL for about 3 years. I was also handling the DB server for about a year though I cannot claim to be a full fledged DBA which I always wanted to be. I have drifted away from Informix products now due to the rarity of it in the market and the practical difficulty of low job market ;) I still believe that Informix DB engine is the strongest of all though I am outdated with all new releases of Informix products. I am very happy to see that Informix is in a come back trail when we all sadly believed that it will be phased out by IBM.

Thanks & regards,

Ganesh
greatganesh@yahoo.com

Resequencing Serial Columns - By Jonathan Leffler

Is there any general script or logic for resetting or shuffling all the existing serial values in a table so as to avoid all the holes and get room for new numbers in the sequence? For example, suppose that IDS must renumber the customers table in the stores database because the maximum customer number is now in danger of overflowing. What algorithm would work there?

Algorithm:

1. The algorithm isn't particularly simple, primarily because serial columns are cross-referenced and the algorithm must update the cross-references too.
2. To reallocate numbers, take the current list of actual values and sort them into order. (This assumes that some of the constraint numbers are enormous, in the range close to 2 billion; if the range is more restricted, then alternative algorithms are feasible.) (2005-07-15: sorted order is not always critical, but an unsorted list would undermine the assumption in step 5, which matters because you need to be confident that when you assign Snew, you will not collide with any unmapped Sold.)
3. Assign each a new number based on its position in the sorted order. This might assign the new value 1 or 1000 to the first item, and you can increment by a value other than 1 if desired.
4. We can assume that there are significant gaps in the sequence of numbers, so if N = number of values present in the table and M = number of possible values (2**31 - 1), we can assume N << M.
5. Consequently, and especially if we are dealing with a start value of 1 (or the same start value as the original set of numbers), we can assume that for each serial number Sold, the new number Snew is not greater than Sold.
6. If we have negative numbers to deal with (unusual - but not impossible), then it would be convenient to treat those as unsigned 32-bit numbers, so they sort greater than all the positive numbers. You can do this by storing the serial values in a DECIMAL(10) column, adding 2**31 to the negative values.
7. Now there is a mapping between Sold and Snew.
8. For each entry in sequence of Sold values (with negatives still appearing later than the positives), the sequence of operations needs to be something like:
   1. If Sold = Snew, skip this entry (that was a lucky break - chances are we'd never get this scenario, but...)
   2. Copy the record identified by Sold into a parallel record with Snew.
   3. Locate all the cross-references to Sold, updating the reference to Snew.
   4. Remove the record identified by Sold.
9. Note that in this case, we might have to change more than just the serial number - the constraint name also changes. If there are cross-references to the constraint name, the upgrade step must change those too.
10. The advantage of the copy-update-delete cycle (as against an update-update cycle) is that the constraint checking does not have to be tinkered with - deferred or disabled - while the update is in progress. This is because both Sold and Snew exist for the entries to cross-references while the updates are taking place.
11. We'd have to ensure that there are no cyclic dependencies in step 8c. What other scenarios could cause problems here? Hierarchical structures...
    1. Table A has serial column S
    2. Table B has an integer column X which cross-references A.S, another column Y with (say) an item number (integer), and the combination B(X, Y) is the primary key of B.
    3. Table C has integer columns XC, YC which cross-reference B(X, Y).
    4. Now, when changing A.S from Sold to Snew, you need to update B.X from Sold to Snew, but you need to do that with the same copy-update-delete cycle so that the cross-references in C remain valid.
    5. Or you need to turn the constraints off...

Note that a single table could have multiple cross-references to a serial column; I don't think that complicates things.

Note that by virtue of the assumptions above, all updates would either not alter the serial value (8a) or would decrease it (5). Consequently, there will not be any non-trivial collisions between the old number sequence and the new number sequence. This means that the updates in 8c will be safe - there is no risk of changing any Snew value twice.

Does that cover the bases? The algorithm in step 8 is messy, not least because the set of tables to be changed has to be determined on the fly (the most dynamic of dynamic SQL), and also because of the issues raised in step 11 (in the most general case; in the particular case of the customers table, you probably don't have to worry about that).

Problem (2005-07-15) - Step 8b runs foul of multiple unique constraints on the table, which means that you can't insert the new row. The provisional solution is to lock the table to prevent access by anyone else; defer all the unique constraints (except, perhaps, the constraint on the serial column); do the updates; re-enable the unique constraints.

If there's anything else I've left out, please let me know.

Background: Praveen Ghantasala asked a variation of this question in an email sent on 2003-12-05; Jonathan Leffler responded with a variation of this answer on 2003-12-08.

Using GUIDs with IDS 9.x

Jacques Roy
IBM Worldwide Sales Support
29 Jan 2004

The Informix(r) Dynamic Server 9.x (IDS 9.x) supports multiple methods to create unique identifiers. The original method is to use the SERIAL type with a unique constraint. IDS 9.x adds the SERIAL8 type that is an 8-byte integer value that virtually gives an unlimited number of identifiers. With the use of a unique constraint, the SERIAL8 can virtually provide unlimited number of unique identifiers. Both SERIAL and SERIAL8 are specific to a given table.

As of IDS 9.4, we can also use a SEQUENCE object to generate an identifier that can then be used in any table in the database. This virtually insures a unique identifier for any table within a database as long as the generation is done through one specific sequence.

With the raising popularity of distributed systems and collaboration between corporations, there is a need for the generation and manipulation of identifiers that are guaranteed to be unique worldwide. This can be done with the help of the operating systems capabilities to generate a Globally Unique Identifier (GUID) and the use of an opaque type that can manipulate the GUID.

This article describes the implementation of a new data type that represents a GUID. This includes a User-Define Routine (UDR) that generates a GUID with the help of the operating system.

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0401rindex.php

Calendar of events

Useful links

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

Closing and credits

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.