IBM 000-8697 User Manual

IBM Informix OnLine Database Server
Administrator’s Guide
Version 5.x December 2001 Part No. 000-8697
Note: Before using this information and the product it supports, read the information in the appendix entitled “Notices.”
This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such.
When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you.
© Copyright International Business Machines Corporation 1996, 2001. All rights reserved. US Government User Restricted Rights—Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
ii IBM Informix OnLine Database Server Administrator’s Guide

Table of Contents

Introduction
In This Introduction ................. 3
About This Manual .................. 3
Organization of This Manual ............. 4
Demonstration Database .............. 5
IBM Informix OnLine ................. 7
Product Overview................. 7
IBM Informix OnLine and Other IBM Informix Products . . . 7
Documentation Conventions .............. 8
Typographical Conventions ............. 8
Icon Conventions ................. 9
Command-Line Conventions ............. 9
Sample Code Conventions .............. 12
Additional Documentation ............... 14
Printed Manuals ................. 14
Error Message Files ................ 15
Documentation Notes, Release Notes, Machine Notes .... 18
Related Reading ................... 19
Compliance with Industry Standards ........... 19
IBM Welcomes Your Comments ............. 20
Table of
Contents
Chapter 1 Installation and Initial Configuration
In This Chapter ................... 1-5
Define Your Starting Point ............... 1-6
Upgrade an Earlier Version of OnLine ......... 1-7
Overview of OnLine Installation Steps ......... 1-10
Overview of OnLine Initial Configuration Tasks ....... 1-10
OnLine Configuration Files ............. 1-11
Contents of tbconfig.std .............. 1-13
Set Up Your Initial Configuration ............ 1-20
Root Dbspace Configuration Guidelines......... 1-21
Mirroring Configuration Guidelines .......... 1-24
Physical Log Configuration Guidelines ......... 1-25
Logical Log Configuration Guidelines ......... 1-26
Message File Guidelines .............. 1-28
Archive Tape Device Guidelines ........... 1-28
Logical Log Tape Device Guidelines .......... 1-29
Identification Parameter Guidelines .......... 1-31
Shared-Memory Parameter Guidelines ......... 1-32
Machine- and Product-Specific Parameter Guidelines .... 1-39
OnLine Disk Space Allocation ............ 1-40
Configuration Checklist ................ 1-50
Enter Your Configuration and Initialize OnLine ....... 1-51
Setting Shared Memory Parameters .......... 1-53
Initialize OnLine ................. 1-54
Set Your Environment Variables ........... 1-54
Modify UNIX Startup and Shutdown Scripts ....... 1-56
Create Blobspaces and Dbspaces ........... 1-59
Errors During Initialization .............. 1-59
OnLine Error Message Format ............ 1-60
UNIX Error Message Format............. 1-60
iv IBM Informix OnLine Database Server Administrator’s Guide
Chapter 2 System Architecture
In This Chapter .................. 2-7
Initialization ................... 2-7
Initialization Commands ............. 2-8
What Happens During Shared-Memory Initialization . . . 2-10
What Happens During Disk-Space Initialization ..... 2-14
UNIX Kernel and Semaphore-Allocation Parameters .... 2-18
OnLine User Processes ................ 2-22
How User Processes Attach to Shared Memory ...... 2-24
User Processes and Critical Sections.......... 2-28
OnLine User Process Status and States ......... 2-29
OnLine Database Server Process ........... 2-30
Orphaned Database Server Processes ......... 2-31
OnLine Daemon Processes .............. 2-33
tbinit Daemon ................. 2-33
tbundo Daemon ................ 2-34
tbpgcl Daemon ................. 2-34
Shared Memory and Process Communication ........ 2-36
Shared Memory and Buffer Locks .......... 2-38
Managing Shared-Memory Resources ......... 2-39
Shared-Memory Header.............. 2-47
Shared-Memory Internal Tables ........... 2-48
Shared-Memory Buffer Pool ............ 2-55
OnLine LRU Queues ............... 2-57
LRU Queues and Buffer Pool Management ....... 2-58
How a User Process Acquires a Buffer ......... 2-60
Physical Log Buffer ............... 2-63
Logical Log Buffer ................ 2-66
OnLine Checkpoints ............... 2-70
What Happens During a Checkpoint ......... 2-72
When the Daemons Flush the Buffer Pool........ 2-73
How OnLine Synchronizes Buffer Flushing ....... 2-74
Write Types Describe Flushing Activity ........ 2-75
Writing Data to a Blobspace ............ 2-78
Disk Data Structures ................ 2-81
OnLine Disk Space Terms and Definitions ....... 2-81
Structure of the Root Dbspace ............ 2-87
Structure of a Regular Dbspace ........... 2-89
Structure of an Additional Dbspace Chunk ....... 2-90
Structure of a Blobspace .............. 2-91
Table of Contents v
Structure of a Blobspace or Dbspace Mirror Chunk ..... 2-92
OnLine Limits for Chunks ............. 2-93
Reserved Pages ................. 2-95
Chunk Free-List Page ...............2-103
tblspace Tblspace.................2-104
Database Tblspace ................2-107
Create a Database: What Happens on Disk ........2-108
OnLine Limits for Databases.............2-110
Create a Table: What Happens on Disk .........2-110
Create a Temporary Table: What Happens on Disk .....2-113
Structure of an Extent ...............2-114
Next Extent Allocation ...............2-117
Structure of a Dbspace Page .............2-120
Data Row Format and Rowid ............2-123
Data Pages and Data Row Storage ...........2-125
Structure of an Index Page .............2-133
Structure of a Dbspace Bit-Map Page ..........2-143
Blob Storage and the Blob Descriptor ..........2-145
Structure of a Dbspace Blob Page ...........2-146
Blobspace Page Types ...............2-148
Structure of a Blobspace Blobpage ...........2-149
Physical Log ....................2-152
Logical Log Files ..................2-154
Fast Recovery and Data Restore............2-154
File Rotation ..................2-155
File Contents ..................2-156
Number and Size.................2-156
Blobspace Logging ................2-158
Long Transactions ................2-159
vi IBM Informix OnLine Database Server Administrator’s Guide
Chapter 3 Operating OnLine
In This Chapter .................. 3-5
Changing Modes .................. 3-6
Types of OnLine Modes .............. 3-6
From Offline to Quiescent ............. 3-8
From Offline to Online .............. 3-8
From Quiescent to Online ............. 3-9
Gracefully from Online to Quiescent ......... 3-10
Immediately from Online to Quiescent......... 3-11
From Any Mode Immediately to Offline ........ 3-12
Logical Log Administration .............. 3-13
Examine Your Logical Log Configuration ........ 3-14
Change Pathname of Logical Log Tape Device ...... 3-18
Change Block Size of Logical Log Tape Device ...... 3-21
Change Tape Size of Logical Log Tape Device ...... 3-22
Change Maximum Number of Logical Log Files ..... 3-23
Change Size of Logical Log Files ........... 3-24
Logical Log File Status .............. 3-26
Logical Log File ID Numbers ............ 3-27
Add a Logical Log File .............. 3-28
Drop a Logical Log File .............. 3-30
Move a Logical Log File to Another Dbspace ...... 3-31
Change the Logging Status of a Database ........ 3-33
Back Up a Logical Log File ............. 3-36
Start Continuous Logical Log Backup ......... 3-37
End Continuous Logical Log Backup ......... 3-38
Switch to the Next Logical Log File .......... 3-39
Free a Logical Log File .............. 3-39
If the Logical Log Backup Cannot Complete ....... 3-42
Archive Administration ............... 3-43
Archive Types ................. 3-43
How Long Will an Archive Take? .......... 3-46
Plan the Archive Schedule ............. 3-47
Examine Your Archive Configuration ......... 3-50
Change Pathname of Archive Tape Device ....... 3-52
Change Block Size of Archive Tape Device ....... 3-55
Change Tape Size of Archive Tape Device........ 3-56
Create an Archive, Any Type ............ 3-57
Table of Contents vii
If the Logical Log Files Fill During an Archive....... 3-59
If an Archive Terminates Prematurely ......... 3-60
Monitor OnLine Activity ............... 3-61
Monitor Archive History .............. 3-61
Monitor Blobs in a Blobspace ............ 3-63
Monitor Blobs in a Dbspace ............. 3-65
Monitor Buffers ................. 3-66
Monitor Buffer-Pool Activity............. 3-68
Monitor Checkpoints ............... 3-69
Monitor Chunks ................. 3-70
Monitor Configuration Information .......... 3-73
Monitor Databases ................ 3-74
Monitor Dbspaces ................ 3-75
Monitor Disk Pages ................ 3-77
Monitor Extents ................. 3-78
Monitor Index Information ............. 3-79
Monitor Logging Activity.............. 3-80
Monitor the Message Log .............. 3-82
Monitor OnLine Profile .............. 3-83
Monitor Shared Memory and Latches.......... 3-84
Monitor Tblspaces ................ 3-85
Monitor Users and Transactions ........... 3-86
Modify OnLine Configuration ............. 3-87
Create a Blobspace ................ 3-88
Drop a Blobspace................. 3-91
Change the Number of Buffers in the Pool ........ 3-92
Change the Size of Either Log Buffer .......... 3-93
Add a Chunk .................. 3-94
Change the Maximum Number of Chunks ........ 3-96
Create a Dbspace................. 3-97
Drop a Dbspace ................. 3-99
Enforce/Turn Off Residency for This Session .......3-100
Enforce/Turn Off Residency .............3-100
Change the Status of a Mirrored Chunk .........3-101
Enable Mirroring.................3-104
Start/End Mirroring in a Blobspace or Dbspace ......3-105
Change Physical Log Location or Size .........3-107
Change the Checkpoint Interval ...........3-109
Change the Destination of Console Messages .......3-110
viii IBM Informix OnLine Database Server Administrator’s Guide
Change the Maximum Number of Dbspaces....... 3-111
Change the Maximum Number of Locks ........ 3-112
Change the Maximum Number of Tblspaces....... 3-113
Change the Maximum Number of Users ........ 3-114
Change the Number of Page Cleaners ......... 3-115
Things to Avoid .................. 3-116
Chapter 4 Data Consistency, Recovery, and Migration
In This Chapter .................. 4-5
Consistency Checking ................ 4-6
Using the tbcheck Commands............ 4-6
Using the OnLine Message Log ........... 4-8
Setting Consistency-Checking Variables ........ 4-9
Recovering from Corruption ............ 4-12
Mirroring .................... 4-14
Beginning................... 4-15
Processing .................. 4-16
Recovery ................... 4-17
Ending .................... 4-17
OnLine Logging Overview .............. 4-18
Dbspace Logging ................ 4-19
Blobspace Logging................ 4-22
What Happens During Logical Log Backup ........ 4-26
Ready LTAPEDEV ................ 4-27
Locate the Next Logical Log ............ 4-27
Copy Blobpages ................ 4-27
Place Log Header on Tape ............. 4-28
Write Log Records to Tape ............. 4-29
Write Trailer Page ................ 4-30
What Happens During an Archive ........... 4-30
Read Archive History Information .......... 4-31
Mount a Tape on TAPEDEV ............ 4-31
Verify the Archive Level .............. 4-32
Check Free Space in the Logical Log ......... 4-32
Force a Checkpoint ............... 4-32
Synchronize tbtape and tbinit Activities ........ 4-33
Write Tape Header Page .............. 4-35
Archive Reserved Pages .............. 4-36
Determine Archive Criteria............. 4-37
Archive Disk Pages That Meet Criteria......... 4-38
Table of Contents ix
Monitor and Archive Physical Log Pages ........ 4-38
Write a Trailer Page ................ 4-38
Update the Reserved Pages ............. 4-38
Fast Recovery ................... 4-39
How Does OnLine Initiate Fast Recovery? ........ 4-39
Fast Recovery and Logging ............. 4-40
Step 1: Checkpoint Condition ............ 4-41
Step 2: Find Checkpoint Record in Logical Log ...... 4-41
Step 3: Roll Forward Log Records ........... 4-43
Step 4: Roll Back Incomplete Transactions ........ 4-44
Data Restore: When Should You Do It? .......... 4-45
Steps That Occur During a Data Restore......... 4-45
Gather All Tapes Needed for Restore .......... 4-47
Verify OnLine Configuration............. 4-48
Initiate Data Restore from Offline Mode ......... 4-49
Mount Level-0 Archive Tape ............. 4-49
Verify Current Configuration ............ 4-50
Prompt for Logical Log Backup ............ 4-50
Write Each Archive Page to Disk ........... 4-51
Initialize Shared Memory .............. 4-51
Roll Forward Logical Logs ............. 4-51
OnLine Is Quiescent................ 4-52
Database and Table Migration .............. 4-52
Description of Migration Methods ........... 4-54
Which Migration Method Is Best for You? ........ 4-57
Using UNLOAD with LOAD or dbload ......... 4-60
Using dbexport and dbimport ............ 4-62
Using tbunload and tbload ............. 4-63
Migrating Data from OnLine to SE........... 4-65
Migrating Data from SE to OnLine........... 4-66
x IBM Informix OnLine Database Server Administrator’s Guide
Chapter 5 How to Improve Performance
In This Chapter .................. 5-3
Disk Layout ................... 5-4
Optimize Blobspace Blobpage Size ........... 5-5
tbcheck -pB and tbcheck -pe Utility Commands...... 5-5
Blobpage Average Fullness ............. 5-7
Apply Effective Criteria .............. 5-8
Eliminate User-Created Resource Bottlenecks ........ 5-8
When Is Tuning Needed? ............... 5-10
% Cached Fields ................ 5-10
ovtbls, ovlock, ovuser, and ovbuff Fields ........ 5-11
Bufsize Pages/IO Fields .............. 5-11
Shared-Memory Buffers ............... 5-13
When Is Tuning Necessary? ............ 5-13
How Is Tuning Done? .............. 5-13
Shared-Memory Resources .............. 5-14
When Is Tuning Necessary? ............ 5-14
How Is Tuning Done? .............. 5-15
Log Buffer Size .................. 5-15
Logging Status ................. 5-15
How Is Tuning Done? .............. 5-16
Page-Cleaner Parameters ............... 5-17
Efficient Page Cleaning .............. 5-17
How Is Tuning Done? .............. 5-19
Checkpoint Frequency ................ 5-20
Performance Tradeoffs .............. 5-20
How Is Tuning Done? .............. 5-21
Psort Parallel-Process Sorting Package .......... 5-22
How Psort Works ................ 5-22
Tuning Psort .................. 5-23
Psort and Shared Memory ............. 5-24
SPINCNT Configuration Parameter ........... 5-24
Table of Contents xi
Chapter 6 DB-Monitor Screens
In This Chapter ................... 6-3
Main Menu................... 6-4
Status Menu .................. 6-5
Parameters Menu ................ 6-6
Dbspaces Menu ................. 6-7
Mode Menu .................. 6-8
Force-Ckpt Option ................ 6-9
Archive Menu .................. 6-10
Logical-Logs Menu ................ 6-11
Chapter 7 Utilities
In This Chapter ................... 7-5
dbexport: Unload a Database and Schema File ........ 7-5
Syntax .................... 7-6
Destination Options................ 7-7
Contents of the Schema File ............. 7-9
dbimport: Create a Database .............. 7-10
Syntax .................... 7-11
Input File Location Options ............. 7-12
Create Options ................. 7-14
dbload: Load Data from a Command File .......... 7-15
Syntax .................... 7-16
Command-File Syntax Check ............ 7-18
Starting Line Number ............... 7-18
Batch Size ................... 7-19
Bad-Row Limits ................. 7-20
How to Create a Command File............ 7-21
dbschema: Output SQL Statements ............ 7-32
Syntax .................... 7-32
Include Synonyms ................ 7-33
Include Privileges ................ 7-34
Specify a Table, View, or Procedure .......... 7-35
tbcheck: Check, Repair, or Display ............ 7-36
Syntax .................... 7-38
Option Descriptions................ 7-39
tbinit: Initialize OnLine ................ 7-45
Syntax .................... 7-46
xii IBM Informix OnLine Database Server Administrator’s Guide
tbload: Create a Database or Table ............ 7-47
Syntax .................... 7-48
Specify Tape Parameters.............. 7-49
Create Options ................. 7-50
tblog: Display Logical Log Contents ........... 7-51
Syntax .................... 7-51
Log-Record Read Filters .............. 7-52
Log-Record Display Filters ............. 7-54
Interpreting tblog Output ............. 7-55
tbmode: Mode and Shared-Memory Changes ........ 7-64
Syntax .................... 7-65
Change OnLine Mode .............. 7-66
Force a Checkpoint ............... 7-67
Change Shared-Memory Residency .......... 7-68
Switch the Logical Log File ............. 7-68
Kill an OnLine Server Process ............ 7-69
Kill an OnLine Transaction ............. 7-69
tbparams: Modify Log Configuration Parameters ...... 7-70
Syntax .................... 7-70
Add a Logical Log File .............. 7-70
Drop a Logical Log File .............. 7-71
Change Physical Log Parameters........... 7-72
tbspaces: Modify Blobspaces or Dbspaces ......... 7-73
Syntax .................... 7-73
Create a Blobspace or Dbspace ........... 7-74
Drop a Blobspace or Dbspace ............ 7-75
Add a Chunk ................. 7-76
Change Chunk Status............... 7-77
tbstat: Monitor OnLine Operation ............ 7-78
Syntax .................... 7-80
Option Descriptions ............... 7-82
tbtape: Logging, Archives, and Restore .......... 7-102
Syntax .................... 7-103
Request a Logical Log Backup............ 7-104
Start Continuous Backups ............. 7-104
Create an Archive ................ 7-105
Perform a Data Restore .............. 7-105
Change Database Logging Status........... 7-106
Table of Contents xiii
tbunload: Transfer Binary Data in Page Units ........7-107
Syntax ....................7-108
Specify Tape Parameters ..............7-109
Chapter 8 OnLine Message Log
In This Chapter ................... 8-3
OnLine Message Log ................. 8-3
Alphabetized Messages ................ 8-5
Chapter 9 Product Environment
In This Chapter ................... 9-3
The OnLine Environment ............... 9-3
OnLine Features ................. 9-3
Features Beyond the Scope of OnLine.......... 9-6
What Is Multiple Residency? .............. 9-7
How Multiple Residency Works ........... 9-10
How to Set Up Multiple Residency .......... 9-11
OnLine Administration with IBM Informix STAR ....... 9-15
Sharing Data by Using IBM Informix STAR........ 9-15
IBM Informix STAR and Two-Phase Commit Protocol .... 9-19
Two-Phase Commit and Automatic Recovery ....... 9-23
Independent Action and Manual Recovery ........ 9-29
Heuristic Decisions: What and Why .......... 9-30
Heuristic Rollback ................ 9-36
Heuristic End-Transaction ............. 9-40
Two-Phase Commit Protocol Errors .......... 9-43
Two-Phase Commit and Logical Log Records ....... 9-44
Determining Database Consistency .......... 9-51
IBM Informix STAR Configuration Parameters ...... 9-57
Track a Transaction with tbstat Output ......... 9-58
Appendix A Notices
Index
xiv IBM Informix OnLine Database Server Administrator’s Guide

