infx management suite

Simple infx commands: infx dbs

2013-07-24T13:57:00.000+08:00

It has always seemed strange to me, that a database management system, such as Informix, has no command to list all the databases.

You need to use SQL to get this information out.

Of course, if you use infx you can easily list out all the databases in your instance using the infx dbs command.

infx dbs command

Here is an example of the infx dbs command output:

database      space  owner     created    log  buff  ansi  alloc mb    used   data  index  scans
bartest       dat01  informix  07/14/2012   1     0     0      3.08    2.11   1.43   0.69     17
monitor_jgh1  dat01  informix  07/14/2012   1     1     0    201.92  146.91  53.82  93.10     17
mtest         dat02  informix  07/15/2013   1     0     0  1,154.65    2.05   1.27   0.78     14
schema_test   dat01  informix  07/18/2013   1     0     0      2.85    1.90   1.23   0.66     13
stores_demo   dat03  informix  07/20/2013   1     1     0     14.70   12.32  10.50   1.81     30
sysadmin      root   informix  07/14/2012   1     0     0     12.36   10.15   4.84   5.31  3,701
sysmaster     root   informix  07/14/2012   1     0     0      3.66    2.58   1.57   1.01     19
sysuser       root   informix  07/14/2012   1     0     0      3.18    2.19   1.44   0.75     17
sysutils      root   informix  07/14/2012   1     0     0      5.78    3.35   1.71   1.64     17

the output described

column	definition
database	the name of the database
space	the storage space this database was created in
owner	name of the user who created or owns the database
created	date that the database was created
log	1=database has logging, 0=no logging
buff	1=database has buffered logging
ansi	1=database has ANSI mode logging
alloc mb	the total number of mb allocated to this database
used	the total mb used by the database
data	the amount of mb used by the data in the tables
index	the amount of mb used by the indices
scans	the total number of sequential scans executed in this database, since last statistics reset

other options

This command, as with all infx commands, has filtering options for each column.

Example: only databases in the dat02 storage space

demo1@bobii:/home/informix>infx dbs space=dat02

database  space  owner     created     log  buff  ansi  alloc mb  used  data  index  scans
mtest     dat02  informix  07/15/2013    1     0     0  1,154.65  2.05  1.27   0.78     14

Example: change the order so that the largest database is first

demo1@bobii:/home/informix>infx dbs order=alloc

database      space  owner     created     log  buff  ansi  alloc mb    used   data  index  scans
mtest         dat02  informix  07/15/2013    1     0     0  1,154.65    2.05   1.27   0.78     14
monitor_jgh1  dat01  informix  07/14/2012    1     1     0    201.92  146.91  53.82  93.10     17
stores_demo   dat03  informix  07/20/2013    1     1     0     14.70   12.32  10.50   1.81     30
sysadmin      root   informix  07/14/2012    1     0     0     12.36   10.15   4.88   5.27  3,735
sysutils      root   informix  07/14/2012    1     0     0      5.78    3.35   1.71   1.64     17
sysmaster     root   informix  07/14/2012    1     0     0      3.66    2.58   1.57   1.01     19
sysuser       root   informix  07/14/2012    1     0     0      3.18    2.19   1.44   0.75     17
bartest       dat01  informix  07/14/2012    1     0     0      3.08    2.11   1.43   0.69     17
schema_test   dat01  informix  07/18/2013    1     0     0      2.85    1.90   1.23   0.66     13

Any column can be used for filtering and ordering. For more information about the infx command line utilities see: infx command line reference

Video: How to install infx

2012-09-12T10:24:00.000+08:00

A short video demonstration of how to install infx. Less than four minutes and you are done, quicker still if you don't keep stopping to explain it.

Includes:
Setting up Apache
Registering existing instances

infx v1.4b available for download

2012-07-20T11:55:00.000+08:00

infx v1.4b is now available for download at http://www.infx.me/download.

This version mostly contains performance enhancements, especially in the dashboards, as well as some bug fixes and other features.

New features include...
1. infx sqlhelp command line help for the SQL Admin API
2. independent refresh of dashboard sections/consoles

Full release notes are included below.

RELEASE

infx management suite v1.4b new features, bug fixes and notes.

i. NEW FEATURES

a) General

1. Performance enhancements in dashboards.

b) Dashboards
1. Refresh each section/console of the dashboard

c) Command line
1. New command, infx sqlhelp, provides command line help for
SQL Admin API commands.

ii. BUG FIXES

a) General

b) Dashboards
1. Issues with filter criteria
2. Session SQL display

c) Command line

Monitoring informix with nagios and infx

2012-06-07T18:20:00.000+08:00

infx contains a status monitor, that will provide the real time state of an instance. It doesn't record any history, or send any notifications.

If, for example, you want to be notified when there is a problem, you need to use a third party monitoring tool. infx provides the status information about informix, and the monitoring tool records the history and sends notifications etc.

The purpose of this post is to describe the process of integrating the infx status monitor with another monitoring tool, in this case nagios.

overview

set up infx server with nagios user and plug in
create the check_infx command to output the host status
set up nagios sudo permissions for check_infx command
define the infx service check in nagios
restart the nagios service

nagios core

nagios is a powerful monitoring system that allows you to monitor multiple hosts and set up alerts, actions and escalations for problems. It has many powerful features, different front ends and add-ons.

nagios core is the basic version, with a basic interface. This is available for free, with paid support options. There is also a large community of users for support, as well as providing free front-end tools and add-ons.

There are other editions of nagios are available. See www.nagios.com for more details.

nagios with infx

The infx all command can be used to sum up the overall status of a host. This command applies criteria to each Informix instance on the host to determine a status. Each instance status is then combined to produce an overall host status.

If any Informix instance is warning, then the overall status will be warning. If any are error, then the overall status will be error. Otherwise the status is good.

