IIUG Insider (Issue #134) August 2011 Other Issues

Highlights: Compare the Informix Version 11 editions

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
  • Compare the Informix Version 11 Editions
• Conference corner
  • Turn Insight into action at the Information on Demand 2011/a>
• Education
  • Advanced DataTools
• Developer corner
  • The best database for every purpose!
  • New in DeveloperWorks
• Informix news
• Support corner
  • What UPDATE STATISTICS command should you run?
• Informix corner
  • New BP Exclusive Forum on IIUG
  • IBM Smart Metering Data Summit
  • Informix Dynamic Server 11.50 Full High-Availability Clustering
  • BM Webcast: Data Management for a New Generation with IBM Informix
• Informix resources
  • Informix blogs and things
• Calendar of events
• Useful links
• Closing and credits

The summer is coming to its end. In Israel it is hot and dry. Hurricanes and tornados are not part of our life. Unfortunately we have enough problems of our own.

My best wishes to all Informix users in the US and to those who are not YET Informix users as well. Informix is beta testing 11.7 xc4 - one more step towards the next major release.

Activity round Informix is growing. Not as much as we would like it too though. The next IBM Data Management Event is the Information On Demand conference. This year it will take place in Las Vegas, as it did last year, and the year before that, and before that. The IIUG board will have its next meeting at the IOD conference. If you are there, look us up. We are always happy to meet our Informix friends.

Gary Ben-Israel
IIUG Insider Editor

The IIUG 2012 Informix Conference is moving to California!

The IIUG Board of Directors is pleased to announce that the IIUG 2012 Informix Conference will be held at the San Diego Marriott Mission Valley, April 22 - 25, 2012. This hotel features the same number of guest rooms as the location for the 2011 Conference, but with 40% more meeting space available surrounding a spacious outdoor pool area. Upgraded wireless Internet is available throughout the facility. Although pricing is not available at this time, we expect the pricing to be in the same neighborhood as the 2011 event for both attendees and sponsors.

The IIUG Conference Planning Committee is proud, committed and will continue to provide a quality conference containing cutting edge Informix sessions and showcasing quality Informix vendors. Sunday's kickoff will feature several informative tutorials and the Conference's three-day agenda will feature over 80 technical sessions. Hands-on labs will be offered again in 2012. All sessions will be conducted by leading Informix users as well as IBM Informix experts from around the world.

For more information on the facility, see the hotel website at:

http://www.marriott.com/hotels/travel/sanmv-san-diego-marriott-mission-valley/

Important dates to watch for include:

• The Call for Presentations will be announced during September, 2011.
• Registration will open the end of October, 2011.
• The Call for Presentations Deadline will be early November, 2011.

For more details continue to watch next month's issue of the IIUG Insider or check out the IIUG official Conference Web Site at http://www.iiug.org/conf.

IBM Informix is a premier database for high volume online transaction processing (OLTP) as well as integrated applications. Informix has built an incredible reputation over time as the database server that "just works!" Informix has kept pace with its customers as they've grown and expanded without costing them a fortune for ongoing maintenance and administration. In most cases, these companies have grown without requiring significant infrastructure upgrades because Informix is so efficient. In turn, customers have responded by rating Informix as #1 in customer satisfaction for several years.

Customers choose Informix because of its reliability, flexibility, ease-of-use, and total cost of ownership. In simple terms, this means Informix works without a problem or major administrative issues regardless of the operating system Informix is running on, so you can manage your application needs as your business dictates. Informix is available across a broad range platforms and operating systems including AIX, HP-UX, Sun Solaris, Macintosh OS X, Linux, System i, System p, System x, System z, and Windows.

Informix has recently made some dramatic and significant changes to the licensing model including "for-purchase" and "free" editions that can be downloaded and used in production. These editions are tailored from a price and functionality perspective to a specific market segment. The Informix purchased editions offer some sub-capacity pricing models, giving you the flexibility to buy just what you need to support your data services requirements without having to worry about the total processing power of the hardware on which Informix is installed. All editions may be deployed in "virtual" environments, giving you additional deployment flexibility. Finally, for the ultimate in flexibility and pricing convenience, you can take advantage of new "pay-as-you-go", ready-to-use cloud computing technologies featuring Informix through vendors such as Amazon Cloud Computing and other providers.

In March 2011, Informix introduced significant new function that dramatically enhanced its ability to serve as a data warehouse or data analysis server. The new Informix Warehouse Accelerator (IWA) technology uses compression and a columnar approach to storing and accessing data in-memory as opposed to the row-based approach used in most database engines. This new in-memory approach enables Informix to provide virtually unlimited data warehouse growth while providing data response times that are hundreds of times faster than conventional systems. Informix Ultimate Warehouse Edition can be used in combination with other advanced Informix technologies, including high-availability clustering, Informix Flexible Grid, spatial analysis, and advanced access control, to build a robust, scalable data store that can resolve the most demanding and difficult business issues.

For the full article got to:

http://www.iiug.org/library/ids_11/Compare_Informix_Editions.pdf

Turn Insight into action at the Information on Demand 2011 global conference October 23-27, 2011 at Mandalay Bay, Las Vegas Nevada. Experience proven technologies and meaningful business insights to improve performance while reducing costs. Choose from three technical forums that give you in-depth education including hands-on labs and usability sand boxes, and the thought-provoking forum for Business Leadership.

Registration is now open for IOD 2011:

http://www.ibm.com/software/data/2011-conference

Advanced DataTools Advanced Informix Training (Online and in-person)

September 26-29, 2011 - Advanced Informix Performance Tuning Course

Learn how to become the FASTEST Informix DBA from 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. It 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 Informix 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. All courses may be taken online from your desk or at our training center in Virginia. To take a class online, all you need is a web browser and a SSH client (like Putty) to connect to the training lab.

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)

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

