Advanced DataTools Newsletter – October 2020

1. Are you planning to upgrade to Informix 14.10?  Attend our Informix for Database Administrators Training Course. We have the Informix 14.10 training for you. Lester Knutsen will be teaching our next Informix DBA Class, Informix for Database Administrators updated for Informix version 14.10, on October 5-8, 2020. There is only one space left! More […]

Setting Up Informix To Use systemd

Back in the older days of UNIX systems most sites used SysV to manage all the start and stop scripts from /etc/init.d. Newer versions of Linux have switched over to systemd for managing services as well as to handle logging. The systemctl command controls the services, while journalctl gives you access to the current system […]

How do you use the command-line utilities to manage and monitor an Informix Server? We will discuss onstat, oncheck, and the new InformixHQ to manage your server.


How do you build databases and tables in Informix? We will discuss best practices for creating and managing databases and tables, partitioning, and database security. We will demonstrate object-oriented data types, using SQL to generate SQL Scripts and creating a Non-OS user for Informix database security.


Interview on Informix

Lester Knutsen’s Interview on Informix for G2 The following is a link to an interview Lester Knutsen did on June 17, 2020, on why Informix is my best database! https://www.g2.com/products/ibm-informix/reviews/ibm-informix-review-3266329  

How do you connect users and tools to an Informix Server from Windows, Unix, and the Web? We will discuss connections using Microsoft ODBC, Informix SDK, Java, PHP, DRDA, and other options. This is a Webcast reply from June 25, 2020.


Advanced DataTools Newsletter – July 2020

1. Advanced Informix Performance Tuning – July 13-16, 2020 by Lester Knutsen and Art Kagel Are you ready to take your DBA Skills to the next level? 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, […]

How do you backup and restore an Informix Server? High Availability is crucial to a successful database. We will cover and demonstrate database backups, restores, and setting up High Availability Data Replication (HDR). This replay includes additional material on HDR setup and switching between Primary and Secondary Servers that is not covered in the original webcast.


Migrating and Upgrading to Informix 14 and Migrating to the cloud. The easy and the hard way.

IBM User Group DayPresentation
Migrating and Upgrading to Informix 14 or the cloud. The easy and the hard way.

There are several easy procedures to upgrade and migrate Informix. We will look at the easy way (in-place upgrades, dbexport, cdr migrate, backup/restore), but sometimes they may not work for you, and you need to create a custom process. We will go over a set of procedures and scripts using HPL, external tables, and parallel unloads/loads that I have used over the last few years to migrate from small systems up through terabyte-sized databases to VMs, new hardware, and the cloud.

How do you configure and maintain Informix Logs? The logs are critical to database reliability, and we will discuss best practices for managing and configuring Informix logical logs. We will also cover the backup and recovery of logical logs.


Advanced DataTools Newsletter – April 2020

Our Remote Informix DBA Support is Uninterrupted Advanced DataTools operations are uninterrupted by COVID-19 and we continue to support our customers in these difficult times. All our senior Informix DBAs are effectively working remotely and maintain the high standard of support to which we are committed. Our Informix DBA consultants are working and supporting our […]

Informix Tutorial – Managing Informix Disk Space

Disk IO is often the performance bottleneck for databases, every transaction needs to get written safely to disk fast. How do you set up and configure the disk space for an Informix Server? What disk and RAID configurations provide the best performance and reliability?

Disk IO is often the performance bottleneck for databases, every transaction needs to get written safely to disk fast. How do you set up and configure the disk space for an Informix Server? What disk and RAID configurations provide the best performance and reliability?


Informix Tutorials – Configuring a New Informix Server

Informix Tutorials Webcast Replay – Informix Tutorial – Configuring a New Informix Server by Lester Knutsen, February 27, 2020.

How to script configuring an Informix Server? This Webcast will cover everything you need to know to build with scripts, a new medium, or large server. The Webcast will be a review of the Informix ONCONFIG file and configuring memory, CPUs, Network, and Disk for a more extensive Informix Server configuration.

Informix Tutorials Webcast Replay - Informix Tutorial - Configuring a New Informix Server by Lester Knutsen, February 27, 2020. How to script configuring an Informix Server? This Webcast will cover everything you need to know to build with scripts, a new medium, or large server. The Webcast will be a review of the Informix ONCONFIG file and configuring memory, CPUs, Network, and Disk for a more extensive Informix Server configuration.


Informix Tutorials – Getting Started with Informix

