Informix OnLine Dynamic Server Handbook

Table of Contents

When I designed the book, I wanted it to be a hands-on type of experience. My intention was to take you through the logical series of step 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.


NOTE:  This layout of the Table of Contents does not show the proper nested hierarchy of the sections.  
       If I were to attempt that, this page would go on forever. 

I. PREPARING TO USE THE ENGINE..

1. Introduction to OnLine Dynamic Server.
What Is OnLine Dynamic Server?
The OnLine Dynamic Server Architecture Model.
The Process Component.
The Shared Memory Component.
The Resident Portion.
The Virtual Portion.
The Message Portion.
The Disk Component.
Definition of Terms.
Physical Elements.
Instance Elements.
Database Terms.
Types of Database Environments.
Transactions.
Checkpoints.
Summary.
Coming Up.

2. Preparing for Initialization.
Logical Database Design.
Calculating Table Size.
Disk Drive Issues. Mirroring.
RAID.
Level 0.
Level 1.
Level 0+1.
Levels 5 and 6.
Software-Based Disk Management.
Which to Choose?
Using Symbolic Links.
Using "Cooked" Files for dbspaces.
Dbspace Design Issues. Kernel Tuning.
Archiving Strategies.
Setting Up the Environment.
Required files.
The Onconfig File.
The sqlhosts file.
Field 1--Instance Name.
Field 2--Nettype "Word".
Field 3--Hostname.
Field 4--Network Service Name.
Field 5--Connection Options.
The Keep-Alive Option.
The Security Options.
Communication Buffer Size Option.
Environment Variables.
Required Variables.
INFORMIXDIR.
ONCONFIG.
INFORMIXSERVER.
INFORMIXSQLHOSTS.
DBEDIT.
Other Variables.
Multiple Residency Issues.
Summary.
Coming Up.

II. INITIALIZING, CONFIGURING, AND OPERATING THE ENGINE.

3. Initializing an OnLine Dynamic Server Instance.
The Onmonitor Utility.
Stepping Through the Initialization Process.
Initial Device Configuration.
Shared Memory Configuration.
VP and Performance Configuration.
Data Replication. Diagnostics.
Parallel Data Query (PDQ).
The Sysmaster Database and the SMI Interface.
Summary.
Coming Up.

4. Basic Administrative Tasks.
Changing Operating Modes.
Changing Database Logging Modes.
Managing Dbspaces and BLOBspaces.
Offsets. Partitions. Temporary Dbspaces.
Creating a Dbspace or BLOBspace.
Adding a Disk Chunk.
Dropping a Disk Chunk.
Dropping a Dbspace or BLOBspace.
Adding or Dropping a Mirror.
Changing the Status of a Chunk.
Setting or Changing DATASKIP.
Creating, Moving, and Resizing Logs.
The Physical Log.
The Logical Log.
Killing a User Thread.
Starting and Stopping OnLine Dynamic Server Automatically.
Summary.
Coming Up.

5. Building a Database Environment.
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.
Constraints, Referential Integrity, and Indexes.
Stored Procedures.
Constraints and Indexes.
Fragmenting Constraints. Populating the Database.
Dbimport.
The SQL "load" Statement.
The Dbload Utility.
The OnLoad Utility.
The High-Performance Parallel Loader.
Flat File Loads Through 4GL Applications.
Concurrency and Isolation Levels.
Lock Types and Modes.
Understanding and Setting Isolation Levels.
OnLine-Specific SQL Statements.
Violations and Diagnostics, Constraint and Index Enabling and Filtering.
Roles.
Session Authorization.
Rename Database.
Summary.
Coming Up.

III. PLANNING FOR DATABASE RECOVERY AND PERFORMANCE TUNING.

6. Archiving and Restoring.
Archiving Strategies.
The Focused Approach.
The "Whole-istic" Approach.
Logical Logs.
Tape Devices.
Understanding the Archiving Process.
Using the Ontape Utility.
Creating Archives.
Restoring from Archives.
Summary.
Coming Up.

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.
D/B Cockpit.
Onperf.
Using the Sysmaster Database.
Summary.
Coming Up.

8. Enhancing Performance.
Tuning Virtual Processors.
What Are Virtual Processors?
VP Classes.
Monitoring and Tuning Virtual Processors.
Adding or Removing Virtual Processors.
Adding Vps.
Dropping Vps.
Update Statistics and Data Distributions.
Modes and Distributions.
Distributions and Resolution.
Data Distributions.
Data Resolutions.
Usage Recommendations.
The OnLine Query Optimizer.
Factors Affecting Optimization.
OPTCOMPIND and Joins.
PDQ and MGM.
PDQ.
MGM.
Summary.
Coming Up.

IV. EXPANDING YOUR HORIZONS, ADDITIONAL TOPICS AND TECHNOLOGIES.

9. Providing High Availability and Reliability.
What Is High-Availability Data Replication?
How Does HADR Work?
Logical Log Transfer Modes.
Synchronous Transfer Mode.
Asynchronous Transfer Mode.
Server Actions When a Failure Occurs.
What Is a Failure?
What Happens in a Failure Condition?
Initializing HADR.
Server and Software Conditions.
OnLine 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 Server Failure with DRAUTO = 0 or 1.
Primary Server Failure with DRAUTO = 2.
Recovering from a Logical Failure.
HADR and Applications.
Summary.
Coming Up.

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 Support Organization.
The Front Line Group.
The Advanced Support Group.
The International Support Group.
How You Can Help Informix's Technical Support Group.
Responsibilities of the Administrator Following a Crash.
Summary.
Coming Up.

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.
Summary.
Coming Up.

12. Scripts to Help Get the Job Done.
Archiving Scripts. archive_db. chk_archiving. do_archive.
Automating Administrative Tasks.
Starting and Stopping the Instance.
Instance Log Maintenance.
checkon.
chk_chunks.
chk_dbspaces.
chk_ckpoint.
control_chkpt_intervals.
chk_logging.
Database Monitoring and Schema Modification.
chk_table_size.
where_are_tables.
find_db_names.
transaction_size.
strip_index.
dbdiff2.shr.
Miscellaneous.
datadctnry.4gl.
dbpriv.uue.
html_ec.shr and systabs.shr.
The Informix FAQ and misql.shr.
uninstall.shr.
upd_stat.4gl.

Appendix A: Other Informix Resources.
Publications.
Hard-Copy and Web-Based Publications.
Web-Based-Only Publications.
Miscellaneous Publications.
International and Local User Groups.
The Informix Worldwide User Conference.
Using the Internet to Access Informix-Oriented Resources.
Accessing the Internet.
Internet Service Providers (ISPs).
Understanding Internet Addresses.
Internet Information Services.
Usenet News.
The World Wide Web (WWW).
Anonymous ftp.
Electronic Mail.
E-Mail Lists.
informix-list.
Telnet.
Archie.
Where to Find Informix-Oriented Information.

Appendix B Table Sizing Worksheets.
Using the Table Sizing Worksheet for OnLine Versions 5.x and Earlier.
Initial Extent Size.
Next Extent Size.
Using the Table Sizing Worksheet for OnLine Dynamic Server.
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.

Index.


Home