nagios needs a command that produces one line of output that defines the current status. If this overall status is good, the command returns a 0 exit code. Codes of 1 and 2 are used for warnings and errors respectively.

The first step is to create a template to define the output format, and then create a command to act as a plug-in and execute it.

define template output

I use an infx template file to format the status information as required.

For example, create the file /infx/local/template/nagios.htmlt

<TMPL_VAR NAME=HOST_STATUS> <TMPL_VAR NAME=HOST_STATUS_REASON><br>

plug-in command

I created the following shell script called check_infx, this will perform the check and format the output. The steps involved are:

executes the infx all command with the nagios template
translates status e.g. good = ok, warn = warning
outputs status information
exits with appropriate code

code

#! /bin/sh

# determine location of infx software
if [ -z "$INFX" ] ; then
        for dir in /infx /usr/local/infx /home/informix/infx ; do
                if [ -e $dir ] ; then
                        INFX=$dir
                        break
                fi
        done
fi

# get the status information and reformat for nagios
out=`$INFX/scripts/infx all util=nagios | sed -e "s/good/ok/g" -e "s/warn/warning/g"`
status=`echo $out | cut -d' ' -f1 | tr '[a-z]' '[A-Z]'`
status_reason=`echo $out | cut -d' ' -f2-`
echo "INFX $status -- $status_reason"

# determine the correct exit code for this status
case "$status" in
    OK)
        stat=0
        ;;
    WARNING)
        stat=1
        ;;
    ERROR)
        stat=2
        ;;
    *)
        stat=3
        ;;
esac

# return exit code to nagios
exit $stat

For script code without formatting, click here: check_infx

Note: the infx all command automatically looks under /infx/local/template for templates. You could also locate the template in any sub directory of that tree.

Locate this check_infx command under /home/informix/bin. Be sure to set permissions 755 so that no-one but informix can edit the file, but so that others can execute it.

This command must be executed as the informix user, so we will have to set up sudo to allow the nagios user to execute this command as informix.

setup nagios user

If you have not already set up nagios server monitoring, you will need to create a nagios user and group. set the users home directory to /home/nagios.

To allow nagios to execute the check_infx command as the informix user, add the following to the /etc/sudoers file.

nagios    ALL=(informix) NOPASSWD: /home/informix/bin/check_infx

This needs to be done on all servers hosting Informix instances you want to monitor through nagios.

setup nagios config

Perform the following change to your nagios config.

host group

Define a host group for infx servers.

To enable the infx check for a host, you add it the members list of this hostgroup.

define hostgroup {
        hostgroup_name  infx-servers
        alias           Check infx over SSH
        members         bobii     # <-- added our server here
}

nagios service

We define a nagios service for the the check_infx command.

define service{
    hostgroup_name        infx-servers
    service_description   infx
    check_command         check_infx
    max_check_attempts    4
    normal_check_interval 60
    retry_check_interval  1
}

max_check_attempts - specifies the number of times nagios will retry the command. If the state stays the same each time, then nagios will consider that state "hard" and send a notification.

retry_check_interval - specifies how many minutes to wait between re-checks of the service, before setting the "hard" state.

normal_check_interval - specifies how many minutes to wait between status checks. I would suggest fifteen minutes to one hour depending on the host.

With the above settings, nagios will check the infx status each hour. If it has changed, e.g. from ok to critical, nagios will retry the check up to four times, waiting a minute each time. If the status remains critical, then the state is set as "hard" and a notification will be sent.

This helps prevent excessive notifications and notifications for transient conditions.

nagios command

Finally, we define the actual command to be executed that performs the check.

define command{
    command_name    check_infx
    command_line    $USER1$/check_by_ssh -H $HOSTADDRESS$ -C "/usr/bin/sudo -u informix
                                 /home/informix/bin/check_infx"
}

restart

Now we restart nagios, and this will enable the check.

Here is a screen shot of nagios showing the infx status, along with the other standard Linux server checks.

The status information includes the status of each of the three Informix instances on the bobii server.

Summary

Together, nagios core and infx provide a no cost option for centralized monitoring of informix instances.

Once a server has infx installed and is set up under nagios, this command will automatically include all the Informix instances on the host.

You can exclude instances from the check, as well as excluding components of the check. For example, to ignore the backup status check result for an instance, or ignore the instance altogether.

infx controls the settings and thresholds that trigger the warning or error status. Nagios controls the notifications, who receives them, when to notify, when to escalate etc.

See status monitor for more information on the infx status monitor.

For details on how to exclude instances or change the alert levels and settings, see sub-infx-alert.ini.

This provides the basic monitoring of all instances on a host. More detailed instance monitoring can be performed, that will be the subject of future posts.

infx v1.3b available for download

2012-05-14T20:22:00.001+08:00

infx v1.3b is now available for download at http://www.infx.me/download.

This version contains bug fixes as well as enhancements.

New features include...
1. automatic apache set up
2. email notifications
3. ifxclone utility

Full release notes are included below.

RELEASE

infx management suite v1.3b new features, bug fixes and notes.

i. NEW FEATURES

a) General

1. Follow the prompts to install a stand alone Apache

config file when you install or update infx.
2. Alternatively, execute /infx/www/conf/httpd-setup

manually to set up httpd.
3. Automatically send information or error email when

services complete
4. Added table defrag to sysadmin table maintenance functions
5. Command line automatically searches for infx in either

/infx, /home/informix/infx or /usr/local/infx
If you install to a different location, set INFX in your

.profile to the new location
6. Instance discovery service output reformatted and clarified.

b) Dashboards
1. Allow access to archived versions of message files
2. Access task scheduler information
3. Added table defrag to table maintenance dashboard
4. Added drop down menus to interface

