IIUG Insider (Issue #121) July 2010 Other Issues

Highlights: New Informix Packaging

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
  • New Informix Packaging
  • Competitive Pricing and Packaging
• Education
  • Advanced DataTools
  • IBM
• Developer corner
  • Informix Chat with the Lab
  • Using Estimated Rollback Time Feature of 11.50.xC6
  • New in DeveloperWorks
• Support corner
  • Announcing the Continuing Support Pilot for Informix versions 9.4 and 10
• Informix corner
  • Message from the Director
  • Migrating from Microsoft SQL Server to IBM Informix Abstract
  • New Informix Choice Edition announcement
  • Withdrawal of Ultimate-C Edition announcement
• Informix resources
  • Informix Blogs and things
• Calendar of events
• Useful links
• Closing and credits

In May IBM came out with a new pricing and new editions for Informix. Two months later there is a modification to the new offerings. The Ultimate-C free version no longer exists. The Innovator-C edition is now the only free deployable edition available on Mac and Windows. A new low price entry level edition called Choice was added to Windows and Mac. It is tempting to say the first pricing module was poorly planned and the new one is just a hysteric reaction.

I don't think this is the case. The May announcement was carefully planned. The main target was the SMB space the fastest growing market segment. After the May pricing model IBM kept a close watch and talked to many partners and customers. The model was reevaluated and revised. I think it is the right course of action. Please read the highlights section for detailed information.

IBM has a clear road map for Informix, offers a new production free edition, is aggressively promoting Informix in the SMB market place and recruiting new solutions partners. I regard it as a big step forward. Informix always had a technological advantage now it is offered in a competitive price.

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

New Informix Editions - Creating Growth Opportunities for Clients and Partners

Greater values on new for purchase editions on Informix V11.50xC7

IDS Workgroup to Informix Growth Edition

• 3-nodes HA cluster of any type
  • Queries and updates on secondary nodes
  • No warm/idle standby charge
  • Full Informix license on each hot standby node, charge metric can be different from primary
• Continuous Availability Feature (aka Shared Disk Secondary (SDS) ) included, up to 3 SDS nodes
  • Same charge metric on all SDS nodes
• Advanced Access Control Feature (aka LBAC) included
• Unlimited Enterprise Replication (ER) nodes, both root/admin and leafnodes
• Informix Warehouse for Workgroup Edition rolled into Growth Edition
  • SQL Warehousing Tool (formerly Informix Warehouse Feature) included
• 4 sockets processor limits for all charge metrics, no more PVU limit

IDS Enterprise to Informix Ultimate Edition

• Unlimited scaling and functionality
• Formerly for charge feature options now included
  • Continuous Availability Feature
  • Advanced Access Control Feature
• Storage Optimization Feature remains a for charge feature option
• HA cluster of any type
  • No warm/idle standby charge
  • Full Informix license on each hot standby node, charge metric can be different from primary
• Unlimited SDS nodes
  • Same charge metric on all SDS nodes
• Informix Warehouse for Enterprise Edition rolled into Ultimate Edition
  • SQL Warehousing Tool (formerly Informix Warehouse Feature) included
  • Purchase Storage Optimization Feature separately

Informix Editions Summary

Informix Offering Mapping — Old to New Editions

License Entitlement Migration for IBM Passport Advantage Customers

• Active IBM Passport Advantage customers have been automatically migrated to the new Informix replacement license entitlements on May 25, 2010
• In the interim, until their next S&S renewal is completed, Passport Advantage will display both the old and replacement Informix license entitlements
• Beginning on May 25, 2010, renewal quotes will be based on the new replacement part numbers.
• After the next S&S renewal is completed, only the new replacement entitlements will be displayed
• We prepared a customer letter explaining the entitlement migration and what to expect
  • The letter is available in http://www-01.ibm.com/support/docview.wss?uid=swg21431901

Informix Training - Online, Onsite or in Our Classroom

Advanced Informix Performance Tuning Course by Lester Knutsen and Art Kagel.

Do you want to learn how to be the FASTEST Informix DBA? This is the course our Fastest Informix DBA contest was developed from and will increase your database performance tuning skills.

This 4-day course focuses on techniques for optimizing an Informix Database. Labs will demonstrate more than 100% performance improvement. Each student will have a 4-core Linux server with IDS 11 and a large dataset for benchmark exercises and labs. Attend the class in person or online via our Web Training Center. Both Art Kagel and Lester Knutsen will be teaching the Advanced Classes together.

This course is for database administrators and application developers who will be responsible for managing, optimizing, and tuning an IDS database server. The focus is on skills, procedures, and scripts to improve the performance of your database server. The course will provide a toolkit of scripts and utilities to start monitoring and optimizing your IDS database server. (Cost $2,000 USD)

Course Schedule:

• September 27-30, 2010
• November 15-18, 2010

Class times are 9:00 AM to 5:00 PM; dress is casual.

For directions and hotels near Advanced DataTools' Office see:

http://www.advancedatatools.com/OurCompany/DirectionsMapsHotels.pdf

For more information visit http://www.advancedatatools.com/Training/InformixTraining.html or call 703-256-0267 Ext. 101 for more information and to register.

US only, All IM

Register now to get your software skills, and a complimentary netbook, too!
Now you can receive a complimentary Lenovo S10-3 netbook after completing an eligible Information Management class. Simply register for an eligible software training class between May 5 — October 29, 2010 and complete the class by December 17, 2010. Note this is a U.S. only promotion cannot be combined with other IBM discounts.

Learn more! http://www.ibm.com/training/us/swgnetbook5

US only, All IM

Time is running out! Redeem your Assess and Save voucher now!
Candidates who purchased, and passed an Information Management Online Assessment Exam during the offering period, automatically received a 50% off certification voucher good for the corresponding exam. Plan to redeem your voucher on or before August 31st, 2010 and save before it expires!

For more information visit http://www-01.ibm.com/software/data/education/voucher.html?ca=july2010nl

Thursday, August 26
8:30 AM Pacific
10:30 AM Central
11:30 AM Eastern
4:30 PM London
5:30 PM Paris

Informix Post End of Service Support Options and Upgrade Best practices

Speakers:

Al Martin - Director; WW Data Management Support; IBM
Charles Gonsalves - Sr. Manager; WW Informix Support, IBM
Chris Fender - Program Director; WW IM S&S Strategy and Sales
Ron Privett - Sr.Engineer, Americas Down Systems and Diagnostics Team.

Available for Informix versions 7.3, 9.4 and 10, these options will provide you with access to expert IBM Support professionals and will help you maintain business continuity until you upgrade to the latest version of Informix. We will also cover best practices when upgrading from Informix 10 to Informix 11.50; including upgrading standalone and replicated environments.

Presentation material will be available at http://www.ibm.com/informix/labchats prior to the event.

Questions, comments: ahujam@us.ibm.com

To register for this event, use the following link:

https://events.webdialogs.com/register.php?id=d6def198fd&l=en-US

For webinar-specific questions, contact the moderator. For technical support, contact UnyteSupport@us.ibm.com.

It was a beautiful morning on July 4, 2010. I had taken a long weekend to enjoy the lake with some friends and celebrate my wife's birthday. Birds were chirping, fish were jumping, coffee was brewing, bacon was sizzling, the kids were spending some quality time with the grandparents and I was enjoying a rare treat, sleeping in. When the phone rang and I saw the number I knew it wasn't a good phone call.

You see, I'm the only Informix DBA where I work, which means I'm always on call. I'm OK with this and so is my employer. This setup rarely causes either of us problems because Informix is so darn stable and if I'm completely unavailable I do have a backup with a good bit of Informix know-how.

I answer the phone and get a description of "the database is running slow and new connections are failing, can you join the outage bridge?"

At about this time the alarms that are sent to my phone start rolling in. "Dynamically allocated new virtual shared memory segment", "DR: ping timeout" and "DR: Turned off on primary server". Well crap, any hopes of this being a false alarm are DOA and I fire up the laptop and wireless internet card and join the bridge.

To my disappointment I am unable to acquire an internet connection at the lake (it worked earlier when I tested it) and am now in the unfortunate position of asking my backup to run onstat commands and look at the online logs and telling me what he sees. I don't know how the Informix Support people do this day in and day out, it is totally frustrating. My hat goes off to them.

10 minutes into the outage I'm in the car driving into town in search of civilization and internet and after a lengthy back and forth between me and my Eyes that went something like this:

Me: Run onstat -g act and tell me what you see.
Eyes: onstat -g acv just returns a list of onstat options
Me: Did you say you ran onstat -g atp?

(Phonetic Alphabet Tables can be found here: http://www.osric.com/chris/phonetic.html)

We finally determine that a huge delete took place, ran for some time, was aborted by the user and the rollback is causing all kinds of havoc with locks being dynamically added, dynamically allocated virtual segments, memory swapping and general server unhappiness. Eventually the rollback completes and the engine is bounced to free the allocated locks and things are back to normal. We are lucky that our bread and butter application does not depend too heavily on this server to function, so the damage to our bottom line was minimal.

When things like this happen we have a follow up meeting after the dust settles to discuss what happened and how to prevent it from happening again. Pretty standard, really.

In this case my initial suggestion of "Don't accidentally attempt to delete 11.5 million rows from a table with row level locking in a single transaction on a holiday" was met with little enthusiasm. While it is a good policy, accidents do happen, and we need to identify potential long transactions before they become a problem.

A new feature in 11.50.xC6 that allows you to view the estimated rollback time of a transaction gave us the information we needed.

> onstat -x 

IBM Informix Dynamic Server Version 11.50.FC6   -- On-Line -- Up 93 days 
01:09:22 -- 376408 Kbytes 

Transactions 
                                                                            
              est. 
address          flags userthread       locks  begin_logpos      current 
logpos    isol    rb_time  retrys coord 
db99a028         A---- db957028         0      -                 - 
COMMIT  -        0 
db99a340         A---- db957880         0      -                 - 
COMMIT  -        0 
db99a658         A---- db9580d8         0      -                 - 
COMMIT  -        0 
df40b750         A-B-- e0147788         4      13026:0x1ebb6018 
13026:0x1ebb609c  COMMIT  5:31     0 
4 active, 16 total, 8 maximum concurrent

A new column, est. rb_time, has been added to onstat -x. We can continuously monitor this column via a light weight script run by cron (or whatever you like) and send an alarm if we find a potentially long transaction. This gives us the ability to log in, identify what is going on, determine if this is a legitimate transaction or a bad one that will cause problems and kill the session before the rollback time gets out of hand.

Is this a perfect solution for everyone? Nah, but it works well for my OLTP environment where our transactions are teeny tiny and a rollback time of more than 30 seconds should raise an eyebrow and a rollback time of more than 60 seconds is cause for alarm.

#!/bin/ksh 

# script to monitor for long rollback times 

# sendmail used to email alerts 
SENDMAIL="/usr/sbin/sendmail -t" 

# send only an email if rollback time reaches MAXROLLBACKEMAIL 
MAXROLLBACKEMAIL=${1:-30} 

# send a page if rollback time reaches MAXROLLBACKALARM 
MAXROLLBACKALARM=${2:-60} 

# email address 
EMAIL_NOTIFY=dba@email.com 

# pager 
DBA_PAGE_EMAIL=number@pager.com 

# force MAXROLLBACKEMAIL to be less than or equal to MAXXROLLBACKALARM 
if [[ ${MAXROLLBACKEMAIL} -gt ${MAXROLLBACKALARM} ]] 
then 
       MAXROLLBACKEMAIL=${MAXROLLBACKALARM} 
fi 

# use grep and awk to extract rollback times and convert them to seconds 
# inspect each rollback time and send an email or alarm if over the limits 
for time in $(onstat -x | awk '{print $8}' | grep -vE "isol|On-Line|-|^$" | 
awk ' 
BEGIN { 
       FS=":" 
} 
{ 
       printf("%ld\n", $1 * 60 + $2) 
} 
') 
do 
       if [[ ${time} -ge ${MAXROLLBACKEMAIL} ]] 
       then 
               if [[ ${time} -ge ${MAXROLLBACKALARM} ]] 
               then 
                       EMAIL_NOTIFY="${EMAIL_NOTIFY},${DBA_PAGE_EMAIL}" 
               fi 

               cat - << EOF | ${SENDMAIL} > /dev/null 2>&1 
to:${EMAIL_NOTIFY} 
from:$longTx@yourcompany.com 
subject:${INFORMIXSERVER} - long transaction 
there is a transaction with an estimated rollback time of ${time} seconds 
EOF 
               print -- "$(date +'%Y-%m-%d %H:%M:%S'): there is a 
transaction with an estimated rollback time of ${time} seconds" 
       fi 
done

Andrew Ford
IIUG Board of Directors

Migrating from Microsoft SQL Server to IBM Informix
http://www.redbooks.ibm.com/abstracts/sg247847.html?Open
In this IBM Redbooks publication, we discuss considerations, and describe a methodology, for transitioning from Microsoft SQL Server 2008 to the Informix Dynamic Server. We focus on the topic areas of data, applications, and administration, providing information about the differences in features and functionality, including the data types, data manipulation language, data definition language, and stored procedures. Understanding the features and functionality of the two products assists you in developing a migration plan.

26 Jul 2010

Compare the Informix Version 11 editions
http://www.ibm.com/developerworks/data/library/techarticle/dm-0801doe/index.html
Get an introduction to the various editions of IBM Informix, and compare features, benefits, and licensing considerations in a side-by-side table. Regardless of which edition you choose, Informix brings you legendary ease-of-use, reliability, stability, and access to extensibility features.

22 Jul 2010

Get started with direct file I/O support in IBM Informix 4GL
http://www.ibm.com/developerworks/data/library/techarticle/dm-1007informix4gl/index.html
IBM Informix 4GL is a comprehensive development and production environment for creating sophisticated database applications. The 7.50.xC4 release of Informix 4GL includes direct support for file I/O without requiring function calls in another language such as C. This article describes the advantages of using the direct I/O support and provides you with an introduction to how you can use it with operations such as OPEN FILE, CLOSE FILE, READ, WRITE, and SEEK. The article includes code samples that can help you get started with implementing the file I/O feature in your own applications.

15 Jul 2010

For more information go to DeveloperWorks - Informix:
http://www.iiug.org/url/devworks.html

IBM is pleased to introduce the Continuing Support offering, a pilot program for Informix versions 9.4x and 10.x that will provide you with continued access to the same knowledgeable Informix global Support team you are working with today. This assistance, included with your active Informix Subscription and Support renewal, will help keep your business running on Informix software until you are able to upgrade to take advantage of new features available in the latest Informix version. The Continuing Support Pilot begins October 1, 2010 and ends September 30, 2011.

http://www-01.ibm.com/software/data/informix/upgrade/

This exciting offering allows you to enjoy many of the Subscription and Support benefits that you have told us matter most to you, including:

• 24x7 Service Request assistance over the phone or online for Severity 1 issues
• Local business hours Service Request assistance over the phone or online for Severity 2 - 4 issues
• Access to documentation, technotes and other online product material
• Access to existing available fixes created before the product's EOS date to correct known defects

The Continuing Support offering does not include:

• Development analysis of new defects
• Development of or packaging fixes that are not already available
• Support for 3rd party components
• Infrastructure validation for Informix products that are not included on the product compatibility matrix released as part of the product end-of-support announcement

Pilot Program Participation

On or before the September 30, 2010 End of Service date for IBM Informix version 10, you only need to complete your annual Informix Subscription and Support renewal to receive Continuing Support assistance for a period of one year.

To get more information the Continuing Support Informix Pilot Program, email askifmx@us.ibm.com.

As many of you know, in late May we introduced new Informix packaging and pricing to deliver greater value to our clients and partners with what we believed would enable growth opportunities at a lower cost. We received a lot of positive feedback, but also some suggestions to add a more attractive mid-market offering.

We took this feedback and decided to make changes to the new offerings. On July 20, we are launching a new Informix Choice Edition for Mac OS X and Windows. This is a low-cost, feature-rich Informix edition that offers enterprise-class functionality at a very attractive price point with high-availability for the mid-market. It will replace the Informix Ultimate-C Edition introduced in May, which is now withdrawn. Further, the Informix Growth Edition introduced in May now supports Windows and Mac OS X environments, in addition to UNIX and Linux.

Complete details of all Informix editions and entitlements are available on the IIUG website:

http://www.iiug.org/news/announcements/new_prices.php

We recognize that major changes such as this need to be communicated as widely as possible, and we'll continue to strive to do a better job of that. I hope this newsletter is a step in that direction.

Regards,

Jerry

An IBM Redbooks publication

Abstract

In this IBM Redbooks publication, we discuss considerations, and describe a methodology, for transitioning from Microsoft SQL Server 2008 to the Informix Dynamic Server. We focus on the topic areas of data, applications, and administration, providing information about the differences in features and functionality, including the data types, data manipulation language, data definition language, and stored procedures. Understanding the features and functionality of the two products assists you in developing a migration plan.

We provide a conversion methodology and discuss the processes for migrating the database objects and data from SQL Server to Informix using various methods. We show the SQL differences between SQL Server and Informix and illustrate, with examples, how to convert tables, views, stored procedures, functions, and triggers. We provide script conversion samples for data loading. We describe application programming and conversion considerations. In addition, we discuss the Informix configuration, as well as the administration features and functions Informix provides to help DBAs manage the Informix database server after it is migrated.

With this information, you can develop your required transition methodology, and you can plan and execute the conversion activities in an orderly and cost-effective manner.

http://www.redbooks.ibm.com/Redbooks.nsf/RedbookAbstracts/sg247847.html?Open

• US: http://www.iiug.org/url/us_210-272.html
• AP: http://www.iiug.org/url/ap_ap10-0249.html
• CAN: http://www.iiug.org/url/can_a10-0710.html
• EMEA: http://www.iiug.org/url/emea_zp10-0348.html
• LA: http://www.iiug.org/url/la_lp10-0362.html

• US: http://www.iiug.org/url/us_910-180.html
• AP: http://www.iiug.org/url/ap_wp10-0086.html
• CAN: http://www.iiug.org/url/can_a10-0672.html
• EMEA: http://www.iiug.org/url/emea_zp10-0327.html
• LA: http://www.iiug.org/url/la_lp10-0339.html

• The IIUG forums at http://www.iiug.org/forums/technical.php **** these are very good****
• Andrew Ford's blog http://www.informix-dba.blogspot.com/ **** New *****
• Bruce Weed's blog http://bruceweed.wordpress.com/
• Blogs, Videos, News and more at http://planetids.com
• IDS Experts at http://www.ibm.com/developerworks/blogs/page/idsteam
• Mark Jamison at http://www.ibm.com/developerworks/blogs/page/gbowerman
• Madison Pruet at http://www.ibm.com/developerworks/blogs/page/roundrep
• Jacques Roy at http://www.ibm.com/developerworks/blogs/page/jacquesroy
• Cathy Elliott at https://www.ibm.com/developerworks/mydeveloperworks/blogs/InformixVoices
• Informix blogs at http://www.ibmdatabasemag.com/blog/main/archives/informix/index.html
• Kate Dawson at http://www.ibm.com/developerworks/blogs/page/katedawson
• Fernando Nunes at http://informix-technology.blogspot.com/
• Virtual Worlds, MMOGs, Serious data - Informix Technology for Persistent Worlds http://informix4virtual.wordpress.com/
• IBM DATABASE MAGAZINE at http://www.ibmdatabasemag.com
• The Informix Zone at http://www.informix-zone.com
• The Informix mag at http://www.informixmag.com/
• IIUG Developers Desktop http://www.iiug.org/devnews/Vol_1_Iss_4/Home.html
• There is an Informix group on LinkedIn. The group is called "Informix Supporter", so anyone loving Informix can join, from current IBM employees, former Informix employees, to users. It will also be a good occasion to get in touch with others or long-time-no-seen friends. If you fancy showing the Informix logo on your profile, join. To join, simply go to: http://www.linkedin.com/e/gis/25049/5E4B2048E558

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. The 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 gary@iiug.org.