Specifying startup SQL tracing information by using the SQLTRACE configuration parameter

Use the SQLTRACE configuration parameter to control the default tracing behavior when the database server starts. By default, this parameter is not set. The information you set includes the number of SQL statements to trace and the tracing mode.

Before you begin

Any user who can modify the onconfig file can modify the value of the SQLTRACE configuration parameter and effect the startup configuration. However, only user informix, root, or a DBSA who has been granted connect privileges to the sysadmin database can use SQL administration API commands to modify the runtime status of the SQL tracing.

About this task

To specify SQL tracing information when the database server starts:

Procedure

  1. Set the SQLTRACE configuration parameter in the onconfig file.
  2. Restart the database server.

Example

The following setting in the onconfig file specifies that the database server gathers low-level information about up to 2000 SQL statements executed by all users on the system and allocates approximately four megabytes of memory (2000 * two KB).
SQLTRACE level=LOW,ntraces=2000,size=2,mode=global

If you use only a percentage of the allocated buffer space (for example, 42 percent of the buffer space), the amount of memory that is allocated is still two KB.

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:
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. See Enable SQL tracing.

For more information about using task() and admin() functions, see the HCL OneDB™ Administrator's Reference.

For more information about the SQLTRACE configuration parameter, including minimum and maximum values for some fields, see the HCL OneDB Administrator's Reference.