Documentation forServer & Application Monitor
Monitoring your applications and environment is a key capability of SolarWinds Observability Self-Hosted (formerly Hybrid Cloud Observability) and is available in the Essentials edition. Server & Application Monitor (SAM) is also available in a standalone module.

IBM Informix

This SAM application monitor template assesses the performance of an IBM Informix database by retrieving performance data from the built-in System Monitoring Interface (SMI) tables located in the sysmasterdatabase.

Prerequisites

Install IBM Informix 11.70 on the target server, and install an IBM Informix ODBC driver on the SolarWinds Platform. The driver can be found in the IBM Informix Client SDK on the IBM site. Your database should be accessible by using the olsoctcp protocol.

By default, all components use the following ODBC connection string:

>Driver={IBM INFORMIX ODBC DRIVER};Host=${IP};Server=ol_informix1170;Service=ol_informix1170;Protocol=olsoctcp;Database=sysmaster;Uid=${USER};Pwd=${PASSWORD};

Adjust this string to suit your environment by updating the Server and Service fields.

Credentials

Database user name and password.

Minimum permissions

Create an OS user (for example: monuser) and add this user into “Informix” group. This is default Informix installation user group. The following highlighted instructions can be found at: http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.igul.doc%2Fids_in_005x.htm.

Create the group informix and user informix

Typically the installation application creates these required objects, but you may need to create them before installing. The Mac OS X installation application automatically creates group and user informix in all circumstances, so this task does not apply to Mac computers.

You need to create the objects before you run the installation application in the following situations:

  • You want to specify a particular identifier (ID) number.
  • The group informix exists on the system; however, the user informix does not. In this case, you need to create the user only.

If you plan to install IBM Informix products using RPM Package Manager and user informix and group informix do not exist on the target computer, you must create these objects on the operating system before performing the RPM-based installation.

To create the group informix and user informix:

  1. Create the group informix by using the groupadd utility followed by the name of the group, in this format: groupadd n informix
    where n is an unused identifier (ID) greater than 100. On AIX®, use the mkgroup command instead of groupadd.
  2. Create the user informix by using the useradd utility followed by the group (informix) and user name (informix) in this format:
    useradd -u n -g informix informix
    where n is an unused identifier (ID) greater than 100.

    Only add users to the group informix if the users need administrative access to the database server.

  3. Create a password for user informix by running the passwd utility.
  4. Login as a DBA (informix user) to “sysmaster” database on your Informix instance (For example: ol_informix1170).
  5. Under DBA user, create stored procedures described in the “For IBM Informix installed on Linux” section below and grant execute permissions on these procedures to the “monuser” by using the following SQL query: GRANT EXECUTE ON procedure_name TO monuser;
  6. Edit /etc/sqlhosts and /etc/services. This step is described in the “For IBM Informix installed on Linux” below.
  7. Correct the Server and Service properties in the ODBC connection string. Adjust the “Platform to run polling job on” option as needed.

For IBM Informix installed on Windows

The following commands should be executed on the IBM Informix server before using this template. These commands will create all the necessary stored procedures for monitoring your server from SAM:

--Page Reads /sec
CREATE PROCEDURE APM_P_READS ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'pagreads';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'pagreads';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Page Writes /sec
CREATE PROCEDURE APM_P_WRITES ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'pagwrites';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'pagwrites';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Committed Transactions /sec
CREATE PROCEDURE APM_TR_COM ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'iscommits';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'iscommits';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Rolled back Transactions /sec
CREATE PROCEDURE APM_TR_ROL ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'isrollbacks';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'isrollbacks';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Latch Request Waits /sec
CREATE PROCEDURE APM_LATCH_WAIT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'latchwts';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'latchwts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Lock Requests /sec
CREATE PROCEDURE APM_L_REQ ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockreqs';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockreqs';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Lock Waits /sec
CREATE PROCEDURE APM_L_WAIT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockwts';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockwts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Deadlocks /sec
CREATE PROCEDURE APM_DEADL ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'deadlks';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'deadlks';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;
				