c) Command line
1. Script to copy an instance using the ifxclone utility
2. Change rsync to only send infx software by default.

ii. BUG FIXES

a) General
1. Install only runs as informix user
2. Correct backup status display

b) Dashboards
1. List server names in SQL Admin API quick list
2. Fixed pool status in instance list dashboard
3. Fixed modify task to work with negative task ids

c) Command line
1. Fix help for commands that are only available in

specific versions
2. Correctly display the history of just one service
3. Error when service log file cannot be accessed
4. Set up instance alias for discovered instances

File systems for Informix

2012-04-26T11:56:00.000+08:00

When you are setting up an Informix instance you have two options for storing your data. One is to use chunk files, stored in file systems. The other is to access devices directly as raw chunks. This makes a difference to how you allocate the storage. It does not change the way you create and access databases, or the SQL and other commands used.

This post will concentrate on settings and options to use on file systems for chunk files. Other posts will cover using raw devices, RAID and SAN settings etc.

File systems

File systems are designed to efficiently manage hundreds and thousands of files. This is overkill for an Informix database, and some of the file system functionality is already handled internally by Informix. It is best to turn off the functionality that is not needed, and reduce the amount of file system overhead.

To achieve this, it is best to dedicate a file system to Informix chunks, and use other file systems to store applications and files. Then, tune the dedicated file system specifically with Informix chunk files in mind.

File system types

If the database grows, at some point you might need to extend the file system. So try to use a type that can be re-sized with out having to be un-mounted first. Otherwise, you will have to shut the database down completely to un-mount it.

On Linux, ext3 and ext4 file system types allow this. They also do journaling which needs to be turned off.

Turn off journaling

Because the file systems are design to handle large numbers of files, file system journals are used to track file changes. This speeds up recovery time if there is a server crash. But because Informix is using pre-allocated chunk files, and has its own logging, you can turn this feature off.

This improves database write performance.

This command is run once, before the file system is mounted. The setting is permanent and will take effect every time the file system is mounted.

Example: turn off journaling for the file system on device /dev/xvdb

tune2fs -O ^has_journal /dev/xvdb

Turn off volume read-ahead

If you are using a volume manager, such as LVM, you can turn off it's read-ahead feature and let Informix handle the read-ahead itself.

Because Informix is maintaining the internal structures of the chunk files, it has the most information about when to read-ahead.

This command should be performed when the logical volume is first created, or changed while the file system is un-mounted.

Example: turn off read-ahead on an LVM volume called /dev/dbvg/db00_lv.

lvchange /dev/dbvg/db00_lv -r 0

Disable file access time updates

By default, the file system maintains a record of the last access time. This is updated for every read of a file.

While we might be interested in the creation and modification time of the chunks, we can generally live with out the chunk access time.

Turning it off saves us that update every time a database read is performed.

This option is specified each time the file system is mounted.

Example: mount the file system on /dev/dbvg/db00_lv

mount -o noatime /dev/dbvg/db00_lv /db

To set this option each time the server is booted, specify the option in /etc/fstab or equivalent for your platform.

Use Informix direct i/o

To change these settings, you have to bounce the database before they take effect.

Set DIRECT_IO to 1 in your Informix ONCONFIG file. When the chunk file is opened, a flag is set so that the I/O to this file bypasses the file system buffer cache.

Then, allocate memory as buffers to Informix, and let it handle the cache.

Pin the buffer cahce

Where possible, do not have RESIDENT set to 0 in the ONCONFIG file. Either set it to -1 (all memory segments) or 1 to at least pin the buffer cache in memory. This ensures the buffer cache is not subject to paging.

Summary

dedicated file system for Informix chunks
use a re-sizable file system type
turn off journaling
turn off volume manager read-ahead
use noatime
use DIRECT_IO
pin buffer cache in memory

What other tweaks can we do to improve file system performance?

Basic Informix instance customization

2012-04-09T15:08:00.001+08:00

When installing and setting up infx, you may want to customize the Informix environment.

The infx discovery service automatically detects instances and sets their basic Informix connection parameters e.g. INFORMIXDIR, INFORMIXSERVER, and ONCONFIG. You may also need to specify a DBDATE parameter, or other environment variable such as LD_LIBRARY_PATH.

It is important to make these settings before starting an instance with the infx utilities. These variables will be set before start up, as well as when executing the infx services.

infx uses a multi level approach that allows you to apply settings to just one specific instance, or to all instances.

overall config

You make local configuration changes by editing the file /infx/local/etc/config.ini. These settings will be applied to every instance.

Example contents of blank configuration you start with at install.

; config.ini
;

; instance management params
[infxenv]

; set/override environment settings
[env]

; set vars for use in template output etc
[template]
[]

config file format

The infxenv section is used to override settings of Informix connection parameters such as INFORMIXSERVER, INFORMIXDIR, and ONCONFIG, as well as infx specific parameters.

The env section is used to specify general environment parameters such as DBDATE or LD_LIBRARY_PATH.

The template section is used to set special variables that will be used in template output, such as a company name or logo.

You set variables by adding them to the appropriate section, one variable per line.

The syntax and contents of this file are documented here: http://www.infx.me/config-reference.

example: all instances

Change /infx/local/etc/config.ini in the {env] section to set a DBDATE for all instances.

... snip ...
; set/override environment settings
[env]
DBDATE=DMY4/
... snip ...

example: one instance

The format of the instance specific config file is exactly the same as the overall config file, but there is one of these for each instance.

For an instance named demo01, edit the config file /infx/inst/demo01/etc/config.ini.

These settings will only be applied when the demo01 instance is started or when an infx service is executed against it.

distributed environments

If you are managing Informix instances on multiple hosts, you can keep all the options the same by regularly transferring the /infx/local/etc files. Set up the settings on a reference server, and then send the files to remote hosts to automatically configure them.

