Upgrade Guide
1. Planning the Upgrade
Purpose of This Guide
In This Chapter
Use of New Features
The Upgrade Plan
Upgrade Types
Upgradedb Method
Unload/Reload Method
Upgrade Method and Ingres Releases
From Releases Prior to Ingres 6.4
From Ingres 6.4
From Releases Newer than Ingres 6.4
From a 32-bit to a 64-bit Release
To Member-Aligned Alpha OpenVMS (axm.vms)
Release-specific Upgrade Issues
Required Installations for Upgrading
Possible Hardware Setups for Upgrading
How You Perform the Upgrade
How You Plan for Application Issues
The Test Plan for Applications
Categories of Application Functions
Application Debugging After the Upgrade
Binary Level Support
2. Creating a New Ingres Development Environment
Purpose of this Chapter
Platform-specific Examples in This Guide
How You Move an Existing Development Installation into the New Development Installation
Create the New Development Installation
How You Prepare Your Applications
Reserved Keyword Conflicts
Re-image ABF Applications
Report-Writer Syntax Change When Upgrading from Ingres 6.4
Report-Writer Runtime Parameter Errors (UNIX)
Use of the ANSIDATE Data Type
Migration to Unicode Applications
How You Load Databases and Applications into the New Installation
Create Users, Profiles, Groups, and Roles
Move Databases
Move Ingres Star Databases
The system_maintained Column Name
Compile Applications
How You Prepare for Development Installation Upgrade
Installation Back Up
Ingres Monitoring Tools and Scripts
Third Party Products Used
Checkpoint Template Changes
Checkpoint and Rollforward Changes
Shared Library Search Path (UNIX)
UNIX Kernel Parameters
Testing Applications and Procedures
Application Testing
Performance Testing
System Administrator Procedure Testing
How You Practice the Upgrade
3. Upgrading Using Upgradedb
In This Chapter
Ownership Assumptions for Running Upgradedb
How You Upgrade Using the Upgradedb Utility
Disable User Access
Disable Remote Command Server
Shut Down Ingres and Back Up System
Clean the Database
Record Database Information
Checkpoint and Turn Off Journaling
Shut Down Ingres
Preserve Site Modifications
Commonly Customized Files
Preserve Necessary Files
Visual DBA Configurations
(Optional) Delete Install Directory and Move Bin, Lib, Utility Directories (UNIX)
Install Ingres
How You Upgrade to Older Versions That Require a Patch
Create imadb Database
Restore Site Modifications
Carry Forward Checkpoint Template Modifications
Start Ingres
Run Upgradedb Utility
Review Ingres Configuration
(Optional) Reapply Optimizer Statistics
Checkpoint the Database
Install Upgraded Applications
4. Upgrading Using Unload/Reload
In This Chapter
Why Use Unload/Reload?
Variations of Unload/Reload Procedure
How You Perform an Upgrade Using Unload/Reload
Create Unload Directory
Run Unloaddb
Check for Obsolete Users
(Optional) Checkpoint the Database
Disable User Access
Disable Remote Command Server
Shut Down Ingres and Back Up System
Unload the Database
(Optional) Print Optimizer Statistics
Record Database Information
Record Database Privileges
Save Users, Groups, and Roles
Destroy the Database
Clean iidbdb Database
Disable Ingres Startup
Preserve Site Modifications
Visual DBA Configurations
(Optional) Delete Install Directory and Move Bin, Lib, Utility Directories (UNIX)
Install Ingres
How You Upgrade to Older Versions That Require a Patch
Create imadb Database
Restore Site Modifications
Review Ingres Configuration
Set Up Ingres Net
Start Ingres
Recreate Users, Groups, and Roles
Recreate Locations
Recreate the Database
Extend the Database
Recreate Database Privileges
Fix FE Reload Script
Reload the Database
Upgrade Front-End Catalogs
Reapply Optimizer Statistics
Checkpoint the Database
Install Upgraded Applications
5. Troubleshooting Upgradedb
How to Avoid Upgradedb Problems
Typical Upgradedb Problems
Other Upgradedb Problems
6. Considerations for OpenVMS
In This Chapter
OpenVMS Requirements
Considerations When Installing Ingres on OpenVMS
Mount the CD
Run VMSINSTAL
Known Installation Issues
How to Install Ingres for OpenVMS Clusters
Upgrading to Ingres 10S on OpenVMS
Schema Checking
Application Rebuilding
Building Member_Aligned Against Ingres 2.6 or 2006
Modifications Required For C Applications
Modifications Required For COBOL Applications
A. Upgrading from Ingres 6.4
How To Use This Appendix
Considerations for Ingres 6.4
Application Preparation
UPDATE . . . FROM Semantics Change
Decimal Constant Semantics Change
Greater Sensitivity to BYREF Errors
Journaling On by Default
Greater Sensitivity to Arithmetic Errors
4GL TABLE_KEY Type Conversions
User-Defined Data Type Changes
Application Preparation Summary
System Preparation
Ingres Startup and Shutdown
ingprenv Replaces ingprenv1
Archiver Exit Shellscript
Transaction Log Size
Unload/Reload Procedure for Upgrading from 6.4
Unload/Reload Upgrade Types
Front-end Catalogs and the Upgradefe Program
How You Upgrade from Ingres 6.4 Using Unload/Reload
Check for Obsolete Users
Record Database Privileges
Save Users, Groups, and Roles
Clean iidbdb Database
Record Ingres Configuration
Shut Down Ingres
Fix Logins
Save Ingres Settings
Clean Up Ingres 6.4
Create Work Location
Restore Site Modifications
Configure Ingres
Recreate Users, Groups, and Roles
Recreate Database Privileges
Fix FE Reload Script
Alternate Upgradedb Procedure
How You Upgrade from Ingres 6.4 Using Upgradedb (Alternate)
Create Unload Directory
Run Unloaddb
Edit the Unloaddb Output
Remove Non-table Objects
Checkpoint and Turn Off Journaling
Save Ingres Settings
Recreate Objects
Reapply Storage Structures
Corresponding Parameter Names
Parameters in 6.4 rundbms.opt File
Notes on Specific DBMS Server Parameters
Locking and Logging System Parameters
Notes on Specific Logging and Locking Parameters
B. Keywords
Keywords in This Appendix
Table Key
Reserved Single Keywords
Reserved Double Keywords
Other Reserved Keywords
C. Features Introduced in Ingres 10S
Actian Director
DBMS Server Enhancements
Geospatial Data Types and Functions
Common Table Expressions
Mulitiple-row INSERT
SQL Functions
Command Enhancements
Terminal Monitor Commands
New Location for Logs (Windows)
Log File Names Can Include Process ID and Date
II_DATE_FORMAT Settings ISO4T and ISO4TC
UCS_BASIC Collation for Columns
SET SESSION COLLATION
Default Configuration for Transactional System
Connectivity Enhancements
ODBC Batch Queries
JDBC getGeneratedKeys Enhancement
Ingres .NET Data Provider Enhancements
Supportability Enhancements
Unicode-enabled Database by Default
Portable UTF-8-encoded Copydb and Unloaddb
Date Format with Space Delimiter
DROP TABLE IF EXISTS
SET HASHAGG and SET HASHJOIN
Logging System Statistics Enhancement
Identification of Long Running Queries
Miscellaneous Enhancements
D. Features Introduced in Ingres 10.0
DBMS Server Enhancements
Ingres for 64-bit Windows
Multiversion Concurrency Control (MVCC)
Scalar Subqueries
Data at Rest Encryption
Long Identifiers
Renaming Tables and Columns
Bulk Loading Improvements
Batch Query Execution
BOOLEAN Data Type
New SQL Functions
Terminal Monitor Silent Mode
Daitch-Mokotoff Soundex Function
Check Digit Functions
Recovery Server Error Handling
Hash Join and Hash Aggregation Improvements
Query Execution Improvements Related to Partitioned Tables
Direct I/O Improvements (UNIX)
File Allocation Improvements (UNIX)
Standard Compression Performance Improvements
Miscellaneous Changes
Connectivity Enhancements
JDBC 4.0 Support
64-bit ODBC Driver
ODBC Driver Names
IngresType.IngresDate Parameter Type in .NET Data Provider
Named Parameters in Parameterized Queries in .NET Data Provider
Positional Parameter Support in Drivers
TCP/IP for Local Communications on Windows
Performance Improvements to Communications Protocol Drivers on Windows
Supportability Enhancements
Process ID (PID) for GCF Servers Displayed in iimonitor
OpenAPI Writing of DBMS Trace Messages to File
Usability Enhancements
Improved Visual Tools Dialogs
Terminal Monitor Usability Enhancements
E. Features Introduced in Ingres 9.3
DBMS Server Enhancements
Large Object Pattern Matching
Table Procedures
Identity Columns
Unordered Sequences
64-bit Integer Sequences
Connectivity Enhancements
Support for Multiple Data Access Servers
Deprecation of wintcp Network Protocol Driver (Windows)
Scrollable Cursor Support in ODBC
EnlistTransaction and TransactionScope Support in Ingres .NET Data Provider
Supportability Enhancements
Pluggable Authentication Module (PAM) Support (Linux and UNIX)
Ingres JDBC Driver Properties Generator
Line Numbers for All Error Messages
F. Features Introduced in Ingres 9.2
DBMS Server Enhancements
Scrollable Cursors
LOB Locators
UTF8 Character Set
Improved Out of the Box Defaults
Automatic Storage Structure for New Tables
Additional SQL Functions
Incremental Rollforwarddb
Improved Exception Handling
Increased Precision for Decimal Data Type
Improved Performance of String Comparisons
Unicode Uppercase and Lowercase
Fetch First n and Offset n
Cached Dynamic Cursor Query Plans
Changes to ANSI Date Feature
ii_ipaddr Function Supports IPv6 Addresses
Supportability Enhancements
Logging of Verifydb, Ckpdb, and Rollforwarddb
Ability to View Cursor Definition Text for an Executing Fetch
Server Type Reported for Terminated Programs
Connectivity Enhancements
LOB Locator Support in JDBC and OpenAPI
Scrollable Cursors in JDBC and OpenAPI
Connection Pooling in ODBC CLI (UNIX and VMS)
.NET Data Provider Enhancements
Performance Improvements in Network Communications (UNIX and Windows)
G. Features Introduced in Ingres 9.1 (Ingres 2006 Release 2)
New Features in the DBMS Server
Derived Tables
ANSI Date and Time Support
BEFORE Triggers
SQL Language Enhancement--Describe Input Statement
Indexes on Temporary Tables
Syntax for Referencing Temporary Tables
Sequence Defaults
Automatic Coercion Between Integers and Strings
Date Functions
Ease of Use Enhancements
Name Server Registration Management
Server Management for GCF Servers
Connectivity Enhancements
ODBC Enhancements
JDBC Enhancements
Ingres .NET Data Provider 2.0
PHP 5 Support
Support for IPv6 Networks
Supportability Enhancement
Usability Enhancements
Ingres High Availability Option on Linux
Removed or Deprecated Features
JDBC Server Removed
Ingres ICE Deprecated
H. Features Introduced in Ingres 9.0 (Ingres 2006)
What Is Ingres 2006?
Features Included in Open Source
Features Not Included in Open Source
New Features for Database Administrators
Parallel Query
Key Range Table Partitioning
Online Modify
Ingres High Availability Option
Unextenddb Utility
Killing Queries
Numeric Overflow Support in Report-Writer
Collation Specification at the Column Level
System-wide Setting for Default Lock Level
New Features for Application Developers
Automatic Sequence Number Generation
No Wait for Lock Requests
Support for New Data Types
Additions to the Visual DBA Suite
Visual Database Objects Differences Analyzer
Visual Configuration Differences Analyzer
Export Assistant
Connectivity Enhancements
Support for JDBC 3.0 API
Data Access Server
JDBC Driver
JDBC Information Utility
Updatable Result Sets in JDBC
.NET Data Provider and Visual Studio .NET Integration
Ingres .NET Data Provider
Visual Studio .NET Integration
Ingres ODBC Administrator
WinSock 2.2 API TCP/IP Protocol Driver for Windows
ODBC Call-level Interface
New Features for Linux
RPM Packaging
KDE/GNOME Desktop Integration
Changes to Existing Features
Enhanced Unicode Support
Unicode Coercion
Collation Sensitive Support for Wildcard Searching
Complex Query Optimization
Increased Range Table Limit
JDBC User ID Enhancements
Increased Column Limit
VDBA Enhancements
VDBA Architecture Split
Visual SQL
Visual Performance Monitor
Additional Properties
Notification of Metadata Changes
Ingres Visual Manager Enhancements
Direct Access to All Ingres Tools
Registering Alerts in the OS Event Log
Viewing Message Explanations
Configuration Rules System Enhancements
Shadow Copy of the Symbol Table
Additional Join Functionality
Improved Out-of-the-Box Configuration Defaults
Improved IMA Support
CREATEDB Enhancements
ALTERDB Enhancements
Terminal Monitor Enhancements
Enhancements for Log Full
Extended B-tree Limits
Installer Enhancements
Installation as a User Other Than ingres
Supportability Enhancements
Help System Enhancements
I. Features Introduced in Ingres 2.6
User-Visible Language Enhancements
Row Producing Procedures
SUBSTRING Function
New Aggregate Functions
Increased Maximum Size of Character Data Types
User-Visible DBA Enhancements
Usermod Utility
Auditdb Utility
Copydb Utility
Raw Location Support
GatherWrite Threads
XML Import/Export Utility
Journal Analyzer
Import Assistant
Automated Creation of Location Directories
Remote Command Server Enhancements
Microsoft Transaction Server Support
Concurrent Rollback
Internal Performance Enhancements
Aggregate Sort Nodes
Composite Histograms
Optimizer Support for Hash Joins
Locking System Performance Improvements
Preallocated RSB/LKBs
Miscellaneous Locking System Improvements
Logging System Performance Improvements
Buffer Manager Performance Improvements
Operating System Integration
64-Bit Operating Systems
Operating System Thread Implementation on Linux
Ingres ICE Enhancements
ICE Development Environment
ODBC Enhancements
Functions Supported by ODBC Driver
Unavailable Features in the ODBC Driver
JDBC Enhancements
Support for Unicode
New Character Sets to Support Euro Currency Symbol
J. Features Introduced in Ingres II 2.5
Sort Enhancements
QEF Sort Enhancements
DMF Sort Enhancements
Parallel Sort Techniques
ANSI/ISO Constraint Enhancements
Large Cache Support
Dynamic Write Behind Threads
Partitioned Transaction Log File
Optimizer and Optimizedb Enhancements
Read-only Database Support
Example: Create a Read-only Database
New SQL Functionality
Order By/Group By Expression
CASE Expression
Parallel Index Creation
SELECT Enhancement
Bitwise Operator Support
Aggregate Functions
Miscellaneous Functions
Extended Date Support
Large File Support
Large Catalogs
Row Locking for System Catalogs
Update Mode Locking
Value Locking for Serializable Transaction with Equal Predicate
Query Optimization and Execution Enhancements
Ingres Star Features
Ingres Net Features
Ingres ICE Features
Ingres ICE Security Enhancements
Ingres ICE Session Management Enhancements
Storage Management
Macro Language Extensions
Visual DBA Features
Replicator Enhancements
Generic Replicator Server
Increased Replicator Concurrency
OpenAPI Enhancements
Ingres 10.2
OpenAPI Enhancements