Introduction

In This Introduction .................. 3
About This Manual................... 3
Organization of This Manual .............. 4
Demonstration Database ............... 5
IBM Informix OnLine .................. 7
Product Overview.................. 7
IBM Informix OnLine and Other IBM Informix Products .... 7
Documentation Conventions ............... 8
Typographical Conventions .............. 8
Icon Conventions .................. 9
Command-Line Conventions .............. 9
Elements That Can Appear on the Path ......... 10
How to Read a Command-Line Diagram ........ 11
Sample Code Conventions ............... 12
Introduction
Additional Documentation ................ 14
Printed Manuals .................. 14
Error Message Files ................. 15
Using the ASCII Error Message File .......... 15
Using the PostScript Error Message Files ........ 18
Documentation Notes, Release Notes, Machine Notes ..... 18
Related Reading .................... 19
Compliance with Industry Standards ............ 19
IBM Welcomes Your Comments .............. 20
2 IBM Informix OnLine Database Server Administrator’s Guide

In This Introduction

This introduction provides an overview of the information in this manual and describes the conventions it uses.

About This Manual

The IBM Informix OnLine Administrator’s Guide describes the powerful Informix online transaction processing (OLTP) database server.
You do not need database management experience or familiarity with relational database concepts to use this manual. However, a knowledge of
SQL (Structured Query Language) would be useful. For detailed information
about IBM Informix SQL, see the IBM Informix Guide to SQL: Tutorial and the
IBM Informix Guide to SQL: Reference.
This manual serves as both an administrator and operator guide and a reference manual. Chapter 1, “Installation and Initial Configuration,” supports the instructions provided in the UNIX Products Installation Guide.
Chapter 2, “System Architecture,” provides an optional, technical discussion
of the IBM InformixOnLinesystem architecture. Subsequent chapters explain how to take advantage of all the features and functionality of the
IBM Informix OnLine database server.
Introduction 3

Organization of This Manual

Organization of This Manual
This manual includes the following chapters:
Chapter 1, “Installation and Initial Configuration,” provides a step-
Chapter 2, “System Architecture,” provides optional reference
Chapter 3, “Operating OnLine,” explains the routinetasks of OnLine
Chapter 4, “Data Consistency, Recovery, and Migration,” provides
Chapter 5, “How to Improve Performance,” describes strategies you
Chapter 6, “DB-Monitor Screens,” explains how to use the
Chapter 7, “Utilities,” describes the function and syntax of each of
Chapter 8, “OnLine Message Log,” provides reference material that
Chapter 9, “Product Environment,” describes three possible OnLine
by-step explanation of OnLine database server installation and setup. The chapter includes a worksheet to assist you in planning your system and in documenting your configuration.
material about OnLine operation that is intended to deepen your understanding of OnLine 5.x.
administration: startup and shutdown, logical log management, archive management, monitoring OnLine activity, and managing disk space.
background information and instructions for using the high-avail­ability features of OnLine.
can use to obtain maximum performance within your processing environment.
DB-Monitor menu facility provided with OnLine.
the 14 OnLine utilities.
documents the internal messages that OnLine generates during processing.
environments. First, this chapter describes the OnLine features that are available to you within a single-system environment. Second, this chapter describes how to configure and administer OnLine database servers if you are running more than one OnLine database server on a single host machine. Finally, this chapter describes OnLine administration issues that arise when you use the
IBM Informix STAR product to run OnLine in a client/server
environment.
4 IBM Informix OnLine Database Server Administrator’s Guide

Demonstration Database

A Notices appendix contains information about IBM products, services, and features. An index directs you to areas of particular interest.
Demonstration Database
Your IBM Informix OnLine software includes a demonstration database called stores5 that contains information about a fictitious wholesale sporting­goods distributor.The sample command files that make up a demonstration application are included as well.
Most of the examples in this manual are based on the stores5 demonstration database. The stores5 database is described in detail and its contents are listed in using DB-Access to manipulate the data in the demonstration database, refer to the DB-Access User Manual.
The script you use to install the demonstration database is called dbaccessdemo5 and is located in the $INFORMIXDIR/bin directory. The database name that you supply is the name given to the demonstration database. If you do not supply a database name, the name defaults to stores5. Follow these rules for naming your database:
IBM Informix Guide to SQL: Reference. For further information about
Names for databases can be up to 10 characters long.
The first character of a name must be a letter.
You can use letters, characters, and underscores (_) for the rest of the
name.
DB-Access makes no distinction between uppercase and lowercase
letters.
The database name should be unique.
When you run dbaccessdemo5, you are, as the creator of the database, the owner and Database Administrator (DBA) of that database.
After you install OnLine, the files that make up the demonstration database are protected so that you cannot make any changes to the original database.
Introduction 5
Demonstration Database
You can run the dbaccessdemo5 script again whenever you want a fresh demonstration database to work with. The script prompts you when the creation of the database is complete and asks if you would like to copy the sample command files to the current directory. Answer “N” to the prompt if you have made changes to the sample files and do not want them replaced with the original versions. Answer “Y” to the prompt if you want to copy over the sample command files.
To create and populate the demonstration database in the
IBM Informix OnLine
environment
1. Set the INFORMIXDIR environment so that it contains the name of the
directory in which your IBM Informix products are installed. Set SQLEXEC to $INFORMIXDIR/lib/sqlturbo. (For a full description
of environment variables, see IBM Informix Guide to SQL: Reference.)
2. Create a new directory for the SQL command files.
Create the directory by entering:
mkdir dirname
3. Make the new directory the current directory by entering:
cd dirname
4. Create the demonstration database and copy over the sample
command files by entering:
dbaccessdemo5 dbname
The data for the database is put into the root dbspace.
To give someone else the SQL privileges to access the data, use the GRANT and REVOKE statements. The GRANT and REVOKE statements are described in IBM Informix Guide to SQL: Reference.
To use the command files that have been copied to your directory, you must have UNIX read and execute permissions for each directory in the pathname of the directory from which you ran the dbaccessdemo5 script. To give someone else the permissions to access the command files in your directory, use the UNIX chmod command.
6 IBM Informix OnLine Database Server Administrator’s Guide

IBM Informix OnLine

IBM Informix OnLine

Product Overview

The IBM Informix OnLine database server combines high-availability, online transaction-processing (OLTP)performance with multimedia capabilities. By managing its own shared-memory resources and disk I/O, OnLine delivers process concurrency while maintaining transaction isolation. Table data can spanmultiple disks, freeing administratorsfromconstraints imposed bydata storage limitations. The mance to users throughout a client/server environment. The IBM Informix
TP/XA product allows you to use the OnLine database server as a Resource
Manager within an X/Open environment.

IBM Informix OnLine and Other IBM Informix Products

IBM provides a variety of application development tools, CASE tools, database servers, utilities, and client/server products. DB-Access is a utility that allows you to access, modify, and retrieve information from OnLine relational databases. IBM Informix OnLine supports all application devel­opment tools currently available, including products like IBM Informix SQL,
IBM Informix 4GL and Interactive Debugger, and the Informix embedded
language products, such as IBM Informix ESQL/C. IBM Informix OnLine also works with third-party application development tools through the
IBM Informix ODBC Driver and the IBM Informix JDBC Driver.
IBM Informix STAR product brings OnLine perfor-
For running applications on a network, IBM Informix STAR provides distributed database access to multiple IBM Informix OnLine database servers.
Introduction 7

Documentation Conventions

Documentation Conventions
This section describes the conventions that this manual uses. These conventionsmake it easierto gather information fromthis and other volumes in the documentation set.
The following conventions are discussed:
Typographical conventions
Icon conventions
Command-line conventions
Example code conventions

Typographical Conventions

This manual uses the following conventions to introduce new terms, illustrate screen displays, describe command syntax, and so forth.
Convention Meaning
KEYWORD All primary elements in a programming language statement
(keywords) appear in uppercase letters in a serif font.
italics
italics
italics
boldface
boldface
monospace
monospace
Withintext, new terms and emphasized words appear in italics. Within syntax and code examples, variable values that you are to specify appear in italics.
Names of program entities (such as classes, events, and tables), environment variables, file and pathnames, and interface elements (such as icons, menu items, and buttons) appear in boldface.
Information that the product displays and information that you enter appear in a monospace typeface.
Tip: When you are instructed to “enter” characters or to “execute” a command, immediately press RETURN after the entry. When you are instructed to “type” the text or to “press” other keys, no RETURN is required.
8 IBM Informix OnLine Database Server Administrator’s Guide

Icon Conventions

Icon Conventions
Comment icons identify three types of information, as the following table describes. This information always appears in italics.
Icon Label Description
Warning: Identifies paragraphs that contain vital instructions,
cautions, or critical information
Important: Identifies paragraphs that contain significant
information about the feature or operation that is being described
Tip: Identifies paragraphs that offer additional details or
shortcuts for the functionality that is being described

Command-Line Conventions

OnLine supports a variety of command-line options. These are commands that you enter at the operating system prompt to perform certain functions as part of OnLine administration.
This section defines and illustrates the format of the commands. These commands have their own conventions, which may include alternative forms of a command, required and optional parts of the command, and so forth.
Each diagram displays the sequences of required and optional elements that arevalid in a command. A diagram begins at the upper left with a command. It ends at the upper right with a vertical line. Between these points, you can trace any path that does not stop or back up. Each path describes a valid form of the command. You must supply a value for words that are in italics.
Introduction 9
Command-Line Conventions
Elements That Can Appear on the Path
You might encounter one or more of the following elements on a path.
Element Description
command This required element is usually the product name or
variable A word in italics represents a value that you must
-flag A flag is usually an abbreviation for a function, menu,
.ext A filename extension, such as .sql or .cob, might follow
other short word used to invoke the product or call the compiler or preprocessor script for a compiled Informix product. It may appear alone or precede one or more options. You must spell a command exactly as shown and must use lowercase letters.
supply, such as a database, file, or program name. The nature of the value is explained immediately following the diagram.
or option name or for a compiler or preprocessor argument. You must enter a flag exactly as shown, including the preceding hyphen.
a variable representing a filename. Type this extension exactlyas shown, immediately after the name of the file and a period. The extension may be optional in certain products.
(.,;+*-/) Punctuation and mathematical notations are literal
symbols that you must enter exactly as shown.
"" Double quotes are literal symbols that you must enter
as shown. You can replace a pair of double quotes with a pair of single quotes, if you prefer. You cannot mix double and single quotes.
Privileges
p. 6-17
ALL
10 IBM Informix OnLine Database Server Administrator’s Guide
A reference in a box represents a subdiagram on the same page or another page. Imagine that the subdi­agram is spliced into the main diagram at this point.
A shaded option is the default. Even if you do not explicitly type the option, it will be in effect unless you choose another option.
(1 of 2)
Element Description
A branch below the main line indicates an optional path.
-
The vertical line is a terminator and indicates that the statement is complete.
Commands enclosed in a pair of arrows indicate that this is a subdiagram.
Command-Line Conventions
A gate ( ) in an option indicates that you can only
1
use that option once, even though it is within a larger loop.
1
-s
1
-t
(2 of 2)
How to Read a Command-Line Diagram
Figure 1 shows the elements of an OnLine utility command used to unload
OnLine data in binary, disk-page units:
Figure 1
Example of a Command-Line Diagram
tbunload database
Specify
Tape
Parameters
p. 7-109
table name
Introduction 11

Sample Code Conventions

To construct a similar command, start at the top left with the command
tbunload. Then follow the diagram to the right, including the elements that
you want. Figure 1 illustrates the following steps.
Sample Code Conventions
1. Type tbunload.
2. Optionally, change the parameters ofthe tape devicethat is to receive
the data. If you wish to do this, turn to page 7-109 for further syntax informa-
tion. Otherwise, tbunload uses the current archive tape device.
3. Specify either a database name or a table name to indicate the data
that you wish to copy to tape. You can take the direct route to the terminator, or you can take an
optional path indicated by any one of the branches below the main line.
Once you are back at the main diagram, you come to the terminator. Your tbunload command is complete.
4. Press RETURN to execute the command.
Examples of SQL code appear throughout this manual. Except where noted, the code is not specific to any single Informix application development tool. If only SQL statements are listed, they are not delineated by semicolons.
For instance, you might see the following example code:
DATABASE stores . . . DELETE FROM customer
WHERE customer_num = 121 . . . COMMIT WORK CLOSE DATABASE
12 IBM Informix OnLine Database Server Administrator’s Guide
Sample Code Conventions
To use this SQL code for a specific product, you must apply the syntax rules for that product. For example, if you are using DB-Access or IBM Informix
SQL, you must delineate the statements with semicolons. If you areusing an
embedded language, you must use EXEC SQL and a semicolon (or other appropriate delimiters) at the start and end of each statement, respectively.
For detailed directions on using SQL statements for a particular application development tool, see the manual for your product.
Tip: Ellipsis points in a code example indicate that more code would be added in a full application, but it is not necessary to show it to describe the concept being discussed.
Introduction 13

Additional Documentation

Additional Documentation
For additional information, refer to the following types of documentation:

Printed Manuals

You might want to refer to a number of related Informix product documents that complement this manual.
Printed manuals
Error message files
Documentation notes, release notes, and machine notes
Related reading
If you have never used SQL (Structured Query Language) or an
Informix application development tool, read IBM Informix Guide to
SQL: Tutorial to learn basic database design and implementation
concepts.
A companion volume to the tutorial, IBM Informix Guide to SQL:
R
eference, provides full information on the structure and contents of
thedemonstration database that isprovidedwith OnLine. Itincludes details of the Informix system catalog tables, describes Informix and common UNIX environment variables that should be set, and defines column data types supported by Informix products. Further, it provides a detailed description of all the SQL statements supported by Informix products. It also contains a glossary of useful terms.
You, or whoever installs OnLine, should refer to the UNIX Products
Installation Guide for your particular release to ensure that OnLine is
properly set up before you begin to work with it.
If you are using OnLine across a network, you may also want to refer
to the IBM Informix NET and IBM Informix STAR Installation and Config- uration Guide.
14 IBM Informix OnLine Database Server Administrator’s Guide

Error Message Files