Alternatively, don't send the /infx/local/etc files and manage settings manually on each of the remote hosts.

host specific settings

If found, infx automatically loads a config file for a specific host.

For example, the host named demo will load the file /infx/local/etc/sub-demo.ini.

The format is the same as the overall and instance specific files.

Put settings in this file that apply to one host, such as IFMX_AIXKAIO_NUM_REQ.

summary

Config files can be used to customize infx settings, instance connection settings, environment variables and template values.

Edit the appropriate config file, based on where you want the setting to take effect.

scope	file
all instances	`/infx/local/etc/config.ini`
only demo01 instance	`/infx/inst/demo01/etc/config.ini`
only loaded on host named demo	`/infx/local/etc/sub-demo.ini`

All config files have the same format.
When you discover new instances, be sure to make these changings before starting the instance using infx.

Installing informix software with infx

2012-03-30T09:03:00.001+08:00

When installing Informix, consider installing to a version specific location, instead of accepting the default.

This will allow you to install multiple versions, and switch between them as needed.

For example, to install and test a new version, while the existing version continues to run.

Use the infx idsinstall command to automatically install Informix software to a version specific location.

The IBM Informix software is available for download from www-01.ibm.com/software/data/informix/downloads.html

Transfer the downloaded file to your server. The infx default location is /home/informix/tar. Any location can be specified with a full path name.

To install, pass the name of the archive to the infx idsinstall command. infx will perform the following steps:

create temporary location
extract tar to temp location
create new version directory under /infx/sw
run the ids_install command

Note: You are still required to answer prompts and accept the license agreement

By default, the command searches /home/informix/tar for the tar file. Specify an absolute path for a different location.

example

Install iif.11.70.FC4TL.linux-x86_64.tar to /home/informix/infx/sw/IIF_11.70FC4TL.

/home/informix>sudo /home/informix/infx/scripts/infx idsinstall tar=iif.11.70.FC4TL.linux-x86_64.tar

install from /home/informix/tar/iif.11.70.FC4TL.linux-x86_64.tar
install to /home/informix/infx/sw/IIF_11.70FC4TL
creating tmp /home/informix/tar/tmp
extracting /home/informix/tar/iif.11.70.FC4TL.linux-x86_64.tar
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

...snip...

===============================================================================
Installation Complete
---------------------

Congratulations! IBM Informix Software Bundle installation is complete.

Product install status:
IBM Informix 11.70: Successful
IBM Informix Client-SDK: Successful
IBM Informix JDBC Driver: Successful


For more information about using Informix products, see the IBM Informix 11.70
Information Center at
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp.

PRESS  TO EXIT THE INSTALLER:
removing /home/informix/tar/tmp
install complete

The install is complete, software was installed to /home/informix/infx/sw/IIF_11.70FC4TL:

test1@awsvpn02:/home/informix>ls -l /home/informix/infx/sw

total 4
drwxr-xr-x  28 informix informix 4096 Mar 28 21:19 IIF_11.70FC4TL
lrwxrwxrwx.  1 informix informix   17 Mar 27 02:43 latest -> /opt/IBM/informix

create new instance

Next, create an instance using this new version. The infx inst func=new commands allows you to specify the Informix software

demo1bhdr@awsvpn02:/home/informix>infx inst func=new newinst=test1 sw=IIF_11.70FC4TL

INFO: creating instance test1 server: 1 config: infx-config.inst onconfig: infx-small.onconfig

...snip...

WARNING: overriding /home/informix/infx/inst/test1/informixdir=latest with sw=IIF_11.70FC4TL
INFO: creating /home/informix/infx/inst/test1/informixdir -> /home/informix/infx/sw/IIF_11.70FC4TL
/home/informix/infx/inst/test1/etc/config.ini created
OK: creating onconfig file /home/informix/infx/inst/test1/informixdir/etc/onconfig.test1
from /home/informix/infx/etc/infx-small.onconfig
onconfig from /home/informix/infx/etc/infx-small.onconfig to /home/informix/infx/inst/test1/informixdir/etc/onconfig.test1
for demo1bhdr
sqlhosts: /home/informix/infx/inst/test1/informixdir/etc/sqlhosts
OK: test1 added to sqlhosts
OK: test1sec added to sqlhosts
add alias test1sec to /home/informix/infx/local/etc/sub-awsvpn02.ini

A new instance was created, with an informixdir pointing to the new version. Next step is to initialize the instance.

test1@awsvpn02:/home/informix>infx inst func=init inst=test1 squiggle=yes

execute oninit -ivy
Your evaluation license will expire on 2012-06-26 00:00:00
DS_NONPDQ_QUERY_MEM recalculated and changed from 1024Kb to 128Kb
Reading configuration file '/home/informix/infx/inst/test1/informixdir/etc/onconfig.test1'
...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded

...snip...

Forking onmode_mon thread...succeeded
Creating periodic thread...succeeded
Starting scheduling system...succeeded
Verbose output complete: mode = 5

Check the instance list to see the version of each instance:

test1@awsvpn02:/home/informix>infx list

num  inst       status  type  version
0    demo1bhdr  Down    IDS   11.70.FC4IE  IBM Informix Dynamic Server Version 11.70.FC4
1    test1      Up      IDS   11.70.FC4TL  IBM Informix Dynamic Server Version 11.70.FC4

Each instance is running a different version of Informix.

modify instance

Use the infx func=idir command to change the Informix software directory for an instance.

Requirements:

instance stopped
ONCONFIG file in new software dirctory
sqlhosts entries if required

When ready, change the Informix software version for the test1 instance:

test1@awsvpn02:/infx/sw>infx inst test1 func=idir sw=latest
informixdir /home/informix/infx/inst/test1/informixdir -> /home/informix/infx/sw/latest

