System Administrator Guide
1. Introducing Ingres System Administration
Audience
System Administrator Responsibilities
System Administration Summary
Special Considerations
Query Language Used in this Guide
System-specific Text in this Guide
Path Notation in this Guide
Terminology Used in this Guide
Syntax Conventions Used in this Guide
2. Understanding Ingres Components
Components of an Ingres Installation
Ingres Architecture
DBMS Server
Query Processing
The Tool or Application Process
DBMS Server Process
Query Environment
DBMS Server Facilities
Abstract Data Type Facility
Compatibility Library Facility
Data Manipulation Facility
Optimizer Facility
Parser Facility
Query Execution Facility
Query Storage Facility
System Control Facility
Relation Description Facility
General Communication Facility
Name Server
How the Name Server Works
Name Server Maintenance Utility
Communications Server
Data Access Server
Bridge Server
General Communications Architecture
Querying and Reporting Tools
Visual Tools (Windows)
Visual Manager
Visual Performance Monitor
Visual DBA
Remote Command Server
Grant Access to Remote Users
How Remote Commands Are Executed
Configuration Manager
Network Utility
Export Assistant
Import Assistant
Journal Analyzer
Visual SQL
Visual Configuration Differences Analyzer
Visual Database Objects Differences Analyzer
Logging and Locking Systems
Lock Manager
Logging Facility
Log Buffers
How the Logging Facility Works
Recovery Process
Data Manipulation Facility Recovery Process (dmfrcp)
Recovery Modes
Archiver Process
Data Manipulation Facility Archiver Process (dmfacp)
Journal Files
Disaster Recovery Command
Transactions Recovery
Types of Transaction Recovery
Redo Operation in a Cluster Environment
Ingres Log Files
Transaction Log File
Error Log
Archiver Log
Recovery Log
Primary Configuration Log Files
Optional Configuration Log Files
Optional Log Files
Process Logs
Optional Log Facility
3. Configuring Ingres
Configurable Components
Configuration Tools
Access Configuration-By-Forms Utility
How You Navigate in CBF
Access Configuration Manager Utility
Access Visual Configuration Differences Analyzer Utility
Component Configurations
Configurable Properties for a Component
Configuration Snapshot
Configuration Parameters
Setting Parameter and Derived Parameter Values
Using VCDA to Restore Configuration Parameter
Protecting or Recalculating Derived Parameters
Components That Use Derived Parameters
DBMS Server Configuration
Fast Commit Option
Write Behind Option
Data Access Server Configuration
Communications Server Configuration
Bridge Server Configuration
Star Server Configuration
Name Server Configuration
Recovery Server Configuration
Security Configuration
Locking System Configuration
Logging System Configuration
Multiple Log Partition Configuration
Primary Transaction Log and Dual Transaction Log
Non-Configurable Components
History of Configuration Changes
4. Setting Environment Variables and Logicals
Scope of the Environment Variables
Symbol Table
Windows Environment Variables
UNIX Environment Variables
VMS Logicals
Installation-Wide Environment Variables
View Environment Variables in Windows and UNIX
View Environment Variables in VMS
Example: Set the ING_EDIT Variable
User-Definable Environment Variables and Logicals
Example: Set an Environment Variable in the Local Environment
Display Current Value for Variables
Environment Variables and Logicals that Can Be Reset Locally
Environment Variables and Logicals that Cannot Be Reset
Customization of Query Runtime Environment
Syntax Rules for Startup Files and Environment Variables
Dayfile
DBNAME_ING
Example: Set DBNAME_ING
DBNAME_SQL_ INIT
Example: Set DBNAME_SQL_INIT
II_SQL_INIT
Example: Set II_SQL_INIT
ING_SET
Example: Set ING_SET
ING_SET_DBNAME
Example: Set ING_SET_DBNAME
ING_SYSTEM_SET
Example: Set ING_SYSTEM_SET
INIT_INGRES
Example: Set INIT_INGRES
Startup
Startsql
Installation Locations
When Not to Change Installation Locations
Allocation of Space for Future Growth
Allocation of Additional Space for Existing Tables
Location Variables that Do and Do Not Require Unloading (UNIX and Windows)
Change the Value of the ING_ABFDIR Variable (UNIX and Windows)
Change the Value of the ING_ABFDIR Logical (VMS)
Change an Installation Location with Unloading (Windows)
Change an Installation Location with Unloading (UNIX)
Change Location Variables (Other than ING_ABFDIR) on VMS
Use of Symbolic Links (UNIX)
5. Customizing Ingres
Ingres Character Sets
Default Character Sets
Local Collation Sequences
Supported Collation Sequences
Multi Collation Sequence
Spanish Collation Sequence
Unicode_french Collation Sequence
Custom Collation Sequence
Description File—Describe Collation Sequence
Description File—Describe Collation Sequence
value
string
Archiver Exit Script (acpexit)
Customization of Archiver Exit Script
Archiver Exit Script Parameters
How to Use a Custom Mapping File for Unicode Coercion
6. Troubleshooting Ingres
Process of Troubleshooting
Troubleshooting Tools
Determine the Problem Area
Error Log Files
Optional Error Log Files
Display Value for II_CONFIG
View List of Log Files
Check the Error Log Files
Find Your Problem Category
Troubleshoot Startup, Shutdown, or Configuration Problems
Check Ingres Installation on Windows
Detect Ingres Startup Problems on Windows
Check Ingres Installation on UNIX
Ingbuild on UNIX
Detect Ingres Startup Problems on UNIX
Check Ingres Installation on VMS
VMSINSTAL on VMS
Detect Ingres Startup Problems on VMS
Detect Ingres Startup Problems on System Reboot (Windows)
Detect Ingres Startup Problems on System Reboot (UNIX)
Detect Ingres Startup Problems on System Reboot (VMS)
Check Shutdown Problems on Windows
Check Shutdown Problems on UNIX
Check Shutdown Problems on VMS
Ingres Processes on Windows
Check for Name Server Errors on Windows
Check for Communications Server Process Errors on Windows
Check for Bridge Server Errors on Windows
Check for Remote Command Process Errors on Windows
Check for Recovery Process Errors on Windows
Check for Archiver Process Errors on Windows
Check for DBMS Server Process Errors on Windows
Check for Data Access Server Errors on Windows
Ingres Processes on UNIX
Name Server Errors on UNIX
Check for Name Server Errors on UNIX
Check for Communications Server Process Errors on UNIX
Check for Bridge Server Process Errors on UNIX
Recovery Process Errors on UNIX
Check for Recovery Process Errors on UNIX
Check for Remote Command Process on UNIX
Archiver Process on UNIX
Check for Archiver Process Errors on UNIX
Check for DBMS Server Process Errors on UNIX
Check for Data Access Server Process on UNIX
Ingres Processes on VMS
Name Server Errors on VMS
Check for Name Server Errors on VMS
Communications Server Process on VMS
Check for Communications Server Process Errors on VMS
Bridge Server Errors on VMS
Check for Bridge Server Process Errors on VMS
Recovery Process Errors on VMS
Check for Recovery Process Errors on VMS
Remote Command Process Errors on VMS
Check for Remote Command Server on VMS
Archiver Process Errors on VMS
Check for Archiver Process Errors on VMS
Check for DBMS Server Process Errors on VMS
Data Access Server Errors on VMS
Check for Data Access Server Errors on VMS
Problems with Tools Startup
DBMS Server Stopped
Windows:
UNIX:
VMS:
Database Connection Problems
No Database Connections
Individual Database Connection Failure
Inconsistent Databases and Recovery
Automatic Recovery
Recovery During Normal Operation
Recovery at Shutdown
Recovery at Startup
Recovery Process Monitoring
Inconsistent Database
Diagnose an Inconsistent Database
Common Causes of Inconsistent Databases
Examine Configuration File of a Database
Recovery Rules
Recover an Inconsistent Database
Performance Problems
Flow Diagram for Troubleshooting Performance Problems
Diagnose DBMS Server Due to Logging System Problem
Logstat Status Fields
How to Avoid Logfull Abort
Resource and Maintenance Problems
Tools for Identifying Operating System Problems
Diagnose Poor Performance Due to Insufficient System Resources
UNIX:
VMS:
What You Need Before Contacting Technical Support
Windows Installations
UNIX Installations
VMS Installations
7. Using Monitoring and Tracing Tools
Supported Monitoring and Tracing Tools
System Utilities
Operating System Utilities
Windows Operating System Utilities
Windows Diagnostics
Windows Performance Monitor
Windows Event Viewer
Windows Registry Editor
Windows Task Manager
UNIX Operating System Utilities
ps
iostat
pstat (BSD)
sar (System V)
sysdef (System V)
vmstat
VMS Operating System Utilities
help
monitor
show
sysgen
Vendor Utilities
Error Messages
Error Message Format
xxxx
Message Help Files
Fatal Errors
Non-Fatal Errors
Facility Codes
Facility Codes for Primary Components
Server-Only Components
Tools-Only Components
Components for Stream File Management
Components for Utility Routines
Miscellaneous Components
Log Files
Transaction Log File
Error Log
Archiver Log
Recovery Log
Primary Configuration Log Files
Optional Configuration Log Files
Other Optional Log Files
Trace Utilities
ODBC Call-level Interface
SET Statement
Environment Variables Commonly Used with SET
Example: Set Statement Entered Directly
Example: Set Statements in an Include File
SET Statements in Startup Files
SET Options for Tracing Queries
The SET PRINTQRY Option
Example: Turn Query Display On Using ING_SET
Example: Turn Query Display On Using II_EMBED_SET
The SET QEP Option
The SET JOINOP NOTIMEOUT Option
Canceling SET Options
The SET SESSION WITH ON_ERROR Option
I/O Tracing
Lock Tracing
Event Tracing
User-Server Communications
Trace Communication Using printgca
Example: Level 2 Tracing Using GCA Trace Utiltiy
UNIX Trace Facilities
Bourne Shell -x Option
UNIX Trace Command
8. Managing Your System and Monitoring Performance
Managing the Installation with Visual Manager
Functions of Visual Manager
Visual Manager Window
System and User Parameter Configuration Through IVM
Set Parameter Configuration Through IVM
Message and Notification Management Through IVM
Event Monitoring Management Through IVM
Alert Events Notification Settings in IVM
View Message Explanations in IVM
Component Monitoring Through IVM
Server Monitoring Through IVM
Logging System Monitoring Through IVM
Locking System Monitoring Through IVM
Access to Visual Tools Through IVM
Monitoring and Managing Ingres with Visual Performance Monitor
Visual Performance Monitor Window
Comparing Configurations with Visual Configuration Differences Analyzer
Configuration Snapshot File
Comparison of Configuration Snapshots
Comparison of Remote Installation Snapshots
Restoration of Configuration Parameters Through VCDA
How VCDA Handles Concatenated config.dat Files
9. Analyzing and Recovering Journaled Transactions
Journal Analyzer
Start Journal Analyzer
Journal Analyzer Window
Transaction Views
Database Level Journaled Transactions
Group Changes by Transaction
Table Level Journaled Transactions
Select Transactions and Row Changes
Recover Transactions or Individual Row Changes
Recover “Whole” Transactions or Individual Rows
Rules, Constraints, and Integrities
Scan Journals to End Option
Order of Transactions and Statements in Transactions
How to Check If Rows Have Changed After the Transaction
Users of Transactions
Number of Rows Affected by Each Individual “Reverse Statement”
Recover Immediately or Generate External Scripts Option
Redo Transactions and Individual Row Changes
10. Understanding Ingres Management Architecture
IMA Audience
IMA Overview
Components of IMA
Management Information Base
MIB Object Model
Classid
Location
vnode
gca_address
Instances
MIB Creation
Registration of IMA Tables
REGISTER TABLE Statement—Register IMA Table
tablename
col_name
format
IMA Table Types
REMOVE TABLE Statement—Remove Table Registrations
tablename
IMA DBMSINFO Constants
Management Domains
Extending the Domain in the Local Instance
Extending the Domain to a Remote Instance
Removing a Remote Instance Domain
Restoring the Domain
Control Objects
Restrictions in IMA Use
REGISTER TABLE Examples
Examples: Domain Tables
Example: Table of SCF Sessions
Example: Lock Tables
Example: Logging Tables
Query Examples on IMA Tables
Example: What Are User Sessions Doing?
Example: Who Is Waiting for a Lock?
Example: Who Is Holding Locks?
Example: Who Is Holding a Lock that Other Sessions Need?
Example: When Did a Query Start?
Example: Shutting Down a DBMS Server
Class ID Objects
IMA_STATUS_LOOKUP
A. Environment Variables and Logicals
DBNAME_ING
DBNAME_SQL_INIT
DD_RSERVERS
II_4GL_DECIMAL
II_ABF_ASSUME_VALUE
II_AFD_TIMEOUT
II_APPLICATION_LANGUAGE
II_APP_NO_SIG_HANDLE (UNIX)
II_BIND_SVC_xx
II_C_COMPILER (VMS)
II_CHARSETxx
II_CHECKPOINT
II_CLIENT
II_COLLATION
collation_name
II_CONFIG
II_CONNECT_RETRIES
II_DATABASE
II_DATE_CENTURY_ BOUNDARY
II_DATE_FORMAT
II_DBMS_LOG
II_DBMS_SERVER
II_DECIMAL
II_DIRECT_IO (UNIX)
II_DISABLE_SYSCHECK
II_DML_DEF
II_DUMP
II_EMBED_SET
II_ERSEND (UNIX)
II_FRS_KEYFIND
II_GC_REMOTE
II_GCA_LOG
II_GCx_TRACE
II_GCD_LOG
II_GCN_LOG
II_GCNxx_PORT
II_HALF_DUPLEX
II_HELP_EDIT
II_INSTALLATION
II_JOURNAL
II_LANGUAGE
II_LOG_DEVICE (VMS)
II_LP32_ENABLED
II_MONEY_FORMAT
Logical Definition Result
II_MONEY_PREC
II_MSGDIR
II_NULL_STRING
II_NUMERIC_LITERAL
II_NUM_OF_PROCESSORS
II_NUM_SLAVES (UNIX)
II_OO_TABLE_SIZE
II_PATTERN_MATCH
II_PF_NODE
II_POST_4GLGEN
II_PRINTSCREEN _FILE
II_PRINT_UTF8ALIGN
II_SQL_INIT
II_STAR_LOG
II_SYSTEM
II_TEMPORARY
II_TERMCAP_FILE
II_TFDIR
II_THREAD_TYPE
II_TIMEZONE_NAME
II_TM_ON_ERROR
II_TUXEDO_LOC
II_TUX_SHARED
II_TUX_AS_MAX
II_TUX_XN_MAX
II_UNICODE_CONVERTER
II_UUID_MAC (Windows)
II_VNODE_PATH
II_WORK
II_XA_TRACE_FILE
IIDLDIR
ING_ABFDIR
ING_ABFOPT1
ING_EDIT
ING_PRINT
ING_SET
ING_SET_DBNAME
ING_SHELL (UNIX)
ING_SYSTEM_SET
INGRES_KEYS
INIT_INGRES
TERM (UNIX)
TERM_INGRES
Ingres 10S
TERM_INGRES