IBM Support

Set SQLTRACE without restart the server

Question & Answer


Question

SQLTRACE - How can it be turned on without bounce the server?

Answer

If you do not want to set the SQLTRACE configuration parameter and restart the server, you can run the following SQL administration API command, which provides the same function as setting SQLTRACE for the current session:

Database sysadmin;

EXECUTE FUNCTION task("set sql tracing on", 100,"1k","med","user");

After enabling the SQL tracing system in user mode, you can then enable tracing for each user.

EXECUTE FUNCTION sysadmin:task("set sql tracing on");
EXECUTE FUNCTION sysadmin:task("set sql tracing off");

and then unload information from syssqltrace.

The following example enables SQL tracing for the user with the session ID of 74:

EXECUTE FUNCTION task("set sql user tracing on", 74);

The following example enables the tracing of SQL statements of users who are currently connected to the system as long as they are not logged in as user root or informix.

dbaccess sysadmin -<<END
execute function task("set sql tracing on", 1000, 1,"low","user");
select task("set sql user tracing on", sid)
FROM sysmaster:syssessions
WHERE username not in ("root","informix");
END

The following example disables SQL tracing globally:

EXECUTE FUNCTION task('set sql tracing off');
(expression) SQL tracing off.
1 row(s) retrieved.

The following example disables SQL tracing for the session with an ID of 47:

EXECUTE FUNCTION task(“set sql user tracing off”,47);

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.7;12.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
03 June 2021

UID

swg21680644