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
- 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
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
- 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
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 %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
Index
Home