Check the instance list to see the version of each instance:

test1@awsvpn02:/infx/sw>infx list

num  inst       status  type  version
0    demo1bhdr  Down    IDS   11.70.FC4IE  IBM Informix Dynamic Server Version 11.70.FC4
1    test1      Up      IDS   11.70.FC4IE  IBM Informix Dynamic Server Version 11.70.FC4

Now both instances are on the same version.

Automatic Informix instance discovery and set up

2012-03-18T12:41:00.000+08:00

After you install infx, you run the discovery service to locate existing Informix instances. This automatically configures infx to access and manage them.

The infx disco command locates instances in the output of onstat -g dis, reads their ONCONFIG files, and creates the required files and directories.

This all happens automatically in the background, the purpose of this post is to go through each of those steps in more detail.

Step 1. Locate instances

The infx disco command uses the output from onstat -g dis to locate existing instances.

Sample output:

There are 1 servers found
Server        : ol_informix1170
Server Number : 116
Server Type   : IDS
Server Status : Down
Server Version: IBM Informix Dynamic Server Version 11.70.FC4IE
Shared Memory : 0x44000000
INFORMIXDIR   : /home/informix/sw/IIF_11.70FC4IE
ONCONFIG      : /home/informix/sw/IIF_11.70FC4IE/etc/onconfig.ol_informix1170
SQLHOSTS      : /home/informix/sw/IIF_11.70FC4IE/etc/sqlhosts.ol_informix1170
Host          : bobii

Step 2. Set up instance variables

infx uses the following information to connect to the instance:

variable	setting
INFORMIXSERVER	ol_informix1170
INFORMIXDIR	/home/informix/sw/IIF_11.70FC4IE
ONCONFIG	onconfig.ol_informix1170
INFORMIXSQLHOSTS	/home/informix/sw/IIF_11.70FC4IE/etc/sqlhosts.ol_informix1170

Step 3. Set up infx variables

infx calculates other values it needs, by reading the ONCONFIG file:

infx var	variable	value	location
CHKDIR	ROOTPATH	directory portion of path	`/infx/inst/ol_informix1170/etc/config.ini`
instalias	DBSERVERALIAS	alternate instance names	`/infx/local/etc/config.ini`

Step 4. Set up directories

infx requires a few directories for each instance. These are for config information, log files and temp files:

directory	reason
/infx/inst/ol_informix1170	top level directory
/infx/inst/ol_informix1170/etc	instance specific config files
/infx/inst/ol_informix1170/tmp	infx services temp files
/infx/inst/ol_informix1170/logs	infx services log files

Once set up is complete, and you can start executing infx commands on this instance e.g.

demo1b@bobii:/home/informix>infx list

bobii(192.168.0.34) cpus: 2/2 mem: 3.89gb free: 0.03gb time: Sun Mar 18 11:48:16 2012

stat inst            mode   version      up           mem    read   write  informixdir
..   ...             ...    ...          ...          ...     ...     ...  ...
ok   ol_informix1170 online 11.70.FC4IE  00:00:20  0.17gb  93.85%  96.53%  IIF_11.70FC4IE
...    ...           ...    ...          ...          ...     ...     ...  ...

infx disco options

By default, the infx disco command only lists instances and does not automatically set up anything. You must specify additional options to set up any instances.

command	function
infx disco	list new instances
infx disco newinst=ol_informix1170 save=yes	set up new instance called ol_informix1170
infx disco newinst=all save=yes	set up all new instances

infx auto service

The infx auto service is used to automatically execute a command against every instance on a host. For example, this service is used to start and stop the instances at boot time, and to execute automatic backups.

By default, infx creates four files for each new instance. These files prevent the automatic service from executing against the new instance.

When you are ready for infx to include the new instances, remove these files from /infx/inst/ol_informix1170/etc

file	purpose
`deny-auto-backup-dbs`	do not include in automatic backups
`deny-auto-clean`	do not automatically clean storage pools
`deny-auto-start`	do not automatically start instance at system boot time
`deny-auto-stop`	do not automatically stop instance at system shutdown time

For more information about installing and configuring infx, see www.infx.me/1-install

Installing infx

2012-02-28T11:59:00.000+08:00

Just a short note about installing infx.

The infx package file e.g. infx-v1.2b.tar.gz, is a tar archive file.

You cannot gunzip it, use tar to extract the contents directly from the archive instead.

i.e. tar xvf infx-v1.2b.tar.gz

Sorry about any confusion.

Thanks,

Jason

infx v1.2b available for download

2012-02-20T09:26:00.000+08:00

infx 1.2b is available for download at http://www.infx.me/download

This release contains bug fixes and a few enhancements.

It fixes issues with the dashboards under Internet Explorer. Some issues are still outstanding with Firefox. I always recommend you download and install the Google Chrome browser.

It also has enhancements for setting up SSH keys to access remote hosts, and to remotely install and update infx.

The release notes are attached.

Please feel free to contact me with questions and comments.

Thanks,
Jason

RELEASE NOTES

infx management suite v1.2b new features, bug fixes and notes.

i. NEW FEATURES

a) General

1. Work when installed under alternate location, without requiring link to /infx

b) Dashboards

Pop up that allows you to run any infx command. Includes additional information about each command and parameters
Pop up that allows you to run any SQL admin API command. Includes additional information about each command and parameters
New icons, better formatting
Prompt IE user to download and automatically install GCF
Allow access to stored photos from the instance list

c) Command line

infx keygen and infx keysend commands to manage SSH keys
infx rsync command to automatically update remote infx software
Add verbose=yes option to infx help command to output each parameters help information

ii. BUG FIXES

a) General

Moved includes out of local config.ini into sub-include.ini

b) Dashboards