--Sequential Scans /sec
CREATE PROCEDURE APM_SEQ_S ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'seqscans';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'seqscans';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Sorts /sec
CREATE PROCEDURE APM_SORTS ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'totalsorts';
SYSTEM "timeout 10";
SELECT value INTO y FROM sysprofile WHERE name = 'totalsorts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Number of Network Connections /sec
CREATE PROCEDURE APM_CONNECT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT ng_connects INTO x FROM sysnetglobal;
SYSTEM "timeout 10";
SELECT ng_connects INTO y FROM sysnetglobal;
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;
  1. On the IBM Informix server, open the setnet32 utility found in the IBM Informix folder. Select the Host Information tab and add the following:
    • Current Host: APM-server
    • User Name: your_username (e.g.: Informix)
    • Password Option: Password
    • Password: username_password
  2. Open C:\Windows\System32\drivers\etc\services and add the following line: service_name 1528/tcp #INFORMIX where service_name is name of your Informix instance (for example: ol_informix1170).
  3. Add a firewall rule for tcp port 1528.

For IBM Informix installed on Linux

Execute the following SQL commands:

The only difference between the prior SQL commands and the following SQL commands is that the string SYSTEM "timeout 10"; has been changed to SYSTEM "sleep 10"; multiple times throughout.

--Page Reads /sec
CREATE PROCEDURE APM_P_READS ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'pagreads';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'pagreads';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Page Writes /sec
CREATE PROCEDURE APM_P_WRITES ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'pagwrites';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'pagwrites';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Committed Transactions /sec
CREATE PROCEDURE APM_TR_COM ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'iscommits';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'iscommits';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Rolled back Transactions /sec
CREATE PROCEDURE APM_TR_ROL ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'isrollbacks';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'isrollbacks';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Latch Request Waits /sec
CREATE PROCEDURE APM_LATCH_WAIT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'latchwts';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'latchwts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Lock Requests /sec
CREATE PROCEDURE APM_L_REQ ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockreqs';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockreqs';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Lock Waits /sec
CREATE PROCEDURE APM_L_WAIT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockwts';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockwts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Deadlocks /sec
CREATE PROCEDURE APM_DEADL ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'deadlks';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'deadlks';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Sequential Scans /sec
CREATE PROCEDURE APM_SEQ_S ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'seqscans';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'seqscans';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Sorts /sec
CREATE PROCEDURE APM_SORTS ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'totalsorts';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'totalsorts';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

--Number of Network Connections /sec
CREATE PROCEDURE APM_CONNECT ()
RETURNING DECIMAL(8,2);
DEFINE x,y,res DECIMAL(8,2);
SELECT ng_connects INTO x FROM sysnetglobal;
SYSTEM "sleep 10";
SELECT ng_connects INTO y FROM sysnetglobal;
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;

Open the /etc/sqlhost:

Dbservername Protocol APM-server Service_name where:

  • Dbservername is the database name
  • Protocol is the protocol used to connect to the database (put olsoctcp)
  • APM-server is the hostname of your SolarWinds Platform server
  • Service_name is the name of your Informix instance

For example:

ol_informix1170 olsoctcp myAPM ol_informix1170

Open the /etc/services file and add the following line:

service_name 1528/tcp #INFORMIX

where service_name is the name of your Informix instance (for example: ol_informix1170).

Add a firewall rule for TCP port 1528.

Component monitors:

Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application.

Read Cache Hit %

This monitor returns the percentage of the read cache rate. The read and write cache rates can vary dramatically depending upon the applications and the type and size of the data being operated on. In general, both the read cache rate and write cache rate should be in the 80 to 90th percentile. If these rates are consistently lower than 80%, you should consider increasing the value of the Buffersparameter in your Informix configuration file to achieve higher read and write cache rates. Low read and write cache rates indicate IDS is doing a lot more disk reads and writes than it should, which will greatly slow down overall database engine performance.