Informix Tutorials Webcast Replay – Getting Started with Informix by Lester Knutsen on Thursday, January 30, 2020. The Webcast is a step by step guide to installing and getting up and running a basic Informix Server. We will demonstrate using InformixHQ, the IBM Informix Knowledge Center, and how to find resources to get started with Informix.

Informix Tutorials Webcast Replay - Getting Started with Informix by Lester Knutsen on Thursday, January 30, 2020, The Webcast is a step by step guide to installing and getting up and running a basic Informix Server. We will demonstrate using InformixHQ, the IBM Informix Knowledge Center, and how to find resources to get started with Informix.


Congratulations Arman Prescott

 21 Years of Great Work Arman Prescott started with Advanced DataTools in January 1999 and was our first Senior Database Consultant to move to Denver. He has supported our projects with the Bureau of Land Management in developing and supporting the Collections and Billing System, which manages over a billion dollars in revenue for over […]

Informix 14.10 Command Line Utilities Quick Reference Guide

I have updated our series of Informix Command Line Utilities Quick Reference Guides. Folks, you can download the Informix 14 Quick Reference Guide here. The Quick Reference Guide includes the following Informix commands: Oncheck Oninit Onmode Onparams Onspaces Onstat Ontape In 1995, after years of trying to find a manual to check the command line […]

Informix has Automated Update Statistics built into the server. Update Statistics collects metrics to enable the server to run your SQL faster. How do you monitor what tables and indexes need to have the statistics updated and manage Automated Update Statistics (AUS)?


Is Your Organization Ready For Informix 14?

Informix 11.70 versions reach end of support in 2020. Is your organization ready to make the change? Informix 14.10 brings in a wide number of new features and options and an entirely new installation method. Contact the experts at Advanced DataTools to help you plan, coordinate, and execute an upgrade plan to get you on […]

How does the Informix SQL Optimizer choose which index or table or access method to use when running your SQL? This presentation will focus on how to view and understand the SQL Explain plan to see how your SQL is executed. The SQL Explain plan will provide times for each step in your process.


Exploring the Sysmaster Database – My New Scripts from the IIUG World 2019 Conference

These are my latest Sysmaster Scripts from my presentation at the Informix IIUG World Conference in 2019. Download presentation PDF: advancedatatools.com/…/Sysmaster_IIUG_Conf_2019_A12_LesterKnutse.pdf Download New Sysmaster 2019 Scripts (zip file): advancedatatools.com/…s/sysmaster2019.zip Download Past Sysmaster 2017 Scripts (zip file): advancedatatools.com/…s/sysmaster2017.zip

This presentation will focus on Best Practices for Informix database indexes. Every table should have at least one index, and managing indexes is an integral part of a DBA's task and time. Indexing is one method to optimize the performance of a database by reducing the number of disk reads when a query is executed. An index is a structure which is used to locate and access a row in a database table quickly. Indexes are used to enforce uniqueness, primary, and foreign keys and constraints. Indexes can also be a source of problems in a database and may slow performance down when misused.


At some point, every system gets outdated or starts showing its age. We all have had to deal with the headaches and logistical nightmares of moving our core databases from a legacy system on to a new server. In this webcast, we will cover different ways in which you can leverage Informix Enterprise Replication to move from an old server to a new one with minimal downtime, headache, and infrastructure needed. We will be covering cases of needing to handle the potential for fail backs, migrating between different OS platforms, as well as covering ideas to minimize end user interruption.


The second part of this webcast will further examine the Informix utilities which can be used to migrate data to a new system, and how to combine different utilities to speed up migrations and minimize downtime.


Migrating your Informix databases to new hardware and operating systems can be a daunting task. During this webcast, we will look at some of the challenges involved, and things to consider when migrating. We will look at the various tools available in Informix which can be used to unload and load data and cover the use of dbexport and dbimport.


IBM just announced the exciting new Informix 14 version in March. Advanced DataTools taught our first course for Informix DBAs on release 14 in April. We have been part of the beta programs and would like to share our findings including how to use the new installer, how to upgrade a database, and an introduction to the new Informix HQ server management.


End to end security is becoming more critical every day. In this webcast, we will demonstrate step by step how to set up basic SSL encryption for the Informix engine and how to configure ODBC clients for Windows and Linux to make sure all of your data is secured in transit.


Informix includes many Advanced SQL OnLine Analytical Processing (OLAP) functions that are rarely used, and we will demonstrate how to take advantage of them. OLAP is Business Intelligence queries that allow users to perform reporting for sales, marketing, inventory control, business processes, and many other functions. Typically it would require a multi-dimensional database. SQL OLAP functions allow you to roll-up, roll-down and slice and dice your data. We will demonstrate examples of Window Partitioning of data (before and after group by), Ranking, using row numbers, and before and after rows using ANSI SQL-99 standards, known as the Elementary OLAP package.