Fix add chunk for pre-11.7 versions

c) Command line

iii. NOTES

a) General

Version 1.1 users must remove all the default include lines from your local config.ini after install. The following lines are no longer required.

; define command line alias

[include sub-infx-cli.ini]

; backup and restore to disk with ontape/onbar

[include sub-infx-ontape.ini]

[include sub-infx-onbar.ini]

; status monitor settings

[include sub-infx-alert.ini]

[include sub-infx-ignore.ini]

; include host specific config

[include sub-${host}.ini]

How to use the infx profile dashboard to get detailed database stats

2011-12-18T10:49:00.001+08:00

A short demonstration of how to get detailed table and index information using the infx table profiler. Includes statistics on sizing, locking and access for each table and index.

View table and index information:

Table access, such as buffer and page read/writes
Table locking, such as a table lock summary and total locking requests
Table space allocations for each table partition and index
Table schema information
Detailed statistics about individual indices
Detailed statistics about individual table partitions

Perform table maintenance actions:

Run the oncheck utility on a table
Table re-size, re-pack, shrink.
Change lock mode of a table

Comparing the Infx Management Suite and Open Admin Tool

2011-11-03T19:52:00.000+08:00

I have just completed an overview that highlights the differences between infx and the Open Admin Tool. It helps explain what infx does and why you might need it!

You can read it here: Comparing infx and OAT

Feature comparison: infx and OAT feature list

Feel free to post questions in the comments or email me directly for more info. In the near future I hoping to announce the beta version...

infx and onstat testing

2011-09-26T15:23:00.000+08:00

I have been independently developing the infx management suite and the testing has gotten to the point where I need some help.

Specifically, what I need is test data, output from Informix onstat commands, message logs and onconfig files.

I am hoping there are people out there who can run some commands on their system, and then send me the output.

infx works by running onstat commands and parsing their output. It also has a "snapshot" mode that takes the onstat output from a file instead. What I hope to do is get onstat files from as many different people as I can, and test them using this mode.

Below is a script that gathers the required data. Note the disclaimer below, make sure you perform your own checks and tests before executing the script.

#!/bin/sh
#
# getdash.sh
#
# Get instance test data for the infx instance dashbaord
# Email output to jgh@jgharris.com.
#
# Setup INFORMIXSERVER, ONCONFIG, INFORMIXDIR and PATH first
#
# Disclaimer:
#  This program s provided as is, without any warranty of any kind,
# either express or implied, including, but not limitied to, the
# implied warranties of non-infringement, merchantability or fitness
# for a particular purpose. The author shall not be liable for any
# loss or damages arising out of your use of the program.
#
# Copyright: 2011 Jason Harris (jgh@jgharris.com), all rights reserved.
# You may freely distribute this program.
#

# values to name the files with
host=`uname -n`
t=`date +%Y%m%d-%H%M`
file=$host-$INFORMIXSERVER-$t

# seperate file for each section
onstat=$file.onstat
onconfig=$file.onconfig
msg=$file.msg
tar=$file.tar

# get onstat output
onstat -i <<EOF >$onstat
-d
-D
-g arc
-F
-x
-l
-p
-u
-k
-g dri
-g con
-g rea
-g act
-g arc
-g ckp
-g seg
-g sql
-g ses
-g osi
-g glo
-g iov
q
EOF

# get onconfig information
onstat -c | sed -e "s/\t/ /g" >$onconfig

# get message log
log=`grep "^MSGPATH " $onconfig | sed -e "s/^MSGPATH  *//" -e "s/ .*//"`
if [ -f "$log" ] ; then
        tail -100 $log >$msg
fi

# make tar file with output
rm -f $tar
rm -f $tar.gz
tar cvf $tar $file.*
gzip $tar
rm $file.onstat $file.onconfig $file.msg

You should initially run this script against test and development instances only.

Instructions:

Click here for a copy without the formatting: getdash.sh
Create a directory i.e. mkdir /home/informix/infx
Create this script i.e. vi getdash.sh, paste contents and save.
Make script executable i.e. chmod +x getdash.sh
Setup your environment to access an Informix instance
Execute the script i.e. ./getdash.sh
Email the .tar.gz file to jgh@jgharris.com, put "infx test data" in the subject line.

The output file should only be a few MB. Perform your own check of the data, and make sure it is appropriate for use in testing.

What will I do with the data I receive? It will be placed on my test system and used to execute tests. It will not be made publicly available or passed onto a third party. On request, all your data can be removed from my servers.

I will send back to you some of the output infx produces from the files, such as instance status, users and space lists.

If you have any questions about this then please contact me. Otherwise thanks for the help and happy onstating!

How to: Informix onbar backups from the infx instance dashboard

2011-09-17T14:25:00.000+08:00

This video shows you how to run Informix onbar backups using the infx instance dashboard.

Use the instance dashboard to:

Run an Informix dbspace backup
Run a fake backup
Run a logical log backup
Review the backup service output
Review the backup command history

Thw demonstration uses the Informix Storage Manager. It contains a brief introduction of how to customize infx to access ISM functionality.

See how to:

Start the ISM daemons
Shut down the ISM daemons
Run the ism show command
Run the ism op command

How to: Informix ontape restore using the infx instance dashboard

2011-09-06T10:15:00.000+08:00

In this video demonstration see how to run ontape restores using the infx instance dashboard.

Use the dashboard to:

Run an Informix complete restore
Run a physical restore
Run a logical restore
Use ontape to copy the data from one Informix instance to another
Review the restore service output

Next up, how to use the onbar utility with infx.

How to: Informix ontape backups from the infx instance dashboard

2011-09-02T08:26:00.000+08:00

This video shows you how to run Informix ontape backups using the infx instance dashboard.

See how to:

