Administering Informix Dynamic Server on Windows NT

Table of Contents

If you compare the tables of contents for this book with that of my other, UNIX-oriented book, you'll see that they are very similar in design. Of course the content has been updated to reflect current technology. The overall structure though is the same -- I wanted the book to be a hands-on type of experience. My intention was to take you through the logical series of steps that an administrator would have to follow as you worked with the engine to create an operational database instance. I felt it was important to include both engine and database administrative functions as each would occur during this process.

As a result, the book is divided into 4 sections. In the first, the engine's architecture and terminology are explained. General instance and database design issues are covered as well as the creation or modification of instance configuration files. In the second section, I walk you through the initialization process explaining all the variables and their impact on the instance. I also cover all the basic instance and database administrative functions that need to be performed with some regularity. In the third section of the book, I write about archiving strategies, different engine and database monitoring utilities, and some of the engine components that can be used to enhance performance. Finally, in the last section I cover a number of additional technologies or concepts that are you'll probably run into over the course of your administrative career as well as some administrative utilities I have found to be effective in administering databases and instances. There are several appendices as well--my favorite being Appendix C which contains a partial transcript of an interview I conducted with Gary Kelley, the architect of OnLine Dynamic Server.

If you have any comments, please send them to me.

PART 1: Preparing to Use the Engine

Chapter 1: The Informix Dynamic Server Architecture
What Is Informix-Dynamic Server?
The Informix-Dynamic Server Architecture Model
The Processor Component
The Shared Memory Component
The Resident Portion
The Virtual Portion
The Disk Component
Definition of Terms
Physical Elements
Instance Elements
Database Terms
Types of Database Environments

Chapter 2: Preparing for Initialization
Required Software
Logical Database Design
Calculating Table Sizes
Disk Drive Issues
Level 0
Level 0+1
Level 1
Level 5 and 6
Software-Based RAID
Which to Choose?
Using "Cooked" Files or "Raw" Space for Dbspaces
Dbspace Design Issues
Basic NT Administration Issues
Basic Back-up Strategies
Setting Up the Environment
Required Files
The Services File
The Hosts File
NT Registry Settings
Field 1 – Host Name
Field 2 – Options
Communication Buffer Size Option
The Security Options
Field 3 – Protocol or Nettype "Word"
Field 4 – Network Service Name
Environment Variables
Multiple Residency Issues

PART 2: Installing, Configuring, and Operating the Engine

Chapter 3: Installing and Initializing the Server
Installing or Upgrading the Engine
A New Installation
Upgrading from a 7.2x Version of the Engine
Installing Answers OnLine
Replicating an Installation Set on Multiple Servers
Reinitializing the Instance from Scratch
Understanding and Modifying Configuration Parameters
Initial Devices
System Configuration
Shared Memory
VP and Performance
Data Replication
CDR Parameters
HADR Parameters
Backup and Restore
Parallel Data Query (PDQ) and Performance
Uninstalling the Software
The Sysmaster and Sysutils Databases and the SMI
The Sysmaster Database and the SMI
The Sysutils Database
Creating Additional Instances on the Server

Chapter 4: The IECC and Basic Administrative Tasks
The Informix Enterprise Command Center
Installing IECC software on Clients and Unix Hosts
Components of the IECC
Instance Reports
Bar Activity
The Spaces Snapin
The Database Snapin
The Instance Manager
Creating a New Instance
Dropping an Instance
The setnet32 Utility
Changing Operating Modes
From the Control Panel
With the IECC
Using Command Line Utilities
Changing Database Logging Modes
Managing Dbspaces and BLOBspaces
Temporary Dbspaces
Creating a Dbspace or BLOBspace
Using the Space Snapin
Understanding Informix’s Chunk Naming Conventions
Using the onspaces Utility
Adding a Disk Chunk
Dropping a Disk Chunk
Dropping a Dbspace
Adding or Dropping an Informix Mirror
Changing the Status of a Chunk
Setting or Changing DATASKIP
Creating, Moving and Resizing Logs
The Physical Log
The Logical Log
Terminating a User Session
Starting and Stopping Dynamic Server Automatically

Chapter 5: Building a Database Environment
The SQL Editor Utility
The Dbaccess Utility
Logging Modes
Creating the Database
Table and Index Creation and Fragmentation
Creating Tables and Indexes
Fragmenting Tables
Round-Robin Fragmentation
"By Expression" Fragmentation
Evaluating the Expression
Fragmenting Indexes
Altering Fragments
Initializing, Adding, or Modifying Fragments
Dropping Fragments
Attaching Tables
Detaching Fragments
Setting Tables and/or Indexes to being Memory Resident
Constraints, Referential Integrity, and Indexes
Stored Procedures and Triggers
Constraints and Indexes
The Logical Differences Between Constraints and Indexes
Differences in Their Enforcement
How to Create Constraints
Check Constraints
Index-Based Constraints
Fragmenting Constraints
Populating the Database
The SQL "load" Command
The dbload Utility
The onload Utility
Flat File Loads Through 4GL Applications
Concurrency and Isolation Levels
Lock Types and Modes
Understanding and Setting Isolation Levels
SQL Statements That Might be of Interest
Violations and Diagnostics, Constraint and Index Enabling and Filtering Session Authorization
Rename Database
Alter Table
Case Insensitive Queries
Date and Datetime Conversion
Case Statements
Select First N Rows
The dbschema Utility
PART 3: Planning for Database Recovery and Performance Tuning

