User Guide
1. Introduction
What Is Actian Analytics Database--Vector?
Vector Technology
Vectorised Processing--Calculating Query Answers Fast
Storage Innovations--Beating the Disk Bottleneck
Purpose of This Guide
Latest Version of Documentation
Path Notation in This Guide
Your Support Options
2. New Features in Version 5.1
New in This Release
3. Vector Concepts
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
SQL Access
VECTORWISE Storage Type
Storage Structures
Default Database Characteristics
Transaction Management
Vector Architecture
Command Summary
4. Understanding Usage Scenarios
Typical Use Scenarios
Reporting on DBT-3 Database Benchmark
How to Run the Benchmark
Generate Data
Start Vector
Create a Database
Create a Vector Table
Bulk Load the Data into the Table
Create Statistics
Run a Reporting Query
5. Vector and Ingres
Compatibility with Ingres Database
SQL Elements
Unsupported Features
Incompatibilities with Ingres Database
SQL Cursors
Some Subqueries Do Not Work
Scalar Subquery Cardinality Check
Cross-system Queries
SQL Data Type Incompatibilities
SQL Function Incompatibilities
Differences in Constraint Checking
6. Developing Applications
Application Programming Languages
Application Programming Interfaces
Connectivity Drivers
7. Recommended Platforms
RAID File System
Hardware versus Software RAID
Supported CPUs
Disk Subsystems
Guidelines for a Balanced Platform
8. Creating Databases
Database File Locations
Commands for Working with a Database
The Master Database (iidbdb)
The Management Database (imadb)
Rules for Naming Databases
Database Default Characteristics
How a Database Is Created
Create a User
Create a Database
How the Createdb Command Works
Show Information on Databases with infodb
Show Information on Databases with vwinfo
Remove a Database
9. Using Alternate Locations
Locations and Areas
Default Locations
Create an Alternate Location
Guidelines for Using Locations
Alter or Drop a Location
Work Locations
Initial and Extended Work Locations
Extend and Unextend a Database
Create a Database in an Alternate Location
10. Managing Tables and Views
Table Management
Tools for Creating a Table
Table Ownership
Table Location
Requirements for Using an Alternate Location for a Table
Create a Table in an Alternate Location
Using Multiple Locations
Enable or Disable Journaling
Duplicate Rows in Tables
Data Type Conversion Functions for Default Values
Constraints
Constraint Types
Delete Constraints
Views
Views and Permissions
Working with View Objects
Updates on Views
Types of Updates Not Permitted on Views
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
11. Creating Indexes
Creating Indexes
Create a Primary (Clustered) Index
Creating a Secondary Index
Single-attribute Secondary Index
Multi-Attribute Secondary Index
Secondary Indexes with Joins
12. Loading Data
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-Vector Connector
Load Data with Spark-Vector Loader
13. Updating Data
Methods for Updating Data
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
14. Using External Tables
Introduction to External Tables
How External Tables Work
External Table Requirements
Starting and Stopping the Spark-Vector Provider
Spark-Vector Provider Log
Configuring Logging
Spark-Vector Provider Configuration
Syntax for Defining an External Table
Reading and Writing to an External Table
How to Add Extra Packages
External Table Limitations
External Table Usage Notes
Troubleshooting External Tables
15. Using the Query Optimizer
Data and Query Optimization
Database Statistics
Methods for Generating Statistics
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
Histogram (Optimization Output)
Histogram Cells
Statistics and 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
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
16. Performing Backup and Recovery
Logging System
Logging Facility
Recovery Process
Archiver Process
Methods for Backing Up Data
Backup by Checkpoints
Full or Incremental Backup
Online and Offline Checkpoints
Limitations to Checkpoint Operation
Checkpoint (Back up) a Database with ckpdb
Checkpoint Template File
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
Journals
Tools for Performing Journaling
Enable Journaling on an Entire Database
Journaling and Online/Offline Checkpoints
Methods for Stopping Journaling
Disable Journaling When Checkpointing
Disable Journaling When Altering a Database
Database Characteristics Affected by Alterdb
Backup and Restore Scenario Using Checkpoints and Journals
Backup and Restore Using Copydb
Copy Database to a Backup Location Using movedb (Linux)
Copy Files to a Backup Location Using OS Commands
Recovery from Checkpoints
How the Roll Forward (Restore) Operation Works
Limitations to Rollforward Operation
Tools for Performing a Roll Forward
Recover a Database with rollforwarddb
Recover a Journaled Database
Recover a Non-Journaled Database
Recover a Database from Tape Checkpoints
Recover a Database from an Old Checkpoint
Recover from the Loss of the Transaction Log File
Backup and Recovery of the Master Database (iidbdb)
The iidbdb and Checkpointing
Checkpoint Template File Description
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 Linux
Archiver Exit Script (acpexit)
Customization of Archiver Exit Script
Archiver Exit Script Parameters
17. Monitoring
View Information about a Database
vwinfo Statistics Display
vwinfo Active Sessions Display
vwinfo PDT Usage Display
View Query Profile Information
iivwquery View
iivwprof View
Interpreting I/O Information in IIVWPROF View
iivwprof_last View
Examples of Using Query Profiling Information
Configure Query Profiling
System Catalogs for Vector Query Profiling Views
Query Tracing
SC930 Output Files
Set SC930 Output Directory
Enabling and Disabling SC930
SC930 Message Format
Example Trace Output
A. Command Reference
Where to Issue Commands
General Command Syntax
Dynamic Vnode Specification--Connect to Remote Node
Standard Flags and Parameters
Schema Qualifier--Specify Ownership
Delimited Identifiers on the Command Line
Delimited Identifiers Used on Authorization Parameters
Delimited Identifiers and Case Sensitivity
accessdb Command--Authorize User Access
alterdb Command--Set Database Characteristics
cbf Command--Start Configuration-By-Forms
ckpdb Command--Back Up a Database
ckpdb Examples
clonedb Command--Clone a Database
clonedb Example
copydb Command--Copy and Restore a Database
copydb Examples
createdb Command--Create a Database
createdb Examples
destroydb Command--Destroy a Database
destroydb Examples
extenddb Command--Extend Database to New Location
extenddb Examples
iiinitres Command--Install Parameter into CONFIG.DAT
iigetres Command--Get the Value of a Resource
iimonitor Command--Administer DBMS, Recovery, and GCF Servers
iimonitor Utility Commands
Help Command
Show Server Command
Show Sessions Command
Session ID Format in Command Output
Transaction Mode and Information in Command Output
Output of Show All Sessions Command
Format Sessions Command
Set Server Command
Stop Server Command
iimonitor Commands That Perform Actions on Sessions
dmtstatrcpstats Command
Quit Command
iimonitor Commands Specific to GCF Servers
Set Trace Command
Register Server Command
Remove Tickets Command
Remove Pooled Sessions Command
iinamu Command--Administer the Name Server
iinamu Example: Show All Registered Servers
iinamu Example: Show All DBMS Servers for the Server Class Ingres
iinamu Example: Show Communications Server Registrations
iinamu Example: Add a DBMS Server to the Name Server Registry
iinamu Example: Delete a DBMS Server from the Name Server Registry
iinamu Example: Stop the Name Server
iiodbcadmin Command--Run ODBC Administrator
iiodbcadmin Examples
iiremres Command--Remove Parameter from CONFIG.DAT
iisetres Command--Set Configuration Parameter
iisuitz Command--Install IANA Time Zone Files
iivalres Command--Validate Configuration Resource
infodb Command--Display Database Information
Infodb Command Output--Database Information Section
Infodb Command Output--Journal Information Section
Infodb Command Output--Dump Information Section
Infodb Command Output--Checkpoint History for Journal Section
Infodb Command Output--Checkpoint History for Dump Section
Infodb Command Output--Cluster Journal History Section
Infodb Command Output--Extent Directory Section
Infodb Command Output--Vectorwise Directory Section
ingprenv Command--Display Environment Variable Value
ingprenv Example
ingsetenv Command--Set Vector Environment Variable
ingsetenv Example
ingstart Command--Start a Vector Instance
ingstart Examples
ingstatus Command--Show Server Status
ingstop Command--Stop a Vector Instance
ingunset Command--Delete Environment Variable
ipm Command--Start the Interactive Performance Monitor
isql Command--Start Interactive SQL Terminal Monitor
isql Examples
movedb Command--Copy Data Locations to Another Machine
movedb Example
mkrc Command--Generate System Service Script
netutil Command--Start Net Management Utility
netutil Examples
optimizedb Command--Generate Statistics for the Query Optimizer
optimizedb -z Flags
optimizedb Examples
relocatedb Command--Move Location to Another Location
relocatedb Examples
rollforwarddb Command--Recover a Database
rollforwarddb Example
sql Command--Start the Line-based SQL Terminal Monitor
sql Examples
statdump Command--Print Statistics in iistats and iihistogram Catalogs
statdump Examples
sysmod Command--Modify System Catalogs
unextenddb Command--Unextend a Database Location
unloaddb Command--Create Files for Unloading and Reloading a Database
unloaddb Examples
upgradedb Command--Upgrade a Database
upgradefe Command--Install and Upgrade Tool Catalog Definitions
vwinfo Command--Display Information about a Database
vwload Command--Load Data into a Table
vwload Escape Sequences
vwload Date Format Settings
vwload Supported Character Sets
vwload in Parallel Mode
Requirements for Parallel vwload
Limitations of Parallel vwload
vwload from Remote System
vwload Examples
B. Setting Up Spark for Use with Vector
Software Downloads
Set Up Spark
How to Access S3 Cloud Storage
How to Securely Manage S3 Credentials
C. Vector to Spark Data Type Mapping
D. Limits
Limits
E. Features Introduced in Previous Versions
New Features in Version 5.0
New Features in Version 4.2
New Features in Version 3.5
New Features in Version 3.0 SP1
New Features in Version 3.0
User Guide
New Features in Version 3.0
This site works best with JavaScript enabled