NuoDB Docs
NuoDB.com Dev Center
NuoDB.com Dev Center

NuoDB Docs

    • Introduction to NuoDB
      • Docker
      • Kubernetes
        • .deb and .rpm Package Install
        • .tar.gz Tar File Install
      • New Features
      • Changes in this Release
      • Resolved Issues
      • Preview Features in this release
      • Known Issues
      • Deprecated Features
      • End Of Life Features
        • NuoDB COMMUNITY EDITION SOFTWARE LICENSE AGREEMENT
          • Appendix A
        • NuoDB PROFESSIONAL SERVICES ADDENDUM
      • Trademarks
      • Third-Party Acknowledgments
        • System Requirements
        • NuoDB At a Glance
          • Obtaining and Installing an Enterprise Edition License
            • Using the RedHat Package Manager to Install or Upgrade NuoDB
            • Using the Debian Package Utility to Install NuoDB
            • Using a Linux TAR File to Install NuoDB
            • Resolving Warnings About Previously Used NuoDB IDs
            • Post-Installation Tasks on Linux
            • Disabling Transparent Huge Pages
            • Using an EXE File to Install NuoDB on Windows
            • Using a ZIP File to Install NuoDB on Windows
            • Preparing to Upgrade
              • Upgrading With Databases Down
              • Rolling Upgrade: Databases Remain Available
            • Upgrading the Database Protocol
            • After You Upgrade
          • Downgrading to a Previous Release
            • Uninstalling NuoDB on Linux
            • Uninstalling NuoDB on Windows
          • Configuring NuoDB Admin
            • Generating Keystores and Truststores: Shared Admin Key
            • Generating Keystores and Truststores: Unique Admin Key
            • Using Certificates Signed by a Public Certificate Authority
            • Rotating Key Pair Certificates
            • Managing TLS Security
          • Creating the Domain and Adding Hosts
          • Starting and Stopping NuoDB Admin
            • Displaying Domain, Database, and Archive Status
            • Displaying Durable Domain Configuration Information
            • Displaying Database Process Information
            • Displaying Database Engine Attributes
          • Performing Health Checks on Domain and Database Status
          • Resetting the NuoDB Domain State
            • Determining If Admin Process Quorum Exists
            • Handling Unreachable Processes
            • Removing an Unreachable Admin Server from the Durable Domain
            • Re-establishing Admin Process Quorum
            • Creating an Archive
            • Creating a Database
            • Validating Database Archives
          • Confirming Domain and Database Status
          • Shutting down a Database
          • Restarting a Database
          • Starting a Storage Manager
          • Starting a Transaction Engine
          • Extending the Database Across Multiple Hosts (Scaling Out)
          • Adding and Shutting Down a Redundant Storage Manager
          • Restoring a Database Using an Archive
          • Deprovisioning Hosts in a Multi-Host Configuration (Scaling In)
            • General Steps for Backing Up Databases
                • Copying the Archive and Journal of the Shutdown Storage Manager
              • About the Results of a Successful Hot Copy
              • Using Backup Sets
              • Using Full Hot Copy
              • Using Incremental Hot Copy
              • Using Journal Hot Copy
              • Command Format for Invoking Hot Copy
              • Restoring Data From Backup Sets
              • Example of Hot Copy Running
              • Example of Hot Copy Log Message
              • Examples of Hotcopy Errors
              • Example of Hotcopy Success
          • Deleting Databases
          • Database Management - A Working Example
          • Connection Properties
          • Default Ports (NuoDB Admin)
          • REST API Reference (NuoDB Admin)
        • System Requirements
        • Installing NuoDB
        • Enabling TLS in Containerized Environments
        • Automatic Management of NuoDB State
        • Scaling Down the Admin Layer without Kubernetes-Aware Admin
      • Durable Domain Configuration
      • Domain Credentials
      • Admin Process
      • Admin Process Quorum
      • Maintaining Admin Process Quorum
      • User Authentication and Authorization
        • Configuring Transparent Data Encryption
        • Transparent Data Encryption Error Codes
        • About NuoDB DBAs
        • Connecting to a database Using TLS and LDAP
        • User Authentication Using LDAP
        • About Users and Roles
        • About Schema Access Levels
        • Assigning Database Access Levels
        • Examples of Assigning Database Access Levels
        • Managing Database Schemas
        • Managing Database Tables
        • Managing Other Database Objects
        • About Commit Protocols
        • About Commit Protocols and Recoverability
        • About the Safe Commit Protocol
      • About Database Journaling
        • Introduction to Table Partitions and Storage Groups
        • Use Cases for Table Partitions and Storage Groups
        • Storage Group Rules
        • Table Partition Rules
          • Rules for Adding and Removing Storage Groups
          • Adding a Storage Group to an Archive
          • Removing a Storage Group from an Archive
          • Deleting a Storage Group from a Database
        • Managing Table Partitions
        • Example of Using Storage Groups and Table Partitions
      • Restoring a Database Using an Archive
        • Asynchronous Commit Concepts
        • Asynchronous Commit Pros and Cons
        • Asynchronous Commit Setup
        • Normal Operation of Asynchronous Commit
        • Disaster Recovery Using Asynchronous Commit
        • Asynchronous Commit Example
        • Using the Handoff Database Command Example
      • Reducing Memory Pressure on Transaction Engines
      • Setting Client Connection Limits for Transaction Engines
      • pynuoadmin Management Client Package Setup
      • Validating Database Archives
      • Useful SQL Metrics Related to Database Operation
      • Useful System Tables Related to Database Operation
      • Load Balancer Policies
        • NuoDB Client-only Package Drivers
        • NuoDB Community Drivers
        • NuoDB Drivers Available at Other Public Sites
        • Enabling TLS in the C++ Driver
        • Database Operations Using C++ Driver
        • Notes for Using C++ Driver
        • Examples of Using C++ Driver
        • C++ Driver API Reference
        • Database Operations Using Java JDBC Driver
        • Enabling TLS in the JDBC Driver
          • Specifying Properties on the Connection URL
          • Configuring a NuoDB DataSource
          • Using try-with-resources
          • JDBC Transaction Isolation Levels Supported by NuoDB
          • Canceling a Statement
          • Setting Query Timeout in Microseconds
          • Logging
          • Exception Handling
          • ResultSet Shelf Life
          • Setting Result Set Holdability
          • LOB Streaming
          • JDBC and XA Transactions Using NuoDB
          • Direct Transaction Engine Connection
        • Examples of Using Java JDBC Driver
        • Java DataSource API Reference
        • Hibernate Dialect Considerations
        • Connecting to a Database Using Java Hibernate Driver
        • Using JNDI
        • Example of Using Java Hibernate Driver
        • Using ADO.NET Driver Objects Directly
        • Database Operations Using ODBC Driver
        • Notes for Using ODBC Driver
        • Enabling TLS in the C Driver
        • Database Operations Using C Driver
        • Notes for Using C Driver
        • Example of Using C Driver
        • C Driver API Reference
      • Sample NuoDB Programs on GitHub
        • About the Vectorized Execution Engine
          • SQL Internationalization and Localization
            • SQL String and Character Types
            • SQL Numeric Types
            • SQL Date and Time Types
            • SQL Boolean Types
            • SQL Binary Types
            • SQL Enumerated Types
            • Implicit Conversions for INSERT and UPDATE Statements
          • SQL Error Codes
            • SQL Aggregate Functions
            • SQL Comparison Operators
            • SQL Conditional Expressions
            • SQL Data Type Conversion Functions
            • SQL Date and Time Functions and Operators
            • SQL Logical Operators
            • SQL Mathematical Functions and Operators
            • SQL Pattern Matching
            • SQL String Functions and Operators
            • SQL System Information Functions
          • SQL Keywords
          • SQL Standard Compliance
            • ALTER DATABASE
            • ALTER FUNCTION
            • ALTER PROCEDURE
            • ALTER SEQUENCE
            • ALTER TABLE
            • ALTER TRIGGER
            • ALTER USER
            • ALTER VIEW
            • ANALYZE
            • CALL
            • COMMIT
            • CREATE FUNCTION
            • CREATE INDEX
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE TABLE
            • CREATE TRIGGER
            • CREATE USER
            • CREATE VIEW
            • DELETE
            • DROP FUNCTION
            • DROP INDEX
            • DROP PROCEDURE
            • DROP ROLE
            • DROP SCHEMA
            • DROP SEQUENCE
            • DROP TABLE
            • DROP TRIGGER
            • DROP USER
            • DROP VIEW
            • EXECUTE
            • EXPLAIN
            • GRANT
            • HELP
            • INSERT
            • KILL CONNECTION
            • KILL STATEMENT
            • LOCK
            • MAP PARTITION
            • RELEASE SAVEPOINT
            • RENAME TABLE
            • REVOKE
            • ROLLBACK
            • SAVEPOINT
            • SELECT
            • SET
            • SHOW
            • START TRANSACTION
            • TRUNCATE
            • UNMAP PARTITION
            • UPDATE
            • USE
          • SQL System Properties
            • ALLSYSTEMFIELDS System Table Description
            • ALLSYSTEMTABLES System Table Description
            • CONNECTIONS System Table Description
            • CONNECTIONSTATEMENTS System Table Description
            • DATATYPES System Table Description
            • DEFAULTSTORAGEGROUPS System Table Description
            • DEPENDENCIES System Table Description
            • DOMAINS System Table Description
            • DUAL System Table Description
            • FIELDS System Table Description
            • FOREIGNKEYS System Table Description
            • FORMATS System Table Description
            • FUNCTIONCOLUMNS System Table Description
            • FUNCTIONS System Table Description
            • GLOBALATOMS System Table Description
            • INDEXES System Table Description
            • INDEXFIELDS System Table Description
            • INDEXHISTOGRAMBUCKETS System Table Description
            • INDEXHISTOGRAMS System Table Description
            • INDEXPREFIXSTATISTICS System Table Description
            • INDEXSTATISTICS System Table Description
            • JAVACLASSES System Table Description
            • LASTSTATEMENT System Table Description
            • LOCALATOMS System Table Description
            • LOCALCONNECTIONS System Table Description
            • LOCALINDEXATOMS System Table Description
            • LOCALQUERYTIMEOUTS System Table Description
            • LOCALSTATEMENTS System Table Description
            • LOCALTABLEATOMFIELDS System Table Description
            • LOCALTABLEATOMS System Table Description
            • LOCALTRANSACTIONALLOCKS System Table Description
            • LOCALTRANSACTIONS System Table Description
            • MEMPROPERTIES System Table Description
            • NODES System Table Description
            • NODESTORAGEGROUPS System Table Description
            • OPEN RESULTS System Table Description
            • PARTITIONCRITERIA System Table Description
            • PARTITIONEDTABLES System Table Description
            • PARTITIONIDS System Table Description
            • PASSWORDS System Table Description
            • PRIVILEGES System Table Description
            • PRIVTYPES System Table Description
            • PROCEDURECOLUMNS System Table Description
            • PROCEDURES System Table Description
            • PROPERTIES System Table Description
            • PSEUDOTABLEFIELDS System Table Description
            • PSEUDOTABLES System Table Description
            • QUERYSTATS System Table Description
            • ROLES System Table Description
            • SCHEMAS System Table Description
            • SEQUENCES System Table Description
            • STATSCACHE System Table Description
            • STORAGEGROUPS System Table Description
            • TABLECONSTRAINTS System Table Description
            • TABLES System Table Description
            • TEMPFIELDS System Table Description
            • TEMPINDEXES System Table Description
            • TEMPINDEXFIELDS System Table Description
            • TEMPTABLES System Table Description
            • TRANSACTIONALLOCKS System Table Description
            • TRANSACTIONS System Table Description
            • TRIGGERCLASSES System Table Description
            • TRIGGERS System Table Description
            • USERROLES System Table Description
            • USERS System Table Description
            • VERSIONS System Table Description
            • VIEW_TABLES System Table Description
        • Understanding EXPLAIN Command Output
        • Current Limitations and Unsupported features
      • Rules for Specifying NuoDB Identifier Names
          • Changing Default Transaction Behavior
          • Overriding Default Transaction Behavior
        • About Lock Behavior
          • About Implicit Transactions
          • About Explicit Transactions
        • Transaction Behavior in SQL Stored Procedures
        • Explicit Transactions and Rollback Modes
          • Description of NuoDB Transaction Isolation Levels
          • Examples of Reading Data After a Concurrent Transaction Updates Data
          • Examples of Selecting Data for Update After a Concurrent Transaction Updates Data
          • Examples of Updating a Row That is Being Updated by a Concurrent Transaction
          • Examples of Concurrent Updates That Do Not Overlap
          • JDBC Transaction Isolation Levels Supported by NuoDB
          • Behavior If Connection to a Database is Lost
          • Behavior If NuoDB Throws an Exception
        • About INNER Join Operations
        • About LEFT OUTER Join Operations
        • About CROSS Join Operations
        • About Multiple Join Operations
        • Using Conditions to Control the Flow of Execution
        • Using Loops to Control the Flow of Execution
          • Variables
          • Supported Statements
          • SQL Procedural Conditional Statements
          • Control Flow
          • Dynamic SQL
          • Comments
        • Creating Database Objects in Stored Procedures
          • DECLARE Cursor
          • OPEN Cursor
          • FETCH Cursor
          • FETCH_STATUS
          • Update and Delete Using WHERE CURRENT OF
          • CLOSE Cursor
          • DEALLOCATE Cursor
          • Examples Using Cursors
        • Using a Result Set as Input to a Stored Procedure
          • Returning a Result Set from a Stored Procedure
          • Returning Multiple Result Sets from a Stored Procedure
          • Using Embedded Java Stored Procedures
          • Using Embedded Java Triggers
          • Using Embedded Java User Defined Functions
          • Unsupported Methods in Embedded Java API
        • About the SELECT List of an Updatable View
        • About the FROM Clause of an Updatable View
        • About the WHERE Clause of an Updatable View
        • Examples of Creating and Updating Views
      • Investigating Hung Queries
        • Identifying Historical Slow Queries Using System Tables
        • Identifying Currently Running Slow Queries Using System Tables
        • Obtaining SQL Elapsed Time Report
        • Running ANALYZE to Obtain Index Statistics
          • Identifying Index Usage (EXPLAIN)
          • Obtaining NuoDB Optimizer Estimates
          • Obtaining Index Use Statistics
          • Example of Obtaining Index Use Statistics
        • Using Optimizer Hints
        • Configuring NuoDB SQL
        • Command Line Editing With NuoDB SQL
          • About the NuoDB SQL Configuration File
          • About the NuoDB SQL History File
          • Using the NuoDB SQL Init File
        • Using Command Line History in NuoDB SQL
        • Paginating NuoDB SQL Output
        • Displaying NuoDB SQL Output Vertically
        • Batch Processing with NuoDB SQL
        • Examples with NuoDB SQL
      • Internationalization and Localization
      • Tuning Tips for System Configuration
      • Tuning Tips for NuoDB Configuration
      • Tuning Tips for Database Design
      • Tuning Tips for Journal Performance
      • About DBT2 Benchmark Performance
      • Providing Diagnostic Information to NuoDB Support
      • Troubleshooting With NuoDB Command
        • Connection Timed Out with Java IOException
        • No Such Database Name Error
        • No Available Nodes Error
      • Identifying and Killing Long-Running Queries
      • Messages Displayed in Relation to Connections Supported
        • About Log Entry Format
        • About Dynamic Logging
        • About Static Logging
        • About NuoDB Admin tier logging
        • Description of Logging Categories
        • Cannot Start Transaction Engine Without a Storage Manager
        • Inconsistent Statistic for Index
        • Insufficient Storage Managers to Start Transaction
        • Storage Manager Startup Failure: Attempt to Initialize Exisiting Archive
        • Transaction Engine or Storage Manager Quits Unexpectedly
      • Basic Regular Expression Constructs
        • Data Type Mappings (nuodb-types.config)
        • Database Configuration for a Particular Host (nuodb.config)
        • Host Properties (nuoadmin.conf)
          • NuoDB Archive - Checking
          • NuoDB Archive - Restoring
          • NuoDB Command Reference
        • NuoDB Loader
          • Downloading NuoDB Migrator Source Code
          • NuoDB Migrator Properties File
          • About NuoDB Migrator Commands
            • NuoDB Migrator Mappings for DEFAULT Values
            • NuoDB Migrator Mappings for MySQL Unsigned Types
            • NuoDB Migrator Mappings for Unsupported Types
            • NuoDB Migrator Mappings for Supported Types
            • Dumping a Source Database to be Migrated
            • Loading Dumped Files into NuoDB Target Database
            • Generating DDL SQL Statement Scripts for Migrated Data
            • Example of Migrating From IBM DB2
            • Example of Migrating From MSSQL
            • Example of Migrating From MySQL
            • Example of Migrating From Oracle
            • Example of Migrating From PostgreSQL
          • Using NuoDB Migrator to Copy NuoDB Databases
            • Overriding NuoDB Migrator Type Mappings
            • Examples of NuoDB Migrator Schema Command
            • Troubleshooting Migration Metadata Error During Schema Generation
          • NuoDB Migrator Dump Command
          • NuoDB Migrator Load Command
        • NuoDB SQL
      • Database Options
      • Environment Variables
        • Agent Log Description
        • NuoDB Admin Event Log
        • NuoDB Admin Audit Log
        • SQL Connections Log
        • Crash Directory Description
        • Engine Stop Log Description
      • Metrics Published by Database Processes
        • Using DbVisualizer
        • Using SQL Workbench/J
        • Using SQuirreL Universal SQL
        • Using MySQL mysqldump Utility
        • Logging
        • Privileges
        • Network Encryption
        • About the Remote Commit Protocol
        • About the Region Commit Protocol
        • About the Local Commit Protocol
        • Using the Scalar Execution Engine
          • SQL Internationalization and Localization
            • SQL String and Character Types
            • SQL Numeric Types
            • SQL Date and Time Types
            • SQL Boolean Types
            • SQL Binary Types
            • SQL Enumerated Types
            • Implicit Conversions for INSERT and UPDATE Statements
          • SQL Error Codes
            • SQL Aggregate Functions
            • SQL Comparison Operators
            • SQL Conditional Expressions
            • SQL Data Type Conversion Functions
            • SQL Date and Time Functions and Operators
            • SQL Logical Operators
            • SQL Mathematical Functions and Operators
            • SQL Pattern Matching
            • SQL String Functions and Operators
            • SQL System Information Functions
            • SQL Window Functions
          • SQL Keywords
          • SQL Standard Compliance
            • ALTER DATABASE
            • ALTER FUNCTION
            • ALTER PROCEDURE
            • ALTER SEQUENCE
            • ALTER TABLE
            • ALTER TRIGGER
            • ALTER USER
            • ALTER VIEW
            • ANALYZE
            • CALL
            • COMMIT
            • CREATE DOMAIN
            • CREATE FUNCTION
            • CREATE INDEX
            • CREATE JAVACLASS
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE TABLE
            • CREATE TRIGGER
            • CREATE USER
            • CREATE VIEW
            • DELETE
            • DROP DOMAIN
            • DROP FUNCTION
            • DROP INDEX
            • DROP JAVACLASS
            • DROP PROCEDURE
            • DROP ROLE
            • DROP SCHEMA
            • DROP SEQUENCE
            • DROP TABLE
            • DROP TRIGGER
            • DROP USER
            • DROP VIEW
            • EXECUTE
            • EXPLAIN
            • GRANT
            • HELP
            • INSERT
            • KILL CONNECTION
            • KILL STATEMENT
            • LOCK
            • MAP PARTITION
            • RELEASE SAVEPOINT
            • RENAME TABLE
            • REPLACE
            • REVOKE
            • ROLLBACK
            • SAVEPOINT
            • SELECT
            • SET
            • SHOW
            • START TRANSACTION
            • TRUNCATE
            • UNMAP PARTITION
            • UPDATE
            • USE
          • SQL System Properties
            • ALLSYSTEMFIELDS System Table Description
            • ALLSYSTEMTABLES System Table Description
            • CONNECTIONS System Table Description
            • CONNECTIONSTATEMENTS System Table Description
            • DATATYPES System Table Description
            • DEFAULTSTORAGEGROUPS System Table Description
            • DEPENDENCIES System Table Description
            • DOMAINS System Table Description
            • DUAL System Table Description
            • FIELDS System Table Description
            • FOREIGNKEYS System Table Description
            • FORMATS System Table Description
            • FUNCTIONCOLUMNS System Table Description
            • FUNCTIONS System Table Description
            • GLOBALATOMS System Table Description
            • INDEXES System Table Description
            • INDEXFIELDS System Table Description
            • INDEXHISTOGRAMBUCKETS System Table Description
            • INDEXHISTOGRAMS System Table Description
            • INDEXPREFIXSTATISTICS System Table Description
            • INDEXSTATISTICS System Table Description
            • JAVACLASSES System Table Description
            • LASTSTATEMENT System Table Description
            • LOCALATOMS System Table Description
            • LOCALATOMSEQUENCES System Table Description
            • LOCALCONNECTIONS System Table Description
            • LOCALINDEXATOMS System Table Description
            • LOCALQUERYTIMEOUTS System Table Description
            • LOCALSTATEMENTS System Table Description
            • LOCALTABLEATOMFIELDS System Table Description
            • LOCALTABLEATOMS System Table Description
            • LOCALTRANSACTIONALLOCKS System Table Description
            • LOCALTRANSACTIONS System Table Description
            • MEMPROPERTIES System Table Description
            • NODES System Table Description
            • NODESTORAGEGROUPS System Table Description
            • OPEN RESULTS System Table Description
            • PARTITIONCRITERIA System Table Description
            • PARTITIONEDTABLES System Table Description
            • PARTITIONIDS System Table Description
            • PASSWORDS System Table Description
            • PRIVILEGES System Table Description
            • PRIVTYPES System Table Description
            • PROCEDURECOLUMNS System Table Description
            • PROCEDURES System Table Description
            • PROPERTIES System Table Description
            • PSEUDOTABLEFIELDS System Table Description
            • PSEUDOTABLES System Table Description
            • QUERYBUFFERSTATS System Table Description
            • QUERYSTATS System Table Description
            • ROLES System Table Description
            • SCHEMAS System Table Description
            • SEQUENCES System Table Description
            • STATSCACHE System Table Description
            • STORAGEGROUPS System Table Description
            • TABLECONSTRAINTS System Table Description
            • TABLES System Table Description
            • TEMPFIELDS System Table Description
            • TEMPINDEXES System Table Description
            • TEMPINDEXFIELDS System Table Description
            • TEMPTABLES System Table Description
            • TRANSACTIONALLOCKS System Table Description
            • TRANSACTIONS System Table Description
            • TRIGGERCLASSES System Table Description
            • TRIGGERS System Table Description
            • USERROLES System Table Description
            • USERS System Table Description
            • VERSIONS System Table Description
            • VIEW_TABLES System Table Description
        • Understanding EXPLAIN Command Output
        • Using the SQL Trace Facility
      • REST API
      • Java DataSource API
      • C Driver API
      • C++ Driver API
    • Glossary
  • NuoDB Docs
    • 4.2.x
    • 4.1.x
    • 4.0.x
  • Deprecated Features
  • Scalar Execution Engine
  • SQL Reference Information

SQL Reference Information

Using the Scalar Execution Engine SQL Internationalization and Localization

All content © 2020 NuoDB.

Site built with Antora and the Antora default ui.