Run a dbspace backup
Run a fake backup
Run an external backup
Run a logical log backup
Review the backup service output
Review the backup command history

Next up, how to restore those backups.

Introduction to the infx management suite

2011-08-29T11:44:00.008+08:00

An introduction and demonstration of the infx management suite, the next generation tool for managing Informix databases.

In this introduction I will briefly show you how to:

Get an overview of the status of an instance
Manage alert thresholds
Manage storage spaces
Run a backup
Monitor user sessions

Any of which can be done from a browser or the command line interface.

Watch it in full screen, high definition 720p if available.

What would you like to see me demo next?

How to: Quickly access Informix onstat and onconfig information

2011-08-16T02:42:00.002+08:00

Do you frequently find yourself needing quick access to simple information about your Informix instance? Info such as onconfig variables or other parameters?

While you can create templates with infx to specify the information you need, that is overkill when you just want to know something simple, such as the instance server number.

To fill this need, the infx status service has a simple interface that allows ad hoc access to the full details of the onstat parser library.

Lets take the instance server number as an example:

demo1@bobii:/home/informix>infx status vars=onconfig_servernum
23

Lets say I also want to know the onconfig MSGPATH parameter:

demo1@bobii:/home/informix>infx status vars=onconfig_servernum+onconfig_msgpath
23 /infx/inst/demo1/logs/online.log

In a script, these values could easily be read into variables:

#!/bin/sh

infx status vars=onconfig_servernum+onconfig_msgpath | read server msg

echo server=$server msgpath=$msg

You can also access list values, such as the name of each chunk file in an instance:

demo1@bobii:/home/informix>infx status list=chunkfile_list vars=path
/infx/inst/demo1/chks/sblob01.000
/infx/inst/demo1/chks/dat01.001
/infx/inst/demo1/chks/sblob01.001
/infx/inst/demo1/chks/log02.000
/infx/inst/demo1/chks/root.001
/infx/inst/demo1/chks/tmp01.000
/infx/inst/demo1/chks/dat02.000
/infx/inst/demo1/chks/log01.000
/infx/inst/demo1/chks/ltmp01.000
/infx/inst/demo1/chks/plog.000
/infx/inst/demo1/chks/blob01.000
/infx/inst/demo1/chks/dat01.000
/infx/inst/demo1/chks/idx01.000
/infx/inst/demo1/chks/tmp02.000
/infx/inst/demo1/chks/dat04.000

That one can be pretty handy when you want to run an external backup e.g.

tar cvf /backup/ext.tar `infx status list=chunkfile_list vars=path`

The onstat parser reference contains all the lists and variables available
informix onstat and onconfig information

How to: Informix HDR using infx services

2011-06-18T08:47:00.000+08:00

While infx itself does not contain any services for Informix database replication, it can still be used to simplify the process.

The Informix High Availability Data Replication (HDR), is used to replicate all the databases in an Informix instance to another host. Informix Enterprise Replication (ER) is used to replicate individual databases and tables.

In this example I will show how to setup Informix HDR with infx services. The example assumes that Informix and infx are already installed on both servers.

The basic process is:

create a new instance on the second server
modify it to be a secondary instance
copy the primary instance data with the hot copy service
start HDR on the secondary
start HDR on the primary

Step 1: Create new instance

On the second server, create a new instance to be the secondary.

noenv@cloud2:/home/informix>infx inst func=new newinst=test1dr
noenv@cloud2:/home/informix>infxenv test1dr
test1dr@cloud2:/home/informix>

Step 2a: Change Informix ONCONFIG

Use the infxonconfig command to edit the ONCONFIG file of the new instance. We need to match the settings on the primary server, minimally this means changing the ROOTPATH.

Change ROOTPATH /infx/inst/test1dr/chks/root.001 to /infx/inst/test1/chks/root.001

Step 2b: Change infx chunks directory

Change the chunks directory to match the primary server. Use the infxconfig command to edit the infx config file and add to the [infxenv] section:

[infxenv]
CHKDIR = /infx/inst/test1/chks

Step 3: Hot copy data from primary server

To start HDR, we first need to synchronize data between the primary and secondary servers. I will use the hot copy function of the restore service to run an ontape backup on the primary and restore it directly to the secondary.

test1dr@cloud2:/home/informix>infx restore inst=test1dr type=hot source=test1@cloud1
restore service is continuing in background, pid=14033
test1dr@cloud2:/home/informix>

To monitor this restore processes you can:

Use infx follow to monitor the restore service log
Use infx dbspace to monitor the dbspace status
Use infx service to monitor the status of the restore service
Use infx status to monitor the status of the instance

Example output, infx dbspace command showing dbspace output during restore:

test1dr@cloud2:/home/informix>infx dbspace
class  space  pg  flags       chks  size mb  free mb  used mb   used%  read mb  write mb
warn   root   2k  IP RESTORE     1   100.00    91.83     8.17   8.17%     0.00      3.84
error  dat01  2k  DOWN DOWN      1   100.00    99.89     0.11   0.11%     0.00      0.00
error  dat02  2k  DOWN DOWN      1   100.00    99.89     0.11   0.11%     0.00      0.00
error  plog   2k  DOWN DOWN      1   100.00     4.89    95.11  95.11%     0.00      0.00
error  log01  2k  DOWN DOWN      1   100.00    66.89    33.11  33.11%     0.00      6.38
error  log02  2k  DOWN DOWN      1   100.00    66.89    33.11  33.11%     0.00      0.00

The output shows that the root space is being restored, and all others are still down.

Once the hot copy completes, the secondary will be in fast recovery mode, ready to start HDR replication.

Step 4a: Add primary server to sqlhosts file

Use the infxsqlhosts command to add the primary server to the Informix sqlhosts file. Add this entry:
test1 onsoctcp cloud1 62001

