• User Guide
  • Introduction
    • What Is Actian Analytics Database--Analytics Engine?
    • Analytics Engine 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
  • Analytics Engine 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
    • X100 Storage Type
    • Storage Structures
    • Default Database Characteristics
    • Transaction Management
    • Analytics Engine Architecture
    • Command Summary
  • Understanding Usage Scenarios
    • Typical Use Scenarios
    • Reporting on DBT-3 Database Benchmark
    • How to Run the Benchmark
      • Generate Data
      • Start Analytics Engine
      • Create a Database
      • Create an Analytics Engine Table
      • Bulk Load the Data into the Table
      • Create Statistics
      • Run a Reporting Query
  • Analytics Engine 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
  • Developing Applications
    • Application Programming Languages
    • Application Programming Interfaces
    • Connectivity Drivers
  • Recommended Platforms
    • RAID File System
      • Hardware versus Software RAID
    • Supported CPUs
    • Disk Subsystems
    • Guidelines for a Balanced Platform
  • 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
  • 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
  • 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
  • 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
  • Vector Indexing
    • Vector Indexing in X100 Tables
    • Querying a Table using a Vector Index
    • Limitations
  • Loading Data
    • Methods for Loading Data
    • Load Data with COPY Statement
    • Load Data with vwload Utility
      • Load Data from Cloud Sources with vwload
    • Load Data with COPY VWLOAD Statement
    • Load Data with Spark SQL through the Spark Connector
      • Load Data with Spark Connector Loader
        • Using the Loader included in the service container
        • Examples of Loading Data with Spark Connector Loader
        • CSV File with Header
        • CSV File without Header
        • JSON File
        • Parquet File
        • ORC File
    • Loading Data using External Tables
  • 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
  • Using External Tables
    • Introduction to External Tables
    • How External Tables Work
    • External Table Requirements
    • Syntax for Defining an External Table
    • Reading and Writing to an External Table
    • External Table Limitations
    • External Table Usage Notes
    • Troubleshooting External Tables
    • Examples of Defining External Tables and Loading Data
      • CSV Files
        • Small Data Set
        • Large Data Set
      • JSON Files
      • JDBC Data Source
      • ORC Files
      • PARQUET Files
      • AVRO Files
      • Iceberg Tables
  • Using Transient Tables
    • Introduction to Transient Tables
    • Syntax for Defining a Transient Table
    • Transient Table Limitations
  • 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
  • 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
    • Cloud Checkpoint Locations
      • Creating Checkpoint Locations
        • Setting up a Cloud Checkpoint Location
    • Troubleshooting
      • Checkpoint Errors Troubleshooting
    • Archiver Exit Script (acpexit)
      • Customization of Archiver Exit Script
      • Archiver Exit Script Parameters
  • 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 Analytics Engine Query Profiling Views
    • Query Tracing
      • SC930 Output Files
      • Set SC930 Output Directory
      • Enabling and Disabling SC930
      • SC930 Message Format
      • Example Trace Output
  • Managing Workload
    • Workload Management Overview
      • Setting up workload management
    • Workload Queues
      • Queue Guidelines
      • System Queues
      • Queue Ratios
      • Queue Configurations
    • Workload Configuration
    • How to Configure Workload Management
      • iiwlm_config Catalog
      • iiwlm_queue Catalog
      • iiwlm_queueauth Catalog
    • DBA Workload Management Tasks
    • Workload Management Limitations
    • Monitoring the Workload Management System
      • IMA Tables for Workload Management
      • ima_wlm_queues Table
      • ima_wlm_queueauths Table
      • ima_wlm_sessions Table
    • Auditing Workload Management
      • iiwlm_log Table
  • 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
      • Create a Read-only 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
    • iisugetudflib Command--Download Predefined UDFs
    • iisuitz Command--Install IANA Time Zone Files
    • iisuonnx Command--Download ONNX Container Image
    • iisuremotefs Command--Set up and Configure Remote File Systems Support
    • iisuspark Command-- Download Spark Connector Container Image from Repository
    • iisutensorflow Command--Download Tensorflow
    • iisuudfc Command--Download UDF Container Image from Repository
    • iivalres Command--Validate Configuration Resource
    • import_model Command--Import Predefined Models
    • import_udflib Command--Import Predefined UDFs
    • 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 Analytics Engine Environment Variable
      • ingsetenv Example
    • ingstart Command--Start an Analytics Engine Instance
      • ‑ingstart Examples
    • ingstatus Command--Show Server Status
    • ingstop Command--Stop Actian Analytics Engine Instance
    • ingunset Command--Delete Environment Variable
    • ipm Command--Start the Interactive Performance Monitor
    • isql Command--Start Interactive SQL Terminal Monitor
      • isql Examples
    • lockstat/iilockstat Command--Display Locking Status
      • lockstat/iilockstat Command Output
      • lockstat/iilockstat Command Output – Locking System Summary
      • lockstat/iilockstat Command Output – Locking System Statistics
      • lockstat/iilockstat Command Output – Statistics by Lock Type
      • lockstat/iilockstat Command Output – Locks by Lock List
      • lockstat/iilockstat Command Output – Locks by Resource
      • lockstat/iilockstat Command Output – DLM Locks
    • logstat/iilogstat Command--Display Logging Status
      • logstat/iilogstat Command Output – Logging System Summary
      • logstat/iilogstat Command Output – Current Log File Header
      • logstat/iilogstat Command Output – List of Active Processes
      • logstat/iilogstat Command Output – List of Active Databases
      • logstat/iilogstat Command Output – List of Transactions
      • logstat/iilogstat Example: Determine Databases that Are Active
      • logstat/iilogstat Example: Determine Proximity to FORCE-ABORT-LIMIT
    • 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 Actian Spark Connector for Use with Analytics Engine
    • Set Up Spark Connector
    • Starting and Stopping the Spark Connector
    • Spark Connector Log
      • Accessing Spark UI on a Remote Host
      • Configuring Logging
    • How to Securely Manage S3 Credentials
  • C. Analytics Engine to Spark Data Type Mapping
  • D. Limits
    • Limits

User Guide

D. Limits