The DB-Access User’s Manual describes how to invoke the utility to
access, modify, and retrieve information from OnLine relational databases.
When errors occur, you can look them up by number and find their
cause and solution in the IBM Informix Error Messages manual. If you prefer, you can look up the error messages in the online message file described in “Error Message Files” on page 15.
Error Message Files
Informix software products provide ASCII files that contain all the Informix error messages and their corrective actions. To access the error messages in the ASCII file, Informix provides scripts that let you display error messages on the terminal or print formatted error messages.
The optional IBM Informix Messages and Corrections product provides PostScript files that contain the error messages and their corrective actions. If you have installed this product, you can print the PostScript files on a PostScript printer.
Using the ASCII Error Message File
You can use the file that contains the ASCII text version of the error messages and their corrective actions in two ways:
Use the finderr script to display one or more error messages on the
terminal screen.
Use the rofferr script to print one error message or a range of error
messages.
The scripts are in the $INFORMIXDIR/bin directory. The ASCII file has the following path:
$INFORMIXDIR/msg/errmsg.txt
The error message numbers range from -1 to -33000. When you specify these numbers for the finderr or rofferr scripts, you can omit the minus sign. A few messages have positive numbers; these messages are used solely within the application development tools. In the unlikely event that you want to display them, you must precede the message number with a + sign.
Introduction 15
Error Message Files
Themessages numbered -1 to-100 can beplatform-dependent. If themessage text for a message in this range does not apply to your platform, check the operating system documentation for the precise meaning of the message number.
The finderr Script
Use the finderr script to display one or more error messages, and their correctiveactions, on the terminal screen. The finderr script has the following syntax:
finderr
-
+
msg_num
msg_num is the number of the error message to display.
You can specify any number of error messages per finderr command. The finderr command copies all the specified messages and their corrective actions to standard output.
For example, to display the -359 error message, you can enter the following command:
finderr -359
The following example demonstrates how to specify a list of error messages. This example also pipes the output to the UNIX more command to control the display. You can also redirect the output to another file so that you can save or print the error messages:
finderr 233 107 113 134 143 144 154 | more
16 IBM Informix OnLine Database Server Administrator’s Guide
Error Message Files
The rofferr Script
Use the rofferr script to format one error message or a range of error messages for printing. By default, rofferr displays output on the screen. You need to send the output to nroff to interpret the formatting commands and then to a printer, or to a file where the nroff output is stored until you are readyto print. Youcan then print the file. For information on using nroff and on printing files, see your UNIX documentation.
The rofferr script has the following syntax:
start_msgrofferr
-
+
-
+
end_msg
start_msg Is the number of the first error message to format
This error message number is required.
end_msg Is the number of the last error message to format
This error message number is optional. If you omit end_msg, only start_msg is formatted.
The following example formats error message -359. It pipes the formatted error message into nroff and sends the output of nroff to the default printer:
rofferr 359 | nroff -man | lpr
The following example formats and then prints all the error messages between -1300 and -4999:
rofferr -1300 -4999 | nroff -man | lpr
Introduction 17

Documentation Notes, Release Notes, Machine Notes

Using the PostScript Error Message Files
Use the IBM Informix Messages and Corrections product to print the error messages and their corrective actions on a PostScript printer. The PostScript error messages aredistributed in a number of files of the format errmsg1.ps, errmsg2.ps, and so on. These files are located in the $INFORMIXDIR/msg directory. Each file contains approximately 50 printed pages of error messages.
Documentation Notes, Release Notes, Machine Notes
In addition to the IBM Informix set of manuals, the following online files, located in the $INFORMIXDIR/release directory, supplement the information in this manual. Please examine these files because they contain vital infor­mation about application and performance issues.
Online File Purpose
ONLINEDOC_5 The documentation notes file for your version of this manual
describes features that are not covered in the manual or that were modified since publication.
ENGREL_5 The release notes file describes feature differences from earlier
versions of IBM Informix products and how these differences might affect current products. This file also contains infor­mation about any known problems and their workarounds.
ONLINE_5 The machine notes file describes any special actions that you
must take to configure and use IBM Informix products on your computer.Machine notes arenamed for the product described.
18 IBM Informix OnLine Database Server Administrator’s Guide

Related Reading

Related Reading
If you have had no prior experience with database management, you may want to refer to an introductory text like C. J. Date’s An Introduction to Database Systems: Seventh Edition (Addison-Wesley Publishing, 1999). If you want more technical information on database management, consider consulting the following tests:
Database Systems: A Practical Approach to Design, Implementation, and
Management, 3rd Edition, by C. Begg and T. Connolly (Addison­Wesley Publishing, 2001)
Inside Relational Databases, 2nd Edition, by M. Whitehorn and B.
Marklyn (Springer-Verlag, 2001)
This guide assumes you are familiar with your computer operating system. If you have limited UNIX system experience, you may want to look at your operating system manual or a good introductory text before starting to learn about IBM Informix OnLine.
Some suggested texts about UNIX systems follow:
A Practical Guide to the UNIX System, 3rd Edition by M. Sobell
(Addison-Wesley Publishing, 1994)
Learningthe UNIX Operating System by J. Peek (O’Reilly& Associates,
1997)
Design of the UNIX Operating System by M. Bach (Prentice-Hall, 1987)

Compliance with Industry Standards

The American National Standards Institute (ANSI) has established a set of industry standards for SQL. IBM Informix SQL-based products are fully compliant with SQL-92 Entry Level (published as ANSI X3.135-1992), which is identical to ISO 9075:1992. In addition, many features of Informix database servers comply with the SQL-92 Intermediate and Full Level and X/Open
SQL CAE (common applications environment) standards.
Introduction 19

IBM Welcomes Your Comments

IBM Welcomes Your Comments
To help us with future versions of our manuals, we want to know about any correctionsor clarifications that you would find useful. Include the following information:
The name and version of your manual
Any comments that you have about the manual
Your name, address, and phone number
Send electronic mail to us at the following address:
This address is reserved for reporting errors and omissions in our documen­tation. For immediate help with a technical problem, contact Customer Services.
We appreciate your suggestions.
doc@informix.com
20 IBM Informix OnLine Database Server Administrator’s Guide
Installation and Initial Configuration
In This Chapter .................... 1-5
Define Your Starting Point ................ 1-6
Upgrade an Earlier Version of OnLine ........... 1-7
Compare Your Current Configuration to OnLine 5.x .... 1-7
Create a Level-0 Archive .............. 1-8
Load the Software and Execute the install Script ...... 1-8
Initialize Shared Memory .............. 1-8
Run tbcheck .................. 1-9
Create a New Level-0 Archive ............ 1-9
Overview of OnLine Installation Steps........... 1-10
Overview of OnLine Initial Configuration Tasks......... 1-10
OnLine Configuration Files .............. 1-11
Contents of tbconfig.std ................ 1-13
Chapter
1
Set Up Your Initial Configuration .............. 1-20
Root Dbspace Configuration Guidelines .......... 1-21
ROOTNAME .................. 1-21
ROOTPATH .................. 1-22
ROOTOFFSET.................. 1-22
ROOTSIZE ................... 1-23
Mirroring Configuration Guidelines ........... 1-24
MIRROR.................... 1-24
MIRRORPATH ................. 1-24
MIRROROFFSET................. 1-25
Physical Log Configuration Guidelines .......... 1-25
PHYSDBS ................... 1-25
PHYSFILE ................... 1-25
Logical Log Configuration Guidelines ........... 1-26
LOGFILES ................... 1-27
LOGSIZE ................... 1-27
Message File Guidelines ................ 1-28
MSGPATH................... 1-28
CONSOLE ................... 1-28
Archive Tape Device Guidelines ............. 1-28
TAPEDEV ................... 1-28
TAPEBLK ................... 1-29
TAPESIZE ................... 1-29
Logical Log Tape Device Guidelines............ 1-29
LTAPEDEV ................... 1-30
LTAPEBLK ................... 1-30
LTAPESIZE ................... 1-31
Identification Parameter Guidelines............ 1-31
SERVERNUM .................. 1-31
DBSERVERNAME ................ 1-32
Shared-Memory Parameter Guidelines........... 1-32
RESIDENT ................... 1-32
USERS .................... 1-33
TRANSACTIONS................. 1-33
LOCKS .................... 1-33
BUFFERS ................... 1-34
TBLSPACES................... 1-34
CHUNKS ................... 1-35
DBSPACES ................... 1-35
PHYSBUFF ................... 1-35
LOGBUFF ................... 1-36
LOGSMAX ................... 1-36
CLEANERS ................... 1-36
SHMBASE ................... 1-37
CKPTINTVL .................. 1-37
LRUS ..................... 1-37
LRU_MAX_DIRTY ................ 1-37
LRU_MIN_DIRTY ................ 1-38
LTXHWM ................... 1-38
LTXEHWM................... 1-38
Machine- and Product-Specific Parameter Guidelines...... 1-39
DYNSHMSZ.................. 1-39
GTRID_CMP_SZ ................. 1-39
DEADLOCK_TIMEOUT .............. 1-39
TXTIMEOUT .................. 1-39
SPINCNT ................... 1-40
1-2 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Disk Space Allocation ..............1-40
Allocate Raw Disk Space or Cooked Files?.........1-40
How Much Disk Space Do You Need? ..........1-41
How Should You Apportion Disk Space? .........1-43
How to Allocate Disk Space..............1-47
Evaluate UNIX Kernel Parameters ...........1-49
Configuration Checklist ..................1-50
Enter Your Configuration and Initialize OnLine .........1-51
Setting Shared Memory Parameters ............1-53
Initialize OnLine ...................1-54
Set Your Environment Variables..............1-54
SQLEXEC ....................1-55
TBCONFIG ...................1-55
Modify UNIX Startup and Shutdown Scripts .........1-56
Startup .....................1-57
Shutdown ....................1-58
Create Blobspaces and Dbspaces .............1-59
Errors During Initialization ................1-59
OnLine Error Message Format ..............1-60
UNIX Error Message Format...............1-60
Installation and Initial Configuration 1-3
1-4 IBM Informix OnLine Database Server Administrator’s Guide

In This Chapter

This chapter describes how to get started administering your IBM Informix
OnLine environment.
You need the following items to install your OnLine database server:
UNIX Products Installation Guide
IBM Informix OnLine electronic media
IBM Informix OnLine serial number keycard
The specific steps that you should follow as part of your installation depend on your environment. To find the starting point that is right for you, refer to
page 1-6.
Installation refers to the three-step procedure of preparing your UNIX environment, loading the product files onto your UNIX system, and running the installation script to correctly set up the product files. An overview of the installation procedure is illustrated on page 1-10 and described in detail in the UNIX Products Installation Guide.
Initial configuration refers to the set of values that OnLine reads and imple­ments the first time that you initialize OnLine disk space. The initial configuration receives special attention because of the number of adminis­trative issues that you must consider as you define the values for your initial configuration.
Initial configuration tasks refers to the steps that you complete as you take OnLine to online mode for the first time and prepare your OnLine system to receive data. This chapter explains each of the configuration tasks, including how to arrive at the initial configuration values that are correct for your OnLine environment and how to enter these values and initialize OnLine.
Installation and Initial Configuration 1-5
Define Your Starting Point
Define Your Starting Point
This section directs you to the starting point for your specific installation and configuration.
If you are installing an follow all the steps illustrated on page 1-10. After you complete the software load and installation, turn to page 1-10 of this manual for instructions on completing your initial configuration for OnLine 5.x.
If you are replacing an OnLine 5.x database server, you must unload your current data. The OnLine utilities for importing data accept ASCII files as input. Read the sections in this manual that discuss data migration beforeyou unload your data. (Refer to page 4-52.) When you are ready to install OnLine 5.x, follow all the steps illustrated on page 1-10. After you complete the software load and instal­lation, turn to page 1-10 of this manual for instructions on completing your initial configuration for OnLine 5.x.
If you are installing OnLine 5.x and plan to run more than one independent OnLine 5.x database server on the same host machine, you must define different configuration files for each instance of OnLine. This situation of multiple OnLine 5.x systems is referred to as multiple residency. Refer to
page 9-7 for a complete discussion of multiple-residency issues. When you
are ready to install OnLine 5.x, follow all the steps illustrated on page 1-10. After you complete the software load and installation, turn to page 1-10 of this manual for instructions on completing your initial configuration for OnLine 5.x.
If you are installing OnLine 5.x and plan to run it on the same host machine where you are running IBM Informix SE, you can load the OnLine 5.x software into the same $INFORMIXDIR directory that contains your
IBM Informix SE software. You do not need to define a different value for INFORMIXDIR. To install OnLine 5.x, follow the steps illustrated on
page 1-10, beginning with the second step, loading the software. After you
complete the software load and installation, turn to page 1-10 of this manual for instructions on completing your initial configuration for OnLine 5.x.
IBM Informix OnLine 5.x product for the first time,
IBM Informix SE or other database server with the
1-6 IBM Informix OnLine Database Server Administrator’s Guide

Upgrade an Earlier Version of OnLine

If you are installing OnLine 5.x and plan to run it on the same host machine where you are running an earlier version of OnLine, you must load the OnLine 5.x software into a different $INFORMIXDIR directory than the one that contains your earlier server software.Toinstall OnLine 5.x, follow all the steps illustrated on page 1-10. Be sure that you define the OnLine 5.x INFOR-
MIXDIR
you complete the software load and installation, turn to page 1-10 of this manual for instructions on completing your initial configuration for OnLine 5.x.
and PATH environment variables correctly for user informix. After
Upgrade an Earlier Version of OnLine
If you areupgrading an earlier version of OnLine, you do not need to allocate more UNIX disk space than is already set aside for OnLine. The tasks in the upgrade procedure follow:
1. Compare Your Current Configuration to OnLine 5.x.
2. Create a Level-0 Archive.
3. Load the Software and Execute the install Script.
4. Initialize Shared Memory.
5. Run tbcheck to verify database integrity.
6. Create a New Level-0 Archive.
Warning: Do not initialize disk space if you are upgrading your OnLine system. If you initialize disk space, you destroy your current OnLine system and all existing data.
Compare Your Current Configuration to OnLine 5.x
OnLine 5.x adds 10 configuration parameters to support features and improved performance. Informix recommends that you compare the contents of tbconfig.std to your current configuration file before you initialize shared memory. You might decide to modify your current configu­ration or to specify nondefault values when you initialize shared memory to better take advantage of OnLine 5.x features. The contents of tbconfig.std are described on page 1-13. Guidelines for setting the values of the parameters begin on page 1-20.
Installation and Initial Configuration 1-7
Upgrade an Earlier Version of OnLine
Create a Level-0 Archive
Ask all users to exit their applications before you begin the upgrade procedure. (Perform a graceful shutdown by executing tbmode -s from the command line.) Createa level-0 archive of your current OnLine system. Keep a copy of your current configuration file for reference.
Load the Software and Execute the install Script
Take OnLine to offline mode. (Execute tbmode -ky.) Verify that you are loggedin as user root. (The script installs OnLine 5.x into the $INFORMIXDIR directory specified for user root.)
Instructions for loading the software and executing the installation script are contained in the UNIX Products Installation Guide. OnLine 5.x overwrites any OnLine database server products that might exist in the $INFORMIXDIR directory.
Initialize Shared Memory
Log out as user root and log in again as user informix. Reinitialize OnLine shared memory from the DB-Monitor Parameters menu, Shared-Memory option, or from the command line (execute tbinit). When you initialize shared memory, your current OnLine configuration file is updated for OnLine 5.x. If you do not specify values for the new parameters, default values are assigned.
If you are unfamiliar with the shared-memory initialization procedure, turn to page 3-8.
1-8 IBM Informix OnLine Database Server Administrator’s Guide
Upgrade an Earlier Version of OnLine
Run tbcheck
Verify the integrity of the upgraded 5.x databases before you continue. To do this, execute the following commands from the system prompt:
tbcheck -ci dbname
tbcheck -cD dbname
tbcheck -cc dbname
tbcheck -cr
If you encounter any inconsistencies, refer to page 4-6.
Checks and verifies the integrity of the database indexes.
Checks and verifies the integrity of database data. Checks and verifies the integrity of the OnLine
5.x system catalog tables. Checks and verifies the integrity of the OnLine
5.x reserved pages.
Create a New Level-0 Archive
After OnLine 5.x is initialized, create a level-0 archive of the OnLine 5.x system. When the archive is completed, take OnLine to online mode.
Databases are automatically upgraded to OnLine 5.x format when they are opened for the first time. Part of the upgrading procedurefor databases is the creation of Version 5.x system catalog tables for each database. For further information about the 5.x SQL system catalog, refer to the IBM Informix Guide to SQL: Reference.
Installation and Initial Configuration 1-9

