Home
Administering
In addition to administering the database server, you can tune performance, replicate data, and archive data.
System administration
These topics contain concepts, procedures, and reference information for database and database server administrators to use for managing and tuning HCL Informix® database servers.
Administrator's Reference
The Informix® Administrator's Reference includes comprehensive descriptions of HCL Informix configuration parameters, the system-monitoring interface (SMI) tables in the sysmaster database, the syntax of database server utilities such as onmode and onstat, logical-log records, disk structures, event alarms, and unnumbered error messages.
Configuring and monitoring Informix®
Database configuration parameters
The Informix® database server uses a configuration file, which is called the onconfig file, during initialization. This file contains default configuration parameter values. You can modify the parameter values to improve performance and other characteristics of the instance or database.
STMT_CACHE_QUERY_PLAN configuration parameter
Use the STMT_CACHE_QUERY_PLAN configuration parameter to produce a query plan from any query that exists in the Statement Cache.

Administering
In addition to administering the database server, you can tune performance, replicate data, and archive data.
- System administration
  These topics contain concepts, procedures, and reference information for database and database server administrators to use for managing and tuning HCL Informix® database servers.
  - List of utilities
    HCL Informix® includes utilities and applications that you can use to perform administrative tasks and capture information about configuration and performance.
  - Administrator's Guide
    The Informix® Administrator's Guide provides the information required to administer HCL Informix.
  - Administrator's Reference
    The Informix® Administrator's Reference includes comprehensive descriptions of HCL Informix configuration parameters, the system-monitoring interface (SMI) tables in the sysmaster database, the syntax of database server utilities such as onmode and onstat, logical-log records, disk structures, event alarms, and unnumbered error messages.
    - Configuring and monitoring Informix®
      - Database configuration parameters
        The Informix® database server uses a configuration file, which is called the onconfig file, during initialization. This file contains default configuration parameter values. You can modify the parameter values to improve performance and other characteristics of the instance or database.
        onconfig file
        When you add or change information in the onconfig file, you must follow the conventions that are used in the file.
        onconfig Portal: Configuration parameters by functional category
        The information in this section lists configuration parameters as they are in the UNIX™ onconfig.std file.
        ADMIN_MODE_USERS configuration parameter
        The ADMIN_MODE_USERS configuration parameter specifies a list of users, besides the user informix and members of the DBSA group, that you want to access the database server in the administration mode.
        ADMIN_USER_MODE_WITH_DBSA configuration parameter
        The ADMIN_USER_MODE_WITH_DBSA configuration parameter specifies which users, besides the user informix, can connect to the database server in the administration mode.
        ALARMPROGRAM configuration parameter
        Use the ALARMPROGRAM configuration parameter to specify the full pathname of the alarmprogram file that handles event alarms and controls logical-log backups.
        ALLOW_NEWLINE configuration parameter
        Use the ALLOW_NEWLINE configuration parameter to allow or disallow newline characters in quoted strings for all sessions.
        ALRM_ALL_EVENTS configuration parameter
        Use the ALRM_ALL_EVENTS configuration parameter to specify whether the ALARMPROGRAM configuration parameter runs for all events that are logged in the MSGPATH configuration parameter, or only for noteworthy events.
        AUTO_AIOVPS configuration parameter
        The AUTO_AIOVPS configuration parameter enables the database server to automatically increase the number of asynchronous I/O virtual processors (AIO VPs) and page cleaner threads when the database server detects that the I/O workload outpaced the performance of the existing AIO VPs.
        AUTO_CKPTS configuration parameter
        The AUTO_CKPTS configuration parameter allows the server to trigger checkpoints more frequently to avoid the blocking of transactions.
        AUTO_LLOG configuration parameter
        Use the AUTO_LLOG configuration parameter to automatically add logical logs in the specified dbspace to improve performance.
        AUTO_TUNE_SERVER_SIZE configuration parameter
        Use the AUTO_TUNE_SERVER_SIZE configuration parameter to set the sizes of memory and storage spaces to allocate based on the number of expected concurrent users.
        AUTO_LRU_TUNING configuration parameter
        Use the AUTO_LRU_TUNING configuration parameter to enable automatic LRU tuning, which automatically maintains enough clean pages for page replacement.
        AUTO_READAHEAD configuration parameter
        Use the AUTO_READAHEAD configuration parameter to change the automatic read-ahead mode or to disable automatic read-ahead operations for a query.
        AUTO_REPREPARE configuration parameter
        The AUTO_REPREPARE configuration parameter controls whether the database server automatically reoptimizes SPL routines and reprepares prepared objects after the schema of a table that is referenced by the SPL routine or by the prepared object was changed.
        AUTO_STAT_MODE configuration parameter
        Use the AUTO_STAT_MODE configuration parameter to enable or disable the mode for selectively updating only stale or missing data distributions in UPDATE STATISTICS operations instead of updating statistics for all data distributions.
        AUTO_TUNE configuration parameter
        Use the AUTO_TUNE configuration parameter to enable or disable all automatic tuning configuration parameters that have values that are not present in the onconfig file.
        AUTOLOCATE configuration parameter
        Use the AUTOLOCATE configuration parameter to enable the automatic location of databases, indexes, and tables, and the automatic fragmentation of tables.
        BATCHEDREAD_INDEX configuration parameter
        Use the BATCHEDREAD_INDEX configuration parameter to enable the optimizer to execute light scans for indexes. This reduces the number of times that a buffer is read, thus improving performance.
        BATCHEDREAD_TABLE configuration parameter
        Use the BATCHEDREAD_TABLE configuration parameter to enable or disable light scans on compressed tables, tables with rows that are larger than a page, and tables with VARCHAR, LVARCHAR, and NVARCHAR data.
        BLOCKTIMEOUT configuration parameter
        Use the BLOCKTIMEOUT configuration parameter to specify the number of seconds that a thread or database server will hang. After the timeout, the thread or database server will either continue processing or fail.
        BTSCANNER Configuration Parameter
        Use the BTSCANNER configuration parameter to set the B-tree scanner. The B-tree scanner improves transaction processing for logged databases when rows are deleted from a table with indexes. The B-tree scanner threads remove deleted index entries and rebalance the index nodes. The B-tree scanner automatically determines which index items are to be deleted.
        BUFFERPOOL configuration parameter
        Use the BUFFERPOOL configuration parameter to configure how many data pages are cached in shared memory and how often those pages are flushed to disk between checkpoints. The default values of the BUFFERPOOL configuration parameter are adequate for many systems. However, you can change the values to tune the performance of your system.
        CHECKALLDOMAINSFORUSER configuration parameter
        Use the CHECKALLDOMAINSFORUSER configuration parameter to check all of the domains for all users.
        CKPTINTVL configuration parameter
        Use the CKPTINTVL configuration parameter to specify the frequency, expressed in seconds, at which the database server checks to determine whether a checkpoint is needed. When a checkpoint occurs, all pages in the shared-memory buffer pool are written to disk.
        CLEANERS configuration parameter
        Use the CLEANERS configuration parameter to specify the number of page-cleaner threads available during the database server operation. By default, the database server always runs one page-cleaner thread. A general guideline is one page cleaner per disk drive. The value specified has no effect on the size of shared memory.
        CLUSTER_TXN_SCOPE configuration parameter
        Set the CLUSTER_TXN_SCOPE configuration parameter to configure your high-availability cluster so that when a client session issues a commit, the server blocks the session until the transaction is applied in that session, on a secondary server, or across the cluster.
        CONSOLE configuration parameter
        Use the CONSOLE configuration parameter to specify the path and name for console-message file.
        CONVERSION_GUARD configuration parameter
        Use the CONVERSION_GUARD configuration parameter to specify whether HCL Informix® stops or continues an upgrade to a new version of the server if an error occurs during the upgrade process.
        DATASKIP Configuration Parameter
        Use the DATASKIP configuration parameter to control whether the database server skips a dbspace that is unavailable during the processing of a transaction.
        DBCREATE_PERMISSION configuration parameter
        Use the DBCREATE_PERMISSION configuration parameter to restrict the permission to create databases to the user that you specify.
        DB_LIBRARY_PATH configuration parameter
        Use the DB_LIBRARY_PATH configuration parameter to specify a comma-separated list of valid directory prefix locations from which the database server can load external modules, such as DataBlade® modules. You can also include server environment variables, such as $INFORMIXDIR, in the list.
        DBSERVERALIASES configuration parameter
        Use the DBSERVERALIASES configuration parameter to specify an alias name, or a list of unique alias names for the database server. Each alias defined by the DBSERVERALIASES configuration parameter can be used in a different connection, as specified by entries in the sqlhosts information.
        DBSERVERNAME configuration parameter
        Use the DBSERVERNAME configuration parameter to specify a unique name that you want to associate with the database server. You specify this configuration parameter when you install the database server.
        DBSPACETEMP configuration parameter
        Use the DBSPACETEMP configuration parameter to specify a list of dbspaces that the database server uses to globally manage the storage of temporary tables.
        DD_HASHMAX configuration parameter
        Use the DD_HASHMAX configuration parameter to specify the maximum number of tables in each hash bucket in the data-dictionary cache.
        DD_HASHSIZE configuration parameter
        Use the DD_HASHSIZE configuration parameter to specify the number of hash buckets or lists that are in the data-dictionary cache.
        DEADLOCK_TIMEOUT configuration parameter
        Use the DEADLOCK_TIMEOUT configuration parameter to specify the maximum number of seconds that a database server thread can wait to acquire a lock.
        DEF_TABLE_LOCKMODE configuration parameter
        Use the DEF_TABLE_LOCKMODE configuration parameter to specify the lock mode at the page or row level for new tables.
        DEFAULTESCCHAR configuration parameter
        The DEFAULTESCCHAR configuration parameter specifies the default escape character that is used for LIKE and MATCHES conditions.
        DELAY_APPLY Configuration Parameter
        Use the DELAY_APPLY configuration parameter to configure RS secondary servers to wait for a specified period of time before applying logs.
        DIRECT_IO configuration parameter (UNIX™)
        Use the DIRECT_IO configuration parameter to control the use of direct I/O for cooked files used for dbspace chunks.
        DIRECTIVES configuration parameter
        Use the DIRECTIVES configuration parameter to enable or disable the use of optimizer directives. These directives specify behavior for the query optimizer in developing query plans for SELECT, UPDATE, and DELETE statements.
        DISABLE_B162428_XA_FIX configuration parameter
        Use the DISABLE_B162428_XA_FIX configuration parameter to specify when transactions are freed.
        DISK_ENCRYPTION configuration parameter
        DRDA_COMMBUFFSIZE configuration parameter
        Use the DRDA_COMMBUFFSIZE configuration parameter to specify the size of the DRDA® communications buffer.
        DRAUTO configuration parameter
        Set the DRAUTO configuration parameter to specify a HDR-failover method for HDR high-availability systems.
        DRIDXAUTO configuration parameter
        Use the DRIDXAUTO configuration parameter to specify whether the primary High-Availability Data Replication (HDR) server automatically starts index replication if the secondary HDR server detects a corrupted index.
        DRINTERVAL configuration parameter
        Use the DRINTERVAL configuration parameter to specify the maximum number of seconds between flushes of the data-replication buffer, whether to use HDR SYNC mode, or whether to use the synchronization mode that is specified by the HDR_TXN_SCOPE configuration parameter.
        DRLOSTFOUND configuration parameter
        Use the DRLOSTFOUND configuration parameter to specify the path name to the HDR lost-and-found file. This file indicates that some transactions were committed on the HDR primary database server before that were not committed on the secondary database server when the primary database server experienced a failure.
        DRTIMEOUT configuration parameter
        Use the DRTIMEOUT configuration parameter to specify the length of time, in seconds, that a database server in a high-availability data-replication pair waits for a transfer acknowledgment from the other database server in the pair. This parameter applies only to high-availability data-replication pairs.
        DS_HASHSIZE configuration parameter
        Use the DS_HASHSIZE configuration parameter to specify the number of hash buckets in the data-distribution cache and other caches. The database server stores and accesses column statistics that the UPDATE STATISTICS statement generates in the MEDIUM or HIGH mode in the data-distribution cache.
        DS_MAX_QUERIES configuration parameter
        Use the DS_MAX_QUERIES configuration parameter to specify the maximum number of parallel database queries (PDQ) that can run concurrently.
        DS_MAX_SCANS configuration parameter
        Use the DS_MAX_SCANS configuration parameter to limit the number of PDQ scan threads that the database server can execute concurrently.
        DS_NONPDQ_QUERY_MEM configuration parameter
        Use the DS_NONPDQ_QUERY_MEM configuration parameter to increase the amount of memory that is available for a query that is not a Parallel Database Query (PDQ). (You can only use this parameter if PDQ priority is set to zero.)
        DS_POOLSIZE configuration parameter
        Use the DS_POOLSIZE parameter to specify the maximum number of entries in the data-distribution cache and other caches. The database server stores and accesses column statistics that the UPDATE STATISTICS statement generates in the MEDIUM or HIGH mode in the data-distribution cache.
        DS_TOTAL_MEMORY configuration parameter
        Use the DS_TOTAL_MEMORY configuration parameter to specify the amount of memory available for PDQ queries. The amount should be smaller than the computer physical memory, minus fixed overhead such as operating-system size and buffer-pool size.
        DUMPCNT configuration parameter (UNIX™)
        Use the DUMPCNT configuration parameter to specify the number of assertion failures in a thread for which a database server dumps shared memory or generates a core file by calling the gcore utility.
        DUMPCORE configuration parameter (UNIX™)
        Use the DUMPCORE configuration parameter to control whether assertion failures cause a virtual processor to dump a core image. The core file is left in the directory from which the database server was last invoked. (The DUMPDIR parameter has no impact on the location of the core file.)
        DUMPDIR configuration parameter
        DUMPDIR specifies a directory in which the database server dumps shared memory, gcore files, or messages from a failed assertion.
        DUMPGCORE configuration parameter (UNIX™)
        Use the DUMPGCORE configuration parameter to specify whether to dump the gcore core file. Use this configuration parameter with operating systems that support gcore.
        DUMPSHMEM configuration parameter (UNIX™)
        Use the DUMPSHMEM configuration parameter to indicate whether a shared memory dump is created on an assertion failure. This configuration parameter also specifies how much memory is written to the shmem.pid.cnt file in the directory specified by the DUMPDIR configuration parameter.
        DYNAMIC_LOGS configuration parameter
        Use the DYNAMIC_LOGS configuration parameter to allow logical logs to be dynamically added when necessary to prevent transaction blocking.
        EILSEQ_COMPAT_MODE configuration parameter
        Use the EILSEQ_COMPAT_MODE configuration parameter to control if HCL Informix® checks whether character data inserted by a client application contains code point sequences not recognized by the locale of the current database.
        ENABLE_SNAPSHOT_COPY configuration parameter
        Use the ENABLE_SNAPSHOT_COPY configuration parameter to enable or disable the ability to clone a server using the ifxclone utility.
        ENCRYPT_CIPHERS configuration parameter
        Use the ENCRYPT_CIPHERS configuration parameter to define all ciphers and modes that can be used by the current database session. ENCRYPT_CIPHERS is used for Enterprise Replication and High-Availability Data Replication only.
        ENCRYPT_HDR configuration parameter
        Use the ENCRYPT_HDR configuration parameter to enable or disable HDR encryption.
        ENCRYPT_MAC configuration parameter
        Use the ENCRYPT_MAC configuration parameter to control the level of message authentication code (MAC) generation. This configuration parameter is used only for Enterprise Replication and High-Availability Data Replication.
        ENCRYPT_MACFILE configuration parameter
        Use the ENCRYPT_MACFILE configuration parameter to specify a list of the full path names of MAC key files. This configuration parameter is used only for Enterprise Replication and High-Availability Data Replication.
        ENCRYPT_SMX configuration parameter
        Use the ENCRYPT_SMX configuration parameter to set the level of encryption for high-availability configurations on secondary servers and between Enterprise Replication Servers.
        ENCRYPT_SWITCH configuration parameter
        Use the ENCRYPT_SWITCH configuration parameter to define the frequency at which ciphers or secret keys are renegotiated. This configuration parameter is used only for Enterprise Replication and High-Availability Data Replication.
        EXPLAIN_STAT configuration parameter
        Use the EXPLAIN_STAT configuration parameter to enable or disable the inclusion of a Query Statistics section in the explain output file.
        EXT_DIRECTIVES configuration parameter
        Use the EXT_DIRECTIVES configuration parameter to enable or disable the use of external query optimizer directives.
        EXTSHMADD configuration parameter
        Use the EXTSHMADD configuration parameter to specify the size of virtual-extension segments that are added when a user-defined routine or a DataBlade® routine is run in a user-defined virtual processor.
        FAILOVER_CALLBACK configuration parameter
        Use the FAILOVER_CALLBACK configuration parameter to specify the script executed by the database server when a database server transitions from a secondary server to a primary or standard server.
        FAILOVER_TX_TIMEOUT configuration parameter
        In high-availability cluster environments, use the FAILOVER_TX_TIMEOUT configuration parameter to enable transactions to complete after failover of the primary server.
        FASTPOLL configuration parameter
        Use the FASTPOLL configuration parameter to enable or disable fast polling of your network. FASTPOLL is a platform-specific configuration parameter.
        FILLFACTOR configuration parameter
        Use the FILLFACTOR configuration parameter to specify the degree of index-page fullness. A low value provides room for growth in the index. A high value compacts the index.
        FULL_DISK_INIT configuration parameter
        Use the FULL_DISK_INIT configuration parameter to prevent an accidental disk reinitialization of an existing database server instance. This configuration parameter specifies whether or not the disk initialization command (oninit -i) can run on your HCL Informix® instance when a page zero exists at the root path location, which is at the first page of the first chunk location.
        HA_ALIAS configuration parameter
        The HA_ALIAS configuration parameter defines a network alias that is used for server-to-server communication in a high-availability cluster. The specified network alias is also used by Connection Managers, the ifxclone utility, and onmode -d commands.
        HA_FOC_ORDER configuration parameter
        Use the HA_FOC_ORDER configuration parameter to define a single connection-management failover rule for a high-availability cluster of servers.
        HDR_TXN_SCOPE configuration parameter
        The HDR_TXN_SCOPE configuration parameter is used with the DRINTERVAL configuration parameter to specify the synchronization mode for HDR replication in a high-availability cluster.
        IFX_EXTEND_ROLE configuration parameter
        Your database system administrator (DBSA), by default user informix, can use the IFX_EXTEND_ROLE parameter to control which users are authorized to register DataBlade® modules or external user-defined routines (UDRs).
        IFX_FOLDVIEW configuration parameter
        Use the IFX_FOLDVIEW configuration parameter to enable or disable view folding. For certain situations where a view is involved in a query, view folding can significantly improve the performance of the query. In these cases, views are folded into a parent query instead of the query results being put into a temporary table.
        IFX_XA_UNIQUEXID_IN_DATABASE configuration parameter
        Use the IFX_XA_UNIQUEXID_IN_DATABASE configuration parameter to enable the transaction manager to use the same XID to represent global transactions on different databases in the same database server instance.
        INFORMIXCONRETRY configuration parameter
        Use the INFORMIXCONRETRY configuration parameter to specify the maximum number of connection attempts that can be made to each database server after the initial connection attempt fails. These attempts are made within the time limit that the INFORMIXCONTIME configuration parameter specifies.
        INFORMIXCONTIME configuration parameter
        Use the INFORMIXCONTIME configuration parameter to specify the number of seconds that the CONNECT statement attempts to establish a connection to a database server.
        LIMITNUMSESSIONS configuration parameter
        Use the LIMITNUMSESSIONS configuration parameter to define the maximum number of sessions that you want connected to HCL Informix®.
        LISTEN_TIMEOUT configuration parameter
        Use the LISTEN_TIMEOUT configuration parameter to specify the number of seconds in which the server waits for a connection.
        LOCKS configuration parameter
        The LOCKS configuration parameter specifies the initial size of the lock table.
        LOGBUFF configuration parameter
        Use the LOGBUFF configuration parameter to specify the size in kilobytes for the three logical-log buffers in shared memory.
        LOGBUF_INTVL configuration parameter
        Use the LOGBUF_INTVL configuration parameter to ensure the logical log buffer is flushed periodically when only buffered logging is used.
        LOGFILES configuration parameter
        Use the LOGFILES configuration parameter to specify the number of logical-log files that the database server creates during disk initialization.
        LOG_INDEX_BUILDS configuration parameter
        Use the LOG_INDEX_BUILDS configuration parameter to enable or disable index page logging.
        LOG_STAGING_DIR configuration parameter
        Use the LOG_STAGING_DIR configuration parameter to specify the location of log files received from the primary server when configuring delayed application of log files on RS secondary servers.
        LOGSIZE configuration parameter
        Use the LOGSIZE configuration parameter to specify the size that is used when logical-log files are created.
        LOW_MEMORY_MGR configuration parameter
        Use the LOW_MEMORY_MGR configuration parameter to enable automatic low memory management, which you can use to change the default behavior of a primary or standard server when it reaches its memory limit.
        LOW_MEMORY_RESERVE configuration parameter
        Use the LOW_MEMORY_RESERVE configuration parameter to reserve a specific amount of memory for use when critical activities are needed and the server has limited free memory.
        LTXEHWM configuration parameter
        Use the LTXEHWM configuration parameter to specify the long-transaction, exclusive-access, high-watermark. When the logical-log space reaches the LTXEHWM threshold, the long transaction currently being rolled back is given exclusive access to the logical log.
        LTXHWM configuration parameter
        Use the LTXHWM configuration parameter to specify the long-transaction high-watermark. The long-transaction high-watermark is the percentage of available log space that, when filled, triggers the database server to check for a long transaction.
        MAX_FILL_DATA_PAGES configuration parameter
        Use the MAX_FILL_DATA_PAGES configuration parameter to control inserting more rows to pages that have variable-length rows.
        MAX_INCOMPLETE_CONNECTIONS configuration parameter
        Use the MAX_INCOMPLETE_CONNECTIONS configuration parameter to specify the maximum number of incomplete connections in a session.
        MAX_PDQPRIORITY configuration parameter
        Use the MAX_PDQPRIORITY configuration parameter to limit the PDQ resources that the database server can allocate to any one DSS query.
        MIRROROFFSET configuration parameter
        In HCL Informix®, MIRROROFFSET specifies the offset into the disk partition or into the device to reach the chunk that serves as the mirror for the initial chunk of the root dbspace.
        MIRRORPATH configuration parameter
        Set the MIRRORPATH configuration parameter to a full path name prior to performing a complete disk initialization of your instance. Do not set MIRRORPATH and simply restart the server, as errors may result. This path will be the mirror chunk for the initial chunk of the root dbspace.
        MSG_DATE configuration parameter
        Use the MSG_DATE configuration parameter to enable the insertion of a date in MM/DD/YY format at the beginning of each message printed to the online log.
        MSGPATH configuration parameter
        Use the MSGPATH configuration parameter to specify the full path name of the message-log file. The database server writes status messages and diagnostic messages to this file during operation.
        MULTIPROCESSOR configuration parameter
        Use the MULTIPROCESSOR configuration parameter to specify whether the database server performs locking in a manner that is suitable for a single-processor computer or a multiprocessor computer.
        NET_IO_TIMEOUT_ALARM configuration parameter
        Use the NET_IO_TIMEOUT_ALARM configuration parameter to control whether to be notified if network write operations have been blocked for 30 minutes or more.
        NETTYPE configuration parameter
        Use the NETTYPE parameter to tune the network protocols that are defined in the sqlhosts information.
        NS_CACHE configuration parameter
        Use the NS_CACHE configuration parameter to define the maximum retention time for entries in the Informix® name service caches: the host name/IP address cache, the service cache, the user cache, and the group cache.
        NUMFDSERVERS configuration parameter
        For network connections on UNIX™, use the NUMFDSERVERS configuration parameter to specify the maximum number of poll threads to handle network connections migrating between HCL Informix® virtual processors (VPs).
        OFF_RECVRY_THREADS configuration parameter
        Use the OFF_RECVRY_THREADS configuration parameter to specify the number of recovery threads that are used for logical recovery during a cold restore or fast recovery.
        ON_RECVRY_THREADS configuration parameter
        The ON_RECVRY_THREADS configuration parameter is the maximum number of recovery threads that the database server uses for logical recovery when the database server is online (during a warm restore).
        ONDBSPACEDOWN configuration parameter
        Use the ONDBSPACEDOWN configuration parameter to define the action that the database server takes when any disabling event occurs on a primary chunk within a noncritical dbspace.
        ONLIDX_MAXMEM configuration parameter
        Use the ONLIDX_MAXMEM configuration parameter to limit the amount of memory that is allocated to a single preimage pool and a single updator log pool.
        OPTCOMPIND configuration parameter
        Use the OPTCOMPIND to specify information that helps the optimizer choose an appropriate query plan for your application.
        OPT_GOAL configuration parameter
        Use the OPT_GOAL configuration parameter to specify an optimization goal for queries.
        PC_HASHSIZE configuration parameter
        Use PC_HASHSIZE to specify the number of hash buckets in the caches that the database server uses. PC_HASHSIZE applies to UDR cache only.
        PC_POOLSIZE configuration parameter
        Use the PC_POOLSIZE configuration parameter to specify the maximum number of user-defined routines that are stored in the UDR cache.
        PFSC_BOOST configuration parameter
        Use the PFSC_BOOST configuration parameter to enable or disable the boosted partition free space cache feature.
        PHYSBUFF configuration parameter
        Use the PHYSBUFF configuration parameter to specify the size in kilobytes of the two physical-log buffers in shared memory.
        PHYSFILE configuration parameter
        Use the PHYSFILE configuration parameter to specify the size of the physical log file when you first initialize the disk space and bring the database server online.
        PLOG_OVERFLOW_PATH configuration parameter
        The PLOG_OVERFLOW_PATH parameter specifies the location of the file that is used during fast recovery if the physical log file overflows.
        PLCY_HASHSIZE configuration parameter
        The PLCY_HASHSIZE configuration parameter specifies the number of hash buckets in the security policy information cache.
        PLCY_POOLSIZE configuration parameter
        Use the PLCY_POOLSIZE configuration parameter to specify the maximum number of entries in each hash bucket of the security policy information cache.
        PN_STAGEBLOB_THRESHOLD configuration parameter
        Use the PN_STAGEBLOB_THRESHOLD configuration parameter to reserve space for BYTE and TEXT data in round-robin fragments.
        PRELOAD_DLL_FILE configuration parameter
        The PRELOAD_DLL_FILE configuration parameter specifies the path name for a shared library file that is preloaded when the database server is started.
        QSTATS configuration parameter
        The QSTATS configuration parameter specifies the ability of onstat -g qst to print queue statistics.
        REMOTE_SERVER_CFG configuration parameter
        Use the REMOTE_SERVER_CFG configuration parameter to specify the file that lists trusted remote hosts.
        REMOTE_USERS_CFG configuration parameter
        Use the REMOTE_USERS_CFG configuration parameter to specify the file that lists the names of trusted users that exist on remote hosts.
        RESIDENT configuration parameter
        Use the RESIDENT configuration parameter to specify whether resident and virtual segments of shared memory remain resident in operating-system physical memory.
        RESTARTABLE_RESTORE configuration parameter
        Use the RESTARTABLE_RESTORE configuration parameter to control whether the database server performs restartable restores.
        RESTORE_POINT_DIR configuration parameter
        Use the RESTORE_POINT_DIR configuration parameter to change the path name of the directory where restore point files will be placed during a failed upgrade to a new version of the server. HCL Informix® will store restore point files in a subdirectory of the specified directory, with the server number as the subdirectory name, only if the CONVERSION_GUARD configuration parameter is enabled.
        ROOTNAME configuration parameter
        ROOTNAME specifies a name for the root dbspace for this database server configuration.
        ROOTOFFSET configuration parameter
        ROOTOFFSET specifies the offset into an allocation of disk space (file, disk partition, or device) at which the initial chunk of the root dbspace begins.
        ROOTPATH configuration parameter
        Use the ROOTPATH configuration parameter to specify the full path name, including the device or file name, of the initial chunk of the root dbspace. The ROOTPATH configuration parameter is stored in the reserved pages as a chunk name.
        ROOTSIZE configuration parameter
        Use the ROOTSIZE configuration parameter to specify the size in kilobytes of the initial chunk of the root dbspace. The size that you select depends on your immediate plans for your database server.
        RSS_FLOW_CONTROL configuration parameter
        Specifies when flow control occurs in a high-availability cluster that contains at least one remote standalone (RS) secondary server.
        RSS_NONBLOCKING_CKPT configuration parameter
        Use the RSS_NONBLOCKING_CKPT configuration parameter to enable non-blocking checkpoint at RS secondary server.
        RTO_SERVER_RESTART configuration parameter
        Use the RTO_SERVER_RESTART configuration parameter to specify recovery time objective (RTO) standards for the amount of time, in seconds, that HCL Informix® has to recover from a problem after you restart the server and bring it into online or quiescent mode.
        S6_USE_REMOTE_SERVER_CFG configuration parameter
        Use the S6_USE_REMOTE_SERVER_CFG configuration parameter to control whether the file specified by the REMOTE_SERVER_CFG configuration parameter is used to authenticate secure connections for server clusters and Enterprise Replication.
        SB_CHECK_FOR_TEMP configuration parameter
        Use the SB_CHECK_FOR_TEMP configuration parameter to prevent the copying of a temporary smart large object into a permanent table.
        SBSPACENAME configuration parameter
        Use the SBSPACENAME configuration parameter specifies the name of the default sbspace.
        SBSPACETEMP configuration parameter
        Use the SBSPACETEMP configuration parameter to specify a list of default temporary sbspace for storing temporary smart large objects without metadata or user-data logging. If you store temporary smart large objects in a standard sbspace, the metadata is logged.
        SDS_ALTERNATE configuration parameter
        Use the SDS_ALTERNATE configuration parameter to define an alternate means of communication between the primary server and SD secondary servers in a high-availability cluster.
        SDS_ENABLE configuration parameter
        Use the SDS_ENABLE configuration parameter to enable SD secondary server functionality.
        SDS_FLOW_CONTROL configuration parameter
        Specifies when flow control occurs in a high-availability cluster that contains at least one shared-disk (SD) secondary server.
        SDS_LOGCHECK configuration parameter
        Use the SDS_LOGCHECK configuration parameter to set the number of seconds to delay the secondary server from taking over the role of the primary server. If the secondary server detects that the primary server is generating log records during the delay period, then the failover is prevented. The delay can prevent an unnecessary failover if network communication between the primary and secondary servers is temporarily unavailable.
        SDS_PAGING configuration parameter
        The SDS_PAGING configuration parameter specifies the location of two files that serve as buffer paging files.
        SDS_TEMPDBS configuration parameter
        Use the SDS_TEMPDBS configuration parameter to specify information that the shared disk (SD) secondary server uses to dynamically create temporary dbspaces. This configuration parameter can be specified only on the SD secondary server.
        SDS_TIMEOUT configuration parameter
        Use the SDS_TIMEOUT configuration parameter to specify the amount of time in seconds that the primary server in a high-availability cluster will wait for a log-position acknowledgment to be sent from a shared disk (SD) secondary server.
        SEC_APPLY_POLLTIME configuration parameter
        Use the SEC_APPLY_POLLTIME configuration parameter to control how long log replay thread should poll for new work before yielding.
        SEC_DR_BUFS configuration parameter
        Use the SEC_DR_BUFS configuration parameter to control the number of replication buffers to be used for replicating log records to secondary server. Buffer size is same as LOGBUFF config value.
        SEC_LOGREC_MAXBUFS configuration parameter
        Use the SEC_LOGREC_MAXBUFS configuration parameter to control the number of log buffers to be used for replaying log records at secondary server. Each log buffer is of size 16KB.
        SEC_NONBLOCKING_CKPT configuration parameter
        Use the SEC_NONBLOCKING_CKPT configuration parameter to enable non-blocking checkpoint at HDR and RS secondary server.
        SECURITY_LOCALCONNECTION configuration parameter
        Use the SECURITY_LOCALCONNECTION configuration parameter to verify security on local connections by verifying that the ID of the local user who is running a program is the same ID of the user who is trying to access the database.
        SEQ_CACHE_SIZE configuration parameter
        Use the SEQ_CACHE_SIZE configuration parameter to specify the maximum number of sequence objects that are cached in memory.
        SERVERNUM configuration parameter
        The SERVERNUM configuration parameter specifies a relative location in shared memory.
        SESSION_LIMIT_LOCKS configuration parameter
        The SESSION_LIMIT_LOCKS configuration parameter specifies the maximum number of locks available in a session. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
        SESSION_LIMIT_LOGSPACE configuration parameter
        The SESSION_LIMIT_LOGSPACE configuration parameter specifies the maximum amount of log space that a session can use for individual transactions. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
        SESSION_LIMIT_MEMORY configuration parameter
        The SESSION_LIMIT_MEMORY configuration parameter specifies the maximum amount of memory that a session can allocate. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
        SESSION_LIMIT_TEMPSPACE configuration parameter
        The SESSION_LIMIT_TEMPSPACE configuration parameter specifies the maximum amount of temporary table space that a session can allocate. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
        SESSION_LIMIT_TXN_TIME configuration parameter
        The SESSION_LIMIT_TXN_TIME configuration parameter specifies the maximum amount of time that a transaction can run in a session. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
        SHMADD configuration parameter
        Use the SHMADD configuration parameter to specify the size of the segments that are dynamically added to the virtual portion of shared memory.
        SHMBASE configuration parameter
        Use the SHMBASE configuration parameter to specifiy the base address where shared memory is attached to the memory space of a virtual processor.
        SHMNOACCESS configuration parameter
        The SHMNOACCESS configuration parameter specifies a virtual memory address range to not use to attach shared memory.
        SHMTOTAL configuration parameter
        Use the SHMTOTAL configuration parameter to specify the total amount of shared memory (resident, virtual, communications, and virtual extension portions) to be used by the database server for all memory allocations. The onconfig.std value of 0 implies that no limit on memory allocation is stipulated.
        SHMVIRT_ALLOCSEG configuration parameter
        Use the SHMVIRT_ALLOCSEG configuration parameter to specify a threshold at which Informix® should allocate a new shared memory segment and the level of the event alarm activated if the server cannot allocate the new memory segment.
        SHMVIRTSIZE configuration parameter
        Use the SHMVIRTSIZE configuration parameter to specify the initial size of a virtual shared-memory segment.
        SINGLE_CPU_VP configuration parameter
        The SINGLE_CPU_VP configuration parameter specifies whether or not the database server is running with only one CPU virtual processor.
        SMX_COMPRESS configuration parameter
        Use the SMX_COMPRESS configuration parameter to specify the level of compression that the database server uses before sending data from the source database server to the target database server.
        SMX_NUMPIPES configuration parameter
        The SMX_NUMPIPES configuration parameter sets the number of pipes for server multiplexer group (SMX) connections.
        SMX_PING_INTERVAL configuration parameter
        Use the SMX_PING_INTERVAL configuration parameter to specify the number of seconds in a timeout interval, where a secondary server waits for activity from the primary server in a Server Multiplexer Group (SMX) connection.
        SMX_PING_RETRY configuration parameter
        Use the SMX_PING_RETRY configuration parameter to specify the maximum number of times that a secondary server repeats the timeout interval that is specified by the SMX_PING_INTERVAL configuration parameter if a response from the primary server is not received. If the maximum number is reached without a response, the secondary server prints an error message in the online.log and closes the Server Multiplexer Group (SMX) connection.
        SP_AUTOEXPAND configuration parameter
        Use the SP_AUTOEXPAND configuration parameter to enable or disable the automatic creation or extension of chunks.
        SP_THRESHOLD configuration parameter
        Use the SP_THRESHOLD configuration parameter to define the minimum amount of free kilobytes that can exist in a storage space before HCL Informix® automatically runs a task to expand the space, either by extending an existing chunk in the space or by adding a new chunk.
        SP_WAITTIME configuration parameter
        Use the SP_WAITTIME configuration parameter to specify the maximum number of seconds that a thread waits for a dbspace, temporary dbspace, plogspace, sbspace, temporary sbspace, or blobspace space to expand before returning an out-of-space error.
        SQL_LOGICAL_CHAR configuration parameter
        Use the SQL_LOGICAL_CHAR configuration parameter to enable or disable the expansion of size specifications in declarations of built-in character data types.
        SQLTRACE configuration parameter
        Use the SQLTRACE parameter to control the startup environment of SQL tracing.
        STACKSIZE configuration parameter
        Use the STACKSIZE configuration parameter to specify the stack size for the database server user threads.
        STATCHANGE configuration parameter
        Use the STATCHANGE configuration parameter to specify a positive integer for a global percentage of a change threshold for the server to use to determine if distribution statistics qualify for an update when the automatic mode for UPDATE STATISTICS operations is enabled.
        STMT_CACHE configuration parameter
        Use the STMT_CACHE configuration parameter to determine whether the database server uses the SQL statement cache.
        STMT_CACHE_HITS configuration parameter
        Use the STMT_CACHE_HITS configuration parameter to specify the number of hits (references) to a statement before it is fully inserted in the SQL statement cache.
        STMT_CACHE_NOLIMIT configuration parameter
        Use the STMT_CACHE_NOLIMIT configuration parameter to control whether to insert qualified statements into the SQL statement cache.
        STMT_CACHE_NUMPOOL configuration parameter
        Use the STMT_CACHE_NUMPOOL configuration parameter to specify the number of memory pools for the SQL statement cache. To obtain information about these memory pools, use onstat -g ssc pool.
        STMT_CACHE_QUERY_PLAN configuration parameter
        Use the STMT_CACHE_QUERY_PLAN configuration parameter to produce a query plan from any query that exists in the Statement Cache.
        STMT_CACHE_SIZE configuration parameter
        Use the STMT_CACHE_SIZE configuration parameter to specify the size of the SQL statement caches in kilobytes. The new cache size takes effect the next time a statement is added to a cache.
        STOP_APPLY configuration parameter
        Use the STOP_APPLY configuration parameter to stop an RS secondary server from applying log files received from the primary server.
        STORAGE_FULL_ALARM configuration parameter
        Use the STORAGE_FULL_ALARM configuration parameter to configure the frequency and severity of messages and alarms when storage spaces become full.
        SYSALARMPROGRAM configuration parameter
        Use the SYSALARMPROGRAM configuration parameter to specify the full path name of the evidence.sh script. The database server executes evidence.sh when a database server failure occurs. You can use the output from the evidence.sh script to diagnose the cause of a database server failure.
        SYSSBSPACENAME configuration parameter
        Use the SYSSBSPACENAME configuration parameter to specify the name of the sbspace in which the database server stores fragment-level data-distribution statistics, which the syfragsdist system catalog table stores as BLOB objects in its encsdist column. Also use SYSSBSPACENAME to specify the name of the sbspace in which the database server stores statistics that the UPDATE STATISTICS statement collects for certain user-defined data types.
        TBLSPACE_STATS configuration parameter
        Use the TBLSPACE_STATS configuration parameter to turn on and off the collection of tblspace statistics. Use the onstat -g ppf command to list tblspace statistics.
        TBLTBLFIRST configuration parameter
        Use the TBLTBLFIRST configuration parameter if you want to specify the first extent size of tblspace tblspace in the root dbspace. Set this parameter if you do not want the database server to automatically manage the extent size.
        TBLTBLNEXT configuration parameter
        The TBLTBLNEXT configuration parameter specifies the next extent size of tblspace tblspace in the root dbspace. Set this parameter if you do not want the database server to automatically manage the extent size.
        TEMPTAB_NOLOG configuration parameter
        Use the TEMPTAB_NOLOG configuration parameter to disable logging on temporary tables.
        TENANT_LIMIT_CONNECTIONS configuration parameter
        The TENANT_LIMIT_CONNECTIONS configuration parameter specifies the maximum number of connections to a tenant database.
        TENANT_LIMIT_MEMORY configuration parameter
        The TENANT_LIMIT_MEMORY configuration parameter specifies the maximum amount of shared memory for all sessions that are connected to the tenant database.
        TENANT_LIMIT_SPACE configuration parameter
        The TENANT_LIMIT_SPACE configuration parameter specifies the maximum amount of storage space available to a tenant database. Storage space includes all permanent dbspaces, BLOB spaces, and sbspaces.
        TXTIMEOUT configuration parameter
        Use the TXTIMEOUT configuration parameter to specify the amount of time that a participant in a two-phase commit waits before it initiates participant recovery. This parameter is used only for distributed queries that involve a remote database server. Nondistributed queries do not use this parameter.
        UNSECURE_ONSTAT configuration parameter
        Use the UNSECURE_ONSTAT configuration parameter to remove the database system administrator (DBSA) user access restriction for onstat commands.
        UPDATABLE_SECONDARY configuration parameter
        Use the UPDATABLE_SECONDARY configuration parameter to set the number of connections to establish between the primary and secondary servers. Setting this configuration parameter enables client applications to perform update, insert, and delete operations on a high-availability secondary server.
        USELASTCOMMITTED configuration parameter
        Use the USELASTCOMMITTED configuration parameter to specify the isolation level for which the LAST COMMITTED feature of the COMMITTED READ isolation level is implicitly in effect.
        USEOSTIME configuration parameter
        Use the USEOTIME configuration parameter to control whether the database server uses subsecond precision when obtaining the current time from the operating system.
        USERMAPPING configuration parameter (UNIX™, Linux™)
        Use the USERMAPPING configuration parameter to set whether or not the database server accepts connections from mapped users.
        USRC_HASHSIZE configuration parameter
        The USRC_HASHSIZE configuration parameter specifies the number of hash buckets in the LBAC credential memory cache. This memory cache holds information about the LBAC credentials of users.
        USRC_POOLSIZE configuration parameter
        The USRC_POOLSIZE configuration parameter specifies the maximum number of entries in each hash bucket of the LBAC credential memory cache. This memory cache holds information about the LBAC credentials of users.
        USTLOW_SAMPLE configuration parameter
        Use the USTLOW_SAMPLE configuration parameter to enable the generation of index statistics based on sampling when you run UPDATE STATISTICS statements in LOW mode.
        VP_MEMORY_CACHE_KB configuration parameter
        Use the VP_MEMORY_CACHE_KB parameter to create a private memory cache for each CPU virtual processor and tenant virtual processor.
        VPCLASS configuration parameter
        Use the VPCLASS configuration parameter to create and configure virtual processors.
        VP_KAIO_PERCENT configuration parameter
        VP_KAIO_PERCENT is the percentage of total KAIO event resources on the system that each CPU VP will allocate.
        WSTATS configuration parameter
        Use the WSTATS configuration parameter to specify whether the onstat -g wst command displays wait statistics for threads within the system.
      - The sysmaster database
        These topics describe the sysmaster database and provide reference information for the system-monitoring interface (SMI).
      - The sysadmin Database
        The sysadmin database contains the tables that contain and organize the Scheduler tasks and sensors, store data collected by sensors, and record the results of Scheduler jobs and SQL administration API functions.
      - Disk Structures and Storage
      - Interpreting Logical-Log Records
    - Administrative Utilities
    - SQL Administration API
    - Appendixes
  - DB-Access User's Guide
    The Informix® DB-Access User's Guide describes how to use the DB-Access utility to access, modify, and retrieve information from Informix database servers.
  - High-Performance Loader User's Guide
    The describes how to use the HCL Informix® to load and unload large quantities of data efficiently to or from Informix databases.
  - Performance Guide
    The Informix® Performance Guide describes how to configure and operate your HCL Informix database server to improve overall system throughput and to improve the performance of SQL queries.
  - SNMP Subagent Guide
    The describes the Simple Network Management Protocol (SNMP) and the software that you need to use SNMP to monitor and manage HCL Informix® database servers and databases.
  - InformixHQ Guide
    InformixHQ is a modern web console for visualizing, monitoring, and managing your Informix server instances. It is purpose built for ease-of-use, scaling out, and optimizing DevOps needs. It provides critical performance management capabilities, monitoring how key performance metrics are changing over time and tracking how efficiently Informix is running your workload even when you’ve stepped away from your screen. Its monitoring system feeds directly into a customizable alerting system so you can be immediately alerted via email, Twilio, or PagerDuty whenever an issue occurs on one of your Informix database server instances. InformixHQ is designed to be scalable to efficiently manage and monitor as many Informix database server instances as you need. Moreover, it's a tool that can be shared by the DBAs, the app developers, the ops engineers, and management and accessed from any desktop, laptop, or mobile device. InformixHQ is the centralized hub for graphical monitoring, alerting, and administration of your Informix database servers.
- Backup and Restore Guide
  The Informix® Backup and Restore Guide describes how to use the Informix ON-Bar and ontape utilities to back up and restore database server data. These utilities enable you to recover your databases after data is lost or becomes corrupted due to hardware or software failure or accident.
- Enterprise Replication
  The HCL® Informix® Enterprise Replication Guide describes the concepts of data replication using Informix Enterprise Replication, including how to design your replication system, as well as administer and manage data replication throughout your enterprise.

STMT_CACHE_QUERY_PLAN configuration parameter

Use the STMT_CACHE_QUERY_PLAN configuration parameter to produce a query plan from any query that exists in the Statement Cache.

onconfig.std value: STMT_CACHE_QUERY_PLAN
values: 0 = Disabled.
1 = Enabled
takes effect: After you edit your onconfig file and restart the database server.; When you reset the value dynamically in your onconfig file by running the onmode -wf command.; When you reset the value in memory by running the onmode -wm command.

Usage

You can use STMT_CACHE_QUERY_PLAN to enable SQL statement cache to hold query plan for each cached statement.