Oninit
Talk to an expert →

← All articles

Technical Articles

Informix Server-Side SQLIDEBUG: Enabling, Disabling, and Understanding sqli_dbg

Informix® provides a low-level SQLI tracing facility commonly known as SQLIDEBUG. It can be enabled on the client side, but Informix also supports a server-side SQLIDEBUG facility through the special sqli_dbg virtual processor class.

The server-side facility is useful when diagnosing SQLI-level client/server behavior, because it can generate binary SQLI trace files that can later be decoded with sqliprint.

This article focuses specifically on:

  • enabling server-side SQLIDEBUG
  • disabling it safely
  • understanding why sqli_dbg can remain visible after tracing is disabled
  • interpreting CPU usage for the sqli_dbg VP
  • determining whether tracing is actually active
  • avoiding a potentially serious mistake: killing the sqli_dbg operating-system process directly

The behavior described here was tested on IBM Informix Dynamic Server 14.10.FC13W7.

Enabling Server-Side SQLIDEBUG

Server-side SQLIDEBUG can be enabled with:

mkdir -p /tmp/sqli
onmode -p 1 sqli_dbg

The special VP class is sqli_dbg. Once enabled, Informix creates SQLI trace data beneath /tmp/sqli, depending on version and platform configuration.

The resulting files are binary SQLI traces rather than normal text log files. They are intended to be decoded with the Informix sqliprint utility, for example: 

sqliprint trace_file

or, depending on the version of sqliprint:

sqliprint -o output.txt trace_file

sqliprint is typically supplied as part of the Informix Client SDK and may not be installed on every Informix server host.

Confirming That sqli_dbg Is Running

The VP can be seen using:

onstat -g glo

For example:

Virtual processor summary:
 class       vps       usercpu   syscpu    total
 ...
 sqli_dbg    1         0.29      0.61      0.90

It also appears in the individual virtual processor list:

vp    pid       class       usercpu   syscpu    total
64    959995    sqli_dbg    0.29      0.61      0.90

This confirms that an Informix VP of class sqli_dbg exists. It does not, by itself, prove that SQLIDEBUG tracing is currently active — that distinction becomes important once SQLIDEBUG is disabled.

Disabling Server-Side SQLIDEBUG

The corresponding onmode command is:

onmode -p -1 sqli_dbg

Testing on Informix 14.10.FC13W7 showed an initially surprising result: onmode -p -1 sqli_dbg stopped the creation of SQLIDEBUG trace files, but the sqli_dbg VP remained visible in onstat -g glo. For example: 

class       vps       usercpu   syscpu    total
sqli_dbg    1         0.29      0.61      0.90

and the individual VP remained present:

64    959995    sqli_dbg    0.29      0.61      0.90

This means that the presence of the VP is not a reliable indication that tracing is still enabled.

The Important Observation

After issuing onmode -p -1 sqli_dbg, no additional files were created beneath /tmp/sqli, and existing trace files stopped growing. Therefore, on the tested FC13W7 system, the practical state was: 

SQLIDEBUG tracing: OFF
sqli_dbg VP:       still resident
trace generation:  stopped

The negative onmode -p command therefore appears to disable the active SQLIDEBUG tracing workload while leaving the sqli_dbg VP resident inside the engine. This is an important operational distinction.

Why onstat -g glo Can Be Misleading

After tracing was disabled, onstat -g glo continued to show accumulated CPU usage for the sqli_dbg VP. For example: 

sqli_dbg    1    0.29    0.61    0.90

These values are cumulative CPU counters. They should not be interpreted as meaning that the VP is actively consuming that amount of CPU at the moment the command is run — the fields represent CPU time accumulated since the VP started.

A stack inspection showed the VP in a yield state, which is consistent with a mostly sleeping or waiting VP.

Checking the Operating-System Process

The corresponding operating-system process can be inspected with ps. For example:

while :; do
    ps -p 1002154 -o pid,pcpu,time,stat,cmd
    sleep 5
done

Example output:

    PID %CPU     TIME STAT CMD
1002154  0.2 00:00:00 S<   oninit -vw
1002154  0.2 00:00:00 S<   oninit -vw
1002154  0.2 00:00:00 S<   oninit -vw

The important fields are S, which indicates that the process is sleeping, and TIME, which is accumulated CPU time.

The %CPU value reported by ps should not be treated as instantaneous CPU consumption. It can continue to show a small non-zero value even while the process is currently sleeping.

Measuring CPU Usage Directly Through /proc

On Linux, a more precise check can be made using /proc/<pid>/stat. Fields 14 and 15 are:

14 = utime
15 = stime

These are the accumulated user and system CPU ticks for the process. For example:

while :; do
    awk '{print $14,$15}' /proc/1002154/stat
    sleep 5
done

Observed output:

22 49
22 50
23 51

The kernel clock tick rate can be determined using:

getconf CLK_TCK

On the tested system: 100. Therefore, 1 CPU tick = 0.01 seconds.

The sample changed from 22 + 49 = 71 ticks to 23 + 51 = 74 ticks over approximately ten seconds. That represents 3 ticks, or 0.03 CPU seconds — approximately 0.3% of one CPU core during the sample period.