As a consultant I often am asked "What is the best database for everything we do?" Unfortunately, there is no easy answer for that, and I know this is not the response you all expect from me. Bear with me on this and I will explain.

The best database, and to be clear by 'database' I mean Database Management System, is not MySQL, PostGreSQL, Ingres, Oracle, Sybase, IBM DB2 branded products, IBM's Informix branded products, Voltdb, OpenSQL, Berkeley DB, Progress, Unify, or any of the literally hundreds of other RDBMS products on the market whether they are free or for-cost. No single database product can serve all of the needs of any large organization nor for most smaller organizations. That's a fact! While I think that IBM Informix Dynamic Server Ultimate Edition is the cats meow of RDBMS products - the performance, feature, and innovation leader in my book - it is not best for every purpose in every organization.

So, the question remains out there: "What is the best database for ...", what do I recommend? Because if my clients have to use a different database server product for each group of applications that fit best, or even groupings that are sometimes second best, they are still going to have to maintain and be able to manage and tune several products from several companies won't they? The list of skill sets that their DBAs will need will be very broad and I will have to hire more DBAs than I might need if I could just use one product.

I've been thinking about this for a while, and I keep coming back to the marketing presentation I made to IBM two years ago and to how appropriate that was to this question even more so than to the question of how IBM should market its database products. If my clients could get the "right" database, or close enough, for every situation from a single vendor, and if that vendor could make the skills needed to manage all of those database products they market sufficiently portable, that would reduce my clients' costs of ownership substantially making this single vendor solution even more attractive.

OK, so what product lines are out there? Oracle effectively has MySQL for very small projects and their Enterprise Class Oracle server in its various "editions". Sybase has an Enterprise class server and a mobile server. Ingres has only an Enterprise class server but not much third party software support left. Progress is a good SMB server only. Same with Unify. PostGreSQL has many nice enterprise class features but not all and many organizations won't bet the ranch on an open source product that doesn't have corporate support behind it. That brings us to IBM which, as I pointed out two years ago, has a plethora of database management systems appropriate for any size organization and any task:

• C-ISAM and IMS for application level databases.
• IBM Informix Standard Edition for small business. Clean simple OLTP performance for simple schema databases serving up to several hundred users with zero maintenance.
• IBM Informix OnLine for small to medium business. Clean simple OLTP performance for simple schema databases up to several TB in size serving up to several thousand users with extremely low maintenance and outstanding reliability and uptime.
• IBM Informix Dynamic Server (Innovator, Growth/Choice, and Ultimate Editions) for:
  • Medium to large business
  • OLTP performance and reliability
  • Medium to large Data Warehouse
  • Large Data Mart and DSS applications
  • Object Relational features
  • Industry leading expandability and extendability
  • Industry leading uptime and reliability with near zero unplanned outage and very low planned outage requirements
  • Industry leading TCO requiring 1/2 to 1/4 the DBA support of other RDBMS products
  • Databases up to 128PB serving many thousands of concurrent users
  • Industry leading replication and clustering technology that scales better than the competition
  • Unmatched embeddability with configurable footprint