Chapter 6: Backing Up and Restoring
Back-Up Strategies
The Focused Approach
The "Whole-istic" Approach
Logical Logs
Backup Devices
Tape Devices
Flat Files
Understanding the Backup Process
Restore Options
Using the Ontape Utility
Creating Backups
Restoring From Backups
Using ON-Bar and the ISM
The ON-Bar Utility and the ISM
Configuring the ISM and ON-Bar
Setting the Server Properties
Log Files
ON-Bar Activity Log
ON-Bar Debug Log and Debug Level
Legato Message Log
Save Group Log
ISM Debug Level and Log
Configuring Storage Devices
Labeling Volumes
Mounting Volumes
Creating Backups with ON-Bar
Using Interfaces from the IECC
Using the Command Line Utility
Creating a "Fake" Backup
Volume Inventories
Restoring and Recovering From an ON-Bar Backup
Restoring Instances and/or Dbspaces
Using the Command Line Utility
Using Interfaces from the IECC
Restartable Restores
Recovering From a Catastrophic Failure
Logical Log Backups Through ON-Bar
Cloning Volumes or Save Sets

Chapter 7: Monitoring the Instance
Command Line Utilities
Engine Status Reports: The onstat Utility
Looking at User Threads
Disk and Chunk Information
General Instance Monitoring
Database Integrity Reports: The oncheck Utility
Instance Reserved Pages
System Catalog Tables
Tablespace Report
Chunk Free List and Tablespace Interleaving
Verifying Data and Index Consistency and Integrity
Graphical Utilities
Using the Sysmaster Database

Chapter 8: Enhancing Performance
Tuning Virtual Processors
What Are Virtual Processors?
VP Classes
Monitoring and Tuning Virtual Processors
Adding and Removing Virtual Processors
Adding VPs
Dropping VPs
update statistics and Data Distributions
Modes and Distributions
Distributions and Resolution
Data Distributions
Data Resolutions
"update statistics" Modes
Usage Recommendations
The Query Optimizer
Factors Affecting Optimization
Optimizer Directives
The OPT_GOAL Parameter and the "set optimization" Command
A Collection of Miscellaneous Thoughts
LRU efficiency
Virtual Shared Memory Usage

PART 4: Expanding Your Horizons, Additional Topics and Technologies

Chapter 9: Understanding and Using Data Replication
What is High-Availability Data Replication?
How Does HADR Work?
Logical Log Transfer Modes
Synchronous Transfer Mode
Asynchronous Transfer Mode
Instance Actions When a Failure Occurs
What Is a Failure?
What Happens in a Failure Condition?
Initializing HADR
Server and Software Conditions
Dynamic Server Conditions
Network Conditions
A Step-by-Step Approach to Initializing HADR
Recovering After a HADR Failure
Effect of the DRAUTO Parameter on the Recovery Process
A Step-by-Step Approach to Recovering from HADR Failure Conditions
Restarting HADR After a Scheduled Maintenance Period
Recovering from a Physical Failure
Primary Instance Failure with DRAUTO = "0" or "1"
Primary Instance Failure with DRAUTO = "2"
Recovering from a Logical Failure
HADR and Applications

Chapter 10: Recovering From a Crash
How Does the Engine Protect Itself?
Physical Mechanisms
Logical Mechanisms
The Fast Recovery Process
What is a Checkpoint?
What is the Fast Recovery Process?
The First Phase – Verifying the Physical Integrity of the Instance
The Second Phase – Verifying the Logical Integrity of the Data
Informix Technical Support Options
The Informix Technical Support Organization
The Front Line Group
The Advanced Support Group
The Regional Advanced Support Team
How You Can Help Informix’s Technical Support Group
Responsibilities of the Administrator Following a Crash

Chapter 11: Distributed Transactions
Introduction to Distributed Transactions
Distributed Transaction Commit Protocols
The Heterogeneous Commit Protocol
The Two-Phase Commit Protocol
Terminology and Technology
Case Study #1--Successful Commit
Case Study #2--Coordinator Failure
Failure in the Precommit Phase
Failure in the Postdecision Phase
Case Study #3--Participant Failure
What Is an Independent Action?
Case Study #4--Heuristic Rollback Condition
Case Study #5--Heuristic End Transaction Condition
Recovering from a Heuristic Failure

Chapter 12: Using the Windows High Performance Loader
Preparing to Use the HPL
Configuration Issues
Replacing the start_onpload() Stored Procedure
%ONCONFIG% Parameters
The onpload Database
HPL Logs
Using the IECC to Run HPL Jobs on Unix Servers
Creating and Running HPL Jobs
Modifying or Deleting HPL Jobs

Chapter 13: Scripts to Help Get the Job Done
Setting Up a Korn Shell Window Environment
Instance and Other Log Maintenance
Instance Administration
Stopping and Starting Unregistered Instances
Database Administration
The Informix FAQ

Appendix A: Other Informix Resources
Hard-Copy and Web-Based Publications
Web-Based Only Publications
Misc. Publications
International and Local User Groups
The Informix Worldwide User Conference

Appendix B Table Sizing Worksheets
Using the Table Sizing Worksheet for Informix Dynamic Server Versions 5.x and Earlier
Initial Extent Size
Next Extent Size
Using the Table Sizing Worksheet for Informix Dynamic Server Version 7.3
Section 1—Calculating the Data Portion
Row Length Smaller than Page Size
Row Length Greater than Page Size
Remainder Portion Greater than the Remainder Page Size
Remainder Portion Less than the Remainder Page Size
Section 2—Calculating the Index Portion

Appendix C An Interview With Gary Kelley

Appendix D An Extract from the DB-Access Manual