Art Kagel developed and maintains the popular open source utility dostats to enable you to automate and keep up to date catalog statistics and data distribution that the Informix Optimizer needs in order to select the best method of running your SQL statements. He will describe how it works, how to compile it on several platforms, and why you should upgrade to the latest version. He will also discuss the Informix Automated Update Statistics (AUS) and how that compares to dostats.


An overview of using Informix External Tables for unloading and loading data and the benefits that they offer.


How do you capture the cost of SQL statements and activity on your database server. We will take a look at SQL Trace, and the Sysmaster tables that enable you to save and analyze the costs of your SQL.


Whether you are reorganizing a table, loading a table or doing a full data migration, you will want to use the unload/load capabilities of Informix. This is an in depth look at the various options available to you in such an endeavor as well as issues which should be taken into consideration.


Loading and Unloading With Informix – FAQ, by Jack Parker

  Loading and Unloading with Informix   Note, for simplicity these facilities will herein be referred to as ‘loaders’. This is not meant to be a definitive document defining everything there is to know about the subject. This is meant to be a starting point on your road to understanding this topic and a roadmap […]

Have you ever had a large batch job that had to be rerun from scratch when the primary server crashed after the job had been running for many hours? Wouldn't you like to have that job just continue running when your secondary server takes over? You can! This will be a live demonstration of how to configure your servers to take advantage of the Informix Transaction Survival feature.


This presentedesentation will demonstrate how to take advantage of the automatic Range Interval Partitioning and Rolling Windows to fragment your huge Informix tables. Automatically adding new partitions and fragments by interval can replace elaborate fragmentation schemes easily. The feature includes the ability to automatically drop partitions of old data reducing the need to purge old data.


The Informix database depends on the operating system for the CPU, Memory, Disk and Network resources. The presentation will focus on optimizing the Unix, AIX and Linux OS for the best database performance. We will review Kernel Settings, HugePages, enabling and disabling CPUs, OS disk scheduling, SAR and other tools for monitoring Unix and Linux.


For years Informix users who backup to local disks have been stuck with the limits of ontape or the tediousness and slowness of the Informix TSM system. Introduced in version 12 there is the brand new PSM system. Tom will cover the basics of getting started using it and may have you rethinking your backup strategies.


This session will cover Getting Started with Enterprise Replication.


This session will cover comparing similar data types. A review of normalization. Implementing difficult relationships.


This session will cover some of the basics of Informix Tuning, with a focus on monitoring and tuning of queries. Some of the areas that will be covered include: Identifying long running queries, Explain plans, Optimizer directives, Monitoring the buffer pool usage, Checking statistics, and Finding busy tables


This is the latest Webcast replay of Lester's talk on Exploring the Sysmaster database. He presented cripts to monitor and tune your Informix Server and perform a system health check. Examples include how to tell what is the most expensive query running on your server now, what page size is best for your tables, what indexes are used and not used, how is memory used on your server, and much more.... These scripts will help you identify the performance bottlenecks on your Informix Server.


Virtual Machines have many advantages and disadvantages. This Webcast will explore the best practices for running Informix in a Virtual Machine. We will look at small and large databases in virtual machines. A monster virtual machine is a VM with lots of memory, CPUs and disks. I have had the opportunity to performance tune many Informix database servers running in a virtual environment and will share some of the problems and solutions for Informix to work well in VM. We will talk about everything from a VM on my laptop to a VM with more than 256 GB of memory and more than 96 Virtual CPUs.


Mike discusses how to setup and use Informix auditing to monitor the usage of your database servers.


Tom discusses how to setup and use the connection manger to provide fail-over and capability to your database servers.


This is a continuation of an earlier Webcast on Informix Configuration, ONCONFIG, CPUs, and Memory.


Running Informix in a Monster Virtual Machine

Running Informix in a Monster Virtual Machine Lester Knutsen presented at the IIUG Informix Conference April 23-27, 2018 in Raleigh, NC.

A monster virtual machine is a VM with lots of memory, CPUs and disks. I have been dealing with many Informix database servers running in a virtual environment and will share some of the problems and solutions for Informix to work well in VM. We will talk about everything from a VM on my laptop to a VM with more than 256 GB of memory and more than 96 Virtual CPUs.

This webcast will focus on best practices for Informix DBAs on Backup, Recovery, and High Availability Disaster Recovery (HDR).


This webcast will focus on best practices for Informix DBAs including why Not to use RAID5.