Administering Informix Dynamic Server, Building the Foundation

Table of Contents

If you compare the tables of contents for this book with that of my first editions, you'll see that it is very similar in design. Of course the content has been updated to reflect current technology. Becuase of the new information, it was decided to split this into two volumes just to keep the sheer volume of the book to a reasonable size.

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 data server 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 material is devided into two volumes. In this, the first volume, the data server'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. Next, I walk you through the initialization process explaining the most important 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 including backup and recovery as well as different data server and database monitoring utilities.

There is one appendix with table sizing information and guidance.

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

Chapter 1: Introduction to Informix Dynamic Server
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 Message Portion
The Virtual-extension Portion
The Disk Component
Definition of Terms
Physical Elements
Instance Elements
Database Terms
Types of Databases

Chapter 2: An Introduction to Extensibility
What is it and Why Should you Care?
Extensible data Types
Functional Indexes
User-defined Routines and Aggregates

Chapter 3: Preparing for Initialization
Logical Database Design
Calculating Table Sizes
Disk Drive Issues
Level 0
Level 1
Level 0+1
Levels 5 and 6
Software-Based Disk Management
Which to Choose?
Using "Cooked" Files or "Raw" Space for Dbspaces
Using Symbolic Links
Working with Cooked Files
Dbspace Design Issues
Kernel Tuning
Backup Strategies
Setting up the Environment
Preparing the install location
Required Files
The $ONCONFIG file
Configuring in Windows
Environment Variables
Required Variables
Other Variables
Multiple Residency Issues

Chapter 4: Installing and Initializing IDS
Installing or upgrading the Server
Upgrading from an Earlier Version
A New Installation
Replicating the installation on Multiple Physical Servers
Control files for the installation wizard, modifying an installation
Understanding and Modifying Configuration Parameters
Initial Devices
System Configuration
Shared Memory
Data Replication
Backup and Restore
Starting the Instance for the First Time
The "Sys" Databases
The sysmaster Database and the SMI
The sysutils Database
The sysuser Database
The sysadmin Database
The syscdr Database
The sysha Database

Chapter 5: Basic Administrative Tasks
Informix Administration Utilities
OpenAdmin Tool for IDS
Server Studio Java Edition
The SQL Administration API
Changing Operating Modes
Changing Database Logging Modes
Managing Dbspaces and BLOBspaces
Types of Logical Storage Spaces
Temporary Spaces
Configurable Dbspace Page Sizes
Understanding Informix's Naming Conventions - Windows
Creating Spaces
Simple BLOBspaces
Smart BLOBspaces
Adding a Disk Chunk
Dropping a Disk Chunk
Dropping a Space
Adding or Dropping a Mirror
Changing the Status of a Chunk
Setting or Modifying DATASKIP
Creating, Moving and Resizing Logs
The Physical Log
The Logical Logs
Starting and Stopping Informix Dynamic Server Automatically
Basic Instance Access Control and Security
Terminating a User Session

Chapter 6: Building a Database Environment
The dbaccess Utility
Database Logging Modes
Creating the Database
Table and Index Creation, Partitioning
Creating Tables and Indexes
Additional Table Creation Options
Partitioning Tables
Round Robin Partitioning
"By Expression" Partitioning
Evaluating the Expression
Partitioning Indexes
Altering Partitions
Initializing, Adding or Modifying Partitions
Dropping Partitions
Attaching Tables
Detaching Partitions
Altering Tables
Constraints, Referential Integrity and Indexes
Stored Procedures, UDRs and Triggers
Constraints and Indexes
The Logical Differences between Constraints and Indexes
Differences in their Enforcement
How to Create Constraints
Fragmenting Constraints
Populating the Database
The SQL load Statement
The dbload Utility
The onload utility
The High-Performance Loader
Flat File Loads Through 4GL or other Application Languages
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
Default Roles
Iterator Functions and Derived Tables in the from Clause
Date and Datetime Functions
String manipulation
Triggers on Views
Order-by Not in Select List
The dbschema Utility

Chapter 7: Backing up and Restoring
Backup Strategies
The Focused Approach
The "Whole-istic" Approach
Logical Logs
Backup Devices
Tape Devices
Backup to Directory and File Handling
Understanding the Backup Process
Restore Options
Redirected restores
Using the Ontape Utility
Creating Backups (including fake)
Restoring From Backups
Using ON-Bar and the Informix Storage Manager
The ON-Bar Utility Suite and the ISM
Configuring the ISM and ON-Bar
Setting the Server Properties
Log Files
Configuring Storage Devices
Labeling Volumes
Mounting Volumes
Creating Backups with ON-Bar (including fake)
Volume Inventories
Restoring and Recovering from an ON-Bar Backup
Restoring Instances and/or Dbspaces
Restartable Restores
Recovering from a Catastrophic Failure
Logical Log Backups through ON-Bar
Cloning Volumes or Save Sets
Verifying Backups with Archecker
Using the Table-Level Restore Functionality

Chapter 8: Monitoring the Instance
The New System Administration Interface
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
Instance and System Monitoring Through OAT

Appendix A: Table Sizing Worksheet