Troubleshooting
Problem
When checking the Informix message file online.log, you see several messages like this: 15:03:10 Explain file for session 111 : /opt/IBM/informix/1150fc8w3/sqexplain.out.111 15:08:01 Explain file for session 117 : /opt/IBM/informix/1150fc8w3/sqexplain.out.117 15:08:54 Explain file for session 118 : /opt/IBM/informix/1150fc8w3/sqexplain.out.118 15:09:10 Explain file for session 119 : /opt/IBM/informix/1150fc8w3/sqexplain.out.119 15:09:01 Explain file for session 120 : /opt/IBM/informix/1150fc8w3/sqexplain.out.120 15:09:54 Explain file for session 121 : /opt/IBM/informix/1150fc8w3/sqexplain.out.121 15:10:10 Explain file for session 122 : /opt/IBM/informix/1150fc8w3/sqexplain.out.122 15:10:01 Explain file for session 123 : /opt/IBM/informix/1150fc8w3/sqexplain.out.123 15:10:54 Explain file for session 124 : /opt/IBM/informix/1150fc8w3/sqexplain.out.124 In this case, several output files can be found in the /opt/IBM/informix/1150fc8w3 filesystem causing system overload, filesystem full and performance bootlenecks.
Symptom
All the sessions connecting to a certain database seem to have the 'explain for queries' turned on automatically causing the explain files to be generated.
In high intensive use systems, this can cause I/O performance issues and filesystem full warnings.
Cause
The sysdbopen() IBM Informix internal stored procedure (SPL) was created and it's running the 'set explain on' statement in its procedure body.
Diagnosing The Problem
1. Check if the sysdbopen() SPL is in the UDR Cache Entries of the IBM Informix using the onstat -g prc | grep sysdbopen command:
UDR Cache Entries:
list# id ref_cnt dropped? heap_ptr udr name
--------------------------------------------------------------
6 413 0 0 22cca6838 stores@prod:.sysdbopen
2. In this case, the sysdbopen () SPL was created within the stores database. Try running the dbschema command below to check for the sysdbopen() SPL body:
$ dbschema -d stores -f sysdbopen
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.FC8W3
create procedure "public".sysdbopen()
...
< Other SQL statements >
...
set explain on;
...
< Other SQL statements >
...
end procedure;
3. When checking the Informix message log file, online.log, you will see several messages like this:
15:03:10 Explain file for session 111 : /opt/IBM/informix/1150fc8w3/sqexplain.out.111
15:08:01 Explain file for session 117 : /opt/IBM/informix/1150fc8w3/sqexplain.out.117
15:08:54 Explain file for session 118 : /opt/IBM/informix/1150fc8w3/sqexplain.out.118
15:09:10 Explain file for session 119 : /opt/IBM/informix/1150fc8w3/sqexplain.out.119
15:09:01 Explain file for session 120 : /opt/IBM/informix/1150fc8w3/sqexplain.out.120
15:09:54 Explain file for session 121 : /opt/IBM/informix/1150fc8w3/sqexplain.out.121
15:10:10 Explain file for session 122 : /opt/IBM/informix/1150fc8w3/sqexplain.out.122
15:10:01 Explain file for session 123 : /opt/IBM/informix/1150fc8w3/sqexplain.out.123
15:10:54 Explain file for session 124 : /opt/IBM/informix/1150fc8w3/sqexplain.out.124
Resolving The Problem
You can avoid the sysdbopen() SPL execution with one of the following options:
1. Set the IFX_NODBPROC environment variable to any value, including 0, to cause the database server to bypass and prevent the execution of the sysdbopen( ) stored procedure;
2. Using DBAccess, drop and re-create the sysdbopen () SPL removing or just putting a comment symbol (--) in the 'set explain on' statement line:
$ dbaccess stores -
Database selected.
> drop procedure "public".sysdbopen();
Routine dropped.
> create procedure "public".sysdbopen()
> ...
> < Other SQL statements >
> ...
> -- set explain on;
> ...
> < Other SQL statements >
> ...
> end procedure ;
Routine created.
3. Using DBAccess, just drop the sysdbopen () SPL:
$ dbaccess stores -
Database selected.
> drop procedure "public".sysdbopen();
Routine dropped.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21576621