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-availability 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 sportinggoods 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 development 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 subdiagram 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
Loading...