• IBM DB2 Mainframe for mainframe based applications
• IBM DB2 LUW which fills most of the same niches as Informix Dynamic Server. Additionally, DB2 offers
  • Strength in 3rd party application support
  • Very large data warehouses
  • Distributed server
  • DB2 Pure Scale highly scalable clustering
• IBM Informix Ultimate Warehouse Edition with the Informix Warehouse Accelerator for accelerating complex data warehouse style queries by up to 300X without losing Informix's OLTP performance and features and without requiring expensive special purpose hardware.
• Netizza provides performance even beyond Informix Ultimate Warehouse for dedicated DW applications that need ultimate speed.

So is IBM missing anything? Yes! It is missing a unified structure of management and maintenance tools that can cross and span the entire range of database products so that an organization's DBA skills can also span the product line. Put that in place and two things happen:

I have the answer to my clients' questions and
IBM can approach the market with this now unified product line that no one can touch.

Instead of producing a white paper entitled "Six reasons to move from Oracle to IBM" which should really have been entitled "Six reasons to move from Oracle to IBM DB2" they can produce a white paper entitled "Sixty reasons to move from Oracle to IBM" that can highlight all of the benefits of using a unified database product line within your organization. This makes IBM a one-stop-shop and makes my life as a consultant a whole lot easier.

Art S. Kagel
Advanced DataTools
http://www.advancedatatools.com
Blog: http://informix-myview.blogspot.com/

Perform case-insensitive queries in Informix
http://www.ibm.com/developerworks/data/library/techarticle/dm-1108caseinsensitive/index.html
When you query your data, you may wish your query to be case-sensitive, or you may wish to ignore upper- and lower-case differences in data. In the past, IBM Informix databases were case-sentitive, and it required some effort to perform a case-insensitive search. Now with the latest release, Informix supports case-insensitive databases. In this article, learn how to create a case-insensitive database and explore considerations for implementing check constraints, using distinct searches, using group-by aggregate queries, implementing distributed queries, and more, in a case-insensitive database environment.

25 Aug 2011

Integrate the rich Internet application framework ZK with Informix to build real-world applications
http://www.ibm.com/developerworks/data/tutorials/dm-1108riaframework/index.html
This tutorial presents a real-world example that integrates IBM Informix and ZK, a rich Internet application (RIA) framework. Informix is a flagship IBM RDBMS product, while ZK is a Java-based web application framework supporting Ajax applications. This event-driven framework enables creation of rich user interfaces with minimal knowledge and use of JavaScript. ZK's unique server-centric approach enables synchronization of components and events across the client and server via the core engine.

18 Aug 2011

Use Informix ODBC Driver with an ODBC Driver Manager
http://www.ibm.com/developerworks/data/library/techarticle/dm-1108odbcdrivermanager/index.html
Because there are many different types of ODBC Driver managers available in today's market, many users find it complicated to configure the IBM Informix ODBC Driver to work with third-party driver managers such as UnixODBC, Progress DataDirect, or iODBC. This article describes the components used by an ODBC application and provides a quick reference of the configuration parameters needed to set up IBM Informix ODBC with a third-party ODBC Driver Manager.

04 Aug 2011

IIUG user view: Happy 10th anniversary to IBM and Informix
http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2011_Issue3/IIUG/index.html
IBM and Informix have been together for 10 years, and are still producing technological milestones. IIUG president Stuart Litel talks about why Informix remains his RDBMS of choice.

01 Aug 2011

Informix DBA: Informix Warehouse Accelerator
http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2011_Issue3/InformixDBA/index.html
The Informix Warehouse Accelerator combines new data warehouse technology with traditional relational database technology. Learn how the author's benchmark went from 9 hours and 40 minutes to just over 15 minutes using this new technology.

01 Aug 2011

Newsbytes: IBM Data Management magazine, Issue 3, 2011
http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2011_Issue3/NewsBytes/index.html
Get the latest IBM database news in this wide-ranging summary of topics, including InfoSphere Warehouse terabyte pricing, new big data analysis tools, new recovery tools, new Informix technologies to increase performance, business partner news, and much more.

01 Aug 2011

Informix Warehouse Accelerator: Using SmartMart (1)
http://ibm.co/DWMFusm
The information provided applies to the Informix Server Version 11.70.xC3 and later. This blog entry presents a demo script for the "SmartMart" feature that was introduced with the recent release of Informix Ultimate Warehouse Edition 11.70.FC3. The script analyses a given query workload and produces the definition for a data mart that will be able to accelerate this workload. This definition is saved in an XML text file. The "iwadb" example database (see this earlier blog entry for more info) will be used to illustrate the workflow.

