• 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
    • • Installation Guide
    • • Release Summary
  •  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 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
•  D. 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
•  E. 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)
•  F. 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
•  G. 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
•  H. 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
•  I. 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 10S

OpenAPI Enhancements