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.

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

Transactions

Checkpoints

Chapter 2: Preparing for Initialization

Required Software

Logical Database Design

Calculating Table Sizes

Disk Drive Issues

Mirroring

RAID

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 %INFORMIXDIR%

The %ONCONFIG% File

The Services File

The Hosts File

NT Registry Settings

%SQLHOSTS%

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

Diagnostics

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

Sessions

Events

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

Dbimport

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

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

OPTCOMPIND and Joins

Optimizer Directives

The OPT_GOAL Parameter and the "set optimization" Command

PDQ and MGM

PDQ

MGM

A Collection of Miscellaneous Thoughts

LRU efficiency

Virtual Shared Memory Usage

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 %PLCONFIG% File

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

checkon

check_chunks

check_dbspaces

check_checkpoint

control_chkpt_intervals

chk_users_dbs

instance_watch

Stopping and Starting Unregistered Instances

Database Administration

chk_table_size

where_are_tables

table_hits

chk_seq_scans

find_db_names

transaction_size

strip_index

chk_user_waits

dbdiff2.shr

datadctnry.4gl

upd_stat.4gl

The Informix FAQ

Appendix A: Other Informix Resources

Publications

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