Join IIUG
 for   
 
Insider

IIUG Insider (Issue #151)
January 2013

Other Issues

Highlights: The New IBM Informix: It's Simply Powerful

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 Back to top

We are beginning a new year. In February we are starting the board election process. Please participate. If you are not willing to join the board at least vote for those who do.

We are excited about 2013. In 2013 Informix 12.10 will be launched. The EVP program is on its way.

The IIUG Informix conference will take place in San Diego. This conference will have new cool stuff about the new Informix version.

I am starting another year as the IIUG Insider editor. I will do my best to keep you up to date with the Informix news, events and technical content. The Insider is not just a source of information. It is your voice. If there is anything you would like to share, ideas or suggestions email me.

Enjoy 2013.

Gary Ben-Israel
IIUG Insider Editor

Highlights Back to top
The New IBM Informix: It's Simply Powerful

IBM Webcast: The New IBM Informix: It's Simply Powerful

Join us on March 5th at 1pm ET to learn how IBM Informix, an exceptional database software, can help you take your business into the future, right now! Whether you are looking for more efficient operational analytics; deploying applications to the private cloud; working with sensor or meter data; or just looking to increase your productivity and usability, the latest release of this powerful software brings you a cost-effective solution that addresses all your data management requirements.

Register today to learn more about the new IBM Informix!

Conference corner Back to top
2013 IIUG Informix Conference sessions

We have Confirmations!

We are excited that so many of our selected speakers are confirming their invitations to present at the 2013 IIUG Conference in San Diego, April 21 - 25. We have a great selection of topics being offered this year by several new speakers and many of your favorite speakers.

Take a minute and check out just a few of the sessions that will be presented at the conference:

Getting PHP and PDO_Informix to Play Nicely: An Intro

This class will go over the basics of installing PHP with the PDO_Informix driver manually. This can be useful if you are looking at adding into PHP with Informix support into your environment or if you wish to deploy OAT manually to a system where you have more control over the webserver. This will also cover some basics of testing and tuning the connection to the engine.

Pushing the Envelope: Adventures in Upgrading Informix Legacy Systems the "New" way

Working with several different versions of Informix on several operating systems spanning multiple computer environments has given me great insight into upgrading Informix legacy systems. This presentation will be about many of the challenges we have faced and how we have overcome many of them to keep our systems fairly current.

Building Mobile Mission-critical applications on Informix (Real-case study).

Our client, Nespresso-Spain, asked us for a project to solve two business issues:

  1. In Nespresso's Boutiques, sometimes long lines are formed at the cash registers thus decreasing the expected service level. They wanted a solution to speed up customers standing in the line whiteout the need to put more Points of Sale or dedicating more Boutique space to install them.
  2. To increase its brand awareness, Nespresso installs temporary Boutiques in selected outdoor events, like golf or tennis tournaments. Instead of having costly impersonal line ups, they wanted a point of sale in the palm of salesperson's hand with the capability to raise the level of interaction with the client with the possibility of being by their side. At the same time, the system should require a simple infrastructure and offer a high transactional speed to operate these Boutiques.

We came up with our Axional Mobile POS, coupled with an Informix database.

It can operate both on-line and off-line and offers outstanding customer service. One of the key success factors is its capability to process the sales transaction in a fraction of second including the data verification and consolidation with the server. This high performance can be achieved thanks to the Informix database installed in a server, interfaced with their SAP system.

The system includes a sophisticated synchronization mechanism between Informix database and the tablets to allow the mobile to devices work off/on-line.

To speed up the application development we used our software platform which allows IT departments to dynamically roll out business applications to mobile users with Android devices, from an Informix centralized system. This platform is designed according to a scalable multi-tenant architecture. A multi-service architecture allows for a high transactional load and is prepared to work on a cloud environment.

Informix Storage Optimization in a Large Database

How the Informix Data Compression Storage Optimization feature can help you manage the storage usage increasing in a VLDB without additional budget and improving the performance to access data into the database.

Useful Business Analytics SQL operators and more

The set operators (UNION, INTERSECT and MINUS) are useful in business analytic contexts. They can be used in performing integrity checks of your database after you have done a DML operation like UPDATE, INSERT, DELETE, or MERGE. The set operators can similarly be used when you transfer data to a history table, for example, when you need to verify that the correct data is in the history table before you delete rows from the original table. With the introduction of INTERSECT and MINUS in IDS, this presentation will highlight how and where these operators can be used.

Table Join Tricks

Learn about ANSI and Informix join performance and techniques with examples. This presentation will demonstrate performance improvement in ANSI & Informix style queries and its in depth analysis. It will show how IDS performs transformation on ANSI style joins, join method selection (HASH join & Nested Loop join) and Index selection process to achieve performance benefits. Examples will include analytical functions, derived tables, sub query, intersect/minus operators, remote query, hierarchical query.

The MERGE statement is used to perform update/delete or insert of specified target table rows based on the condition specified in the ON clause. The Merge statement implemented in IBM Informix Dynamic Server follows SQL 2003 standard. MERGE is a useful command, especially in data warehouse environments where transaction tables are merged into bigger warehouse tables. MERGE also replaces the client side logic performed today to do conditional updates and inserts (also known as UPSERT logic).MERGE statement provides ease-of-use and performance as the entire operation is performed within the server with a singleton statement.

Informix Network Encryption

There are a couple of options available for using network encryption for Informix client/server communications. Option 1 is to use Communication Support Modules (CSM). Option 2 is Secure Sockets Layer (SSL). Both require setup and configuration changes on both the Informix server and the application client. This presentation will briefly explain the 2 methods and provide steps to install and configure the necessary components.

Implementing BiTemporal Data Using Informix

In this session, learn what bitemporal data is, how it can improve the quality of your data, and how it can help your organization respond to regulatory and data audit requirements for point-in-time historical perspective reporting.

Not all data is as perfect as it could be. Sometimes, we're forced to work with incomplete or incorrect data, and correct things later as a more complete picture of reality becomes available. Sometimes, reality just changes.

Modifying data in the database is simple enough. A simple UPDATE, INSERT, and/or DELETE statement and the work is done. But, in systems that require, for auditing and other point-in-time perspective reporting purposes, reproducibility of reports between runs, a straightforward update, insert, or delete may be counter-productive. Doing such a data modification can destroy the possibility of re-creating the same output on consecutive runs of the same query.

The solution to this problem is known as bitemporal data versioning or simply BiTemporal Data. Attendees will examine the problem space and learn one solution that can be implemented in Informix today.

How to use more of your memory

The Growth Edition of Informix Dynamic Server allows no more than 16 GB shared memory to be allocated. This primarily limits buffers (i.e. disk cache) to around 12 GB, while still leaving enough for other essential memory pools. However, modern machines typically have much more RAM than this, which could be put to better use.

This presentation will provide the complete process to use spare memory as RAM disk file systems. Their contents are volatile, so they can only be used for temporary tables (DBSPACETEMP) and sort/merge files (PSORT_DBTEMP). The commands to create them on all supported Linux and UNIX flavours will be given, along with other relevant environment and configuration parameter settings.

Temporary data is then never written to disk, dramatically improving run times of larger queries and preventing interference with OLTP sessions by reducing buffer turnover. In an actual case, disk writes were reduced by 96%, eliminating SAN contention with other applications.

Even using Ultimate Edition, index builds in temp dbspaces are not only slower but can crash the instance on some versions if they run out of space, which is avoided by setting PSORT_DBTEMP to use file systems, and run time is shorter still if these are RAM disks.

Note that RAM disk should not be confused with solid state drives (SSD), which are persistent and a better technology for logical and physical logs when combined with RAID 10.

It is hoped that other old hands at the conference will attend, so that different perspectives can be discussed after the practicalities have been covered.

Indexing techniques: which one to use when

This presentation will make a global picture of all the available indexing techniques, from "antique" and generic techniques to indexing features in 11.70 and Centaurus.

Each technique will be described, jointly with an application case study supported by my tpc-c informix benchmark, whose purpose will be to demonstrate the efficiency of each technique.

Large Informix Database Migration with No or Minimal Downtime

Introduce a sequence of steps on how to use dbexport/dbimport to migrate a large scale of distributed/replicated databases from one OS plate form to another OS plate form. The migration procedure can be implemented with no downtime or minimal downtime which greatly improves system availability.

As Easy as ABC! The Performance Made Easy Button - Lessons Learned

Performance Tuning Made Easy - Performance tuning is hard, everyone knows that.

Attend this session for a fast track to desired performance. It will discuss several tips, techniques & tricks for a varied audience of developers, DBAs, architects, system administrators & managers and provide an excellent guide to obtaining optimal performance.

Leave this session knowing what actions to take when you get back to work on Monday.

  • Objective 1: Focus on system performance
  • Objective 2: Focus on application performance
  • Objective 3: Focus on database performance
  • Objective 4: Focus on writing efficient SQL
  • Objective 5: Provide an A-to-Z checklist for an overall IDS Health Check

Using IFXCLONE to Setup or Re-Sync HDR

I will describe in detail how to use the new IFXCLONE utility to setup or re-sync your HDR environment. There is little documentation on the details of this process, and I learned several valuable lessons in our implementation. I will show some of the gotchas that will waste valuable time if you have to learn them on your own.

What is RAID, Tips to define Infrastructure, case study, tips for choosing Ontape or Onbar

What is RAID, an explanation of the main RAID levels. A Case study using vRAID in Storage shared among VLDB Server and App Servers environment making amazing performance improvements in the Informix Databases and Applications in general. Some tips to choose the Informix OnTape or OnBar programs, depending on the Infrastructure regarding to storage, network among servers and backup devices.

Web Application Security

This class is for anyone who is writing or maintaining applications that in any way interfaces with the larger networked world. We will be covering basic best practices for securing your applications and hopefully securing your job a little better. We will be demonstrating live examples of how to bypass security measures to better show how to protect against it.

IWA configuration & deployment on real world

Presentation about a real customer experience and how the IWA has been installed and deployed and a performance analysis in real numbers. This presentation is a walk on all aspects of IWA and a good knowledge base for users that expects to put their IWA installation on work.

  1. Hardware selection
  2. Tips about IWA installation: # of coprocessors, topology, memory,...
  3. Datamart definition and data refresh automazation
  4. Performance analysis between two different hardware configurations
  5. Real complex test comparison between IWA, IDS and non-columnar in memory DB
  6. Identifying the less and most time consuming SQL sentences on IWA
  7. How IWA uses the computer memory. Analysis of real resources required by IWA
  8. How IWA could be used on a large company on a day by day basis. User Experience.
  9. Real SQL operations in numbers: performance comparison about time used on some DWH enterprise tasks
  10. Experiences about deploying a Analytics tool with IWA

This is just a sampling of the sessions you will find at the 2013 IIUG Conference. Don't forget to keep checking the website at www.iiug2013.org/. The site will be updated as additional conference sessions, tutorials and hands-on labs are confirmed.

Don't forget the Thursday tutorials are included with your paid registration. Register today!

See you April 21 -25, 2013 in San Diego, California.

IIUG Conference Planning Committee

Informix News Back to top
Articles

Informix accelerates analytic integration into OLTP
Read the OVUM Report by Madan Sheina: "Informix accelerates analytic integration into OLTP"

IBM Informix in hybrid workload environments
This report by Philip Howard of Bloor Research takes a detailed look at Informix in a mixed workload environment.

The Pre-eminence of IBM Informix TimeSeries : Part 1 and Part 2
This article talks about the superior performance of the IBM Informix TimeSeries feature.

Support corner Back to top
Create a user define task that run periodically to perform administrative work

Like a lot of fellow System Administrators, you may need to execute many system or administrative works automatically. Some of these works should occur at regular intervals. Other works need to run only once, perhaps during off hours such as evenings or weekends. You can use the Informix DB Scheduler to create a user define task to run administrative work automatically in the background at predictable times. The DB Scheduler uses SQL statements instead of operating system job scheduling tools, e.g., cron job.

In this document you will see an example of user define task that deletes some unwanted files on a periodic basis. For better understanding you can perform this work in two phases.

  1. Create a stored procedure that performs the actual delete operation.
  2. Define a task that executes the above stored procedure at a certain interval.

Create a stored procedure

First you are creating a procedure under 'sysadmin' database named 'delete_apb_documents_proc()' that removes all files as 'apb_document*' from /tmp directory which were accessed more than 60 minutes ago.


CREATE PROCEDURE sysadmin:delete_apb_documents_proc()

DEFINE cmd CHAR(80);
LET cmd = "find /tmp -maxdepth 1 -type f -name 'apb_document*' -amin +60 -exec rm {} \;";
SYSTEM cmd;

END PROCEDURE;

Use the UNIX manual page for 'find' command (man find) to get details on options used in the above example.

Define a task

Next, defining a task named 'delete_apb_document()' which runs at the top of each hour and executes the procedure delete_apb_documents_proc().


INSERT INTO sysadmin:ph_task
(
tk_name,
tk_group,
tk_description,
tk_type,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency
)
VALUES
(
"delete_apb_document",
"MISC",
"Remove apb_document files from /tmp directory that last accessed 60 minutes ago",
"TASK",
"EXECUTE PROCEDURE sysadmin:delete_apb_documents_proc(); ",
"01:00:00",
"23:00:00",
INTERVAL ( 60 ) MINUTE TO MINUTE
);

Check the Informix Information Center to get more information on how to define a task.

You can run all the above SQL statements against 'sysadmin' database using dbaccess utility.

- Sanjit Chakraborty

Informix corner Back to top

A Message from your Marketing Team

Happy New Year to all our valued clients and business partners! Welcome to the new "Informix Round-Up" newsletter designed to keep you up to date on all of the latest Informix news. I wanted to personally thank you for a very strong 2012, where we continued to make enhancements to our portfolio, including our recent announcement of the new Informix Hypervisor Edition, increasing business agility by providing an innovative, performance based foundation to build, reuse, run and manage database applications within virtualized environments. Our business continues to grow and we continue to garner recognition amongst analysts, within IBM, and the marketplace. Here's to an even more successful 2013! I hope to see you at the 2013 IIUG Informix Conference on April 21-25 in San Diego.

Janet Oswald
Program Director,
Database Software & Systems Marketing
Awareness & Demand Programs
IBM

Do you have an Informix champion in you?

Candidates may be nominated by another individual or they may self-nominate before the 28th of February, 2013. We are looking for individuals: customers, business partners, consultants, professors, students, who have demonstrated a strong impact within the community. For a list of current IBM Champions, please visit here. Nominate your champion today by filling up this form.

Case Study

Myers utilizes IBM Informix to provide a complete, world class and affordable broadcast management solution
Abstract: Read how Myers Information Systems has become a dominant player in broadcasting management solutions by embedding IBM Informix in their software, keeping the complexity of data management invisible to their end users.

Webcast

Webcast Replay: "How to Lower Costs by Combining Analytical & Transactional Applications"
Abstract: Listen to "How to Lower Costs by Combining Analytical & Transactional Applications" where Philip Howard, Research Director of Data Management for Bloor Research discusses various analytics and data warehouse environments and the facilities required to support each. Kevin Brown, Informix Database Chief Architect, IBM, gives an overview of Informix functionality.

Educational News

Lu Chuan, one of the IBM Informix technical experts in China, has just published a new book about Informix 11.x. It is currently only available in Chinese (here), but hopefully will be translated into other languages soon.

The University of Missouri-Kansas City (UMKC) has opened enrolment for a new course offering, "Introduction to Health Informatics", using IBM Informix. It will be taught by one of our long time faculty partner's, Professor Praveen Rao, as a "Special Topics" graduate level course. Check out the details of the course description.

System Administration Certification Exam 919 for Informix 11.70 prep, Part 7: Informix Data Warehousing
Abstract: This certification preparation for System Administration for Informix 11.70 lets you learn about IBM Informix Data Warehousing concepts and the tools that you can use to create data warehouses and optimize your data warehouse queries.

Informix Flexible Grid Bootcamp

Thursday, April 25, 2013 from 9:00 AM to 4:00 PM (PDT)

San Diego Marriott Mission Valley
8757 Rio San Diego Drive
San Diego, CA 92108

SAVE THE DATE — Act Now Save your spot for the upcoming Free Informix Flexible Grid Bootcamp after the IIUG Conference. Register for the conference today!

Contact: Anita McKeithen - mckeithe@us.ibm.com

We hope you can make it!

Cheers,

Anita McKeithen - IBM
mckeithe@us.ibm.com

Sponsor corner Back to top
Advanced DataTools

Database Driven Websites - Using PHP with Informix
by Thomas Beebe, Advanced DataTools Corporation

You are invited to attend our next Webcast on Tuesday, February 12, 2013 at 2:00 pm, Eastern Standard Time

What do Wikipedia, Facebook, Wordpress, and Yahoo all have in common? They all run on PHP. PHP is an open source server-side scripting language designed for Web development to produce dynamic Web pages. And it works great with the fastest database, Informix. In this talk we will be discussing how quick and easy it is get going in PHP and how to set up PHP to talk to Informix using the PDO driver library. You can easily use PHP to select, insert, update and delete data from an Informix database to create a database driven web site.

Register here.

Informix Training at Advanced DataTools

Our 2013 Informix Training Schedule is online. We have classes for new Informix DBA's or advanced Informix DBA's. Attend an advanced class taught by Lester Knutsen and Art Kagel. Register early as our classes filled up last year.

  • March 11-14, 2013 - Informix for Database Administrators. This course is for database administrators, programmers, and technical support personnel who will be setting up, managing, and tuning IBM Informix 11, 10, or 7 databases.
  • July 15-18, 2013 - Advanced Informix Performance Tuning. This course is for database administrators and application developers who will be responsible for managing, optimizing, and tuning an Informix 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 Informix database server.
  • September 9-12, 2013 - Informix for Database Administrators. This course is for database administrators, programmers, and technical support personnel who will be setting up, managing, and tuning IBM Informix 11, 10, or 7 databases.
  • October 21-24, 2013 - Advanced Informix Performance Tuning. This course is for database administrators and application developers who will be responsible for managing, optimizing, and tuning an Informix 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 Informix database server.

We guarantee to NEVER cancel a course and will teach a course as long as one student is registered! See our website for our schedule and more information.

Informix resources Back to top
IBM Informix home page

http://www.informix.com or directly at: http://www-01.ibm.com/software/data/informix/

Informix blogs and Wikis

I am trying something new on this section. I will first mention the blogs and wikis that have been updated during the last month. Blogs and wikis that have not been updated for over 6 months will be removed.

Blogs and Wikis that have been updated during the last month

More Blogs and Wikis

Forums, Groups, Videos, and Magazines  
Useful links Back to top

http://www.iiug.org/quicklinks.html

Closing and credits Back to top

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.

Sources:  IIUG Board of Directors
IBM Corp.
Editors:  Gary Ben-Israel
Rhonda Hackenburg

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