Overview of OnLine Installation Steps

Overview of OnLine Installation Steps
InstallingOnLine 5.x involves three major steps, which are summarizedhere. For detailed information, see the UNIX Products Installation Guide.
Important: For each step, you must be logged in as root.
1. Create UNIX environment:
Create user informix.
Set INFORMIXDIR.
Set PATH.
Change your directory to $INFORMIXDIR.
Load OnLine 5.x software:
2.
Copy Informix files into the Informix installation directory.
3. Install OnLine 5.x:
Run ./installonline to change owner, group, and mode of
product files.
Overview of OnLine Initial Configuration Tasks
OnLine initial configuration includes configuration planning and disk-space initialization. The rest of this chapter provides instructions for the initial configuration tasks.
Since OnLine 5.x is already installed in $INFORMIXDIR, you can use a UNIX editor to examine the configuration file tbconfig.std that is described in the following pages. Youcan also access the OnLine monitor facility,DB-Monitor. To do so, log in as user informix and enter the command tbmonitor at the command line.
1-10 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Configuration Files
OnLine Configuration Files
You are not limited to just one configuration file. You can create and manage multiple OnLine configuration files, and each file can contain a unique set of configuration parameter values. This section explains how multiple configu­ration files are created and managed.
As part of OnLine 5.x installation, the product software is loaded into the Informix product directory, specified as the environment variable INFOR-
MIXDIR
locatedin the directory $INFORMIXDIR/etc. The tbconfig.std file containsthe default values for the configuration parameters and serves as the template for all other configuration files that you create.
The OnLine environment variable TBCONFIG specifies the name of the UNIX file (which must be located in the directory $INFORMIXDIR/etc) that is read as input to either the disk-space or shared-memory initialization procedure. The TBCONFIG environment variable enables you to create and maintain multiple configuration files, each with different values. As user informix, youcan initialize OnLinesharedmemory with adifferentsetof configuration parameters by resetting the value of TBCONFIG.
. One of the files loaded during installation is tbconfig.std, which is
The default value of TBCONFIG is defined as tbconfig. When you first load the OnLine 5.x software, the file tbconfig does not exist. The tbconfig file is created for you the first time that you initialize OnLine. If you initialize from within DB-Monitor, the tbconfig file contains the parameter values entered as part of initialization. If you initialize from the command line, using the OnLine utility tbinit, the tbconfig file contains default values obtained from tbconfig.std.
You set the value of TBCONFIG when you define the environment variables as one of your last tasks during installation.
You can modify the configuration file from within DB-Monitor while OnLine is online. The changes you make are written immediately to the file specified asTBCONFIG.IfTBCONFIG is not specified, OnLine modifies the file tbconfig. But even though the values in the file change, most changes to the parameter values do not take effect until you reinitialize OnLine shared memory. Until you take this step, it is possible that the values in the file specified as
TBCONFIG do not match the values in your current, effective configuration.
Installation and Initial Configuration 1-11
OnLine Configuration Files
If you modify the configuration file while OnLine is online, you might want tocomparethe current configuration values with the new values stored inthe file specified as TBCONFIG.
To obtain a copy of your current, effective OnLine configuration through
DB-Monitor, choose the Status menu, Configuration option. You are asked to
supply a filename for the output file. If you supply a filename (without a directory location), a copy of the current configuration is stored in filename.out in the current working directory.
To display a copy of the configuration file, $INFORMIXDIR/etc/$TBCONFIG, execute the command tbstat -c at the UNIX prompt while OnLine is running. (If TBCONFIG is not specified, OnLine displays the contents of $INFOR-
MIXDIR
You can use a UNIX system editor to create other configuration files (apart fromtbconfig.std, tbconfig, and the file specified by TBCONFIG). Each config­uration file must be located in the $INFORMIXDIR/etc directory. The requirement that all configuration files must exist in $INFORMIXDIR/etc means that you cannot make the directory read-only. If you do, you are unable to save any parameter changes you make from DB-Monitor during OnLine operation. The installation procedurecreates the $INFORMIXDIR/etc with read-only permissions for all users except root and user informix.
/etc/tbconfig by default.)
Do not add parameters to a configuration file that are not included in tbconfig.std. If you do, the next time you attempt to modify a configuration parameter or initialize shared memory through DB-Monitor, OnLine detects that the unknown parameters do not exist in tbconfig.std and rejects them as invalid. OnLine removes any parameters from the configuration file that do not exist in tbconfig.std.
Do not remove the tbconfig.std file. If you do, OnLine is unable to create a new configuration file the first time you attempt to modify a parameter or initialize shared memory through DB-Monitor.
Informix recommends that you do not alter the contents of the tbconfig.std file. All supported parameters are contained in tbconfig.std.
1-12 IBM Informix OnLine Database Server Administrator’s Guide
Contents of tbconfig.std
Contents of tbconfig.std
The tbconfig.std file contains all OnLine configuration parameters. The paragraphs that follow name each parameter and provide a brief definition. The parameters are listed in alphabetic order, not in the order in which they appear in tbconfig.std. Figure 1-1 displays a copy of the tbconfig.std file. (If you are unfamiliar with the terms used by Informix to describe units of disk space, refer to the IBM Informix Guide to SQL: Tutorial.)
BUFFERS specifies the number of OnLine shared-memory page buffers
available to OnLine user processes. Refer to page 1-34 for information about setting the value of this parameter.
BUFFSIZE is an unalterable configuration parameter that specifies the page
size for this platform. Changes made to the value shown for BUFFSIZE have no effect.
CHUNKS specifies a value that approximates the maximum number of
chunks that OnLine can support on this specific hardware platform. The number of chunks can be system-dependent. Refer to page 1-35 for infor­mation about setting the value of this parameter.
CKPTINTVL specifies the maximum interval, expressed in seconds, that can
elapse before OnLine checks to determine if a checkpoint is needed. When a checkpoint occurs, pages in the shared-memory buffer pool disk are synchro­nized with the corresponding pages on disk. Refer to page 1-37 for information about setting the value of this parameter.
CLEANERS specifies the number of dedicated page-cleaner daemons to
initialize for this OnLine configuration. Refer to page 1-36 for information about setting the value of this parameter.
CONSOLE specifies the pathname destination for console messages. The
default value, /dev/console, sends messages to the system console screen. Refer to page 1-28 for information about setting the value of this parameter.
DBSERVERNAME specifies the unique name of this OnLine database server,
as distinguished from other OnLine database servers that might exist in the $INFORMIXDIR directory or in a client/server environment. Refer to
page 1-32 for information about setting the value of this parameter.
Installation and Initial Configuration 1-13
Contents of tbconfig.std
DBSPACES specifies the maximum number of dbspaces supported by this
OnLine configuration. Like CHUNKS, the number of dbspaces can be system­dependent. Refer to page 1-35 for information about setting the value of this parameter.
DEADLOCK_TIMEOUT specifies the maximum number of seconds that an
OnLine user process can wait to acquire a lock in a client/server environment. The parameter is used only if this OnLine configuration uses the distributed capabilities of IBM Informix STAR.Refer to page 9-57 for infor­mation about setting the value of this parameter.
DYNSHMSZ specifies the amount of shared memory that is allocated during
initialization and made available to the database servers during execution. This parameter is only used by the IBM Informix TP/XA product. Refer to the
IBM Informix TP/XA User Manualfor information about setting the value of this
parameter.
GTRID_CMP_SZ specifies the number of bytes to compare for global trans-
action identification numbers. This parameter is only used by the
IBM Informix TP/XA product. Refer to the IBM Informix TP/XA User Manual for
information about setting the value of this parameter.
LOCKS specifies the maximum number of locks available to OnLine user
processes. Refer to page 1-33 for information about setting the value of this parameter.
LOGBUFF specifies in kilobytes the size of each of the three logical log buffers
that reside in shared memory. Refer to page 1-36 for information about setting the value of this parameter.
LOGFILES specifies the number of logical log files currently configured for
OnLine. You set this value initially.However,if you add or drop logs during OnLine operation, this value is updated automatically. Refer to page 1-27for information about setting the value of this parameter.
LOGSIZE specifies in kilobytes the size of each logical log file maintained by
OnLine. The total disk space dedicated to the logical logs is equal to
LOGFILES multiplied by LOGSIZE. Refer to page 1-27 for information about
setting the value of this parameter.
LOGSMAX specifies the maximum number of logical log files supported by
this OnLine configuration. Refer to page 1-36 for information about setting the value of this parameter.
1-14 IBM Informix OnLine Database Server Administrator’s Guide
Contents of tbconfig.std
LRUS specifies the number of LRU (least-recently used) queues. The LRU
queues manage the shared-memory buffer pool. Refer to page 1-37 for infor­mation about setting the value of this parameter.
LRU_MAX_DIRTY specifies the percentage of modified pages in the LRU
queues that, when reached, flags the queue to be cleaned. Refer to page 1-37 for information about setting the value of this parameter.
LRU_MIN_DIRTY specifies the percentage of modified pages in the LRU
queues that, when reached, flags the page cleaners that cleaning is no longer mandatory, although it might continue for other reasons. Refer to page 1-38 for information about setting the value of this parameter.
LTAPEBLK specifies in kilobytes the size of the tape block for the logical log
backup tape device. Refer to page 1-30 for information about setting the value of this parameter.
LTAPEDEV specifies the device pathname of the logical log backup tape
device. Refer to page 1-30 for information about setting the value of this parameter.
LTAPESIZEspecifies in kilobytes the maximum amount of data that should be
stored on a tape mounted on the logical log backup tape device. Refer to
page 1-31 for information about setting the value of this parameter.
LTXEHWM specifies the “long transaction, exclusive access, high-water
mark.” The LTXEHWM is a higher percentage than the LTXHWM percentage. If the logical log fills to LTXEHWM,the long transaction currently being rolled back is given “exclusive” access to the logical log. The term “exclusive” is not entirelyaccurate. Most OnLineactivity is suspended until the transaction has completed its rollback, but transactions that are in the process of rolling back or committing retain access to the logical log. Refer to page 1-38 for infor­mation about setting the value of this parameter.
LTXHWM specifies the “long transaction high-water mark.” The value of
LTXHWM is the percentage of available logical log space that, when filled, triggers the tbinit daemon to check for a long transaction. If a long trans­action is found, the transaction is aborted and the executing OnLine database server process rolls back all modifications associated with it. Refer to
page 1-38 for information about setting the value of this parameter.
Installation and Initial Configuration 1-15
Contents of tbconfig.std
MIRROR specifies whether OnLine blobspace and dbspace mirroring is
enabled. Refer to page 1-24 for information about setting the value of this parameter.
MIRROROFFSET specifies in kilobytes the offset into the disk partition or into
the device to reach the beginning of the mirror chunk. Refer to page 1-25 for information about setting the value of this parameter.
MIRRORPATH specifies the pathname of the mirrorchunk where the mirrored
root dbspace resides. Informix recommends that this value be a linked pathname that points to the mirror-chunk device. Refer to page 1-24for infor­mation about setting the value of this parameter.
MSGPATH specifies the pathname of the OnLine message log. The message
log contains diagnostic and status messages that document OnLine operation. Refer to page 1-28 for information about setting the value of this parameter.
PHYSBUFF specifies in kilobytes the size of each of the two physical log
buffers that reside in shared memory. Refer to page 1-35 for information about setting the value of this parameter.
PHYSDBS specifies the name of the dbspace where the physical log resides.
WhenOnLine disk space is firstinitialized, the physical log must reside in the root dbspace. After initializing, you can move the physical log out of the root dbspace to improve performance. Refer to page 1-25 for information about setting the value of this parameter.
PHYSFILE specifies in kilobytes the size of the physical log. Refer to page 1-25
for information about setting the value of this parameter.
RESIDENT indicates whether OnLine shared memory will remain resident in UNIX physical memory. Not all UNIX operating systems support forced
residency. Refer to page 1-32 for information about setting the value of this parameter.
ROOTNAME specifies the name of the root dbspace. Refer to page 1-21 for
information about setting the value of this parameter.
ROOTOFFSET specifies in kilobytes the offsetinto the disk partition or into the
device to reach the beginning of the initial chunk of the root dbspace. Refer to page 1-22 for information about setting the value of this parameter.
1-16 IBM Informix OnLine Database Server Administrator’s Guide
Contents of tbconfig.std
ROOTPATH specifies the pathname of the chunk where the root dbspace
resides. Informix recommends that this value be a link that points to the root dbspace chunk device. Refer to page 1-22 for information about setting the value of this parameter.
ROOTSIZE specifies the size of the root dbspace in kilobytes. Refer to
page 1-23 for information about setting the value of this parameter.
SERVERNUM specifies a unique identification number which, along with the DBSERVERNAME, distinguishes this OnLine database server from all others.
Refer to page 1-31 for information about setting the value of this parameter.
SHMBASE specifies the address that serves as the base of shared memory
when shared memory is attached to the memory space of a user process. Refer to page 2-26 for information about the value of this parameter.
SPINCNT is supported by some multiprocessor machines. Refer to page 1-40
for information about setting the value of this parameter.
TAPEBLK specifies in kilobytes the size of the tape block for the archive tape
device. Refer to page 1-29 for information about setting the value of this parameter.
TAPEDEV specifies the device pathname of the archive tape device. Refer to
page 1-28 for information about setting the value of this parameter.
TAPESIZE specifies in kilobytes the maximum amount of data that should be
stored on a tape mounted on the archive tape device. Refer to page 1-29 for information about setting the value of this parameter.
TBLSPACES specifies the maximum number of open or active tblspaces
supported by this OnLine configuration. Refer to page 1-34 for information about setting the value of this parameter.
TRANSACTIONS specifies the maximum number of concurrent OnLine user
processes supported by this OnLine configuration. As a general guideline,
TRANSACTIONS is set to the value of USERS. Refer to page 1-33 for infor-
mation about setting the value of this parameter.
TXTIMEOUT specifies, for a client/server environment, the maximum
number of seconds that an OnLine database server waits for a transaction during a two-phase commit. This parameter is used only if OnLine uses the distributed capabilities of IBM Informix STAR. Refer to page 9-57 for infor­mation about setting the value of this parameter.
Installation and Initial Configuration 1-17
Contents of tbconfig.std
USERS specifies the maximum number of OnLine user processes that can
attach to shared memory concurrently. A user process is broadly defined as a process that is, or will be, attached to shared memory. User processes include database server processes, daemon processes, and utility processes. (In this manual, no reference is made to application tool processes.) Refer to
page 1-33 for information about setting the value of this parameter.
Figure 1-1
The Contents of tbconfig.std
#**************************************************************# # # # INFORMIX SOFTWARE, INC. # # # # Title: tbconfig.std # # Sccsid:@(#)tbconfig.std 8.5 6/11/91 16:19:05 # # Description: INFORMIX-OnLine Configuration Parameters # # # #**************************************************************#
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name ROOTPATH /dev/online_root
# Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 20000 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration
MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing root
MIRROROFFSET 0 # Offset into mirror device (Kbytes)
# Physical Log Configuration
PHYSDBS rootdbs # Name of dbspace that contains physical log PHYSFILE 1000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 6 # Number of logical log files LOGSIZE 500 # Size of each logical log file (Kbytes)
# Message Files
MSGPATH /usr/informix/online.log
# OnLine message log pathname CONSOLE /dev/console
# System console message pathname
# Archive Tape Device
1-18 IBM Informix OnLine Database Server Administrator’s Guide
dbspace mirror
Contents of tbconfig.std
TAPEDEV /dev/tapedev
TAPEBLK 16 # Archive tape block size (Kbytes) TAPESIZE 10240 # Max. amount of data to put on tape (Kbytes)
# Logical Log Backup Tape Device
LTAPEDEV /dev/tapedev
LTAPEBLK 16 # Logical log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape
# Identification Parameters
SERVERNUM 0 # Unique id associated with this OnLine
DBSERVERNAME ONLINE # Unique name of this OnLine instance
# Shared Memory Parameters
RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) USERS 20 # Maximum number of concurrent user processes TRANSACTIONS 20 # Maximum number of concurrent transactions LOCKS 2000 # Maximum number of locks BUFFERS 200 # Maximum number of shared memory buffers TBLSPACES 200 # Maximum number of active tblspaces CHUNKS 8 # Maximum number of chunks DBSPACES 8 # Maximum number of dbspaces and blobspaces PHYSBUFF 32 # Size of physical log buffers (Kbytes) LOGBUFF 32 # Size of logical log buffers (Kbytes) LOGSMAX 6 # Maximum number of logical log files CLEANERS 1 # Number of page-cleaner processes SHMBASE 0x400000 # Shared memory base address CKPTINTVL 300 # Checkpoint interval (in seconds) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 60 # LRU modified begin-cleaning limit (percent) LRU_MIN_DIRTY 50 # LRU modified end-cleaning limit (percent) LTXHWM 80 # Long TX high-water mark (percent) LTXEHWM 90 # Long TX exclusive high-water mark (percent)
# Archive tape device pathname
# Logical log tape device pathname
(Kbytes)
instance
# Machine- and Product-Specific Parameters
DYNSHMSZ 0 # Dynamic shared memory size (Kbytes) GTRID_CMP_SZ 32 # Number of bytes to use in GTRID comparison DEADLOCK_TIMEOUT 60 # Max time to wait for lock in distributed
TXTIMEOUT 300 # Transaction timeout for I-STAR (in seconds) SPINCNT 0 # No. of times process tries for latch
env.
Installation and Initial Configuration 1-19
Set Up Your Initial Configuration
STAGEBLOB # INFORMIX-OnLine/Optical staging area
# System Page Size
BUFFSIZE machine-specific # Page size (do not change!)
(multiprocessor-machine default is 300)
Set Up Your Initial Configuration
This chapter uses a workbook approach to help you define your initial configuration. The configuration worksheet lists each parameter needed for initialization. The default value for the parameter is displayed in bold type next to the parameter name. Additional lines are provided for you to record your parameter values where they differ from the default. Where appro­priate, the worksheet includes calculation workspace.
In the pages that follow, each tbconfig. std parameter group is defined in detail, along with guidelines and instruction to help you choose a value that is appropriate for your environment. The topics are organized according to the layout of the tbconfig.std file.
Before you begin, decide on your immediate use for OnLine. Do you plan to use OnLine in a learning environment for a short time, or do you plan to use OnLine in a production environment right away?
If you plan to experiment with OnLine as part of learning the product, you can use the default configuration parameters wherever they are provided. If your goal is to initialize OnLine for a production environment right away, carefully consider the effect of each parameter within your application environment.
Refer to page 1-23 for an explanation of how this decision (default or custom configuration) affects the size of the root dbspace.
1-20 IBM Informix OnLine Database Server Administrator’s Guide
Root Dbspace Configuration Guidelines
Root Dbspace Configuration Guidelines
The root dbspace, like all dbspaces, consists of at least one chunk. You can add other chunks to the root dbspace after OnLine is initialized. All disk configuration parameters refer to the first (initial) chunk of the root dbspace.
The root dbspace contains information that is critical for OnLine operation. Specific control and tracking information needed for OnLine operation is
stored in the root dbspace reserved pages. At initialization, the root dbspace also contains the physical log and all
OnLine logical log files. After OnLine is initialized, you can move the logs to other dbspaces to improve performance.
During operation, the root dbspace is the default location for all temporary tables created implicitly by OnLine to perform requested data management. The root dbspace is also the default dbspace location for any CREATE
DATABASE
ROOTNAME
statement.
Select a name for the root dbspace for this OnLine configuration. The name mustbe unique among all dbspaces and blobspaces. The name cannot exceed 18 characters. Valid characters are restricted to digits, letters, and the under­score.Informixrecommends that you selecta name thatis easily recognizable as the root dbspace. The default value of ROOTNAME is rootdbs.
Installation and Initial Configuration 1-21
Root Dbspace Configuration Guidelines
ROOTPATH
The ROOTPATH parameter specifies the pathname of the initial chunk of the root dbspace. ROOTPATH is stored in the OnLine reserved pages as a chunk name.
Informix recommends that, instead of entering the actual device name for the initial chunk, you define dbspace initial chunk. The link enables you to quickly replace the disk where the chunk is located. The convenience becomes important if you need to restore your OnLine data. The restore process requires that all chunks that were accessible at the time of the last archive are accessible when you perform the restore. The link means that you can replace a failed device with another device and link the new device pathname to ROOTPATH. You do not need to wait for the original device to be repaired.
For now, select a link pathname as the chunk pathname for ROOTPATH.You will determine the actual chunk pathname for the root dbspace when you allocate disk space. (Refer to page 1-40.) Since the number of chunks managed by OnLine is affected by the length of the chunk names, select a short pathname. The default value of ROOTPATH is the link pathname /dev/online_root.
ROOTPATH as a pathname that is a link to the root
ROOTOFFSET
ROOTOFFSET specifies the offset into the disk partition or into the device to
reach the initial chunk. Leave this worksheet field blank until you allocate OnLine disk space. (Refer to page 1-40.) The default value of ROOTOFFSET is 0 KB.
1-22 IBM Informix OnLine Database Server Administrator’s Guide
Root Dbspace Configuration Guidelines
ROOTSIZE
ROOTSIZE specifies the size of the initial chunk of the root dbspace, expressed
in kilobytes. The size that you select depends on your immediate plans for OnLine.
The ROOTSIZE default value is 20,000 KB (about 19.5 MB). If you are configuring OnLine for a learning environment, plan to make the
root dbspace 20 to 60 MB. If you plan to add test databases to this system, choose the larger size. Enter this value in two places on the configuration worksheet. First, enter it as the size of the root dbspace in the ROOTSIZE field. Second, enter it into the field labeled Size of the root dbspace on the second page under the heading Disk Layout.
If you are configuring OnLine for a production environment, you need to calculate an appropriate size for the root dbspace.
At the time of initial configuration, the root dbspace must be large enough to accommodate five possible components:
Physical log
Logical log files
Disk space allocated to accommodate temporary internal tables
needed by OnLine for processing
Diskspace allocated to accommodate any databases or tblspaces that
you might want to store in the root dbspace
Disk space to accommodate OnLine control information
Your worksheet contains blanks for you to enter the sizes of these component parts of the root dbspace, as you determine them. Do not complete this section of your worksheet now.You will complete each blank, A through J, as you work through the disk allocation tasks. (Refer to page 1-40.)
Installation and Initial Configuration 1-23
Mirroring Configuration Guidelines
Mirroring Configuration Guidelines
Mirroring is not required, but it is strongly recommended. Refer to page 4-14 for a complete discussion of mirroring and mirroring administration.
Mirroringis a strategy that pairs primary chunks of one defined blobspace or dbspace with equal-sized mirror chunks. Writes to the primary chunk are duplicated asynchronously on the mirror chunk.
Any database that has extreme requirements for reliability in the face of hardwarefailure should be located in a mirrored dbspace. Above all, the root dbspace should be mirrored.
The same OnLine database server on the same host machine must manage both chunks of a mirrored set. Mirroring on disks managed over a network is not supported. For a complete description of mirroring and how it works, refer to page 4-14.
MIRROR
The MIRROR parameter is a flag that indicates whether mirroring is enabled for OnLine. The default value of MIRROR is 0, indicating mirroring is disabled. The alternative value of MIRROR is 1, indicating mirroring is enabled.
Enable mirroring if you plan to create a mirror for the root dbspace as part of initialization. Otherwise, leave mirroring disabled. If you later decide to add mirroring, you can change the parameter value through DB-Monitor or by editing your configuration file. (Refer to page 3-104.)
MIRRORPATH
The MIRRORPATH parameter specifies the full pathname of the chunk that will serve as the mirror for the initial chunk of the root dbspace (ROOTPATH).
MIRRORPATH should be a link to the chunk pathname of the actual mirror
chunk for the same reasons that ROOTPATH is specified as a link. (Refer to
page 1-22.) Similarly, you should select a short pathname for the mirror
chunk. No default value is provided, but /dev/mirror_root is one suggestion for a link pathname.
1-24 IBM Informix OnLine Database Server Administrator’s Guide
Physical Log Configuration Guidelines
MIRROROFFSET
The MIRROROFFSET parameter specifies the offset into the disk partition or into the device to reach the chunk that serves as the mirror for the root dbspace initial chunk. Leave this worksheet field blank until you allocate OnLine disk space.
Physical Log Configuration Guidelines
This section describes how to assign values to the physical log parameters. The physical log is a block of contiguous disk space that serves as a storage
area for copies of unmodified disk pages. The physical log is a component of OnLine fast recovery, a fault-tolerant feature that automatically recovers OnLine data in the event of a system failure. Refer to page 4-39 for more information about fast recovery. Refer to page 2-152 for detailed information about the physical log.
PHYSDBS
PHYSDBS specifies the name of the dbspace that contains the physical log.
For the initial configuration, the physical log must be created in the initial chunk of the root dbspace. For this reason, you do not specify PHYSDBS as part of the configuration. It is assigned by default to the valueof ROOTNAME.
After additional dbspaces have been defined, you can move the physical log to another dbspace to reduce disk contention.
PHYSFILE
PHYSFILE specifies the size of the physical log in kilobytes. A general
guideline for sizing your physical log is that the size of the physical log should be about twice the size of one logical log file.
A more precise guideline is that total disk space allocated to the physical log and the logical log files should equal about 20 percent of all dbspace dedicated to OnLine. The ratio of logical log space to physical log space should be about 3:1.
Installation and Initial Configuration 1-25
Logical Log Configuration Guidelines
Refer to page 1-42for guidelines on deciding how much disk space should be dedicated to OnLine dbspaces. Refer to page 1-26 for information about sizing the logical log files.
The default value of PHYSDBS is 1,000 KB. The default values included in the tbconfig.std file adhere to both of the
guidelines just described. The size of the physical log is 1,000 KB. The default value of logical log size is 3,000 KB. Total space devoted to the physical and logical logs is 4,000 KB. This value meets the first criterion of 20 percent of the root dbspace, which is 20,000 KB. The strategy also meets the second recommen­dation to allocate logging space in a ratio of 3:1, logical log space to physical log space.
LOGSIZE is 500 KB. The default value of LOGFILES is 6. Thus, total
Logical Log Configuration Guidelines
Thissection describes how to assign initial configurationvalues to the logical log parameters. Refer to page 3-14 for a detailed discussion of logical log configu­rationguidelines.Refer to page 4-18for anoverviewof the mechanics of OnLine blobspace and dbspace logging.
OnLine supports transaction logging, which is the ability of the database server to track and, if needed, to roll back all changes made to the database during application transactions. OnLine transaction logging is implemented by recording each change made to a database in disk space allocated for the OnLine logical log files.
The logical log files contain a history of all database changes since the time of the last archive. At any time, the combination of OnLine archive tapes plus OnLine logical log files contain a complete copy of your OnLine data.
As OnLine administrator,you decide on the optimum total size of the logical log: LOGFILES multiplied by LOGSIZE. The optimum size of the logical logs is based on the length of individual transactions. (OnLine does not permit a single transaction to span all logical log files.) Refer to page 2-156 for detailed information on selecting values for LOGFILES and LOGSIZE that are specifi­cally tuned to your application environment.
1-26 IBM Informix OnLine Database Server Administrator’s Guide
Logical Log Configuration Guidelines
LOGFILES
LOGFILES specifies the number of logical log files managed by OnLine.
The minimum number required for OnLine operation is three log files. The maximum number is determined by the number of logical log descriptors that can fit on a page. For a 2-KB page, the maximum number is about 60 log files. The default value of
Select the number of logical log files after you determine a general size for total logical log size and you select a size for each logical log file.
LOGFILES is 6.
LOGSIZE
LOGSIZE specifies the size of each logical log file managed by OnLine.
The minimum size for a single logical log file is 200 KB. The default value of
LOGSIZE is 500 KB.
A general guideline for sizing the individual logical log files is derived from the guideline for all logging space: the total disk space allocated to the physical log and the logical log files should equal about 20 percent of all dbspace dedicated to OnLine. The ratio of logical log space to physical log space should be about 3:1.
The default values included in the tbconfig.std file adhere to the guideline just described. The default value of LOGSIZE is 500 KB. The default value of
LOGFILES is 6. Totallogical log size is 3,000 KB. The size of the physical log is
1,000KB. Totalspacedevoted to the physical and logical logs is 4,000 KB. This value meets the first criterion of 20 percent of the root dbspace, which is 20,000 KB. The strategy also meets the second recommendation to allocate logging space in a ratio of 3:1, logical log space to physical log space.
Installation and Initial Configuration 1-27

