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.
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.
Parallel database query (PDQ)
You can manage how the database server performs PDQ and you can monitor the resources that the database server uses for PDQ.
Database server operations that use PDQ
Informix® processes some types of SQL operations that the database server processes in parallel. However some situations limit the degree of parallelism that Informix can use.

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.
  - 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.
    - Performance basics
      Performance measurement and tuning issues and methods are relevant to daily database server administration and query execution.
    - Performance monitoring and the tools you use
      You can use performance monitoring tools to create a performance history, to monitor database resources at scheduled times, or to monitor ongoing transaction or query performance.
    - Effect of configuration on CPU utilization
      The combination of operating-system and Informix® configuration parameters can affect CPU utilization. You can change the settings of the Informix configuration parameters that directly affect CPU utilization, and you can adjust the settings for different types of workloads.
    - Effect of configuration on memory utilization
      The combination of operating-system and Informix® configuration parameters can affect memory utilization.
    - Table performance considerations
      Some performance issues are associated with unfragmented tables and table fragments.
    - Boosted Partition Free Space Caches (PFSC)
    - Indexes and index performance considerations
      Informix® provides several types of indexes. Some performance issues are associated with indexes.
    - Locking
      The database server uses locks, which can affect concurrency and performance. You can monitor and administer locks.
    - Fragmentation guidelines
      One of the most frequent causes of poor performance in relational database systems is contention for data that resides on a single I/O device. Proper fragmentation of high-use tables can significantly reduce I/O contention. These topics discuss the performance considerations that are involved when you use table fragmentation.
    - Queries and the query optimizer
      These topics describe query plans, explain how the database server manages query optimization, and discuss factors that you can use to influence the query plan. These topics also describe performance considerations for SPL routines, the UDR cache, and triggers.
    - Optimizer directives
      Optimizer directives are comments that tell the query optimizer how to execute a query. You can use optimizer directives to improve query performance.
    - Parallel database query (PDQ)
      You can manage how the database server performs PDQ and you can monitor the resources that the database server uses for PDQ.
      - What PDQ is
        Parallel database query (PDQ) is a database server feature that can improve performance dramatically when the server processes queries that decision-support applications initiate. PDQ enables Informix® to distribute the work for one aspect of a query among several processors. For example, if a query requires an aggregation, Informix can distribute the work for the aggregation among several processors.
      - Structure of a PDQ query
        Each decision-support query has a primary thread. The database server can start additional threads to perform tasks for the query (for example, scans and sorts). Depending on the number of tables or fragments that a query must search and the resources that are available for a decision-support query, the database server assigns different components of a query to different threads.
      - Database server operations that use PDQ
        Informix® processes some types of SQL operations that the database server processes in parallel. However some situations limit the degree of parallelism that Informix can use.
        Parallel update and delete operations
        Informix® performs some types of update and delete operations in parallel.
        Parallel insert operations
        Informix® performs some types of insert operations in parallel.
        Parallel index builds
        Index builds can take advantage of PDQ and can be parallelized. The database server performs both scans and sorts in parallel for index builds.
        Parallel user-defined routines
        If a query contains a user-defined routine (UDR) in an expression, the database server can execute a query in parallel when you turn on PDQ.
        Hold cursors that use PDQ
        When hold cursors that are created by declaring the WITH HOLD qualifier have no locks, PDQ is enabled.
        SQL operations that do not use PDQ
        The database server does not process some types of queries in parallel.
        Update statistics operations affected by PDQ
        An SQL UPDATE STATISTICS statement that is not processed in parallel, is affected by PDQ because it must allocate the memory used for sorting. Thus the behavior of the UPDATE STATISTICS statement is affected by the memory management associated with PDQ.
        SPL routines and triggers and PDQ
        Statements that involve SPL routines are not executed in parallel. However, statements within procedures are executed in parallel.
        Correlated and uncorrelated subqueries
        The database server does not use PDQ to process correlated subqueries. Only one thread at a time can execute a correlated subquery. While one thread executes a correlated subquery, other threads that request to execute the subquery are blocked until the first one completes.
        OUTER index joins and PDQ
        The database server reduces the PDQ priority of queries that contain OUTER index joins to LOW (if the priority is set to a higher value) for the duration of the query. If a subquery or a view contains OUTER index joins, the database server lowers the PDQ priority of only that subquery or view, not of the parent query or any other subquery.
        Remote tables used with PDQ
        Although the database server can process the data stored in a remote table in parallel, the data is communicated serially because the database server allocates a single thread to submit and receive the data from the remote table. The database server lowers the PDQ priority of queries that require access to a remote database to LOW.
      - The Memory Grant Manager
        The Memory Grant Manager (MGM) is a database server component that coordinates the use of memory, CPU virtual processors (VPs), disk I/O, and scan threads among decision-support queries. The MGM uses the DS_MAX_QUERIES, DS_TOTAL_MEMORY, DS_MAX_SCANS, and MAX_PDQPRIORITY configuration parameters to determine the quantity of these PDQ resources that can be granted to a decision-support query.
      - The allocation of resources for parallel database queries
        When you configure the database server, consider how the use of PDQ affects users of OLTP, decision-support (DSS) applications, and other applications. Then you can plan how to allocate resources for PDQ.
      - Managing PDQ queries
        The database server administrator, the writer of an application, and the users all have a certain amount of control over the amount of resources that Informix® allocates to processing a query. The database server administrator exerts control through the use of configuration parameters. The application developer or the user can exert control through an environment variable or SQL statement.
      - Monitoring resources used for PDQ and DSS queries
        You can monitor the resources (shared memory and threads) that the Memory Grant Manager (MGM) has allocated for PDQ queries and the resources that PDQ and decision-support (DSS) queries currently use.
    - Improving individual query performance
      You can test, monitor, and improve queries.
    - Appendix
  - 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.

Database server operations that use PDQ

Informix® processes some types of SQL operations that the database server processes in parallel. However some situations limit the degree of parallelism that Informix® can use.

In the topics on database server operations that use PDQ in this section, a query is any SELECT statement.