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.
Administrative Utilities
The onstat utility
The onstat utility reads shared-memory structures and provides statistics about the database server at the time that the command runs.
onstat -g monitoring options
The options that you can use with onstat -g command are used for support and debugging only. You can include only one of these options in the onstat -g command.
onstat -g defragment command: Print defragment partition extents
Use the onstat -g defragment command to display information about the active requests to defragment partition extents.

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®
    - Administrative Utilities
      - Overview of Utilities
        The Informix® database server utilities allow you to perform administrative tasks directly from the command line.
      - The finderr utility
        Use the finderr utility to view additional information on Informix® error messages. On UNIX™ and Linux™ platforms, the information appears on the command line. On Windows™ platforms, the information appears in the Error Messages program.
      - The genoncfg Utility
        Use the genoncfg utility to expedite the process of customizing the default Informix® configuration file (onconfig.std) to the host environment and your planned usage of a database server instance.
      - The oncheck Utility
        Use the oncheck utility to check specified disk structures for inconsistencies, repair inconsistent index structures, and display information about disk structures.
      - The onclean utility
        Use the onclean utility to force a shut down of the database server when normal shut down with the onmode utility fails or when you cannot restart the server. The onclean utility attempts to clean up shared memory, semaphores, and stops database server virtual processes.
      - The oncmsm utility
        Use the oncmsm utility to start or shut down a Connection Manager, load a new configuration file into a Connection Manager to modify the Connection Manager's settings, or update the format of a configuration file.
      - The onconfig_diff utility
        Use the onconfig_diff utility to compare two onconfig files.
      - The ondblog utility
        Use the ondblog utility to change the logging mode for one or more databases.
      - The oninit utility
        The oninit utility starts the database server.
      - The onkstash Utility
        Use the onkstash utility to create a password stash file for an existing PKCS#12 keystore.
      - The onkstore Utility
      - The onlog utility
        The onlog utility displays the contents of a logical-log file, either on disk or on backup.
      - The onmode utility
        Use the onmode utility to change the database server operating mode and perform various other operations on shared memory, sessions, transactions, parameters, and segments.
      - The onparams Utility
        Use the onparams utility to add or drop a logical-log file, change physical-log parameters, and add a new buffer pool.
      - The onpassword utility
        Use the onpassword utility to encrypt and decrypt a password file. Connection Manager and Enterprise Replication utilities require a password file to connect to database servers over an untrusted network.
      - The ifxclone utility
        You use the ifxclone utility to create a server clone from a snapshot of an existing database server.
      - The onspaces utility
        Use the onspaces utility to manage the storage spaces in your database.
      - The onstat utility
        The onstat utility reads shared-memory structures and provides statistics about the database server at the time that the command runs.
        onstat Utility Commands Sorted by Functional Category
        The information in this topic lists onstat commands that are sorted by functional category.
        Monitor the database server status
        To monitor the database server status, view the heading of the onstat command.
        onstat command syntax
        The complete syntax for the onstat command, including information about the interactive mode and how to have options to execute repeatedly.
        onstat command: Equivalent to the onstat -pu command
        If you invoke onstat without any options, the command is interpreted as onstat -pu (the -p option and the -u option).
        onstat - command: Print output header
        All onstat output includes a header. The onstat - command displays only the output header and the value that is returned from this command indicates the database server mode.
        onstat -- command: Print onstat options and functions
        Use the onstat -- command to display a listing of all of the onstat options and their functions. You cannot combine this option with any other flag.
        Running onstat Commands on a Shared Memory Dump File
        You can run onstat commands against a shared memory dump file. The shared memory dump file can be produced explicitly by using the onstat -o command. If the DUMPSHMEM configuration parameter is set to 1 or set to 2, the dump file is created automatically at the time of an assertion failure.
        onstat -a command: Print overall status of the database server
        Use the onstat -a command to display information about the status of the database server. This command does not display information about all of the onstat options, only about those onstat options used for initial troubleshooting.
        onstat -b command: Print buffer information for buffers in use
        Use the onstat -b option to display information about the buffers that are currently in use, including the total number of resident pages in the buffer pool.
        onstat -B command: Prints information about used buffers
        Use the onstat -B option to display information about buffers that are not on the free-list.
        onstat -c command: Print ONCONFIG file contents
        Use the onstat -c command to display the contents of the ONCONFIG file.
        onstat -C command: Print B-tree scanner information
        Use the -C command to display information about the B-tree scanner subsystem and each B-tree scanner thread.
        onstat -d command: Print chunk information
        Use the onstat -d command to show information about chunks in each storage space.
        onstat -D command: Print page-read and page-write information
        Use the onstat -D command to display page-read and page-write information for the first 50 chunks in each space.
        onstat -f command: Print dbspace information affected by dataskip
        Use the -f command to list the dbspaces that the dataskip feature currently affects.
        onstat -F command: Print counts
        Use the onstat -F command to display a count for each type of write that flushes pages to disk.
        onstat -g monitoring options
        The options that you can use with onstat -g command are used for support and debugging only. You can include only one of these options in the onstat -g command.
        onstat -g act command: Print active threads
        Use the onstat -g act command to display information about the active threads.
        onstat -g afr command: Print allocated memory fragments
        Use the onstat -g afr command to display information about the allocated memory fragments for a specified session or shared-memory pool. Each session is allocated a pool of shared memory.
        onstat -g all command: Print diagnostic information
        Use the onstat -g all command to gather diagnostic information if advised to do so by Informix® Support. For normal administrative purposes, use the onstat -g command with individual options.
        onstat -g aqt command: Print data mart and accelerated query table information
        Use the onstat -g aqt command to display information about the data marts and the associated accelerated query tables (AQTs).
        onstat -g arc command: Print archive status
        Use the onstat -g arc command to display information about the last committed archive for each dbspace and also information about any current ongoing archives.
        onstat -g ath command: Print information about all threads
        Use the onstat -g ath command to display information about all threads.
        onstat -g bth and -g BTH: Print blocked and waiting threads
        Use the onstat -g bth command to display the dependencies between blocking and waiting threads. Use the onstat -g BTH command to display session and stack information for the blocking threads.
        onstat -g buf command: Print buffer pool profile information
        Use the onstat -g buf command to show profile information for each buffer pool.
        onstat -g cac command: Print information about caches
        Use the onstat -g cac command to see summary and detailed information about all caches or about a single cache.
        onstat -g ckp command: Print checkpoint history and configuration recommendations
        Use the onstat -g ckp command to print checkpoint history and show configuration recommendations if a suboptimal configuration is detected.
        onstat -g cfg command: Print the current values of configuration parameters
        Use the onstat -g cfg command to print a list of configuration parameters with their current values. You can use more command options to print more information about the configuration parameters.
        onstat -g cluster command: Print high-availability cluster information
        Use the onstat -g cluster command to display information about the servers in a high-availability cluster environment.
        onstat -g cmsm command: Print Connection Manager information
        Use the onstat -g cmsm command to display information about a specific Connection Manager, or all of the Connection Managers that are attached to the database server the command is run on.
        onstat -g con command: Print condition and thread information
        Use the onstat -g con command to display information about conditions and the threads that are waiting for the conditions.
        onstat -g cpu: Print runtime statistics
        Use the onstat -g cpu command to display information about runtime statistics for each thread that is running in the server.
        onstat -g dbc command: Print dbScheduler and dbWorker thread statistics
        Use the onstat -g dbc command to display statistics about the Scheduler tasks that are currently running, which are handled by dbWorker threads, or scheduled to be run, which are handled by the dbScheduler thread.
        onstat -g defragment command: Print defragment partition extents
        Use the onstat -g defragment command to display information about the active requests to defragment partition extents.
        onstat -g dic command: Print table information
        Use the onstat -g dic command to display a line of information about each table that is cached in the shared-memory dictionary. If you specify a table name, this command prints internal SQL information about that particular table.
        onstat -g dis command: Print database server information
        Use the onstat -g dis command to display a list of database servers, the status of each server, and information about each server, including the location of the INFORMIXDIR directory, sqlhosts file, and ONCONFIG file. You can use this command in any database server mode, including offline.
        onstat -g dll command: Print dynamic link library file list
        Use the onstat -g dll command to display a list of and the status of dynamic link library (DLL) files that were loaded.
        onstat -g dmp command: Print raw memory
        Use the onstat -g dmp command to display information about raw memory at a given address for a number of given bytes.
        onstat -g dri command: Print high-availability data replication information
        Use the onstat -g dri command, either alone or with the ckpt or que options, to print information about high-availability data replication statistics on the current server.
        onstat -g dsc command: Print distribution cache information
        Use the onstat -g dsc command to display information about the distribution cache.
        onstat -g dsk command: Print the progress of the currently running compression operation
        Use the onstat -g dsk command to print information that shows the progress of currently running compression operations, such as compress, repack, and shrink.
        onstat -g env command: Print environment variable values
        Use the onstat -g env command to display the values of the environment variables that the database server currently uses.
        onstat -g ffr command: Print free fragments
        Use the onstat -g ffr command to display information about the free memory fragments for a specified session or shared-memory pool.
        onstat -g glo command: Print global multithreading information
        Use the onstat -g glo command to display global information about multithreading, information about each virtual processor that is running, and cumulative statistics for each virtual-processor class. This information includes CPU use information about the virtual processors, the total number of sessions, and other multithreading global counters.
        onstat -g his command: Print SQL trace information
        Use the onstat -g his command to display SQL trace information from the collection of syssqltrace tables (syssqltrace, syssqltrace_info, syssqltrace_hvar and syssqltrace_itr) in the sysmaster database.
        onstat -g ioa command: Print combined onstat -g information
        Use the onstat -g ioa command to display combined information from the onstat -g iob, onstat -g iof, onstat -g ioq, and onstat -g iov commands.
        onstat -g iob command: Print big buffer use summary
        Use the onstat -g iob command to display a summary of big buffer use.
        onstat -g iof command: Print asynchronous I/O statistics
        Use the onstat -g iof command to display the asynchronous I/O statistics by chunk or file.
        onstat -g iog command: Print AIO global information
        Use the onstat -g iog command to display global information about AIO.
        onstat -g ioq command: Print I/O queue information
        Use the onstat -g ioq command to display statistics about the number and types of operations performed by I/O queues.
        onstat -g ipl command: Print index page logging status information
        Use the onstat -g ipl command to display information about the status of index page logging.
        onstat -g iov command: Print AIO VP statistics
        Use the onstat -g iov command to display asynchronous I/O statistics for each virtual processor.
        onstat -g lap command: Print light appends status information
        Use the onstat -g lap command to display information about the status of light appends occurring in the system.
        onstat -g laq command: Print log apply queues
        Use the onstat -g laq command to print information about log recovery apply queues.
        onstat -g lmm command: Print low memory management information
        Use the onstat -g lmm command to display information about automatic low memory management settings and recent activity.
        onstat -g lmx command: Print all locked mutexes
        Use the onstat -g lmx command to display information about all locked mutexes.
        onstat -g lsc command: Print active light scan status (deprecated)
        The onstat -g lsc command has been superseded by the onstat -g scn command.
        onstat -g mem command: Print pool memory statistics
        Use the onstat -g mem command to display the memory statistics for a pool.
        onstat -g mgm command: Print MGM resource information
        Use the onstat -g mgm command to show resource information about Memory Grant Manager (MGM).
        onstat -g nbm command: Print a block bit map
        Use the onstat -g nbm command to display the block bit map for the nonresident segments.
        onstat -g nsc command: Print current shared memory connection information
        Use the onstat -g nsc command to display information about shared memory connections either for all of the current connections or for a specified connection ID.
        onstat -g nsd command: Print poll threads shared-memory data
        Use the onstat -g nsd command to display information about shared-memory data for poll threads.
        onstat -g nss command: Print shared memory network connections status
        Use the onstat -g nss sessionid command to display information about the status of the shared memory network connections.
        onstat -g ntd command: Print network statistics
        Use the onstat -g ntd command to display network statistics by service.
        onstat -g ntm command: Print network mail statistics
        Use the onstat -g ntm command to display statistics about network mail.
        onstat -g ntt command: Print network user times
        Use the onstat -g ntt command to display information about network user times.
        onstat -g ntu command: Print network user statistics
        Use the onstat -g ntu command to display information about network user statistics.
        onstat -g opn command: Print open partitions
        Use the onstat -g opn command to display a list of the partitions (tables and indexes), by thread ID, that are currently open in the system.
        onstat -g osi: Print operating system information
        Use the onstat -g osi command to display information on your operating system resources and parameters, including shared memory and semaphore parameters, the amount of memory currently configured on the computer, and the amount of memory that is unused.
        onstat -g pd command: Print push data session-related information
        Use the onstat -g pd command to display information about the push data session.
        onstat -g pd event command: Print push data event-related information
        Use the onstat -g pd event command to display information about the push data event.
        onstat -g pfsc command: Print partition free space cache information
        Use the onstat -g pfsc command to display information about all partition free space caches.
        onstat -g pos command: Print file values
        Use the onstat -g pos command to display the values in the $INFORMIXDIR/etc/.infos.DBSERVERNAME file.
        onstat -g ppd command: Print partition compression dictionary information
        Use the onstat -g ppd command to display information about the active compression dictionaries that were created for compressed tables and table fragments or compressed B-tree indexes. You can choose to print information for a particular numbered partition or for all open partitions.
        onstat -g ppf command: Print partition profiles
        Use the onstat -g ppf partition_number command to display the partition profile for the specified partition number.
        onstat -g pqs command: Print operators for all SQL queries
        Use the onstat -g pqs command to display information about the operators used in all of the SQL queries that are currently running.
        onstat -g prc command: Print sessions using UDR or SPL routines
        Use the onstat -g prc command to display the number of sessions that are currently using the UDR or SPL routine.
        onstat -g proxy command: Print proxy distributor information
        Use the onstat -g proxy command to display information about proxy distributors. The output of the onstat -g proxy command differs slightly depending on whether the command is run on a primary server or on a secondary server.
        onstat -g qst command: Print wait options for mutex and condition queues
        Use the onstat -g qst command to display the wait statistics for mutex queues and condition queues (queues of waiters for a mutex or a condition).
        onstat -g rah command: Print read-ahead request statistics
        Use the onstat -g rah command to display information about read-ahead requests.
        onstat -g rbm command: Print a block map of shared memory
        Use the onstat -g rbm command to display a hexadecimal bitmap of the free and used blocks within the resident segment of shared memory.
        onstat -g rea command: Print ready threads
        Use the onstat -g rea command to display information about the virtual processor threads whose current status is ready.
        onstat -g rss command: Print RS secondary server information
        Use the onstat -g rss commands to display information about remote standalone secondary servers.
        onstat -g rwm command: Print read and write mutexes
        Use the onstat -g rwm command to display information about read, write, and waiting mutex threads, and to list the addresses of the tickets that these threads have acquired.
        onstat -g sch command: Print VP information
        Use the onstat -g sch command to display information about thread migration and the number of semaphore operations, spins, and busy waits for each virtual processor.
        onstat -g scn command: Print scan information
        Use the onstat -g scn command to display the status of a current scan and information about the scan.
        onstat -g sds command: Print SD secondary server information
        Use the onstat -g sds command to display information about shared-disk secondary servers.
        onstat -g seg command: Print shared memory segment statistics
        Use the onstat -g seg command to show the statistics for shared memory segments.
        onstat -g ses command: Print session-related information
        Use the onstat -g ses command to display information about the session.
        onstat -g shard command: Print information about the shard definition
        Use the onstat -g shard command to display information about the sharding definition.
        onstat -g sle command: Print all sleeping threads
        Use the onstat -g sle command to print all sleeping threads.
        onstat -g smb command: Print sbspaces information
        Use the onstat -g smb command to display detailed information about sbspaces.
        onstat -g smx command: Print multiplexer group information
        Use the onstat -g smx command to display information about the server multiplexer group for servers using SMX.
        onstat -g spi command: Print spin locks with long spins
        Use the onstat -g spi command to display information about spin locks with long spins.
        onstat -g sql command: Print SQL-related session information
        Use the onstat -g sql command to display SQL-related information about a session.
        onstat -g spf: Print prepared statement profiles
        Use the onstat -g spf command to display current statistics about SQL queries.
        onstat -g src command: Patterns in shared memory
        Use the onstat -g src command to search for patterns in shared memory.
        onstat -g ssc command: Print SQL statement occurrences
        Use the onstat -g ssc command to monitor the number of times that the database server reads the SQL statement in the cache.
        onstat -g stk command: Print thread stack
        Use the onstat -g stk tid command to display the stack of the thread specified by thread ID.
        onstat -g stm command: Print SQL statement memory usage
        Use the onstat -g stm command to display the memory that each prepared SQL statement uses.
        onstat -g stq command: Print queue information
        Use the onstat -g stq command to display information about the queue.
        onstat -g sts command: Print stack usage for each thread
        Use the onstat -g sts command to display information about the maximum and current stack use for each thread.
        onstat -g sym command: Print symbol table information for the oninit utility
        Use the onstat -g sym command to display symbol table information for the oninit utility.
        onstat -g top command: Print top consumers of resources
        Use the onstat -g top command to display information about top consumers of various resources such as CPU time, I/O operations, and memory growth.
        onstat -g tpf command: Print thread profiles
        Use the onstat -g tpf command to display thread profiles.
        onstat -g ufr command: Print memory pool fragments
        Use the onstat -g ufr command to display a list of the fragments that are currently in use in the specified memory pool.
        onstat -g vpcache command: Print CPU VP and tenant VP private memory cache statistics
        Run the onstat -g vpcache command to display statistics about CPU virtual processor and tenant virtual processor private memory caches.
        onstat -g wai command: Print wait queue thread list
        Use the onstat -g wai command to display a list of the threads in the system that are currently in the wait queue and not currently executing. The output is sorted by thread ID.
        onstat -g wmx command: Print all mutexes with waiters
        Use the onstat -g wmx command to display all of the mutexes with waiters.
        onstat -g wst command: Print wait statistics for threads
        Use the onstat -g wst command to show the wait statistics for the threads within the system.
        onstat -G command: Print TP/XA transaction information
        Use the onstat -G command to display information about global transactions generated through the TP/XA library.
        onstat -h command: Print buffer header hash chain information
        Use the onstat -h command to display information about the buffer header hash chains (sometimes called "hash buckets") that are used to access pages in each buffer pool.
        onstat -i command: Initiate interactive mode
        Use the onstat -i command to put the onstat utility in the interactive mode.
        onstat -I command: Print general statistics about index operations
        Use the onstat -I command to display instance-wide information about index operations.
        onstat -k command: Print active lock information
        Use the onstat -k command to print information about active locks, including the address of the lock in the lock table.
        onstat -l command: Print physical and logical log information
        Use the onstat -l command to display information about the physical logs, logical logs, and temporary logical logs.
        onstat -L command: Print the number of free locks
        Use the onstat -L command to print the number of free locks on a lock-free list.
        onstat -m command: Print recent system message log information
        Use the onstat -m command to display the 20 most recent lines of the system message log.
        onstat -o command: Output shared memory contents to a file
        Use the onstat -o command to write the contents of shared memory to a specified file for later analysis. If you do not specify an output file, a file named onstat.out is created in the current directory.
        onstat -p command: Print profile counts
        Use the onstat -p command to display information about profile counts either since you started the database server or since you ran the onstat -z command.
        onstat -P command: Print partition information
        Use the onstat -P command to display the partition number and the pages in the buffer pool for all of the partitions.
        onstat -r command: Repeatedly print selected statistics
        Use the onstat -r command to repeatedly print the statistics for other options specified in the command at specified intervals.
        onstat -R command: Print LRU, FLRU, and MLRU queue information
        Use the onstat -R command to display detailed information about the LRU queues, FLRU queues, and MLRU queues. For each queue, the onstat -R command displays the number of buffers in the queue and the number and percentage of buffers that have been modified.
        onstat -s command: Print latch information
        Use the onstat -s command to display general latch information, including the resource that the latch controls.
        onstat -t and onstat -T commands: Print tblspace information
        Use the onstat -t command to display tblspace information for active tblspaces. Use the onstat -T command to display tblspace information for all tblspaces.
        onstat -u command: Print user activity profile
        Use the onstat -u command to display a profile of user activity.
        onstat -x command: Print database server transaction information
        Use the onstat -x command to display transaction information on the database server.
        onstat -X command: Print thread information
        Use the onstat -X command to obtain precise information about the threads that are waiting for buffers.
        onstat -z command: Clear statistics
        Use the onstat -z command to clear database server statistics, including statistics that relate to Enterprise Replication, and set the profile counts to 0.
        Return codes on exiting the onstat utility
        The onstat utility displays a set of return codes when you exit the utility.
    - 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.

onstat -g defragment command: Print defragment partition extents

Use the onstat -g defragment command to display information about the active requests to defragment partition extents.

Figure 1. Syntax:

Example output

Figure 2. onstat -g defragment command output

Defrag info
id  table name                     tid  dbsnum  partnum  status                substatus  errnum
15  stores_demo:informix.stdtab2   49   2       2097155  SEARCHING_FOR_EXTENT  0          0

Note: This command displays information about defragment requests that are active. If there are no active defragment requests, only the column headings are returned.

Output description

id

The ID of the defragment request.

table name

The fully-qualified name of the table that is being defragmented.

tid

The thread ID.

dbsnum

The dbspace number that is being defragmented.

partnum

The partition number that is being defragmented.

status

SEARCHING_FOR_EXTENT
MERGING_EXTENTS
DEFRAG_COMPLETED
DEFRAG_FAILED

substatus

The detailed status number, if any.

errnum

The last error number returned from the defragmentation request.