Message File Guidelines

Message File Guidelines
The console receives messages that deserve your immediate attention–for example, alerting you that your logical logs are full. The OnLine message log contains a more complete set of messages that record OnLine activity but rarely require immediate action.
MSGPATH
MSGPATH specifies the UNIX pathname of the OnLine message file. OnLine
writes status messages and diagnostic messages to this message file during operation. The default value for MSGPATH is /usr/informix/online.log.
CONSOLE
CONSOLE specifies the pathname destination for console messages. The
default value for CONSOLE is /dev/console, which sends messages to the system console screen.

Archive Tape Device Guidelines

This section describes how to assign initial configuration values to the archive tape device parameters. Refer to page 3-50 for a detailed discussion of archive tape configuration guidelines.
As OnLine administrator, you are responsible for creating and maintaining archives. OnLine supports several different archiving strategies, including online archiving, remote archiving, and incremental archiving.
Informix strongly recommends that your OnLine environment include two tape devices, one for archiving and a second for backing up the logical log files to tape. If you must use the same device for archiving and for backing up the logical logs, plan your archive schedule carefully to eliminate contention for the one tape device. Refer to page 3-49.
TAPEDEV
TAPEDEV specifies the archive tape device. TAPEDEV can be a link pathname
that points to the actual tape device to provide flexibility in case the actual device is unavailable.
1-28 IBM Informix OnLine Database Server Administrator’s Guide

Logical Log Tape Device Guidelines

The default value of TAPEDEV is /dev/tapedev. You can set the value of TAPEDEV to /dev/null if you are testing or proto-
typing an application, or if you are using OnLine in a learning environment. During OnLine operation, some tasks require that you create an archive. If you set TAPEDEV to /dev/null, you can create an archive instantly, without overhead. However, you are not archiving your OnLine data. You cannot perform a restore.
You can set the value of machine and create archives across your network. For instructions on how to do this, refer to page 3-54.
Tape devices that do not rewind automatically before opening and on closing are considered incompatible with OnLine operation.
TAPEDEV to specify a tape device on another host
TAPEBLK
TAPEBLK specifies the block size of the archive tape device, in kilobytes.
Specifythe largest block size permitted by your tape device. If the tape device pathname is /dev/null, the block size is ignored. The default value of
TAPEBLK is 16KB.
TAPESIZE
TAPESIZE specifies the maximum amount of data that should be written to
each tape, expressed in kilobytes. If the tape device pathname is /dev/null, the tape size is ignored. The default value of TAPESIZE is 10,240KB.
Logical Log Tape Device Guidelines
This section describes how to assign values to the logical log backup tape device parameters. Refer to page 3-13 for a complete list of logical log admin­istration topics related to logical log backups.
As OnLine administrator, you are responsible for the prompt back up of the logical log files. The logical log backup tapes, along with the archive tapes, constitute a complete copy of your OnLine data.
Installation and Initial Configuration 1-29
Logical Log Tape Device Guidelines
OnLine supports a logical log backup option called Continuous-Logging, which backs up each logical log as soon as it becomes full. The Continuous­Loggingoption is recommended forall OnLine configurations, but it requires a dedicated tape device while the option is active.
Informix strongly recommends that your OnLine environment include two tape devices, one for continuous backup of the logical logs and one for archiving.
LTAPEDEV
LTAPEDEV specifies the logical log backup tape device. LTAPEDEV can be a
link pathname that points to the actual tape device to provide flexibility in case the actual device is unavailable.
The default value of LTAPEDEV is /dev/tapedev. You can set the value of LTAPEDEV to /dev/null if you are testing an appli-
cation or if you are using OnLine in a learning environment. The only advantage of doing this is to eliminate the need for a tape device. However, you cannot recover OnLine data beyond that which is stored as part of an archive.
You can set the value of LTAPEDEV to specify a tape device on another host machine and perform logical log backups across your network. For instruc­tions on how to do this, refer to page 3-19.
Tape devices that do not rewind automatically before opening and on closing are considered incompatible with OnLine operation.
LTAPEBLK
LTAPEBLK specifies the block size of the logical log backup tape device, in
kilobytes. Specify the largest block size permitted by your tape device. If the pathname of the tape device is /dev/null, the block size is ignored. The default value of LTAPEBLK is 16KB.
1-30 IBM Informix OnLine Database Server Administrator’s Guide
Identification Parameter Guidelines
LTAPESIZE
LTAPESIZE specifies the maximum amount of data that should be written to
each tape, expressed in kilobytes. If the pathname of the tape device is /dev/null, the tape size is ignored. The default value of LTAPESIZE is 10,240KB.
Identification Parameter Guidelines
This section describes how to assign values to the OnLine identification parameters.
OnLine identification parameters are an issue if you are configuring more than one OnLine database server for a single host machine or if you plan to integrate this OnLine database server into a network of OnLine servers that use the client/server capabilities of IBM Informix STAR.
In either case, the database server processes require a method to uniquely identify their associated OnLine shared memory space within UNIX shared memory. The identification key is linked to the value of the SERVERNUM parameter.
For a complete discussion of configuration issues affected by multiple residency, refer to page 9-7. For information about IBM Informix STARconfig­uration issues, refer to the IBM InformixNET and IBM Informix STARInstallation and Configuration Guide. For more information about the IBM Informix STAR configuration parameters in the tbconfig.std file, refer to page 9-57.
SERVERNUM
SERVERNUM specifies a unique identification number associated with this
specificoccurrenceof OnLine. The identifier distinguishes this OnLine server from all other database servers in the $INFORMIXDIR directory and the network, if one exists.
The default value of SERVERNUM is 0. The value cannot exceed 255. If OnLine and earlier database servers co-exist on the same machine, they
must have unique values for SERVERNUM.The SERVERNUMvalue for earlier servers is implicitly set to 0. Therefore, OnLine requiresa value that is greater than 0.
Installation and Initial Configuration 1-31