test1dr@cloud2:/home/informix>infxsqlhosts

Step 4b: Start HDR on secondary

Once the restore is complete, use the onmode command to start HDR replication on the secondary.

test1dr@cloud2:/home/informix>onmode -d seoondary test1
test1dr@cloud2:/home/informix>

Step 5a: Add secondary server to primary sqlhosts file

On the primary server, use the infxsqlhosts command to add the secondary server to the Informix sqlhosts file. Add this entry:
test1dr onsoctcp cloud2 62001

test1@cloud1:/home/informix>infxsqlhosts

Step 5b: Start HDR on primary

Use the onmode command to start HDR replication on the primary.

test1@cloud1:/home/informix>onmode -d primary test1dr
test1@cloud1:/home/informix>

Use the onstat -m command and check the instance message log.

test1@cloud1:/home/informix>onstat -m
...
11:45:20 DR: new type = primary, secondary server name = test1dr
11:45:20 DR: Trying to connect to secondary server = test1dr
11:45:21 DR: Primary server connected
11:45:21 DR: Secondary server needs failure recovery

11:45:25 Logical Log 9 Complete, timestamp: 0x2e016.
11:45:25 DR: Sending log 9, size 5500 pages, 58.05 percent used
11:45:26 Logical Log 9 - Backup Started
11:45:31 Logical Log 9 - Backup Completed

Once sending of the logs and synchronization is complete, HDR replication will be on.

Check primary server status

Use the infx status command and check the primary server status.

test1@cloud1:/home/informix>infx status

test1 (online(prim)) ver: 11.50.FC7IE up: 00:13:20

host                   user            memory          space         write           prof
name  cloud1   active    19   total  186.39mb  total  0.60gb  total     16   read  67.05%
cpu   1/1         max    24     res  120.85mb   free  0.43gb     fg      0  write  36.00%
mem   0.51gb    total   128    virt   65.54mb   temp  0.00gb    lru      0  ckpts      14
free  0.04gb  threads    19    free   34.11mb   free  0.00gb  chunk     16  waits       4

 status: good
    hdr: test1dr (good:on)
 backup: 2011-05-24 11:39 (9) ll avail: 100%
threads: 1 active: soctcppoll(1)

Check secondary server status

Check the status of the secondary instance.

test1@cloud1:/home/informix>infx status test1dr@cloud2

test1dr (readonly(sec)) ver: 11.50.FC7IE up: 00:13:20

host                   user            memory          space         write           prof
name  cloud2   active    34   total  186.39mb  total  0.60gb  total      4   read  89.68%
cpu   1/1         max    34     res  120.85mb   free  0.43gb     fg      0  write   0.00%
mem   0.51gb    total   128    virt   65.54mb   temp  0.00gb    lru      0  ckpts      16
free  0.01gb  threads    34    free   27.82mb   free  0.00gb  chunk      4  waits       0

 status: good
    hdr: test1 (good:on)
 backup: 2011-05-24 11:39 (9) ll avail: 100%
threads: 1 active: soctcppoll(1)

Summary

infx simplified this process by:

Creating the secondary instamce, ONCONFIG file and sqlhosts entries
Creating chunk files that match the primary server
Providing the correct environment for the primary server ontape backup

There are a few other things infx could do, such as using the new ifxclone command from v11.7. But that will be the subject of a different post.

What do you find is the most annoying thing about setting up and managing Informix HDR instances?

Inside infx: How the onstat parser works

2011-05-28T02:50:00.001+08:00

The majority of information you see from infx has come from the output of the Informix onstat command. Only a small percentage (at this time anyway) comes from the sysmaster database.

There are two reasons for that:

onstat is available even when sysmaster isn't
onstat outputs loads of information and it's very fast

availablity

The onstat command can be used to track the database instance, even when it's not online. For example, monitoring the progress of a cold restore or fast recovery.

output by template

You output information from the onstat parser via a template. Using that template, infx determines a subset of onstat options required.

For example, the variable PROFILE_CACHEREAD requires the "-p" option, the DBSPACE_NAME requires "-d", and so on.

execute onstat

infx runs all options in a single execution of the onstat command, using the -i (interactive) option.

To run -g sql, -g ses and -g ckp in a single execution of onstat:

test1@cloud1:/home/informix> onstat -i <<EOF
> -g sql
> -g ses
> -g ckp
> q
> EOF
...
test1@cloud1:/home/informix>

parser

The onstat command places an "onstat>" prompt between each options output, this is used to separate them before parsing.

In the case of output like the "-p" option, there is one variable for each value parsed. In the case of options like "-g sql", a list is created to hold the values for each line in the output.

Normal variables are output by name e.g.:

<TMPL_VAR NAME=PROFILE_CACHEREAD>

Lists are output using loops e.g.:

<TMPL_LOOP NAME=USERS>...</TMPL_LOOP>

For the complete list of variables see parser library.

flexibility

You can access the onstat parser via browser based dashboards, or with command line utilities.

All output is easily customized, all you have to do is modify the template, infx automatically determines the new set of onstat options.

summary

infx simplifies monitoring and managing Informix databases by giving you easy access to information about the instance.

Comments and suggestions welcome.

Simplified management of informix databases

2011-05-18T20:54:00.000+08:00

For over 20 years I have been working as a DBA with Informix, and in that time I have been asked by clients to script or automate just about any database related task you can think of.

My latest challenge has been to bring that experience together into a suite of flexible and easy to use utilities.

I have built the basic or "core" version, it's a start, but there are still more areas to be covered.

What this blog is for:

announcements about the infx software
discussion about what it does
discussion about what it should be doing

And the first announcement is that the site, infx.me, is now up with the basic documentation and description of infx.

Up for discussion, am I crazy trying this, or is it about time someone did?