22 Jul 2011

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

Announcement PreferredPartner

PreferredPartner, an IBM Premier Business Partner specializing in IBM software solutions and IT services, announced today the general availability of Analytics Accelerator : a new datawarehouse solution that bundles IBM's Informix Ultimate Warehouse Edition and off-the-shelf hardware with targeted technical consulting services http://conta.cc/PPRInfmx.

Oninit and Traak Systems partner to help transform real-time data into actionable insights

Oninit and Traak Systems partner to help transform real-time data into actionable insights : Oninit Consulting, the IBM Informix services company and Traak Systems, which provides predictive analytic solutions, have announced a partnership which will see them jointly develop solutions for the energy, renewable energy and utilities marketplace. Read the complete article here http://bit.ly/PROnInf.

Informix Web Cast in Portuguese by Miguel Carbone

http://www.videolog.tv/video.php?id=674267

We all know how critical it is to keep accurate and up-to-date database server statistics. Update statistics are key part of getting consistent performance. It is a very common question to ask: What UPDATE STATISTICS command one should run?

The following SQL script offers a way to generate list all necessary UPDATE STATISTICS commands for a database. You just need to replace the "<Database Name>" string with an appropriate database name. It will create an output file call 'update_stat_cmd.sql' in the current directory.

BEGIN WORK;

SELECT name, value
FROM sysadmin:ph_threshold
WHERE name IN ("AUS_AGE","AUS_AUTO_RULES")
INTO TEMP temp_threshold;

UPDATE sysadmin:ph_threshold
SET value = -1
WHERE name = "AUS_AGE";

UPDATE sysadmin:ph_threshold
SET value = 1
WHERE name = "AUS_AUTO_RULES";

SELECT tk_next_execution FROM sysadmin:ph_task
WHERE tk_name = "Auto Update Statistics Evaluation"
INTO TEMP temp_ph_task;

EXECUTE FUNCTION sysadmin:exectask ("Auto Update Statistics Evaluation");

UNLOAD TO 'update_stat_cmd.sql' DELIMITER ';'
SELECT aus_cmd_exe
FROM sysadmin:aus_command
WHERE aus_cmd_dbs_partnum = (
      SELECT partnum
      FROM sysmaster:sysdbspartn
      WHERE name =
      AND bitand(flags, 3 ) > 0); ""

UPDATE sysadmin:ph_threshold
SET value =
    ( SELECT value FROM temp_threshold
      WHERE name = "AUS_AGE" )
WHERE name = "AUS_AGE";

UPDATE sysadmin:ph_threshold
SET value =
    ( SELECT value FROM temp_threshold
      WHERE name = "AUS_AUTO_RULES" )
WHERE name = "AUS_AUTO_RULES";

UPDATE sysadmin:ph_task
SET tk_next_execution =
    ( SELECT tk_next_execution from temp_ph_task
      WHERE tk_name = "Auto Update Statistics Evaluation" )
WHERE tk_name = "Auto Update Statistics Evaluation";

COMMIT WORK;

You can run the above SQL script against sysadmin database to generate UPDATE STATISTICS commands according to Informix guideline. It goes by the following guidelines.

• All tables are updated in LOW mode
• All the leading index keys are updated in HIGH mode
• All non-leading index keys are updated in MEDIUM mode
• The minimum resolution for MEDIUM mode is 2.0
• The minimum confidence for MEDIUM mode is 0.95
• The minimum resolution for HIGH mode is 0.5

The above SQL script has dependencies on syadmin database and database scheduler. SQL script will fail if the database scheduler turned off.

- Sanjit Chakraborty

A new exclusively discussion forum hosted by the IIUG has been set up for business partners to use as a mechanism to collaborate with each other. This forum is only visible and accessible to business partners who sign up for it. Membership is open to any IBM Informix business partner (we will verify your business partner status) and you must also be an IIUG member. To sign up for access, please email Ted Wasserman (tedwas@us.ibm.com) or Gary Proctor (gproctor@us.ibm.com) and provide the main email address in your IIUG member record, or the SIGs email address in your Member record (Under the Membership button). You will be sent a confirmation email when your access has been approved.

IBM Invites you to the Smart Metering Data Summit 2011

Thursday, 15 September, 2011 8:00 AM - 3:00 PM
IBM Briefing Center - Austin, TX, 11301 Burnet Road, Building 101, Austin, TX

Networking Dinner:

Wednesday, 14 September, 2011 6:00 PM - 9:00 PM
Fleming's Steakhouse 11600 Century Oaks Terrace, Austin, TX

Registration Link: http://bit.ly/IBMSMDS

An IBM Proof of Technology

Date: September 14, 2011
Time: 8:30 AM - 4:30 PM
Location: IBM 4th Floor Room 429B, 650 3rd Avenue South, Minneapolis, MN 55402

Introduction

In mid-2007, IBM unleashed the fastest cat in the data server world with Informix Dynamic Server 11. The release strengthened and enhanced Informix Dynamic Server's legendary ease of use with a wide range of additions to its autonomic system-management capabilities; a new, flexible, and extensible system-monitoring and administration tool; a new command-line administrative API to further increase system security; bundled DataBlades for increased application capability with reduced complexity; and much, much more.

The crown jewel of this release was the increased capability added to Informix Dynamic Server's High-Availability Data Replication (HDR) technology. With this new functionality, now called Informix Dynamic Server High Availability clustering (Informix Dynamic Server H/A clustering), you can use additional secondary instance types to create an "availability fabric" to safeguard your Informix Dynamic Server data and provide uninterrupted data services through any type of manmade or natural outage. The simplicity of operations and ease of setup for this clustering technology is outstanding and completely unheard of in the data server market today. As those who use IBM Informix like to say about it, "It just works!" Customers around the world are using the technology and are benefiting from the flexibility and enterprise-quality stability that Informix Dynamic Server H/A clustering provides.

IBM Informix Dynamic Server development has continued to enhance and bring new functionality to the Informix Dynamic Server H/A clustering suite of technologies. In this Proof of Technology, you will create and use the latest version of the Informix Dynamic Server H/A clustering technologies to create a cluster of instances that can provide virtually uninterrupted data services to your environment.

Objective

In this Proof of Technology, you will create and use the full spectrum of Informix Dynamic Server H/A clustering technologies including updatable secondary instances, the Online Connection Manager and Server Monitor agent, Service Level Agreements, delayed application of transactions for protection against SQL errors, and the Failover Arbitrator to create a cluster of instances that can provide virtually uninterrupted data services within a business environment.

Audience

Agenda

Cost

Schedule

Contact for information

To enroll in this Proof of Technology, please contact: Brian Mealman, 612-397-5468, bamealm@us.ibm.com

Date: Sep 8, 2011
Time: 1:00 PM - 2:00 PM (Eastern Time)
Hosted By: Kristine Enes (IBM)
Presented By: Tom Rieger (IBM), Scott Whalen (IBM)

This session is designed to help you learn about why Informix still is the best solution - from the most mundane of data needs, all the way to your largest of systems. We will show examples where Informix is better than any other technology when you combine all of its capabilities.

Come listen to what your competition is telling your customers about 3rd Generation database in-memory technologies and find out how IBM - Informix can deliver unprecedented query response times in a completely integrated, flexible and cost effective environment.

This webcast is intended for technical and business professionals new to Informix as well as individuals familiar with the platform who are interested in learning how to gain incremental value within your environment.

Featuring live Q&A with: Tom Rieger & Scott Whalen, IBM Informix SWAT Team Leaders.

Please join us to learn more about our latest offers available to both existing and new Informix clients!

Register Now: https://events.webdialogs.com/portal/wipevents/register.php?id=338476fce3&am

• The IIUG forums at http://www.iiug.org/forums/technical.php **** these are very good****
• Blogs, Videos, News and more at http://planetids.com *** Up to date feeds from many blogs ***
• Informix On Campus http://informixoncampus.blogspot.com/2011/06/faculty-from-regional-universities-at.html *** New ***
• Louis T.Cherian http://louistcherian.wordpress.com/ *** New ***
• Art Kagel's blog http://informix-myview.blogspot.com/
• Andrew Ford's blog http://www.informix-dba.blogspot.com/
• Mark Jamison at http://www.ibm.com/developerworks/blogs/page/gbowerman
• Jacques Roy at http://www.ibm.com/developerworks/blogs/page/jacquesroy
• Kate Dawson at http://www.ibm.com/developerworks/blogs/page/katedawson
• Fernando Nunes at http://informix-technology.blogspot.com/
• Eric Vercelletto at http://www.levillageinformix.blogspot.com/ (in French)
• Mark Scranton at http://markscranton.wordpress.com/2011/07/23/informix-and-marketing/
• IBM DATABASE MAGAZINE at http://www.ibmdatabasemag.com
• The Informix Zone at http://www.informix-zone.com
• 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.