Shared-Memory Parameter Guidelines

DBSERVERNAME
DBSERVERNAME specifies a unique name associated with this specific occur-
rence of OnLine. The identifier distinguishes this OnLine server from all other database servers in the $INFORMIXDIR directory and the network, if one exists.
The value of are restricted to digits, letters, and the underscore. The default value of
DBSERVERNAME is ONLINE.
DBSERVERNAME cannot exceed 18 characters. Valid characters
Shared-Memory Parameter Guidelines
This section describes how to assign values to the OnLine shared-memory parameters.
As part of the initialization procedure, DB-Monitor prompts you to enter the values of all but eight of the shared-memory parameters listed in tbconfig.std.
These eight parameters are used to tune performance. Default values are used during initialization. Tuning is best done later, when you can monitor and evaluate OnLine performance under typical working conditions. The eight performance parameters do not appear on the configuration worksheet but they are described in this section.
RESIDENT
The value of RESIDENT indicates whether OnLine shared memory remains resident in UNIX physical memory. If your UNIX system supports forced residency, you can specify that OnLine shared memory is not swapped to disk.
The size of OnLine shared memory is a factor in your decision. Before you decide on residency, verify that the amount of physical memory available aftersatisfying OnLine requirementsis sufficientto execute allrequiredUNIX and application processes.
The default value of RESIDENT in tbconfig.std is 0, indicating that residency is not enforced. A value of 1 indicates that residency is enforced.
1-32 IBM Informix OnLine Database Server Administrator’s Guide
Shared-Memory Parameter Guidelines
USERS
USERS specifies the maximum number of user processes that can concur-
rently attach to shared memory. The value can have a large effect on the size of shared memory because it determines the minimum values for four other shared-memory parameters (LOCKS, TBLSPACES, BUFFERS, and
TRANSACTIONS.)
To arrive at a value for of user processes active at any one time plus the value of CLEANERS, plus 4. Add one more user process if you intend to implement mirroring.
The minimum value is equal to the value of CLEANERS plus 4, plus 1 if mirroring is enabled. The maximum value is 1000. The default value is 20.
(The four required user processes are the master daemon, tbinit; the under­taker daemon, tbundo; the DB-Monitor process, tbmonitor; and one additional user process to ensure a slot for an administrative process. If you enable mirroring, an additional mirror daemon is needed.)
USERS, specify the highest likely value for the number
TRANSACTIONS
The value of TRANSACTIONS refers to the maximum number of concurrent transactions supported by OnLine.
The minimum value of TRANSACTIONS is the value of USERS. The maximum value is the number of transactions that can fit into a checkpoint record, which for OnLine 5.x is 1,364.
By default, OnLine sets the value of TRANSACTIONS equal to the value of
USERS. DB-Monitor does not prompt for this value during initialization. The
default value is appropriate unless you plan to use OnLine in an X/Open environment. If you are configuring OnLine for use with IBM Informix
TP/XA, refer to the IBM Informix TP/XA User Manual.
LOCKS
LOCKS specifies the maximum number of locks available to OnLine user
processesduring processing. The number of lockshas a relativelysmall effect on the size of shared memory. The minimum value for LOCKS is equal to 20 locks per user process. The maximum value is 8 million. The default value is
2000.
Installation and Initial Configuration 1-33
Shared-Memory Parameter Guidelines
BUFFERS
BUFFERS specifies the maximum number of shared-memory buffers available
to OnLine user processes during processing. The minimum value for
is 32,000. The default value is 200. As a general guideline, buffer space should range from 20 to 25 percent of
physical memory. Informix recommends that you initially set BUFFERS so that buffer space (the value of BUFFERS multiplied by BUFFSIZE) is equal to 20 percent of physical memory. Then calculate all other shared-memory parameters.
If you find that after you have configured all other parameters you can afford to increase the size of shared memory, increase the value of BUFFERS until buffer space reaches the recommended 25 percent upper limit.
BUFFERS is 4 per user process. The maximum value
TBLSPACES
TBLSPACES specifies the maximum number of active (open) tblspaces.
Temporary tables and system catalog tables are included in the active table count.
The minimum value for TBLSPACES is 10 per user process. This minimum must be greater than the maximum number of tables in any one database, including the system catalog tables, plus 2. (This minimum is required to permit OnLine to execute a DROP DATABASE statement.) The maximum value is 32,000. The default value is 200. Consider the demands of your appli­cation when you assign a value.
1-34 IBM Informix OnLine Database Server Administrator’s Guide
Shared-Memory Parameter Guidelines
CHUNKS
CHUNKS specifies the maximum number of chunks supported by OnLine.
The value specified should be as close as possible to the maximum number permitted, which is operating-system dependent.
The maximum number of chunks is the lesser of two values:
The number of chunk entries (pathnames) that can fit on an OnLine
page
The maximum number of open files per process allowed by the
operating system, minus 6
The default value for CHUNKS is 8. For specific instructions on how to calculate the number of chunk entries that can fit on a page, refer to page 2-93.
DBSPACES
DBSPACES specifies the maximum number of dbspaces supported by
OnLine. The maximum number of dbspaces is equal to the value of CHUNKS, since each dbspace requires at least one chunk. The minimum value is 1, representing the root dbspace. The default value is 8.
PHYSBUFF
PHYSBUFF specifies the size in kilobytes of each of the two physical log
buffers in shared memory. Double buffering permits user processes to write to the active physical log buffer while the other buffer is being flushed to the physical log on disk.
The recommended value for PHYSBUFF is 16 pages, or 16 multiplied by
BUFFSIZE. (BUFFSIZE is the machine-specific page size and is the last
parameter listed in tbconfig.std.) The default value is 32KB.
LOGBUFF
LOGBUFF specifies the size in kilobytes of each of the three logical log buffers
in shared memory. Triple buffering permits user processes to write to the active buffer while one of the other buffers is being flushed to disk. If flushing isnot complete by the time the active buffer fills, user processes begin writing to the third buffer.
Installation and Initial Configuration 1-35
Shared-Memory Parameter Guidelines
The recommended value for LOGBUFF is 16 pages, or 16 multiplied by
BUFFSIZE.(BUFFSIZEis the machine-specific page size and the last parameter
listed in tbconfig.std.) The default value is 32KB.
LOGSMAX
LOGSMAX specifies the maximum number of logical log files that OnLine
supports. OnLine requires at least three logical log files for operation. In general, you can set the value of LOGSMAX equal to the value of LOGFILES.If you plan to relocate the logical log files out of the root dbspace after you initialize OnLine, assign for this is explained on page 3-31, which describes how to move the logical log files to another dbspace.
The default value of LOGSMAX is 6. The maximum number of logical log files that you can display using DB-Monitor is 50. (You can display any number of log files using the tbstat utility.)
CLEANERS
CLEANERS specifies the number of additional page-cleaner daemon
processes available during OnLine operation. (By default, one page-cleaner process is always available.)
LOGSMAX the value of LOGFILES, plus 3. The reason
A general guideline is one page cleaner per physical device, up to a maximum of eight. Youmight be able to tune the value to achieve an increase in performance. Refer to page 5-19.
The maximum value for CLEANERS is 32. The minimum value is 0. The default value is 1. (The value specified has no effect on the size of shared memory.)
SHMBASE
SHMBASE specifies the base address where shared memory is attached to the
memory space of a user process. Do not change the value of SHMBASE. The default value for SHMBASE is platform-dependent. DB-Monitor does not prompt for this value during initialization. For more information about the role of SHMBASE in initialization, refer to page 2-26.
1-36 IBM Informix OnLine Database Server Administrator’s Guide
Shared-Memory Parameter Guidelines
CKPTINTVL
CKPTINTVL specifies the maximum interval, expressed in seconds, that can
elapse before OnLine checks to determine if a checkpoint is needed. The default value for CKPTINTVL is 300 seconds, or five minutes.
DB-Monitor does not prompt for this value during initialization. You can tune
this parameter to affect performance. Refer to page 5-20.
LRUS
LRUS specifies the number of LRU (least recently used) queues in the shared-
memory buffer pool. The role of the LRU queues is described on page 2-58. The default value for LRUS is the larger of USERS/2 or 8, where USERS is the
value of the configuration parameter. DB-Monitor does not prompt for this value during initialization. You can tune this parameter to affect perfor­mance. Refer to page 5-19.
LRU_MAX_DIRTY
LRU_MAX_DIRTY specifies the percentage of modified pages in the LRU
queues that, when reached, flags the queue to be cleaned. The interaction betweenthe page-cleaner daemon processes and the LRU queues is described on page 2-58.
The default value for LRU_MAX_DIRTY is 60 percent.
DB-Monitor does not prompt for this value during initialization. You can tune
this parameter to affect performance. Refer to page 5-19.
LRU_MIN_DIRTY
LRU_MIN_DIRTY specifies the percentage of modified pages in the LRU
queues that, when reached, flags the page cleaners that cleaning is no longer mandatory. Page cleaners might continue cleaning beyond this point under some circumstances. The interaction between the page-cleaner daemon processes and the LRU queues is described on page 2-58.
The default value for LRU_MAX_DIRTY is 50 percent.
Installation and Initial Configuration 1-37
Shared-Memory Parameter Guidelines
DB-Monitor does not prompt for this value during initialization. You can tune
this parameter to affect performance. Refer to page 5-19.
LTXHWM
LTXHWM specifies the “long transaction high-water mark.” In the logical log, LTXHWM is the percentage of available logical log space that, when filled,
triggers the tbinit daemon to check for long transactions. If a long transaction is found, the transaction is aborted and the executing OnLine database server process rolls back all modifications associated with this transaction.
The default value for
LTXHWMis 50 percent. This means that up to 50 percent
ofthe available log space can be spanned by one user's transaction. When this level is exceeded, the OnLine database server process is signalled to immedi­ately roll back that transaction. The rollback procedure continues to generate logical log records, however, so the logical log continues to fill. This is the reason for the LTXEHWM parameter.
DB-Monitor does not prompt for this value during initialization. Refer to
page 2-159 for more information about LTXHWM.
LTXEHWM
LTXEHWM specifies the “long transaction, exclusive access, high-water
mark.” The LTXEHWM must be a higher percentage than the LTXHWM percentage. If the logical logs fill to LTXEHWM, the long transaction currently being rolled back (refer to LTXHWM) is given “exclusive” access to the logical log. The term “exclusive” is not entirely accurate. Most OnLine activity is suspended until the transaction has completed its rollback, but transactions that are in the process of rolling back or committing retain access to the logical log.
The default value for LTXEHWM is 60 percent.
DB-Monitor does not prompt for this value during initialization. Refer to
page 2-159 for more information about LTXEHWM.
1-38 IBM Informix OnLine Database Server Administrator’s Guide
Machine- and Product-Specific Parameter Guidelines
Machine- and Product-Specific Parameter Guidelines
Because your machine or product environment might not support these parameters, they do not appear on the configuration worksheet. DB-Monitor does not prompt for any of these values during initialization.
DYNSHMSZ
The DYNSHMSZ parameter affects your OnLine configuration only if you plan to use OnLine with the value for DYNSHMSZ is 0. After you initialize OnLine, you can modify the value as required by your environment. Refer to theIBM Informix TP/XA product documentation for information about setting this parameter.
GTRID_CMP_SZ
TheGTRID_CMP_SZ parameter affects your OnLine configuration only if you are planning to use OnLine with the IBM Informix TP/XA library product. The default value for GTRID_CMP_SZ is 32 bytes. After you initialize OnLine, you can modify the value as required by your environment. Refer to the
IBM Informix TP/XA product documentation for information about setting
this parameter.
IBM Informix TP/XA library product. The default
DEADLOCK_TIMEOUT
TheDEADLOCK_TIMEOUT parameter affects your OnLine configuration only if you areplanning to use OnLine with IBM Informix STAR.Refer to page 9-57 for information about using OnLine in a client/server environment.
TXTIMEOUT
The TXTIMEOUT parameter affects your OnLine configuration only if you are planning to use OnLine with IBM Informix STAR. Refer to page 9-57 for infor­mation about using OnLine in a client/server environment.
Installation and Initial Configuration 1-39

OnLine Disk Space Allocation