This proves that the resident sqli_dbg VP was not completely dormant. It still woke occasionally and consumed a very small amount of CPU. However, this did not mean that SQLIDEBUG tracing remained active — the trace directory showed no additional files or file growth. The more accurate description is therefore: 

sqli_dbg VP:       resident
thread state:      mostly sleeping/yielding
CPU activity:      very small periodic wakeups
SQLIDEBUG tracing: disabled
trace output:      stopped

How to Determine Whether SQLIDEBUG Is Actually Active

Do not rely solely on onstat -g glo | grep sqli_dbg, because the VP may remain present after tracing has been disabled. The definitive test is whether SQLIDEBUG trace data continues to be written.

Before disabling:

ls -ltr /tmp/sqli

Disable tracing:

onmode -p -1 sqli_dbg

Then run some known SQL workload and check again:

ls -ltr /tmp/sqli

A more useful monitoring command is:

while :; do
    find /tmp/sqli -type f -printf '%p %s %T@\n' 2>/dev/null
    sleep 2
done

If no new files are created and the sizes of existing files do not increase, tracing has stopped even if the sqli_dbg VP remains visible.

Do Not Kill the sqli_dbg PID

This is the most important operational warning in this article.

The operating-system PID associated with sqli_dbg is an Informix virtual processor. It is not an external tracing daemon. For example:

vp    pid       class
64    959995    sqli_dbg

The corresponding process might appear in Linux as oninit -vw.

Do not attempt to stop SQLIDEBUG using kill <pid> or kill -9 <pid>. The VP is part of the Informix engine. Testing showed that killing the sqli_dbg operating-system process can cause the Informix engine to crash.

The correct mechanism for disabling tracing is:

onmode -p -1 sqli_dbg

Even if the VP remains visible afterward.

Recommended Operational Procedure

Enable tracing. Create the trace directory if required, and enable the SQLIDEBUG VP:

mkdir -p /tmp/sqli
onmode -p 1 sqli_dbg

Confirm its presence:

onstat -g glo | grep sqli_dbg

Monitor trace creation:

ls -ltr /tmp/sqli

Disable tracing. Run:

onmode -p -1 sqli_dbg

Do not expect the sqli_dbg VP necessarily to disappear from onstat -g glo. Instead, verify that trace activity has stopped: 

ls -ltr /tmp/sqli

Run a representative SQL workload and check again. If no new files are created and existing files are not growing, SQLIDEBUG is no longer tracing.

Optional CPU verification. To confirm that the remaining VP is only minimally active:

PID=<sqli_dbg_pid>

while :; do
    awk '{print $14,$15}' /proc/$PID/stat
    sleep 5
done

Determine the Linux tick rate:

getconf CLK_TCK

For a typical value of 100, each tick represents 0.01 CPU seconds. Small occasional increases are consistent with a resident VP periodically waking and yielding — they are not, by themselves, evidence that SQLIDEBUG tracing remains enabled.

Practical Status Matrix

ObservationMeaning
sqli_dbg absent from onstat -g gloVP not present
sqli_dbg present and trace files growingSQLIDEBUG active
sqli_dbg present and new trace files appearingSQLIDEBUG active
sqli_dbg present, stack yielding, no file growthSQLIDEBUG effectively disabled
Small CPU-tick increases after disableResident VP periodically waking
Killing the VP PIDUnsafe — will crash the Informix engine

Key Takeaways

  • Server-side SQLIDEBUG can be enabled with onmode -p 1 sqli_dbg, and disabled with onmode -p -1 sqli_dbg.
  • On Informix 14.10.FC13W7, disabling SQLIDEBUG did not remove the sqli_dbg VP from onstat -g glo. The VP remained resident and occasionally consumed a very small amount of CPU.
  • The authoritative practical test for whether tracing is active is trace-file activity, not simply the existence of the sqli_dbg VP.
  • If /tmp/sqli stops receiving new data after the -1 command, tracing has stopped.
  • Never kill the sqli_dbg operating-system PID directly. It is an Informix VP, and doing so can crash the database engine.

Tested Environment

The observations in this article were made using:

  • IBM Informix Dynamic Server 14.10.FC13W7
  • Linux
  • CLK_TCK = 100

Behavior on older Informix releases, different fix packs, or other operating systems should be verified before assuming it is identical.

A Note on Production Use

The sqli_dbg facility is a low-level Informix diagnostic mechanism intended for troubleshooting. Because it records SQLI activity and can generate significant trace volume, it should be enabled only for the period required to capture the problem being investigated.

Production use should always include:

  • sufficient free filesystem space
  • a defined trace window
  • monitoring of /tmp/sqli
  • controlled shutdown using onmode
  • confirmation that trace-file activity has stopped

References

  • IBM Informix Developer’s Handbook — IBM Redbook SG24-7884. The primary source for the server-side SQLIDEBUG command described in this article. It explicitly documents onmode -p 1 sqli_dbg as a method for enabling SQLIDEBUG tracing at the server side, and discusses the SQLIDEBUG trace and the use of sqliprint.
  • Tomáš Zahradník (IBM Advanced Technical Support), CIDUG presentation. An older community presentation that also discussed server-side SQLIDEBUG and showed the sqli_dbg enable/disable form. Useful supporting historical documentation rather than the primary authority — the IBM Redbook above, and the current Informix Administrator’s Reference, are the stronger sources for the behavior this article documents.