Category Archives: Informix DBA(Interview questions)

important configuration parameters

The below are important configuration parameters:

1) INFORMIXDIR: Is the directory where the Informix server is installed

2) INFORMIXSERVER: The Informix instance name

3) INFORMIXSQLHOSTS: This is optional one.
If no entry specified for this then the default entry for this is: $INFORMIXDIR/etc/sqlhosts

The line format in sqlhosts is as below:

<DB Server name> <protocol> <hostname> <port number>
ISAM => Standard Engine (SE) => Online Engine (IDS)
onsoctcp (or) onipcshm
Soc: Using socket
ipc: inter process communication
shm: Shared memory

ONCONFIG: This tells the Informix instance about configuration parameters

PATH: The path for executables. The $INFORMIXDIR/bin should be added to this.

LD_LIBRARY_PATH: Tells where to look for Informix library files on non AIX platform

SHLIBPATH: Tells where to look for Informix library files on AIX platforms
*************Sample Informix instance Environment file******************
export INFORMIXDIR=/opt/informix/run32
export ONCONFIG=onconfig.devc
export TERM=vt100
export TERMCAP=$INFORMIXDIR/etc/termcap


Checking Informix database server status

To check the instance status type “onstat -“ from command prompt as shown in below screenshot

ids9> onstat –

Server Status.png
“On-Line” from above screenshot means the instance is up and running.
9.40.HC7: is the Informix version number
and the instance is on line from 212 days 19:25:41

Note: onstat is an utility that comes with Informix installation. The path of this utility is: $INFORMIXDIR/bin/onstat

Commands to switch between various Informix operating modes

Online Offline Quiescent Single/Admin
Online           — onmode -ky (or) onmode -k Gracefully: onmode –s (or) onmode –sy

Immediate: onmode –u (or) onmode -uy

onmode -j
Offline oninit -v


oninit -yv

           — oninit -s oninit -j


onmode -m onmode –ky (or) onmode -k             — onmode -j
Single User / Administrative onmode  -m onmode -k onmode -s (graceful)

onmode -u (Immediate)

What are various Informix operating modes?

The IBM Informix database server has 4 principal modes of operation:

  1. Online
  2. Single User/Administrative
  3. Quiescent/Maintenance
  4. Offline

OFFLINE  <==>  Quiescent  <==>  Single User <==> ONLINE

1. Online: In this mode, users can connect with the database server and perform all database activities. This is normal operating mode of the database server

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — On-Line — Up 36 days 04:41:25 — 164468 Kbytes

2. Offline: When the database server is not running. No shared memory is allocated

[informix@test1 ~]$ onstat –
shared memory not initialized for INFORMIXSERVER ‘ids13’

3. Single User/Administrative: This is the single user mode. This mode allows Informix user to perform all functions, including the issuance of SQL and DDL commands.

The -j -U option enables DBSA to designate specific users (in addition to the informix user) to access the database server.

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Single-User — Up 00:00:24 — 148084 Kbytes

4. Quiescent/Maintenance: Only administrators can access the database server to perform maintenance functions that do not involve the execution of SQL and DDL statements

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Quiescent — Up 00:01:37 — 148084 Kbytes

Bouncing Informix Dynamic Server (IDS)


Bouncing Informix server = stopping the server instance and then starting again.
Please note that: “Informix server” and “Informix server instance” are interchangeable

Stopping the Server Instance: (online to offline)
onmode –k (or) onmode -ky
The –y option eliminates the automatic prompt that confirms an immediate shutdown

Starting the Server instance: (offline to online)
oninit -v