SPINCNT
The SPINCNT parameter affects only multiprocessor machines that use spin­and-retry latch acquisition. SPINCNT specifies the number of times that a process attempts to acquire a latch in shared memory before it enters a wait mode.
The default value of
SPINCNT on a uniprocessor machine is 0. The default
value of SPINCNT on a multiprocessor machine is 300. Refer to page 5-24 for information about tuning the value of this parameter.
OnLine Disk Space Allocation
This section explains how to allocate disk space for OnLine. The disk space allocation task can be divided into four smaller tasks:
Decide whether to dedicate raw disk space or cooked files to OnLine
Determine how much disk space to dedicate to OnLine
Decide how to apportion the disk space (disk layout)
Allocate the disk space
Allocate Raw Disk Space or Cooked Files?
This section describes the advantages and trade-offs between either allocating raw disk space managed by OnLine or storing OnLine data in cooked file space. As a general guideline, you experience greater perfor­mance and increased reliability if you allocate raw disk space.
Each chunk (unit of disk space) that is dedicated to OnLine can be either one of the following:
Raw disk space I/O is managed by OnLine. Cooked file The file contents are managed by OnLine, but the I/O
is managed by the UNIX operating system.
Cookedfiles are easier to allocate thanraw disk space.However,you sacrifice reliability and experience lower performance if you store OnLine data in cooked files.
1-40 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Disk Space Allocation
Cooked files are unreliable because I/O on a cooked file is managed by the
UNIX operating system. A write to a cooked file can result in data being
written to a memory buffer in the UNIX file manager instead of being written immediately to disk. As a consequence, UNIX cannot guarantee that the committed data has actually reached the disk. This is the problem. OnLine recovery depends on the guarantee that data written to disk is actually on disk. If, in the event of system failure, the data is not present on disk, the OnLine automatic recovery mechanism could be unable to properly recover the data. (The data in the UNIX buffer could be lost completely.) The end result could be inconsistent data.
Performance degrades if you give up the efficiency benefits of OnLine­managed I/O. If you must use cooked UNIX files, try to store the least frequently accessed data in the cooked files. Try to store the files in a file system that is located near the center cylinders of the disk device, or in a file system with minimal activity. In a learning environment, where reliability and performance are not critical concerns, cooked files are acceptable. (Since OnLine manages the internal arrangement of data, you cannot edit the contents of a cooked file.)
Significant performance advantages and increased data reliability are ensuredwhenOnLine performs itsown disk management on raw disk space.
Raw disk space appears to your UNIX operating system as a disk device or part of a disk device. In most operating systems, the device is associated with both a block-special file and a character-special file in the /dev directory.
When you link your raw disk space to an OnLine chunk pathname, verify that you use the character-special file for the chunk name, not the block-special file. (The character-special file can directly transfer data between the address space of a user process and the disk using direct memory access (DMA), which results in orders-of-magnitude better performance.)
How Much Disk Space Do You Need?
This section applies only if you are configuring OnLine for a production environment.The first step in answering the question “How much space?” is to calculate the size requirements of the root dbspace. The second step is to estimate the total amount of disk space to allocate to all OnLine databases, including space for overhead and growth.
Installation and Initial Configuration 1-41
OnLine Disk Space Allocation
Calculate Root dbspace Size
Analyze your application to estimate the amount of disk space that OnLine might require for implicit temporary tables, which are tables OnLine creates as part of processing. Implicit temporary tables are stored in the root dbspace and deleted when the database server process ends.
The following types of statements require temporary tblspace:
Statements that include a GROUP BY clause
Statements that include subqueries
Statements that use distinct aggregates
Statements that use auto-index joins
Try to estimate how many of these statements will run concurrently. Estimate the size of these temporary tblspaces by estimating the number of values returned.
Enter this value in the field labeled E under ROOTSIZE on the configuration worksheet (page 1-21).
Next, decide if users will store databases or tables in the root dbspace. One advantage to root dbspace storage is that the dbspace is usually mirrored. If root dbspace is the only dbspace you intend to mirror, place all critical data there for protection. Otherwise, store databases and tables in another dbspace.
Estimate the amount of disk space, if any, that you will allocate for root dbspace tables. Enter this value in the field labeled F under ROOTSIZE on the worksheet (page 1-21).
Now calculate the size of the root dbspace, using the fields A through J that appear on the first page of the configuration worksheet.
The amount of disk space required for OnLine control information is 3 percent of the size of the root dbspace, plus 14 pages, expressed as kilobytes (or 14 x BUFFSIZE).
Complete the worksheet calculations to arrive at the size of the initial chunk for the root dbspace. Enter this value in two places on the configuration worksheet. First, enter it as the size of the root dbspace in the ROOTSIZE field. Second, enter it into the field labeled Size of the root dbspace, on the second page, under the heading Disk Layout.
1-42 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Disk Space Allocation
Project Total Space Requirements
The amount of additional disk space needed for OnLine data storage depends on your production environment. Every application environment is different.The following list suggests some of the steps you might take to help you calculate the amount of disk space to allocate (beyond the root dbspace):
1. Decide how many databases and tables you need to store. Calculate
the amount of space required for each one.
2. Calculate a growth rate for each table and assign some amount of
disk space to each table to accommodate growth.
3. Decide which databases and/or tables you want to mirror.
Refer to IBM Informix Guide to SQL: Tutorial for instructions about calculating the size of your data bases.
After you arrive at a value, enter it on the second page of the configuration worksheet, in the field labeled Additional disk space for OnLine chunks.
How Should You Apportion Disk Space?
When you allocate disk space (raw disk or cooked files), you allocate it in units called chunks. A dbspace or a blobspace is associated with one or more chunks. You must allocate at least one chunk for the root dbspace.
(Refer to page 2-81 for a discussion of the relationships among chunks, dbspaces, blobspaces, databases, and tblspaces.)
Informix recommends that you format your disk(s) so that each chunk is associated with its own UNIX disk partition. When every chunk is defined as a separate partition (or device), you will find it is easy to track disk space usage. You avoid errors caused by miscalculated offsets.
If you are working with a disk that is already partitioned, you might be required to use offsets to define a chunk.
Installation and Initial Configuration 1-43
OnLine Disk Space Allocation
After you decide how you plan to define the chunks, decide on the number of chunks you plan to create and a size for each. The size of a chunk is mostly determined by storage considerations:
With which blobspace or dbspace is this chunk associated?
Which databases, tables, or blob columns arestored in this blobspace
or dbspace?
How many chunks (of what size) compose the dbspace or blobspace?
Issues of disk contention and mirroring should also influence your decisions regarding the size of the chunks:
Where should high-use tables be located to reduce contention?
Where should the mirror chunks for each primary chunk be located
to maximize fault tolerance?
Space Allocation in a Learning Environment
If you are configuring OnLine for a learning environment, allocate a single chunk for the root dbspace. Allocate a second chunk on a different device if you plan to mirror the root dbspace. Ideally, different controllers should manage the devices. The mirror chunk should be the size of the root dbspace, specified as ROOTSIZE.
Space Allocation in a Production Environment
The configuration worksheet provides space for you to record your decisions regarding each chunk: its size, linked pathname, actual pathname, and associated dbspace or blobspace. Refer to ROOTPATH and MIRRORPATH for the linked pathnames for the root dbspace chunk and the mirror root chunk, respectively. Guidelines for making these decisions follow.
In a production environment, your goal is to minimize hardware disk contention; that is, to limit the amount of disk head movement across a disk and reduce the number of times processes compete for access to the same disk.
1-44 IBM Informix OnLine Database Server Administrator’s Guide
Figure 1-2 illustrates four guidelines for planning the physical layout of your
OnLine data. Each guideline is described in detail in the text that follows.
Consider mirroring
primary mirror
Isolate high-use tables
disk platter
OnLine Disk Space Allocation
Figure 1-2
Guidelines for planning your disk layout
Locate a primary chunk and its mirror chunk on different disks.
high-use table Locate high-use tablesontheirowndeviceatthecenterof the disk
or spread them across multiple devices.
Consider rapidly growing tables
table 1 extents table 2 extents table 3 extents
Plan for the final location of the physical and logical logs
logical log physical log
Avoid dbspace fragmentation caused by improperly sized extents.
Separate the logs and locate them on disks not shared by active tables.
Installation and Initial Configuration 1-45
OnLine Disk Space Allocation
Critical tables and databases should be mirrored. The root dbspace should be mirrored. Mirroring is specified by chunk. Locate the primary and the mirrored chunk on different disks. Ideally, different controllers should handle the disks.
You can place a table with high I/O activity on a disk device that is dedicated to its use. When disk drives have different performance levels, you can put the tables with the most use on the fastest drives. Separate disk devices reduce competition for disk access when joins are formed between two high­demand tables.
To reduce contention between programs using the same table, you can attempt to spread the table across multiple devices. To do this, locate a tblspace in a dbspace that includes multiple chunks, each of which are located on different disks. Although you have no control over how the table data is spread across the chunks, this layout might result in multiple disk access arms for one table.
To minimize disk head movement, place the most frequently accessed data as close to the middle partitions of the disk as possible. (When a disk device is partitioned, the innermost partitions have cylinder numbers that are nearestthemiddle of therange of cylinder numbers and generally experience the fastest access time.) Place the least frequently used data on the outermost partitions. This overall strategy minimizes disk head movement.
When two or more large, growing tables share a dbspace, their new extents can be interleaved. This interleaving creates gaps between the extents of any one table. Performance might suffer if disk seeks must span more than one extent. Work with the table owners to optimize the table extent sizes, or consider placing the tables in separate dbspaces.
Both the logical log files and the physical log are extremely active and should be given priority in disk placement. Both should be on the fastest devices and on the most central disk cylinders.
The initial configuration automatically places the physical and logical logs in the initial chunk of the root dbspace. Since the root dbspace also is extremely active, you can place the root dbspace on the most central disk cylinder and createother dbspaces foruser database tables. Another strategy is to improve performance by physically separating the logs and placing them in separate dbspaces on disk devices that are not shared by active tables. For instructions on how to change the location of the logical and physical log after initial­ization, refer to page 3-31 and page 3-107, respectively.
1-46 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Disk Space Allocation
The logs contain critical information and should be located in mirrored dbspaces, despite the fact that their high level of activity makes it costly (in terms of performance) to do so.
Compare the total amount of dbspace disk space (exclude blobspaces) that you intend to allocate to OnLine with the amount of space dedicated to OnLine logging (physical log size plus total space allocated for the logical log files). Ideally, logging space should be about 20 percent of total dbspace. Adjust your worksheet values, if necessary.
(You should have values entered for all parameters on the worksheet except for ROOTOFFSET and MIRROROFFSET. Guidelines for these parameters are described as part of the next topic.)
How to Allocate Disk Space
This section provides instructions for allocating raw disk space or cooked files.
Cooked File Space
To allocate cooked file space, concatenate null to a pathname that represents one chunk of cooked file space. The cooked disk space file should have permissions set to 660 (rw-rw----). Group and owner must be set to informix.
# become root su root password: # create file for the cooked device cat /dev/null > chunk_pathname # establish correct ownership chown informix chunk_pathname chgrp informix chunk_pathname chmod 660 chunk_pathname exit
If you areplanning to locate your root dbspace in a cooked file, verify that the pathnamefor the cooked file is the valueof ROOTPATH on your configuration worksheet.
Installation and Initial Configuration 1-47
OnLine Disk Space Allocation
Raw File Space
Consult your UNIX system manuals for instructions on how to create and install a raw device.
In general, you can either repartition your disks or unmount an existing file system. In either case, take proper precautions to back up any files before you unmount the device.
Change the group and owner of the character-special devices to informix. (The filename of the character-special device usually begins with the letter r (for example, /dev/rsd1f).
Verify that the UNIX permissions on the character-special devices are 660. Usually, the character-special designation and device permissions appear as crw-rw---- if you execute the UNIX ls -l command on the filename. (Some
UNIX systems vary.)
Many UNIX systems keep partition information for a physical disk drive on the drive itself in a volume table of contents (VTOC). The VTOC is commonly stored on the first track of the drive. A table of alternate sectors (and bad­sector mappings) can also be stored on the first track.
If you plan to allocate partitions at the start of a disk, use offsets to prevent OnLine from overwriting critical information required by UNIX. Specify an offset for the root dbspace or its mirror with the ROOTOFFSET and
MIRROROFFSET parameters, respectively.
Create a link between the character-special device name and another filename with the UNIX link command, usually ln.
Do not mount the character-special device. Do not create file systems on the character-special devices.
Execute the UNIX command ls-lg on your device directory to verify that both the devices and the links exist. An example output follows, although your
UNIX system display might differ slightly:
crw-rw---- 1 informix Mar 7 14:30 /dev/rxy0h crw-rw---- 1 informix Mar 7 14:30 /dev/rxy0a lrwxrwxrwx 1 informix Mar 7 15:15 /dev/my_root@->/dev/rxy0h lrwxrwxrwx 1 informix Mar 7 15:15 /dev/raw_dev2@->/dev/rxy0a
1-48 IBM Informix OnLine Database Server Administrator’s Guide
OnLine Disk Space Allocation
Evaluate UNIX Kernel Parameters
Your OnLine product arrives with a machine-specific file, $INFOR-
MIXDIR
kernel parameters. Compare the values in this file with your current UNIX configuration.
If the recommended values for OnLine differ significantly from your current environment, consider modifying your
Background information that describes the role of the UNIX kernel param­eters in OnLine operation is provided on page 2-18.
/release/ONLINE_5.x, that contains recommended values for UNIX
UNIX kernel settings.
Installation and Initial Configuration 1-49
Configuration Checklist
Configuration Checklist
Figure 1-3 is a checklist to help you verify that you have correctly completed
the initialization preparation tasks.
Create user and group informix
The only member of the group informix is user informix. Perform OnLine administrative actions as user informix.
Create raw devices
Do not mount raw devices. Raw devices should not include file systems.
Set device permissions
Each raw device should display informix as its group and owner. Permissions on each raw device are set to 660 (crw-rw----).
Verify UNIX kernel parameters
The recommendations for UNIX kernel parameters included in the machine­specific ONLINE_5.x file are compatible with your current UNIX kernel parameters.
Verify chunk offsets, if needed
If your system uses track 0 for control information and if the root dbspace or its mirror is at the start of a partition, check that you included an offset into the device.
Figure 1-3
Checklist to
verify that the
preparation
tasks for OnLine
initialization
have been
completed
correctly
Verify size of root dbspace
Check that the initial chunk of the root dbspace is large enough to contain the physical log, all logical logs, and OnLine overhead requirements.
Verify OnLine shared-memory size
Checkthatthe size youselectedforOnLinesharedmemoryfitswithintheamount of shared memory available on your UNIX machine.
1-50 IBM Informix OnLine Database Server Administrator’s Guide
Enter Your Configuration and Initialize OnLine
Enter Your Configuration and Initialize OnLine
When you configure OnLine for the first time, you specify two sets of parameter values through DB-Monitor. The first set of parameters is the disk parameters; the second set is the shared memory parameters. Each set of values is specified on its own DB-Monitor screen. After you complete both screens, OnLine prompts you to begin the initialization.
Verify that you are logged into your your path includes $
INFORMIXDIR/bin. To access DB-Monitor, enter the
UNIX system as user informix and that
following command at the UNIX prompt:
tbmonitor
The main DB-Monitor menu appears, as follows:
INFORMIX-OnLine: Status <Parameters> Dbspaces Mode Force-Ckpt ... Set configuration parameters.
-------------------Off-line------------------ Press CTRL-W for Help. ------
From the main menu, select the Parameters option. From the Parameters menu options, select Initialize.
Installation and Initial Configuration 1-51
Enter Your Configuration and Initialize OnLine
The disk parameters initialization screen appears. Some fields contain default values. The following screen representation replaces the default values in each field with the name of the OnLine configuration parameter associated with that field:
INITIALIZATION: Make desired changes and press ESC to record changes. Press Interrupt to abort changes. Press F2 or CTRL-F for field-level help.
Page Size [BUFFSIZE ] Kbytes Mirror [MIRROR ]
Sys. Log File [MSGPATH ] System Msgs. [CONSOLE ]
Tape Dev. [TAPEDEV ] Block Size [TAPEBLK ] Kbytes Total Tape Size [TAPESIZE ] Kbytes Log Tape Dev. [LTAPEDEV ] Block Size [LTAPEBLK ] Kbytes Total Tape Size [LTAPESIZE ] Kbytes
Root Name [ROOTNAME ] Root Size [ROOTSIZE] Kbytes
Primary Path [ROOTPATH ]
Mirror Path [MIRRORPATH ]
Phy. Log Size [PHYSFILE ] Kbytes Log. Log Size [LOGSIZE ] Kbytes
DISK PARAMETERS
Offset [ROOTOFFSET ] Kbytes
Offset [MIRROROFFSET ] Kbytes
Number of Logical Logs [LOGFILES ]
To initialize OnLine, enter the values for the disk parameters and use your worksheet as reference.
As you enter values, the last line in the screen changes dynamically, depending on your cursor location. The last line always contains a brief explanation of the value you should enter in the current field. If you press F2 or CTRL-F, additional help messages appear that pertain to the field where your cursor is located.
Atany time duringthe initial configurationsetup, you canpress the Interrupt key to abort your changes and return to the Parameters menu options.
After you complete this disk parameters screen, press ESC to record the values entered. Automatically, the shared-memory parameters screen appears.
1-52 IBM Informix OnLine Database Server Administrator’s Guide

Setting Shared Memory Parameters

Setting Shared Memory Parameters
Like the disk parameters screen, the shared-memory parameters screen appears with some default values in some fields. In the following represen­tation of the shared-memory parameters screen, each default value has been replaced with the name of the OnLine configuration parameter associated with that field:
SHARED MEMORY: Make desired changes and press ESC to record changes. Press Interrupt to abort changes. Press F2 or CTRL-F for field-level help.
Page Size [ BUFFSIZE ] Kbytes
Server Number [ SERVERNUM ] Server Name [ DBSERVERNAME ] Deadlock Timeout [ DEADLOCK_TIMEOUT ] Seconds Forced Residency [ RESIDENT ] Number of Page Cleaners [ CLEANERS ]
Physical Log Buffer Size [ PHYSBUFF ] Kbytes Logical Log Buffer Size [ LOGBUFF ] Kbytes Max # of Logical Logs [ LOGSMAX ] Max # of Users [ USERS ] Max # of Locks [ LOCKS ] Max # of Buffers [ BUFFERS ] Max # of Chunks [ CHUNKS ] Max # of Open Tblspaces [ TBLSPACES ] Max # of Dbspaces [ DBSPACES ] ============ Shared memory size _________ Kbytes
SHARED MEMORY PARAMETERS
Enter the values for each parameter. After you verify that the values are correct, press ESC to indicate that you wish to record your changes. OnLine prompts a verification:
Do you want to keep these changes to the parameters (y/n)?
If you enter n for no, DB-Monitor returns to the Parameters menu. You can begin again if you wish.
Ifyou enter y for yes,OnLine stores the configuration parameter values in the current configuration file, if one exists. If no configuration file exists, OnLine creates a configuration file for you. The file OnLine creates is placed in the $INFORMIXDIR/etc directory. The configuration file is named according to the value specified by TBCONFIG, if it is set. If TBCONFIG is not set, the file is named tbconfig by default.
Installation and Initial Configuration 1-53

Initialize OnLine

Initialize OnLine
OnLineprompts you for confirmation that you wish to initialize immediately using these current values:
Do you really want to continue? (y/n) WARNING: The root dbspace will be initialized. All previous data will be destroyed.
When you initialize OnLine starting from the DB-Monitor Initialize option (disk parameters screen), you are initializing both disk space and shared memory. When you initialize disk space, you automatically re-create a new OnLine database server and destroy all existing data in that space.
Enter
y to direct the tbinit process to initialize both OnLine disk space and
shared memory. If you enter n, the changed parameter values are retained in the configuration
file and DB-Monitor returns to the Parameters menu. If you receive any error messages as OnLine attempts to initialize, turn now
to page 1-60. Otherwise, OnLine displays messages as the initialization proceeds. When initialization is complete, OnLine is in quiescent mode.
AfterOnLine is initialized, continue with the remaining tasks toprepareyour system to receive data:
Set your environment variables
Modify UNIX startup and shutdown scripts, if desired
Create blobspaces and additional dbspaces, if desired

Set Your Environment Variables

You already set the INFORMIXDIR and PATH environment variables before you loaded the software. This section instructs you in setting two additional environment variables:
SQLEXEC The pathname of the database server TBCONFIG
1-54 IBM Informix OnLine Database Server Administrator’s Guide
The OnLine configuration file
Set Your Environment Variables
SQLEXEC
The value of SQLEXEC directs the front-end processes to a specific database server within the $INFORMIXDIR directory. The default value for SQLEXEC is $INFORMIXDIR/lib/sqlturbo, the OnLine database server. If OnLine is the only database server in your $INFORMIXDIR directory, you do not need to define SQLEXEC.
Ifyou worked with an
IBM Informix SE database server on thismachine in the
past, you might have an SQLEXEC environment variable already set for use with SE. If you are not planning to maintain the SE database server but intend to run only OnLine on this machine, you might need to modify SQLEXEC to ensure that it now reflects the OnLine database server.
If you intend to maintain both an SE database server and an OnLine database server on the same machine, ensure that all users have their SQLEXEC variable properly set. (The pathname of the SE database server is $INFORMIXDIR/lib/sqlexec.)
Set the SQLEXEC environment variable as follows:
C shell:
Bourne shell: SQLEXEC=sqlexec_value
setenv SQLEXEC sqlexec_value
export SQLEXEC
TBCONFIG
The TBCONFIG environment variable performs two tasks:
Directs the tbinit process to the OnLine configuration file that is to
be read for initialization values
Directs the OnLine server process (sqlturbo) to the correct OnLine
shared-memory space
The TBCONFIG value is not a full pathname; therefore, all OnLine configu­ration files must reside in the directory $INFORMIXDIR/etc.
Installation and Initial Configuration 1-55

Modify UNIX Startup and Shutdown Scripts

If your environment contains a single OnLine database server, you do not need to explicitly set TBCONFIG. If the tbinit process cannot find the file specified by TBCONFIG, it creates a copy of tbconfig.std, places the copy in the file specified by TBCONFIG, and uses the values in that file for initialization.
You must set TBCONFIG if you changed the name of your configuration file to something other than tbconfig, or if your environment supports two or more OnLine database servers on the same machine. In the latter case, each OnLine server requires a separate, unique configuration file that is stored in $INFORMIXDIR/etc. (Refer also to the discussion of multiple residency on
page 9-7.)
Since each OnLine configuration file requires a unique value for
SERVERNUM, you might prefer to name each file so that it can easily be
related to a specific value. For example, the file tbconfig3 could indicate that this configuration file specifies the unique SERVERNUM of 3.
Set the TBCONFIG environment variable as follows:
C shell: Bourne shell:
setenv TBCONFIG config_filename
TBCONFIG=config_filename export TBCONFIG
Modify UNIX Startup and Shutdown Scripts
You can modify your UNIX startup file to initialize OnLine automatically when your machine enters multiuser mode. You can also modify your UNIX shutdown file to shut down OnLine in a controlled manner whenever UNIX shuts down.
1-56 IBM Informix OnLine Database Server Administrator’s Guide
Modify UNIX Startup and Shutdown Scripts
Startup
Add UNIX and OnLine utility commands to the UNIX startup script that perform the following steps:
1. Set the INFORMIXDIR environment variable to the full pathname of
the directory in which OnLine is installed. (If multiple versions of OnLine are running on your UNIX system, you must reset INFOR-
MIXDIR
2. Set the PATH environment variable to include the $INFOR-
MIXDIR
3. Set the TBCONFIG environment variable to the desired configuration
file.
4. Execute tbinit, which starts OnLine and leaves it in online mode.
Examples of these commands for the C shell and Bourne shell follow:
C shell: setenv INFORMIXDIR/directory_name
for each OnLine system that you initialize.)
/bin directory.
setenv PATH $PATH:$INFORMIXDIR/bin setenv TBCONFIG config_filename tbinit
Bourne shell: INFORMIXDIR= /directory_name
export INFORMIXDIR PATH=$PATH:$INFORMIXDIR/bin export PATH TBCONFIG=config_filename export TBCONFIG tbinit
Installation and Initial Configuration 1-57
Modify UNIX Startup and Shutdown Scripts
Shutdown
Add UNIX and OnLine utility commands to the UNIX shutdown script that perform the following steps:
1. Set the INFORMIXDIR environment variable to the full pathname of
the directory in which OnLine is installed. (If multiple versions of OnLine are running on your UNIX system, you must reset INFOR-
MIXDIR
2. Set the PATH environment variable to include the $INFOR-
MIXDIR
3. Set the TBCONFIG environment variable to the desired configuration
file.
4. Execute tbmode -ky,which initiates immediate shutdown and takes
OnLine offline.
These commands should execute after all user and database server processes have finished working.
Examples of these commands for the C shell and Bourne shell follow:
C shell: setenv INFORMIXDIR /directory_name
for each OnLine system that you shut down.)
/bin directory.
setenv PATH $PATH:$INFORMIXDIR/bin setenv TBCONFIG config_filename tbmode -ky
Bourne shell: INFORMIXDIR= /directory_name
export INFORMIXDIR PATH=$PATH:$INFORMIXDIR/bin export PATH TBCONFIG=config_filename export TBCONFIG tbmode -ky
1-58 IBM Informix OnLine Database Server Administrator’s Guide

Create Blobspaces and Dbspaces

Create Blobspaces and Dbspaces
After OnLine is initialized, you can create blobspaces and dbspaces as desired. If you plan to use blobspaces, Informix recommends that you create one or two blobspaces before you createa dbspace. The reason for this is the way that OnLine archives data. During an archive, OnLine temporarily blocks blobpage allocation in a chunk until the chunk is archived. Since chunks are archived in order, it is to your advantage to create a blobspace early to ensure that the chunks in the blobspace receive low chunk ID numbers. This guarantees that the chunk is archived early in the process and available to receive blobs for the duration of the online archive. Refer to
page 4-35 for more information about what happens during an online
archive. For more information about how to create a blobspace, refer to page 3-88. For more information about how to create a dbspace, refer to page 3-97.

Errors During Initialization

Figure 1-3 on page 1-51 contains a list of preparatory tasks that must be
performed properly for initialization. If any of these actions are omitted or performed incorrectly, errors can result.
If you receive an error during initialization, verify that you performed these tasks properly. If the list does not identify the error, use the following infor­mation to help you interpret the error message. The source of an initialization error message could be either OnLine or your UNIX operating system.
Installation and Initial Configuration 1-59

OnLine Error Message Format

OnLine Error Message Format
The OnLine error message format is straightforward:
-nn Explanatory statement of error condition
OnLine messages begin with a number that identifies the category of error. Explanatory text follows. Use the error message number as a key into the Informix Error Messages manual. The manual lists all Informix error messages (not just OnLine messages), along with information about the cause of the error and corrective actions available to you. An example of an OnLine error message follows:
-146 ISAM error: The other copy of this disk

UNIX Error Message Format

UNIX operating-system error messagesare passed onto you by OnLine. Most
initialization errors generated by UNIX refer to shared-memory resource deficiencies.These messages arereturnedto the OnLine initialization process tbinit. For this reason, tbinit often appears first in the error message text.
is currently disabled or non-existent.
The tbinit process name is typically followed by three items of information generated by UNIX. The specifics of the messages vary, depending both on the error and on your machine platform. In general, the message conforms to the following format:
tbinit:UNIX_call[mnemonic][associated_values]
Your UNIX documentation contains precise information about the cause of any UNIX errors and detailed information about corrective actions.
Usually, UNIX system call errors indicate a deficiency in a shared-memory resource when OnLine is attempting to create its own shared memory. The information on page 2-18 describes how OnLine uses the UNIX kernel param­eters in shared-memory creation. You might be able to diagnose the problem and identify the appropriate corrective action from this information.
1-60 IBM Informix OnLine Database Server Administrator’s Guide

System Architecture

In This Chapter .................... 2-7
Initialization ..................... 2-7
Initialization Commands ............... 2-8
Shared Memory Commands ............. 2-9
Disk Space Commands............... 2-10
What Happens During Shared-Memory Initialization ..... 2-10
Shared-Memory Initialization Procedure......... 2-11
Step 1: Calculate Configuration Values ......... 2-11
Step 2: Create Shared Memory ............ 2-12
Step 3: Attach to Shared Memory ........... 2-12
Step 4: Initialize Shared Memory Structure ........ 2-12
Step 5: Wake Parent Process ............. 2-13
Steps 6 and 7: Initiate Fast Recovery and First Checkpoint . . 2-13
Step 8: Drop Temporary Tables (Optional) ........ 2-13
Step 9: Document Configuration Changes ........ 2-14
Step 10: Check for Forced Residency .......... 2-14
Step 11: Begin Looping as Master Daemon ........ 2-14
What Happens During Disk-Space Initialization ....... 2-14
Step 1: Calculate Configuration Values ......... 2-15
Step 2: Create OnLine Shared Memory ......... 2-16
Step 3: Attach to Shared Memory ........... 2-16
Step 4: Initialize Shared-Memory Structures ....... 2-16
Step 5: Initialize Disk Space ............. 2-16
Step 6: Wake Parent tbinit Process ........... 2-17
Step 7: Initiate First Checkpoint ............ 2-17
Step 8: Change to Quiescent Mode ........... 2-18
Step 9: Set Forced Residency ............. 2-18
Step 10: Loop as Master Daemon ........... 2-18
UNIX Kernel and Semaphore-Allocation Parameters...... 2-18
Chapter
2
OnLine User Processes.................. 2-22
How User Processes Attach to Shared Memory ........ 2-24
Step 1: Obtain SERVERNUM ............. 2-24
Step 2: Calculate Shared-Memory Key Value ....... 2-25
Steps 3 and 4: Request Shared-Memory Segment and
Attach to SHMBASE ............. 2-25
Step 5: Attach Additional Segments .......... 2-27
User Processes and Critical Sections............ 2-28
OnLine User Process Status and States ........... 2-29
OnLine Database Server Process ............. 2-30
Orphaned Database Server Processes ........... 2-31
OnLine Daemon Processes ................ 2-33
tbinit Daemon ................... 2-33
tbundo Daemon .................. 2-34
tbpgcl Daemon ................... 2-34
Shared Memory and Process Communication.......... 2-36
Shared Memory and Buffer Locks ............ 2-38
Buffer Share Lock................. 2-38
Buffer Update Lock ................ 2-38
Buffer Exclusive Lock ............... 2-39
Managing Shared-Memory Resources ........... 2-39
Shared-Memory Latches .............. 2-41
OnLine Timestamps ................ 2-44
Hash Tables and the Hashing Technique ......... 2-46
Shared-Memory Header ................ 2-47
Shared-Memory Internal Tables ............. 2-48
OnLine Buffer Table ................ 2-48
OnLine Chunk Table................ 2-49
OnLine Dbspace Table ............... 2-50
OnLine Latch Table ................ 2-51
OnLine Lock Table ................ 2-51
OnLine Page-Cleaner Table ............. 2-52
OnLine Tblspace Table ............... 2-52
OnLine Transaction Table .............. 2-54
OnLine User Table ................ 2-54
Shared-Memory Buffer Pool .............. 2-55
Regular Buffers ................. 2-56
Big Buffers ................... 2-56
OnLine LRU Queues ................. 2-57
2-2 IBM Informix OnLine Database Server Administrator’s Guide
LRU Queues and Buffer Pool Management..........2-58
LRU_MAX_DIRTY .................2-59
LRU_MIN_DIRTY .................2-59
How a User Process Acquires a Buffer ...........2-60
Step 1: Identify the Data ...............2-61
Step 2: Determine Lock-Access Level ..........2-61
Step 3: Locate the Page in Memory ...........2-61
Step 4: Read the Page in from Disk ...........2-62
Steps 5-7: Lock Buffer, Release Lock, and Wake
Waiting Processes ..............2-62
Physical Log Buffer ..................2-63
Double Buffering .................2-64
Causes of Flushing .................2-64
Flushing a Full Buffer ................2-65
Logical Log Buffer ..................2-66
Triple Buffering ..................2-66
Buffer Contents ..................2-68
Causes of Flushing .................2-68
Flushing a Full Buffer ................2-69
OnLine Checkpoints .................2-70
Main Events During a Checkpoint ...........2-70
Initiating a Checkpoint ...............2-70
Fast Recovery...................2-71
Archive Checkpoints ................2-71
What Happens During a Checkpoint ............2-72
When the Daemons Flush the Buffer Pool ..........2-73
How OnLine Synchronizes Buffer Flushing..........2-74
Write Types Describe Flushing Activity ...........2-75
Sorted Write ...................2-76
Idle Write ....................2-76
Foreground Write .................2-77
LRU Write ....................2-77
Chunk Write ...................2-77
Big-Buffer Write ..................2-78
Writing Data to a Blobspace ...............2-78
Disk Data Structures ...................2-81
OnLine Disk Space Terms and Definitions ..........2-81
Chunk .....................2-82
Page ......................2-82
Blobpage ....................2-84
System Architecture 2-3
Dbspace and Blobspace............... 2-84
Database .................... 2-85
Tblspace .................... 2-85
Extent ..................... 2-85
Physical Log .................. 2-86
Logical Log ................... 2-86
Structure of the Root Dbspace .............. 2-87
Structure of a Regular Dbspace ............. 2-89
Structure of an Additional Dbspace Chunk ......... 2-90
Structure of a Blobspace ................ 2-91
Structure of a Blobspace or Dbspace Mirror Chunk ...... 2-92
OnLine Limits for Chunks ............... 2-93
Reserved Pages ................... 2-95
PAGE_PZERO .................. 2-97
PAGE_CONFIG ................. 2-97
PAGE_CKPT .................. 2-97
PAGE_DBSP .................. 2-99
PAGE_PCHUNK .................2-100
PAGE_MCHUNK.................2-101
PAGE_ARCH ..................2-102
Chunk Free-List Page.................2-103
tblspace Tblspace ..................2-104
tblspace Tblspace Entries ..............2-105
Tblspace Number.................2-105
tblspace Tblspace Size ...............2-106
tblspace Tblspace Bit-Map Page ............2-107
Database Tblspace ..................2-107
Create a Database: What Happens on Disk .........2-108
Allocate Disc Space ................2-109
Track Systems Catalogs ...............2-109
OnLine Limits for Databases ..............2-110
Create a Table: What Happens on Disk ...........2-110
Allocate Disc Space ................2-111
Add Entry to tblspace Tblspace ............2-111
Add Entry to System Catalog Tables ..........2-111
Create a Temporary Table: What Happens on Disk.......2-113
Placement ...................2-113
Tracking ....................2-113
Cleanup ....................2-114
2-4 IBM Informix OnLine Database Server Administrator’s Guide
Structure of an Extent .................2-114
Extent Size....................2-114
Page Types ...................2-115
Next Extent Allocation .................2-117
Structure of a Dbspace Page ...............2-120
Page Header ...................2-121
Timestamp ...................2-121
Slot Table ....................2-121
Data Row Format and Rowid ..............2-123
Data Pages and Data Row Storage .............2-125
Single-Page Storage ................2-126
Multipage Storage .................2-127
Storage of Modified Rows ..............2-129
Page Compression .................2-133
Structure of an Index Page ...............2-133
The Root Node Page ................2-134
Leaf Node Pages .................2-136
Index Key Entries .................2-138
Branch Node Pages.................2-141
Structure of a Dbspace Bit-Map Page ............2-143
2-Bit Bit-Mapped Pages ...............2-143
4-Bit Bit-Mapped Pages ...............2-144
Blob Storage and the Blob Descriptor ............2-145
Structure of a Dbspace Blob Page .............2-146
Blobspace Page Types .................2-148
Blobspace Free-Map Page ..............2-148
Blobspace Bit-Map Page ...............2-148
Blobpage ....................2-149
Structure of a Blobspace Blobpage .............2-149
Physical Log......................2-152
Logical Log Files ....................2-154
Fast Recovery and Data Restore..............2-154
File Rotation ....................2-155
File Contents ....................2-156
Number and Size...................2-156
Blobspace Logging ..................2-158
Long Transactions ..................2-159
System Architecture 2-5
2-6 IBM Informix OnLine Database Server Administrator’s Guide
Loading...