Write Cache Hit %

This monitor returns the percentage of write cache rate. The read and write cache rates can vary dramatically depending upon the applications and the type and size of the data being operated on. In general, both the read cache rate and write cache rate should be in the 80 to 90th percentile. If these rates are consistently lower than 80%, you should consider increasing the value of the Buffersparameter in your Informix configuration file to achieve higher read and write cache rates. Low read and write cache rates indicate IDS is doing a lot more disk reads and writes than it should, which will greatly slow down overall database engine performance.

Page Reads /sec

This monitor returns the number of physical database page reads issued. This value should be as low as possible. Higher values may indicate indexing or memory constraints.

Page Writes /sec

This monitor returns the number of physical database page writes issued. This value should be as low as possible. Higher values may indicate indexing or memory constraints.

Committed Transactions/sec

This monitor returns the number of committed transactions rate, per second.

Rolled Back Transactions/sec

This monitor returns the number of rolled back transactions rate, per second.

Latch Request Waits/sec

A latch was the first method that was used in Informix products to protect shared memory resources from being accessed by multiple users at one time. This monitor returns the number of events, per second, when a thread had to wait for a latch.

Buffer Waits Ratio

This monitor returns the buffer waits ratio using the following formula: BR = (bufwaits/(pagreads + bufwrits)) * 100.

If this value is below seven, everything is considered fine. If this value is between seven and ten, you can expect some sluggishness in response times. If this value is greater than ten, it is likely that system responses are very slow.

Suggested resolutions:

  • Increase the value of the Buffers parameter if the number of unused buffers is zero, and/or if viewing onstat -P over time, you see a small number of partnums trading large percentages of the buffer cache back and forth.
  • Increase the value of the LRUS and Cleaners parameters significantly. (The Cleaners parameter should always be greater than or equal to the value of the LRUSparameter for the best LRU flush performance.

    Avoid 32 & 64. A known bug may still remain with Informix that causes very poor LRU contention at those values.

Lock Requests/sec

A lock is used to reserve access to a database object. This monitor returns the rate of events, per second, that sessions requested a lock.

Lock Waits /sec

A lock is used to reserve access to a database object. This monitor returns the rate of events, per second, when sessions had to wait for a lock.

Deadlocks/sec

A deadlock occurs when two users hold locks and each user wants to acquire a lock that the other user owns. Informix uses the lock table to detect deadlocks automatically and stop them before they occur. This monitor returns the deadlocks rate, per second. This value should be as low as possible.

Sequential Scans/sec

This monitor returns the sequential scans rate, per second. This value should be as low as possible. If the value of this monitor is constantly high and continues to increase, it may indicate some performance problems, especially if your system is in an OLTP environment. You should investigate further to determine the root cause of excessive sequential scans.

Sequential access to tables can impact performance since the database engine needs to scan entire tables to pick up rows that satisfy the query's conditions.

If a table is small (a few hundred rows), the database engine can scan tables that reside in memory. When the next scan occurs, table data can be retrieved directly from memory. However, if the tables are large (over 100,000 rows), sequential scans may negatively impact performance.

For tables with a large number of sequential scans, it is recommended to add indexes to thr table or use program directives to force the internal query optimizer to choose indexes for accessing data in this table, rather than sequential scans.

Sorts/sec

This monitor returns the sorts rate, per second.

Number of Databases

This monitor returns the number of databases presented in this instance.

Number of DBspaces

This monitor returns the number of database spaces presented in this instance.

Number of Network Connections/sec

This monitor returns the cutwork connections rate, per second.

Locks Overflow

This monitor returns the number of times IDS attempted to exceed the maximum number of locks. If this number is non-zero, you may need to increase the value of the Locksparameter in the configuration file.

Datadbs available space (MB)

This monitor returns the available space, in MB, for database space - datadbs. This value should be as high as possible. You can change which dbspace to monitor by correcting the name in this line of your SQL query: and name = 'datadbs'