Administering
System administration
List of utilities
Administrator's Guide
The database server
Overview of database server configuration and administration
Database server concepts
Environment configuration
Database server configuration
Storage space creation and management
Automatic performance tuning
Feature configuration
Connectivity configuration
Limit session resources
Automate startup and shutdown on UNIX
Automate startup on Windows
Database server maintenance tasks
Client/server communication
Client/server architecture
Network protocol
Network programming interface
Windows network domain
Database server connections
Supporting multiplexed connections
Connections that the database server supports
Local connections
Shared-memory connections (UNIX)
Stream-pipe connections (UNIX and Linux)
Named-pipe connections (Windows)
Local-loopback connections
Communication support services
Connectivity files
Network-configuration files
TCP/IP connectivity files
Client and server actions when a TCP/IP connection is opened
Multiple TCP/IP ports
Network security files
Trusted-host information
Trusted-user information
The netrc information
User impersonation
The sqlhosts file and the SQLHOSTS registry key
Creating the sqlhosts file with a text editor
Setting up the SQLHOSTS registry key with Setnet32 (Windows)
The sqlhosts information
IANA standard service names and port numbers in the sqlhosts.std file
sqlhosts connectivity information
sqlhosts file and SQLHOSTS registry key options
Group information
Creating a group in the sqlhosts file
Alternatives for TCP/IP connections
Informix support for IPv6 addresses
Configuration parameters related to connectivity
Connection information set in the DBSERVERNAME configuration parameter
Connection information set in the DBSERVERALIASES configuration parameter
Connection information set in the LIMITNUMSESSIONS configuration parameter
Connection information set in the NETTYPE configuration parameter
Name service maximum retention time set in the NS_CACHE configuration parameter
Connection information set in the NUMFDSERVERS configuration parameter
Connection information set in the HA_ALIAS configuration parameter
Environment variables for network connections
Automatically terminating idle connections
Distributed Relational Database Architecture (DRDA) communications
Overview of DRDA
Configuring connectivity between Informix and HCL Data Server clients
Allocating poll threads for an interface/protocol combination with the NETTYPE configuration parameter
Specify the size of the DRDA communication buffer with the DRDA_COMMBUFFSIZE configuration parameter
The DRDAEXEC thread and queries from clients
SQL and supported and unsupported data types
Display DRDA connection information
Display DRDA session information
Examples of client/server configurations
A network connection
Multiple connection types
Accessing multiple database servers
HCL Informix MaxConnect
Database server initialization
Initialization process
Database server operating modes
Users permitted to change modes
Changing database server operating modes
Specifying administration mode users
Disk, memory, and process management
Virtual processors and threads
Virtual processors
Threads
Advantages of virtual processors
Shared processing
Save memory and resources
Parallel processing
Add and drop virtual processors in online mode
Bind virtual processors to CPUs
How virtual processors service threads
Control structures
Context switching
Stacks
Queues
Ready queues
Sleep queues
Wait queues
Mutexes
Virtual processor classes
CPU virtual processors
Determine the number of CPU virtual processors needed
Run on a multiprocessor computer
Run on a single-processor computer
Add and drop CPU virtual processors in online mode
Prevent priority aging
Processor affinity
Set processor affinity with the VPCLASS configuration parameter
User-defined classes of virtual processors
Determine the number of user-defined virtual processors needed
User-defined virtual processors
Specify user-defined virtual processors
Assign a UDR to a user-defined virtual-processor class
Add and drop user-defined virtual processors in online mode
Tenant virtual processor class
Java virtual processors
Disk I/O virtual processors
I/O priorities
Logical-log I/O
Physical-log I/O
Asynchronous I/O
Kernel-asynchronous I/O
AIO virtual processors
Network virtual processors
Specifying Network Connections
Run poll threads on CPU or network virtual processors
Specify the number of networking virtual processors
Specify listen and poll threads for the client/server connection
Fast polling
Multiple listen threads
Add listen threads
Add a network-interface card
Dynamically starting, stopping, or restarting a listen thread
Communications support module virtual processor
Encrypt virtual processors
Audit virtual processor
Miscellaneous virtual processor
Basic text search virtual processors
MQ messaging virtual processor
Web feature service virtual processor
XML virtual processor
Manage virtual processors
Set virtual-processor configuration parameters
Start and stop virtual processors
Add virtual processors in online mode
Add virtual processors in online mode with onmode
Add network virtual processors
Drop CPU and user-defined virtual processors
Monitor virtual processors
Monitor virtual processors with command-line utilities
The onstat -g ath command
The onstat -g glo command
The onstat -g ioq command
The onstat -g rea command
Monitor virtual processors with SMI tables
Shared memory
Shared memory
Shared-memory use
Shared-memory allocation
Shared-memory size
Action to take if SHMTOTAL is exceeded
Processes that attach to shared memory
How a client attaches to the communications portion (UNIX)
How utilities attach to shared memory
How virtual processors attach to shared memory
Obtain key values for shared-memory segments
Specify where to attach the first shared-memory segment
Attach additional shared-memory segments
Define the shared-memory lower-boundary address
Resident portion of shared memory
Shared-memory header
Logical-log buffer
Physical-log buffer
High-Availability Data-Replication buffer
Lock table
Buffer pool portion of shared memory
Virtual portion of shared memory
Management of the virtual portion of shared memory
Size of the virtual portion of shared memory
Components of the virtual portion of shared memory
Shared-memory internal tables
Buffer table
Chunk table
Dbspace table
Page-cleaner table
Tblspace table
Transaction table
User table
Big buffers
Session data
Thread data
Stacks
Heaps
Data-distribution cache
Dictionary cache
SQL statement cache
Sort memory
SPL routine and the UDR cache
Global pool
Communications portion of shared memory (UNIX)
Virtual-extension portion of shared memory
Concurrency control
Shared-memory mutexes
Shared-memory buffer locks
Types of buffer locks
Share lock
Exclusive lock
Database server thread access to shared buffers
FIFO/LRU queues
Components of LRU queue
Pages in least-recently used order
LRU queues and buffer-pool management
Number of LRU queues to configure
Number of cleaners to allocate
Number of pages added to the MLRU queues
End of MLRU cleaning
Read-ahead operations
Database server thread access to buffer pages
Flush data to disk
Flush buffer-pool buffers
Flush before-images first
Flush the physical-log buffer
Synchronize buffer flushing
Types of writes during flushing
Foreground write
LRU write
Chunk write
Flush the logical-log buffer
After a transaction is prepared or terminated in a database with unbuffered logging
When a session that uses nonlogging databases or unbuffered logging terminates
When a checkpoint occurs
When a page is modified that does not require a before-image in the physical-log file
Buffer large-object data
Write simple large objects
Blobpages and shared memory
Creation of simple large objects
Creation of blobpage buffers
Access smart large objects
Memory use on 64-bit platforms
Manage shared memory
Set operating-system shared-memory configuration parameters
Maximum shared-memory segment size
Using more than two gigabytes of memory (Windows)
Maximum number of shared-memory identifiers (UNIX)
Semaphores (UNIX)
Set database server shared-memory configuration parameters
Set SQL statement cache parameters
Set up shared memory
Turn residency on or off for resident shared memory
Turn residency on or off in online mode
Turn residency on or off when restarting the database server
Add a segment to the virtual portion of shared memory
Reserve memory for critical activities
Configure the server response when memory is critically low
Scenario for maintaining a targeted amount of memory
Monitor shared memory
Monitor shared-memory segments
Monitor the shared-memory profile and latches
Command-line utilities to monitor shared memory and latches
SMI tables
Monitor buffers
Deleting shared memory segments after a server failure
Data storage
Chunks
Disk allocation for chunks
Disk access on Windows
Unbuffered or buffered disk access on UNIX
Extendable chunks
Partitions and offsets
Pages
Blobpages
Sbpages
Extents
Dbspaces
Control of where simple large object data is stored
Root dbspace
Temporary dbspaces
Blobspaces
Sbspaces
Advantages of using sbspaces
Sbspaces and Enterprise Replication
Metadata, user data, and reserved area
Control of where smart large object data is stored
Storage characteristics of sbspaces
Extent sizes for sbspaces
Average smart-large-object size
Buffering mode
Last-access time
Lock mode
Logging
Levels of inheritance for sbspace characteristics
More information about sbspaces
Temporary sbspaces
Comparison of temporary and standard sbspaces
Temporary smart large objects
Plogspace
Extspaces
Databases
Tables
Damaged tables
Table types for Informix
Standard permanent tables
RAW tables
Temp tables
Properties of table types
Loading of data into a table
Fast recovery of table types
Backup and restore of RAW tables
Temporary tables
Temporary tables that you create
Where user-created temporary tables are stored
Temporary tables that the database server creates
Where database server-created temporary tables are stored
Tblspaces
Maximum number of tblspaces in a table
Table and index tblspaces
Extent interleaving
Table fragmentation and data storage
Amount of disk space needed to store data
Size of the root dbspace
Amount of space that databases require
The storage pool
Disk-layout guidelines
Dbspace and chunk guidelines
Table-location guidelines
Sample disk layouts
Logical-volume manager
Manage disk space
Allocate disk space
Specify an offset
Specify an offset for the initial chunk of root dbspace
Specify an offset for additional chunks
Use offsets to create multiple chunks
Allocating cooked file spaces on UNIX
Allocating raw disk space on UNIX
Create symbolic links to raw devices (UNIX)
Allocating NTFS file space on Windows
Allocating raw disk space on Windows
Specify names for storage spaces and chunks
Specify the maximum size of chunks
Specify the maximum number of chunks and storage spaces
Back up after you change the physical schema
Monitor storage spaces
Manage dbspaces
Creating a dbspace that uses the default page size
Specifying the first and next extent sizes for the tblspace tblspace
Creating a dbspace with a non-default page size
Improving the performance of cooked-file dbspaces by using direct I/O
Storing multiple named fragments in a single dbspace
Creating a temporary dbspace
What to do if you run out of disk space
Adding a chunk to a dbspace or blobspace
Rename dbspaces
Additional actions that may be required after you rename a dbspace
Managing automatic location and fragmentation
Manage blobspaces
Creating a blobspace
Prepare blobspaces to store TEXT and BYTE data
Determine blobpage size
Determine database server page size
Obtain blobspace storage statistics
Manage sbspaces
Creating an sbspace
Size sbspace metadata
Adding a chunk to an sbspace
Alter storage characteristics of smart large objects
Creating a temporary sbspace
Manage the plogspace
Automatic space management
Creating and managing storage pool entries
Marking a chunk as extendable or not extendable
Modifying the sizes of an extendable storage space
Changing the threshold and wait time for the automatic addition of more space
Configuring the frequency of the monitor low storage task
Manually expanding a space or extending an extendable chunk
Example of minimally configuring for and testing the automatic addition of more space
Example of configuring for the automatic addition of more space
Drop a chunk
Verify whether a chunk is empty
Drop a chunk from a dbspace with onspaces
Drop a chunk from a blobspace
Drop a chunk from an sbspace with onspaces
The -f (force) option
Delete smart large objects without any pointers
Drop a storage space
Preparation for dropping a storage space
Drop a mirrored storage space
Drop a storage space with onspaces
Back up after dropping a storage space
Creating a space or chunk from the storage pool
Returning empty space to the storage pool
Manage extspaces
Create an extspace
Drop an extspace
Skip inaccessible fragments
The DATASKIP configuration parameter
The dataskip feature of onspaces
Use onstat to check dataskip status
The SQL statement SET DATASKIP
Effect of the dataskip feature on transactions
Determine when to use dataskip
Determine when to skip selected fragments
Determine when to skip all fragments
Monitor fragmentation use
Display databases
SMI tables
Monitor disk usage
Monitor chunks
The onstat -d utility
The onstat -d update option
The onstat -D option
Monitor chunk I/O activity with the onstat -g iof command
The oncheck -pr command
The oncheck -pe command
SMI tables
Monitor tblspaces and extents
SMI tables
Monitor simple large objects in a blobspace
Determine blobpage fullness with oncheck -pB
Monitor blobspace usage with oncheck -pe
Monitor simple large objects in a dbspace with oncheck -pT
Monitor sbspaces
The onstat -d option
The oncheck -ce and oncheck -pe options
The oncheck -cs option
The oncheck -ps option
Monitoring the metadata and user-data areas
Multitenancy
Creating a tenant database
Managing tenant databases
Restoring a tenant database to a point in time
Storage optimization
Storage optimization methods
Scheduling data optimization
Example: Optimizing data storage on demand
Partition defragmentation
Compression
Data that you can compress
Data that you cannot compress
B-tree index compression
Compression ratio estimates
Compression dictionaries
Tools for moving compressed data
BLOBspace Blob Compression
Methods for viewing compression information
Load data into a table
Moving data with external tables
External tables
Defining external tables
Map columns to other columns
Load data from and unload to a named pipe
Loading data with named pipes
FIFO virtual processors
Unloading data with named pipes
Copying data from one instance to another using the PIPE option
Monitor the load or unload operations
Monitor frequent load and unload operations
Monitor FIFO virtual processors
External tables in high-availability cluster environments
System catalog entries for external tables
Performance considerations when using external tables
Manage errors from external table load and unload operations
Reject files
External table error messages
Recoverability of table types for external tables
Storage space encryption
Creating encrypted or unencrypted storage spaces
Changing storage space encryption during a restore
Monitoring the encryption of storage spaces
Logging and log administration
Logging
Database server processes that require logging
Transaction logging
Logging of SQL statements and database server activity
Activity that is always logged
Activity logged for databases with transaction logging
Activity that is not logged
Database-logging status
Unbuffered transaction logging
Buffered transaction logging
ANSI-compliant transaction logging
No database logging
Databases with different log-buffering status
Database logging in an X/Open DTP environment
Settings or changes for logging status or mode
Manage the database-logging mode
Change the database-logging mode
Modify the database-logging mode with ondblog
Change the buffering mode with ondblog
Cancel a logging mode change with ondblog
End logging with ondblog
Make a database ANSI compliant with ondblog
Changing the logging mode of an ANSI-compliant database
Modify the database logging mode with ontape
Turn on transaction logging with ontape
End logging with ontape
Change buffering mode with ontape
Make a database ANSI compliant with ontape
Modify the table-logging mode
Alter a table to turn off logging
Alter a table to turn on logging
Disable logging on temporary tables
Monitor transactions
Monitor the logging mode of a database
Monitor the logging mode with SMI tables
Logical log
What is the logical log?
Location of logical-log files
Identification of logical-log files
Status flags of logical-log files
Size of the logical-log file
Number of logical-log files
Performance considerations
Dynamic log allocation
Freeing of logical-log files
Action if the next logical-log file is not free
Action if the next log file contains the last checkpoint
Log blobspaces and simple large objects
Switch log files to activate blobspaces
Back up log files to free blobpages
Back up blobspaces after inserting or deleting TEXT and BYTE data
Log sbspaces and smart large objects
Sbspace logging
Logging for smart large objects
Logging for updated smart large objects
Turn logging on or off for an sbspace
Smart-large-object log records
Prevent long transactions when logging smart-large-object data
Logging process
Dbspace logging
Blobspace logging
Manage logical-log files
Estimate the size and number of log files
Estimate the log size when logging smart large objects
Estimate the number of logical-log files
Back up logical-log files
Backing up blobspaces
Back up sbspaces
Switch to the next logical-log file
Free a logical-log file
Delete a log file with status D
Free a log file with status U
Freeing a log file with status U-B or F
Freeing a log file with status U-C or U-C-L
Free a log file with status U-B-L
Monitor logging activity
Monitor the logical log for fullness
The onstat -l command
The oncheck -pr command
Monitor temporary logical logs
SMI tables
Monitor log-backup status
Allocate logical log files
Dynamically add a logical-log file to prevent transaction blocking
Size and number of dynamically added log files
Location of dynamically added logical log files
Monitor events for dynamically added logs
Dynamically add logical logs for performance
Adding logical-log files manually
Dropping logical-log files
Change the size of logical-log files
Move logical-log files
Display logical-log records
Controlling long transactions
Physical logging, checkpoints, and fast recovery
Critical sections
Physical logging
Fast recovery use of physically-logged pages
Backup use of physically-logged pages
Database server activity that is physically logged
Physical recovery messages
Physical logging and simple large objects
Physical logging and smart large objects
Size and location of the physical log
Strategy for estimating the size of the physical log
Physical-log overflow when transaction logging is turned off
Checkpoints
LRU values for flushing a buffer pool between checkpoints
Checkpoints during backup
Fast recovery
Need for fast recovery
Situations when fast recovery is initiated
Fast recovery and buffered logging
Possible physical log overflow during fast recovery
Fast recovery and no logging
Fast recovery after a checkpoint
The server returns to the last-checkpoint state
The server locates the checkpoint record in the logical log
The server rolls forward logical-log records
The server rolls back uncommitted transactions
Manage the physical log
Change the physical-log location and size
Monitor physical and logical-logging activity
Monitor checkpoint information
Turn checkpoint tuning on or off
Force a checkpoint
Server-provided checkpoint statistics
SMI tables
Turn automatic LRU tuning on or off
Fault tolerance
Mirroring
Mirroring
Benefits of mirroring
Costs of mirroring
Consequences of not mirroring
Data to mirror
Alternatives to mirroring
Logical volume managers
Hardware mirroring
External backup and restore
Mirroring process
Creation of a mirror chunk
Mirror status flags
Recovery
Actions during processing
Disk writes to mirror chunks
Disk reads from mirror chunks
Detection of media failures
Chunk recovery
Result of stopping mirroring
Structure of a mirror chunk
Using mirroring
Preparing to mirror data
Enable the MIRROR configuration parameter
Allocate disk space for mirrored data
Link chunks (UNIX)
Relink a chunk to a device after a disk failure
Using mirroring
Mirroring the root dbspace during initialization
Change the mirror status
Manage mirroring
Start mirroring for unmirrored storage spaces
Start mirroring for unmirrored dbspaces using onspaces
Start mirroring for new storage spaces
Start mirroring for new spaces using onspaces
Add mirror chunks
Add mirror chunks using onspaces
Swap mirror chunk
Take down a mirror chunk
Take down mirror chunks using onspaces
Recover a mirror chunk
Recover a mirror chunk using onspaces
End mirroring
End mirroring using onspaces
Consistency checking
Perform periodic consistency checking
Verify consistency
Validate system catalog tables
Validate data pages
Validate extents
Validate indexes
Validate logical logs
Validate reserved pages
Validate metadata
Monitor for data inconsistency
Read assertion failures in the message log and dump files
Validate table and tblspace data
Retain consistent level-0 backups
Deal with corruption
Find symptoms of corruption
Fix index corruption
Fix I/O errors on a chunk
Collect diagnostic information
Disable I/O errors
Monitor the database server for disabling I/O errors
The message log to monitor disabling I/O errors
Event alarms to monitor disabling I/O errors
No bad-sector mapping
High availability and scalability
Strategies for high availability and scalability
Components supporting high availability and scalability
Advantages of data replication
Clustering versus mirroring
Clustering versus two-phase commit
Type of data replicated in clusters
Primary and secondary database servers
Transparent scaling and workload balancing strategies
High availability strategies
High-availability cluster configuration
Plan for a high-availability cluster
Configuring clusters
Hardware and operating-system requirements for clusters
Database and data requirements for clusters
Database server configuration requirements for clusters
Database server version
Storage space and chunk configuration
Non-default page sizes in an HDR environment
Mirroring
Physical-log configuration
Dbspace and logical-log tape backup devices
Logical-log configuration
High-availability cluster configuration parameters
Cluster transaction coordination
Configuring secure connections for high-availability clusters
Starting HDR for the First Time
Decrease setup time using the ontape STDIO feature
Remote standalone secondary servers
Comparison of RS secondary servers and HDR secondary servers
Index page logging
How index page logging works
Enable or disable index page logging
View index page logging statistics
Starting an RS secondary server for the first time
Decrease setup time through an alternative backup method
Converting an offline primary server to an RS secondary server
Delayed application of log records
Specifying the log staging directory
Delay application of log records on an RS secondary server
Stop the application of log records
Flow control for remote standalone secondary servers
Shared disk secondary servers
SD secondary server
Disk requirements for SD secondary servers
Setting up a shared disk secondary server
Obtain SD secondary server statistics
Promote an SD secondary server to a primary server
Convert a primary server to a standard server
SD secondary server security
Flow control for shared-disk secondary servers
Cluster administration
How data replication works
How data initially replicates
Replication of primary-server data to secondary servers
Fully synchronous mode for HDR replication
Nearly synchronous mode for HDR replication
Asynchronous mode for HDR replication
Lost-and-found transactions
Data replication configuration examples
Remote standalone secondary configuration examples
Shared disk secondary configuration examples
Enterprise Replication as part of the recoverable group
High-availability clusters with Enterprise Replication configuration example
Example of a complex failover recovery strategy
Troubleshooting high-availability cluster environments
Design data replication group clients
Use of temporary dbspaces for sorting and temporary tables
Performing basic administration tasks
Changing the configuration parameters for an HDR replication pair
Back up storage spaces and logical-log files
Changing the logging mode of databases
Add and drop chunks and storage spaces
Renaming chunks
Saving chunk status on the secondary database server
Use and change mirroring of chunks
Manage the physical log
Manage the logical log
Manage virtual processors
Manage shared memory
Configure SMX connections
Replicate an index to an HDR secondary database server
Encrypting data traffic between HDR database servers
Adjust LRU flushing and automatic tuning in HDR server pairs
Cloning a primary server
Creating a clone of a primary server
Database updates on secondary servers
Isolation levels on secondary servers
Set lock mode
Transient types on high-availability cluster secondary servers
Row versioning
Backup and restore with high-availability clusters
Change the database server mode
Changing the database server type
Prevent blocking checkpoints on HDR servers
View statistics for nonblocking checkpoints on HDR servers
Monitor HDR status
Command-line utilities
The onstat -g dri option
The oncheck -pr option
SMI tables
Obtain RS secondary server statistics
Remove an RS secondary server
RS secondary server security
Create or change a password on an RS secondary server
Transaction completion during cluster failover
Configuring the server so that transactions complete after failover
Connection management through the Connection Manager
Configuring connection management
Creating Connection Manager configuration files
Parameters and format of the Connection Manager configuration file
CMALARMPROGRAM Connection Manager configuration parameter
CM_TIMEOUT Connection Manager configuration parameter
CLUSTER Connection Manager configuration parameter
DEBUG Connection Manager configuration parameter
EVENT_TIMEOUT Connection Manager configuration parameter
FOC Connection Manager configuration parameter
GRID Connection Manager configuration parameter
INFORMIXSERVER Connection Manager configuration parameter
LOCAL_IP Connection Manager configuration parameter
LOG Connection Manager configuration parameter
LOGFILE Connection Manager configuration parameter
MACRO Connection Manager configuration parameter
NAME Connection Manager configuration parameter
REPLSET Connection Manager configuration parameter
SECONDARY_EVENT_TIMEOUT Connection Manager configuration parameter
SERVERSET Connection Manager configuration parameter
SLA Connection Manager configuration parameter
SQLHOSTS Connection Manager configuration parameter
SSL_LABEL Connection Manager configuration parameter
Modifying Connection Manager configuration files
Converting older formats of the Connection Manager configuration file to the current format
Configuring environments and setting configuration parameters for connection management
Defining sqlhosts information for connection management
Defining sqlhosts information for connection management of high-availability clusters
sqlhosts for HA clusters that use secure ports
sqlhosts for HA clusters that use DRDA
sqlhosts for HA clusters that use DRDA and secure ports
sqlhosts for grids and replicate sets
sqlhosts for grids and replicate sets that use secure ports
sqlhosts for HA replication systems
sqlhosts for HA replication systems that use secure ports
sqlhosts for server sets
Creating a password file for connecting to database servers on untrusted networks
Modifying encrypted password information
Starting Connection Managers on UNIX and Linux
Starting Connection Managers on Windows
Stopping connection management
Monitoring and troubleshooting connection management
Strategies for increasing availability with Connection Managers
Configuration examples for connection management
Example of configuring connection management for a high-availability cluster
Example of configuring connection management for a grid or replicate set
Example of configuring connection management for a high-availability replication system
Example: Configuring connection management for untrusted networks
Example: Configuring for prioritizing connections and network monitoring
Example: Configuring for an SSL connection
Example: Using the OpenSSL encryption library
Example: Using the GSKit encryption library
Configuring a CM to connect to Oninit using SSL
Configuring a client to connect to a CM using SSL
Cluster failover, redirection, and restoration
Failover configuration for high-availability clusters
Failover with ISV cluster management software
I/O fencing for shared file systems
Cluster failures
Automatic switchover
Automatic switchover without a reliable network
Manual switchover
Connecting offline servers to the new primary server
Redirection and connectivity for data-replication clients
Redirecting clients automatically with the DBPATH environment variable
How the DBPATH redirection method works
Redirecting clients with the connectivity information
Changing client connectivity information
Connecting to the database server
Automatic redirection with server groups
Redirecting clients with the INFORMIXSERVER environment variable
Redirecting clients with application code
Comparison of redirection methods
Recover HDR and RS clusters after failure
Recovering a cluster after critical data is damaged
Restarting HDR or RS clusters after a network failure
Restarting HDR or RS clusters if the secondary server fails
Recovering an HDR cluster after the secondary server became the primary server
Restart if the primary server fails
Recovering a shared-disk cluster after data is damaged
Recovering an SD cluster after the secondary server became the primary server
Distributed data
Multiphase commit protocols
Transaction managers
TP/XA Library with a transaction manager
Microsoft Transaction Server (MTS/XA)
Informix transaction support for XA-compliant, external data sources
XA in high-availability clusters
Loosely-coupled and tightly-coupled modes
Two-phase commit protocol
When the two-phase commit protocol is used
Two-phase commit concepts
Phases of the two-phase commit protocol
Precommit phase
Postdecision phase
How the two-phase commit protocol handles failures
Types of failures that automatic recovery handles
Administrator's role in automatic recovery
Automatic-recovery mechanisms for coordinator failure
Automatic-recovery mechanisms for participant failure
Presumed-end optimization
Independent actions
Situations that initiate independent action
Possible results of independent action
Independent actions that allow transactions to complete successfully
Independent actions that result in an error condition
Independent actions that result in heuristic decisions
The heuristic rollback scenario
Conditions that result in a heuristic rollback
Condition 1: Logical log fills to a high-watermark
Condition 2: System administrator executes onmode -z
Results of a heuristic rollback
Situation 1: Coordinator issues a commit and all participants report heuristic rollbacks
Situation 2: Coordinator issued a commit; one participant commits and one reports a heuristic rollback
The heuristic end-transaction scenario
When to perform a heuristic end transaction
How to use onmode -Z
Action when the transaction is ended heuristically
Monitor a global transaction
Two-phase commit protocol errors
Two-phase commit and logical-log records
Logical-log records when the transaction commits
Logical-log records written during a heuristic rollback
Logical-log records written after a heuristic end transaction
Configuration parameters used in two-phase commits
Function of the DEADLOCK_TIMEOUT parameter
Function of the TXTIMEOUT parameter
Heterogeneous commit protocol
Gateways that can participate in a heterogeneous commit transaction
Enable and disable of heterogeneous commit
How heterogeneous commit works
Precommit phase
Gateway commit phase
Heterogeneous commit optimization
Implications of a failed heterogeneous commit
Database server coordinator failure
Participant failure
Interpretation of heterogeneous commit error messages
Application attempts to update multiple gateway participants
Failed attempt to commit distributed transaction using heterogeneous commit
Manually recovering from failed two-phase commit
Determine if manual recovery is required
Determine if a transaction was implemented inconsistently
Global transaction ended prematurely
Heuristic end transaction
Heuristic rollback
Determine if the distributed database contains inconsistent data
Obtaining information from the logical log
Obtain the global transaction identifier
Decide if action is needed to correct the situation
Example of manual recovery
Overview of automatic monitoring and corrective actions
The Scheduler
Scheduler tables
Built-in tasks and sensors
Creating a task
Creating a sensor
Actions for task and sensors
Creating a group
Creating a threshold
Creating an alert
Monitor the scheduler
Modifying the scheduler
Remote administration with the SQL administration API
SQL administration API admin() and task() functions
Viewing SQL administration API history
Controlling the size of the command_history table
Query drill-down
Specifying startup SQL tracing information by using the SQLTRACE configuration parameter
Disable SQL tracing globally or for a session
Enable SQL tracing
Enable global SQL tracing for a session
HCL Informix server licensing
FlexNet Licensing Overview
Licensing configuration
System startup
System alerts when licenses are not available
High Availability server instances
Monitoring the currently held licenses
FlexNet Operations portal
The REST interface
Security
Local License Server
Informix Warehouse Accelerator
Administrator's Reference
Configuring and monitoring Informix
Database configuration parameters
onconfig file
Modifying the onconfig file
Displaying the settings in the onconfig file
onconfig Portal: Configuration parameters by functional category
ADMIN_MODE_USERS configuration parameter
ADMIN_USER_MODE_WITH_DBSA configuration parameter
ALARMPROGRAM configuration parameter
ALLOW_NEWLINE configuration parameter
ALRM_ALL_EVENTS configuration parameter
AUTO_AIOVPS configuration parameter
AUTO_CKPTS configuration parameter
AUTO_LLOG configuration parameter
AUTO_TUNE_SERVER_SIZE configuration parameter
AUTO_LRU_TUNING configuration parameter
AUTO_READAHEAD configuration parameter
AUTO_REPREPARE configuration parameter
AUTO_STAT_MODE configuration parameter
AUTO_TUNE configuration parameter
AUTOLOCATE configuration parameter
BATCHEDREAD_INDEX configuration parameter
BATCHEDREAD_TABLE configuration parameter
BLOCKTIMEOUT configuration parameter
BTSCANNER Configuration Parameter
BUFFERPOOL configuration parameter
CHECKALLDOMAINSFORUSER configuration parameter
CKPTINTVL configuration parameter
CLEANERS configuration parameter
CLUSTER_TXN_SCOPE configuration parameter
CONSOLE configuration parameter
CONVERSION_GUARD configuration parameter
DATASKIP Configuration Parameter
DBCREATE_PERMISSION configuration parameter
DB_LIBRARY_PATH configuration parameter
DBSERVERALIASES configuration parameter
DBSERVERNAME configuration parameter
DBSPACETEMP configuration parameter
Use Hash Join Overflow and DBSPACETEMP
DD_HASHMAX configuration parameter
DD_HASHSIZE configuration parameter
DEADLOCK_TIMEOUT configuration parameter
DEF_TABLE_LOCKMODE configuration parameter
DEFAULTESCCHAR configuration parameter
DELAY_APPLY Configuration Parameter
DIRECT_IO configuration parameter (UNIX)
DIRECTIVES configuration parameter
DISABLE_B162428_XA_FIX configuration parameter
DISK_ENCRYPTION configuration parameter
DRDA_COMMBUFFSIZE configuration parameter
DRAUTO configuration parameter
DRIDXAUTO configuration parameter
DRINTERVAL configuration parameter
DRLOSTFOUND configuration parameter
DRTIMEOUT configuration parameter
DS_HASHSIZE configuration parameter
DS_MAX_QUERIES configuration parameter
DS_MAX_SCANS configuration parameter
DS_NONPDQ_QUERY_MEM configuration parameter
DS_POOLSIZE configuration parameter
DS_TOTAL_MEMORY configuration parameter
Algorithm for DS_TOTAL_MEMORY
DUMPCNT configuration parameter (UNIX)
DUMPCORE configuration parameter (UNIX)
DUMPDIR configuration parameter
DUMPGCORE configuration parameter (UNIX)
DUMPSHMEM configuration parameter (UNIX)
DYNAMIC_LOGS configuration parameter
EILSEQ_COMPAT_MODE configuration parameter
ENABLE_SNAPSHOT_COPY configuration parameter
ENCRYPT_CIPHERS configuration parameter
ENCRYPT_HDR configuration parameter
ENCRYPT_MAC configuration parameter
ENCRYPT_MACFILE configuration parameter
ENCRYPT_SMX configuration parameter
ENCRYPT_SWITCH configuration parameter
EXPLAIN_STAT configuration parameter
EXT_DIRECTIVES configuration parameter
EXTSHMADD configuration parameter
FAILOVER_CALLBACK configuration parameter
FAILOVER_TX_TIMEOUT configuration parameter
FASTPOLL configuration parameter
FILLFACTOR configuration parameter
FULL_DISK_INIT configuration parameter
GSKIT_VERSION configuration parameter
HA_ALIAS configuration parameter
HA_FOC_ORDER configuration parameter
HDR_TXN_SCOPE configuration parameter
HETERO_COMMIT configuration parameter
IFX_EXTEND_ROLE configuration parameter
IFX_FOLDVIEW configuration parameter
IFX_XA_UNIQUEXID_IN_DATABASE configuration parameter
INFORMIXCONRETRY configuration parameter
INFORMIXCONTIME configuration parameter
LICENSE_SERVER configuration parameter
LICENSE_TIMING configuration parameter
LIMITNUMSESSIONS configuration parameter
LISTEN_TIMEOUT configuration parameter
LOCKS configuration parameter
LOGBUFF configuration parameter
LOGBUF_INTVL configuration parameter
LOGFILES configuration parameter
LOG_INDEX_BUILDS configuration parameter
LOG_STAGING_DIR configuration parameter
LOGSIZE configuration parameter
LOW_MEMORY_MGR configuration parameter
LOW_MEMORY_RESERVE configuration parameter
LTXEHWM configuration parameter
LTXHWM configuration parameter
MAX_FILL_DATA_PAGES configuration parameter
MAX_INCOMPLETE_CONNECTIONS configuration parameter
MAX_PDQPRIORITY configuration parameter
MIRROR configuration parameter
MIRROROFFSET configuration parameter
MIRRORPATH configuration parameter
MSG_DATE configuration parameter
MSGPATH configuration parameter
MULTIPROCESSOR configuration parameter
NET_IO_TIMEOUT_ALARM configuration parameter
NETTYPE configuration parameter
NS_CACHE configuration parameter
NUMFDSERVERS configuration parameter
OFF_RECVRY_THREADS configuration parameter
ON_RECVRY_THREADS configuration parameter
ONDBSPACEDOWN configuration parameter
Database Server Behavior When ONDBSPACEDOWN Does Not Apply
ONLIDX_MAXMEM configuration parameter
OPTCOMPIND configuration parameter
OPT_GOAL configuration parameter
PC_HASHSIZE configuration parameter
PC_POOLSIZE configuration parameter
PFSC_BOOST configuration parameter
PHYSBUFF configuration parameter
PHYSFILE configuration parameter
PLOG_OVERFLOW_PATH configuration parameter
PLCY_HASHSIZE configuration parameter
PLCY_POOLSIZE configuration parameter
PN_STAGEBLOB_THRESHOLD configuration parameter
PRELOAD_DLL_FILE configuration parameter
QSTATS configuration parameter
REMOTE_SERVER_CFG configuration parameter
REMOTE_USERS_CFG configuration parameter
RESIDENT configuration parameter
RESTARTABLE_RESTORE configuration parameter
RESTORE_POINT_DIR configuration parameter
ROOTNAME configuration parameter
ROOTOFFSET configuration parameter
ROOTPATH configuration parameter
ROOTSIZE configuration parameter
RSS_FLOW_CONTROL configuration parameter
RSS_NONBLOCKING_CKPT configuration parameter
RTO_SERVER_RESTART configuration parameter
S6_USE_REMOTE_SERVER_CFG configuration parameter
SB_CHECK_FOR_TEMP configuration parameter
SBSPACENAME configuration parameter
SBSPACETEMP configuration parameter
SDS_ALTERNATE configuration parameter
SDS_ENABLE configuration parameter
SDS_FLOW_CONTROL configuration parameter
SDS_LOGCHECK configuration parameter
SDS_PAGING configuration parameter
SDS_TEMPDBS configuration parameter
SDS_TIMEOUT configuration parameter
SEC_APPLY_POLLTIME configuration parameter
SEC_DR_BUFS configuration parameter
SEC_LOGREC_MAXBUFS configuration parameter
SECURITY_LOCALCONNECTION configuration parameter
SEQ_CACHE_SIZE configuration parameter
SERVERNUM configuration parameter
SESSION_LIMIT_LOCKS configuration parameter
SESSION_LIMIT_LOGSPACE configuration parameter
SESSION_LIMIT_MEMORY configuration parameter
SESSION_LIMIT_TEMPSPACE configuration parameter
SESSION_LIMIT_TXN_TIME configuration parameter
SHMADD configuration parameter
SHMBASE configuration parameter
SHMNOACCESS configuration parameter
SHMTOTAL configuration parameter
SHMVIRT_ALLOCSEG configuration parameter
SHMVIRTSIZE configuration parameter
SINGLE_CPU_VP configuration parameter
VPCLASS Values and the SINGLE_CPU_VP Configuration Parameter
SMX_COMPRESS configuration parameter
SMX_NUMPIPES configuration parameter
SMX_PING_INTERVAL configuration parameter
SMX_PING_RETRY configuration parameter
SP_AUTOEXPAND configuration parameter
SP_THRESHOLD configuration parameter
SP_WAITTIME configuration parameter
SQL_LOGICAL_CHAR configuration parameter
SQLTRACE configuration parameter
SSL_KEYSTORE_LABEL configuration parameter
STACKSIZE configuration parameter
STATCHANGE configuration parameter
STMT_CACHE configuration parameter
STMT_CACHE_HITS configuration parameter
STMT_CACHE_NOLIMIT configuration parameter
STMT_CACHE_NUMPOOL configuration parameter
STMT_CACHE_QUERY_PLAN configuration parameter
STMT_CACHE_SIZE configuration parameter
STOP_APPLY configuration parameter
STORAGE_FULL_ALARM configuration parameter
SYSALARMPROGRAM configuration parameter
SYSSBSPACENAME configuration parameter
TBLSPACE_STATS configuration parameter
TBLTBLFIRST configuration parameter
TBLTBLNEXT configuration parameter
TEMPTAB_NOLOG configuration parameter
TENANT_LIMIT_CONNECTIONS configuration parameter
TENANT_LIMIT_MEMORY configuration parameter
TENANT_LIMIT_SPACE configuration parameter
TLS_VERSION configuration parameter
TXTIMEOUT configuration parameter
UNSECURE_ONSTAT configuration parameter
UPDATABLE_SECONDARY configuration parameter
USELASTCOMMITTED configuration parameter
USEOSTIME configuration parameter
USERMAPPING configuration parameter (UNIX, Linux)
USRC_HASHSIZE configuration parameter
USRC_POOLSIZE configuration parameter
USTLOW_SAMPLE configuration parameter
VP_MEMORY_CACHE_KB configuration parameter
VPCLASS configuration parameter
VP_KAIO_PERCENT configuration parameter
WSTATS configuration parameter
The sysmaster database
The sysmaster Database
The buildsmi Script
The bldutil.sh Script
The System-Monitoring Interface
Understanding the SMI Tables
Accessing SMI tables
SELECT statements
Triggers and Event Alarms
SPL and SMI Tables
Locking and SMI Tables
The System-Monitoring Interface Tables
The sysutils Tables
sysadtinfo
sysaudit
syschkio
syscheckpoint
syschunks
sysckptinfo
syscluster
syscmsm
syscmsmsla
syscmsmtab
syscmsmunit
syscompdicts_full
sysconfig
sysdatabases
sysdbslocale
sysdbspaces
sysdri
sysdual
sysenv
sysenvses
sysextents
sysextspaces
sysfeatures
sysha_lagtime Table
sysha_type
sysha_workload
sysipl
syslocks
syslogs
syslogfil table
sysmgminfo
sysnetclienttype
sysnetglobal
sysnetworkio table
sysonlinelog
sysprofile
sysproxyagents
sysproxydistributors
sysproxysessions table
sysproxytxnops table
sysproxytxns table
sysptnhdr
sysptprof table
sysrepevtreg table
sysrepstats table
User interface for sysrepstats and sysrepevtreg tables
sysrsslog
sysscblst
syssscelem
syssesappinfo
syssesprof
syssessions
syssmx
syssmxses
syssqexplain table
syssqltrace
syssqltrace_hvar
syssqltrace_info
syssqltrace_iter
syssrcrss
syssrcsds
systabnames
systhreads
systrgrss
systrgsds
sysvpprof
The SMI Tables Map
Information from onstat in the SMI Tables
The sysadmin Database
The Scheduler tables
The ph_task Table
The ph_run Table
The ph_group Table
The ph_alert Table
The ph_threshold Table
The results tables
The command_history table
The storagepool table
The tenant table
Disk Structures and Storage
Dbspace Structure and Storage
Structure of the Root Dbspace
Reserved Pages
Structure of a Regular Dbspace
Structure of an Additional Dbspace Chunk
Structure of a Mirror Chunk
Structure of the Chunk Free-List Page
Structure of the Tblspace Tblspace
Tblspace tblspace entries
Tblspace Numbers
Structure of the Database Tblspace
Database Tblspace Entries
Structure and Allocation of an Extent
Extent Structure
Extent size
Page Types Within a Table Extent
Page Types Within an Index Extent
Next-Extent Allocation
Next-Extent Size
Extent size doubling
Lack of Contiguous Space
Merge of Extents for the Same Table
Structure and Storage of a Dbspace Page
Rows in Nonfragmented Tables
Definition of Rowid
Use of Rowids
Rows in Fragmented Tables
Access to Data in Fragmented Tables with Rowid
Recommendations on Use of Rowid
Data-Row Format and Storage
Storage of Row
Location of Rows
Page Compression
Structure of Fragmented Tables
Structure of B-Tree Index Pages
Definition of B-tree terms
Logical Storage of Indexes
Creation of Root and Leaf Nodes
Creation of branch nodes
Duplicate Key Values
Key-Value Locking
Adjacent Key Locking
Freed Index Pages
Filling Indexes
Calculating the Length of Index Items
Functional Indexes
Structure of R-Tree Index Pages
Storage of Simple Large Objects
Structure of a Blobspace
Structure of a Dbspace Blobpage
Simple-Large-Object Storage and the Descriptor
Creation of Simple Large Objects
Deletion or Insertion of Simple Large Objects
Size Limits for Simple Large Objects
Blobspace Page Types
Structure of a Blobspace Blobpage
Sbspace Structure
Structure of the metadata area
Sbpage Structure
Time Stamps
Database and Table Creation: What Happens on Disk
Database Creation
Disk-Space Allocation for System Catalog Tables
Tracking of System Catalog Tables
Table Creation
Disk-Space Allocation
Entry in the Tblspace Tblspace
Entries in the System Catalog Tables
Creation of a Temporary Table
Interpreting Logical-Log Records
About Logical-Log Records
Transactions That Drop a Table or Index
Transactions That Are Rolled Back
Checkpoints with Active Transactions
Distributed Transactions
Logical-Log Record Structure
Logical-Log Record Header
Logical-log record types and additional columns
Log Record Types for Smart Large Objects
Administrative Utilities
Overview of Utilities
Obtaining utility version information
Setting local environment variables for utilities (UNIX)
The finderr utility
The genoncfg Utility
The oncheck Utility
oncheck Check-and-Repair
What Does Each Option Do?
Using the -y Option to Perform Repairs
Repairing Indexes in Sbspaces and External Spaces
Locking and oncheck
oncheck utility syntax
oncheck -cc and-pc: Check system catalog tables
oncheck -cd and oncheck -cD commands: Check pages
oncheck -ce, -pe: Check the chunk-free list
oncheck -ci and -cI: Check index node links
oncheck -cr and -cR: Check reserved pages
oncheck -cs, -cS, -ps, -pS: Check and display sbspaces
oncheck -pB: Display blobspace statistics
oncheck -pd and pD: Display rows in hexadecimal format
oncheck -pk, -pK, -pl, -pL: Display index information
oncheck -pp and -pP: Display the contents of a logical page
oncheck -pr and pR: Display reserved-page information
oncheck -pt and -pT: Display tblspaces for a Table or Fragment
Turn On Locking with -x
Send Special Arguments to the Access Method with -u
Return Codes on Exit
The onclean utility
The onshutdown script
The oncmsm utility
The onconfig_diff utility
The ondblog utility
The oninit utility
The -FILE option
Return codes for the oninit utility
The onkstash Utility
The onkstore Utility
Create a Keystore with onkstore
Creating an AWS type keystore
Creating an Azure type keystore
Creating a KMIP type keystore
Verifying a Keystore File
Changing the Password for a Keystore File
Converting a Keystore File
List the contents of a Keystore File
The onlog utility
The onmode utility
onmode command syntax
onmode -a: Add a shared-memory segment
onmode -BC: Allow large chunk mode
onmode -c: Force a checkpoint
onmode -C: Control the B-tree scanner
onmode -cache surrogates: Cache the allowed.surrogates file
onmode -d: Set data-replication types
onmode -d: Set High Availability server characteristics
onmode -d command: Replicate an index with data-replication
onmode -D, -M, -Q, -S: Change decision-support parameters
onmode -e: Change usage of the SQL statement cache
onmode -F: Free unused memory segments
onmode -h: Update sqlhosts caches
onmode -I: Control diagnostics collection
onmode -k, -m, -s, -u, -j: Change database server mode
Taking the Database Server to Offline Mode with the -k Option
Bringing the Database Server Online with the -m Option
Shutting Down the Database Server Gracefully with the -s Option
Shutting Down the Database Server Immediately with the -u Option
Changing the Database Server to Administration Mode with the -j Option
onmode -l: Switch the logical-log file
onmode -n, -r: Change shared-memory residency
onmode -O: Override ONDBSPACEDOWN WAIT mode
onmode -p: Add or drop virtual processors
Rules for adding and dropping virtual processors
Monitoring poll threads with onstat
onmode -P: Start, stop, or restart a listen thread dynamically
onmode -R: Regenerate .infos.dbservername File
onmode -W: Change settings for the SQL statement cache
SQL statement cache examples
onmode -we: Export a file that contains current configuration parameters
onmode -wf, -wm: Dynamically change certain configuration parameters
onmode -wm: Change LRU tuning status
onmode -wi: Import a configuration parameter file
onmode -Y: Dynamically change SET EXPLAIN
onmode -z: Kill a database server session
onmode -Z: Kill a distributed transaction
The onparams Utility
onparams syntax
onparams -a -d dbspace: Add a logical-log file
onparams -d -l lognum: Drop a logical-log file
onparams -p: Change physical-log parameters
Backing Up After You Change the Physical-Log Size or Location
Changing the Size of the Physical Log and Using Non-Default Page Sizes
onparams -b: Add a buffer pool
Examples of onparams Commands
The onpassword utility
The ifxclone utility
The onspaces utility
onspaces syntax
onspaces -a: Add a chunk to a dbspace or blobspace
onspaces -a: Add a chunk to an sbspace
onspaces -c -b: Create a blobspace
onspaces -c -d: Create a dbspace
onspaces -c -P: Create a plogspace
onspaces -c -S: Create an sbspace
Creating a Temporary Sbspace with the -t Option
Creating an Sbspace with the -Df option
Changing the -Df Settings
Using the onspaces -g option
onspaces -c -x: Create an extspace
onspaces -ch: Change sbspace default specifications
onspaces -cl: Clean up stray smart large objects in sbspaces
onspaces -d: Drop a chunk in a dbspace, blobspace, or sbspace
onspaces -d: Drop a space
onspaces -f: Specify DATASKIP parameter
onspaces -m: Start mirroring
Using a File to Specify Chunk-Location Information with the -f Option
onspaces -r: Stop mirroring
onspaces -ren: Rename a dbspace, blobspace, sbspace, or extspace
Renaming a dbspace, blobspace, sbspace, or extspace when Enterprise Replication is active
Performing an Archive after Renaming a Space
onspaces -s: Change status of a mirrored chunk
Avoid overwriting a chunk
The onstat utility
onstat Utility Commands Sorted by Functional Category
Monitor the database server status
onstat command syntax
onstat command: Equivalent to the onstat -pu command
onstat - command: Print output header
onstat -- command: Print onstat options and functions
Running onstat Commands on a Shared Memory Dump File
onstat -a command: Print overall status of the database server
onstat -b command: Print buffer information for buffers in use
onstat -B command: Prints information about used buffers
onstat -c command: Print ONCONFIG file contents
onstat -C command: Print B-tree scanner information
onstat -d command: Print chunk information
onstat -D command: Print page-read and page-write information
onstat -f command: Print dbspace information affected by dataskip
onstat -F command: Print counts
onstat -g monitoring options
onstat -g act command: Print active threads
onstat -g afr command: Print allocated memory fragments
onstat -g all command: Print diagnostic information
onstat -g aqt command: Print data mart and accelerated query table information
onstat -g arc command: Print archive status
onstat -g ath command: Print information about all threads
onstat -g bth and -g BTH: Print blocked and waiting threads
onstat -g buf command: Print buffer pool profile information
onstat -g cac command: Print information about caches
onstat -g ckp command: Print checkpoint history and configuration recommendations
onstat -g cfg command: Print the current values of configuration parameters
onstat -g cluster command: Print high-availability cluster information
onstat -g cmsm command: Print Connection Manager information
onstat -g con command: Print condition and thread information
onstat -g cpu: Print runtime statistics
onstat -g dbc command: Print dbScheduler and dbWorker thread statistics
onstat -g defragment command: Print defragment partition extents
onstat -g dic command: Print table information
onstat -g dis command: Print database server information
onstat -g dll command: Print dynamic link library file list
onstat -g dmp command: Print raw memory
onstat -g dri command: Print high-availability data replication information
onstat -g dsc command: Print distribution cache information
onstat -g dsk command: Print the progress of the currently running compression operation
onstat -g env command: Print environment variable values
onstat -g ffr command: Print free fragments
onstat -g glo command: Print global multithreading information
onstat -g his command: Print SQL trace information
onstat -g ioa command: Print combined onstat -g information
onstat -g iob command: Print big buffer use summary
onstat -g iof command: Print asynchronous I/O statistics
onstat -g iog command: Print AIO global information
onstat -g ioq command: Print I/O queue information
onstat -g ipl command: Print index page logging status information
onstat -g iov command: Print AIO VP statistics
onstat -g lap command: Print light appends status information
onstat -g laq command: Print secondary server queues
onstat -g lmm command: Print low memory management information
onstat -g lmx command: Print all locked mutexes
onstat -g lsc command: Print active light scan status (deprecated)
onstat -g mem command: Print pool memory statistics
onstat -g mgm command: Print MGM resource information
onstat -g nbm command: Print a block bit map
onstat -g nsc command: Print current shared memory connection information
onstat -g nsd command: Print poll threads shared-memory data
onstat -g nss command: Print shared memory network connections status
onstat -g ntd command: Print network statistics
onstat -g ntm command: Print network mail statistics
onstat -g ntt command: Print network user times
onstat -g ntu command: Print network user statistics
onstat -g opn command: Print open partitions
onstat -g osi: Print operating system information
onstat -g pd command: Print push data session-related information
onstat -g pd event command: Print push data event-related information
onstat -g pfsc command: Print partition free space cache information
onstat -g pos command: Print file values
onstat -g ppd command: Print partition compression dictionary information
onstat -g ppf command: Print partition profiles
onstat -g pqs command: Print operators for all SQL queries
onstat -g prc command: Print sessions using UDR or SPL routines
onstat -g proxy command: Print proxy distributor information
onstat -g qst command: Print wait options for mutex and condition queues
onstat -g rah command: Print read-ahead request statistics
onstat -g rbm command: Print a block map of shared memory
onstat -g rea command: Print ready threads
onstat -g rss command: Print RS secondary server information
onstat -g rwm command: Print read and write mutexes
onstat -g sch command: Print VP information
onstat -g scn command: Print scan information
onstat -g sds command: Print SD secondary server information
onstat -g seg command: Print shared memory segment statistics
onstat -g ses command: Print session-related information
onstat -g shard command: Print information about the shard definition
onstat -g sle command: Print all sleeping threads
onstat -g smb command: Print sbspaces information
onstat -g smx command: Print multiplexer group information
onstat -g spi command: Print spin locks with long spins
onstat -g sql command: Print SQL-related session information
onstat -g spf: Print prepared statement profiles
onstat -g src command: Patterns in shared memory
onstat -g ssc command: Print SQL statement occurrences
onstat -g stk command: Print thread stack
onstat -g stm command: Print SQL statement memory usage
onstat -g stq command: Print queue information
onstat -g sts command: Print stack usage for each thread
onstat -g sym command: Print symbol table information for the oninit utility
onstat -g top command: Print top consumers of resources
onstat -g tpf command: Print thread profiles
onstat -g ufr command: Print memory pool fragments
onstat -g vpcache command: Print CPU VP and tenant VP private memory cache statistics
onstat -g wai command: Print wait queue thread list
onstat -g wmx command: Print all mutexes with waiters
onstat -g wst command: Print wait statistics for threads
onstat -G command: Print TP/XA transaction information
onstat -h command: Print buffer header hash chain information
onstat -i command: Initiate interactive mode
onstat -j command: Provide onpload status information
onstat -k command: Print active lock information
onstat -l command: Print physical and logical log information
onstat -L command: Print the number of free locks
onstat -m command: Print recent system message log information
onstat -o command: Output shared memory contents to a file
onstat -p command: Print profile counts
onstat -P command: Print partition information
onstat -r command: Repeatedly print selected statistics
onstat -R command: Print LRU, FLRU, and MLRU queue information
onstat -s command: Print latch information
onstat -t and onstat -T commands: Print tblspace information
onstat -u command: Print user activity profile
onstat -x command: Print database server transaction information
Determine the position of a logical-log record
Determine the mode of a global transaction
onstat -X command: Print thread information
onstat -z command: Clear statistics
Return codes on exiting the onstat utility
SQL Administration API
SQL Administration API Functions
SQL Administration API Overview
admin() and task() Function Syntax Behavior
admin() and task() Argument Size Specifications
admin() and task() Function Return Codes
SQL administration API portal: Arguments by privilege groups
add bufferpool argument: Add a buffer pool (SQL administration API)
add chunk argument: Add a new chunk (SQL administration API)
add log argument: Add a new logical log (SQL administration API)
add memory argument: Increase shared memory (SQL administration API)
add mirror argument: Add a mirror chunk (SQL administration API)
alter chunk argument: Change chunk status to online or offline (SQL administration API)
alter logmode argument: Change the database logging mode (SQL administration API)
alter plog argument: Change the physical log (SQL administration API)
archive fake argument: Perform an unrecorded backup (SQL administration API)
autolocate database add argument: Add a dbspace to the dbspace list (SQL administration API)
autolocate database anywhere argument: Add all dbspaces to the dbspace list (SQL administration API)
autolocate database argument: Specify dbspaces for automatic location and fragmentation (SQL administration API)
autolocate database off argument: Disable automatic fragmentation for a database (SQL administration API)
autolocate database remove argument: Remove a dbspace from the dbspace list (SQL administration API)
cdr argument: Administer Enterprise Replication (SQL administration API)
cdr add trustedhost argument: Add trusted hosts (SQL administration API)
cdr autoconfig serv argument: Autoconfigure connectivity and replication (SQL administration API)
cdr list trustedhost argument: List trusted hosts (SQL administration API)
cdr remove trustedhost argument: Remove trusted hosts (SQL administration API)
check data argument: Check data consistency (SQL administration API)
check extents argument: Check extent consistency (SQL administration API)
check partition argument: Check partition consistency (SQL administration API)
checkpoint argument: Force a checkpoint (SQL administration API)
clean sbspace argument: Release unreferenced smart large objects (SQL administration API)
create blobspace argument: Create a blobspace (SQL administration API)
create blobspace from storagepool argument: Create a blobspace from the storage pool (SQL administration API)
create chunk argument: Create a chunk (SQL administration API)
create chunk from storagepool argument: Create a chunk from the storage pool (SQL administration API)
create database argument: Create a database (SQL administration API)
create dbaccessdemo argument: Create the demonstration database (SQL administration API)
create dbspace argument: Create a dbspace (SQL administration API)
create dbspace from storagepool argument: Create a dbspace from the storage pool (SQL administration API)
create plogspace: Create a plogspace (SQL administration API)
create sbspace argument: Create an sbspace (SQL administration API)
create sbspace from storagepool argument: Create an sbspace from the storage pool (SQL administration API)
create sbspace with accesstime argument: Create an sbspace that tracks access time (SQL administration API)
create sbspace with log argument: Create an sbspace with transaction logging (SQL administration API)
create tempdbspace argument: Create a temporary dbspace (SQL administration API)
create tempdbspace from storagepool argument: Create a temporary dbspace from the storage pool (SQL administration API)
create tempsbspace argument: Create a temporary sbspace (SQL administration API)
create tempsbspace from storagepool argument: Create a temporary sbspace from the storage pool (SQL administration API)
defragment argument: Dynamically defragment partition extents (SQL administration API)
drop blobspace argument: Drop a blobspace (SQL administration API)
drop blobspace to storagepool argument: Return space from an empty blobspace to the storage pool (SQL administration API)
drop chunk argument: Drop a chunk (SQL administration API)
drop chunk to storagepool argument: Return space from an empty chunk to the storage pool (SQL administration API)
drop database argument: Drop a database (SQL administration API)
drop dbspace argument: Drop a dbspace (SQL administration API)
drop dbspace to storagepool argument: Return space from an empty dbspace to the storage pool (SQL administration API)
drop log argument: Drop a logical log (SQL administration API)
drop plogspace: Drop the plogspace (SQL administration API)
drop sbspace argument: Drop an sbspace (SQL administration API)
drop sbspace to storagepool argument: Return space from an empty sbspace to the storage pool (SQL administration API)
drop tempdbspace argument: Drop a temporary dbspace (SQL administration API)
drop tempdbspace to storagepool argument: Return space from an empty temporary dbspace to the storage pool (SQL administration API)
drop tempsbspace to storagepool argument: Return space from an empty temporary sbspace to the storage pool (SQL administration API)
export config argument: Export configuration parameter values (SQL administration API)
file status argument: Display the status of a message log file (SQL administration API)
grant admin argument: Grant privileges to run SQL administration API commands
ha make primary argument: Change the mode of a secondary server (SQL administration API)
ha rss argument: Create an RS secondary server (SQL administration API)
ha rss add argument: Add an RS secondary server to a primary server (SQL administration API)
ha rss change argument: Change the password of an RS secondary server (SQL administration API)
ha rss delete argument: Delete an RS secondary server (SQL administration API)
ha sds clear argument: Stop shared-disk replication (SQL administration API)
ha sds primary argument: Convert an SD secondary server to a primary server (SQL administration API)
ha sds set argument: Create a shared-disk primary server (SQL administration API)
ha set idxauto argument: Replicate indexes to secondary servers (SQL administration API)
ha set ipl argument: Log index builds on the primary server (SQL administration API)
ha set primary argument: Define an HDR primary server (SQL administration API)
ha set secondary argument: Define an HDR secondary server (SQL administration API)
ha set standard argument: Convert an HDR server into a standard server (SQL administration API)
ha set timeout argument: Change SD secondary server timeout (SQL administration API)
import config argument: Import configuration parameter values (SQL administration API)
index compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API)
index estimate_compression argument: Estimate index compression (SQL administration API)
master_key reset argument: Change the keystore password (SQL administration API)
message log delete argument: Delete a message log file (SQL administration API)
message log rotate argument: Rotate the message log file (SQL administration API)
message log truncate argument: Delete the contents of a message log file (SQL administration API)
modify chunk extend argument: Extend the size of a chunk (SQL administration API)
modify chunk extendable argument: Mark a chunk as extendable (SQL administration API)
modify chunk extendable off argument: Mark a chunk as not extendable (SQL administration API)
modify chunk swap_mirror argument: Switch primary and mirror chunk files without any downtime(SQL administration API)
modify space swap_mirror argument: Switch all primary and mirror chunk files for a space without any downtime (SQL administration API)
modify config arguments: Modify configuration parameters (SQL administration API)
modify space expand argument: Expand the size of a space (SQL administration API)
modify space sp_sizes argument: Modify sizes of an extendable storage space (SQL administration API)
onbar argument: Backup the storage spaces (SQL administration API)
onmode and a arguments: Add a shared-memory segment (SQL administration API)
onmode and c arguments: Force a checkpoint (SQL administration API)
onmode and C arguments: Control the B-tree scanner (SQL administration API)
onmode and d arguments: Set data-replication types (SQL administration API)
onmode and D arguments: Set PDQ priority (SQL administration API)
onmode and e arguments: Change usage of the SQL statement cache (SQL administration API)
onmode and F arguments: Free unused memory segments (SQL administration API)
onmode and h arguments: Update sqlhosts caches (SQL administration API)
onmode and j arguments: Switch the database server to administration mode (SQL administration API)
onmode and l arguments: Switch to the next logical log (SQL administration API)
onmode and m arguments: Switch to multi-user mode (SQL administration API)
onmode and M arguments: Temporarily change decision-support memory (SQL administration API)
onmode and n arguments: Unlock resident memory (SQL administration API)
onmode and O arguments: Mark a disabled dbspace as down (SQL administration API)
onmode and p arguments: Add or remove virtual processors (SQL administration API)
onmode and Q arguments: Set maximum number for decision-support queries (SQL administration API)
onmode and r arguments: Force residency of shared memory (SQL administration API)
onmode and S arguments: Set maximum number of decision-support scans (SQL administration API)
onmode and W arguments: Reset statement cache attributes (SQL administration API)
onmode and wf arguments: Permanently update a configuration parameter (SQL administration API)
onmode and wm arguments: Temporarily update a configuration parameter (SQL administration API)
onmode, wm, and AUTO_LRU_TUNING arguments: Change LRU tuning status (SQL administration API)
onmode and Y arguments: Change query plan measurements for a session (SQL administration API)
onmode and z arguments: Terminate a user session (SQL administration API)
onmode and Z arguments: Terminate a distributed transaction (SQL administration API)
onsmsync argument: Synchronize with the storage manager catalog (SQL administration API)
onstat argument: Monitor the database server (SQL administration API)
ontape archive argument: Backup the data on your database (SQL administration API)
print error argument: Print an error message (SQL administration API)
print file info argument: Display directory or file information (SQL administration API)
print partition argument: Print partition information (SQL administration API)
rename space argument: Rename a storage space (SQL administration API)
reset config argument: Revert configuration parameter value (SQL administration API)
reset config all argument: Revert all dynamically updatable configuration parameter values (SQL administration API)
reset sysadmin argument: Move the sysadmin database (SQL administration API)
restart listen argument: Stop and start a listen thread dynamically (SQL administration API)
revoke admin argument: Revoke privileges to run SQL administration API commands
scheduler argument: Stop or start the scheduler (SQL administration API)
scheduler lmm enable argument: Specify automatic low memory management settings (SQL administration API)
scheduler lmm disable argument: Stop automatic low memory management (SQL administration API)
set chunk argument: Change the status of a chunk (SQL administration API)
set dataskip argument: Start or stop skipping a dbspace (SQL administration API)
set index compression argument: Change index page compression (SQL administration API)
set onconfig memory argument: Temporarily change a configuration parameter (SQL administration API)
set onconfig permanent argument: Permanently change a configuration parameter (SQL administration API)
set sbspace accesstime argument: Control access time tracking (SQL administration API)
set sbspace avg_lo_size argument: Set the average size of smart large objects (SQL administration API)
set sbspace logging argument: Change the logging of an sbspace (SQL administration API)
set sql tracing argument: Set global SQL tracing (SQL administration API)
set sql tracing database argument: Change database tracing (SQL administration API)
set sql tracing session argument: Control tracing for a session (SQL administration API)
set sql tracing user argument: Control tracing for users (SQL administration API)
set sql user tracing argument: Set global SQL tracing for a user session (SQL administration API)
start json listener argument: Start the MongoDB API wire listener (deprecated)
start listen argument: Start a listen thread dynamically (SQL administration API)
start mirroring argument: Starts storage space mirroring (SQL administration API)
statement cache enable argument: Enable the SQL statement cache (SQL administration API)
statement cache flush argument: Flush the SQL statement cache (SQL administration API)
statement cache hits argument: Specify the number of hits in the SQL statement cache (SQL administration API)
statement cache nolimit argument: Control whether to insert qualified statements into the SQL statement cache (SQL administration API)
statement cache off argument: Disable the SQL statement cache (SQL administration API)
statement cache save argument: Save the SQL statement cache (SQL administration API)
statement cache restore argument: Restore the SQL statement cache (SQL administration API)
stop json listener: Stop the wire listener (deprecated)
stop listen argument: Stop a listen thread dynamically (SQL administration API)
stop mirroring argument: Stops storage space mirroring (SQL administration API)
storagepool add argument: Add a storage pool entry (SQL administration API)
storagepool delete argument: Delete one storage pool entry (SQL administration API)
storagepool modify argument: Modify a storage pool entry (SQL administration API)
storagepool purge argument: Delete storage pool entries (SQL administration API)
Table and fragment pfsc_boost argument: Enable or disable a boosted partition free space cache (PFSC)
Table and fragment compress and uncompress operations (SQL administration API)
table or fragment arguments: Compress data and optimize storage (SQL administration API)
Output of the estimate compression operation (SQL administration API)
purge compression dictionary arguments: Remove compression dictionaries (SQL administration API)
tenant create argument: Create a tenant database (SQL Administration API)
tenant drop argument: Drop a tenant database (SQL Administration API)
tenant update argument: Modify tenant database properties (SQL Administration API)
Appendixes
Database server files
Troubleshooting errors
Collecting Diagnostics using onmode -I
Creating Tracepoints
Collecting data with the ifxcollect tool
Event Alarms
Using ALARMPROGRAM to Capture Events
Setting ALRM_ALL_EVENTS
Writing Your Own Alarm Script
Customizing the ALARMPROGRAM Scripts
Precautions for Foreground Operations in Alarm Scripts
Interpreting event alarm messages
Events in the ph_alert Table
Event Alarm Parameters
Event alarm IDs
Severity 5 event alarms
Connection Manager event alarm IDs
Messages in the database server log
How the Messages Are Ordered in This Chapter
How to view these messages
Message Categories
Messages: A-B
Aborting Long Transaction: tx 0xn.
Affinitied VP mm to phys proc nn.
Affinity not enabled for this server.
Assert Failed: Error from SBSpace cleanup thread.
Assert Failed: Short description of what failed Who: Description of user/session/thread running at the time Result: State of the affected database server entity Action: What action the database administrator should take See Also: DUMPDIR/af.uniqid containing more diagnostics.
Begin re-creating indexes deferred during recovery.
Building 'sysmaster' database requires ~mm pages of logical log. Currently there are nn pages available. Prepare to back up your logs soon.
Building 'sysmaster' database...
Messages: C
Cannot Allocate Physical-log File, mm wanted, nn available.
Cannot alter a table which has associated violations table.
Cannot change to mode.
Cannot Commit Partially Complete Transactions.
Cannot create a user-defined VP class with 'SINGLE_CPU_VP' non-zero.
Cannot create violations/diagnostics table.
Cannot insert from the violations table to the target table.
Cannot modify/drop a violations/diagnostics table.
Cannot Open Dbspace nnn.
Cannot Open Logical Log.
Cannot Open Mirror Chunk pathname, errorno = nn.
Cannot Open Primary Chunk pathname, errorno = nnn.
Cannot Open Primary Chunk chunkname.
Cannot open sysams in database name, iserrno number.
Cannot open sysdistrib in database name, iserrno number.
Cannot open system_table in database name, iserrno number.
Cannot open systrigbody in database name, iserrno number.
Cannot open systriggers in database name, iserrno number.
Cannot open sysxtdtypes in database name, iserrno number.
Cannot Perform Checkpoint, shut system down.
Cannot Restore to Checkpoint.
Cannot Rollback Incomplete Transactions.
Cannot update pagezero.
Cannot update syscasts in database name. Iserrno number.
Can’t affinity VP mm to phys proc nn.
Changing the sbspace minimum extent value: old value value1, new value value2.
Checkpoint blocked by down space, waiting for override or shutdown.
Checkpoint Completed: duration was n seconds.
Checkpoint Page Write Error.
Checkpoint Record Not Found in Logical Log.
Chunk chunkname added to space spacename.
Chunk chunkname dropped from space spacename.
Chunk number nn pathname -- Offline.
Chunk number nn pathname -- Online.
The chunk pathname must have READ/WRITE permissions for owner and group.
The chunk pathname must have owner-ID and group-ID set to informix.
The chunk pathname will not fit in the space specified.
Cleaning stray LOs in sbspace sbspacename.
Completed re-creating indexes.
Configuration has been grown to handle up to integer chunks.
Configuration has been grown to handle up to integer dbslices.
Configuration has been grown to handle up to integer dbspaces.
Continuing Long Transaction (for COMMIT): tx 0xn.
Could not disable priority aging: errno = number.
Could not fork a virtual processor: errno = number.
Create_vp: cannot allocate memory.
Messages: D-E-F
Dataskip is OFF for all dbspaces.
Dataskip is ON for all dbspaces.
Dataskip is ON for dbspaces: dbspacelist.
Dataskip will be turned {ON|OFF} for dbspacename.
DBSERVERALIASES exceeded the maximum limit of 32
DBSPACETEMP internal list not initialized, using default.
The DBspace/BLOBspace spacename is now mirrored.
The DBspace/BLOBspace spacename is no longer mirrored.
devname: write failed, file system is full.
Dropping temporary tblspace 0xn, recovering nn pages.
Dynamically allocated new shared memory segment (size nnnn).
ERROR: NO "wait for" locks in Critical Section.
Error building sysmaster database. See outfile.
Error in dropping system defined type.
Error in renaming systdist.
Error removing sysdistrib row for tabid = tabid, colid = colid in database name. iserrno = number
Error writing pathname errno = number.
Error writing shmem to file filename (error). Unable to create output file filename errno=mm.Error writing filename errno=nn.
Fail to extend physical log space.
Fatal error initializing CWD string. Check permissions on current working directory. Group groupname must have at least execute permission on '.'.
Fragments dbspacename1 dbspacename2 of table tablename set to non-resident.
Forced-resident shared memory not available.
Freed mm shared-memory segment(s) number bytes.
Messages: G-H-I
gcore pid; mv core.pid dir/core.pid.ABORT.
I/O function chunk mm, pagenum nn, pagecnt aa --> errno = bb.
I/O error, primary/mirror Chunk pathname -- Offline (sanity).
Informix database_server Initialized - Complete Disk Initialized.
Informix database_server Initialized - Shared Memory Initialized.
Informix database_server Stopped.
In-Place Alter Table. Perform EXECUTE FUNCTION sysadmin:task('table update_ipa', 'table_name','database');
ERROR: Insufficient available disk in the root dbspace to increase the entire Configuration save area.
Insufficient available disk in the root dbspace for the CM save area. Increase the size of the root dbspace in the ONCONFIG file and reinitialize the server.
Internal overflow of shmid's, increase system max shared memory segment size.
Messages: J-K-L-M
Listener-thread err = error_number: error_message.
Lock table overflow - user id mm session id nn.
Logical-log File not found.
Logical Log nn Complete.
Logical logging vberror for type:subtype in (failed_system).
Log Record: log = ll, pos = 0xn, type = type:subtype(snum), trans = xx
Log record (type:subtype) at log nn, 0xn was not undone.
Log record (type:subtype) failed, partnum pnum row rid iserrno num.
Log record (type:subtype) in log nn, offset 0xn was not rolled back.
Logical Recovery allocating nn worker threads thread_type.
Logical Recovery Started.
Maximum server connections number.
Memory allocation error.
Mirror Chunk chunkname added to space spacename. Perform manual recovery.
Mixed transaction result. (pid=nn user=userid).
mt_shm_free_pool: pool 0xn has blocks still used (id nn).
mt_shm_init: can’t create resident/virtual segment.
mt_shm_remove: WARNING: may not have removed all/correct segments.
Messages: N-O-P
Newly specified value of value for the pagesize in the configuration file does not match older value of value. Using the older value.
Not enough main memory.
Not enough logical-log files, Increase LOGFILES.
The number of configured inline poll threads exceeds the number of CPU virtual processors.
onconfig parameter parameter modified from old_value to new_value.
oninit: Cannot have SINGLE_CPU_VP non-zero and number of CPU VPs greater than 1.
oninit: Cannot have SINGLE_CPU_VP non-zero and user-defined VP classes.
oninit: Fatal error in initializing ASF with 'ASF_INIT_DATA' flags asfcode = '25507'.
Cannot alter a table which has associated violations table.
oninit: Too many VPCLASS parameters specified.
oninit: VPCLASS classname bad affinity specification.
oninit: VPCLASS classname duplicate class name.
oninit: VPCLASS classname illegal option.
oninit: VPCLASS classname maximum number of VPs is out of the range 0-10000.
oninit: VPCLASS classname name is too long. Maximum length is maxlength.
oninit: VPCLASS classname number of VPs is greater than the maximum specified.
oninit: VPCLASS classname number of VPs is out of the range 0-10000.
onmode: VPCLASS classname name is too long. Maximum length is maxlength.
Online Mode.
onspaces: unable to reset dataskip.
Open transaction detected when changing log versions.
Out of message shared memory.
Out of resident shared memory.
Out of virtual shared memory.
PANIC: Attempting to bring system down.
Participant site database_server heuristically rolled back.
Physical recovery complete: number pages examined, number pages restored.
Physical recovery started at page (chunk:offset).
Portions of partition partnum of table tablename in database dbname were not logged. This partition cannot be rolled forward.
Possible mixed transaction result.
Prepared participant site server_name did not respond.
Prepared participant site server_name not responding.
Messages: Q-R-S
Quiescent Mode.
Read failed. Table name, Database name, iserrno = number
Recovery Mode.
Recreating index: 'dbsname:"owner".tabname-idxname'.
Rollforward of log record failed, iserrno = nn.
Root chunk is full and no additional pages could be allocated to chunk descriptor page.
scan_logundo: subsys ss, type tt, iserrno ee.
Session completed abnormally. Committing tx id 0xm, flags 0xn.
Session completed abnormally. Rolling back tx id 0xm, flags 0xn.
semctl: errno = nn.
semget: errno = nn.
shmat: some_string os_errno: os_err_text.
shmctl: errno = nn.
shmdt: errno = nn.
shmem sent to filename.
shmget: some_str os_errno: key shmkey: some_string.
Shutdown (onmode -k) or override (onmode -O).
Shutdown Mode.
Space spacename added.
Space spacename dropped.
Space spacename -- Recovery Begins(addr).
Space spacename -- Recovery Complete(addr).
Space spacename -- Recovery Failed(addr).
sysmaster database built successfully.
Successfully extend physical log space
Messages: T-U-V
This ddl operation is not allowed due to deferred constraints pending on this table and dependent tables.
This type of space does not accept log files.
TIMER VP: Could not redirect I/O in initialization, errno = nn.
Too Many Active Transactions.
Too many violations.
Transaction Not Found.
Transaction heuristically rolled back.
Transaction table overflow - user id nn, process id nn.
Unable to create output file filename errno = nn.
Unable to extend nn reserved pages for purpose in root chunk.
Unable to start SQL engine.
Unable to open tblspace nn, iserrno = nn.
The value of pagesize pagesize specified in the config file is not a valid pagesize. Use 2048, 4096 or 8192 as the value for PAGESIZE in the onconfig file and restart the server.
Violations table is not started for the target table.
Violations table reversion test completed successfully.
Violations table reversion test failed.
Violations table reversion test start.
Violations tables still exist.
Virtual processor limit exceeded.
VPCLASS classname name is too long. Maximum length is maxlength.
VPCLASS classname duplicate class name.
VPCLASS classname Not enough physical procs for affinity.
Messages: W-X-Y-Z
WARNING: aio_wait: errno = nn.
WARNING: Buffer pool size may cause database server to get into a locked state. Recommended minimum buffer pool size is num times maximum concurrent user threads.
warning: Chunk time stamps are invalid.
Warning: name_old is a deprecated onconfig parameter. Use name_new instead. See the release notes and the Informix Administrator's Reference for more information.
Warning: name_old is a deprecated onconfig parameter. Use name_new instead.
Warning: Unable to allocate requested big buffer of size nn.
You are turning off smart large object logging.
Messages: Symbols
HH:MM:SS Informix database server Version R.VV.PPPPP Software Serial Number RDS#XYYYYYY.
argument: invalid argument.
function_name: cannot allocate memory.
Conversion and reversion error messages
Cannot revert new fragment expression for index index, tabid id.
Cannot revert new table fragment expression for table with id id.
The conversion of the database name has failed.
Database name is not revertible...
Database name: Must drop trigger (id = id_number) before attempting reversion.
The dummy updates failed while converting database name. This may imply data corruption in the database. If so, restore the original database with the tape backup. For more information, see output_file.
Error in slow altering a system table.
Internal server error.
Must drop long identifiers in table name in database name
Must drop new database (name) before attempting reversion. Iserrno error_number
Must drop new user defined statistics in database name, iserrno number
The pload database contains load/unload jobs referring to long table names, column names, or database names. These jobs will not work as expected until they are redefined.
Reversion canceled.
There is a semi-detached index in this table, which cannot be reverted.
WARNING: Target server version must have a certified Storage Manager installed after conversion/reversion and before bringing up server.
Conversion and Reversion Messages for Enterprise Replication
CDR reversion test failed; for details look in $INFORMIXDIR/etc/revtestcdr.out.
Enterprise Replication is not ready for conversion. The Control and TRG send queues should be empty for conversion/reversion to proceed.
Enterprise Replication should be in a stopped state for conversion/reversion to proceed.
...‘syscdr’ reversion failed; for details look in $INFORMIXDIR/etc/revcdr.out.
'syscdr' conversion failed. For details, look in $INFORMIXDIR/etc/concdr.out.
Syscdr should NOT contain new replicate sets for reversion to succeed.
Syscdr should not contain replicates defined with the --floatieee option for reversion to succeed.
Dynamic Log Messages
Dynamically added log file logid to DBspace dbspace_number.
Log file logid added to DBspace dbspace_number.
Log file number logid has been dropped from DBspace dbspace_number.
Log file logid has been pre-dropped.
Pre-dropped log file number logid has been deleted from DBspace dbspace_number.
ALERT: Because the oldest logical log (logid) contains records from an open transaction (transaction_address), the server is attempting to dynamically add a log file. But there is no space available. Please add a DBspace or chunk. Then complete the transaction as soon as possible.
ALERT: The oldest logical log (logid) contains records from an open transaction (transaction_address). Logical logging will remain blocked until a log file is added. Add the log file with the onparams -a command, using the -i (insert) option, as in: onparams -a -d dbspace -s size -i. Then complete the transaction as soon as possible.
Log file logid has been pre-dropped. It will be deleted from the log list and its space can be reused once you take level-0 archives of all BLOBspaces, Smart BLOBspaces and non-temporary DBspaces.
Sbspace Metadata Messages
Allocated number pages to Metadata from chunk number.
Allocated number pages to Userdata from chunk number.
Freeing reserved space from chunk number to Metadata.
Freeing reserved space from chunk number to Userdata.
Truncate Table Messages
The table cannot be truncated if it has an open cursor or dirty readers.
The table cannot be truncated. It has at least one non-empty child table with referential constraints.
Limits in Informix
DB-Access User's Guide
Getting started with DB-Access
Requirements for the Informix server DB-Access utility
Environment variables for DB-Access
Requirements for the Informix Client Software Development Kit DB-Access utility
Demonstration databases
Creating a demonstration database
dbaccessdemo command: Create demonstration databases
Start DB-Access
dbaccess command: Start DB-Access
CONNECTION menu options
DATABASE menu options
QUERY-LANGUAGE menu options
TABLE menu options
Example: Start DB-Access for a database
Example: Run a command file
Example: View the Information Schema
Example: Check for ANSI compliance
Example: Show nonprintable characters in hexadecimal
Run DB-Access in interactive mode without menus
Connect to a database environment in interactive mode
The full-screen menu interface
The Query-language option
SQL editor
A system editor
Statements that the Run option supports
Redirect query results
Choose an existing SQL statement
Save the current SQL statement
Support for SPL Routines
What happens when errors occur
The Database option
List of available databases
Retrieve nondefault locale information
Close a database
The Table option
Display table information
The Connection and Session options
Implicit closures
Appendixes
How to read online help for SQL statements
Demonstration SQL
SQL files for the relational database model
The alt_cat.sql command file
The c_calls.sql command file
The c_cat.sql command file
The c_custom.sql command file
The c_index.sql command file
The c_items.sql command file
The c_manuf.sql command file
The c_orders.sql file
The c_proc.sql command file
The c_state command file
The c_stock.sql command file
The c_stores.sql command file
The c_table.sql command file
The c_trig.sql command file
The c_type.sql command file
The c_view1.sql command file
The c_view2.sql command file
The d_proc.sql command file
The d_trig.sql command file
The d_view.sql command file
The del_stock.sql command file
The ins_table.sql command file
The sel_agg.sql command file
The sel_all.sql command file
The sel_group.sql command file
The sel_join.sql command file
The sel_ojoin1.sql command file
The sel_ojoin2.sql command file
The sel_ojoin3.sql command file
The sel_ojoin4.sql command file
The sel_order.sql command file
The sel_sub.sql command file
The sel_union.sql command file
The upd_table.sql command file
SQL files for the Dimensional Database Model
The createdw.sql file
The loaddw.sql file
User-defined routines for the object-relational database model
High-Performance Loader User's Guide
High-Performance Loader overview
Overview of HPL features
The HPL data-load process
The HPL data-unload process
HPL loading modes
HPL components
The onpload utility
The ipload utility
The onpladm utility
The onpload database
Relationships among the parts of the HPL
Environment variables needed for the HPL
Preparing multiple onpload databases
The PLCONFIG environment variable
The PLOAD_SHMBASE environment variable
Avoid shared-memory collision
Set the PLOAD_SHMBASE environment variable
The PLOAD_LO_PATH environment variable
The PLOAD_SHMAT environment variable
Architecture of the onpload utility
The onpload utility deluxe-mode process
Threads that the onpload utility uses
Threads that the database server uses
The onpload utility express-mode load process
The onpload utility unload process
Examples of loading and unloading jobs using the ipload utility
Prepare to use the ipload utility
Create a file of data
Create a database
The ipload utility
Start the ipload utility
Choose a project
Check the ipload utility default values
Look at the Defaults window
Look at the Machines window
Load Job windows
Load Job Select window
Load Job window
Device-Array windows
Device Array Selection window
Device-Array Definition window
Format windows
Format Views window
Record Formats window
Format-Definition window
Filter, Discard Records, and Logfile text boxes
Map Views window
Creating a map by using Map Views window
Completing the Load Record Maps window
Map-Definition window
Associating each input item with a column of the database table
Load Options window
Running the ipload job
Active Job window
The ipload utility Generate options
Use the information you created with the ipload example
Preparing the Unload Job window
Performing the unload job
The ipload utility windows
The ipload utility GUI or the onpladm command-line interface
Start the ipload utility
The ipload utility GUI
The HPL main window
Initial options on the HPL main window
Options of the HPL main window
Component-Selection windows
Component-Definition windows
Load Job and Unload Job windows
Views windows
Access views windows
Available options in a Views window
Search for a component in a Views window
Expand the view in a Views window
Selection-List windows
Message windows
The HPL ipload utility buttons
The HPL ipload utility toolbar buttons
The Browse button
The Copy button
Copying an existing format into a new format
The Delete button
Deleting an existing format
The Notes button
Creating a note
The Print button
The HPL ipload utility icon buttons
Buttons at the bottom of the HPL ipload utility display
The HPL online help
The UNIX keyboard commands to move the cursor
Define HPL projects
HPL projects
Project organization
Select or create a project with the Projects window
Defining a new project
Selecting a project
Selecting a project for a load or unload job
Selecting a project to edit
Configure the High-Performance Loader
Configure the ipload utility
Select a database server
Selecting a database server
Create the onpload database
Modify the onpload default values
The Defaults window
Specifying the onpload defaults
Selecting a driver
The Drivers window
Adding a custom-driver name
Modify the machine description
The Machines window
Editing the description of a computer
Adding a computer type to the Machines list
Define device arrays
Device arrays
Multiple devices in a device array
The Device-Array Selection window
Creating a device array
Opening an existing device array
The Device-Array Definition window
Adding, editing, and removing devices
Adding devices to the device array
Editing a device in the device array
Deleting a device from the device array
Define formats
Formats
Formats of supported datafile records
Fixed-length records
Creating a fixed format
Data types allowed in a fixed format
Editing a format
Adding a new field description to the format
Inserting a new field into the format
Editing the description of a field
Deleting a field description from the format
Create a fixed format that uses carriage returns
Create a fixed format that includes BYTE or TEXT data
Inline data
Data in a separate file
Create a fixed format that includes Ext type or Simple LO data
Fixed-length data
Inline data
Simple LO data in a separate file
Delimited records
Create a delimited format
Data types allowed in a delimited format
Create a delimited format that includes BYTE or TEXT data
Create a delimited format with extended data types
COBOL records
Create a COBOL format
Picture and usage descriptions
Other formats
Format options
Modifying fixed and COBOL formats
Modifying delimited-format options
Testing the import of a CSV file
Format Views window
Define queries
HPL queries
Creating a query
Using the Column Selection window
Editing the WHERE clause
Editing a query
Exporting and importing queries
Importing a query
Exporting a query
The Database Views window
Define maps
Load and unload maps
The Map-Definition window
The Table and Format panes
Unassigned or multiple-assigned fields and columns
Identical field names and column names
Creating a load map
Unload maps
Creating an unload map
Unload data by using functions
Mapping options
Defining the mapping options
Set the mapping options
Editing options
Using the Delete button
Using the Find button
Using the Specs button
Map Views window
Seeing the load maps of a database
Seeing selected load maps
Define filters
Filters
Example of using filter
Creating a filter
Preparing the filter definition
Modifying a filter
Editing an existing filter
Adding an item to the filter
Inserting an item into the filter sequence
Deleting a filter
Filter views
Filters with code-set conversion (GLS)
Unload data from a database
Unload jobs
Components of the unload job
Choose the database server
Run multiple jobs
The Unload Job windows
Creating an unload job
Run the unload job
Problems during an unload job
Specify to write to the end of the tape
The command-line information
Changing the unload options
Editing an unload job
Generate options for an unload job
Load data to a database table
Load jobs
Components of the load job
Choose the database server
Run multiple jobs
Prepare user privileges and the violations table
Set user constraints
Manage the violations and diagnostics tables
The Load Job windows
Creating a load job
Run the load job
Make a level-0 backup
Problems during a load job
Specify to read to the end of the tape
The command-line information
Changing the load options
Editing a load job
Generate options for a load job
The Generate options of the ipload utility
Overview of the ipload Generate options
Tasks that generate load or unload components
Generate from the Load Job window
Generating a job from the Load Job window
Generate from the Unload Job window
Generating a job that uses a query
Generating a job that unloads an entire table
Generate from the Components menu
The Generate window
Generate group
Format type group
Generating load and unload components
Using the No Conversion Job option
The HPL browsing options
Browsing options
Preview data-file records
Using the Record Browser window
Reviewing data-file records in a selected format
Searching and editing a format
Editing a format
Reviewing records that the conversion rejected
Viewing the violations table
View the status of a load job or unload job
Viewing the log file
Sample log file
Manage the High-Performance Loader
Manage modes, errors, and performance
HPL modes
The HPL deluxe mode
The HPL express mode
How the express and deluxe modes work
Foreign-key constraints
Comparison between an express-mode and a deluxe-mode load operation
HPL load and unload errors
Rejected records from the input file
Constraint violations
View error records
HPL performance
The onpload configuration parameters
Express-mode limitations
The onstat options for onpload
Devices for the device array
HPL usage tasks
Reorganize computer configuration
Alter the schema of a table
Assess information for loading or unloading external data
Settings for a no-conversion load or unload job
Run no-conversion load jobs with tables with hidden columns
An express-mode load with delimited ASCII
HPL performance hints
Choose an efficient format
Ensure enough converter threads and VPs
Ensure enough memory
Ensure enough buffers of adequate size
Increase the commit interval
Limitation when using the Excalibur Text DataBlade Module indexes
The onpload utility
Overview of the onpload utility
The onpload file name size limitations on UNIX
Start the onpload utility
Using the onpload utility
The onpload utility syntax
Set the onpload run mode with the -f option
Type the onpload -f flags
Interpret the onpload -d and -f options together
Modify the size of onpload database parameters
The onpload -i option
Override the onpload database values
Load data into collection data type columns
The onpladm utility
Overview of the onpladm utility
The onpladm utility features
Specification-file conventions
Error handling
Define onpladm utility jobs
Create onpladm jobs
Create conversion jobs
Create conversion jobs by using a quick command
Create conversion jobs by using detailed specification files
Create a conversion-load job
Create a conversion unload job
Create no-conversion jobs
Create no-conversion jobs by using a quick command
Create no-conversion jobs by using detailed specification files
Create a no-conversion load job
Create a no-conversion unload job
Modify a job by using a detailed specification file
Describe a job
List all jobs in a project
Run a job
The onpladm utility when referential constraints are on tables
Delete a job
Define device arrays
Create a device array
Modify a device array
Describe a device array
List project device arrays
Deleting a device array
Define maps
Create maps
Create a map by using a quick command
Create a map with a detailed specification file
Delete a map
Describe a map
Modify a map by using a detailed specification file
List all maps in a project
Define formats
Create a format
Modify a format by using a specification file
Describe a format
List all formats in a project
Delete a format
Define queries
Creating a query
Modify a query
Describing a query
List all queries in a project
Delete a query
Define filters
Create a filter
Modify a filter
Describe a filter
List all filters in a project
Delete a filter
Define projects
Create a project
Run all jobs in a project
List all projects
Delete a project
Define machine types
Create a machine type
Modify a machine type
Describe a machine
List all existing machine types
Delete a machine type
Define database operations
Create a database project
Configure target-server attributes
Set target-server attribute values
List target-server defaults
Appendixes
The onpload database
The defaults table in the onpload database
The delimiters table in the onpload database
The device table in the onpload database
The driver table in the onpload database
The filteritem table in the onpload database
The filters table in the onpload database
The formatitem table in the onpload database
The formats table in the onpload database
The language table in the onpload database
The machines table in the onpload database
The mapitem table in the onpload database
The mapoption table in the onpload database
The maps table in the onpload database
The note table in the onpload database
The project table in the onpload database
The query table in the onpload database
The session table in the onpload database
High-Performance Loader configuration file
HPL configuration parameter descriptions
HPL configuration parameter file conventions
The AIOBUFFERS configuration parameter
The AIOBUFSIZE configuration parameter
The CONVERTTHREADS configuration parameter
The CONVERTVPS configuration parameter
The HPLAPIVERSION configuration parameter
The HPL_DYNAMIC_LIB_PATH configuration parameter
The STRMBUFFERS configuration parameter
The STRMBUFFSIZE configuration parameter
Picture strings
Alphanumeric pictures
Numeric pictures
Date pictures
Match condition operators and characters
Operator descriptions and examples
Custom-conversion functions
Custom conversion example
The onpload conversion process
Integrating custom conversion functions
API functions
The DBXget_source_value(fldname,buffer,buflen) routine
The DBXget_dest_value(fldname,buffer,buflen) routine
The DBXput_dest_value(fldname,buffer) routine
The DBXget_dest_length(fldname) routine
The onstat -j option
Using the onstat -j option
The HPL log-file and pop-up messages
How HPL logfile messages are ordered
HPL logfile message categories
The HPL log-file messages
Blob conversion error occurred on record record_num
Cannot access database table table_name: SQL error error_num
Cannot allocate shared memory
Cannot allocate TLI memory for operating_system structure
Cannot bind socket connection: errno= operating-system_error_num
Cannot bind TLI connection: t_errno= t_error_num
Cannot configure driver driver_name
Cannot connect to message server: Socket error = UNIX_error_num
Cannot connect to message server: TLI erro r= t_error_num, TLI event = t_event_num, errno = error_num
Cannot connect to server_name: SQL error error_num, ISAM error error_num
Cannot connect worker to server data stream
Cannot disable table_name object constraints: SQL error error_num, ISAM error error_num
Cannot disable primary-key constraint. Child-table references exist
Cannot express load to logged table on HDR server server_name
Cannot filter indexes for table table_name: SQL error error_num, ISAM error error_num
Cannot find the shared library path in the plconfig file. Using the shared library from the default location library_location
Cannot find the user-defined function user_func_name in the shared library: error error_num
Cannot get systable info for table table_name: SQL error error_num, ISAM error error_num
Cannot initialize shared library handling
Cannot load code-set conversion file from file_name to file_name
Cannot load mapping definitions
Cannot load the shared library library_location
Cannot locate delimiter in data file
Cannot open
Cannot open simple large object file: file_name, simple large object not loaded
Cannot open database database_name: SQL error error_num, ISAM error error_num
Cannot open file file_name: error number operating-system_error_num
Cannot open TCP connection for server_name: errno operating-system_error_num
Cannot perform express mode load on table with pseudo rowid
Cannot perform express-mode load with rowsize = row_length > page_size
Cannot read file file_name: AIO error code operating-system_error_num
Cannot re-enable all objects: num_violations violations detected
Cannot reorder query statement to align simple large objects or Ext Types
Cannot reorder query statement to align blobs
Cannot set mode of table_name objects from current_mode to final_mode mode: SQL error error_num, ISAM error error_num
Cannot start violations table for table_name: SQL error error_num, ISAM error error_num
Cannot stop violations table for table_name: SQL error error_num, ISAM error error_num
Cannot unload to multiple devices when the given query cannot be executed in parallel
Cannot write file file_name: AIO error code operating-system_error_num
Code-set conversion overflow
Conversion of onpload database failed due to error error_num
Conversion of onpload database failed due to error error_num, run as user informix
Custom conversion function function_name not found in shared library
Discarded num_bytes null bytes from end of tape device device_name
Environment variable variable_name expansion would overflow string
Error accepting socket connection: errno = operating-system_error_num
Error accessing file_name
Error accessing format: SQL error error_num, ISAM error error_num
Error accessing map map_name: SQL error error_num, ISAMerror error_num
Error accessing sysmaster: SQL error error_num, ISAMerror error_num
Error accessing table table_name: SQL error error_num, ISAM error error_num
Error: AIO buffer size buffer_size is less than required minimum size size
Error error_num closing current database
Error operating-system_error_num closing file file_name
Error error_num converting record field field_name to column column_name
Error declaring cursor: could not get table info
Error declaring cursor: SQL error error_num, ISAM error error_num
Error describing unload query query_name: SQL error error_num, ISAM error error_num
Error error_num initializing backend connection
Error inserting into table table_name: SQL error error_num, ISAM error error_num
Error listening for socket connection: t_errno = t_error_num errno = operating-system_error_num
Error listening for TLI connection: t_errno = t_error_num errno = UNIX_error_num
Error error_num on record record_num converting column column_name to record field field_name
Error occurred on record %d reading pipe %s
Error on close of server load session: SQL error error_num, ISAM error error_num
Error opening cursor: SQL Error error_num
Error preparing query: SQL error error_num
Error preparing statement statement_name: SQL error error_num, ISAM error error_num
Error preparing unload query query_name: SQL error error_num, ISAM error error_num
Error error_num reading message queue
Error operating-system_error_num reading TLI/socket connection
Error error_num setting isolation level
Error error_num writing message on message queue
Error operating-system_error_num writing TLI/socket connection
Error: Stream buffer size buffer_size is less than required minimum size size
Exhausted all attempts to allocate shared-memory key.
Fatal error: cannot execute pipe_name
Fatal error: cannot load X resource
Fatal error creating server load session: error error_num
Fatal error getting stream buffer from server
Fatal error in server row processing: SQL error error_num, ISAM error error_num
File type device file file_name is not a regular (disk) file
Got Interrupt: Shutting down
Internal error: Cannot initialize AIO library
Internal error: Cannot send message
Internal error: error_num. Contact Tech Support
Internal error: invalid message type error_num
Internal error error_num reading queue
Invalid count detected, might be due to abnormal BE shutdown
Invalid code-set character: Cannot convert
Invalid HEXASCII simple large object or extended type representation on record record_num
Invalid HEXASCII simple large object representation in fieldname, record record_num
Invalid project name project_name entered
Invalid reject count detected, might be due to abnormal BE shutdown. Using last known reject count and proceeding
Invalid session ID id_number
Invalid tape header expecting -> tape_name
Map map_name type is not a load map
Method not supported by current driver
MT cannot bind to vpid
MT internal failure
MT failure putting CPU online
No insert permission on table table_name
No mapping to simple large object field field_name
onpload must run on the host host_name that contains the target database
onpload terminated by signal
Pipe type device file file_name is not a regular file
Pload cannot reorder queries having expressions/aggregates and blobs/udts in the same select list
Query contains unmapped simple large object column column_name: Cannot proceed
Query for unload is not a select query.
Record is too long to process: recnum record_num, length record_length, bufsize buffer_size
Server interface error; expected num_input but got num_received instead
SQL error error_num executing statement statement_name
Simple large object or extended type conversion error occurred on record record_num
Start record record_num is greater than number of records total_num read from input file_name
Successfully loaded the shared library library_location
Table table_name will be read-only until level-0 archive
Tables with BLOBS cannot be loaded in High Performance Mode
Tables with BLOBS or extended types cannot be loaded in Express mode
Tables with simple large objects or extended types cannot be processed with no conversion (-fn)
Tape header is larger than I/O buffer: tape header_length, I/O buffer_size
Tape type device file file_name is not a character-special or block-special file
There is no mapping to column column_name, which cannot accept null values
Unable to load locale categories for locale locale_name: error error_num
Unload query select item for the query_item expression needs to be assigned a name
Write/read to/from tape until end of device
Write to device (tape or pipe) device_name failed; no space left on device. AIO error error_num
HPL logfile pop-up messages
Cannot attach to server shared memory
Cannot create shared-memory message queue: error error_num
Cannot create shared-memory pool: errno UNIX_error_num
Cannot initialize multithreaded library
Cannot initialize shared memory: errno operating-system_error_num
Cannot load X resource
Cannot open. Enter (r)etry, (c)ontinue, (q)uit job when ready
Cannot open log file log_file_name.
Cannot start I/O. Enter (r)etry, (c)ontinue, (q)uit job when ready
Fatal error: shared memory will conflict with server
Incorrect database version. Make sure that it is upgraded properly
Press ‘r’ when ready, ‘c’ to shutdown device or ‘q’ to quit
Set the shared library path as an absolute path in the plconfig file
Tables with blobs cannot be loaded in High-Performance Mode
Write error. Enter (r)etry, (c)ontinue, (q)uit job when ready
Custom drivers
Add a custom driver to the onpload utility
Adding the driver name to the onpload database
Preparing the custom-driver code
Preparing the file that provides the driver functionality
Preparing the plcstdrv.c file
Rebuilding the shared-library file
Connect your code to onpload at run time
Driver initialization
Register driver functions
An example of a custom driver
The plcstdrv.c file
Custom-driver code for MYDRIVER
Available driver methods
The PL_MTH_OPEN function
The PL_MTH_CLOSE function
Available API support functions
The pl_inherit_methods(driver, methodtable) function
The pl_set_method_function(methodtable, method, function) function
The pl_driver_inherit(method) function
The pl_get_recordlength() function
The pl_set_informix_conversion(flag) function
The pl_lock_globals() function
The pl_reset_inherit_chain(method) function
Run load and unload jobs on a Windows computer
The onpladm utility on Windows
Run the Run Job or Run Project commands
Running onpladm on UNIX with the database server running on Windows
Preparing jobs with the ipload utility on Windows computers
Conversion and reversion scripts for HPL database migration
Upgrade the High-Performance Loader onpload database
Revert from the current onpload database
Performance Guide
Performance basics
Developing a basic approach to performance measurement and tuning
Quick start for acceptable performance on a small database
Performance goals
Measurements of performance
Throughput
Ways to measure throughput
Standard throughput benchmarks
Response time
Response time and throughput
Response-time measurement
Operating-system timing commands
Operating-system tools for monitoring performance
Timing functions within your application
Cost per transaction
Resource utilization and performance
Resource utilization
CPU utilization
Memory utilization
Disk utilization
Factors that affect resource utilization
Maintenance of good performance
Performance monitoring and the tools you use
Evaluate the current configuration
Create a performance history
The importance of a performance history
Tools that create a performance history
Operating-system tools
Database server tools
Performance information that the onstat utility displays
Monitor database server resources
Monitor resources that impact CPU utilization
Monitor memory utilization
Monitor disk I/O utilization
Using onstat -g to monitor I/O utilization
Using the oncheck utility to monitor I/O utilization
Monitor transactions
Using the onlog utility to monitor transactions
Using the onstat utility to monitor transactions
Monitor sessions and queries
Monitoring memory usage for each session
Using the SET EXPLAIN statement
Effect of configuration on CPU utilization
UNIX configuration parameters that affect CPU utilization
UNIX semaphore parameters
UNIX file-descriptor parameters
UNIX memory configuration parameters
Windows configuration parameters that affect CPU utilization
Configuration parameters and environment variables that affect CPU utilization
Specifying virtual processor class information
Setting the number of CPU VPs
Disabling process priority aging for CPU VPs
Specifying processor affinity
Distributing computation impact
Isolating AIO VPs from CPU VPs
Avoiding a certain CPU
Setting the number of AIO VPs
Setting the MULTIPROCESSOR configuration parameter when using multiple CPU VPs
Setting the SINGLE_CPU_VP configuration parameter when using one CPU VP
Optimizing access methods
Setting the value of OPTCOMPIND within a session
Limiting PDQ resources in queries
Limiting the performance impact of CPU-intensive queries
Limiting the number of PDQ scan threads that can run concurrently
Configuring poll threads
Specifying the connection protocol
Specifying virtual-processor classes for poll threads
Specifying the number of connections and poll threads
Improve connection performance and scalability
Enabling fast polling
Network buffer pools
Network buffers
Support for private network buffers
Network buffer size
Virtual processors and CPU utilization
Adding virtual processors
Monitoring virtual processors
Using some onstat-g commands to monitor virtual processors
Monitor virtual processors with the onstat-g glo command
Monitor virtual processors with the onstat-g rea command
Monitor virtual processors with the onstat-g ioq command
Using SMI tables to monitor virtual processors
Private memory caches
Connections and CPU utilization
Multiplexed connections and CPU utilization
MaxConnect for multiple connections UNIX
Effect of configuration on memory utilization
Shared memory
Resident portion of shared memory
Virtual portion of shared memory
Message portion of shared memory
Buffer pool portion of shared memory
Estimating the size of the resident portion of shared memory
Estimating the size of the virtual portion of shared memory
Estimating the size of the message portion of shared memory
Configuring UNIX shared memory
Freeing shared memory with onmode -F
Configuration parameters that affect memory utilization
Setting the size of the buffer pool, logical-log buffer, and physical-log buffer
The BUFFERPOOL configuration parameter and memory utilization
The DS_TOTAL_MEMORY configuration parameter and memory utilization
Algorithm for determining DS_TOTAL_MEMORY
Deriving a minimum for decision-support memory
Deriving a working value for decision-support memory
When the DS_TOTAL_MEMORY configuration parameter is set
When the DS_TOTAL_MEMORY configuration parameter is not set
Checking the derived value for decision-support memory
The LOGBUFF configuration parameter and memory utilization
The LOW_MEMORY_RESERVE configuration parameter and memory utilization
The PHYSBUFF configuration parameter and memory utilization
The LOCKS configuration parameter and memory utilization
The RESIDENT configuration parameter and memory utilization
The SHMADD and EXTSHMADD configuration parameters and memory utilization
The SHMTOTAL configuration parameter and memory utilization
The SHMVIRTSIZE configuration parameter and memory utilization
The SHMVIRT_ALLOCSEG configuration parameter and memory utilization
The STACKSIZE configuration parameter and memory utilization
Configure and monitor memory caches
Data-dictionary cache
Data-dictionary configuration
Data-distribution cache
Data-distribution configuration
Monitor and tune the SQL statement cache
Prepared statements and the statement cache
SQL statement cache configuration
Number of SQL statement executions
Monitoring the number of hits on the SQL statement cache
Determining the number of nonshared entries in the SQL statement cache
Monitoring and tuning the size of the SQL statement cache
Changing the size of the SQL statement cache
Too many single-use queries in the SQL statement cache
Memory limit and size
Multiple SQL statement cache pools
Number of SQL statement cache pools
Size of SQL statement cache pools and the current cache
SQL statement cache information in onstat -g ssc output
Session memory
Data-replication buffers and memory utilization
Memory latches
Monitoring latches with command-line utilities
Monitoring latches with onstat -p
Monitoring latches with onstat -s
Monitoring latches with SMI tables
Encrypted values
Effect of configuration on I/O activity
Chunk and dbspace configuration
Associate disk partitions with chunks
Associate dbspaces with chunks
Placing system catalog tables with database tables
I/O for cooked files for dbspace chunks
Direct I/O (UNIX)
Direct I/O (Windows)
Concurrent I/O (AIX only)
Enabling the direct I/O or concurrent I/O option (UNIX)
Confirming the use of the direct or concurrent I/O option (UNIX)
Placement of critical data
Consider separate disks for critical data components
Consider mirroring for critical data components
Consider mirroring the root dbspace
Consider mirroring smart-large-object chunks
Mirroring and its effect on the logical log
Mirroring and its effect on the physical log
Configuration parameters that affect critical data
Configure dbspaces for temporary tables and sort files
Creating temporary dbspaces
Specify temporary tables in the DBSPACETEMP configuration parameter
Override the DBSPACETEMP configuration parameter for a session
Estimating temporary space for dbspaces and hash joins
PSORT_NPROCS environment variable
Configure sbspaces for temporary smart large objects
Creating temporary sbspaces
Specify which sbspaces to use for temporary storage
Placement of simple large objects
Advantage of blobspaces over dbspaces
Blobpage size considerations
Optimize blobspace blobpage size
Obtain blobspace storage statistics
Determine blobpage fullness with oncheck -pB output
Interpreting blobpage average fullness
Analyzing efficiency criteria with oncheck -pB output
Factors that affect I/O for smart large objects
Disk layout for sbspaces
Configuration parameters that affect sbspace I/O
onspaces options that affect sbspace I/O
Sbspace extents
Lightweight I/O for smart large objects
Advantages of lightweight I/O for smart large objects
Specifying lightweight I/O for smart large objects
Logging
Table I/O
Sequential scans
Light scans
Unavailable data
Configuration parameters that affect table I/O
How DATASKIP affects table I/O
Background I/O activities
Configuration parameters that affect checkpoints
RTO_SERVER_RESTART and its effect on checkpoints
Automatic checkpoints, LRU tuning, and AIO virtual processor tuning
CKPTINTVL and its effect on checkpoints
LOGSIZE and LOGFILES and their effect on checkpoints
Checkpoints and the physical log
ONDBSPACEDOWN and its effect on checkpoints
Configuration parameters that affect logging
LOGBUFF and PHYSBUFF and their effect on logging
LOGFILES and its effect on logging
Calculating the space allocated to logical log files
LOGSIZE and its effect on logging
Estimating logical-log size when logging dbspaces
Estimating the logical-log size when logging simple large objects
Estimating the logical-log size when logging smart large objects
DYNAMIC_LOGS and its effect on logging
AUTO_LLOG and its effect on logging
LTXHWM and LTXEHWM and their effect on logging
TEMPTAB_NOLOG and its effect on logging
SESSION_LIMIT_LOGSPACE and its effect on logging
SESSION_LIMIT_TXN_TIME and its effect on logging
Configuration parameters that affect page cleaning
CLEANERS and its effect on page cleaning
BUFFERPOOL and its effect on page cleaning
RTO_SERVER_RESTART and its effect on page cleaning
Configuration parameters that affect backup and restore
ON-Bar configuration parameters
ontape configuration parameters (UNIX)
Configuration parameters that affect rollback and recovery
OFF_RECVRY_THREADS and ON_RECVRY_THREADS and their effect on fast recovery
PLOG_OVERFLOW_PATH and its effect on fast recovery
RTO_SERVER_RESTART and its effect on fast recovery
The LOW_MEMORY_RESERVE configuration parameter and memory utilization
Configuration parameters that affect data replication and auditing
Configuration parameters that affect data replication
Configuration parameters that affect auditing
LRU tuning
Table performance considerations
Placing tables on disk
Isolating high-use tables
Placing high-use tables on middle partitions of disks
Using multiple disks
Using multiple disks for a dbspace
Using multiple disks for logical logs
Spreading temporary tables and sort files across multiple disks
Backup and restore considerations when placing tables on disks
Factors affecting the performance of nonfragmented tables and table fragments
Estimating table size
Estimating data pages
Estimating tables with fixed-length rows
Estimating tables with variable-length rows
Selecting an intermediate value for the size of the table
Estimating pages that simple large objects occupy
Storing simple large objects in the tblspace or a separate blobspace
Estimating tblspace pages for simple large objects
Managing the size of first and next extents for the tblspace tblspace
Managing sbspaces
Estimating pages that smart large objects occupy
Estimating the size of the sbspace and metadata area
Sizing the metadata area manually for a new chunk
Example of calculating the metadata area for a new chunk
Improving metadata I/O for smart large objects
Monitoring sbspaces
Monitoring sbspaces with oncheck -cS
Monitoring sbspaces with oncheck -pe
Monitoring sbspaces with oncheck -pS
Monitoring sbspaces with onstat -g smb
Changing storage characteristics of smart large objects
Altering smart-large-object columns
Managing extents
Choosing table extent sizes
Extent sizes for tables in a dbspace
Extent sizes for table fragments
Extent sizes for smart large objects in sbspaces
Monitoring active tblspaces
Monitoring the upper limit on extents and extent interleaving
Considering the upper limit on extents
Checking for extent interleaving
Eliminating interleaved extents
Reorganizing dbspaces and tables to eliminate extent interleaving
Creating or altering an index to cluster
Using ALTER TABLE to eliminate extent interleaving
Reclaiming unused space within an extent
Reclaiming space in an empty extent with ALTER INDEX
Reclaiming space in an empty extent by unloading and re-creating or reloading a table
Releasing space in an empty extent with ALTER FRAGMENT
Managing extent deallocation with the TRUNCATE keyword
Defragment partitions to merge extents
Storing multiple table fragments in a single dbspace
Displaying a list of table and index partitions
Changing tables to improve performance
Loading and unloading tables
Advantages of logging tables
Advantages of nonlogging tables
Quickly loading a large standard table
Quickly loading a new nonlogging table
Dropping indexes for table-update efficiency
Creating and enabling referential constraints efficiently
Attaching or detaching fragments
Altering a table definition
Slow alter
In-place alter
Conditions for in-place alter operations
Performance considerations for DML statements
Performance of in-place alters for DDL operations
Altering a column that is part of an index
Fast alter
Denormalize the data model to improve performance
Shortening rows
Expelling long strings
Convert CHAR columns into VARCHAR columns to shorten rows (GLS)
Convert a long string to a TEXT data type column
Move strings to a companion table
Build a symbol table
Splitting wide tables
Redundant data
Adding redundant data
Reduce disk space in tables with variable length rows
Reduce disk space by compressing tables and fragments
Boosted Partition Free Space Caches (PFSC)
Indexes and index performance considerations
Types of indexes
B-tree indexes
Structure of conventional index pages
Forest of trees indexes
R-tree indexes
Indexes that DataBlade modules provide
Estimating index pages
Index extent sizes
Formula for estimating the extent size of an attached index
Formula for estimating the extent size of a detached index
Estimating conventional index pages
Managing indexes
Space costs of indexes
Time costs of indexes
Unclaimed index space
Indexes on columns
Filtered columns in large tables
Order-by and group-by columns
Avoiding columns with duplicate keys
Clustering
Configuration parameters that affect the degree of clustering
Nonunique indexes
Improve query performance with a forest of trees index
Detecting root node contention
Creating a forest of trees index
Disabling and enabling a forest of trees index
Performing a range scan on a forest of trees index
Determining if you are using a forest of trees index
Finding the number of hashed columns and subtrees in a forest of trees index
Creating and dropping an index in an online environment
When you cannot create or drop indexes online
Creating attached indexes in an online environment
Limiting memory allocation while creating indexes online
Improving performance for index builds
Estimating memory needed for sorting
Estimating temporary space for index builds
Storing multiple index fragments in a single dbspace
Improving performance for index checks
Indexes on user-defined data types
Defining indexes for user-defined data types
B-tree secondary-access method
Uses for a B-tree index
Extending a generic B-tree index
Identifying the available access methods
User-defined secondary-access methods
R-tree indexes
Using a functional index
What is a functional index?
When is a functional index used?
Creating a functional index
Using an index that a DataBlade module provides
Choosing operator classes for indexes
Operator classes
Strategy and support functions of a secondary access method
Default operator classes
Built-in B-tree operator class
B-tree strategy functions
B-tree support function
Identifying the available operator classes
User-defined operator classes
Locking
Locks
Locking granularity
Row and key locks
Key-value locks
Page locks
Table locks
Database locks
Configuring the lock mode
Setting the lock mode to wait
Locks with the SELECT statement
Isolation level
Dirty Read isolation
Committed Read isolation
Ways to reduce the risk of Committed Read isolation level conflicts
Cursor Stability isolation
Repeatable Read isolation
Locking nonlogging tables
Update cursors
Locks placed with INSERT, UPDATE, and DELETE statements
The internal lock table
Monitoring locks
Configuring and managing lock usage
Monitoring lock waits and lock errors
Monitoring the number of free locks
Monitoring deadlocks
Monitoring isolation levels that sessions use
Locks for smart large objects
Byte-range locking
How the database server manages byte-range locks
Using byte-range locks
Monitoring byte-range locks
Setting number of locks for byte-range locking
Lock promotion
Dirty Read isolation level and smart large objects
Fragmentation guidelines
Planning a fragmentation strategy
Fragmentation goals
Improved query performance through fragmentation strategy
Reduced contention between queries and transactions
Increased data availability
Increased granularity for backup and restore
Examining your data and queries
Considering physical fragmentation factors
Distribution schemes
Choosing a distribution scheme
Designing an expression-based distribution scheme
Suggestions for improving fragmentation
Strategy for fragmenting indexes
Attached indexes
Detached indexes
Restrictions on indexes for fragmented tables
Strategy for fragmenting temporary tables
Distribution schemes that eliminate fragments
Fragmentation expressions for fragment elimination
Query expressions for fragment elimination
Range expressions in query
Equality expressions in query
Effectiveness of fragment elimination
Nonoverlapping fragments on a single column
Overlapping fragments on a single column
Nonoverlapping fragments, multiple columns
Improve the performance of operations that attach and detach fragments
Improving ALTER FRAGMENT ATTACH performance
Distribution schemes for reusing indexes
Fragmenting the index in the same way as the table
Fragmenting the index with the same distribution scheme as the table
Attaching unfragmented tables together
Ensuring no data movement when you attach a fragment
Indexes on attached tables
Example for situation when corresponding index does not exist
Example for situation when index on table is not usable
Improving ALTER FRAGMENT DETACH performance
Fragmenting the index in the same way as the table
Fragmenting the index using same distribution scheme as the table
Forcing out transactions when altering table fragments
Monitoring Fragment Use
Monitoring fragmentation with the onstat -g ppf command
Monitoring fragmentation with SET EXPLAIN output
Queries and the query optimizer
The query plan
The access plan
The join plan
Nested-loop join
Hash join
Join order
Example of query-plan execution
Example of a join with column filters
Example of a join with indexes
Query plans that include an index self-join path
Query plan evaluation
Report that shows the query plan chosen by the optimizer
The explain output file
Query statistics section provides performance debugging information
Sample query plan reports
Single-table query
Multitable query
Key-first scan
Query plans for subqueries
Query plans for collection-derived tables
Example showing how the database server completes the query
Derived tables folded into parent queries
XML query plans in IBM Data Studio
Factors that affect the query plan
Statistics held for the table and index
Filters in the query
Indexes for evaluating a filter
Effect of PDQ on the query plan
Effect of OPTCOMPIND on the query plan
Single-table query
Multitable query
Effect of available memory on the query plan
Time costs of a query
Memory-activity costs
Sort-time costs
Row-reading costs
Sequential access costs
Nonsequential access costs
Index lookup costs
Reading duplicate values from an index
Searching for NCHAR or NVARCHAR columns in an index
In-place ALTER TABLE costs
View costs
Small-table costs
Data-mismatch costs
Encrypted-value costs
GLS functionality costs
Network-access costs
Optimization when SQL is within an SPL routine
SQL optimization
Displaying the execution plan
Automatic reoptimization
Reoptimizing SPL routines
Optimization levels for SQL in SPL routines
Execution of an SPL routine
SPL routine executable format stored in UDR cache
Adjust the UDR cache
Trigger execution
Performance implications for triggers
SELECT triggers on tables in a table hierarchy
SELECT triggers and row buffering
Optimizer directives
What optimizer directives are
Optimizer directives that are embedded in queries
External optimizer directives
Reasons to use optimizer directives
Preparation for using directives
Guidelines for using directives
Types of optimizer directives that are supported in SQL statements
Access-method directives
Join-order directives
Effect of join order on join plan
Join order when you use views
Join-method directives
Optimization-goal directives
Star-join directives
EXPLAIN directives
Example of directives that can alter a query plan
Configuration parameters and environment variables for optimizer directives
Optimizer directives and SPL routines
Forcing reoptimization to avoid an index and previously prepared statement problem
External optimizer directives
Creating and saving external directives
Enabling external directives
Deleting external directives
Parallel database query (PDQ)
What PDQ is
Structure of a PDQ query
Database server operations that use PDQ
Parallel update and delete operations
Parallel insert operations
Explicit inserts with SELECT...INTO TEMP statements
Implicit inserts with INSERT INTO...SELECT statements
Parallel index builds
Parallel user-defined routines
Hold cursors that use PDQ
SQL operations that do not use PDQ
Update statistics operations affected by PDQ
SPL routines and triggers and PDQ
Correlated and uncorrelated subqueries
OUTER index joins and PDQ
Remote tables used with PDQ
The Memory Grant Manager
The allocation of resources for parallel database queries
Limiting the priority of decision-support queries
Limiting the value of the PDQ priority
Maximizing OLTP throughput for queries
Conserving resources when using PDQ
Allowing maximum use of parallel processing
Determining the level of parallel processing
Limits on parallel operations associated with PDQ priority
Using SPL routines with PDQ queries
Adjusting the amount of memory for DSS and PDQ queries
Limiting the number of concurrent scans
Limiting the maximum number of PDQ queries
Managing PDQ queries
Analyzing query plans with SET EXPLAIN output
Influencing the choice of a query plan
Setting the PDQ priority dynamically
Enabling the database server to allocate PDQ memory
User control of PDQ resources
DBA control of resources for PDQ and DSS queries
Controlling resources allocated to PDQ
DBA control of resources allocated to decision-support queries
Monitoring resources used for PDQ and DSS queries
Using the onstat Utility
Monitoring PDQ threads with onstat utility commands
Monitoring resources allocated for a session running a DSS query
Identifying parallel scans in SET EXPLAIN output
Improving individual query performance
Test queries using a dedicated test system
Display the query plan
Improve filter selectivity
Filters with user-defined routines
Avoid some filters
Avoid difficult regular expressions
Avoid noninitial substrings
Use join filters and post-join filters
Automatic statistics updating
How AUS works
AUS expiration policies
Changing AUS expiration policies
Viewing AUS statements
Prioritizing databases in AUS
Rescheduling AUS
Disabling AUS
Update statistics when they are not generated automatically
Update the statistics for the number of rows
Drop data distributions if necessary when upgrading
Drop distributions in LOW mode without gathering statistics
Creating data distributions
Updating statistics for join columns
Updating statistics for columns with user-defined data types
Update statistics in parallel on very large databases
Adjust the amount of memory and disk space for UPDATE STATISTICS
Data sampling during update statistics operations
Display data distributions
Improve performance by adding or removing indexes
Replace autoindexes with permanent indexes
Use composite indexes
Indexes for data warehouse applications
Configure B-tree scanner information to improve transaction processing
Alice scan mode values
Leaf and range scan mode settings
B-tree scanner index compression levels and transaction processing performance
Setting the level for B-tree scanner compression of indexes
Determine the amount of free space in an index page
Optimizer estimates of distributed queries
Buffer data transfers for a distributed query
The query plan of a distributed query
Improve sequential scans
Enable view folding to improve query performance
Reduce the join and sort operations
Avoid or simplify sort operations
Use parallel sorts
Use temporary tables to reduce sorting scope
Configuring memory for queries with hash joins, aggregates, and other memory-intensive elements
Optimize user-response time for queries
Optimization level
Optimization goals
Specifying the query performance goal
Preferred query plans for user-response-time optimization
Nested-loop joins versus hash joins
Table scans versus index scans
Ordering with fragmented indexes
Optimize queries for user-defined data types
Parallel UDRs
Selectivity and cost functions
User-defined statistics for UDTs
Negator functions
Optimize queries with the SQL statement cache
When to use the SQL statement cache
Using the SQL statement cache
Enabling the SQL statement cache
Placing statements in the cache
Monitoring memory usage for each session
Display all user threads and session memory usage
Display detailed session information and memory usage
Display information about session SQL statements
Display information about the memory that SQL statements use in a session
Monitoring usage of the SQL statement cache
Monitor sessions and threads
Monitor sessions and threads with onstat commands
Monitor blocking threads with the onstat -g bth and onstat -g BTH commands
Monitor threads with onstat –u output
Monitor threads with onstat -g ath output
Monitor threads with onstat -g act output
Monitor threads with onstat -g cpu output
Monitor session resources with onstat -g ses output
Monitor session memory with onstat -g mem and onstat -g stm output
Monitor sessions and threads with SMI tables
Monitor transactions
Display information about transactions
Display information about transaction locks
Display statistics on user sessions
Display Statistics on Sessions Executing SQL Statements
The onperf utility on UNIX
Overview of the onperf utility
Basic onperf utility functions
Display metric values
Save metric values to a file
Review metric measurements
onperf utility tools
Requirements for running the onperf utility
Starting the onperf utility and exiting from it
The onperf user interface
Graph tool
Graph-tool title bar
Graph-tool graph menu
Graph-tool metrics menu
Graph-tool view menu
The graph-tool Configure menu and the Configuration dialog box
Graph-tool Tools menu
Changing the scale of metrics
Displaying recent-history values
Query-tree tool
Status tool
Activity tools
Why you might want to use onperf
Routine monitoring with onperf
Diagnosing sudden performance loss
Diagnosing performance degradation
onperf utility metrics
Database server metrics
Disk-chunk metrics
Disk-spindle metrics
Physical-processor metrics
Virtual-processor metrics
Session metrics
Tblspace metrics
Fragment metrics
Appendix
Case studies and examples
Case study of a situation in which disks are overloaded
SNMP Subagent Guide
SNMP concepts
What is SNMP?
Purpose of the SNMP
The SNMP architecture
SNMP network managers
Master agents
Subagents
Managed components
Management Information Bases
Informix implementation of SNMP
Components of the Informix implementation
Purpose of Informix SNMP
Event notification
Data requests
Traps
Information that OnSNMP provides
SNMP standard
SNMP architecture
Informix implementation of SNMP on UNIX or Linux
The runsnmp.ksh script
UNIX master agents
Assuring compatibility
Installing and configuring a master agent manually
Starting and stopping a master agent
Starting a master agent manually
Stopping a master agent manually
Making sure that a master agent is running correctly
UNIX subagent
UNIX server discovery process
Preparing onsrvapd manually
Issue the onsrvapd command
Starting onsrvapd manually
Making sure that onsrvapd is running correctly
Choose an installation directory
Informix implementation of SNMP on Windows
Windows master agent
Windows subagent
Start and stop OnSNMP
Configure OnSNMP
Windows registry key for the OnSNMP logging level
Windows server discovery process
Start and stop onsrvapd
Installing the Informix SNMP agent
GLS and SNMP
MIB types and objects
Table indexing
Numeric index values
Alphabetical index values
Refresh control value
Files installed for SNMP
Files installed on UNIX or Linux
Files installed on Windows
Management Information Base reference
Application MIB
applTable
RDBMS MIB
rdbmsDbInfoTable
rdbmsDbTable
rdbmsRelTable
rdbmsSrvInfoTable
rdbmsSrvLimitedResourceTable
rdbmsSrvParamTable
rdbmsSrvTable
rdbmsTraps
frdbmsStateChange trap
Online MIB in the Informix Private MIB
onActiveBarTable
onActiveTableTable
onBarTable
onChunkTable
onDatabaseTable
onDbspaceTable
onErQueueTable
onErSiteTable
onFragmentTable
onLockTable
onLogicalLogTable
onPhysicalLogTable
onServerTable
onSessionTable
onSqlHostTable
onTableTable
InformixHQ Guide
What's new in InformixHQ
Architecture
System Compatibility
Getting Started
Starting the InformixHQ Server
Starting the InformixHQ Agent
Logging in InformixHQ
InformixHQ Concepts
InformixHQ Server
InformixHQ Server Configuration
InformixHQ UI
Adding Servers and Groups
Exploring Groups
Exploring Informix Database Servers
Configuring Monitoring
Configuring Alerting
Custom Dashboards
Schema Manager
Viewing Database Information
Viewing Table Information
Creating a Database
Creating a Demo Database
Dropping a Database
Creating an Index
Deleting an Index
Connection Manager
InformixHQ Server Settings
Configuring Alerting Notification
Creating Custom Sensors
User Settings
Configuring User Alerting Notification
Users and Permissions
InformixHQ Agent
InformixHQ Agent Configuration
Frequently asked questions (FAQs) about InformixHQ
High level architecture and functionality
Getting Started
Monitoring and the Repository Database
Security
Users and Permissions
Backup and restore
Backup and Restore Guide
Overview of backup and restore
Backup and restore concepts
Recovery system
Backup systems
Backup levels
Logical-log backup
Manual and continuous logical-log backups
Log salvage
Save logical-log backups
Restore systems
Physical and logical restores
Warm, cold, and mixed restores
Continuous log restore
Comparison of the ON-Bar and ontape utilities
Plan for backup and restore
Plan a recovery strategy
Types of data loss
Determine failure severity
Data use determines your backup schedule
Schedule backups
Security requirements for label-based access control
Plan a backup system for a production database server
Evaluate hardware and memory resources
Evaluate backup and restore time
Evaluate logging and transaction activity
Compress row data
Transform data with external programs
ON-Bar backup and restore system
Overview of the ON-Bar backup and restore system
ON-Bar components
Backup Services API (XBSA)
ON-Bar catalog tables
ixbar file: ON-Bar emergency boot file
bar_act.log file: ON-Bar activity log
ON-Bar script
Configure the storage manager and ON-Bar
Configure a storage manager
Storage-manager definitions in the sm_versions file
Configuring Spectrum Protect
Editing the Spectrum Protect client options files
Editing the Spectrum Protect client user options file
Editing the Spectrum Protect client system options file
Assigning a Spectrum Protect management class for a backup
Setting the HCL Informix Interface for Spectrum Protect environment variables
Registering with the Spectrum Protect server
Initializing the HCL Informix Interface for Spectrum Protect password
Updating the storage-manager definition in the sm_versions file for Spectrum Protect
Configuring ON-Bar for optional Spectrum Protect features
Configuring a third-party storage manager
Validating your storage manager
Configuring ON-Bar
ON-Bar security
Verifying the configuration of ON-Bar and your storage manager
Files that ON-Bar and storage managers use
Back up with ON-Bar
Preparing to back up data
Administrative files to back up
onbar -b syntax: Backing up
List of storage spaces in a file
Backing up blobspaces
onbar -m syntax: Monitoring recent ON-Bar activity
Viewing a list of registered backups
onbar -P syntax: Printing backed-up logical logs
onbar -v syntax: Verifying backups
Temporary space for backup verification
Verification failures
Diagnosing why a backup failed verification
Verifying an expired backup
Restoring when a backup is missing data
Restore data with ON-Bar
Pre-restore checklist
Storage space status and required actions
Storage device availability
onbar -r syntax: Restoring data
Avoid salvaging logical logs
Performing a cold restore
Configuring a continuous log restore by using ON-Bar
Restoring data by using a mixed restore
Strategies for using a mixed restore
Recreating chunk files during a restore
Restoring when using cooked chunks
Restoring when using raw chunks
Reinitializing the database server and restoring data
Replacing disks during a restore
Renaming a chunk to a nonexistent device
Restoring to a different computer
onbar -RESTART syntax: Restarting a failed restore
Resolve a failed restore
External backup and restore
External backup and restore overview
Block before backing up
Rules for an external backup
Prepare for an external backup
Block and unblock database server
Track an external backup
Performing an external backup when chunks are not mirrored
RS secondary server external backup
Performing an external backup of an RS secondary server
Data restored in an external restore
Rename chunks
External restore commands
Rules for an external restore
Performing an external restore
Performing a cold external restore
Performing a warm external restore
Examples of external restore commands
Initializing HDR with an external backup and restore
Customize and maintain ON-Bar
Customizing ON-Bar and storage-manager commands
Updating the ON-Bar script during reinstallation
Print the backup boot files
Migrate backed-up logical logs to tape
Expire and synchronize the backup catalogs
Choose an expiration policy
The onsmsync utility
Regenerate the emergency boot file
Regenerate the sysutils database
Delete a bad backup
Expire backups based on the retention date
Expire a generation of backups
Expire backups based on the retention interval
Expire backups with multiple point-in-time restores
Expire all backups
Monitor the performance of ON-Bar and the storage managers
Set ON-Bar performance statistics levels
View ON-Bar backup and restore performance statistics
ON-Bar catalog tables
The bar_action table
The bar_instance table
The bar_ixbar table
The bar_object table
The bar_server table
The bar_syncdeltab table
ON-Bar catalog map
ON-Bar messages and return codes
Message format in the ON-Bar message log
Message numbers
ON-Bar return codes
ontape backup and restore system
Configure ontape
Set configuration parameters for the ontape utility
Data transformation filter parameters for ontape
Tape and tape device parameters for ontape
Set the tape-device parameters
Specify separate devices for storage-space and logical-log backups
Specify tape devices as symbolic links
Specify a file system directory
Specify a remote device
Specify /dev/null for a tape device
Set TAPEDEV to stdio
Rewind tape devices before opening and on closing
Specify the tape-block-size
Specify the tape size
Tape size for remote devices
Changing your ontape configuration
Back up with ontape
Summary of ontape tasks
Start ontape
Exit codes for ontape
Change database logging status
Create a backup
Backup levels that ontape supports
Back up after changing the physical schema
Prepare for a backup
Avoid temp tables during heavy activity
Make sure enough logical-log space exists
Keep a copy of your configuration file
Verify consistency before a level-0 backup
Online and quiescent backups
Back up to tape
Label tapes created with ontape
Back up to standard output
Back up to a directory
Set the file directory path
Rename existing files
Override the default name of the archive files
ontape utility syntax: Perform a backup
Backup examples
Back up raw tables
Back up to Amazon Simple Storage Service
The ifxbkpcloud.jar utility
Cloud storage file naming conventions
When the logical-log files fill during a backup
When a backup terminates prematurely
Monitor backup history by using oncheck
Back up logical-log files with ontape
Before you back up the logical-log files
Use blobspace TEXT and BYTE data types and logical-log files
Use /dev/null when you do not need to recover
When to back up logical-log files
Start an automatic logical-log backup
Starting a continuous logical-log file backup
End a continuous logical-log backup
Devices that logical-log backups must use
Restore with ontape
Types of physical restore
Full-system restore
Restores of dbspaces, blobspaces, and sbspaces
Cold, warm, or mixed restores
Cold restores
Warm restores
Mixed restores
ontape utility syntax: Perform a restore
Restore the whole system
Gather backup and logical-log tapes before restoring
Decide on a complete cold or a mixed restore
Verify your database server configuration
Set shared-memory parameters to maximum assigned value
Set mirroring configuration to level-0 backup state
Verify that the raw devices or files are available
Perform a cold restore
Salvage logical-log files
Mount tapes during the restore
Restore logical log files
Bring the database server online when the restore is over
Restore selected storage spaces
Gather the appropriate tapes
Ensure that needed device are available
Back up logical-log files
Perform a warm restore
Restore raw tables
Configuring continuous log restore with ontape
Rename chunks during a restore
Validation sequence for renaming chunks
New chunk requirements
Rename chunks with command-line options
Rename chunks with a file
Rename chunks while specifying other options
Rename a chunk to a nonexistent device
Renaming a chunk to a nonexistent device
Restore from standard input
Restore data to a remote server
Simultaneous backup and restore by using standard I/O
Perform an external backup and restore
Recover data by using an external backup and restore
Data that is backed up in an external backup
Rules for an external backup
Performing an external backup
Prepare for an external backup
Block and unblock the database server
Track an external backup
Data that is restored in an external restore
Use external restore commands
Rules for an external restore
Rename chunks
Performing a cold external restore
Examples of external restore commands
Initializing HDR with an external backup and restore
Integrated Backup Encryption
Using a Remote Key Server
Using a Local Encryption Key
Informix Primary Storage Manager
HCL Informix Primary Storage Manager
Examples: Manage storage devices with Informix Primary Storage Manager
Setting up Informix Primary Storage Manager
Collecting information about file directories and devices
Configuring Informix Primary Storage Manager
Managing storage devices
The onpsm utility for storage management
onpsm -C detail output
onpsm -D list output
onpsm -O list output
Device pools
Device-configuration file for the Informix Primary Storage Manager
Informix Primary Storage Manager file-naming conventions
Message logs for Informix Primary Storage Manager
archecker table level restore utility
archecker table level restore utility
Overview of the archecker utility
The archecker configuration file
Schema command file
Table-level restore and locales
Data restore with archecker
Physical restore
Logical restore
The stager
The applier
Syntax for archecker utility commands
Manually control a logical restore
Performing a restore with multiple storage managers
Perform a parallel restore
Restore tables with large objects
When to delete restore files
The archecker schema reference
The CREATE TABLE statement
The CREATE EXTERNAL TABLE statement
The DATABASE statement
The INSERT statement
The RESTORE statement
The SET statement
Schema command file examples
Simple schema command file
Restore a table from a previous backup
Restore to a different table
Extract a subset of columns
Use data filtering
Restore to an external table
Restore multiple tables
Perform a distributed restore
Backup and restore configuration parameter reference
Backup and restore configuration parameters
ON-Bar and ontape configuration parameters and environment variable
BACKUP_FILTER configuration parameter
BAR_ACT_LOG configuration parameter
BAR_BSALIB_PATH configuration parameter
BAR_CKPTSEC_TIMEOUT configuration parameter
BAR_DEBUG configuration parameter
BAR_ENCRYPTION configuration parameter
BAR_DECRYPTION configuration parameter
BAR_DEBUG_LOG configuration parameter
BAR_HISTORY configuration parameter
BAR_IXBAR_PATH configuration parameter
BAR_MAX_BACKUP configuration parameter
BAR_MAX_RESTORE configuration parameter
BAR_NB_XPORT_COUNT configuration parameter
BAR_PERFORMANCE configuration parameter
BAR_PROGRESS_FREQ configuration parameter
BAR_RETRY configuration parameter
BAR_SIZE_FACTOR configuration parameter
BAR_XFER_BUF_SIZE configuration parameter
IFX_BAR_NO_BSA_PROVIDER environment variable
IFX_BAR_NO_LONG_BUFFERS environment variable
IFX_BAR_USE_DEDUP environment variable
IFX_TSM_OBJINFO_OFF environment variable
LTAPEBLK configuration parameter
LTAPEDEV configuration parameter
LTAPESIZE configuration parameter
RESTARTABLE_RESTORE configuration parameter
RESTORE_FILTER configuration parameter
TAPEBLK configuration parameter
TAPEDEV configuration parameter
TAPESIZE configuration parameter
The archecker utility configuration parameters and environment variable
AC_CONFIG file environment variable
AC_DEBUG configuration parameter
AC_IXBAR configuration parameter
AC_LTAPEBLOCK configuration parameter
AC_LTAPEDEV parameter
AC_MSGPATH configuration parameter
AC_SCHEMA configuration parameter
AC_STORAGE configuration parameter
AC_TAPEBLOCK configuration parameter
AC_TAPEDEV configuration parameter
AC_TIMEOUT configuration parameter
AC_VERBOSE configuration parameter
Informix Primary Storage Manager configuration parameters
PSM_ACT_LOG configuration parameter
PSM_CATALOG_PATH configuration parameter
PSM_DBS_POOL configuration parameter
PSM_DEBUG configuration parameter
PSM_DEBUG_LOG configuration parameter
PSM_LOG_POOL configuration parameter
Event alarm configuration parameters
Cloud Backup
Back up to Amazon Simple Storage Service using ON-Bar and the PSM
Back up to Softlayer using ON-Bar and the PSM
Appendixes
Troubleshooting some backup and restore errors
Corrupt page during an archive
Log backup already running
No server connection during a restore
Drop a database before a restore
No dbspaces or blobspaces during a backup or restore
Restore blobspace BLOBs
Changing the system time on the backup system
Migrate data, servers, and tools
Backing up before a database server or storage-manager upgrade
Upgrading a third-party storage manager
Changing storage-manager vendors
Switching from ontape to ON-Bar
GLS support
Use GLS with the ON-Bar utility
Identifiers that support non-ASCII characters
Identifiers that require 7-bit ASCII characters
Locale of ON-Bar messages
Use the GL_DATETIME environment variable with ON-Bar
Use GLS with the ontape utility
Replication
Enterprise Replication
About Enterprise Replication
HCL Informix Enterprise Replication technical overview
Enterprise Replication Terminology
Asynchronous Data Replication
Log-Based Data Capture
High Performance
High Availability
Consistent Information Delivery
Repair and Initial Data Synchronization
Flexible Architecture
Centralized Administration
Ease of Implementation
Network Encryption
How Enterprise Replication Replicates Data
Data Capture
Row Images
Evaluate rows for updates
Send queues and receive queues
Data Evaluation Examples
Data Transport
Applying replicated data
Planning and designing for Enterprise Replication
Plan for Enterprise Replication
Enterprise Replication Server administrator
Asynchronous propagation conflicts
Back up and restore of replication servers
Compression of replicated data
Transaction processing impact
SQL statements and replication
Global language support for replication
Replication between multiple server versions
Schema design for Enterprise Replication
Unbuffered Logging
Table Types
Label-based access control
Out-of-Row Data
Shadow columns
Unique key for replication
Cascading Deletes
Triggers
Constraint and replication
Sequence Objects
The NLSCASE database property
Replicating Table Hierarchies
Replication and data types
Replicating on Heterogeneous Hardware
Serial data types and replication keys
Replication of TimeSeries data types
Replication of large objects
Replicating Simple Large Objects from Tblspaces
Replication of large objects from blobspaces or sbspaces
Replication of opaque user-defined data types
Replication system design
Primary-Target Replication System
Primary-Target Data Dissemination
Data consolidation
Workload Partitioning
Workflow Replication
Primary-Target Considerations
Update-Anywhere Replication System
Conflict Resolution
Conflict resolution rule
Ignore Conflict-Resolution Rule
Time stamp conflict resolution rule
SPL Conflict Resolution Rule
SPL Conflict Resolution for Large Objects
Delete wins conflict resolution rule
Always-Apply Conflict-Resolution Rule
Conflict Resolution Scope
Choosing a Replication Network Topology
Fully Connected Topology
Hierarchical Routing Topology Terminology
Hierarchical Tree Topology
Forest of Trees
Setting up and managing Enterprise Replication
Preparing the Replication Environment
Preparing the Network Environment
Configuring hosts information for replication servers
Configuring port and service names for replication servers
Creating sqlhost group entries for replication servers
Configuring secure ports for connections between replication servers
Configuring network encryption for replication servers
Testing the replication network
Testing the password file
Preparing the Disk
Logical Log Configuration Disk Space
Logical Log Configuration Guidelines
Disk Space for Delete Tables
Shadow column disk space
Setting Up Send and Receive Queue Spool Areas
Row Data sbspaces
Creating sbspaces for Spooled Row Data
Logging Mode for sbspaces
Dropping a Spooled Row Data sbspace
Setting Up the Grouper Paging File
Creating ATS and RIS Directories
Preparing the Database Server Environment
Setting Database Server Environment Variables
Set configuration parameters for replication
Time Synchronization
Preparing Data for Replication
Preparing Consistent Data
Blocking Replication
Using DB-Access to Begin Work Without Replication
Using ESQL/C to Begin Work Without Replication
Preparing to Replicate User-Defined Types
Preparing to Replicate User-Defined Routines
Preparing Tables for Conflict Resolution
Preparing Tables for a Consistency Check Index
Preparing tables without primary keys
Preparing Logging Databases
Preparing for Role Separation (UNIX)
Load and unload data
High-Performance Loader
onunload and onload Utilities
dbexport and dbimport Utilities
UNLOAD and LOAD Statements
Data Preparation Example
Using the cdr start replicate Command
Using LOAD, UNLOAD, and BEGIN WORK WITHOUT REPLICATION
Using High-Availability Clusters with Enterprise Replication
High-availability replication systems
High-Availability Clusters in a Hierarchical Tree Topology
Using High-Availability Clusters in a Forest of Trees Topology
Setting Up Database Server Groups for High-Availability Cluster Servers
Managing Enterprise Replication with High-Availability Clusters
Failover for High-availability clusters in an Enterprise Replication environment
Replication latency for secondary servers
Defining Replication Servers, Replicates, Participants, and Replicate Sets
Starting Database Servers
Defining Replication Servers
Creating a new domain by cloning a server
Example of creating a new replication domain by cloning
Adding a server to the domain by cloning a server
Customizing the Replication Server Definition
Define a replicate
Participant definitions
Defining Replicates on Table Hierarchies
Replicate types
Master Replicate Verification
Creating Strict Master Replicates
Creating Empty Master Replicates
Defining Shadow Replicates
Specifying Conflict Resolution Rules and Scope
Specifying Replication Frequency
Setting Up Failed Transaction Logging
Replicate only changed columns
Using the IEEE Floating Point or Canonical Format
Enabling Triggers
Enabling code set conversion between replicates
Configuring code set conversion between replicates
Code set conversion errors
Controlling the replication of large objects
Replication to SPL routine
Define replicate sets
Exclusive Replicate Sets
Non-Exclusive Replicate Sets
Customizing the Replicate Set Definition
Initially Synchronizing Data Among Database Servers
Set up replication through templates
Defining Templates
Realizing Templates
Verifying Participants without Applying the Template
Synchronizing Data Among Database Servers
Improve Performance During Synchronization
Create tables automatically
Other synchronization options
Changing Templates
Template Example
Grid setup and management
Example of setting up a replication system with a grid
Example of rolling out schema changes in a grid
Creating a grid
Grid maintenance
Viewing grid information
Adding replication servers to a grid
Adding a replication server to a grid by running cdr change grid
Adding a replication server to a grid by cloning
Adding an externally created replicate into a grid replicate set
Adding an existing replicate to a grid replicate set by using cdr change replicateset
Adding an existing replicate to a grid replicate set by altering a table
Creating replicated tables through a grid
Enabling replication within a grid transaction
Propagating updates to data
Administering servers in the grid with the SQL administration API
Propagating database object changes
Propagating external files through a grid
Rerunning failed grid routines
Connection management for client connections to participants in a grid
Grid queries
Defining tables for grid queries
Configuring secure connections for grid queries
Examples of grid queries
Shard cluster setup
Creating a shard cluster
Shard cluster definitions
Sharded queries
Shard cluster management and monitoring
Shard edge server
Managing Replication Servers and Replicates
Managing Replication Servers
Modify server attributes
Dynamically Modifying Configuration Parameters for a Replication Server
Viewing Replication Server Attributes
Connect to another replication server
Temporarily stopping replication on a server
Restarting Replication on a Server
Suspending Replication for a Server
Resuming a Suspended Replication Server
Deleting a Replication Server
Managing Replicates
Modify replicates
Adding or Deleting Participants
Change replicate attributes
Changing the replication key of a replicate
Viewing Replicate Properties
Starting a Replicate
Stopping a Replicate
Suspending a Replicate
Resuming a Suspended Replicate
Deleting a Replicate
Managing Replicate Sets
Connection management for client connections to participants in a replicate set
Modifying Replicate Sets
Adding or Deleting Replicates From a Replicate Set
Changing Replication Frequency For the Replicate Set
Viewing Replicate Sets
Starting a Replicate Set
Stopping a Replicate Set
Suspending a Replicate Set
Resuming a Replicate Set
Deleting a Replicate Set
Managing Templates
Viewing Template Definitions
Deleting Templates
Managing Replication Server Network Connections
Viewing Network Connection Status
Dropping the Network Connection
Reestablishing the Network Connection
Resynchronizing Data among Replication Servers
Performing Direct Synchronization
Synchronizing Significantly Inconsistent Tables
Checking Consistency and Repairing Inconsistent Rows
Interpreting the Consistency Report
Increase the speed of consistency checking
Indexing the ifx_replcheck Column
Repair inconsistencies by time stamp
Repairing inconsistencies while enabling a replication server
Implementing a custom checksum function
Rules for custom checksum functions
Repairing Failed Transactions with ATS and RIS Files
Resynchronize data manually
Alter, rename, or truncate operations during replication
Altering multiple tables in a replicate set
Adding a Replicated Column
Removing replicated columns
Modifying the data type or size of a replicated column
Changing the Name of a Replicated Column, Table, or Database
Changing or re-creating primary key columns
Attaching a New Fragment to a Replicated Table
Remastering a Replicate
Remastering replicates without name verification
Recapture replicated transactions
Monitor and troubleshooting Enterprise Replication
Solve Replication Processing Problems
Failed Transaction (ATS and RIS) Files
Enabling ATS and RIS File Generation
ATS and RIS File Names
ATS and RIS File Formats
XML File Format
XML Tags
ATS and RIS Text File Contents
Disabling ATS and RIS File Generation
Suppressing Data Sync Errors and Warnings
Preventing Memory Queues from Overflowing
Handle potential log wrapping
Monitoring Disk Usage for Send and Receive Queue Spool
Increasing the Sizes or Numbers of Storage Spaces
Recovering when Storage Spaces Fill
Common configuration problems
Troubleshooting Tips for Alter Operations
Enterprise Replication Event Alarms
Enabling or Disabling Enterprise Replication Event Alarms
Push data feature
Push data session survival
Detach trigger
Loopback replication
Loopback Configuration
Replication definition between primary and pseudo groups
Appendixes
The cdr utility
Interpret the cdr utility syntax
Command Abbreviations
Option Abbreviations
Option Order
Long Command-Line Examples
Long Identifiers
Connect Option
Participant and participant modifier
Return Codes for the cdr Utility
Frequency Options
cdr add onconfig
cdr alter
cdr autoconfig serv
cdr change grid
cdr change gridtable
cdr change onconfig
cdr change replicate
cdr change replicateset
cdr change shardCollection
cdr check queue
cdr check replicate
cdr check replicateset
cdr check sec2er
cdr cleanstart
cdr connect server
cdr define grid
cdr define qod
cdr define region
cdr define replicate
cdr define replicateset
cdr define server
cdr define shardCollection
cdr define template
cdr delete grid
cdr delete region
cdr delete replicate
cdr delete replicateset
cdr delete server
cdr delete shardCollection
cdr delete template
cdr disable grid
cdr disable server
cdr disconnect server
cdr enable grid
cdr enable server
cdr error
cdr finderr
cdr list grid
cdr list replicate
cdr list replicateset
cdr list server
cdr list shardCollection
cdr list catalog
cdr list template
cdr migrate server
cdr modify grid
cdr modify replicate
cdr modify replicateset
cdr modify server
cdr realize template
cdr remaster
cdr remaster gridtable
cdr remaster replicateset
cdr remove onconfig
cdr repair
cdr reset qod
cdr resume replicate
cdr resume replicateset
cdr resume server
cdr start
cdr start qod
cdr start replicate
cdr start replicateset
cdr start sec2er
cdr stats rqm
cdr stats recv
cdr stats check
cdr stats sync
cdr stop
cdr stop qod
cdr stop replicate
cdr stop replicateset
cdr suspend replicate
cdr suspend replicateset
cdr suspend server
cdr swap shadow
cdr sync replicate
cdr sync replicateset
cdr -V
cdr view
Enterprise Replication configuration parameter and environment variable reference
CDR_APPLY Configuration Parameter
CDR_AUTO_DISCOVER configuration parameter
CDR_DBSPACE Configuration Parameter
CDR_DELAY_PURGE_DTC configuration parameter
CDR_DSLOCKWAIT Configuration Parameter
CDR_ENV Configuration Parameter
CDR_EVALTHREADS Configuration Parameter
CDR_LOG_LAG_ACTION configuration parameter
CDR_LOG_STAGING_MAXSIZE Configuration Parameter
CDR_MAX_DYNAMIC_LOGS Configuration Parameter
CDR_MAX_FLUSH_SIZE configuration parameter
CDR_MEM configuration parameter
CDR_NIFCOMPRESS Configuration Parameter
CDR_QDATA_SBSPACE Configuration Parameter
CDR_QUEUEMEM Configuration Parameter
CDR_SERIAL Configuration Parameter
CDR_SUPPRESS_ATSRISWARN Configuration Parameter
CDR_TSINSTANCEID configuration parameter
ENCRYPT_CDR Configuration Parameter
GRIDCOPY_DIR Configuration Parameter
SHARD_EDGE_NODE configuration parameter
SHARD_ID configuration parameter
SHARD_MEM configuration parameter
CDR_ALARMS Environment Variable
CDR_ATSRISNAME_DELIM Environment Variable
CDR_DISABLE_SPOOL Environment Variable
CDR_LOGDELTA Environment Variable
CDR_PERFLOG Environment Variable
CDR_RMSCALEFACT Environment Variable
CDR_ROUTER Environment Variable
CDRSITES_10X Environment Variable
CDRSITES_731 Environment Variable
CDRSITES_92X Environment Variable
Grid routines
ifx_get_erstate() function
ifx_grid_connect() procedure
ifx_grid_copy() procedure
ifx_grid_disconnect() procedure
ifx_grid_execute() procedure
ifx_grid_function() function
ifx_grid_procedure() procedure
ifx_grid_redo() procedure
ifx_grid_release() function
ifx_grid_remove() function
ifx_grid_purge() procedure
ifx_gridquery_skipped_nodes() function
ifx_gridquery_skipped_node_count() function
ifx_node_id() function
ifx_node_name() function
Enterprise Replication routines
ifx_get_erstate() function
ifx_set_erstate() procedure
onstat -g commands for Enterprise Replication
Threads shown by the onstat -g ath command
onstat -g cat: Print ER global catalog information
onstat -g cdr: Print ER statistics
onstat -g cdr config
onstat -g ddr
onstat -g dss: Print statistics for data sync threads
onstat -g dtc: Print statistics about delete table cleaner
onstat -g grp
onstat -g nif: Print statistics about the network interface
onstat -g que: Print statistics for all ER queues
onstat -g rcv: Print statistics about the receive manager
onstat -g rep
onstat -g rqm
onstat -g sync
syscdr Tables
The replcheck_stat Table
The replcheck_stat_node Table
SMI Table Reference
The syscdr_ats Table
The syscdr_atsdir Table
The syscdr_ddr Table
The syscdr_nif Table
The syscdr_rcv Table
The syscdr_ris Table
The syscdr_risdir Table
The syscdr_rqm Table
The syscdr_rqmhandle Table
The syscdr_rqmstamp Table
The syscdr_state Table
The syscdrack_buf Table
The syscdrack_txn Table
The syscdrctrl_buf Table
The syscdrctrl_txn Table
The syscdrerror Table
The syscdrlatency Table
The syscdrpart Table
The syscdrprog Table
The syscdrq Table
The syscdrqueued Table
The syscdrrecv_buf Table
The syscdrrecv_stats Table
The syscdrrecv_txn Table
The syscdrrepl Table
The syscdrreplset Table
The syscdrs Table
The syscdrsend_buf Table
The syscdrsend_txn Table
The syscdrserver Table
The syscdrsync_buf Table
The syscdrsync_txn Table
The syscdrtsapply table
The syscdrtx Table
Enterprise Replication Queues
Columns of the Transaction Tables
Columns of the Buffer Tables
Replication Examples
Replication Example Environment
Primary-Target Example
Update-Anywhere Example
Hierarchy Example
Data sync warning and error messages
System administration
List of utilities
Administrator's Guide
The database server
Overview of database server configuration and administration
Database server concepts
Environment configuration
Database server configuration
Storage space creation and management
Automatic performance tuning
Feature configuration
Connectivity configuration
Limit session resources
Automate startup and shutdown on UNIX
Automate startup on Windows
Database server maintenance tasks
Client/server communication
Client/server architecture
Network protocol
Network programming interface
Windows network domain
Database server connections
Supporting multiplexed connections
Connections that the database server supports
Local connections
Shared-memory connections (UNIX)
Stream-pipe connections (UNIX and Linux)
Named-pipe connections (Windows)
Local-loopback connections
Communication support services
Connectivity files
Network-configuration files
TCP/IP connectivity files
Client and server actions when a TCP/IP connection is opened
Multiple TCP/IP ports
Network security files
Trusted-host information
Trusted-user information
The netrc information
User impersonation
The sqlhosts file and the SQLHOSTS registry key
Creating the sqlhosts file with a text editor
Setting up the SQLHOSTS registry key with Setnet32 (Windows)
The sqlhosts information
IANA standard service names and port numbers in the sqlhosts.std file
sqlhosts connectivity information
sqlhosts file and SQLHOSTS registry key options
Group information
Creating a group in the sqlhosts file
Alternatives for TCP/IP connections
Informix support for IPv6 addresses
Configuration parameters related to connectivity
Connection information set in the DBSERVERNAME configuration parameter
Connection information set in the DBSERVERALIASES configuration parameter
Connection information set in the LIMITNUMSESSIONS configuration parameter
Connection information set in the NETTYPE configuration parameter
Name service maximum retention time set in the NS_CACHE configuration parameter
Connection information set in the NUMFDSERVERS configuration parameter
Connection information set in the HA_ALIAS configuration parameter
Environment variables for network connections
Automatically terminating idle connections
Distributed Relational Database Architecture (DRDA) communications
Overview of DRDA
Configuring connectivity between Informix and HCL Data Server clients
Allocating poll threads for an interface/protocol combination with the NETTYPE configuration parameter
Specify the size of the DRDA communication buffer with the DRDA_COMMBUFFSIZE configuration parameter
The DRDAEXEC thread and queries from clients
SQL and supported and unsupported data types
Display DRDA connection information
Display DRDA session information
Examples of client/server configurations
A network connection
Multiple connection types
Accessing multiple database servers
HCL Informix MaxConnect
Database server initialization
Initialization process
Database server operating modes
Users permitted to change modes
Changing database server operating modes
Specifying administration mode users
Disk, memory, and process management
Virtual processors and threads
Virtual processors
Threads
Advantages of virtual processors
Shared processing
Save memory and resources
Parallel processing
Add and drop virtual processors in online mode
Bind virtual processors to CPUs
How virtual processors service threads
Control structures
Context switching
Stacks
Queues
Ready queues
Sleep queues
Wait queues
Mutexes
Virtual processor classes
CPU virtual processors
Determine the number of CPU virtual processors needed
Run on a multiprocessor computer
Run on a single-processor computer
Add and drop CPU virtual processors in online mode
Prevent priority aging
Processor affinity
Set processor affinity with the VPCLASS configuration parameter
User-defined classes of virtual processors
Determine the number of user-defined virtual processors needed
User-defined virtual processors
Specify user-defined virtual processors
Assign a UDR to a user-defined virtual-processor class
Add and drop user-defined virtual processors in online mode
Tenant virtual processor class
Java virtual processors
Disk I/O virtual processors
I/O priorities
Logical-log I/O
Physical-log I/O
Asynchronous I/O
Kernel-asynchronous I/O
AIO virtual processors
Network virtual processors
Specifying Network Connections
Run poll threads on CPU or network virtual processors
Specify the number of networking virtual processors
Specify listen and poll threads for the client/server connection
Fast polling
Multiple listen threads
Add listen threads
Add a network-interface card
Dynamically starting, stopping, or restarting a listen thread
Communications support module virtual processor
Encrypt virtual processors
Audit virtual processor
Miscellaneous virtual processor
Basic text search virtual processors
MQ messaging virtual processor
Web feature service virtual processor
XML virtual processor
Manage virtual processors
Set virtual-processor configuration parameters
Start and stop virtual processors
Add virtual processors in online mode
Add virtual processors in online mode with onmode
Add network virtual processors
Drop CPU and user-defined virtual processors
Monitor virtual processors
Monitor virtual processors with command-line utilities
The onstat -g ath command
The onstat -g glo command
The onstat -g ioq command
The onstat -g rea command
Monitor virtual processors with SMI tables
Shared memory
Shared memory
Shared-memory use
Shared-memory allocation
Shared-memory size
Action to take if SHMTOTAL is exceeded
Processes that attach to shared memory
How a client attaches to the communications portion (UNIX)
How utilities attach to shared memory
How virtual processors attach to shared memory
Obtain key values for shared-memory segments
Specify where to attach the first shared-memory segment
Attach additional shared-memory segments
Define the shared-memory lower-boundary address
Resident portion of shared memory
Shared-memory header
Logical-log buffer
Physical-log buffer
High-Availability Data-Replication buffer
Lock table
Buffer pool portion of shared memory
Virtual portion of shared memory
Management of the virtual portion of shared memory
Size of the virtual portion of shared memory
Components of the virtual portion of shared memory
Shared-memory internal tables
Buffer table
Chunk table
Dbspace table
Page-cleaner table
Tblspace table
Transaction table
User table
Big buffers
Session data
Thread data
Stacks
Heaps
Data-distribution cache
Dictionary cache
SQL statement cache
Sort memory
SPL routine and the UDR cache
Global pool
Communications portion of shared memory (UNIX)
Virtual-extension portion of shared memory
Concurrency control
Shared-memory mutexes
Shared-memory buffer locks
Types of buffer locks
Share lock
Exclusive lock
Database server thread access to shared buffers
FIFO/LRU queues
Components of LRU queue
Pages in least-recently used order
LRU queues and buffer-pool management
Number of LRU queues to configure
Number of cleaners to allocate
Number of pages added to the MLRU queues
End of MLRU cleaning
Read-ahead operations
Database server thread access to buffer pages
Flush data to disk
Flush buffer-pool buffers
Flush before-images first
Flush the physical-log buffer
Synchronize buffer flushing
Types of writes during flushing
Foreground write
LRU write
Chunk write
Flush the logical-log buffer
After a transaction is prepared or terminated in a database with unbuffered logging
When a session that uses nonlogging databases or unbuffered logging terminates
When a checkpoint occurs
When a page is modified that does not require a before-image in the physical-log file
Buffer large-object data
Write simple large objects
Blobpages and shared memory
Creation of simple large objects
Creation of blobpage buffers
Access smart large objects
Memory use on 64-bit platforms
Manage shared memory
Set operating-system shared-memory configuration parameters
Maximum shared-memory segment size
Using more than two gigabytes of memory (Windows)
Maximum number of shared-memory identifiers (UNIX)
Semaphores (UNIX)
Set database server shared-memory configuration parameters
Set SQL statement cache parameters
Set up shared memory
Turn residency on or off for resident shared memory
Turn residency on or off in online mode
Turn residency on or off when restarting the database server
Add a segment to the virtual portion of shared memory
Reserve memory for critical activities
Configure the server response when memory is critically low
Scenario for maintaining a targeted amount of memory
Monitor shared memory
Monitor shared-memory segments
Monitor the shared-memory profile and latches
Command-line utilities to monitor shared memory and latches
SMI tables
Monitor buffers
Deleting shared memory segments after a server failure
Data storage
Chunks
Disk allocation for chunks
Disk access on Windows
Unbuffered or buffered disk access on UNIX
Extendable chunks
Partitions and offsets
Pages
Blobpages
Sbpages
Extents
Dbspaces
Control of where simple large object data is stored
Root dbspace
Temporary dbspaces
Blobspaces
Sbspaces
Advantages of using sbspaces
Sbspaces and Enterprise Replication
Metadata, user data, and reserved area
Control of where smart large object data is stored
Storage characteristics of sbspaces
Extent sizes for sbspaces
Average smart-large-object size
Buffering mode
Last-access time
Lock mode
Logging
Levels of inheritance for sbspace characteristics
More information about sbspaces
Temporary sbspaces
Comparison of temporary and standard sbspaces
Temporary smart large objects
Plogspace
Extspaces
Databases
Tables
Damaged tables
Table types for Informix
Standard permanent tables
RAW tables
Temp tables
Properties of table types
Loading of data into a table
Fast recovery of table types
Backup and restore of RAW tables
Temporary tables
Temporary tables that you create
Where user-created temporary tables are stored
Temporary tables that the database server creates
Where database server-created temporary tables are stored
Tblspaces
Maximum number of tblspaces in a table
Table and index tblspaces
Extent interleaving
Table fragmentation and data storage
Amount of disk space needed to store data
Size of the root dbspace
Amount of space that databases require
The storage pool
Disk-layout guidelines
Dbspace and chunk guidelines
Table-location guidelines
Sample disk layouts
Logical-volume manager
Manage disk space
Allocate disk space
Specify an offset
Specify an offset for the initial chunk of root dbspace
Specify an offset for additional chunks
Use offsets to create multiple chunks
Allocating cooked file spaces on UNIX
Allocating raw disk space on UNIX
Create symbolic links to raw devices (UNIX)
Allocating NTFS file space on Windows
Allocating raw disk space on Windows
Specify names for storage spaces and chunks
Specify the maximum size of chunks
Specify the maximum number of chunks and storage spaces
Back up after you change the physical schema
Monitor storage spaces
Manage dbspaces
Creating a dbspace that uses the default page size
Specifying the first and next extent sizes for the tblspace tblspace
Creating a dbspace with a non-default page size
Improving the performance of cooked-file dbspaces by using direct I/O
Storing multiple named fragments in a single dbspace
Creating a temporary dbspace
What to do if you run out of disk space
Adding a chunk to a dbspace or blobspace
Rename dbspaces
Additional actions that may be required after you rename a dbspace
Managing automatic location and fragmentation
Manage blobspaces
Creating a blobspace
Prepare blobspaces to store TEXT and BYTE data
Determine blobpage size
Determine database server page size
Obtain blobspace storage statistics
Manage sbspaces
Creating an sbspace
Size sbspace metadata
Adding a chunk to an sbspace
Alter storage characteristics of smart large objects
Creating a temporary sbspace
Manage the plogspace
Automatic space management
Creating and managing storage pool entries
Marking a chunk as extendable or not extendable
Modifying the sizes of an extendable storage space
Changing the threshold and wait time for the automatic addition of more space
Configuring the frequency of the monitor low storage task
Manually expanding a space or extending an extendable chunk
Example of minimally configuring for and testing the automatic addition of more space
Example of configuring for the automatic addition of more space
Drop a chunk
Verify whether a chunk is empty
Drop a chunk from a dbspace with onspaces
Drop a chunk from a blobspace
Drop a chunk from an sbspace with onspaces
The -f (force) option
Delete smart large objects without any pointers
Drop a storage space
Preparation for dropping a storage space
Drop a mirrored storage space
Drop a storage space with onspaces
Back up after dropping a storage space
Creating a space or chunk from the storage pool
Returning empty space to the storage pool
Manage extspaces
Create an extspace
Drop an extspace
Skip inaccessible fragments
The DATASKIP configuration parameter
The dataskip feature of onspaces
Use onstat to check dataskip status
The SQL statement SET DATASKIP
Effect of the dataskip feature on transactions
Determine when to use dataskip
Determine when to skip selected fragments
Determine when to skip all fragments
Monitor fragmentation use
Display databases
SMI tables
Monitor disk usage
Monitor chunks
The onstat -d utility
The onstat -d update option
The onstat -D option
Monitor chunk I/O activity with the onstat -g iof command
The oncheck -pr command
The oncheck -pe command
SMI tables
Monitor tblspaces and extents
SMI tables
Monitor simple large objects in a blobspace
Determine blobpage fullness with oncheck -pB
Monitor blobspace usage with oncheck -pe
Monitor simple large objects in a dbspace with oncheck -pT
Monitor sbspaces
The onstat -d option
The oncheck -ce and oncheck -pe options
The oncheck -cs option
The oncheck -ps option
Monitoring the metadata and user-data areas
Multitenancy
Creating a tenant database
Managing tenant databases
Restoring a tenant database to a point in time
Storage optimization
Storage optimization methods
Scheduling data optimization
Example: Optimizing data storage on demand
Partition defragmentation
Compression
Data that you can compress
Data that you cannot compress
B-tree index compression
Compression ratio estimates
Compression dictionaries
Tools for moving compressed data
BLOBspace Blob Compression
Methods for viewing compression information
Load data into a table
Moving data with external tables
External tables
Defining external tables
Map columns to other columns
Load data from and unload to a named pipe
Loading data with named pipes
FIFO virtual processors
Unloading data with named pipes
Copying data from one instance to another using the PIPE option
Monitor the load or unload operations
Monitor frequent load and unload operations
Monitor FIFO virtual processors
External tables in high-availability cluster environments
System catalog entries for external tables
Performance considerations when using external tables
Manage errors from external table load and unload operations
Reject files
External table error messages
Recoverability of table types for external tables
Storage space encryption
Creating encrypted or unencrypted storage spaces
Changing storage space encryption during a restore
Monitoring the encryption of storage spaces
Logging and log administration
Logging
Database server processes that require logging
Transaction logging
Logging of SQL statements and database server activity
Activity that is always logged
Activity logged for databases with transaction logging
Activity that is not logged
Database-logging status
Unbuffered transaction logging
Buffered transaction logging
ANSI-compliant transaction logging
No database logging
Databases with different log-buffering status
Database logging in an X/Open DTP environment
Settings or changes for logging status or mode
Manage the database-logging mode
Change the database-logging mode
Modify the database-logging mode with ondblog
Change the buffering mode with ondblog
Cancel a logging mode change with ondblog
End logging with ondblog
Make a database ANSI compliant with ondblog
Changing the logging mode of an ANSI-compliant database
Modify the database logging mode with ontape
Turn on transaction logging with ontape
End logging with ontape
Change buffering mode with ontape
Make a database ANSI compliant with ontape
Modify the table-logging mode
Alter a table to turn off logging
Alter a table to turn on logging
Disable logging on temporary tables
Monitor transactions
Monitor the logging mode of a database
Monitor the logging mode with SMI tables
Logical log
What is the logical log?
Location of logical-log files
Identification of logical-log files
Status flags of logical-log files
Size of the logical-log file
Number of logical-log files
Performance considerations
Dynamic log allocation
Freeing of logical-log files
Action if the next logical-log file is not free
Action if the next log file contains the last checkpoint
Log blobspaces and simple large objects
Switch log files to activate blobspaces
Back up log files to free blobpages
Back up blobspaces after inserting or deleting TEXT and BYTE data
Log sbspaces and smart large objects
Sbspace logging
Logging for smart large objects
Logging for updated smart large objects
Turn logging on or off for an sbspace
Smart-large-object log records
Prevent long transactions when logging smart-large-object data
Logging process
Dbspace logging
Blobspace logging
Manage logical-log files
Estimate the size and number of log files
Estimate the log size when logging smart large objects
Estimate the number of logical-log files
Back up logical-log files
Backing up blobspaces
Back up sbspaces
Switch to the next logical-log file
Free a logical-log file
Delete a log file with status D
Free a log file with status U
Freeing a log file with status U-B or F
Freeing a log file with status U-C or U-C-L
Free a log file with status U-B-L
Monitor logging activity
Monitor the logical log for fullness
The onstat -l command
The oncheck -pr command
Monitor temporary logical logs
SMI tables
Monitor log-backup status
Allocate logical log files
Dynamically add a logical-log file to prevent transaction blocking
Size and number of dynamically added log files
Location of dynamically added logical log files
Monitor events for dynamically added logs
Dynamically add logical logs for performance
Adding logical-log files manually
Dropping logical-log files
Change the size of logical-log files
Move logical-log files
Display logical-log records
Controlling long transactions
Physical logging, checkpoints, and fast recovery
Critical sections
Physical logging
Fast recovery use of physically-logged pages
Backup use of physically-logged pages
Database server activity that is physically logged
Physical recovery messages
Physical logging and simple large objects
Physical logging and smart large objects
Size and location of the physical log
Strategy for estimating the size of the physical log
Physical-log overflow when transaction logging is turned off
Checkpoints
LRU values for flushing a buffer pool between checkpoints
Checkpoints during backup
Fast recovery
Need for fast recovery
Situations when fast recovery is initiated
Fast recovery and buffered logging
Possible physical log overflow during fast recovery
Fast recovery and no logging
Fast recovery after a checkpoint
The server returns to the last-checkpoint state
The server locates the checkpoint record in the logical log
The server rolls forward logical-log records
The server rolls back uncommitted transactions
Manage the physical log
Change the physical-log location and size
Monitor physical and logical-logging activity
Monitor checkpoint information
Turn checkpoint tuning on or off
Force a checkpoint
Server-provided checkpoint statistics
SMI tables
Turn automatic LRU tuning on or off
Fault tolerance
Mirroring
Mirroring
Benefits of mirroring
Costs of mirroring
Consequences of not mirroring
Data to mirror
Alternatives to mirroring
Logical volume managers
Hardware mirroring
External backup and restore
Mirroring process
Creation of a mirror chunk
Mirror status flags
Recovery
Actions during processing
Disk writes to mirror chunks
Disk reads from mirror chunks
Detection of media failures
Chunk recovery
Result of stopping mirroring
Structure of a mirror chunk
Using mirroring
Preparing to mirror data
Enable the MIRROR configuration parameter
Allocate disk space for mirrored data
Link chunks (UNIX)
Relink a chunk to a device after a disk failure
Using mirroring
Mirroring the root dbspace during initialization
Change the mirror status
Manage mirroring
Start mirroring for unmirrored storage spaces
Start mirroring for unmirrored dbspaces using onspaces
Start mirroring for new storage spaces
Start mirroring for new spaces using onspaces
Add mirror chunks
Add mirror chunks using onspaces
Swap mirror chunk
Take down a mirror chunk
Take down mirror chunks using onspaces
Recover a mirror chunk
Recover a mirror chunk using onspaces
End mirroring
End mirroring using onspaces
Consistency checking
Perform periodic consistency checking
Verify consistency
Validate system catalog tables
Validate data pages
Validate extents
Validate indexes
Validate logical logs
Validate reserved pages
Validate metadata
Monitor for data inconsistency
Read assertion failures in the message log and dump files
Validate table and tblspace data
Retain consistent level-0 backups
Deal with corruption
Find symptoms of corruption
Fix index corruption
Fix I/O errors on a chunk
Collect diagnostic information
Disable I/O errors
Monitor the database server for disabling I/O errors
The message log to monitor disabling I/O errors
Event alarms to monitor disabling I/O errors
No bad-sector mapping
High availability and scalability
Strategies for high availability and scalability
Components supporting high availability and scalability
Advantages of data replication
Clustering versus mirroring
Clustering versus two-phase commit
Type of data replicated in clusters
Primary and secondary database servers
Transparent scaling and workload balancing strategies
High availability strategies
High-availability cluster configuration
Plan for a high-availability cluster
Configuring clusters
Hardware and operating-system requirements for clusters
Database and data requirements for clusters
Database server configuration requirements for clusters
Database server version
Storage space and chunk configuration
Non-default page sizes in an HDR environment
Mirroring
Physical-log configuration
Dbspace and logical-log tape backup devices
Logical-log configuration
High-availability cluster configuration parameters
Cluster transaction coordination
Configuring secure connections for high-availability clusters
Starting HDR for the First Time
Decrease setup time using the ontape STDIO feature
Remote standalone secondary servers
Comparison of RS secondary servers and HDR secondary servers
Index page logging
How index page logging works
Enable or disable index page logging
View index page logging statistics
Starting an RS secondary server for the first time
Decrease setup time through an alternative backup method
Converting an offline primary server to an RS secondary server
Delayed application of log records
Specifying the log staging directory
Delay application of log records on an RS secondary server
Stop the application of log records
Flow control for remote standalone secondary servers
Shared disk secondary servers
SD secondary server
Disk requirements for SD secondary servers
Setting up a shared disk secondary server
Obtain SD secondary server statistics
Promote an SD secondary server to a primary server
Convert a primary server to a standard server
SD secondary server security
Flow control for shared-disk secondary servers
Cluster administration
How data replication works
How data initially replicates
Replication of primary-server data to secondary servers
Fully synchronous mode for HDR replication
Nearly synchronous mode for HDR replication
Asynchronous mode for HDR replication
Lost-and-found transactions
Data replication configuration examples
Remote standalone secondary configuration examples
Shared disk secondary configuration examples
Enterprise Replication as part of the recoverable group
High-availability clusters with Enterprise Replication configuration example
Example of a complex failover recovery strategy
Troubleshooting high-availability cluster environments
Design data replication group clients
Use of temporary dbspaces for sorting and temporary tables
Performing basic administration tasks
Changing the configuration parameters for an HDR replication pair
Back up storage spaces and logical-log files
Changing the logging mode of databases
Add and drop chunks and storage spaces
Renaming chunks
Saving chunk status on the secondary database server
Use and change mirroring of chunks
Manage the physical log
Manage the logical log
Manage virtual processors
Manage shared memory
Configure SMX connections
Replicate an index to an HDR secondary database server
Encrypting data traffic between HDR database servers
Adjust LRU flushing and automatic tuning in HDR server pairs
Cloning a primary server
Creating a clone of a primary server
Database updates on secondary servers
Isolation levels on secondary servers
Set lock mode
Transient types on high-availability cluster secondary servers
Row versioning
Backup and restore with high-availability clusters
Change the database server mode
Changing the database server type
Prevent blocking checkpoints on HDR servers
View statistics for nonblocking checkpoints on HDR servers
Monitor HDR status
Command-line utilities
The onstat -g dri option
The oncheck -pr option
SMI tables
Obtain RS secondary server statistics
Remove an RS secondary server
RS secondary server security
Create or change a password on an RS secondary server
Transaction completion during cluster failover
Configuring the server so that transactions complete after failover
Connection management through the Connection Manager
Configuring connection management
Creating Connection Manager configuration files
Parameters and format of the Connection Manager configuration file
CMALARMPROGRAM Connection Manager configuration parameter
CM_TIMEOUT Connection Manager configuration parameter
CLUSTER Connection Manager configuration parameter
DEBUG Connection Manager configuration parameter
EVENT_TIMEOUT Connection Manager configuration parameter
FOC Connection Manager configuration parameter
GRID Connection Manager configuration parameter
INFORMIXSERVER Connection Manager configuration parameter
LOCAL_IP Connection Manager configuration parameter
LOG Connection Manager configuration parameter
LOGFILE Connection Manager configuration parameter
MACRO Connection Manager configuration parameter
NAME Connection Manager configuration parameter
REPLSET Connection Manager configuration parameter
SECONDARY_EVENT_TIMEOUT Connection Manager configuration parameter
SERVERSET Connection Manager configuration parameter
SLA Connection Manager configuration parameter
SQLHOSTS Connection Manager configuration parameter
SSL_LABEL Connection Manager configuration parameter
Modifying Connection Manager configuration files
Converting older formats of the Connection Manager configuration file to the current format
Configuring environments and setting configuration parameters for connection management
Defining sqlhosts information for connection management
Defining sqlhosts information for connection management of high-availability clusters
sqlhosts for HA clusters that use secure ports
sqlhosts for HA clusters that use DRDA
sqlhosts for HA clusters that use DRDA and secure ports
sqlhosts for grids and replicate sets
sqlhosts for grids and replicate sets that use secure ports
sqlhosts for HA replication systems
sqlhosts for HA replication systems that use secure ports
sqlhosts for server sets
Creating a password file for connecting to database servers on untrusted networks
Modifying encrypted password information
Starting Connection Managers on UNIX and Linux
Starting Connection Managers on Windows
Stopping connection management
Monitoring and troubleshooting connection management
Strategies for increasing availability with Connection Managers
Configuration examples for connection management
Example of configuring connection management for a high-availability cluster
Example of configuring connection management for a grid or replicate set
Example of configuring connection management for a high-availability replication system
Example: Configuring connection management for untrusted networks
Example: Configuring for prioritizing connections and network monitoring
Example: Configuring for an SSL connection
Example: Using the OpenSSL encryption library
Example: Using the GSKit encryption library
Configuring a CM to connect to Oninit using SSL
Configuring a client to connect to a CM using SSL
Cluster failover, redirection, and restoration
Failover configuration for high-availability clusters
Failover with ISV cluster management software
I/O fencing for shared file systems
Cluster failures
Automatic switchover
Automatic switchover without a reliable network
Manual switchover
Connecting offline servers to the new primary server
Redirection and connectivity for data-replication clients
Redirecting clients automatically with the DBPATH environment variable
How the DBPATH redirection method works
Redirecting clients with the connectivity information
Changing client connectivity information
Connecting to the database server
Automatic redirection with server groups
Redirecting clients with the INFORMIXSERVER environment variable
Redirecting clients with application code
Comparison of redirection methods
Recover HDR and RS clusters after failure
Recovering a cluster after critical data is damaged
Restarting HDR or RS clusters after a network failure
Restarting HDR or RS clusters if the secondary server fails
Recovering an HDR cluster after the secondary server became the primary server
Restart if the primary server fails
Recovering a shared-disk cluster after data is damaged
Recovering an SD cluster after the secondary server became the primary server
Distributed data
Multiphase commit protocols
Transaction managers
TP/XA Library with a transaction manager
Microsoft Transaction Server (MTS/XA)
Informix transaction support for XA-compliant, external data sources
XA in high-availability clusters
Loosely-coupled and tightly-coupled modes
Two-phase commit protocol
When the two-phase commit protocol is used
Two-phase commit concepts
Phases of the two-phase commit protocol
Precommit phase
Postdecision phase
How the two-phase commit protocol handles failures
Types of failures that automatic recovery handles
Administrator's role in automatic recovery
Automatic-recovery mechanisms for coordinator failure
Automatic-recovery mechanisms for participant failure
Presumed-end optimization
Independent actions
Situations that initiate independent action
Possible results of independent action
Independent actions that allow transactions to complete successfully
Independent actions that result in an error condition
Independent actions that result in heuristic decisions
The heuristic rollback scenario
Conditions that result in a heuristic rollback
Condition 1: Logical log fills to a high-watermark
Condition 2: System administrator executes onmode -z
Results of a heuristic rollback
Situation 1: Coordinator issues a commit and all participants report heuristic rollbacks
Situation 2: Coordinator issued a commit; one participant commits and one reports a heuristic rollback
The heuristic end-transaction scenario
When to perform a heuristic end transaction
How to use onmode -Z
Action when the transaction is ended heuristically
Monitor a global transaction
Two-phase commit protocol errors
Two-phase commit and logical-log records
Logical-log records when the transaction commits
Logical-log records written during a heuristic rollback
Logical-log records written after a heuristic end transaction
Configuration parameters used in two-phase commits
Function of the DEADLOCK_TIMEOUT parameter
Function of the TXTIMEOUT parameter
Heterogeneous commit protocol
Gateways that can participate in a heterogeneous commit transaction
Enable and disable of heterogeneous commit
How heterogeneous commit works
Precommit phase
Gateway commit phase
Heterogeneous commit optimization
Implications of a failed heterogeneous commit
Database server coordinator failure
Participant failure
Interpretation of heterogeneous commit error messages
Application attempts to update multiple gateway participants
Failed attempt to commit distributed transaction using heterogeneous commit
Manually recovering from failed two-phase commit
Determine if manual recovery is required
Determine if a transaction was implemented inconsistently
Global transaction ended prematurely
Heuristic end transaction
Heuristic rollback
Determine if the distributed database contains inconsistent data
Obtaining information from the logical log
Obtain the global transaction identifier
Decide if action is needed to correct the situation
Example of manual recovery
Overview of automatic monitoring and corrective actions
The Scheduler
Scheduler tables
Built-in tasks and sensors
Creating a task
Creating a sensor
Actions for task and sensors
Creating a group
Creating a threshold
Creating an alert
Monitor the scheduler
Modifying the scheduler
Remote administration with the SQL administration API
SQL administration API admin() and task() functions
Viewing SQL administration API history
Controlling the size of the command_history table
Query drill-down
Specifying startup SQL tracing information by using the SQLTRACE configuration parameter
Disable SQL tracing globally or for a session
Enable SQL tracing
Enable global SQL tracing for a session
HCL Informix server licensing
FlexNet Licensing Overview
Licensing configuration
System startup
System alerts when licenses are not available
High Availability server instances
Monitoring the currently held licenses
FlexNet Operations portal
The REST interface
Security
Local License Server
Informix Warehouse Accelerator
Administrator's Reference
Configuring and monitoring Informix
Database configuration parameters
onconfig file
Modifying the onconfig file
Displaying the settings in the onconfig file
onconfig Portal: Configuration parameters by functional category
ADMIN_MODE_USERS configuration parameter
ADMIN_USER_MODE_WITH_DBSA configuration parameter
ALARMPROGRAM configuration parameter
ALLOW_NEWLINE configuration parameter
ALRM_ALL_EVENTS configuration parameter
AUTO_AIOVPS configuration parameter
AUTO_CKPTS configuration parameter
AUTO_LLOG configuration parameter
AUTO_TUNE_SERVER_SIZE configuration parameter
AUTO_LRU_TUNING configuration parameter
AUTO_READAHEAD configuration parameter
AUTO_REPREPARE configuration parameter
AUTO_STAT_MODE configuration parameter
AUTO_TUNE configuration parameter
AUTOLOCATE configuration parameter
BATCHEDREAD_INDEX configuration parameter
BATCHEDREAD_TABLE configuration parameter
BLOCKTIMEOUT configuration parameter
BTSCANNER Configuration Parameter
BUFFERPOOL configuration parameter
CHECKALLDOMAINSFORUSER configuration parameter
CKPTINTVL configuration parameter
CLEANERS configuration parameter
CLUSTER_TXN_SCOPE configuration parameter
CONSOLE configuration parameter
CONVERSION_GUARD configuration parameter
DATASKIP Configuration Parameter
DBCREATE_PERMISSION configuration parameter
DB_LIBRARY_PATH configuration parameter
DBSERVERALIASES configuration parameter
DBSERVERNAME configuration parameter
DBSPACETEMP configuration parameter
Use Hash Join Overflow and DBSPACETEMP
DD_HASHMAX configuration parameter
DD_HASHSIZE configuration parameter
DEADLOCK_TIMEOUT configuration parameter
DEF_TABLE_LOCKMODE configuration parameter
DEFAULTESCCHAR configuration parameter
DELAY_APPLY Configuration Parameter
DIRECT_IO configuration parameter (UNIX)
DIRECTIVES configuration parameter
DISABLE_B162428_XA_FIX configuration parameter
DISK_ENCRYPTION configuration parameter
DRDA_COMMBUFFSIZE configuration parameter
DRAUTO configuration parameter
DRIDXAUTO configuration parameter
DRINTERVAL configuration parameter
DRLOSTFOUND configuration parameter
DRTIMEOUT configuration parameter
DS_HASHSIZE configuration parameter
DS_MAX_QUERIES configuration parameter
DS_MAX_SCANS configuration parameter
DS_NONPDQ_QUERY_MEM configuration parameter
DS_POOLSIZE configuration parameter
DS_TOTAL_MEMORY configuration parameter
Algorithm for DS_TOTAL_MEMORY
DUMPCNT configuration parameter (UNIX)
DUMPCORE configuration parameter (UNIX)
DUMPDIR configuration parameter
DUMPGCORE configuration parameter (UNIX)
DUMPSHMEM configuration parameter (UNIX)
DYNAMIC_LOGS configuration parameter
EILSEQ_COMPAT_MODE configuration parameter
ENABLE_SNAPSHOT_COPY configuration parameter
ENCRYPT_CIPHERS configuration parameter
ENCRYPT_HDR configuration parameter
ENCRYPT_MAC configuration parameter
ENCRYPT_MACFILE configuration parameter
ENCRYPT_SMX configuration parameter
ENCRYPT_SWITCH configuration parameter
EXPLAIN_STAT configuration parameter
EXT_DIRECTIVES configuration parameter
EXTSHMADD configuration parameter
FAILOVER_CALLBACK configuration parameter
FAILOVER_TX_TIMEOUT configuration parameter
FASTPOLL configuration parameter
FILLFACTOR configuration parameter
FULL_DISK_INIT configuration parameter
GSKIT_VERSION configuration parameter
HA_ALIAS configuration parameter
HA_FOC_ORDER configuration parameter
HDR_TXN_SCOPE configuration parameter
HETERO_COMMIT configuration parameter
IFX_EXTEND_ROLE configuration parameter
IFX_FOLDVIEW configuration parameter
IFX_XA_UNIQUEXID_IN_DATABASE configuration parameter
INFORMIXCONRETRY configuration parameter
INFORMIXCONTIME configuration parameter
LICENSE_SERVER configuration parameter
LICENSE_TIMING configuration parameter
LIMITNUMSESSIONS configuration parameter
LISTEN_TIMEOUT configuration parameter
LOCKS configuration parameter
LOGBUFF configuration parameter
LOGBUF_INTVL configuration parameter
LOGFILES configuration parameter
LOG_INDEX_BUILDS configuration parameter
LOG_STAGING_DIR configuration parameter
LOGSIZE configuration parameter
LOW_MEMORY_MGR configuration parameter
LOW_MEMORY_RESERVE configuration parameter
LTXEHWM configuration parameter
LTXHWM configuration parameter
MAX_FILL_DATA_PAGES configuration parameter
MAX_INCOMPLETE_CONNECTIONS configuration parameter
MAX_PDQPRIORITY configuration parameter
MIRROR configuration parameter
MIRROROFFSET configuration parameter
MIRRORPATH configuration parameter
MSG_DATE configuration parameter
MSGPATH configuration parameter
MULTIPROCESSOR configuration parameter
NET_IO_TIMEOUT_ALARM configuration parameter
NETTYPE configuration parameter
NS_CACHE configuration parameter
NUMFDSERVERS configuration parameter
OFF_RECVRY_THREADS configuration parameter
ON_RECVRY_THREADS configuration parameter
ONDBSPACEDOWN configuration parameter
Database Server Behavior When ONDBSPACEDOWN Does Not Apply
ONLIDX_MAXMEM configuration parameter
OPTCOMPIND configuration parameter
OPT_GOAL configuration parameter
PC_HASHSIZE configuration parameter
PC_POOLSIZE configuration parameter
PFSC_BOOST configuration parameter
PHYSBUFF configuration parameter
PHYSFILE configuration parameter
PLOG_OVERFLOW_PATH configuration parameter
PLCY_HASHSIZE configuration parameter
PLCY_POOLSIZE configuration parameter
PN_STAGEBLOB_THRESHOLD configuration parameter
PRELOAD_DLL_FILE configuration parameter
QSTATS configuration parameter
REMOTE_SERVER_CFG configuration parameter
REMOTE_USERS_CFG configuration parameter
RESIDENT configuration parameter
RESTARTABLE_RESTORE configuration parameter
RESTORE_POINT_DIR configuration parameter
ROOTNAME configuration parameter
ROOTOFFSET configuration parameter
ROOTPATH configuration parameter
ROOTSIZE configuration parameter
RSS_FLOW_CONTROL configuration parameter
RSS_NONBLOCKING_CKPT configuration parameter
RTO_SERVER_RESTART configuration parameter
S6_USE_REMOTE_SERVER_CFG configuration parameter
SB_CHECK_FOR_TEMP configuration parameter
SBSPACENAME configuration parameter
SBSPACETEMP configuration parameter
SDS_ALTERNATE configuration parameter
SDS_ENABLE configuration parameter
SDS_FLOW_CONTROL configuration parameter
SDS_LOGCHECK configuration parameter
SDS_PAGING configuration parameter
SDS_TEMPDBS configuration parameter
SDS_TIMEOUT configuration parameter
SEC_APPLY_POLLTIME configuration parameter
SEC_DR_BUFS configuration parameter
SEC_LOGREC_MAXBUFS configuration parameter
SECURITY_LOCALCONNECTION configuration parameter
SEQ_CACHE_SIZE configuration parameter
SERVERNUM configuration parameter
SESSION_LIMIT_LOCKS configuration parameter
SESSION_LIMIT_LOGSPACE configuration parameter
SESSION_LIMIT_MEMORY configuration parameter
SESSION_LIMIT_TEMPSPACE configuration parameter
SESSION_LIMIT_TXN_TIME configuration parameter
SHMADD configuration parameter
SHMBASE configuration parameter
SHMNOACCESS configuration parameter
SHMTOTAL configuration parameter
SHMVIRT_ALLOCSEG configuration parameter
SHMVIRTSIZE configuration parameter
SINGLE_CPU_VP configuration parameter
VPCLASS Values and the SINGLE_CPU_VP Configuration Parameter
SMX_COMPRESS configuration parameter
SMX_NUMPIPES configuration parameter
SMX_PING_INTERVAL configuration parameter
SMX_PING_RETRY configuration parameter
SP_AUTOEXPAND configuration parameter
SP_THRESHOLD configuration parameter
SP_WAITTIME configuration parameter
SQL_LOGICAL_CHAR configuration parameter
SQLTRACE configuration parameter
SSL_KEYSTORE_LABEL configuration parameter
STACKSIZE configuration parameter
STATCHANGE configuration parameter
STMT_CACHE configuration parameter
STMT_CACHE_HITS configuration parameter
STMT_CACHE_NOLIMIT configuration parameter
STMT_CACHE_NUMPOOL configuration parameter
STMT_CACHE_QUERY_PLAN configuration parameter
STMT_CACHE_SIZE configuration parameter
STOP_APPLY configuration parameter
STORAGE_FULL_ALARM configuration parameter
SYSALARMPROGRAM configuration parameter
SYSSBSPACENAME configuration parameter
TBLSPACE_STATS configuration parameter
TBLTBLFIRST configuration parameter
TBLTBLNEXT configuration parameter
TEMPTAB_NOLOG configuration parameter
TENANT_LIMIT_CONNECTIONS configuration parameter
TENANT_LIMIT_MEMORY configuration parameter
TENANT_LIMIT_SPACE configuration parameter
TLS_VERSION configuration parameter
TXTIMEOUT configuration parameter
UNSECURE_ONSTAT configuration parameter
UPDATABLE_SECONDARY configuration parameter
USELASTCOMMITTED configuration parameter
USEOSTIME configuration parameter
USERMAPPING configuration parameter (UNIX, Linux)
USRC_HASHSIZE configuration parameter
USRC_POOLSIZE configuration parameter
USTLOW_SAMPLE configuration parameter
VP_MEMORY_CACHE_KB configuration parameter
VPCLASS configuration parameter
VP_KAIO_PERCENT configuration parameter
WSTATS configuration parameter
The sysmaster database
The sysmaster Database
The buildsmi Script
The bldutil.sh Script
The System-Monitoring Interface
Understanding the SMI Tables
Accessing SMI tables
SELECT statements
Triggers and Event Alarms
SPL and SMI Tables
Locking and SMI Tables
The System-Monitoring Interface Tables
The sysutils Tables
sysadtinfo
sysaudit
syschkio
syscheckpoint
syschunks
sysckptinfo
syscluster
syscmsm
syscmsmsla
syscmsmtab
syscmsmunit
syscompdicts_full
sysconfig
sysdatabases
sysdbslocale
sysdbspaces
sysdri
sysdual
sysenv
sysenvses
sysextents
sysextspaces
sysfeatures
sysha_lagtime Table
sysha_type
sysha_workload
sysipl
syslocks
syslogs
syslogfil table
sysmgminfo
sysnetclienttype
sysnetglobal
sysnetworkio table
sysonlinelog
sysprofile
sysproxyagents
sysproxydistributors
sysproxysessions table
sysproxytxnops table
sysproxytxns table
sysptnhdr
sysptprof table
sysrepevtreg table
sysrepstats table
User interface for sysrepstats and sysrepevtreg tables
sysrsslog
sysscblst
syssscelem
syssesappinfo
syssesprof
syssessions
syssmx
syssmxses
syssqexplain table
syssqltrace
syssqltrace_hvar
syssqltrace_info
syssqltrace_iter
syssrcrss
syssrcsds
systabnames
systhreads
systrgrss
systrgsds
sysvpprof
The SMI Tables Map
Information from onstat in the SMI Tables
The sysadmin Database
The Scheduler tables
The ph_task Table
The ph_run Table
The ph_group Table
The ph_alert Table
The ph_threshold Table
The results tables
The command_history table
The storagepool table
The tenant table
Disk Structures and Storage
Dbspace Structure and Storage
Structure of the Root Dbspace
Reserved Pages
Structure of a Regular Dbspace
Structure of an Additional Dbspace Chunk
Structure of a Mirror Chunk
Structure of the Chunk Free-List Page
Structure of the Tblspace Tblspace
Tblspace tblspace entries
Tblspace Numbers
Structure of the Database Tblspace
Database Tblspace Entries
Structure and Allocation of an Extent
Extent Structure
Extent size
Page Types Within a Table Extent
Page Types Within an Index Extent
Next-Extent Allocation
Next-Extent Size
Extent size doubling
Lack of Contiguous Space
Merge of Extents for the Same Table
Structure and Storage of a Dbspace Page
Rows in Nonfragmented Tables
Definition of Rowid
Use of Rowids
Rows in Fragmented Tables
Access to Data in Fragmented Tables with Rowid
Recommendations on Use of Rowid
Data-Row Format and Storage
Storage of Row
Location of Rows
Page Compression
Structure of Fragmented Tables
Structure of B-Tree Index Pages
Definition of B-tree terms
Logical Storage of Indexes
Creation of Root and Leaf Nodes
Creation of branch nodes
Duplicate Key Values
Key-Value Locking
Adjacent Key Locking
Freed Index Pages
Filling Indexes
Calculating the Length of Index Items
Functional Indexes
Structure of R-Tree Index Pages
Storage of Simple Large Objects
Structure of a Blobspace
Structure of a Dbspace Blobpage
Simple-Large-Object Storage and the Descriptor
Creation of Simple Large Objects
Deletion or Insertion of Simple Large Objects
Size Limits for Simple Large Objects
Blobspace Page Types
Structure of a Blobspace Blobpage
Sbspace Structure
Structure of the metadata area
Sbpage Structure
Time Stamps
Database and Table Creation: What Happens on Disk
Database Creation
Disk-Space Allocation for System Catalog Tables
Tracking of System Catalog Tables
Table Creation
Disk-Space Allocation
Entry in the Tblspace Tblspace
Entries in the System Catalog Tables
Creation of a Temporary Table
Interpreting Logical-Log Records
About Logical-Log Records
Transactions That Drop a Table or Index
Transactions That Are Rolled Back
Checkpoints with Active Transactions
Distributed Transactions
Logical-Log Record Structure
Logical-Log Record Header
Logical-log record types and additional columns
Log Record Types for Smart Large Objects
Administrative Utilities
Overview of Utilities
Obtaining utility version information
Setting local environment variables for utilities (UNIX)
The finderr utility
The genoncfg Utility
The oncheck Utility
oncheck Check-and-Repair
What Does Each Option Do?
Using the -y Option to Perform Repairs
Repairing Indexes in Sbspaces and External Spaces
Locking and oncheck
oncheck utility syntax
oncheck -cc and-pc: Check system catalog tables
oncheck -cd and oncheck -cD commands: Check pages
oncheck -ce, -pe: Check the chunk-free list
oncheck -ci and -cI: Check index node links
oncheck -cr and -cR: Check reserved pages
oncheck -cs, -cS, -ps, -pS: Check and display sbspaces
oncheck -pB: Display blobspace statistics
oncheck -pd and pD: Display rows in hexadecimal format
oncheck -pk, -pK, -pl, -pL: Display index information
oncheck -pp and -pP: Display the contents of a logical page
oncheck -pr and pR: Display reserved-page information
oncheck -pt and -pT: Display tblspaces for a Table or Fragment
Turn On Locking with -x
Send Special Arguments to the Access Method with -u
Return Codes on Exit
The onclean utility
The onshutdown script
The oncmsm utility
The onconfig_diff utility
The ondblog utility
The oninit utility
The -FILE option
Return codes for the oninit utility
The onkstash Utility
The onkstore Utility
Create a Keystore with onkstore
Creating an AWS type keystore
Creating an Azure type keystore
Creating a KMIP type keystore
Verifying a Keystore File
Changing the Password for a Keystore File
Converting a Keystore File
List the contents of a Keystore File
The onlog utility
The onmode utility
onmode command syntax
onmode -a: Add a shared-memory segment
onmode -BC: Allow large chunk mode
onmode -c: Force a checkpoint
onmode -C: Control the B-tree scanner
onmode -cache surrogates: Cache the allowed.surrogates file
onmode -d: Set data-replication types
onmode -d: Set High Availability server characteristics
onmode -d command: Replicate an index with data-replication
onmode -D, -M, -Q, -S: Change decision-support parameters
onmode -e: Change usage of the SQL statement cache
onmode -F: Free unused memory segments
onmode -h: Update sqlhosts caches
onmode -I: Control diagnostics collection
onmode -k, -m, -s, -u, -j: Change database server mode
Taking the Database Server to Offline Mode with the -k Option
Bringing the Database Server Online with the -m Option
Shutting Down the Database Server Gracefully with the -s Option
Shutting Down the Database Server Immediately with the -u Option
Changing the Database Server to Administration Mode with the -j Option
onmode -l: Switch the logical-log file
onmode -n, -r: Change shared-memory residency
onmode -O: Override ONDBSPACEDOWN WAIT mode
onmode -p: Add or drop virtual processors
Rules for adding and dropping virtual processors
Monitoring poll threads with onstat
onmode -P: Start, stop, or restart a listen thread dynamically
onmode -R: Regenerate .infos.dbservername File
onmode -W: Change settings for the SQL statement cache
SQL statement cache examples
onmode -we: Export a file that contains current configuration parameters
onmode -wf, -wm: Dynamically change certain configuration parameters
onmode -wm: Change LRU tuning status
onmode -wi: Import a configuration parameter file
onmode -Y: Dynamically change SET EXPLAIN
onmode -z: Kill a database server session
onmode -Z: Kill a distributed transaction
The onparams Utility
onparams syntax
onparams -a -d dbspace: Add a logical-log file
onparams -d -l lognum: Drop a logical-log file
onparams -p: Change physical-log parameters
Backing Up After You Change the Physical-Log Size or Location
Changing the Size of the Physical Log and Using Non-Default Page Sizes
onparams -b: Add a buffer pool
Examples of onparams Commands
The onpassword utility
The ifxclone utility
The onspaces utility
onspaces syntax
onspaces -a: Add a chunk to a dbspace or blobspace
onspaces -a: Add a chunk to an sbspace
onspaces -c -b: Create a blobspace
onspaces -c -d: Create a dbspace
onspaces -c -P: Create a plogspace
onspaces -c -S: Create an sbspace
Creating a Temporary Sbspace with the -t Option
Creating an Sbspace with the -Df option
Changing the -Df Settings
Using the onspaces -g option
onspaces -c -x: Create an extspace
onspaces -ch: Change sbspace default specifications
onspaces -cl: Clean up stray smart large objects in sbspaces
onspaces -d: Drop a chunk in a dbspace, blobspace, or sbspace
onspaces -d: Drop a space
onspaces -f: Specify DATASKIP parameter
onspaces -m: Start mirroring
Using a File to Specify Chunk-Location Information with the -f Option
onspaces -r: Stop mirroring
onspaces -ren: Rename a dbspace, blobspace, sbspace, or extspace
Renaming a dbspace, blobspace, sbspace, or extspace when Enterprise Replication is active
Performing an Archive after Renaming a Space
onspaces -s: Change status of a mirrored chunk
Avoid overwriting a chunk
The onstat utility
onstat Utility Commands Sorted by Functional Category
Monitor the database server status
onstat command syntax
onstat command: Equivalent to the onstat -pu command
onstat - command: Print output header
onstat -- command: Print onstat options and functions
Running onstat Commands on a Shared Memory Dump File
onstat -a command: Print overall status of the database server
onstat -b command: Print buffer information for buffers in use
onstat -B command: Prints information about used buffers
onstat -c command: Print ONCONFIG file contents
onstat -C command: Print B-tree scanner information
onstat -d command: Print chunk information
onstat -D command: Print page-read and page-write information
onstat -f command: Print dbspace information affected by dataskip
onstat -F command: Print counts
onstat -g monitoring options
onstat -g act command: Print active threads
onstat -g afr command: Print allocated memory fragments
onstat -g all command: Print diagnostic information
onstat -g aqt command: Print data mart and accelerated query table information
onstat -g arc command: Print archive status
onstat -g ath command: Print information about all threads
onstat -g bth and -g BTH: Print blocked and waiting threads
onstat -g buf command: Print buffer pool profile information
onstat -g cac command: Print information about caches
onstat -g ckp command: Print checkpoint history and configuration recommendations
onstat -g cfg command: Print the current values of configuration parameters
onstat -g cluster command: Print high-availability cluster information
onstat -g cmsm command: Print Connection Manager information
onstat -g con command: Print condition and thread information
onstat -g cpu: Print runtime statistics
onstat -g dbc command: Print dbScheduler and dbWorker thread statistics
onstat -g defragment command: Print defragment partition extents
onstat -g dic command: Print table information
onstat -g dis command: Print database server information
onstat -g dll command: Print dynamic link library file list
onstat -g dmp command: Print raw memory
onstat -g dri command: Print high-availability data replication information
onstat -g dsc command: Print distribution cache information
onstat -g dsk command: Print the progress of the currently running compression operation
onstat -g env command: Print environment variable values
onstat -g ffr command: Print free fragments
onstat -g glo command: Print global multithreading information
onstat -g his command: Print SQL trace information
onstat -g ioa command: Print combined onstat -g information
onstat -g iob command: Print big buffer use summary
onstat -g iof command: Print asynchronous I/O statistics
onstat -g iog command: Print AIO global information
onstat -g ioq command: Print I/O queue information
onstat -g ipl command: Print index page logging status information
onstat -g iov command: Print AIO VP statistics
onstat -g lap command: Print light appends status information
onstat -g laq command: Print secondary server queues
onstat -g lmm command: Print low memory management information
onstat -g lmx command: Print all locked mutexes
onstat -g lsc command: Print active light scan status (deprecated)
onstat -g mem command: Print pool memory statistics
onstat -g mgm command: Print MGM resource information
onstat -g nbm command: Print a block bit map
onstat -g nsc command: Print current shared memory connection information
onstat -g nsd command: Print poll threads shared-memory data
onstat -g nss command: Print shared memory network connections status
onstat -g ntd command: Print network statistics
onstat -g ntm command: Print network mail statistics
onstat -g ntt command: Print network user times
onstat -g ntu command: Print network user statistics
onstat -g opn command: Print open partitions
onstat -g osi: Print operating system information
onstat -g pd command: Print push data session-related information
onstat -g pd event command: Print push data event-related information
onstat -g pfsc command: Print partition free space cache information
onstat -g pos command: Print file values
onstat -g ppd command: Print partition compression dictionary information
onstat -g ppf command: Print partition profiles
onstat -g pqs command: Print operators for all SQL queries
onstat -g prc command: Print sessions using UDR or SPL routines
onstat -g proxy command: Print proxy distributor information
onstat -g qst command: Print wait options for mutex and condition queues
onstat -g rah command: Print read-ahead request statistics
onstat -g rbm command: Print a block map of shared memory
onstat -g rea command: Print ready threads
onstat -g rss command: Print RS secondary server information
onstat -g rwm command: Print read and write mutexes
onstat -g sch command: Print VP information
onstat -g scn command: Print scan information
onstat -g sds command: Print SD secondary server information
onstat -g seg command: Print shared memory segment statistics
onstat -g ses command: Print session-related information
onstat -g shard command: Print information about the shard definition
onstat -g sle command: Print all sleeping threads
onstat -g smb command: Print sbspaces information
onstat -g smx command: Print multiplexer group information
onstat -g spi command: Print spin locks with long spins
onstat -g sql command: Print SQL-related session information
onstat -g spf: Print prepared statement profiles
onstat -g src command: Patterns in shared memory
onstat -g ssc command: Print SQL statement occurrences
onstat -g stk command: Print thread stack
onstat -g stm command: Print SQL statement memory usage
onstat -g stq command: Print queue information
onstat -g sts command: Print stack usage for each thread
onstat -g sym command: Print symbol table information for the oninit utility
onstat -g top command: Print top consumers of resources
onstat -g tpf command: Print thread profiles
onstat -g ufr command: Print memory pool fragments
onstat -g vpcache command: Print CPU VP and tenant VP private memory cache statistics
onstat -g wai command: Print wait queue thread list
onstat -g wmx command: Print all mutexes with waiters
onstat -g wst command: Print wait statistics for threads
onstat -G command: Print TP/XA transaction information
onstat -h command: Print buffer header hash chain information
onstat -i command: Initiate interactive mode
onstat -j command: Provide onpload status information
onstat -k command: Print active lock information
onstat -l command: Print physical and logical log information
onstat -L command: Print the number of free locks
onstat -m command: Print recent system message log information
onstat -o command: Output shared memory contents to a file
onstat -p command: Print profile counts
onstat -P command: Print partition information
onstat -r command: Repeatedly print selected statistics
onstat -R command: Print LRU, FLRU, and MLRU queue information
onstat -s command: Print latch information
onstat -t and onstat -T commands: Print tblspace information
onstat -u command: Print user activity profile
onstat -x command: Print database server transaction information
Determine the position of a logical-log record
Determine the mode of a global transaction
onstat -X command: Print thread information
onstat -z command: Clear statistics
Return codes on exiting the onstat utility
SQL Administration API
SQL Administration API Functions
SQL Administration API Overview
admin() and task() Function Syntax Behavior
admin() and task() Argument Size Specifications
admin() and task() Function Return Codes
SQL administration API portal: Arguments by privilege groups
add bufferpool argument: Add a buffer pool (SQL administration API)
add chunk argument: Add a new chunk (SQL administration API)
add log argument: Add a new logical log (SQL administration API)
add memory argument: Increase shared memory (SQL administration API)
add mirror argument: Add a mirror chunk (SQL administration API)
alter chunk argument: Change chunk status to online or offline (SQL administration API)
alter logmode argument: Change the database logging mode (SQL administration API)
alter plog argument: Change the physical log (SQL administration API)
archive fake argument: Perform an unrecorded backup (SQL administration API)
autolocate database add argument: Add a dbspace to the dbspace list (SQL administration API)
autolocate database anywhere argument: Add all dbspaces to the dbspace list (SQL administration API)
autolocate database argument: Specify dbspaces for automatic location and fragmentation (SQL administration API)
autolocate database off argument: Disable automatic fragmentation for a database (SQL administration API)
autolocate database remove argument: Remove a dbspace from the dbspace list (SQL administration API)
cdr argument: Administer Enterprise Replication (SQL administration API)
cdr add trustedhost argument: Add trusted hosts (SQL administration API)
cdr autoconfig serv argument: Autoconfigure connectivity and replication (SQL administration API)
cdr list trustedhost argument: List trusted hosts (SQL administration API)
cdr remove trustedhost argument: Remove trusted hosts (SQL administration API)
check data argument: Check data consistency (SQL administration API)
check extents argument: Check extent consistency (SQL administration API)
check partition argument: Check partition consistency (SQL administration API)
checkpoint argument: Force a checkpoint (SQL administration API)
clean sbspace argument: Release unreferenced smart large objects (SQL administration API)
create blobspace argument: Create a blobspace (SQL administration API)
create blobspace from storagepool argument: Create a blobspace from the storage pool (SQL administration API)
create chunk argument: Create a chunk (SQL administration API)
create chunk from storagepool argument: Create a chunk from the storage pool (SQL administration API)
create database argument: Create a database (SQL administration API)
create dbaccessdemo argument: Create the demonstration database (SQL administration API)
create dbspace argument: Create a dbspace (SQL administration API)
create dbspace from storagepool argument: Create a dbspace from the storage pool (SQL administration API)
create plogspace: Create a plogspace (SQL administration API)
create sbspace argument: Create an sbspace (SQL administration API)
create sbspace from storagepool argument: Create an sbspace from the storage pool (SQL administration API)
create sbspace with accesstime argument: Create an sbspace that tracks access time (SQL administration API)
create sbspace with log argument: Create an sbspace with transaction logging (SQL administration API)
create tempdbspace argument: Create a temporary dbspace (SQL administration API)
create tempdbspace from storagepool argument: Create a temporary dbspace from the storage pool (SQL administration API)
create tempsbspace argument: Create a temporary sbspace (SQL administration API)
create tempsbspace from storagepool argument: Create a temporary sbspace from the storage pool (SQL administration API)
defragment argument: Dynamically defragment partition extents (SQL administration API)
drop blobspace argument: Drop a blobspace (SQL administration API)
drop blobspace to storagepool argument: Return space from an empty blobspace to the storage pool (SQL administration API)
drop chunk argument: Drop a chunk (SQL administration API)
drop chunk to storagepool argument: Return space from an empty chunk to the storage pool (SQL administration API)
drop database argument: Drop a database (SQL administration API)
drop dbspace argument: Drop a dbspace (SQL administration API)
drop dbspace to storagepool argument: Return space from an empty dbspace to the storage pool (SQL administration API)
drop log argument: Drop a logical log (SQL administration API)
drop plogspace: Drop the plogspace (SQL administration API)
drop sbspace argument: Drop an sbspace (SQL administration API)
drop sbspace to storagepool argument: Return space from an empty sbspace to the storage pool (SQL administration API)
drop tempdbspace argument: Drop a temporary dbspace (SQL administration API)
drop tempdbspace to storagepool argument: Return space from an empty temporary dbspace to the storage pool (SQL administration API)
drop tempsbspace to storagepool argument: Return space from an empty temporary sbspace to the storage pool (SQL administration API)
export config argument: Export configuration parameter values (SQL administration API)
file status argument: Display the status of a message log file (SQL administration API)
grant admin argument: Grant privileges to run SQL administration API commands
ha make primary argument: Change the mode of a secondary server (SQL administration API)
ha rss argument: Create an RS secondary server (SQL administration API)
ha rss add argument: Add an RS secondary server to a primary server (SQL administration API)
ha rss change argument: Change the password of an RS secondary server (SQL administration API)
ha rss delete argument: Delete an RS secondary server (SQL administration API)
ha sds clear argument: Stop shared-disk replication (SQL administration API)
ha sds primary argument: Convert an SD secondary server to a primary server (SQL administration API)
ha sds set argument: Create a shared-disk primary server (SQL administration API)
ha set idxauto argument: Replicate indexes to secondary servers (SQL administration API)
ha set ipl argument: Log index builds on the primary server (SQL administration API)
ha set primary argument: Define an HDR primary server (SQL administration API)
ha set secondary argument: Define an HDR secondary server (SQL administration API)
ha set standard argument: Convert an HDR server into a standard server (SQL administration API)
ha set timeout argument: Change SD secondary server timeout (SQL administration API)
import config argument: Import configuration parameter values (SQL administration API)
index compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API)
index estimate_compression argument: Estimate index compression (SQL administration API)
master_key reset argument: Change the keystore password (SQL administration API)
message log delete argument: Delete a message log file (SQL administration API)
message log rotate argument: Rotate the message log file (SQL administration API)
message log truncate argument: Delete the contents of a message log file (SQL administration API)
modify chunk extend argument: Extend the size of a chunk (SQL administration API)
modify chunk extendable argument: Mark a chunk as extendable (SQL administration API)
modify chunk extendable off argument: Mark a chunk as not extendable (SQL administration API)
modify chunk swap_mirror argument: Switch primary and mirror chunk files without any downtime(SQL administration API)
modify space swap_mirror argument: Switch all primary and mirror chunk files for a space without any downtime (SQL administration API)
modify config arguments: Modify configuration parameters (SQL administration API)
modify space expand argument: Expand the size of a space (SQL administration API)
modify space sp_sizes argument: Modify sizes of an extendable storage space (SQL administration API)
onbar argument: Backup the storage spaces (SQL administration API)
onmode and a arguments: Add a shared-memory segment (SQL administration API)
onmode and c arguments: Force a checkpoint (SQL administration API)
onmode and C arguments: Control the B-tree scanner (SQL administration API)
onmode and d arguments: Set data-replication types (SQL administration API)
onmode and D arguments: Set PDQ priority (SQL administration API)
onmode and e arguments: Change usage of the SQL statement cache (SQL administration API)
onmode and F arguments: Free unused memory segments (SQL administration API)
onmode and h arguments: Update sqlhosts caches (SQL administration API)
onmode and j arguments: Switch the database server to administration mode (SQL administration API)
onmode and l arguments: Switch to the next logical log (SQL administration API)
onmode and m arguments: Switch to multi-user mode (SQL administration API)
onmode and M arguments: Temporarily change decision-support memory (SQL administration API)
onmode and n arguments: Unlock resident memory (SQL administration API)
onmode and O arguments: Mark a disabled dbspace as down (SQL administration API)
onmode and p arguments: Add or remove virtual processors (SQL administration API)
onmode and Q arguments: Set maximum number for decision-support queries (SQL administration API)
onmode and r arguments: Force residency of shared memory (SQL administration API)
onmode and S arguments: Set maximum number of decision-support scans (SQL administration API)
onmode and W arguments: Reset statement cache attributes (SQL administration API)
onmode and wf arguments: Permanently update a configuration parameter (SQL administration API)
onmode and wm arguments: Temporarily update a configuration parameter (SQL administration API)
onmode, wm, and AUTO_LRU_TUNING arguments: Change LRU tuning status (SQL administration API)
onmode and Y arguments: Change query plan measurements for a session (SQL administration API)
onmode and z arguments: Terminate a user session (SQL administration API)
onmode and Z arguments: Terminate a distributed transaction (SQL administration API)
onsmsync argument: Synchronize with the storage manager catalog (SQL administration API)
onstat argument: Monitor the database server (SQL administration API)
ontape archive argument: Backup the data on your database (SQL administration API)
print error argument: Print an error message (SQL administration API)
print file info argument: Display directory or file information (SQL administration API)
print partition argument: Print partition information (SQL administration API)
rename space argument: Rename a storage space (SQL administration API)
reset config argument: Revert configuration parameter value (SQL administration API)
reset config all argument: Revert all dynamically updatable configuration parameter values (SQL administration API)
reset sysadmin argument: Move the sysadmin database (SQL administration API)
restart listen argument: Stop and start a listen thread dynamically (SQL administration API)
revoke admin argument: Revoke privileges to run SQL administration API commands
scheduler argument: Stop or start the scheduler (SQL administration API)
scheduler lmm enable argument: Specify automatic low memory management settings (SQL administration API)
scheduler lmm disable argument: Stop automatic low memory management (SQL administration API)
set chunk argument: Change the status of a chunk (SQL administration API)
set dataskip argument: Start or stop skipping a dbspace (SQL administration API)
set index compression argument: Change index page compression (SQL administration API)
set onconfig memory argument: Temporarily change a configuration parameter (SQL administration API)
set onconfig permanent argument: Permanently change a configuration parameter (SQL administration API)
set sbspace accesstime argument: Control access time tracking (SQL administration API)
set sbspace avg_lo_size argument: Set the average size of smart large objects (SQL administration API)
set sbspace logging argument: Change the logging of an sbspace (SQL administration API)
set sql tracing argument: Set global SQL tracing (SQL administration API)
set sql tracing database argument: Change database tracing (SQL administration API)
set sql tracing session argument: Control tracing for a session (SQL administration API)
set sql tracing user argument: Control tracing for users (SQL administration API)
set sql user tracing argument: Set global SQL tracing for a user session (SQL administration API)
start json listener argument: Start the MongoDB API wire listener (deprecated)
start listen argument: Start a listen thread dynamically (SQL administration API)
start mirroring argument: Starts storage space mirroring (SQL administration API)
statement cache enable argument: Enable the SQL statement cache (SQL administration API)
statement cache flush argument: Flush the SQL statement cache (SQL administration API)
statement cache hits argument: Specify the number of hits in the SQL statement cache (SQL administration API)
statement cache nolimit argument: Control whether to insert qualified statements into the SQL statement cache (SQL administration API)
statement cache off argument: Disable the SQL statement cache (SQL administration API)
statement cache save argument: Save the SQL statement cache (SQL administration API)
statement cache restore argument: Restore the SQL statement cache (SQL administration API)
stop json listener: Stop the wire listener (deprecated)
stop listen argument: Stop a listen thread dynamically (SQL administration API)
stop mirroring argument: Stops storage space mirroring (SQL administration API)
storagepool add argument: Add a storage pool entry (SQL administration API)
storagepool delete argument: Delete one storage pool entry (SQL administration API)
storagepool modify argument: Modify a storage pool entry (SQL administration API)
storagepool purge argument: Delete storage pool entries (SQL administration API)
Table and fragment pfsc_boost argument: Enable or disable a boosted partition free space cache (PFSC)
Table and fragment compress and uncompress operations (SQL administration API)
table or fragment arguments: Compress data and optimize storage (SQL administration API)
Output of the estimate compression operation (SQL administration API)
purge compression dictionary arguments: Remove compression dictionaries (SQL administration API)
tenant create argument: Create a tenant database (SQL Administration API)
tenant drop argument: Drop a tenant database (SQL Administration API)
tenant update argument: Modify tenant database properties (SQL Administration API)
Appendixes