Database Administrator Guide
Introducing Database Administration
In This Guide
Audience
Database Administrator Responsibilities
Database Administration Summary
What You Need to Know
Query Language Used in this Guide
System-specific Text in this Guide
Terminology Used in this Guide
Syntax Conventions Used in this Guide
Hybrid Transaction and Analytics Processing
Actian X Hybrid—Ingres and X100
X100 Compatibility with Ingres
X100 SQL Elements
X100 Unsupported Features
X100 Incompatibilities with Ingres
SQL Cursors
Some Subqueries Do Not Work
Scalar Subquery Cardinality Check
SQL Data Type Incompatibilities
SQL Function Incompatibilities
Differences in Constraint Checking
X100 Concepts
X100 Table Structures
Data Storage Format
Raw Table Storage Format
Data Compression
Data Type Storage Format and Compression Type
Query Optimization
Data Manipulation
Granularity of DML Operations
Snapshot Isolation
Persistence and Recovery
Management of In-memory Updates
Transaction Isolation Model
Snapshot Isolation
Optimistic Concurrency Control
Transaction Examples
Non-dirty Read Example
Conflict Resolution Example
Transactions and DDL Operations
X100 Storage Types
Transliteration Between UTF8 and National Character Set
X100 Transaction Management
Creating Databases
Database File Locations
Commands for Working with a Database
The Master Database (iidbdb)
How Many Databases Can Be Created?
Createdb Privilege
Rules for Naming Databases
Database Default Characteristics
How a Database Is Created
Create a Database with Default Characteristics
Create a Unicode NFD Database
Create a non-Unicode Database
Create a Public Database
Create a Private Database
Change a Database from Private to Public
How the Createdb Command Works
Show Information on Databases with infodb
Show Information on Databases with vwinfo
View Database Information with accessdb
View Database Information with catalogdb
Drop a Database
Using Alternate Locations
Locations and Areas
Default Locations
Alternate Locations
Create an Area in Windows
Create an Area in Linux
Working with Locations
Guidelines for Using Locations
Create a New Location
Work Locations
Initial and Extended Work Locations
Classification of Extended Work Locations
Work Locations for a Session
Extend and Unextend a Database
Relocate Database Files
Managing Tables and Views
Table Management
Tools for Creating a Table
Table Ownership
Table Location
Requirements for Using an Alternate Location for a Table
Alternate Location for a Table
Enable or Disable Journaling
Duplicate Rows in Tables
Duplicate Rows When Adding New Rows or Modifying a Table
Duplicate Rows When Bulk Copying Rows in a Table
Duplicate Rows in Updated Tables
Remove Duplicate Rows
Page Size Specification
Storing BLOBS Inline
Data Type Conversion Functions for Default Values
Constraints
Constraint Types
Indexes for Constraints
Delete Constraints
Techniques for Changing Table Columns
Example: Rename a Column
Example: Insert a Column
Techniques for Moving a Table to a New Location
Relocate a Table
Reorganize a Table
Table Partitions
Partition Management
Assign an Expiration Date to a Table
Purge Expired Tables
Views
Views and Permissions
Working with View Objects
Updates on Views
Types of Updates Not Permitted on Views
Schemas
Tools for Managing Schemas
Synonyms, Temporary Tables, and Comments
Synonyms
Working with Synonym Objects
Temporary Tables
Temporary Table Declaration and the Optional SESSION Schema Qualifier
Examples of Working with Temporary Tables
Comments to Describe Tables and Views
The Comment On Statement
The Declare Table Statement
Populating Tables
Methods of Populating Tables
COPY Statement Syntax
COPY INTO (Unload Data) and COPY FROM (Load Data)
File Name Specification on the COPY Statement
WITH-Clause Options of the COPY Statement
COPY Statement Operation
Unformatted and Formatted Copying
Bulk and Incremental Copy
Copy Permissions and Integrities
Locking During a Copy
Unformatted Copying
Unformatted Copy into a Binary File
Reload a Table with Unformatted Copy
Formatted Copying
Column Name and Format Specifications
column_name
column_name
format
Summary of Data Types and Storage Formats
COPY Statement and Nulls
Copy Data into a Formatted File
Data with Fixed-Length Fields
Data with Variable-Length Fields
Reload Formatted Data
Bulk Copying
Bulk Copying Requirements
Transaction Logging During Bulk and Incremental Copy
Bulk and Incremental Copy Processing
Bulk Copy WITH Clauses
Example: Perform a Bulk Copy to Create a Hash Table
Example: Perform Bulk Copy and Create B-tree Table
Example: Perform Bulk Copy into a Heap Table
Fastload Operation
Requirements for Using Fastload
Perform a Fastload Operation
Data Loading in a Multi-CPU Environment
Advanced Use of the COPY Statement
Populate Multiple Database Tables Using Multiple FIles
Load a Table from Multiple Sources
Multi-line File Records
Load Fixed-Length and Binary Records
Considerations When Loading Large Objects
Considerations for Copying Formatted Large Objects
Considerations for Unformatted Copying a Large Object
Large Data Loads with the SET NOLOGGING Statement
Suspend Transaction Logging
Effects of the Set Nologging Statement
Before Using the Set Nologging Statement
Restore Transaction Logging
Example: Use a Set Nologging Application to Load a New Database
Example: Use a Set Nologging Application to Load an Existing Database
Successful Use of the COPY Statement
How to Check for Integrity Errors
Reloading Problems
Invalid Data in the Copy File
Miscounted Fixed-Length Field Widths in the Copy File
No nl Delimiter in the Copy File
Omitted Delimiters Between Fields in the Copy File
Too Many Delimiters in the Copy File
Error Handling with the COPY Statement
Stop or Continue the Copy
Stop the Copy After a Specified Number of Errors
Roll Back Rows
Log Errors During Copy
Continue the Copy and Log Errors
Troubleshooting Tips for Data Loading
Loading Data into X100 Tables
Methods for Loading Data
Load Data with COPY Statement
Load Data with vwload Utility
Load Data with COPY VWLOAD Statement
Load Data with Spark SQL through the Spark Connector
Load Data with Spark Loader
Updating Data in X100 Tables
Methods for Updating Data in X100 Tables
Batch and Bulk Updates
Transfer Data from Traditional Ingres Table
Batch Updates using Spark SQL
Combining Tables
How to Avoid Propagation
MODIFY...TO COMBINE Statement--Merge and Update Data
Propagate In-memory Updates to Disk
Best Practices for Updates
Allowing Concurrent Inserts
Loading and Unloading Databases
Unload and Copy Operations
Privilege Required for Unload Operation
Privilege Required for Copy Operation
Unload Operation
Objects That Are Unloaded
Ways to Perform the Unload Database Operation
Options on the Unload Database Operation
Files Created During the Unload Database Operation
Unload in ASCII or Binary Format
Floating Point Specification for Unload
Unload to Another Instance
Locking While Unloading a Database
Inconsistent Database During an Unload
Copy Operation
Ways to Perform the Copy Database Operation
Options on the Copy Database Operation
Objects that Are Copied
Scripts Produced by the Copy Database Operation
Reloading Order
Copy in ASCII or Binary Format
Floating Point Specification for Copy Database
Copy a Database to Another Instance
Locking While Copying a Database
Inconsistent Database During Copy Operation
Lock Database Exclusively When Copying
Copying Individual Database Objects
Command Scripts
Prepare to Copy a Database Object
How to Copy a Database Object
Copy Tables
Example: Move a Table to Another Database
Copy Forms
Example: Move Forms to Another Database
Copy Applications
Copy Reports
Example: Copy Reports to Another Database
Increase Object Limit on Commands
Portable Data Files (-nvarchar Flag)
Unload Data into a CSV File (X100 Tables Only)
Copying and Relocating a Database
Example: Copy a Database to a New Database
Example: Copy a Database to a New Database and Use New Locations
Example: Copy a Database to a New Database and Swap Contents of Locations
Generate XML and Import XML Operations
Changing Ownership of Databases and Database Objects
Database Ownership
Changing Ownership of a Database Object
Prepare to Change Ownership of a Database Object
How to Change Ownership of a Database Object
How to Change Ownership of Tables
Example: Change Ownership of Table
Changing Ownership of Applications
Example: Transfer Ownership of an Application to Another User
Changing Ownership of Forms
Example: Transfer Ownership of Forms to Another User
Changing Ownership of Reports
Example: Transfer Ownership of Reports to Another User
How to Change Ownership of a Database
Maintaining Databases
Viewing Database Objects
View Database Objects that Belong to Another User
List All Tables and Their Owners
Ways to Delete Database Objects
Routine Database Maintenance Tips
Operating System Maintenance Tips
Verifying Databases
Databases Shared Among Multiple Users
How File Names Are Assigned for Tables
Select File Names Associated with Tables
Retain Templates of Important Tables
Ensuring Data Integrity
Data Integrity Through Integrities, Rules, and Events
Integrities
Constraints Compared with Integrities
Differences in Error Handling Between Integrities and Constraints
Differences in Null Handling Between Integrities and Constraints
Working with Integrity Objects
How Integrities Are Used
Nulls and Integrities
The COPY Statement and Enforcing Integrities
Rules
Rules and Database Procedures
Working with Rule Objects
How Rules Are Used
Before and After Rules
Example: Use a Rule to Implement the Equivalent of an Integrity
Rules and Transactions
Enforcing Referential Integrity
Reject Technique for Enforcing Referential Integrity
Nullify Technique for Enforcing Referential Integrity
Cascade Technique for Enforcing Referential Integrity
Enforcing General Integrities
Enforcing General-Purpose Rules
Using a Rule to Apply External Resource Controls
Using a Rule to Extend the Permission System
Example: Use a General Purpose Rule to Track Changes to Personnel Numbers
The COPY Statement and Enforcing Rules
Disable Rules
Database Events
Working with Dbevent Objects
How Database Events Work
Raise an Event
Register to Receive an Event
Receive an Event
Example: Using Database Events with Rules
Remove an Event Registration
Choosing Storage Structures and Secondary Indexes
Storage Structure Terminology
Storage Structure and Performance
Types of Storage Structures
Default Storage Structure of New Tables
Heap Storage Structure
Structure of a Heap Table
Heap as Structure for Loading Data
When to Use Heap
Heap Troubleshooting
Hash Storage Structure
Structure of a Hash Table
Retrievals Supported by Hash
When to Use Hash
Hash Troubleshooting
ISAM Storage Structure
Structure of an ISAM Table
Retrievals Supported by ISAM
When to Use ISAM
ISAM Troubleshooting
B-tree Storage Structure
Structure of a B-tree Table
Associated Data Pages in a B-tree Table
Index Growth in a B-tree Table
Splitting in a B-tree Table
Locking and B-tree Tables
Sorted Order in a B-tree Table
Deleted Rows in a B-tree Table
When to Use B-tree
B-tree Troubleshooting
ISAM or B-tree?
When to Choose ISAM over B-tree
When to Choose B-tree over ISAM
Storage Structure Comparison Summary
Keys
Key Columns
Secondary Keys
Secondary Indexes
Working with Indexes
Implementation and Overhead of Secondary Indexes
R-tree Secondary Index
Secondary Indexes and Performance
Example: Load Retrieved Columns into a Secondary Index to Improve Performance
Forced Use of Secondary Indexes
Two Secondary Indexes
Tids
Creating Indexes for X100 Tables
Indexes in X100
Create a Primary (Clustered) Index
Creating a Secondary Index
Single-attribute Secondary Index
Multi-Attribute Secondary Index
Secondary Indexes with Joins
Maintaining Storage Structures
Storage Structures and Performance
Table Pages
Display the Number of Pages in a Table
Limitations of Heap Structure
Modify Procedures
Key Columns and Performance
Tools for Modifying Storage Structures
Cautions When Using the Modify Procedure
Options to the Modify Procedure
Number of Pages
Allocation of Space
Extension of Space
Guidelines for Choosing an Extend Size
Default Fill Factors
Alternate Fill Factors
Leaf Page Fill Factors
Index Page Fill Factors
Ensuring Key Values Are Unique
Table Compression
Page Size
Shrinking a B-tree Index
Before
After
Extending a Table or Index
Modifying Secondary Indexes
Persistence Option
Changing the Index Storage Structure
Remodifying B-tree Tables
Examples: Remodifying a Table to B-tree
Common Errors During the Modify Procedure
Overflow Management
Measure the Amount of Overflow
Repetitive Key Overflow
Poorly Distributed Overflow
Overflow and ISAM and Hash Tables
Example: Showing Overflow Distribution
B-tree Tables and Overflow
Notes:
Secondary Indexes and Overflow
Using the Query Optimizer
Data and Query Optimization
Database Statistics
Methods for Generating Statistics
(Beta Version) Automatic Histogram Generation
Assumptions of the Query Optimizer
Resources Required During Optimization
System Modification After Optimization
Run System Modification
Information Collected by the Optimizer
Types and Levels of Statistics Collected
Non-Sampled and Sampled Statistics
Full Statistics
Minmax Statistics
Key Column Statistics
Statistics from an Input Text File
Column Statistics
Create Statistics on Keys
Histogram (Optimization Output)
Histogram Cells
Statistics and Global Temporary Tables
How to Associate “Model” Histograms with Global Temporary Tables
When to Rerun Optimization
Example: Before and After Optimization
Query Execution Plans
Information on a QEP
View a QEP
Control QEP Generation Using a Environment Variable
Text-Only QEP
QEPs as Data Flow Trees
Modes for Showing Tree Diagrams
Concise QEP
Graphical QEP
Types of Nodes in a QEP
Sort Nodes in a QEP
Non-Join Nodes in a QEP
Orig Nodes
Projection-Restriction Nodes
Exchange Nodes
Join Nodes in a QEP
Cartesian Product Node
Full Sort Merge Node
Partial Sort Merge Node
Hash Join Node
Key and Tid Lookup Join Node
Subquery Join Node
Multiple Query Execution Plans
More Complex QEPs
Parallel Query Execution
Types of Parallelism
Enabling Parallel Query Plans
Sample Parallel QEPs
Optimizer Timeout
Control Optimizer Timeout
Greedy Optimization
Control Greedy Optimization
"Reuse" Heuristic
Summary for Evaluating QEPs
Specialized Statistics Processing
Display Optimizer Statistics
Display Optimizer Statistics for Individual Tables and Columns
Delete Optimizer Statistics
Floating Point Precision in Optimizer Statistics Display
Statistics in Text Files
Unload Optimizer Statistics to a Text File
Sample Text File Statistics
Load Optimizer Statistics from a Text File
Copy a Table and Associated Statistics
Sampled Optimizer Statistics
Create Sampled Statistics
Composite Histograms
Understanding the Locking System
Concurrency and Consistency
Locking System Configuration
Lock Types
Lock Modes
Lock Levels
How the Locking System Works
Lock Requests
Available Locks in the System (lock_limit)
Lock Grants
Lock Mode Compatibility
How the Locking Level is Determined
Initial Locking Level
Escalation of Locks
Methods for Changing How Locking is Handled
Summary of Default Locks
Releasing of Locks
Ways to Avoid Lock Delays
User-Controlled Locking--SET LOCKMODE
Ways to Specify a SET LOCKMODE Statement
Range of the SET LOCKMODE Statement
When to Change the Locking Level
Change the Locking Level with SET LOCKMODE
The MAXLOCKS Value
Change MAXLOCKS Value with SET LOCKMODE
TIMEOUT Value for a Lock Wait
Set a TIMEOUT Value for a Lock Wait
Guidelines for Timeout Handling
Example: Timeout Program
READLOCK Option
READLOCK=NOLOCK Option
READLOCK=EXCLUSIVE Option
Isolation Levels
Inconsistencies During Concurrent Transactions
Inconsistencies and Isolation Levels
Read Uncommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level
Deadlock
Deadlock Example
Deadlock in Single Query Transactions
Different Access Paths as a Source of Deadlock
Lock Escalation as a Source of Deadlock
Overflow Chains and Locking
Deadlock in Applications
Tools for Monitoring Locking
Performance Monitor
SET LOCK_TRACE Statement
LOCK_TRACE Output
LOCK_TRACE Example
Performance During Concurrency
Approaches for Handling Heavy Concurrent Usage
The Never Escalate Approach
The Table Lock Approach
Understanding Multiversion Concurrency Control
What Is Multiversion Concurrency Control?
How MVCC Works
What Data Does the User See?
Application Consistency
Table Y
Invoke MVCC
Disable MVCC
MVCC Restrictions
Table Lock Level Compatibility in an MVCC-capable Database
Lock Level MVCC and Isolation Levels
Lock Level MVCC and Readlock
MVCC Error Messages
Performing Backup and Recovery
The Need for Backup
Full or Partial Recovery
Logging System
Logging Facility
Log Space Reservation
Recovery Process
Archiver Process
Data Verification Before Backup
Methods of Verifying Data Accessibility
Static or Dynamic Backup
Backup by Checkpoints
Table-level Checkpoints
Checkpoint a Database
Checkpoint Selected Tables
Checkpoint and Roll Forward of Tables
The Checkpoint Template File
Online and Offline Checkpoints
Perform an Offline Checkpoint
Checkpoints and Locking
Management of Checkpoint, Journal, and Dump Files
Checkpoint File Version Numbers
Keep N Checkpoints
Maintaining Checkpoint Files Offline
Additional Commands for Managing Checkpoints
Checkpoints and Destroyed Databases
Parallel Checkpointing in Linux
Checkpoint to Disk
Checkpoint to Tape
Putting Checkpoints on Tape in Windows
Putting Checkpoints on Tape in Linux
How to Estimate Checkpoint File Size in Linux
Journals
Tools for Performing Journaling
Database or Table-level Journaling
Database Journaling
Table-level Journaling
Enable Journaling on an Entire Database
New Tables and Journaling
Journaling and Online/Offline Checkpoints
Disable Journaling
Stop Journaling on a Table
Methods for Stopping Journaling on All Tables
Disable Journaling When Checkpointing
Disable Journaling When Altering a Database
Database Characteristics Affected by Alterdb
Journal File Size
Target Journal Size
Journal Block Size
Initial Journal Size
Considerations When Resizing Journal Files
Considerations When Resizing Journal Files on Linux
Audit Trails with Journals
Tools for Auditing a Database
Understanding the Audit Operation
How to Load an Audit Trail as a Table
Backup by Copying
Back Up Tables with Copydb Command
Copy Database to a Backup Location Using movedb (Linux)
Backup by Unloading
Recovery
How the Roll Forward (Restore) Operation Works
Tools for Performing a Roll Forward Operation
Recover a Journaled Database
Apply Journals Incrementally to a Backup Database
Recover a Non-Journaled Database
Recover a Database from Tape Checkpoints
Parallel Roll Forward from Disk (Linux)
Parallel Roll Forward from Tape (Linux)
Table Recovery Using Roll Forward
Retract Changes Using Roll Forward
Recover a Subset of Data Using Roll Forward
Recover a Database from an Old Checkpoint
Recover from the Loss of the Transaction Log File
Recovery Server Offline Error Handling
Recovery Server Online Error Handling
Checkpoint Template File
Checkpoint Template Codes
Examples: Checkpoint Template Code
Substitution Parameters in the Checkpoint Template File
Valid Code Combinations in the Checkpoint Template File
Format of the Checkpoint Template File in Windows
Format of the Checkpoint Template File in Linux
Alternate Checkpoint Template Files (Linux)
Backup and Recovery of the Master Database (iidbdb)
The iidbdb and Checkpointing
Set Log_Trace Statement--Trace Log Writes
Calculating Disk Space
Space Requirements for Tables
Calculate Space Requirements for Heap Tables
Calculate Space Requirements for Hash Tables
Calculate Space Requirements for ISAM Tables
Calculate Space Requirements for B-tree Tables
Calculate Space Requirements When Rows Span Pages
Maximum Row Size Per Page Size
Space Requirements for Compressed Tables
Tracking of Used and Free Pages
Calculation of Allocated Table Size
Space Requirements for Journal Files
Space Requirements for Modify Operations
Factors Affecting Space Requirements for Modify Operations
Summary of Space Requirements for Modify Operations
Space Requirements for Sorts
Insufficient Sort Space
Orphaned Sort Files
Factors Affecting Sort Performance
Improving Database and Query Performance
Locking and Concurrency Issues
Lock Waits and Performance
Multi-query Transactions and Performance
Overflow and Performance
Storage Structure and Overflow
Set Statements and Locking Strategy
Database Maintenance Issues
Optimization and Performance
Table and Index Modification and Performance
System Modification and Performance
Verification and Performance
Design Issues and Performance
Hierarchy for Diagnosing Design-based Performance Problems
Storage Structures and Index Design and Performance
Key Design and Performance
Characteristics of Good Keys
Characteristics of Bad Keys
Multi-Column Keys and Performance
Surrogate Keys and Performance
Query Design and Performance
Performance Tips for X100 Queries
Data Size Consideration for Some X100 Queries
Information Needed By Customer Support
Isolate and Analyze the Problem Query
Create a Test Case
A. System Catalogs
In this Appendix
Standard Catalog Interface
Example of HELP VIEW and HELP Statements
Standard Catalogs for All Databases
iiaccess Catalog
iialt_columns Catalog
iiaudittables Catalog
iicolumns Catalog
iiconstraint_indexes Catalog
iiconstraints Catalog
iidb_comments Catalog
iidb_subcomments Catalog
iidbcapabilities Catalog
iidbconstants Catalog
iidistcols Catalog
iidistschemes Catalog
iievents Catalog
iifile_info Catalog
iihistograms Catalog
iiindex_columns Catalog
iiindexes Catalog
iiingres_tables Catalog
iiintegrities Catalog
iikeys Catalog
iikey_columns Catalog
iilog_help Catalog
iilpartitions Catalog
iimulti_locations Catalog
iipermits Catalog
iiphysical_tables Catalog
iiprocedures Catalog
iiproc_access Catalog
iiproc_params Catalog
iiproc_rescols Catalog
iirange Catalog
iiref_constraints Catalog
iiregistrations Catalog
iirules Catalog
iisecurity_alarms Catalog
iisession_privileges Catalog
iisequences Catalog
iistats Catalog
iisynonyms Catalog
iitables Catalog
iiviews Catalog
Standard Catalogs for iidbdb
iiaudit Catalog
iidatabase_info Catalog
iidbprivileges Catalog
iiextend_info Catalog
iilocation_info Catalog
iiprofiles Catalog
iirolegrants Catalog
iiroles Catalog
iisecurity_state Catalog
iiusers Catalog
Mandatory and Actian X-Only Standard Catalogs
Mandatory Catalogs With Entries Required
Mandatory Catalogs Without Entries Required
Actian X-Only Catalogs
Extended System Catalogs
Organization of Extended System Catalogs
Data Dictionary Catalogs
Object IDs in Extended System Catalogs
Copying the Extended System Catalogs
Catalogs Shared by All Actian X Tools
ii_encodings Catalog
ii_id Catalog
ii_locks Catalog
ii_longremarks Catalog
ii_objects Catalog
Sample Queries for the Extended System Catalogs for SQL
Example: Find Information on Every Report in the Database
Example: Find the Name and Tabbing Sequence Number of Fields on a Form
Example: Find Information on Every ABF Application
Example: Find Information on All Frames and Procedures in an Application
Example: Select Object Information
Forms System Catalogs
ii_encoded_forms Catalog
ii_fields Catalog
ii_forms Catalog
ii_trim Catalog
ABF System Catalogs
ii_abfclasses Catalog
ii_abfdependencies Catalog
ii_abfobjects Catalog
ii_sequence_values Catalog
QBF System Catalogs
ii_joindefs Catalog
ii_qbfnames Catalog
Report-Writer System Catalogs
ii_rcommands Catalog
ii_reports Catalog
Vision System Catalogs
ii_framevars Catalog
ii_menuargs Catalog
ii_vqjoins Catalog
ii_vqtabcols Catalog
ii_vqtables Catalog
Additional Vision Catalog Information
Geospatial System Catalogs
DBMS System Catalogs
System Catalogs for All Databases
System Catalogs for iidbdb
X100 Query Profiling System Catalogs
iivwprof_query Catalog
iivwprof_op Catalog
iivwprof_io Catalog
iivwprof_stage Catalog
iivwprof_expr Catalog
iivwprof_parse_tree Catalog
Miscellaneous System Catalogs
B. Limits
Summary of Limits
Glossary
key
keyed storage structure
QEP
rule
secondary index
storage structure
Database Administrator Guide
storage structure