nuodb documentation

User Guide and Reference

| TOC | 3

| Copyright 2012 NuoDB, Inc. All rights reserved. | 3

Contents

Part I: Quick Start.........................................................................................9Run NuoDB..........................................................................................................................................9Execute SQL Commands.................................................................................................................... 9Run a Simple Java Sample Program.................................................................................................10Use NuoDB Console..........................................................................................................................10How to Set Up a Chorus on One or More Hosts................................................................................11

Part II: Installation and Release Notes..................................................... 15Release Notes................................................................................................................................... 15

Bug Fixes and Improvements..................................................................................................15System Requirements........................................................................................................................18Windows Installation.......................................................................................................................... 18Linux Installation (rpm or deb installers)............................................................................................ 19Sample Startup Script on UNIX......................................................................................................... 19Mac OS X Installation........................................................................................................................ 20

Part III: Administration...............................................................................23Launching a Chorus...........................................................................................................................23System Components..........................................................................................................................24Client Connections.............................................................................................................................28Set Up A Pilot Project From The Command Line...............................................................................30Using Amazon Web Services (AWS) Components............................................................................31Troubleshooting NuoDB.....................................................................................................................33ConfigurationTools.............................................................................................................................33

nuodb...................................................................................................................................... 34nuoloader (NuoDB Loader)..................................................................................................... 35nuoagent................................................................................................................................. 37

Automate Configuration Scenarios.................................................................................................... 38Configuration Using Command Line.................................................................................................. 38

Part IV: Programming With NuoDB.......................................................... 45Programming..................................................................................................................................... 45Connections....................................................................................................................................... 45

Use DbVisualizer.....................................................................................................................45JDBC Connections..................................................................................................................47JDBC Connection Properties.................................................................................................. 49ODBC Connections on Windows............................................................................................ 50Example: Using ODBC and MS Excel to Read a NuoDB Database.......................................50

Using NuoDB SQL............................................................................................................................. 53nuosql Command.................................................................................................................... 53Using Parameterized Queries with NuoDB SQL.....................................................................54Batch Processing from the Command Line.............................................................................55Working With Triggers.............................................................................................................55Selecting from the DUAL Table...............................................................................................58Metadata................................................................................................................................. 58Troubleshooting NuoSQL........................................................................................................58

| TOC | 4


SQL Workbench/J................................................................................................................... 59SQuirreL Universal SQL..........................................................................................................60

Sample Client Programs.................................................................................................................... 61C++ Sample Program............................................................................................................. 62Flights Sample Application......................................................................................................63JRuby Sample Program.......................................................................................................... 63Working with Hibernate........................................................................................................... 64

Part V: SQL Language Elements.............................................................. 67Language Elements........................................................................................................................... 67

Data Types..............................................................................................................................67Operators and Operations.......................................................................................................69Expressions.............................................................................................................................75Date and Time Functions........................................................................................................ 76Object Privileges..................................................................................................................... 78Transactions............................................................................................................................78Clauses and Functions............................................................................................................82

Part VI: SQL Reference Pages.................................................................. 87SQL Reference Pages....................................................................................................................... 87

ALTER DOMAIN..................................................................................................................... 87ALTER SEQUENCE................................................................................................................89ALTER TABLE........................................................................................................................ 89ALTER TRIGGER................................................................................................................... 90ALTER USER..........................................................................................................................90COMMIT..................................................................................................................................91CREATE DATABASE..............................................................................................................91CREATE DOMAIN.................................................................................................................. 91CREATE INDEX......................................................................................................................92CREATE PROCEDURE..........................................................................................................93CREATE ROLE.......................................................................................................................93CREATE SCHEMA................................................................................................................. 94CREATE SEQUENCE.............................................................................................................95CREATE TABLE..................................................................................................................... 95CREATE TRIGGER................................................................................................................ 97CREATE USER.......................................................................................................................99CREATE VIEW..................................................................................................................... 100DELETE................................................................................................................................ 101DROP DATABASE................................................................................................................101DROP DOMAIN.....................................................................................................................102DROP INDEX........................................................................................................................102DROP PROCEDURE............................................................................................................103DROP ROLE......................................................................................................................... 103DROP SCHEMA....................................................................................................................103DROP SEQUENCE...............................................................................................................104DROP TABLE....................................................................................................................... 104DROP TRIGGER...................................................................................................................105DROP USER.........................................................................................................................105DROP VIEW..........................................................................................................................106GRANT..................................................................................................................................106INSERT................................................................................................................................. 108RELEASE..............................................................................................................................109REPLACE............................................................................................................................. 110REVOKE............................................................................................................................... 111

| TOC | 5


ROLLBACK........................................................................................................................... 112SAVEPOINT..........................................................................................................................113SELECT................................................................................................................................ 114SET....................................................................................................................................... 119SHOW................................................................................................................................... 120START TRANSACTION........................................................................................................122TRUNCATE...........................................................................................................................123UPDATE................................................................................................................................123UPGRADE............................................................................................................................ 124USE.......................................................................................................................................125

Glossary........................................................................................................................ 128Appendix: Sample Client Application.........................................................................131

Start an agent and a chorus.............................................................................................................131Install the Flights application............................................................................................................131Load the Flights database and start the application........................................................................ 134Understanding the Flights application..............................................................................................135Flights Application Details................................................................................................................136Optimize Performance of Flights Application................................................................................... 137Add transaction engines to Flights application.................................................................................137Add Updates.................................................................................................................................... 138Add Client by Configuring a Flight Booker....................................................................................... 138Benchmark Runtime........................................................................................................................ 139Troubleshooting............................................................................................................................... 139Restarting the tutorial.......................................................................................................................140

Third Party Acknowledgments....................................................................................142

| TOC | 6


Quick Start

Topics:

• Run NuoDB• Execute SQL Commands• Run a Simple Java Sample

Program• Use NuoDB Console• How to Set Up a Chorus on One

or More Hosts

| Quick Start | 8


| Quick Start | 9


Quick Start

Run NuoDBProcedure to get the product up and running

1. If you have not already done so, install NuoDB.

2. Start a command prompt or terminal window and go to the directory where you installed NuoDB. (Thefollowing examples show the default install directory.)

On Windows:

> cd C:\Program Files\NuoDB\

On Linux and MAC OS X

> cd /opt/nuodb

3. Run the following script (on Mac and UNIX platforms "." must be in your path).

> run-quickstart

The script does the following:

• Configures your path• Starts a NuoDB broker• Creates, starts, and loads a NuoDB test database, as follows:

1. Starts a NuoDB storage manager with its repository in /tmp/test2. Starts a NuoDB transaction engine3. Creates sample schema.4. Creates a sample database.5. Imports data to the sample database from /install_dir/nuodb/samples/quickstart/bruins.csv

• Starts an interactive SQL session

Execute SQL CommandsSample SQL Session

Install the product and run the startup script (run-quickstart) in the install directory.

An interactive SQL session starts in the window where you started NuoDB, as shown:

Starting an interactive SQL session with nuosqlSQL>

1. In the window where the SQL session is running issue the following command:

SQL> show tables;

HOCKEY is the sample table created when you started NuoDB.

2. To see what is in the table, run the following two commands:

SQL> use hockey;

| Quick Start | 10


SQL> select * from hockey;

3. Try other commands as you like.

Related LinksLanguage Elements on page 67Reference information about data types, operations, access, variables, indexes, and isolation levels.

SQL Reference Pages on page 87Syntax, descriptions, and examples for SQL statements

Run a Simple Java Sample ProgramHow to run a simple Java application against NuoDB

The sample is in the following file:

$NUODB_HOME/samples/java/HelloDB.java

The following code fragment shows the first several lines of the sample:

public class HelloDB {

/** The driver class provided by NuoDB. */ public static final String DRIVER_CLASS = "com.nuodb.jdbc.Driver";

/** The base URL for connecting to a local database server. */ public static final String DATABASE_URL = "jdbc:com.nuodb://localhost/";

// the established connection to a local server private final Connection dbConnection;}

1. Change directory to /nuodb/samples/java

2. Issue the following commands:

javac HelloDB.javajava –classpath ../../jar/nuodbjdbc.jar:. HelloDB

You should see the following output:

Name for ID=12 is: fred

Use NuoDB ConsoleHow to start NuoDB Console and view the QuickStart processes.

The specific examples in this procedure are based on the assumption that you have already performed thefirst step (Run NuoDB) in the Quick Start, which sets up a test chorus.

1. Start NuoDB Console by issuing the following from the prompt in a terminal shell or window.

java -jar /opt/nuodb/jar/nuodbconsole.jar

| Quick Start | 11


2. When prompted for a password, enter hockey.

3. Click Discover Domains.

4. In the Results pane observe that there is a domain called hockey. This is the domain for the NuoDBdatabase created by the Quick Start script.

5. If it is not already expanded, click the hockey domain to expand it on the Home page.

6. Observe that there is a chorus (named test) and a broker running on localhost in the hockey domain.

A broker is indicated with a broker icon:

7. Select any item in the test chorus and select GO to open the Results pane, which displays several tabs:General, Monitor, and Logger.These tabs allow you to observe information about the domain, specify logging information, and monitorthe behavior of a client program that is running against the chorus.

8. Click Home to get back to where you started.

Use the NuoDB Console to start and stop processes, connect and disconnect to domains, monitorperformance, and add new hosts and domains as required by your application.

Related LinksOverview of Configuration Tools on page 33Using command-line tools as well as NuoDB Console to configure NuoDB

Configuration Using Command Line on page 38Describes some common configuration scenarios and the commands for enabling them on Linux or MacOS X.

nuosql Command on page 53Start NuoDB SQL as a client against a specified chorus. Requires a chorus username and password.

How to Set Up a Chorus on One or More HostsUse NuoDB to start a broker and domain. Then use NuoDB Console to start agents, storage managers,and transaction engines.

The concept of a chorus is a key feature of the support that NuoDB provides for elasticity and scalability.A chorus is a variable number of processes, running on a variable number of hosts, that serve a singledatabase. Transaction engines and storage managers are part of the chorus. To manage the chorus each

| Quick Start | 12


host must run a NuoDB agent, and at least one of the hosts must run a NuoDB broker. An agent can runas a broker if required.

To create a single chorus you must create the following: a broker, a transaction engine, and a storagemanager.

1. On the host where you want to run NuoDB, start a NuoDB broker by issuing the following command.

java -jar nuoagent.jar --broker --password domain_password

This program is in /install_dir/bin.

As soon as a broker is running you have created a NuoDB domain, even if there are not yet anychoruses.

2. Start the NuoDB Console by running the following command:

java -jar nuodbconsole.jar

This program is in /install_dir/jar.

3. When prompted for a password, enter the domain password you specified earlier.

4. On the Home page of the console, discover the broker you just created by clicking DiscoverDomains...Starting the broker caused the creation of a NuoDB domain as well as a NuoDB broker. By default thedomain is named test and is running on localhost, as shown in the Results pane on the Home page ofthe console.

5. In the Results pane, select the test domain and click Add Process in the navigation pane.

6. Create a transaction engine by clicking it on the list and entering a name.

You are required to create a user name and password when you create the first transaction engine for achorus.

7. Similarly, create a storage manager by specifying a directory to use. Be sure to select the initializeoption.In the Results pane, note that there is a storage manager as well as a transaction engine running in thetest domain on localhost.

8. To view the status of the chorus select the transaction engine and click Go.

• After you start a chorus you can connect and run your client application against the chorus and monitorperformance using NuoDB Console. At first, performance may decrease as the new processes start up.You should be able to observe improved performance after this initial interval.

• Use NuoDB Console and other tools to manage and administer the system. You can performadministrative tasks, such as starting and stopping processes, finding and adding brokers. You can alsospecify and examine logging information and metrics.

• You can use NuoDB Loader to import and export data.• You can use the interactive sessions with NuoDB SQL to issue SQL that you want to use as part of

your client appliation.

Installation and Release Notes

Topics:

• Release Notes• System Requirements• Windows Installation• Linux Installation (rpm or deb

installers)• Sample Startup Script on UNIX• Mac OS X Installation

| Installation and Release Notes | 14




Installation and Release Notes

Release NotesThe Beta 6 release is primarily a bug-fix release that also includes new features and substantialimprovements to the overall stability of the platforms.

Improvements and new features are as follows:

• NuoDB includes a license. The license supports up to 10 nodes and prevents starting new nodes afterJune 2012. Contact NuoDB Tech Support ([email protected]) if more nodes are required.

• NuoDB Console provides an administrative hub for NuoDB databases. The console has beensignificantly improved since earlier releases, especially in terms of ease-of-use and breadth of features.

• An easy-to-use GUI provides rich graphical configuration and display capabilities.• Management tools enable DBAs to manage multiple databases that are distributed and deployed at

a customer site, in the cloud, or in a hybrid environment.• Monitoring tools provide comprehensive real-time monitoring capabilities and assist in the

management of the full lifecycle of NuoDB distributed database nodes.• Security enhancements provide encryption of critical network communications between NuoDB

transaction and storage nodes.

• NuoDB offers secure network communications and user access within an Administration Domain,which represents a set of associated NuoDB distributed databases.

• DBAs can define and manage user roles and access rules for the system.• Product Performance, Quality and Usability

• The software includes Index maintenance bug fixes and performance improvements around load/insert use cases.

• Failsafe shutdown of NuoDB transaction and storage nodes has been significantly improved.• Signiificant progress has been made toward complete compliance with SWL.• NuoDB documentation is now available as browser-based HTML documentation and NuoSQL online

help has been added.

Bug Fixes and Improvements

Since the last release the following improvements have been made and bugs fixed.

Indexes

• ADDED: Index improvements (DB-738, DB-724, DB-871, DB-1026, DB-739, DB-725)• FIXED: Duplicate primary key after second update attempt (DB-567, DB-1082)• FIXED: Index not cleaned up during ROLLBACK or Node Failover (DB-886)• FIXED: Index not cleaned up during GC (DB-893)• FIXED: Index insert failure during GC (DB-889)• FIXED: Incomplete index support for blobs (DB-936)• FIXED: Query returning 0 rows when expecting 2 (DB-1147)• FIXED: Indexes on binary data (DB-988, DB-904, DB-902)• FIXED: Uniqueness of values in a UNIQUE KEY not enforced at creation time (DB-950)



Security

• ADDED: All utilities require proper passwords to be accessed• ADDED: Domain security using password model (DB-686)• FIXED: All hardcoded/default passwords removed

Connection Drivers

• ADDED: ODBC and JDBC driver improvements (DB-884, DB-1154)• ADDED: Implement getSchema in JDBC and ODBC (DB-1085)• ADDED: Compatibility with JDK 1.7 confirmed (DB-1054)• ADDED: activerecord-jdbcnuodb-adapter maps BOOLEAN to TINYINT (DB-891)• FIXED: UTF8 handling could possibly corrupt data (DB-975)• FIXED: Incorrect output printed when using the C++ API (DB-963)• FIXED: ODBC: CLOB data not being written correctly (DB-915)• FIXED: New connections always get the most recent commit sequence (DB-901)• FIXED: JDBC large message handling (greater than 5120 bytes) sometimes fails (DB-899)• FIXED: Missing C++ header files (DB-876)• FIXED: Blob.setBinaryStream() not yet implemented (DB-834)• FIXED: PreparedStatement.setObject() does not support VARBINARY (DB-816)• FIXED: column created with type TINYINT has JDBC type 5 (SMALLINT)• FIXED: TINYINT no longer supported (DB-765)• FIXED: java.sql.Connection object isn't thread safe (DB-120)

SQL improvements

• ADDED: Operator in list is now implemented, select * from t1 where f1 in (1,3,5); (DB-549)• ADDED: Select IN LIST supported (DB-150)• ADDED: SQL LENGTH function (DB-635)• ADDED: LOWER function (DB-565)• ADDED: SQRT function (DB-594)• ADDED: SUBSTR function (DB-593) -• ADDED: Support for DUAL table (DB-609)• ADDED: Support for binary(n) (DB-139)• FIXED: Using CAST(AS STRING) in various contexts causes errors (DB-832)• FIXED: Select from view crashes transaction engine (DB-815)• FIXED: Query returning fewer results than expected due to double conversion issue (DB-1118)• FIXED: Hibernate test suite UnionSubclassFilterTest using derived tables and unions crashes

(DB-1083)• FIXED: Unions in derived tables do not work correctly (DB-1080)• FIXED: Query optimization failed for IN LIST queries (DB-1053)• FIXED: NULL results for joins with select list with '*' using derived tables / aliases (DB-1040)• FIXED: CONSTRAINT "name" UNIQUE|PRIMARY is accepted but not actually enforced (DB-1027)• FIXED: Some SYSTEM tables are case-sensitive (DB-1022)• FIXED: Cannot select from SYSTEM.VIEW_TABLES (DB-1020)• FIXED: COLLATION and CHARACTER SET are accepted in definitions even though not supported

(DB-988)• FIXED: SELECT 1 in (NULL) from ... fails (DB-964)• FIXED: alter table add column + select * from table causes nuodb crash (DB-941)• FIXED: Wrong result when casting strings into integers (DB-877)• FIXED: AS is not optional when defining a column name (aka alias) in a select list (DB-843)• FIXED: Cast from integer literal to float is not implemented (DB-836)



• FIXED: Crash with GROUP BY .. ORDER BY COUNT(*) (DB-705)• FIXED: Crash upon SELECT statement with LEFT join and "IS NOT NULL" condition used together

(DB-682)• FIXED: Problems joining derived tables (DB-676)• FIXED: Support Table definitions with > 64K fields (DB-601, DB-602)• FIXED: ALTER TABLE followed by SELECT * FROM <table> could crash if table has data in it.

(DB-599)• FIXED: null constraint is not yet implemented (DB-566)• FIXED: insert causes "count mismatch between field list and value list" for generated column value

(DB-534)• FIXED: Adding a number to a string that's not a number should produce an error (DB-512)• FIXED: Failures in DELETE after performing REPLACE INTO command (DB-435)• FIXED: The SQL ESCAPE function doesn't work in queries with LIKE matching. (DB-383)• FIXED: Using an alias to name a derived table (subquery) in the FROM clause is not accepted

(DB-366)

NuoSQL improvements:

• ADDED: default statement ending mode is now semi-colon (DB-589, DB-973)• ADDED: --user and --password arguments (DB-360)• ADDED: interactive HELP (DB-974)• ADDED: support history (DB-689)• ADDED: !recall# should echo the command before execution (DB-906)• ADDED: session saving (DB-591)• ADDED: more complete show support (DB-587)• ADDED: default schema support (DB-586)• FIXED: automatic reconnect when connection to database is lost (DB-585)• FIXED: nuosql crash with invalid command line switch (DB-757)• FIXED: batch SQL on different lines creates annoying echoing (DB-294)

Failover improvements:

• FIXED: Unhandled SIGPIPE causes a crash on Socket:write (DB-961)• FIXED: Multiple simultaneous node failure handling (DB-980)• FIXED: Deadlock in MasterCatalog:newConnection (DB-946)

Performance

• FIXED: Shutdown issues (DB-726, DB-1106)

Agents, Brokers, and Administration

• ADDED: Need a way to forcibly remove a node from a broker (DB-794)• ADDED: Remote(udp) syslog support (DB-559)• ADDED: Simple license added for Beta6 that times out the product in June.• FIXED: Agents do not always advertise alternate addresses correctly (DB-1024)• FIXED: "recv failed: No error (10054)" error is printed by broker on node disconnection (DB-1018)• FIXED: Brokers get stuck (DB-796)

Internal issues:

• FIXED: Unresolved internal deadlock (DB-743)• FIXED: checkopen hang, multiple nodes in list (DB-736, DB-839)• FIXED: premature relay teardown causes various issues (DB-880)



• FIXED: Transaction Engine crash (DB-722)• FIXED: Transaction Engine crash during open database call (DB-695, DB-698)• FIXED: Misbehaving client applications cause SEGV during Connection destruction (DB-778)

Connection destruction (DB-778)

• FIXED: Transaction Engine hang (DB-1095, DB-848))• FIXED: Transaction Engine deadlock (DB-1079)• FIXED: Transaction threads waiting on a txn that has already committed (DB-1041)• FIXED: Sysbench: Nuodb transaction nodes fails under load with "Assertion `isActive()' failed" (DB-992)• FIXED: SIGPIPE causes crash on socket write (DB-961)• FIXED: Performance problem with full table scan concurrent to a flights workload (DB-849)• FIXED: NuodDB fatal error messages are not printed to STDERR under default logging level (DB-848)• FIXED: Various assertions (DB-744, DB-702, DB-408, DB-982, DB-762, DB-736)• FIXED: Validation failures (DB-737, DB-692)• FIXED: Various Memory leaks (DB-874)

Demo improvements:

• ADDED: Improve Flights Transaction Generator, write stats to file, exclude warm-up period stats(DB-663)

Documentation

• ADDED: add description of SQL Workbench J to documentation (DB-717)• FIXED: inconsistent naming of product components (DB-659)• FIXED: various SQL reference inconsistencies (DB-977)

Platform issue

• FIXED: Install Windows Visual Studio runtime (DB-1047)• FIXED: Windows Installer is now signed with a code signing certificate.

System RequirementsNuoDB runs on Linux, MAC OS X, and Windows, and requires Java SDK.

Operating Systems Supported

• Linux• Mac OS X• Windows

Additional Software Requirements

NuoDB requires Java 1.6, and has been tested using the following software:

• Oracle Java• MAC JDK• Open JDK

Windows InstallationThe NuoDB installer on Windows is a self-extracting executable.



1. Copy the NuoDB executable to your machine and run the executable.

By default NuoDB is installed in:

c:\Program Files\NuoDB

The default location can be changed during the install process.

2. Configure your environment.

Add “c:\Program Files\NuoDB\bin” to your system’s PATH (Do this from Advanced SystemSettings on Windows.)

Linux Installation (rpm or deb installers)To install and add new users you must have write access to the /opt directory.

1. For Linux distributions that support rpm:Run the RedHat Package Manager (RPM) using the NuoDBpack

rpm –-install nuodb-<build>.rpm

By default NuoDB is installed in the /opt/nuodb directory. You can change this location during theinstallation process.

2. For Linux distributions that support dpkg: Run the Debian package utility, dpkg:

dpkg –i nuodb-<build>.deb

Linux installation instructions apply to Amazon EC2 hosts as well. For Amazon EC2, we recommendinstallation of NuoDB on m1.large or m1.xlarge hosts.


Add install_dir/nuodb/bin to the PATH, where install_dir is where you installed the product.

4. Optionally, use init.d scripts to start NuoDB processes.

NuoDB provides a sample init.d script.

Sample Startup Script on UNIXHow to install and configure a sample NuoDB startup script for UNIX platforms

The first set of steps are for LSB-compliant OS's such as RHEL, CentOS.

1. Copy the script to your /etc/init.d folder.

sudo cp ${NUODB_HOME}/etc/nuoagent /etc/init.d/nuoagent

2. Perform these additional steps:

chmod 755 /etc/init.d/nuoagentsudo chmod +x /etc/init.d/nuoagentsudo chkconfig --add nuoagentsudo chkconfig --level 2345 nuoagent on

3. Use the following commands to start and stop the service:

service nuoagent startservice nuoagent stopservice nuoagent restart



service nuoagent status

4. Use the following command to delete the service:

chkconfig --del nuoagent

The following set of steps are for other LSB distributions such as Debian or Ubuntu.

5. Install the service

sudo cp ${NUODB_HOME}/etc/nuoagent /etc/init.d/nuoagentchmod 755 /etc/init.d/nuoagentsudo update-rc.d nuoagent defaults 98 02

6. To delete the service

update-rc.d -f nuoagent remove




Mac OS X InstallationThe NuoDB installer on MAC OS X is a compressed file: .tar.gz file (tarball) or .zip file.

1. Extract the archive.

2. Move the contents to any location.


Add install_dir/nuodb/bin to the PATH, where install_dir is where you installed the product.

Administration

Topics:

• Launching a Chorus• System Components• Client Connections• Set Up A Pilot Project From The

Command Line• Using Amazon Web Services

(AWS) Components• Troubleshooting NuoDB• Overview of Configuration Tools• Automate Configuration

Scenarios• Configuration Using Command

Line

| Administration | 22




Administration

Launching a ChorusTo launch a chorus you must start a broker, a transaction engine, and a storage manager. Passwords arerequired for each of these NuoDB processes.

• A NuoDB broker process is part of the NuoDB management layer, and must be started before any otherNuoDB processes. Starting a broker causes a domain to be defined.

• Starting any of the management processes (agent, broker, console) requires a domain password. Thispassword must be specified by the first broker that is started for the domain and then used for each newmanagement process.

• Similarly, starting any chorus processes (transaction engine, storage manager) requires a choruspassword. You specify the chorus password when you start the first transaction engine for the chorusand then use the password evey time you start a chorus process.

• A password is also required for users of NuoDB SQL. The first user is known as the databaseadministrator. Use the --dba-user and --dba-password option to make these specifications whenyou start the first transaction engine.

• After you launch the chorus, the last step is to start NuoDB SQL. Using NuoDB SQL the databaseadministrator can create new users and privileges.

1. Start a broker

java -jar nuoagent.jar --broker --password domainpwd &

Note that this executable is in nuodb/bin, not in nuodb/jar.

2. Start the first transaction engine.

nuodb --chorus demo --password choruspwd --dba-user admin --dba-password adminpwd &

In this example the chorus name is demo, the password for the chorus is choruspwd. The first user ofNuoDB SQL is admin with a password of adminpwd.

3. Initialize the archive and start the storage manager.

nuodb --chorus demo --password choruspwd --archive ./temp --initialize &

4. Start NuoDB SQL as a client of the chorus, and create a new user.

nuosql demo@localhost --user admin --password adminpwdSQL> create user NewHire password 'Green';SQL> commit;$ nuosql demo@localhost --user NewHire --password GreenSQL> create table hockey (number integer, name string);SQL> show tables;

Tables in schema HOCKEY

HOCKEY

SQL> create role admin;SQL> grant admin to NewHire default;



System ComponentsManagement and chorus layers

A NuoDB system is designed for the configuration, deployment, and management of one or moredatabases on a cloud. A NuoDB client is a user process that connects to NuoDB for the purpose ofaccessing data in a NuoDB system.

From a conceptual point of view, a NuoDB system has two layers: a management layer and one or moreNuoDB choruses. Each chorus is associated with a database.

The following conceptual illustration shows a simple chorus and its relationship to the management layerfor the chorus.



A NuoDB system may contain multiple choruses, each associated with a database. A NuoDB domain isa collection of NuoDB hosts that have been provisioned to work together to support NuoDB processes.Choruses in a domain usually have common licensing and security specifications. By default, when youstart a NuoDB agent, the agent becomes part of a NuoDB domain.

A chorus has the following components:



• A database is a collection of data that can be resolved by means of a unique name.• A transaction engine is a process that provides access to a single database. It handles requests from

clients, caches data, and coordinates transactions. There may be any number of transaction enginesrunning on a single machine or across a cluster.

• A storage manager is a process that provides durability of the data that it handles (for example bywriting to disk). There may be any number of storage managers running on a single machine or acrossa cluster. Each storage manager is associated with exactly one database.

The management layer has the following components:

• A broker is a server that manages access to transaction engines and storage managers, independent ofany particular database (or chorus) in the NuoDB system.

• An agent is a process that manages NuoDB processes running on a particular computer. It starts andstops the NuoDB servers and communicates with the NuoDB broker about the state of the system. Anagent can also act as a broker.

• NuoDB Console communicates and interacts with all the agents in a NuoDB system. It managesdatabases and choruses.

NuoDB makes use of external components such as Amazon EC2, Amazon EBS, and Amazon S3 to createvirtual machines on the cloud.

Overview of A Simple NuoDB System

A NuoDB system designed for production typically has many hosts, which you can add and removedynamically on the cloud according to the changing requirements of your system.

The following conceptual illustration shows a simple NuoDB system with just four hosts.



This example shows the following hosts:

• Part of the management layer (broker and NuoDB Console) runs on Host 1.• An agent and a transaction engine run on Host 2. (The agent is part of the management layer.)



• Another agent and transaction engine on Host 3.• A storage manager for the chorus runs on Host 4, connects to Data Storage on disk, and maintains

communications with an agent running on the same host.

Note that the components of the chorus (transaction engines and storage manager) do not run on thesame host; performance is optimal if they run on separate hosts. Having just one transaction server on ahost also enhances performance.

Like the chorus, the components of the management layer do not have to run on the same host. That is,the agents running on Host 2 and Host 3 are part of the management layer, while other components of themanagement layer (NuoDB Console and the broker) are running on Host 1.

An agent must run on every host in a NuoDB system.

Related LinksJDBC Connection Properties on page 49Program fragment showing JDBC properties and sample connection URL

Client ConnectionsThe following illustration shows a simple NuoDB chorus and a client that wants to connect to it.



The steps in the process shown in this conceptual illustration are as follows:



1. A client that seeks to connect to the database connects to the broker on Host 1.

Because it communicates with agents the broker can determine the existence of a host as well as itsavailability; it determines the best transaction engine for the client connection at any particular time.

2. The broker communicates back to the client that it should connect to the transaction server on Host 3.3. The client connects to Host 3.

Related LinksSQL Reference Pages on page 87Syntax, descriptions, and examples for SQL statements

JDBC Connection Properties on page 49Program fragment showing JDBC properties and sample connection URL

Set Up A Pilot Project From The Command LineDescription of a very simple NuoDB system (not intended for production).

To get started you can set up a non-durable, non-production NuoDB system running on just one host, andadd a second host to improve performance. Doing this as a prerequisite for building a NuoDB productionenvironment helps you to:

• Evaluate and understand the requirements for a NuoDB system.• Learn more about how to maximize performance when you eventually go live with a system that you

use for production.

The goals of this simple system are to

• Run a broker, transaction engine, and a storage manager on a single host with local storage.• Start a chorus on the same host, loading data, and running a client application against the chorus.• Observe performance statistics (such as transactions per second) while running NuoDB on the single

host.• Add another host, run an agent and a transaction engine on this second host, reconnect the client

application, and notice the performance change.• Optionally add more hosts running agents and transaction engines for the same chorus and observe

changes in performance.• Optionally double the storage for the system by adding a host dedicated as an archive.

Note:

The following step-by-step instructions assume that you have a client application that you would liketo run against a database, and that you have available a second host on which to run NuoDB.

1. Start a NuoDB broker on the machine to run a host of NuoDB:


2. Create a local directory for storage (such as ./temp/archive-storage).

3. Start a chorus on the same host, and initialize a storage manager with local storage for the chorus.

nuodb –-chorus mychorus --password chorus_password –archive ./temp/archive-storage –initialize

where mychorus is the name of the database to be stored.

4. Run a client application against the chorus.

Make sure the client connects to NuoDB and loads the database.

5. Configure another host with an agent and a transaction engine and connect it to the first host. On thenew host:



a) Start a transaction engine for the chorus.

nuodb –-chorus --password chorus_password mychorus

b) Start an agent and specify the host running the NuoDB broker (the first host you configured) as apeer:

java -jar nuoagent.jar --password domain_password --peer <hostname or ip address of peer system>

6. Connect and run the client application.

7. Observe performance for each of two transaction engines running on separate hosts.

You can monitor performance using NuoDB Console. At first, performance may decrease as the newprocesses start up. You should be able to observe improved performance after this initial interval.

Note:

Once you have NuoDB up and running you can use NuoDB the console and other tools tomanage and administer the system.

• From NuoDB Console you can perform administrative tasks, such as starting and stoppingprocesses, finding and adding brokers, and others.

• You can use NuoDB Loader to import and export data.• You can use the interactive sessions with NuoDB SQL to issue SQL commands required by

your application.

Using Amazon Web Services (AWS) ComponentsSuggestions for running NuoDB on the cloud

You can integrate and deploy NuoDB by means of the following Amazon Web Services (AWS).

Name of Service Purpose

Amazon ElasticCompute Cloud (EC2)

You can launch an EC2 host using the AWS Management Console and then runa NuoDB broker on the host. Because EC2 has both an internal and externaladdressing scheme, you can configure connections with other brokers runningon EC2 hosts in the cloud, as well as with clients outside the cloud. This featureis very useful for creating a cluster of externally-accessible brokers.

Amazon SimpleStorage Service(Amazon S3)

NuoDB supports archive storage of serialized atoms on Amazon S3. This makesit possible for you to run storage managers in an EC2 cloud without using EBS.

Amazon Elastic BlockStore (EBS)

To support persistent storage as well as high availability and high reliability, youcan run NuoDB on an instance of EC2 and attach EBS.

You can configure these services to work together, or independently, to support scalable cloud applicationsas well as persistence and reliability of a NuoDB system.

NOTE

See the NuoDB Forum for information about launching NuoDB on AWS.

Overview

To run NuoDB on Amazon Elastic Cloud (EC2) you launch hosts of EC2 using the AWS ManagementConsole and then start NuoDB processes on each host as required for your particular deployment. You



can easily add and remove hosts using NuoDB management tools, such as NuoDB Console, and theelasticity features of Amazon EC2 and NuoDB.

What is Amazon EC2

Amazon EC2 is a web service that provides resizeable compute capacity in the cloud. The NuoDBdocumentation assumes that you are familiar with setting up Amazon EC2. For help getting started seehttp://docs.amazonwebservices.com/AWSEC2/2011-07-15/GettingStartedGuide/.

EC2 has both an internal and external addressing scheme, making it possible for you to configure thecloud to allow access among the agents and brokers (using the internal scheme) as well as access toclients (using the external scheme). In other words, each host on the cloud is essentially known by twonames depending on context.

You specify command-line arguments to configure the address when you start each agent and broker.

The following figure shows the design of an initial system running on Amazon EC2 hosts:

As shown, this system runs a transaction engine and an agent as a broker on on instance of AmazonEC2, and a storage manager and an agent on a second instance of Amazon EC2. The storage managerconnects to Amazon S3 for data storage on the cloud. (Because Amazon EC2 does not support persistentstorage on disk, consider using Amazon S3 or Amazon EBS for storage. You could also put the archive ona physical rather than a virtual host.)

http://docs.amazonwebservices.com/AWSEC2/2011-07-15/GettingStartedGuide/



Troubleshooting NuoDBCommon problems and possible solutions

Broker advertising a chorus node that no longer exists.

Restart the broker.

Cannot start a chorus.

It is possible that the broker is advertising a chorus that no longer exists. Restart the broker

Errors returned from JDBC

Error Probable Cause What To Do

"java.io.IOException: Connection timed out"

The client was unable toconnect to the URL providedfor the broker within thespecified timeout limit.

Contact your NuoDB administrator for thecorrect connection URL.

"Unknown service: SQL2"

The client connected toa NuoDB agent, but theagent is not specified as abroker. Only brokers (that is,agents running as brokers)can mediate NuoDB clientconnections.

Start a broker if necessary and reconnectthe client.

"No such database: DATABASE"

The database nameprovided in the URL doesnot correspond to a runningchorus associated with abroker.

Contact your NuoDB administrator todetermine the correct database name andstart a chorus, including a broker.

"Fatal error: No nodes are available"

The broker is unableto locate any runningtransaction engines for thischorus.

Contact your NuoDB administrator to restartthe transaction engine and associate it withthis broker.

Overview of Configuration ToolsUsing command-line tools as well as NuoDB Console to configure NuoDB

The basic building blocks for a NuoDB configuration are agents, brokers, transaction engines, and storagemanagers. This section tells you how to use two executables, nuoagent and nuodb to implement thesebuilding blocks.

Using nuoagent

One instance of a nuoagent executable must run on every host that runs NuoDB services. An agent canrun in either of the following modes:

Agent mode Keeps track of every NuoDB process running on agiven machine and is the point of administration to



the system. Agents are responsible for starting andmonitoring NuoDB processes.

Broker and Agent mode At least one broker must run as part of a NuoDBconfiguration. A broker manages access (from bothclient and administrative connections) to the NuoDBtransaction engines that are configured.

Using nuodb

One or more instances of the nuodb executable can run on a host in a configuration. This commandimplements the following services:

Name of Service Definition

transaction engine(TXE)

Runs SQL transactions on behalf of clients. Add more SQL transaction enginesto scale the database.

storage manager Storage managers provide durability and redundancy. To provide redundantdurability, run a storage manager on more than one host using separate physicalstorage for each storage manager.

Related LinksJDBC Connection Properties on page 49Program fragment showing JDBC properties and sample connection URL

Automate Configuration Scenarios on page 38NuoDB configurations can be automated as follows.

Sample Startup Script on UNIX on page 19How to install and configure a sample NuoDB startup script for UNIX platforms

Use NuoDB Console on page 10How to start NuoDB Console and view the QuickStart processes.



nuodbStart a NuoDB chorus with two or more options. Requires a user name and password.

Synopsis

nuodb --chorus chorus_name --password password [ --dba-password DBA_account_password ] [ options ]nuodb --chorus chorus_name --password password

Description

Starts a NuoDB process, which can be a transaction engine or a storage manager, for a specified chorus.

To launch a chorus (that is, when you start the first transaction engine for it) you must create a passwordfor the NuoDB DBA (database administrator) account. The dba username and the password created with --dba-password are required to create new users and privileges in NuoDB SQL.

To create additional transaction engines a chorus user name and password are required.



Options

Option Definition

--archive archive_name Archive directory for use by storage manager.

--chorus chorus_name Name of chorus. You must also initialize an archive if one does notexist.

--dba-passwordDBA_account_password

Password that must be created when the first transaction enginewas started

--force Overwrite existing archive

--help Print information about this command

--initialize Initialize archive. If the specified archive has already beeninitialized, NuoDB generates an error.

Use --initialize --force to overwrite an existing archive.

--log filename Log file name

--mem Memory threshold

--password password Chorus password.

--verbose Report details such as the following: debug, info, warn, error,dump, stats, tx, net, records, atoms, archive, threads, gc, msgs,security, scheduler, sql, services, validate, flush

--version Display the server version

nuoloader (NuoDB Loader)Import and export data to and from NuoDB

nuoloader chorus_name --user user_name --password chorus_password [options]...

Description

NuoDB Loader is a command-line utility to import and export data to and from a NuoDB chorus. It supportscomma-separated value (CSV) file formats. It runs as a client of NuoDB and requires a chorus user nameand password.

Options

Option Definition

Connection Options

--user user name --password password --schema database schema

Must be specified if required bythe database.

Import options

--import from filename [,import file options] --to [SQL insert | update]

Specifies a CSV file to beimported along with importoptions.

The import options should followthe filename and be separated bycomma.



Option Definition

If filename> is not provided, datais expected on stdin.

[import file options]

--separator - printable character or word "tab" for horizontal tabulation; --skip - the number of lines to skip in the beginning of the file; --trim - may have value of leading, trailing, both or none, it controls stripping leading and trailing white spaces; --escape -backslash '\' is used to escape double quotes, new lines and tabulation.

Optionally specified list ofattributes that indicate formattingconventions used in the input file.

For an import operation, a validprepared SQL INSERT/UPDATEstatement is expected as theargument.

Question mark '?' is used tospecify a position of a parameter. For example: "insert into FOOvalues (?,?,?)"

Export options

--export [SQL SELECT statement] –to [export file options]

Specify an SQL SELECTstatement to be exported into afile in CSV format, and specifyan output file name for the exportoperation.

If no filename is specified outputis sent to stdout.

[export file options]

separator Printable character or word "tab" for horizontal tabulation; titles If specified, columns names are written in the first line;escape Backslash '\' is used to escape double quotes, new lines and tabulationenclose All fields are enclosed in double quotes;

An optional comma separatedlist of modifiers to the output fileformat.

Batching options

batch-size Size of a batch in bytes, default is 64KB batch-count Size of a batch in the number of rows, default is 5000batch-commit Performs commit after batch execution

For better performance data canbe imported using batches.

Options control the size of singlebatch and whether or not eachbatch should be processed as aseparate transaction.

Batching can be turned off bysetting either --batch-size or --batch-count to zero. -

Reporting options

--verbose Reports progress for each executed batch. --log Redirects error and verbose output into a file.



Option Definition

Miscellaneous options

--strict Halts execution on any error. --help Prints the list of options.--version Prints the version.

By default, an erroneous line isreported and execution continues.

nuoagentStarts a NuoDB agent or broker. Requires a domain password.

Synopsis

java -jar nuoagent.jar --broker --password password [options]java -jar nuoagent.jar --peer hostname --password password [options]

Command Line Options

Name Usage

--broker Start the agent as a connection broker.

If not started as a broker, either the peer or broadcast option is required tospecify an entry to an existing domain.

You must create a password the first time you create a broker.

--password Domain password, which is specified the first time a broker runs.

--peer host[:port] Specifies host to use as a peer connection for entering the domain.Requires domain user name and password. Alternatively you can specify--broadcast but there might be a performance impact.

--port [:port] Use the specified port (instead of the default port, which is 48004)

--bin-dir directory Directory containing the nuodb executable.

If the bin-dir flag is not specified, the agent looks in the directory thatcontains the jar file. If no executable is found the command fails.

--advertise-alt Advertise alternate addresses.

--alt-addr address Associate an address with the specified peer

--domain name Name of the domain to support

--port-range start[,end] Range of allowable port numbers for NuoDB hosts.

--broadcastnumber_of_seconds

Enable broadcast advertisement and discovery of NuoDB hosts atintervals specified by number_of_seconds. May degrade perfomancecompared to use of --peer option.

--help Display information about the command

--verbose Enable logging

--version Display the agent version



Description

A nuoagent process must run (as either an agent or a broker) on any instance where NuoDB choruses arehosted.

Examples

Provision a domain with twohosts.

$ java -jar nuoagent.jar --broker --password domain-password $ java -jar nuoagent.jar --peer host1 --password domain-password

Automate Configuration ScenariosNuoDB configurations can be automated as follows.

1. Configure a host to start nuoagent at boot time.

On UNIX you can run an init.d script by using a sample provide in /install_dir/etc/nuoagent.

2. Use options with nuodb commands in a script to set up the required nuodb processes across allmachines.




Configuration Using Command LineDescribes some common configuration scenarios and the commands for enabling them on Linux or MacOS X.

NOTE

Keep in mind that you must start brokers and agents on the command line, but transaction engines andstorage managers can be started more conveniently using NuoDB Console. Also note use of passwordswith all NuoDB processes.

Minimal single host configuration with durability

This configuration does the following:

• Starts an agent running as a broker on Host1.• Initializes a storage manager on the same host.• Starts a transaction engine for the chorus on the same host.

The nuosql client connects to the test database/chorus through the broker running on Host1.

Host1 > java -jar nuoagent.jar –-broker --password domain_password &Host1 > nuodb --chorus test --password chorus_password --archive /archive/test_data –-initialize &Host1 > nuodb –chorus test --password chorus_password &



Host1 > nuosql test@Host1 --user dba-username --password dba_password &

Note: The “—initialize” option for the storage manager is specified only the first time a database isconfigured. The rest of the examples assume the “—initialize” option has been used.

Multi-host configuration with durability and transactional scalability

This configuration does the following:

• Starts an agent running as broker on Host1• Starts a storage manager on Host1 to store data for the test@Host1 chorus in the /archive/test_data

directory.• Transaction engines run on Host1, Host2, and Host3 managing transactions against the chorus named

test.• Agents also run on Host2 and Host3. The –-peer option is required when adding hosts to the system, so

that additional agents can connect to the broker.

Host1 > java -jar nuoagent.jar –-broker --password domain_password &Host1 > nuodb --chorus --password chorus_password test --archive /archive/test_data &Host1 > nuodb –-chorus --password chorus_password test & Host2 > java -jar nuoagent.jar -–peer Host1 --password domain_password &Host2 > nuodb –-chorus --password chorus_password test &Host3 > java -jar nuoagent.jar –-peer --password domain_passwordHost1 &Host3 > nuodb –chorus --password chorus_password test &

Multi-host configuration with durability, data redundancy, and transactional scalability

This configuration is similar to the previous one except that it runs more than one archive manager. It doesthe following:

• Starts an agent running as broker on Host1• On both Host1 and Host2, a storage manager stores data for the test chorus to the /archive/test_data

directory. It is important that the /archive/test_data directory is not shared in any way by multiplestorage managers.

• Transaction engines run on Host1, Host2, and Host3 managing transactions against the chorus namedtest.

• Agents also run on Host2 and Host3.

Host1 > java -jar nuoagent.jar –-broker --password domain_password &Host1 > nuodb --chorus --password chorus_password test --archive /archive/test_dataHost1 > nuodb –-chorus --password chorus_password test Host2 > java -jar nuoagent.jar –-peer Host1 --password domain_passwordHost2 > nuodb --chorus --password chorus_password test --archive /archive/test_dataHost2 > nuodb –-chorus --password chorus_password testHost3 > java -jar nuoagent.jar –-peer Host1Host3 > nuodb –-chorus --password chorus_passwordtest@Host1 …

Multi-host configuration with broker redundancy, durability, data redundancy and transactionalscalability

This is exactly the same configuration as the previous one but also demonstrates how to specify multiplebrokers . Note that the redundant broker runs on the same host as the redundant storage manager.

Host1 > java -jar nuoagent.jar –-broker Host1 --password domain_password &



> nuodb --chorus --password chorus_password test --archive /archive/testdataHost1 > nuodb –-chorus --password chorus_password test Host2 > nuoagent –-broker –-peer Host1Host2 --password domain_password> nuodb --chorus --password chorus_password test --archive /archive/testdataHost2 > nuodb –-chorus --password chorus_password testHost3 > > java -jar nuoagent.jar –-peer Host2Host3 --password domain_password> nuodb -–chorus --password chorus_password test…

In this configuration a client can get a transactional and consistent view of the database by using either thetest@Host1 or test@Host2 path as the connect string from the client. For example:

Host D > nuosql test@Host1 Host D > nuosql test@Host2

Multiple data center and cloud region configuration

You can use NuoDB in a deployment that spans multiple data centers or cloud regions. A typical scenariois a web application deployment that runs in two or more geographies. In this scenario clients can beglobally load-balanced to a specific data center based on their location.

The following deployment scenario has hosts in the UK (uk-host1 and uk-host2) and the US (us-host1 andus-host2). The steps for this deployment are as follows:

1. Provision the four hosts by running an agent on each, starting some of them to act as brokers as well:

uk-host1 > java -jar nuoagent.jar –-broker --password domain_password &uk-host2 > java -jar nuoagent.jar –-peer uk-host1 --password domain_password&us-host1 > java -jar nuoagent.jar –-broker --password domain_password &us-host2 > java -jar nuoagent.jar –-peer us-host1 --password domain_password&

2. Configure the transaction and storage managers:

uk-host2 > nuodb –-chorus --password chorus_password test –-archive /archive/testdata &uk-host1 > nuodb –-chorus --password chorus_password test &us-host2 > nuodb –-chorus --password chorus_password test –-archive /archive/testdata & us-host1 > nuodb –-chorus --password chorus_password test &

3. Connect a client such as nuosql by specifying the chorus as follows:

chorusnname-@brokerhost

For example

test@uk-host1

or

test@us-host1

Some modifications may be required for this format to work in an Amazon cloud deployment. For example,with Amazon you have to accommodate the complexities of the private/public IP model that is used.

Related LinksOverview of Configuration Tools on page 33



Using command-line tools as well as NuoDB Console to configure NuoDB




Programming With NuoDB

Topics:

• Programming• Connections• Using NuoDB SQL• Sample Client Programs

| Programming With NuoDB | 44




Programming With NuoDB

ProgrammingIntroduction to programming with NuoDB

Programming with NuoDB is like programming with any other database: you write your client program andconnect the client executable to NuoDB using a database connection tool such as JDBC.

NuoDB provides nuosql, an interactive interpreter for NuoSQL, which you can use to test out your clientcode.

NuoDB supports the use of external programming tools, such as Hibernate, SQuirreL, and DbVisualizer.

Related LinksSQL Reference Pages on page 87Syntax, descriptions, and examples for SQL statements

ConnectionsNuoDB automatically installs drivers and supports database connections by means of JDBC and ODBC.

NuoDB supports the following drivers:

Name of Driver Usage

JDBC Driver The JDBC driver is installed automatically in the $NUODB_HOME/jar directory,where $NUODB_HOME is the folder where you installed NuoDB.

ODBC Driver The NuoDB installer automatically installs the a 32-bit driver for ODBCconnections on Windows platforms. NOTE: 64bit Windows applications will notwork properly with the 32-bit NuoDB ODBC driver.

Use DbVisualizerHow to configure DbVisualizer for NuoDB JDBC driver

Since NuoDB has full JDBC support, you can use tools such as DbVisualizer with minimal configuration. The most important task is to configure DbVisualizer to use the NuoDB JDBC driver.

1. Download DbVisualizer from their website:

http://www.dbvis.com

2. Copy the NuoDB JDBC driver into the DbVisualizer installation directory.

For example, on Linux, use commands like the following:

cd ~/DbVisualizer-8.0.4/jdbcmkdir nuodbcp /opt/nuodb/jar/nuodbjdbc.jar nuodb

3. Setup DbVisualizer to use the NuoDB driver

a) Open the Driver Managerb) Specify the items in the screen as shown:

http://www.dbvis.com



4. Open a database by creating a connection using the DbVisualizer connection wizard:

Database > Create Database Connection

5. Follow the steps in the wizard.

Choose the NuoDB driver

6. Connect to the database using the connection form:

7. Browse/Interact with the NuoDB database as you like.



JDBC Connections

NuoDB automatically installs drivers and supports database connections by means of JDBC and ODBC.

Drivers

Driver Definition

JDBC Driver The JDBC driver is installed automatically in the $NUODB_HOME/jar directory,where $NUODB_HOME is the folder where you installed NuoDB.

ODBC Driver The NuoDB installer automatically installs the a 32-bit driver for ODBCconnections on Windows platforms. NOTE: 64bit Windows applications will notwork properly with the 32-bit NuoDB ODBC driver.

JDBC Connections

JDBC provides RDBMS access by allowing you to embed SQL inside Java code.

The correct URL syntax for a NuoDB JDBC client is as follows:

jdbc:com.nuodb://{BROKER}:{PORT}/{DATABASE}

where

BROKER is the tcp/ip address for the NuoDB Broker agent, as IP address or DNS name string.

PORT is the configured port for that broker; if not defined it is assumed to be 48004.

DATABASE is the name of the NuoDB chorus database you are connecting to and is required.

The following fragment is from the Java sample; it shows how to connect to the local database server.

/** An example program for connecting to a NuoDB database server. */public class HelloDB {

/** The driver class provided by NuoDB. */ public static final String DRIVER_CLASS = "com.nuodb.jdbc.Driver";

/** The base URL for connecting to a local database server. */ public static final String DATABASE_URL = "jdbc:com.nuodb://localhost/";

// the established connection to a local server private final Connection dbConnection;



/** * Creates an instance of HelloDB and connects to a local server, * as the given user, to work with the given named database * * @param user the user name for the connection * @param password the password for the given user * @param dbName the name of the database at the server to use */ public HelloDB(String user, String password, String dbName) throws SQLException { Properties properties = new Properties(); properties.put("user", user); properties.put("password", password); properties.put("schema", "hello");

dbConnection = DriverManager.getConnection(DATABASE_URL + dbName, properties); }

/** Closes the connection to the server. */ public void close() throws SQLException { dbConnection.close(); }

/** Creates a simple two-column table: id->name. */ public void createNameTable() throws SQLException { try { Statement stmt = dbConnection.createStatement(); stmt.execute("create table names (id int primary key, name string)"); stmt.close(); dbConnection.commit(); } catch(Exception exception) { System.out.println("Skipping table creation: " + exception.getMessage()); } }

/** * Inserts a row into the table. The id must be unique. * * @param id a unique numeric identifier * @param name a name associated with the given id */ public void insertName(int id, String name) throws SQLException { try { PreparedStatement stmt = dbConnection. prepareStatement("insert into names (id, name) values (?, ?)"); stmt.setInt(1, id); stmt.setString(2, name); stmt.addBatch(); stmt.executeBatch(); dbConnection.commit(); } catch(Exception exception) { System.out.println("Skipping insert..."); } }

Related Links



Example: Using ODBC and MS Excel to Read a NuoDB Database on page 50Step-by-step example using Flights application


JDBC Connection PropertiesProgram fragment showing JDBC properties and sample connection URL

String connectURL = "jdbc:com.nuodb://broker_host:48004/my_chorus"; Properties connectProperties = new Properties(); connectProperties.put("user", DB_USER); connectProperties.put("password", DB_PASSWORD); connectProperties.put("schema", "test"); connectProperties.put("isolation", "write_committed"); try { connection = DriverManager.getConnection(connectURL, connectProperties); connection.setAutoCommit(false); } catch (SQLException e) { throw new RuntimeException("TestConnection: cannot open chorus "+chorus+" at "+connectURL + "; check user credentials and broker status.", e); }

Related LinksRun a Simple Java Sample Program on page 10How to run a simple Java application against NuoDB

C++ Sample Program on page 62Simple “Hello” program in C++

JRuby Sample Program on page 63How to run a sample JRuby application and Gems

Working with Hibernate on page 64Information about the Hibernate sample

System Components on page 24Management and chorus layers

Client Connections on page 28The following illustration shows a simple NuoDB chorus and a client that wants to connect to it.

Using Amazon Web Services (AWS) Components on page 31Suggestions for running NuoDB on the cloud

Overview of Configuration Tools on page 33Using command-line tools as well as NuoDB Console to configure NuoDB

Connections on page 45NuoDB automatically installs drivers and supports database connections by means of JDBC and ODBC.

JDBC Connections on page 47ODBC Connections on Windows on page 50NuoDB supports connections by means of ODBC, which is an open specification that provides apredictable API for accessing SQL servers and any data source with an ODBC connection.

Example: Using ODBC and MS Excel to Read a NuoDB Database on page 50Step-by-step example using Flights application



ODBC Connections on WindowsNuoDB supports connections by means of ODBC, which is an open specification that provides apredictable API for accessing SQL servers and any data source with an ODBC connection.

The transaction modes defined by ODBC and supported by NuoDB are shown in the following table.

ODBC Constant Notes

SQL_TXN_READ_UNCOMMITTED Dirty reads, non-repeatable reads and phantom reads canoccur.

SQL_TXN_READ_COMMITTED Dirty reads are prevented; non-repeatable reads andphantom reads can occur.

SQL_TXN_REPEATABLE_READ Dirty reads are prevented; non-repeatable reads happenafter writes; phantom reads can occur.

SQL_TXN_SERIALIZABLE Dirty reads, non-repeatable reads and phantom reads areprevented.

Related LinksExample: Using ODBC and MS Excel to Read a NuoDB Database on page 50Step-by-step example using Flights application


Example: Using ODBC and MS Excel to Read a NuoDB DatabaseStep-by-step example using Flights application

1. Set up and run the Flight Reservation program as described in Flights Tutorial.

2. Open the ODBC Data Source Administrator from the MS Windows Start menu (search for ODBC in theSearch programs and files area, and choose Data Sources (ODBC)).

a) From the ODBC Data Source dialog select NuoODBC.b) In the NuoDB ODBC Setup dialog specify Flights as the schema. (This is the schema used in the

Flights Tutorial database.)

3. Start MS Excel.

a) Select the Data tabb) Select From Other Sourcesc) Select From Microsoft Query. Do not select From Data Connection Wizard.



d) Select FlightsDemo DSN

e) Choose the FLIGHTS table.

f) Open the FLIGHTS tree and select all of the columns using the > button:

g) Click Next until you get to the Query Wizard-Finish dialog. (Optionally select a different sorting optionfor the table.)

h) Select Return Data to Microsoft Excel.



i) Click Finish.j) In the Import Data dialog click OK.

k) Click Refresh

The Flights Tutorial data should be displayed in the spreadsheet.



Using NuoDB SQLStep-by-step instructions to use NuoDB SQL

NuoDB provides nuosql, an interactive interpreter for NuoSQL, which you can use to test out your clientcode.

1. If you have not already done so, start a broker, a chorus, and an archive.

2. Invoke nuosql specifying the name of the database, which is the name of the chorus including the hostwhere the broker was started, and using your password.

3. Issue NuoSQL statements at the SQL> prompt.The following example creates a table, inserts some data, then selects it.

SQL> create schema xyzzy;SQL> use xyzzy;SQL> create table foo (f1 integer, f2 string);SQL> insert into foo (f1, f2) values (123, 'abc');SQL> select * from foo--- ---123 abc

4. Commit changes

nuosql does not commit changes to the database automatically.

a) To commit changes, use the commit statement.

SQL> commit

b) To undo a change that has not been committed, use the rollback statement.

>SQL> rollback

Related LinksLanguage Elements on page 67Reference information about data types, operations, access, variables, indexes, and isolation levels.


nuosql CommandStart NuoDB SQL as a client against a specified chorus. Requires a chorus username and password.

Synopsis

nuosql chorus_name --user username --password password [options]

Description

Options

Name Of Option Description

--user User name

--password Password

--schema Default schema



Name Of Option Description

--encrypt Encryption algorithm name

--config Configuration file

--file Command file

--nosemicolon Do not require semicolon command terminator

--help Display this information

--version Display the agent version





Using Parameterized Queries with NuoDB SQLImproving performance and security by using parameterized queries

A parameterized query is a query in which placeholders are used for parameters and the parameter valuesare supplied at execution time. NuoDB recommends that you use parameterized queries to improveperformance as well as security.

Because NuoDSB saves SQL statements as they are compiled, parameterized queries are available forreuse with new SQL statements without having to recompile the code that specifies the parameter.

For example, suppose you want to present a list of the sequences defined in a particular schema namedschemaName.

The following code fragment shows how you might approach this without parameterized queries:

char buffer [256];int len = sizeof (buffer);snprintf (buffer, len, "select sequenceName from system.sequences where schema = '%s' ", schemaName);statement = prepareStatement (buffer);

Parameterized queries are both safer and faster. In the following alternative, statement represents aparameterized query:

statement = prepareStatement( "select sequenceName from system.sequences where schema=?");statement->setString(1, schemaName);

Using parameterized queries improves security because the literal field names in the database are notused in every statement.

Using parameters also helps you avoid issues around string length. NuoDB implements object names(table, column, view, constraint, sequence) as almost unbounded UTF8 character strings. Moreover, indexand constraint names can be built automatically by concatenating something to another object name. As aresult a buffer may not be long enough to hold even a very simple query.



Batch Processing from the Command LineUsing commands and writing scripts for batch processing of NuoDB SQL

The nuosql interpreter accepts SQL commands from redirected standard input (stdin) or directly by meansof a file directly using the –command <file> argument.

When redirecting input, nuosql suppresses the “SQL >” prompt on output and automatically exits on EOF.

The following example redirects input of SQL commands and sends the output of all operations to anoutput file:

nuosql --password dba_password test@localhost < my_application_ddl.sql &> output.txt

This example executes the commands in filename:

nuosql --file command_filename --password dba_password

Note: When you specify the --command option, nuosql executes all the commands in thespecified file, and then runs interactively. You can change this behavior by specifying an explicit‘exit’ at the end of the specified file.

Working With TriggersExamples of creating, altering, and dropping triggers in NuoDB

A trigger is a named database object that is associated with a table. It is executed when a particular eventoccurs for the table. For example, you can use triggers to check values before they are inserted into atable, or to perform calculations on values that are part of an update.

How NuoSQL Triggers Work

INSERT, DELETE, or UPDATE statements that execute for a table can activate a trigger for the table. Youcan set the trigger to activate either before or after the triggering statement. For example, you can have atrigger activate before each row that is inserted into a table or after each row that is updated.

NOTE

NuoSQL triggers are non-standard. See the ALTER TRIGGER, CREATE TRIGGER, and DROPTRIGGER topics for the exact syntax for NuoSQL triggers.

Example: Creating Triggers

This code fragment shows two triggers: test1A and test1B. One of the triggers fails because of a mismatchof type definitions for a column.

Create three tables (test1,test2, test3). Each of thetables has one cell (a1, a2,a3, respectively).

SQL> use test;SQL> create table test1(a1 int);SQL> create table test2 (a2 int);SQL> create table test3 (a3 int not null primary key);

Define a trigger that inserts200 as the value of a2 intest2 whenever any value isinserted into test1.

SQL> create trigger test1A for test1 > before insert > as > insert into test2(a2) values(200);



> end_trigger;

To show the results of thetrigger, specify an INSERT totest1 as 100.

SQL> insert into test1(a1) values (100);SQL> select * from test1;

A1 ---

100

SQL> select * from test2;

A2 ---

200

Define another trigger ontest1 to insert 200 as thevalue of a2 in test2, and alsoto insert 300 as the value ofa3, whenever any value isinserted into test1.

SQL> create trigger test1B for test1 > before insert > as > insert into test2(a2) values(400); > insert into test3(a3) values(300); > end_trigger;

To show the results of thetrigger, specify an INSERTto test1 as 100. Note thattest2 has two rows added,because of two triggers thatfired before the insert to test1.

SQL> insert into test1(a1) values(100);SQL> select * from test1;

A1 ---

100 100


A2 ---

200 200 400


A3 ---

300

Example: Altering A Trigger

The following code fragment depends on the code in the previous section (Example: Creating Triggers).

An error occurs on the nextinsert into test1. Trigger test1Battempted to add a row to tabletest3 but failed because of

SQL> insert into test1(a1) values(100);duplicate value in unique index TEST3..PRIMARY_KEY



a mismatch in the definitionof test3(a3). (The value oftest3(a3) is defined as a primarykey and must be unique.)

To avoid the error until you canfix the problem, deactivate thetrigger that causes it, namelytest1B.

SQL> alter trigger test1b for test1 inactive;

Show the results when you dothe insert. Only the first trigger(test1A) fires.

SQL> insert into test1(a1) values(100); SQL> A1 --- 100 100 100

SQL> select * from test2; A2--- 200 200 400 200

SQL> select * from test3; A3 --- 300

Example: Dropping A Trigger

The following code fragment depends on the code in the previous section (Example: Altering A Trigger).

To fix the problem with test1Byou must remove it and define itagain. You cannot use ALTERTRIGGER to redefine it.

SQL> drop trigger test1B;

Example: Using A Sequence Number In A Trigger

The following code fragment depends on the code in the previous section.

Fix the problem by defining thetrigger using a sequence insteadof an integer.

SQL> create sequence test3key;SQL> create trigger test1B for test1 >before insert >as >insert into test2(a2) values (400); >insert into test3(a3) values (next value for test3key); >end_trigger;


A3 ---

300



1


A3 ---

300 1 2

Selecting from the DUAL TableDUAL is a table automatically created by NuoDB.

The DUAL table is accessible by the name DUAL to all users. It resolves to a table with a single row. Itappears to be in whatever schema you are using, as well as in the system schema.

Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement.Because DUAL has only one row, the constant is returned only once.

Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will bereturned as many times as there are rows in the table.

You cannot qualify DUAL with any schema other than the system schema.

See the following example

select 'test' from dual;select 'system' from system.dual;create schema foo;use foo;select 'foo' from dual;use test;drop schema foo;

MetadataSchema for describing NuoDB database objects (such as tables, columns, indexes, and others)

The schema that has metadata information is the SYSTEM schema. The table that has metadatainformation is system.tables.

To list all the non-system tables in the database, use the following:

SQL> select * from system.tables where type <> ‘SYSTEM TABLE’

The system schema also contains tables that describe columns (system.fields), indexes, views, and others.

Troubleshooting NuoSQLHow to deal with NuoSQL errors

Query Connection Closed

If you get an SQL error indicating that the query failed because a remote connection closed, restart thechorus and try again.

For example, using the Flights database, you get the following message:

SQL> insert into tickets (address) values ('703 main street')remote connection closed



remote connection closedsocket is broken

Restart the chorus as follows:

$ nuodb --chorus Flights &[2] 4051

SQL Workbench/JHow to use SQL Workbench/J with NuoDB

1. To use the SQL Workbench/J Universal SQL client with NuoDB, download and configure SQLWorkbench/J

a) Download SQL Workbench/J from:

http://www.sql-workbench.net

b) Install SQL Workbench/J according to the instructions on the website.

Note that the steps differ according to platform.

2. Add a driver using the Manage Drivers dialog and configure as required with the following settings asneeded:

Name NuoDB

Example URL jdbc:com.nuodb://localhost/test

Website URL http://www.nuodb.com

Extra Class Path /opt/nuodb/li /nuodbjdbc.jar

Class Name com.nuodb.jdbc.Driver

3. On the Select Connection Profile dialog, use the settings shown:

http://www.sql-workbench.net



4. Start working with your database. You can browse tables, insert rows, and edit values.

SQuirreL Universal SQLHow to use SQuirreL tool

For Java users NuoDB supports both a Hibernate dialect and sample application that makes use ofHibernate and the NuoDB Hibernate dialect.

1. To use the SQuirreL Universal SQL client with NuoDB, download and configure SQuirreL

a) Download SQuirreL from:

http://sourceforge.net/projects/squirrel-sql

b) Install SQuirreL by running the following command

java –jar <downloaded install file>

c) Choose the Base, Standard and Optional Plugin – Data Import packs

2. Run SQuirreL using the script that the installer created (typically as a menu or desktop shortcut)

3. Click the Drivers tab, add a new driver, and configure with the following settings:

Name NuoDB

Example URL jdbc:com.nuodb://localhost/test

http://sourceforge.net/projects/squirrel-sql



Website URL http://www.nuodb.com

Extra Class Path /opt/nuodb/li /nuodbjdbc.jar

Class Name com.nuodb.jdbc.Driver

4. Create an Alias for NuoDB by clicking the Aliases tab and then clicking Create New Alias.

5. Fill out the Add New Alias dialog. (Note: NuoDB is pre-configured with a UserName/Password ofcloud/user):

Select Auto logon and Connect at Startup

6. Start working with your database. The following screen capture shows a graphical view of the Flights schema that is part of the NuoDB

installation:

Sample Client ProgramsYou can find most of the programming samples in the $NUODB_HOME/samples folder. The following tablelists the samples.

Directory Description Language

flights Simple but comprehensiveprogram showing GUI and otherimportant features of NuoDB.

Java

java Simple “Hello” program. Java

cpp Simple “Hello” program. C++

hibernate Sample application that makesuse of Hibernate and the NuoDBHibernate dialect.

Java dialect

jruby Include sample application andGems

JRuby

Related Links



Programming on page 45Introduction to programming with NuoDB





C++ Sample ProgramSimple “Hello” program in C++


$NUODB_HOME/samples/cpp

1. Set up libraries as required

Options Description

On Linux Ensure that LD_LIBRARY_PATH includes /nuodb/lib64. Go to the /samples/cpp directory andissue the following command:

g++ -I../../include HelloDB.cpp ../../lib64/libNuoRemote.so -o HelloDB

NuoDB also requires libuuid.so.1 andlibncurses.so.5.

On Windows:cd C:\Program Files\NuoDB\samples\cppcopy "C:\program files\NuoDB\bin\NuoRemote.dll" cl/EHsc HelloDB.cpp "c:\program files\NuoDB\lib\NuoRemote.lib"

On MAC OS X: Ensure that DYLD_LIBRARY_PATH includes/nuodb/lib64. Go to the /samples/cppdirectory and issue the following command:

g++ -I../../include HelloDB.cpp ../../lib64/libNuoRemote.dylib -o HelloDB

2. Run the built executable

> ./HelloDB test@localhost

You should see the following output:

Name with id of 12: Fred # 12



Flights Sample ApplicationSimple but comprehensive program showing GUI and other important features

See the /nuodb/sample/flights directory in the installation area.

JRuby Sample ProgramHow to run a sample JRuby application and Gems


$NUODB_HOME/samples/jruby/sample.rb

The following code fragment shows the first several lines of sample.rb:

require 'rubygems'require 'active_record'puts "Connecting to database..."ActiveRecord::Base.establish_connection( :adapter => 'nuodb', :database => 'test', :username => 'cloud', :password => 'user')

The application creates a schema, creates several records using the schema definition, and reads themback.

1. Download and install JRuby (this sample has been tested with JRuby 1.6.5)

2. Set your search path to include the jruby executable. For example:

/Library/Frameworks/JRuby.framework/Versions/Current/bin/jruby)

3. In a directory other than the samples directory, install Gems that are required by the sample

> gem install activerecord

4. Change your working directory to the samples/jruby subdirectory

5. Install the Gems that are provided by the sample:

> gem install jdbc-nuodb-1.0.gem > gem install activerecord-jdbcnuodb-adapter-1.0.gem

6. Start up a nuodb chorus (archive node and transaction engine with a "test" database).

>java -jar nuoagent.jar --broker --password domain_password &[1] 8236>nuodb --chorus --password chorus_password test@localhost &[2] 8237

7. Run the sample:

jruby sample.rb

The results should be as follows:

Connecting to database...Create tables...-- drop_table(:sample_users)-- create_table(:sample_users) -> 0.0190s -> -2 rows



Create user records...Print user records...Found 2 records:User(1), Username: fred, Name: Fred Flintstone, admin Street: 301 Cobblestone Way City: Bedrock Zip: 00001User(2), Username: barney, Name: Barney Rubble, member Street: 303 Cobblestone Way City: Bedrock Zip: 00001Modify user records...Print user records...Found 2 records:User(1), Username: fred, Name: FRED FLINTSTONE, member Street: 301 Cobblestone Way City: Bedrock Zip: 00001User(2), Username: barney, Name: Barney Rubble, member Street: 303 Cobblestone Way City: Bedrock Zip: 00001

How To Install JRuby Active Record Driver for NuoDBActive Record supports object-relational mapping (ORM), which makes it possible to focus on the businesslogic of your client application rather than the underlying details of a database.

Using the Active Record adapter allows you to use any JDBC-compliant database with a JRubyapplication. These instructions are for Linux and MAC OS X; use similar steps for Windows.

Download, install, and configure RVM (Ruby Version Manager) implemented for JRuby.

RVM allows you to install and manage several different versions and implementations of JRuby onone computer, and supports the ability to manage different sets of RubyGems on each. For moreinformation, see About Ruby Version Manager

Working with HibernateInformation about the Hibernate sample

For Java users NuoDB supports both a Hibernate dialect and sample application that makes use ofHibernate and the NuoDB Hibernate dialect.

The sample is in the installation area as follows:

/samples/hibernate

Instructions for building and running the sample are in a file in the same folder:

/samples/hibernate/readme.txt file.

The hibernate dialect jar file is found in /samples/jar.

$NUODB_HOME/jar/nuodb-hibernate-1.0.jar

http://ruby.about.com/od/rubyversionmanager/a/whatisrvm.htm

SQL Language Elements

Topics:

• Language Elements

| SQL Language Elements | 66




SQL Language Elements

Language ElementsReference information about data types, operations, access, variables, indexes, and isolation levels.

Data Types Supported By NuoDBDescribes NuoDB extensions, how character and numeric types are handled, as well as standard datatypes and domains.

NuoDB ExtensionsTypes supported as extensions to the standard.

NuoDB supports the following types as extensions to the standard:

• String is a character string of arbitrary length. • Number is a numeric value of arbitrary length and scale. • Binary with no length is an arbitrarily long string of bytes. (The standard specifies Binary with no

length as a single byte.)

Character and Numeric Types

NuoDB handles String types as potentially unlimited in length. NuoDB recommends that you use Stringinstead of char types. There is no storage penalty for using String, so most application developers shoulduse String rather than char or varchar. For varchar character types, NuoDB checks and supports thedefined length.

NuoDB allows both decimal and numeric values to exceed their defined size. NuoDB does not check thesize of a number when performing numeric operations. (According to SQL 2008 standard, numeric valuescannot hold more than the number of digits defined.)

Characters are all stored in UTF8.

NuoDB supports numeric and decimal values up to 19 digits, for any decimal or numeric defined as largerthan 9 places, and up to 9 places for anything smaller.

Standard Data TypesList of keywords of the predefined data types that are supported by SQL 2008 as well as those supportedby NuoDB.

NOTE TO BETA CUSTOMERS

Row types, interval types, explicit precision on time types, and timezone are not supported.

NuoDB does not support constructed types, and user-defined types

Kind of data type Basic types SQL 2008 Keywords supportedby NuoSQL

CHARACTER

CHARACTER VARYING

Character string types

CHARACTER LARGE OBJECT

character [(<n> [characters])]

character varying (<n>[characters])

varchar (<n> [characters])




Character Large Object [(<n>{K| M | G}]) Char Large Object[(<n>{K | M | G}])

CLOB [(<n>{K | M | G}])

National Character [(<n>[characters])]

National Char[(<n> [characters])]NChar [(<n> [characters])]

National Character Varying (<n>[characters])

National Char Varying (<n>[characters])

National Character Large Object[(<n>{K | M | G})]

NChar Large Object [(<n>{K | M |G})]

NCLOB [(<n>{K | M | G})]

BINARY

BINARY VARYING

Binary string types

BINARY LARGE OBJECT

Binary [(<n>)]

Binary Varying [(<n>)]

Binary Large Object [(<n>{K | M |G})]

BLOB [(<n>{K | M | G})]

NUMERIC

DECIMAL

SMALLINT

INTEGER

Exact numeric types

BIGINT

number

Numeric (<n> [,<n>])

Decimal (<n> [,<n>])

DEC (<n> [,<n>])

SmallInt

Integer

Int

BigInt

REALApproximate numeric types

DOUBLE PRECISION

Real

Double Precision

NOTE: NuoDB recommends theuse of Double Precision ratherthan Float.

Boolean type BOOLEAN boolean

DATEDatetime types

TIME

date




TIMESTAMP

Interval type INTERVAL

time [(<n>)] [WITH[OUT] TIMEZONE]

timestamp [(<n>)] [WITH[OUT]TIME ZONE]

DomainsThere is a distinction between a NuoDB administrative domain that contains choruses and NuoDB SQLdomain

Administrative domain

A NuoDB administrative domain is a collection of hosts that have been provisioned to work togetherto support NuoDB processes. The hosts are a set of machines that are under the same physicalmanagement. A database runs on a chorus (also a set of machines), which can cross domains. You canhave a chorus that is partially local and partially in a cloud.

A chorus can span domains and a domain can run all or part of several choruses. Our expectation is thatthere will be one group of people who are responsible for physically provisioning the domain (e.g the ITstaff) and another group who manage databases (DBA's).

SQL domain

NuoDB SQL supports SQL domains, which support a type of abstraction that can be referenced in manytables. A change to the domain causes a corresponding change in the tables.

Operators and OperationsDescribes NuoDB logical and comparison operators.

Logical Operators

NuoDB supports the following logical operators: AND, OR, NOT.

NuoDB uses a three-valued logic system with true, false, and null, which represents unknown. See thefollowing tables.

a b a AND b a OR b

TRUE TRUE TRUE TRUE

TRUE FALSE FALSE TRUE

TRUE NULL NULL TRUE

FALSE FALSE FALSE FALSE

FALSE NULL FALSE NULL

NULL NULL NULL NULL

a NOT a

TRUE FALSE

FALSE TRUE

NULL NULL



The operators AND and OR are commutative, that is, you can switch the left and right operand withoutaffecting the result.

Comparison Operators

Comparison operators are supported for all supported data types. All comparison operators are binaryoperators that return values of type boolean; expressions like 1 < 2 < 3 are not valid (because there is no <operator to compare a Boolean value with 3).

NuoDB supports the comparison operators described in the following table:

Operator Description

< less than

> greater than

<= less than or equal to

>= greater than or equal to

= equal

BETWEEN

In addition to the comparison operators, the special BETWEEN construct is available:

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Notice that BETWEEN treats the endpoint values as included in the range. NOT BETWEEN does theopposite comparison:

a NOT BETWEEN x AND y

is equivalent to

a < x OR a > y

To check whether a value is or is not null, use the constructs:

expression IS NULL expression IS NOT NULL

NOTE: Do not write expression = NULL because NULL is not "equal to" NULL. (The null value representsan unknown value, and it is not known whether two unknown values are equal.) This behavior conforms tothe SQL standard.

BETWEEN Conditional ExpressionSpecify fields based on values within a specified range

Synopsis

[NOT] BETWEEN startpoint AND endpoint

where startpoint defines the first value in the range and endpoint defines the last value in the range.



Description

The BETWEEN conditional expression is true if the first value expression is between the second and thirdvalue expression, or equal to one of them.

If you precede the BETWEEN expression with the optional NOT qualifier, the condition is true if there areno fields with values within the range you specify (with the second and third value expression).

Examples

The following example demonstrates the use of the BETWEEN conditional expression with a numeric field.

Select rows from the hockey table,choosing numbers where the value fornumber contains a value greater thanor equal to 10 and less than or equal to20.

SQL> select number from hockey where number between 10 and 20;

NUMBER -------

11 18 17 20 19 14

Character String Comparison

Character string comparison operators are described in the following table:

Operator Usage Description

= 'string' = 'comparison' A comparison returning trueif string matches comparisonidentically

<> 'string' <> 'comparison' Identical to the != operator

< 'string' < 'comparison' A comparison returning trueif string should be sortedalphabetically before comparison

<= 'string' <= 'comparison' A comparison returning trueif string should be sortedalphabetically before comparison,or if the values are identical

> 'string' > 'comparison' A comparison returning trueif string should be sortedalphabetically after comparison

>= 'string' >= 'comparison' A comparison returning trueif string should be sortedalphabetically after comparison,or if the values are identical

NOTE: NuoDB does not support the != comparison operator

NuoDB supports the LIKE and ILIKE keywords, which call the like() function, and are sometimes referred toas string comparison operators.



LIKE | NOT LIKEConditionPerform pattern matching.

The LIKE condition allows you to use wild cards in the WHERE clause of SELECT, INSERT, UPDATE, orDELETE statements. This allows you to perform pattern matching.

The LIKE condition can be used in any valid SQL statement.

The patterns that you can choose from are:

• % (percent sign) allows you to match any string of any length (including zero length)• _ (underscore) allows you to match on a single character

Examples

How to find all teammembers who playdefense. SQL> select * from hockey where position like 'D%';

NUMBER NAME POSITION TEAM ------- ----------------- --------- ------

43 MATT BARTKOWSKI Defense Bruins 55 JOHNNY BOYCHUK Defense Bruins 33 ZDENO CHARA Defense Bruins 14 JOE CORVO Defense Bruins 21 ANDREW FERENCE Defense Bruins 47 STEVEN KAMPFER Defense Bruins 54 ADAM MCQUAID Defense Bruins 44 DENNIS SEIDENBERG Defense Bruins

Find team memberswho are not forwards.

SQL> select * from hockey where position not like 'Forward';


43 MATT BARTKOWSKI Defense Bruins 55 JOHNNY BOYCHUK Defense Bruins 33 ZDENO CHARA Defense Bruins 14 JOE CORVO Defense Bruins 21 ANDREW FERENCE Defense Bruins 47 STEVEN KAMPFER Defense Bruins 54 ADAM MCQUAID Defense Bruins 44 DENNIS SEIDENBERG Defense Bruins 40 TUUKKA RASK Goalie Bruins 30 TIM THOMAS Goalie Bruins 1 MAX SUMMIT Fan Bruins

Find team memberswith numbers in the40's.

SQL> select * from hockey where number like '4_';


46 DAVID KREJCI Forward Bruins 49 RICH PEVERLEY Forward Bruins 43 MATT BARTKOWSKI Defense Bruins 47 STEVEN KAMPFER Defense Bruins



44 DENNIS SEIDENBERG Defense Bruins 40 TUUKKA RASK Goalie Bruins

CONTAINING OperatorString comparison

Synopsis

string CONTAINING | NOT CONTAINING pattern

Description

Generates a case-insensitive search for a partial match.

Examples

Select names thatcontain a specific letter. SQL> use hockey;

SQL> select * from hockey;


37 PATRICE BERGERON Forward Bruins 11 GREGORY CAMPBELL Forward Bruins 38 JORDAN CARON Forward Bruins 18 NATHAN HORTON Forward Bruins 23 CHRIS KELLY Forward Bruins 46 DAVID KREJCI Forward Bruins 17 MILAN LUCIC Forward Bruins 63 BRAD MARCHAND Forward Bruins 20 DANIEL PAILLE Forward Bruins 49 RICH PEVERLEY Forward Bruins 67 BENOIT POULIOT Forward Bruins 91 MARC SAVARD Forward Bruins 19 TYLER SEGUIN Forward Bruins 22 SHAWN THORNTON Forward Bruins 43 MATT BARTKOWSKI Defense Bruins 55 JOHNNY BOYCHUK Defense Bruins 33 ZDENO CHARA Defense Bruins 14 JOE CORVO Defense Bruins 21 ANDREW FERENCE Defense Bruins 47 STEVEN KAMPFER Defense Bruins 54 ADAM MCQUAID Defense Bruins 44 DENNIS SEIDENBERG Defense Bruins 40 TUUKKA RASK Goalie Bruins 30 TIM THOMAS Goalie Bruins 1 MAX SUMMIT Fan Bruins

SQL> select name from hockey where name containing 'O'; NAME ----------------

PATRICE BERGERON GREGORY CAMPBELL JORDAN CARON NATHAN HORTON BENOIT POULIOT SHAWN THORNTON



MATT BARTKOWSKI JOHNNY BOYCHUK ZDENO CHARA JOE CORVO TIM THOMAS

String Concatenation

Function Return Type Description

string || string string String concatenation. Use thetext concatenation operator (||)to format output results. Use itanywhere a constant value isallowed in an SQL statement.Values may be repeatedlyconcatenated in a singlestatement by appending the ||operator after each appendedstring constant or identifier.

Numeric OperationsDescribes NuoDB extensions, how character and numeric types are handled, as well as standard datatypes and domains.

NuoDB supports the following kinds of numeric operators::

Mathematicaloperators

Mathematical operators affect one or two values, perform a mathematical operation,and return a value of a numeric data type.

Numericcomparisonoperators

Numeric comparison operators draw a conclusion based on two numeric values (suchas whether one is larger than the other) and returns a value of type boolean, set toeither true or false.

Mathematical operators

You can use mathematical operators in the target list, in the WHERE clause of a SELECT statement, oranywhere else a numeric result may be appropriate. This sometimes will include the ORDER BY clause, aJOIN qualifier, or a GROUP BY clause.

See the following table for a list of the mathematical operators supported by NuoDB.

Operator Usage Description

+ a + b Addition of numeric quantities a and b

- a - b Subtraction of numeric quantity b from a

* a * b Multiplication of numeric quantities a and b

/ a / b Division of numeric quantity a by b

% a % b Modulus, or remainder, from dividing a by b

Numeric comparison operatorsUse comparison operators to compare values of types such as integer to one another. They always returna value of type boolean.



These operators are most commonly used in the WHERE clause, but may be used anywhere in a SQLstatement where a value of type boolean would be valid.

Operator Description

< Less-than, returns true if the value to the left is smaller than the value tothe right

> Greater-than, returns true if the value to the left is greater than the valueto the right

<= Less-than or equal-to, returns true if the value to the left is smaller, orequal to, in quantity than the value to the right

>= Greater-than or equal-to, returns true if the value to the left is greater, orequal to, in quantity than the value to the right

= Equal-to, returns true if the values to the left and right of the operator areequivalent

!= Not equal-to

ExpressionsDescribes value and conditional expressions.

Value Expressions

A type cast specifies a conversion from one data type to another. NuoDB supports type cast according tothe following syntax:

CAST (expression AS type)

CAST value AS data_type

CAST (arrivalDate AS string)

Conditional Expressions

NuoDB supports CASE, COALESCE, NULLIF, and IN as described in the following table:

CASE CASE

WHEN condition

THEN result

[WHEN ...]

[ELSE result]

END

Use a CASE clause whereveran expression is valid. conditionis an expression that returns aboolean result.

If the result is true, then the valueof the CASE expression is theresult that follows the condition.

If the result is false, anysubsequent WHEN clauses aresearched in the same manner.

If no WHEN condition is true thenthe value of the case expressionis the result in the ELSE clause.

If the ELSE clause is omitted andno condition matches, the resultis null.

COALESCE COALESCE(value [, ...]) Returns the first of its argumentsthat is not null.



Null is returned only if allarguments are null. It is oftenused to substitute a default valuefor null values when data isretrieved for display.

Like a CASE expression,COALESCE will not evaluatearguments that are not neededto determine the result (that is,arguments to the right of thefirst non-null argument are notevaluated).

NULLIF NULLIF(value1, value2)

Returns a null value if value1 andvalue2 are equal; otherwise itreturns value1

INWHERE coldata IN (x1,x2,x3,...)

is equivalent to the following:

WHERE (coldata=x1 or coldata=x2 or coldata=x3).

Checks if the specified columndata is a member of the specifiedlist. It can be used with WHERE,CHECK, and creation of views.

Date and Time FunctionsSyntax and examples of date and time functions.

The following table describes support for date and time functions

Purpose offunction

Implementationin NuoDB

Example of Usage

Current date Implementedas 'today' 'yesterday'and'tomorrow'are alsosupported

SQL> create table BirthDates (Name string, BirthDate date);SQL> insert into BirthDates values ('Glenn','today'sele); SQL> select * from BirthDates ;

NAME BIRTHDATE ----- ----------

Glenn 2012-02-28

Differencebetween twodates.

Implementedas datearithmetic.

SQL> create table T1 (f1 date, f2 date, f3 date, f4 integer, f5 integer, f6 integer);SQL> insert into T1 (f1, f2, f3) values ('today', 'tomorrow', 'yesterday');SQL> select * from T1;

F1 F2 F3 F4 F5 F6 ---------- ---------- ---------- ------ ------ ------

2012-02-28 2012-02-29 2012-02-27 <null> <null> <null>



Purpose offunction


Example of Usage

SQL> update T1 set f4=f2-f1;SQL> select f4, f5, f6 from T1;

F4 F5 F6 --- ------ ------

1 <null> <null>

Calculate anew date byadding aninterval toan existingdate

Usearithmeticsymbols

SQL> select f1, f1+4 as FourDaysFromNow from T1;

F1 FOURDAYSFROMNOW ---------- ----------------

2012-02-28 2012-03-03

CurrentTimestamp

Implementedas ‘now'. SQL> create table ChangeLog (Name string, Change

string, When timestamp); SQL> insert into ChangeLog values ('me', 'everything', 'now'); SQL> select * from ChangeLog;

NAME CHANGE WHEN ----- ---------- -------------------

me everything 2012-02-28 13:23:02

Calculatea newtimestampusing castfunction

SQL> select cast('now' as timestamp) from dual;

-------------------

2012-02-28 13:30:37

QUARTER(date),Returnquarter fordate.

Notstandard.Notsupported

WEEK(date),Return weekof year fordate.


DAYNAME(date)Return dayof week fordate.

Notsupported

DAYOFMONTH(date)Return dayof month fordate.

Notsupported

DAYOFYEAR(date)Return day

Notsupported



Purpose offunction


Example of Usage

of year fordate.

MONTH(date),Returnmonth ofyear fordate.


NOTE TO BETA USERS

NuoDB SQL does not yet implement the interval data type and does not accept numbers with fractionalcomponents with time/data arithmetic.

Object PrivilegesBecome the administrator (owner) of a database when you create the first transaction engine for it.

A password is required for users of NuoDB SQL. The first user is known as the database administrator.

Use the --dba-user and --dba-password option to make these specifications when you start thefirst transaction engine and then use them as needed. After you launch the first transaction engine, startNuoDB SQL as the database administrator to create new users and privileges.

TransactionsNuoDB supports several levels of transaction isolation and is based on Multi-Version Concurrency(MVCC).

Multi-Version Concurrency (MVCC) is a form of concurrency control that uses multiple versions of recordsto provide a consistent view of the data to each transaction and to prevent concurrent transactions fromoverwriting each other's changes. Under MVCC, readers do not block writers, and vice-versa.

NuoDB supports the following isolation levels. Note that READ_WRITE and READ_ONLY must bespecified explicitly by the transaction statement.

Name of Isolation Level Description Performance Tradoffs and Comments

CONSISTENT_READ Each transaction sees thestate of the database as it waswhen the transaction started,plus changes made by thattransaction.

CONSISTENT_READ produces moreupdate conflicts, but provides completeisolation from changes made by othertransactions.

WRITE_COMMITTED Each transaction sees thestate of the database as it waswhen the transaction started,plus its own changes, exceptin the case of a SELECT…FOR UPDATE.

WRITE_COMMITTED maintains thecomplete isolation of each transactionfor simple SELECT statements, butcan breach the isolation for SELECT… FOR UPDATE, update, and deletestatements. In an update intensiveapplication, WRITE_COMMITTEDtransactions get fewer errors on updatethan CONSISTENT_READ transactions.

READ_COMMITTED A transaction always readsthe most recently committedversion of a record.

READ_COMMITTED transactions offerno guarantee of isolation, but are veryfamiliar to users of database systems thatdo not implement concurrency through



Name of Isolation Level Description Performance Tradoffs and Comments

MVCC. For applications that must runon multiple database systems, includingthose that are not based on MVCC, the READ_COMMITTED level providescompatible behavior.

AUTOCOMMIT For JDBC connections,NuoDB automatically issuesa commit at the end of eachstatement.

If your JDBC application requires thatseveral changes succeed or fail as a group,you must explicitly set AUTOCOMMIT off.

READ_WRITE A transaction may select,insert, update, and deleterecords, subject to theprivileges granted to the userfor that table

May be specified explicitly for a transaction.

READ_ONLY A transaction may selectonly records; it cannot insert,update, or delete records,regardless of the privilegesgranted to the user.

May be specified explicitly for a transaction.

CONSISTENT_READ

When a transaction running at the CONSISTENT_READ isolation level attempts to update or delete arecord that has been changed by a concurrent transaction, it waits for the other transaction to complete. When it completes, the waiting transaction succeeds in its update only if the previous transaction rolledback. Otherwise, the waiting transaction gets an error.

How the waiting transaction handles the error depends on the logic of the application. If the logic requiresupdating that particular record, then the application must rollback the transaction that got the error andretry it. If not, then the transaction can succeed.

For example, at the CONSISTENT_READ isolation level, the following transaction query gets the samevalue back, even if other transactions have updated the value of seatsAvailable.

select seatsAvailable from flights where flightNumber = 23 and flightDate = 'today'

Note:

The CONSISTENT_READ isolation level is the same as SERIALIZABLE, a name that is used in theSQL standard.

WRITE_COMMITTED

Applications that depend on updating the same record repeatedly are difficult to run atCONSISTENT_READ isolation level because there are frequent failures because of conflictingupdates. For example, an application that assigns unique part numbers by incrementing a master partnumber generator gets frequent update conflicts.

NuoDB offers WRITE_COMMITTED, which is a lower level of transaction isolation that avoids updateconflicts at the expense of read consistency.

When a transaction in WRITE_COMMITTED isolation level executes a SELECT … FOR UPDATEstatement and the record being selected has been updated by a concurrent transaction, it waits for the firsttransaction to complete, then, if the transaction commits, it reads the newest version of the record.



So, for example, the NuoDB sample flights application could keep a single value for the number of seatsavailable on a flight and reduce it by one each time a seat is booked. Suppose the following query returnsa value of 45 for a particular transaction.

select seatsAvailable from flights where flightNumber = 23 and flightDate = 'today'

Suppose the same transaction runs the same query except it adds a "for update" qualifier.

select seatsAvailable from flights where flightNumber = 23 and flightDate = 'today' for update

In this case the query might return 44, that is, the FOR UPDATE leads to a different result. Here's why theresults differ:

• At the CONSISTENT_READ isolation level, when a transaction issues the same query more than once,it gets the same results, unless the transaction itself has made change that affects the query.

• At the WRITE_COMMITTED isolation level, SELECT queries in a transaction also return consistentresults aside from changes that it made, except if SELECT queries have a FOR UPDATE qualifier. IfFOR UPDATE is specified as part of the transaction, and there is a record that has been changed bya concurrent transaction, the reader waits for the update to complete, then reads the most recentlycommitted record version. In other words, it "looks ahead" and finds a value it depends on and waits forthat value to be updated.

For example, at the WRITE_COMMITTED level, the following update statement waits for any concurrentchanges to be committed, then applies the update to the most recent version of the record. l

update flights set seatsAvailable = seatsAvailable - 1 where flightNumber = 23 and flightDate = 'today'

In the same situation, a CONSISTENT_READ transaction would get an update conflict error.

The WRITE_COMMITTED level trades absolute consistency for higher throughput in update-intensiveapplications.

Note:

NuoDB supports an alternative method of creating unique values, called SEQUENCES, which isdefined by the SQL standard.

READ_COMMITTED

NuoDB also supports a transaction isolation level called READ_COMMITTED. At this level, a transactionalways reads the most recently committed version of a record. The following query could return 45 the firsttime it is run in a transaction, 43 the next time, and 25 the third time, depending on the number of timesthe record was updated between the running of the statement.

select seatsAvailable from flights where flightNumber = 23 and flightDate ='today'

For data such as seats available in a flight-booking application, these results are good. For a differentapplication, such as one that reconciles financial accounts, having values change during the life span of atransaction makes it almost impossible to generate consistent results.

At the READ_COMMITTED isolation level, NuoDB handles update conflicts in the same way that it handlesthem at the WRITE_COMMITTED level. That is, when a transaction attempts to update a record that wasupdated by a concurrent transaction, it waits for that transaction to commit, then updates the most recentversion of the record.



AUTOCOMMIT

The JDBC Specification requires that default transactions be executed at the AUTOCOMMIT level,meaning that each statement ends in an implicit commit. At this level, update conflicts and deadlocks areless frequent than when a transaction must be explicitly ended. However, an autocommit transaction cannot guarantee that two related changes succeed or fail together. If the application requires that a depositin one account matches with a withdrawal from another account, it should set AUTOCOMMIT off or riskinconsistent results.

Note: Use SHOW AUTOCOMMIT to display the current status of AUTOCOMMIT as an isolationlevel.

READ_WRITE and READ_ONLY

NuoDB accepts qualifiers that specify the operations allowed by a transaction, such as READ_WRITE andREAD_ONLY.

Errors

In all three isolation levels, errors on the database side are returned to the application, which decideswhether to rollback the transaction. As long as the application maintains its connection with the NuoDBdatabase, it maintains control of its transactions. If NuoDB discovers that it has lost its connection with anapplication, changes made by uncommitted transactions belonging to that application are rolled back.

Defaults

NuoSQL statements execute only in the context of a transaction. If there is no active transaction when anapplication issues a statement to NuoDB, it creates a transaction at the CONSISTENT_READ isolationlevel with a qualifier of READ_WRITE: this means that the transaction can both read and change records. For applications that use the JDBC interface, the transaction is also in AUTOCOMMIT level, meaning thatNuoDB automatically issues a commit at the end of each statement. If your JDBC application requires thatseveral changes succeed or fail as a group, you must explicitly set autocommit level off.

Performance Tradeoffs

The performance of the three isolation levels is approximately equivalent.

Related LinksSHOW on page 120Display information about schemas, tables, domains, and status of AUTOCOMMIT isolation level.

DomainsThere is a distinction between a NuoDB administrative domain that contains choruses and NuoDB SQLdomain

Administrative domain

A NuoDB administrative domain is a collection of hosts that have been provisioned to work togetherto support NuoDB processes. The hosts are a set of machines that are under the same physicalmanagement. A database runs on a chorus (also a set of machines), which can cross domains. You canhave a chorus that is partially local and partially in a cloud.

A chorus can span domains and a domain can run all or part of several choruses. Our expectation is thatthere will be one group of people who are responsible for physically provisioning the domain (e.g the ITstaff) and another group who manage databases (DBA's).



SQL domain

NuoDB SQL supports SQL domains, which support a type of abstraction that can be referenced in manytables. A change to the domain causes a corresponding change in the tables.

Clauses and Functions

CHAR_LENGTH FunctionReturns the number of characters or bytes in a specified string

Synopsis

CHAR[ACTER]_LENGTH (string[USING [CHARACTERS|OCTETS]])

Parameters

string Either a quoted string or a column of string type (such as CHAR,VARCHAR, or others)

USING CHARACTERS Returns the number of characters in the string ()

USING OCTETS Returns number of bytes in the string.

Examples

Discover rows in hockey tablethat do not have names specified. SQL> select number, name from hockey where

(CHAR_LENGTH(name)=0);

NUMBER NAME ------- -----

0 52

SUBSTR FunctionSelects all characters beginning at a specified position until the end of the string (or for an optionallyprovided length).

Synopsis

SUBSTR(str, pos [,len])

Description

SUBSTR(str,pos)

Select all characters in the string starting at position pos.

SUBSTR(str,pos, len)

Select all characters in the string starting at pos and continuing len number ofcharacters.

Examples

Select namesof hockeyplayers whoare goalies.

SQL> select name from hockey where (substr(position,1)='Goalie');

NAME -----------



TUUKKA RASK TIM THOMAS

SQL Reference Pages

Topics:

• SQL Reference Pages

| SQL Reference Pages | 86




SQL Reference Pages

SQL Reference PagesSyntax, descriptions, and examples for SQL statements

Related LinksProgramming on page 45Introduction to programming with NuoDB



ALTER DOMAINChange definition of a domain (not yet implemented)

Synopsis

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT } ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name ADD domain_constraint [ NOT VALID ] ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER DOMAIN name VALIDATE CONSTRAINT constraint_name ALTER DOMAIN name OWNER TO new_owner ALTER DOMAIN name SET SCHEMA new_schema


ALTER DOMAIN has not yet been fully implemented.

Description

ALTER DOMAIN changes the definition of an existing domain. You must own the domain to use ALTERDOMAIN.

To alter the owner, you must also be a direct or indirect member of the new owning role, and that role musthave CREATE privilege on the domain's schema.

These restrictions enforce that altering the owner does not do anything you would not be able to do bydropping and recreating the domain.

NuoDB supports the following forms:

SET | DROP DEFAULT Sets or removes the default value for a domain. Defaults only applyto subsequent INSERT commands; they do not affect rows that arealready in a table using the domain.



SET |DROP NOT NULL Changes whether or not a domain is specified to allow NULL valuesor to reject NULL values. You can only use SET NOT NULL if thecolumns using the domain contain no null values.

ADD domain_constraint [ NOTVALID ]

Adds a new constraint to a domain using the same syntax asCREATE DOMAIN.

You can suppress the checksby adding the new constraint(domain_constraint) using the NOTVALID option. Later you can makeit valid using ALTER DOMAIN ...VALIDATE CONSTRAINT.

NuoDB always checks newly inserted or updated rows against allconstraints, even those marked NOT VALID. NOT VALID is onlyaccepted for CHECK constraints.

DROP CONSTRAINT Drops constraints on a domain.

VALIDATE CONSTRAINT Validates a constraint previously added as NOT VALID, that is,verifies that all data in columns that use the domain satisfy thespecified constraint.

OWNER Changes the owner of the domain to the specified user.

SET SCHEMA Changes the schema of the domain. Also moves any constraintsassociated with the domain into the new schema.

Parameters

name The name (possibly schema-qualified) of anexisting domain to alter.

domain_constraint New domain constraint for the domain.

constraint_name Name of an existing constraint to drop.

NOT VALID Do not verify existing column data for constraintvalidity.

CASCADE Automatically drop objects that depend on theconstraint.

RESTRICT Refuse to drop the constraint if there are anydependent objects. This is the default behavior.

new_owner The user name of the new owner of the domain.

new_schema The new schema for the domain.

Notes

ALTER DOMAIN, ADD CONSTRAINT , and ALTER DOMAIN SET NOT NULL fail if the named domain (orany derived domain) is used within a composite-type column of any table in the database.

Examples

To add a NOT NULL constraint to a domain ALTER DOMAIN zipcode SET NOT NULL;

To remove a NOT NULL constraint from a domain ALTER DOMAIN zipcode DROP NOT NULL;

To add a check constraint to a domain ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);



To remove a check constraint from a domain ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;

To move the domain into a different schema ALTER DOMAIN zipcode SET SCHEMA customers;

ALTER SEQUENCEChange definition of a sequence

Synopsis

ALTER SEQUENCE sequence START [WITH] value

Parameters

sequence The name of an existing sequence to alter.

value New starting value for sequence.

Description

ALTER SEQUENCE changes the starting value of an existing sequence.

Refer to CREATE SEQUENCE for a further description of valid arguments.


ALTER SEQUENCE does not work in Beta 6.

Examples

To change the starting valuefor an existing sequence

alter sequence nextCustomer start with 300;

ALTER TABLEChange definition of a table

Synopsis

ALTER TABLE name ADD PRIMARY KEY column_name...

Description

ALTER TABLE changes the definition of an existing table. To change the schema of a domain, you mustalso have CREATE privilege on the new schema.

Use ALTER TABLE to add a new PRIMARY KEY to a table. If PRIMARY KEY is specified, and thecolumns are not already marked NOT NULL, then ALTER TABLE attempts to do set them to NOT NULL.

IMPORTANT

You can use ALTER TABLE DROP COLUMN to drop a column, but only if you drop all constraints thatinclude the column. Otherwise the column is dropped but the constraints are not dropped, and furtheroperations on the table fail.

You cannot drop a column that is part of an index unless the index has been removed.



Parameters

name The name (possibly schema-qualified) of anexisting table to alter.

column_name Name of a new or existing column.

ALTER TRIGGERChange definition of a trigger

Synopsis

ALTER TRIGGER trigger_name for table_name [ ACTIVE ] | [ INACTIVE ]

Parameters

trigger_name The name of an existing trigger to alter.

table_name The name of the table on which this trigger acts.

Description

Use ALTER TRIGGER to specify whether a trigger should be activated or not.

You must own the table to enable or disable triggers associated with it.

ACTIVE enables a trigger, and INACTIVE disables a trigger.

Refer to CREATE TRIGGER for a further description of valid arguments.

Examples

Disable a trigger alter trigger customer_audit_trigger for customer inactive;

ALTER USERChange definition of a user

Synopsis

ALTER USER username PASSWORD password

Use ALTER USER to change the attributes of a NuoDB user account. Attributes that are not specified inthe command retain their previous settings.

Only a database owner can change privileges and password expiration with this command.

NOTE TO BETA USERS

Users are not currently allowed to change their own passwords.

Parameters

username The name of the user whose attributes are to bealtered.

password The new password to be used for this account.



Notes

Use CREATE USER to add new users, and DROP USER to remove a user.

Examples

Change a userpassword

ALTER USER davide PASSWORD 'hu8jmn3';

COMMITCommit the current transaction

Synopsis

COMMIT [ WORK ]

Parameters

WORK is an optional keyword that has no effect.

Description

COMMIT commits the current transaction. All changes made by the transaction become visible to othersand are guaranteed to be durable if a crash occurs.

Use ROLLBACK to stop and undo a transaction.

You can rollback to a save point, but you cannot commit a save point. A COMMIT implicitly releases allnamed save points. See SAVEPOINT for more information.

Examples

To make all changespermanent

commit work;

Related LinksTransactions on page 78NuoDB supports several levels of transaction isolation and is based on Multi-Version Concurrency(MVCC).

CREATE DATABASEUse NuoDB Console to create and drop databases; you cannot use NuoSQL for this purpose.

Related LinksWhat is NuoDB Console?Overview of Configuration Tools on page 33Using command-line tools as well as NuoDB Console to configure NuoDB


CREATE DOMAINDefine a new domain

Synopsis

CREATE DOMAIN domain_name [AS] data_type



[ [NOT] NULL ] [ DEFAULT default_value ]

Parameters

domain_name The name (optionally schema-qualified) of adomain to be created.

data_type The underlying data type of the domain.

DEFAULT default_value

The DEFAULT clause specifies a default value forcolumns of the domain data type. The value is anyvariable-free expression (but subselects are notallowed). The data type of the default expressionmust match the data type of the domain. NuoDBuses the default expression in any insert operationthat does not specify a value for the column. If thereis no default for a domain, then the default is NULL.Note: If a default value is specified for a particularcolumn, it overrides any default associated with thedomain. In turn, the domain default overrides anydefault value associated with the underlying datatype.

Description

Use CREATE DOMAIN to register a new data domain for use in the current database. The user whodefines a domain becomes its owner.

Usage

Domains are useful for specifying as an abstraction common columns between tables into a single locationfor maintenance.

Examples

Create two domains that areused in the flight example.

create domain airport_code string; create domain airline_code string; create domain Identifier number;

Create and use a domain forproduct names.

create domain name_type char(50) default 'NuoDB'; create table company (name name_type, product name_type); insert into company (product) values ('one'),('two'); select * from company; name product ----- -------- 'NuoDB' one 'NuoDB' two

CREATE INDEXDefine a new index

Synopsis

CREATE [ UNIQUE ] INDEX index_name ON table ( column [, ...] )



Parameters

index_name The name of the index to be created. No schemaname can be included here; the index is alwayscreated in the same schema as its parent table.

table The name (possibly schema-qualified) of the tableto be indexed.

UNIQUE Causes the system to check for duplicate values inthe table when the index is created (if data alreadyexist) and each time data is added. Attempts toinsert or update data which would result in duplicateentries generates an error.

column The name of a column of the table.

Description

CREATE INDEX constructs an index index_name on the specified table.

NuoDB provides B-tree access for indexes.

Use DROP INDEX to remove an index.

Examples``

Define an index based on fivecolumns of the flights table.

create index flt_idx on flights (origin, destination, flight_date, scheduled_departure);

CREATE PROCEDUREReturn the exact string to use to recreate a stored procedure (Not Yet Implemented)


CREATE PROCEDURE is not implemented.

Synopsis

CREATE PROCEDURE proc_name

Description

Examples

CREATE ROLEDefine a new database role

Synopsis

CREATE ROLE name

Description

A user gains privileges by assuming one or more roles, as permitted by the DBA.



CREATE ROLE adds a new role to a NuoDB database. A role is an entity that can own database objectsand have database privileges.

It is recommended that you create roles with privileges and assign roles to users, rather than assignprivileges directly to users.

You must be a database owner to use the CREATE ROLE command.

Parameters

name The name of the new role.

Notes

To change the attributes of a role, use DROP ROLE to remove a role and create a new one.

The recommended approach to add and remove members of roles that are being used as groups is to useGRANT and REVOKE.

Examples

Create a rolefor data baseadministrator.

create role dba;

CREATE SCHEMADefine a new schema

Synopsis

CREATE SCHEMA [schemaname]

Parameters

schemaname The name of a schema to be created. If notspecified, by default the name is USER.

Description

CREATE SCHEMA enters a new schema into the current database. The schema name must be distinctfrom the name of any existing schema in the current database.

If you create a table, view, domain, or sequence, and you wish to use the current schema you do not haveto specify the schema explicitly.

A schema is essentially a namespace; it contains named objects (tables, data types, functions, andoperators) whose names may duplicate those of other objects existing in other schemas.

Named objects are accessed either by "qualifying" their names with the schema name as a prefix, or bysetting a search path that includes the desired schema(s). Unqualified objects are created in the currentschema.

Notes

To create a schema, the you must have CREATE privilege for the current database. Owners of thedatabase bypass this requirement.

Use DROP SCHEMA to remove a schema.



Examples

Create a schema create schema newSchema

CREATE SEQUENCEDefine a new sequence

Synopsis

CREATE SEQUENCE seqname [ START WITH value]

Description

CREATE SEQUENCE enters a new sequence number into the current database starting with a specifiedvalue. A sequence is a source of unique values.

This command supports a mechanism in the database that creates unique numbers automatically,a mechanism which is called a SEQUENCE in the SQL Standard. To avoid interference betweentransactions, sequences are outside transaction control.

You can also use the GENERATED ALWAYS AS IDENTITY clause to create a sequence for a tablewhen you create it. In this case NuoDB creates a sequence for the table and assigns the next value forthe sequence regardless of the value passed in the INSERT statement. Dropping the table makes thesequence go away.

Examples

Create a sequence calledcustomerUpdate starting at 101

create sequence customerUpdate start with 101;

Get the next value for a sequencenamed customerUpdate to use in anupdate operation

update customers set address = '13 Main Street', updateTag = next value for customerUpdate where lastName = 'Joe Smith';

Create Tickets table usingGENERATED ALWAYS ASIDENTITY clause for the ticket_id

create table Tickets ( ticket_id Identifier not null generated always as identity primary key, trip_id Identifier references Trips, flight_id Identifier references Flights, sequence number );

CREATE TABLEDefine a new table

Synopsis

CREATE TABLE [schema_name.]table_name ( { column_name {



data_type | domain } [ DEFAULT default_expr ] [ column_constraint [, ... ] ] } [, ... ] )

where column_constraint is:

{ GENERATED ALWAYS AS IDENTITY | NOT NULL | NULL | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ] }

Description

CREATE TABLE creates a new, initially empty table in the current database. The table is owned by theuser issuing the command.

If a schema name is specified (for example, CREATE TABLE myschema.mytable ...) then the table iscreated in the specified schema. Otherwise it is created in the default schema.

A table cannot have more than 32000 columns. (In practice, the effective limit is lower because of record-length constraints).

GENERATED ALWAYSAS IDENTITY clause

You can use the GENERATED ALWAYS AS IDENTITY clause to createa sequence for a table when you create it. In this case NuoDB createsa sequence for the table and assigns the next value for the sequenceregardless of the value passed in on the INSERT statement. Dropping thetable makes the sequence go away.

You can also specify an identity name, as shown:

SQL> create table t1 (f1 integer generated always as identity (i1));SQL> select * from system.sequences;

SCHEMA SEQUENCENAME ------- -------------

AWH I1

Parameters

table_name The name (optionally schema-qualified) of the table to becreated.

column_name The name of a column to be created in the new table.

data_type The data type of the column. The data type specifies the lengthand scale factor for the column.

domain The name of a domain that defines the characteristics of thiscolumn

DEFAULT default_expr The DEFAULT clause assigns a default value for the column.The value expression, default_expr, cannot include a variable,



subselect or cross-references to other columns in the currenttable.

The data type of the default expression must be comparablewith the data type of the column. For example, the defaultexpression for a numeric value must evaluate to a number,and the default expression for a timestamp must evaluate to atimestamp.

The default expression is used in any insert operation thatdoes not specify a value for the column. If there is no defaultfor a column, then the default is NULL, unless the column isconstrained to be NOT NULL, in which case a value must besupplied for the column.

NOT NULL The column is not allowed to contain NULL values.

NULL The column is allowed to contain NULL values. This is thedefault.

CHECK (expression) CHECK clauses specify integrity constraints or tests that mustbe satisfied by new or updated rows to enable an insert orupdate operation to succeed. CHECK constraints are satisfied ifthey resolve to 'true' or 'unknown'.

Each constraint must be an expression producing a Booleanresult. A condition appearing within a column definition shouldreference that column's value only, while a condition appearingas a table constraint may reference multiple columns.

CHECK expressions cannot contain sub-selects nor refer tovariables other than columns of the current row.

CREATE TRIGGERDefine a new trigger

Synopsis

CREATE TRIGGER name [FOR] table { [ BEFORE | AFTER ] event } [ POSITION number ] [ ACTIVE | INACTIVE ] AS [ statements ] END_TRIGGER


POSITION number is not implemented.

If multiple triggers of the same kind are defined for the same event, they are fired in random order.

Parameters

name The name to give the new trigger. This must bedistinct from the name of any other trigger for thesame table.

table The name of the table the trigger is for.



event The operation that causes the trigger to fire. One ofINSERT, DELETE or UPDATE.

number Specifies the order in which a trigger executes on agiven table.

statements Definition of actions to perform as part of thetrigger. One or more of the following:

INSERT UPDATE DELETE IF ( boolean_expression ) statements [ ELSE statements ] END_IF THROW string_expression

SELECT does not modify any rows so you can not create SELECT triggers. Use views instead of triggers.

ACTIVE enables a trigger, and INACTIVE disables a trigger.

Description

CREATE TRIGGER enters a new trigger into the current database.

A trigger is a database object that is attached to a table. It is fired only when an INSERT, UPDATE orDELETE occurs. You specify the modification action(s) that fire the trigger when it is created. NuoDBautomatically executes a batch of SQL code when the trigger is fired.

The trigger can be specified to fire before the specified action is attempted, or after the specified action(event) has occurred:

Before the operation is attempted

Before an operation means before constraints are checked, as well as before an INSERT, UPDATE orDELETE is attempted. For INSERT and UPDATE operations, the trigger either skips the operation for thecurrent row, or changes the row being inserted.

After the operation has been attempted

After the event means after constraints are checked and after the INSERT, UPDATE or DELETE hascompleted. If the trigger fires after the event, all changes, including the last insertion, update, or deletion,are "visible" to the trigger.

Notes

Triggers make use of two special tables:

• The OLD table contains the data referenced in an INSERT before it is actually committed to thedatabase.

• The NEW table contains the data in the underlying table referenced in a DELETE before it is actuallyremoved from the database.

When an UPDATE is issued both tables are used. More specifically, the new data referenced in theUPDATE statement is contained in the first table, and the data that is being updated is contained in thesecond table. An UPDATE trigger is used to perform an action after an update is made on a table.

Refer to the DROP TRIGGER command for information on how to remove triggers.

Examples

Specify a trigger (namedcustomer_audit_trigger) for a tablenamed customer. The trigger updates

SQL> create table customer(name char(40)) SQL> create table customer_audit(name char(50), updated datetime)



the name and time in another table(ccustomer_audit) after the name isupdated in the customer table.

SQL> create trigger customer_audit_trigger for customer AFTER update SQL> as SQL> insert into customer_audit(name, updated) values (NEW.name,'now'); SQL> end_trigger

Use the trigger and get results.SQL> insert into customer values ('adam');SQL> select * from customer;

NAME -----

adam SQL> update customer set name='adam'; SQL> select * from customer_audit;

NAME UPDATED ----- ------------------- adam 2011-10-26 13:27:28

Create a trigger that tracks the value ofupdates

SQL> create table t (n int, trail string); SQL> create trigger insert_t for t before insert as > trail = n;; > end_trigger;SQL> create trigger update_t for t before update as > trail = trail || ',' || n;; > end_trigger; SQL> insert into t (n) values(1); SQL> update t set n = 2; SQL> update t set n = 3; SQL> update t set n = 4; SQL> update t set n = 5; SQL> select * from t;

N TRAIL -- --------- 5 1,2,3,4,5

CREATE USERDefine a new database user account

Synopsis

CREATE USER username PASSWORD 'password'

Description

CREATE USER adds a new user to a NuoDB database. Use DROP USER to remove a user.

Use CREATE ROLE to grant privileges to users.

Parameters

username The name of the user.



password Sets the user's password. The password can be setor changed later, using ALTER USER.

Examples

Create a user with no password CREATE USER jonathan;

Create a user with a password CREATE USER davide WITH PASSWORD 'jw8s0F4';

CREATE VIEWDefine a new view. Note limitations with SELECT.

Synopsis

CREATE VIEW [schema.]view [ ( column name list ) ] AS SELECT query

Parameters

view The name (optionally schema-qualified) of a view tobe created.

column name list An optional list of names to be used for columnsof the view. If specified, these names override thecolumn names that would be based on the SQLquery.

query An SQL query (that is, a SELECT statement) whichprovides the columns and rows of the view.


Although you can specify GROUP BY with CREATE VIEW, it does not return correct results.

The SELECT clause for a view cannot contain a HAVING clause or UNION clause.

You cannot use AS SELECT * FROM when creating a view.

You cannot update a view.

Support for the optional list of column names is not yet implemented.

Description

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, a query rewriterule (an ON SELECT rule) is automatically generated to support SELECT operations on views.

If a schema name is specified (for example, CREATE VIEW myschema.myview ...) then the view iscreated in the specified schema. Otherwise it is created in the current schema.

The view name can be the same as names for sequences and indexes. It must be distinct from the nameof any other view or table.

Use DROP VIEW to drop views. To change a view, drop and recreate it.



Examples

Creates a view that hasparticular data from aparticular state.

CREATE VIEW MaineAirports (airport_name, city, airport_code, altitude) AS SELECT airport_name, city, airport_code, altitude from Airports WHERE state IN(SELECT state FROM airports WHERE code = 'RKD');

DELETERemove table rows as specified by WHERE. See also TRUNCATE.

Synopsis

DELETE FROM [schema].table [ WHERE condition ];

Parameters

table The name (optionally schema-qualified) of anexisting table.

condition A selection query that returns the rows to bedeleted.

Description

DELETE removes rows from the specified table as specified by the WHERE clause. If the condition(WHERE clause) is absent, the effect is to delete all rows in the table. The result is a valid, but empty table.

NOTE: TRUNCATE provides a faster mechanism to remove all rows from a table.

You must have write access to the table in order to modify it, as well as read access to any table whosevalues are read in the condition.

Examples

Delete any flights that do not leave from, orarrive at, known airports, and also deleteflights that are not part of a known airline.

delete from flights where not exists (select airport_code from airports a where a.airport_code = destination) or not exists (select airport_code from airports a where a.airport_code = origin) or not exists (select airline_code from airlines al where al.airline_code = airline_code);

DROP DATABASEUse NuoDB Console to create and drop databases; you cannot use NuoSQL for this purpose.

Related LinksWhat is NuoDB Console?



Overview of Configuration Tools on page 33Using command-line tools as well as NuoDB Console to configure NuoDB


DROP DOMAINRemove a domain from the database

Synopsis

DROP DOMAIN [IF EXISTS] [SCHEMA. ] [ name ] DROP DOMAIN [SCHEMA.] [name ] [IF EXISTS]

Description

If you do not specify the name of the domain, the default domain is removed.

If the domain does not exist and you specify IF EXISTS, NuoDB does not generate an error. Otherwise, ifthe domain does not exist, an error is generated.

Dropping a domain does not cascade to dropping columns defined in terms of that domain. That is, if anycolumn in any table is defined in terms of the domain specified to be dropped, the definition of that columnremains valid and includes all attributes of the dropped domain.

This behavior complies with SQL2008.

Examples

Removes the Identifier domain drop domain Identifier

DROP INDEXRemove an index

Synopsis

DROP INDEX index_name [IF EXISTS]

Parameters

index_name The name of an index to remove.

Description

DROP INDEX drops an existing index from the database system. To execute this command you must bethe owner of the index.

If the index does not exist and you specify IF EXISTS, NuoDB does not generate an error. Otherwise anerror is generated if the index does not exist.

Refer to CREATE INDEX for information on how to create indexes.

Examples

Remove the title_idx index drop index title_idx;



DROP PROCEDURERemove a specified procedure or function from storage


DROP PROCEDURE is not implemented.

Synopsis

DROP PROCEDURE procedure_name [ CASCADE | RESTRICT]

Description

Use the DROP PROCEDURE statement to remove a standalone stored procedure from the database.

DROP ROLERemove a role from the database

Synopsis

DROP ROLE role_name [ IF EXISTS ]

DROP ROLE [ IF EXISTS ] role_name

Description

Use the DROP ROLE statement to remove a role from the database. When you drop a role, NuoDBrevokes it from all users and roles to whom it has been granted and removes it from the database. Usersessions in which the role is already enabled are not affected.

If the role does not exist and you specify IF EXISTS, NuoDB does not generate an error. Otherwise, if therole does not exist, an error is generated.

Examples

Drop the dba role drop role dba;

Notes

New user sessions cannot enable the role after it is dropped.

DROP SCHEMARemove a schema

Synopsis

DROP SCHEMA name [ CASCADE | RESTRICT ] [ IF EXISTS ]

DROP SCHEMA [ CASCADE | RESTRICT] [IF EXISTS ] name

Parameters

name The name of a schema.

CASCADE Automatically drop objects (tables, functions, andothers) that are contained in the schema.

RESTRICT Refuse to drop the schema if it contains anyobjects. This is the default.



IF EXISTS If the schema does not exist and you specify IFEXISTS, NuoDB does not generate an error.Otherwise, if the schema does not exist, an error isgenerated. An empty schema is treated as if it doesnot exist.

Description

DROP SCHEMA removes schemas from the data base.

A schema can only be dropped by its owner. Note that the owner can drop the schema (and thereby allcontained objects) even if he does not own some of the objects within the schema.

Refer to the CREATE SCHEMA statement for information on how to create a schema.

DROP SEQUENCERemove a sequence

Synopsis

DROP SEQUENCE [ IF EXISTS ] [SCHEMA. ] sequence_name [IF EXISTS ]

Parameters

sequence_name The name (optionally schema-qualified) of a sequence.

IF EXISTS If the sequence does not exist and you specify IF EXISTS, NuoDB doesnot generate an error. Otherwise, if the sequence does not exist, an erroris generated.

Description

Use the DROP SEQUENCE statement to remove a sequence from the database.

You can also use this statement to restart a sequence by dropping and then re-creating it.

DROP TABLERemove a table. See also TRUNCATE.

Synopsis

DROP TABLE [IF EXISTS] [CASCADE | RESTRICT] [SCHEMA.] tablename [IF EXISTS] [CASCADE | RESTRICT]

Parameters

tablename The name (optionally schema-qualified) of anexisting table to drop.

CASCADE Automatically drop objects that depend on the table(such as views).

RESTRICT Refuse to drop the table if there are any dependentobjects. This is the default.



IF EXISTS If the table does not exist and you specify IFEXISTS, NuoDB does not generate an error.Otherwise, if the table does not exist, an error isgenerated.

Description

Use the DROP TABLE statement to remove the table and all its data from the database entirely.

DROP TABLE removes tables from the database. Only its owner may destroy a table. A table may beemptied of rows, but not destroyed, by using DELETE. You can also use TRUNCATE.

DROP TABLE always removes any indexes, triggers, and constraints that exist for the target table.

DROP TRIGGERRemove a trigger

Synopsis

DROP TRIGGER trigger_name [ IF EXISTS ] DROP TRIGGER trigger_name [IF EXISTS ]

Parameters

trigger_name The name of an existing trigger.

table The name (optionally schema-qualified) of a table.

IF EXISTS If the trigger does not exist, and you specify IFEXISTS, NuoDB does not generate an error.Otherwise, if the trigger does not exist, an error isgenerated.

Description

Use the DROP TRIGGER statement to remove a database trigger from the database. The trigger must bein your own schema, or you must have the DROP ANY TRIGGER system privilege.

DROP TRIGGER removes an existing trigger definition. To execute this command the current user mustbe the owner of the table for which the trigger is defined.

Examples

Avoid generating an error whendeleting a trigger in the casewhere it does not exist.

drop customer_audit_trigger if exists;

DROP USERRemove a database user account

Synopsis

DROP USER name [ IF EXISTS ]



Description

Use the DROP USER statement to remove a database.

When you drop a user, NuoDB purges all of that user's schema objects from the recycle bin.

Parameters

name The name of a user.

IF EXISTS If the user does not exist and you specify IFEXISTS, NuoDB does not generate an error.Otherwise, if the user does not exist, an error isgenerated.

Examples

To drop a user account drop user jonathan;

DROP VIEWRemove a view

Synopsis

DROP VIEW [IF EXISTS] [CASCADE | RESTRICT] [SCHEMA.] viewname [IF EXISTS] [CASCADE | RESTRICT]

Parameters

viewname The name (optionally schema-qualified) of anexisting view.

CASCADE Automatically drop objects that depend on the view(such as other views).

RESTRICT Refuse to drop the view if there are any dependentobjects. This is the default.

IF EXISTS If the view does not exist and you specify IFEXISTS, NuoDB does not generate an error.Otherwise, if the view does not exist, an error isgenerated.

Description

DROP VIEW drops an existing view from the database. To execute this command you must be the ownerof the view.

You can change the definition of a view by dropping and re-creating it.

Notes

Refer to CREATE VIEW for information on how to create views.

GRANTDefine access



Synopsis

GRANT rolename [, ...] TO username DEFAULT GRANT { SELECT | INSERT | UPDATE | DELETE |EXECUTE | ALTER | GRANT | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename TO ROLE rolename [, ...]

GRANT { SELECT | INSERT | UPDATE | DELETE |EXECUTE | ALTER | GRANT | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename TO username [, ...]


REFERENCES is not yet implemented.

You cannot yet specify multiple database objects in a single statement when specifying or revokingprivileges.

Description

Use the GRANT statement to grant privileges to roles (rather than users) and then assign roles to users.

Users can have several roles, each of which, by default, are active or inactive at any point in time. Activeroles are the only roles available to a user at a given time. To specify that a user should have a role asactive by default, use the DEFAULT keyword when specifying the role.

The GRANT command gives specific permissions on an object (table, view, sequence, database, function,procedural language, or schema) to one or more users or groups of users. These permissions are added tothose already granted, if any.

You do not have to grant privileges to the creator of an object, because the creator has all privileges bydefault. (The creator could, however, choose to revoke some of his own privileges for safety.)

The ability to grant and revoke privileges is inherent in the creator and cannot be lost. The right to drop anobject, or to alter it in any way not described by a grantable right, is likewise inherent in the creator, andcannot be granted or revoked.

The possible privileges are described in the following table. The privileges required by other commands arelisted on the reference page describing that command.

SELECT Allows SELECT from any column of the specifiedtable, view, or sequence.

INSERT Allows INSERT of a new row into the specifiedtable.

UPDATE Allows UPDATE of any column of the specifiedtable. SELECT ... FOR UPDATE also requires thisprivilege (besides the SELECT privilege).

DELETE Allows DELETE of a row from the specified table.

EXECUTE

ALTER

GRANT



ALL PRIVILEGES Grant all of the privileges applicable to the objectwithout specifying them individually.

NuoDB Administration

NuoDB defines a special user, named DBA, as the administrator of the database, who has all rights. Whena new user account is created, it has no rights. Either the creator or DBA must grant rights to new users,using the DEFAULT qualifier to indicate that a specified role is active by default.

A new role has no rights until someone grants rights to it.

Although the SQL standard says that a user's role is established at connection time, NuoDB allows usersto add and drop roles under application control, possibly as often as they look up new tables. This flexibilitycan be helpful in developing a client application to run against NuoDB.

Notes

Use the REVOKE command to revoke access privileges.

Database owners can access all objects regardless of object privilege settings.

To grant privileges to only a few columns, you must create a view having the desired columns and thengrant privileges to that view.

NuoDB supports {WITH GRANT OPTION}, which specifies permission inheritance.

Examples

Assign the admin role toa new user. By default allprivileges are active.

SQL> show tables;

Tables in schema HOCKEY

ENGINEERS HOCKEYSQL> create role admin;SQL> grant all on hockey to role admin;SQL> create user esther password 'esther22';SQL> grant admin to esther default;

Grant SELECT privileges toa new user. SQL> create user alta password 'glenn';

SQL> grant select on hockey to alta;

INSERTCreate new rows in a table

Synopsis

INSERT INTO table [ ( column [, ...] ) ] VALUES ( expression [, ...] )[, (expression [, ...]) … | SELECT query ]INSERT INTO table DEFAULT VALUES

Parameters


column The name of a column in table.



expression A valid expression or value to assign to column.

query A valid query. Refer to the SELECT statement for afurther description of valid arguments.

Description

Use the INSERT statement to add rows to a table.

INSERT allows you to insert new rows into a table. You can insert a single row at a time or several rows asa result of a query. The columns in the target list may be listed in any order.

Each column not present in the target list is inserted using a default value, either a declared DEFAULTvalue or NULL. NuoDB rejects the new column if a NULL is inserted into a column declared NOT NULL.

If the expression for each column is not of the correct data type, automatic type coercion is attempted.

DEFAULT

Use the DEFAULT keyword to insert a default value that you have defined for a column.

Prerequisites

For you to insert rows into a table, you must have the INSERT object privilege on the table. You must haveINSERT privilege to a table in order to append to it, as well as SELECT privilege on any table specified in aWHERE clause.

Examples

Add a column foraddresses to the ticketstable, and insert a value

SQL> alter table tickets add column address varchar(30); SQL> insert into tickets (address) values ('703 main street')

Define a table with adefault value for thedayweather column andinsert this value to thetable.

SQL> create table days (daynumber bigint generated by default primary key, dayweather string default 'partly cloudy');SQL> insert into days (daynumber, dayweather) values ();SQL> insert into days (daynumber) values ();SQL> insert into days (dayweather) values ('sunny');SQL> insert into days values ();SQL> insert into days default values;SQL> insert into days (daynumber, dayweather) values (432, default); SQL> select * from days; DAYNUMBER DAYWEATHER ---------- -------------

9 partly cloudy 10 partly cloudy 11 sunny 12 partly cloudy 13 partly cloudy 432 partly cloudy

RELEASERelease a save point. See SAVEPOINT.



Synopsis

RELEASE SAVEPOINT savepoint_name;


REPLACEReplace one or more values in a table with new values

Synopsis

REPLACE [INTO] [schema.]table_name [ ( column [, ...] ) ] VALUES ( expression [, ...] ) [, (expression [, ...]) … | SELECT query

Parameters

table_name The name (optionally schema-qualified) of anexisting table.

column The name of a column in table_name. Must includethe primary key if replacing values in an existingrow.

expression A valid expression or value to replace the currentvalue.

query A valid query. Refer to the SELECT statement for adescription of valid arguments.

DescriptionUse the REPLACE statement to update or replace values in a table.

If you specify a value with a a primary key NuoDB attempts to update an existing row. If the row does notexist, the replacement values are inserted as a new row. (Primary keys are always NOT NULL.)

You can replace a single row at a time, or replace several rows as a result of a query. The columns in thetarget list may be specified in any order.

If the expression for each column is not of the correct data type, automatic type coercion is attempted.

Examples

Change the value for ‘AS’ in theairlines table. In this example‘AIRLINE_CODE’ is a primarykey.

SQL> select * from airlines AIRLINE_CODE AIRLINE_NAME ------------- -------------------------- AS Alaska Airlines Inc.



AA American Airlines Inc. CO Continental Air Lines Inc. DL Delta Air Lines Inc. F9 Frontier Airlines Inc. HA Hawaiian Airlines Inc. OH Comair Inc. OO SkyWest Airlines Inc. UA United Air Lines Inc. US US Airways Inc. WN Southwest Airlines Co. YV Mesa Airlines Inc. SQL> replace into airlines (AIRLINE_CODE, AIRLINE_NAME) VALUES ('AS', 'AAA Airlines'); SQL> select * from airlines AIRLINE_CODE AIRLINE_NAME ------------- -------------------------- AS AAA Airlines AA American Airlines Inc. CO Continental Air Lines Inc. DL Delta Air Lines Inc. F9 Frontier Airlines Inc. HA Hawaiian Airlines Inc. OH Comair Inc. OO SkyWest Airlines Inc. UA United Air Lines Inc. US US Airways Inc. WN Southwest Airlines Co. YV Mesa Airlines Inc.

REVOKERemove access privileges

Synopsis

REVOKE ROLE rolename ON [ TABLE ] tablename FROM { username | ROLE rolename} [, ...]REVOKE privileges ON [ TABLE ] tablename FROM { username | ROLE rolename} [, ...]




REFERENCES is not yet implemented.

REVOKE FROM username has no effect if you specify an invalid user name.

Description

Use the REVOKE statement to remove roles and privileges from users and roles.

IMPORTANT

You must specify a tablename. Using REVOKE without specifying a table name has no effect.

Examples

Remove admin role privileges for auser

SQL> create user alta password '1922';SQL> create user admin password '0724';SQL> create role admin;SQL> grant admin to admin;SQL> grant admin to alta;SQL> revoke admin from alta;

ROLLBACKStop and end the current transaction

Synopsis

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT savepoint_name

Parameters

savepoint_name Name of save point that you want to be able tospecify with ROLLBACK

Description

Use the ROLLBACK statement to undo work done in the current transaction, or to manually undo the workdone at a specified save point. ROLLBACK rolls back the current transaction and causes all the updatesmade by the transaction to be discarded.

Notes

NuoDB recommends that you explicitly end transactions in application programs using either a COMMITor ROLLBACK statement. If you do not explicitly commit the transaction and the program terminatesabnormally, then NuoDB rolls back the last uncommitted transaction.

To roll back your current transaction, no privileges are necessary.

Use COMMIT to successfully terminate a transaction.

See SAVEPOINT for more information about save points.

Examples

To stop and end all changes ROLLBACK WORK;




SAVEPOINTMark and name a point within a transaction to use with ROLLBACK and RELEASE

Synopsis

SAVEPOINT savepoint_name RELEASE savepoint_nameROLLBACK TO SAVEPOINT savepoint_name

Parameters

savepoint_name Name of save point that you want to be able tospecify with ROLLBACK

Description

Use SAVEPOINT to mark a point (that is, a save point) within a transaction and specify a name for it.

You can use ROLLBACK TO SAVEPOINT to undo NuoDB operations to the specified save point.

Use RELEASE to explicitly release the specified save point.

A COMMIT implicitly releases all named save points and commits all uncommitted data.

ROLLBACK to a named save point releases all save points back to that named save point. (A ROLLBACKwith no arguments releases all named save points within the transaction. )

Examples

Create several savepoints in a table (t)and rollback to each.

SQL> insert into t values (1) SQL> savepoint first; SQL> insert into t values (2); SQL> savepoint second; SQL> insert into t values (3);SQL> savepoint third; SQL> insert into t values (4); SQL> select * from t; N -- 1 2 3 4 SQL> rollback to savepoint second; SQL> select * from t; N -- 1 2 SQL> insert into t values (3); SQL> insert into t values (4); SQL> select * from t; N

Related Links



Transactions on page 78NuoDB supports several levels of transaction isolation and is based on Multi-Version Concurrency(MVCC).

SELECTRetrieve rows from a table or view

Synopsis

SELECT * | select_item [, ...] FROM from_list [ WHERE condition ] [ GROUP BY group_list [, ...] [ HAVING condition [, ...] ]] [ UNION [ ALL | DISTINCT ] select ] [ ORDER BY order_list] [ OFFSET start [ROW[S]]] [ FETCH [{FIRST | NEXT}] count [ROW[S]] [ONLY] [ FOR UPDATE ]

Parameters

select_item column | expression | literal [[as] item_alias]

column [{source_name | source_alias}.]column_name

source_name Name of a table, view or procedure directly listed inthe from_list.

source_alias Alternate name for a data source

column_name Name of a column from a data source

expression string_expression | numeric_expression |boolean_expression

item_alias Alternate name for select_item that can differentiateit from other select_items that have the samename, or give a name to an expression.

from_list { qualified_data_source [,...] | qualified_data_source[join_clause qualified_data_source [on_clause] ...]

qualified_data_source data_source [AS] source_alias

data_source [schema.]base_table | [schema.]view |[schema.]procedure | derived_table

derived_table ( select ) [AS] source_alias

select Select statement with all features except ORDERBY, OFFSET/FETCH, and FOR UPDATE clauses.

schema Name of the schema that contains the data source

condition Boolean expression giving a result of true or false.See WHERE and HAVING clauses.

Outputs

rows The complete set of rows resulting from the queryspecification.



Description

SELECT retrieves rows from zero or more tables. An abstract version of the processing steps for aSELECT statement is as follows:

1. All elements in the FROM list are computed. If more than one element is specified in the FROM list,they are joined together.

2. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from theoutput.

3. If the GROUP BY clause is specified, the output is aggregated into groups of rows that match on oneor more values. If the HAVING clause is present, NuoDB eliminates groups that do not satisfy the givencondition.

4. The actual output rows are computed using the SELECT output expressions for each selected row.5. The UNION operator combines the output of multiple SELECT statements to form a single result set.

The UNION operator returns all rows that are in one or both of the result sets. Duplicate rows areeliminated unless ALL is specified.

6. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BYis not given, the rows are returned in whatever order the system finds fastest to produce.

7. DISTINCT eliminates duplicate rows from the result. ALL (the default) returns all candidate rows,including duplicates.

8. If the OFFSET or FETCH clause is specified, the SELECT statement returns a subset of the resultrows.

9. If FOR UPDATE is specified, the SELECT statement locks the selected rows against concurrentupdates.

Note:

You must have SELECT privilege on a table to read its values. The use of FOR UPDATE requiresUPDATE privilege as well.

FROM Clause

The FROM clause specifies one or more data sources for the SELECT.

If the select statement contains no restrictions in its ON or WHERE clause, the result is the full crossproduct (Cartesian join) of all the sources. Typically, qualification conditions are added to restrict thereturned rows to a small subset of the Cartesian join.

The FROM clause can contain the following elements:

from_list Data source. Sources can be joined with explicitjoin_types, or in a comma separated list. (Thecomma separated list was deprecated in SQL-92.)

data_source Either a derived ta,ble or the name of a tableor view. The name of the view or table can bequalified with a schema name.

derived_table select statement enclosed in parentheses. Thedata sources within the derived table can bequalified with a schema name.

alias Substitute name for the FROM item containing thealias.

An alias is used for brevity or to eliminate ambiguityfor self-joins (where the same table is scannedmultiple times). When an alias is provided, itcompletely hides the actual name of the table orfunction.



For example given FROM foo AS f, theremainder of the SELECT must refer to this FROMitem as f not foo. A column alias list can bewritten to provide substitute names for one or morecolumns of the table.

join_type One of the following:

[ INNER ] JOIN LEFT[ OUTER ] JOIN RIGHT[ OUTER ] JOIN FULL[ OUTER ] JOIN

For the INNER and OUTER join types, a joincondition may be specified in an ON join_condition,or in the WHERE clause.

A JOIN clause combines two data sources. INNERJOIN produces pairs or records from the twodata sources, matched by the conditions in theWHERE clause and ON clause. LEFT OUTERJOIN returns all rows from the data source beforeit, and matching rows from the data source thatfollows. Matching is again based on the contents ofthe ON clause.

Rows from the first data source that have nomatching rows in the following data sourcerepresent the values that would have come fromthat source as nulls.

If a matching condition is specified in the WHEREclause only rows from first data source that do notmatch that condition will not be included in theresult.

Conversely, RIGHT OUTER JOIN returns all thejoined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). Thisis just a notational convenience, since you couldconvert it to a LEFT OUTER JOIN by switching theleft and right inputs.

FULL OUTER JOIN returns all the joined rows,plus one row for each unmatched left-hand row(extended with nulls on the right), plus one row foreach unmatched right-hand row (extended withnulls on the left).

ON join_condition An expression resulting in a value of type boolean(similar to a WHERE clause) that specifies whichrows in a join are considered to match. Thejoin_condition in the ON clause should includeonly Boolean values that describe the relationshipbetween the data sources being joined.

WHERE Clause

The optional WHERE clause has the general form:



WHERE condition

where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfythis condition is eliminated from the output. A row satisfies the condition if it returns true when the actualrow values are substituted for any variable references.

GROUP BY Clause

The optional GROUP BY clause has the general form

GROUP BY expression [, ...]

GROUP BY condenses into a single row all selected rows that share the same values for the groupedexpressions. expression can be a column name, or the alias or ordinal number of an output column(SELECT list item).

Aggregate functions, if any are used, are computed across all rows making up each group, producing aseparate value for each group.

Without GROUP BY, an aggregate produces a single value computed across all the selected rows. WhenGROUP BY is present, the expressions on a SELECT list cannot refer to ungrouped columns, exceptwithin aggregate functions. Otherwise there would be more than one possible value to return for anungrouped column.

HAVING Clause

The optional HAVING clause has the following general form, where condition is the same as specified forthe WHERE clause.

HAVING condition

HAVING eliminates group rows that do not satisfy the condition.

HAVING is different from WHERE as follows: WHERE filters individual rows before the application ofGROUP BY, while HAVING filters group rows created by GROUP BY.

Unless the reference appears within an aggregate function each column referenced in condition mustunambiguously reference a grouping column

SELECT List

The SELECT list is specified between the key words SELECT and FROM. The list specifies expressionsthat form the output rows of the SELECT statement. The expressions can (and usually do) refer to columnscomputed in the FROM clause.

Instead of an expression, you can use an asterisk ( * ) in the output list as a shorthand for all the columnsof the selected rows. Also, you can write table_name.* as a shorthand for the columns coming from justthat table.

UNION Clause

The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement


In Beta 6, the Union clause does not support WHERE, GROUP BY, or other useful clauses.

ORDER BY Clause

The optional ORDER BY clause has the following general form, where expression can be the name orordinal number of a column (SELECT list item). :



ORDER BY expression [ ASC | DESC] [, ...]

The ORDER BY clause causes the result rows to be sorted according to the specified expressions. If tworows are equal according to the leftmost expression, they are compared according to the next expressionand so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.

The ordinal number refers to the ordinal (left-to-right) position of the result column. This feature makes itpossible to define an ordering on the basis of a column that does not have a unique name. This is neverabsolutely necessary because it is always possible to assign a name to a result column using the ASclause.

It is also possible to use columns that do not appear in the SELECT result list. Thus the followingstatement is valid:

SELECT name FROM distributors ORDER BY code;

A limitation of this feature is that an ORDER BY clause applying to the result of a UNION clause mayspecify only an output column name or number, not an expression.

One may add the key word ASC (ascending) or DESC (descending) after any expression in the ORDERBY clause. If not specified, ASC is assumed by default.

DISTINCT Clause

If DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from eachgroup of duplicates). ALL specifies the opposite: all rows are kept; that is the default.

FETCH Clause

The FETCH clause consists of two independent sub-clauses:

FETCH [FIRST | NEXT count [ROW[S]] [ONLY] OFFSET start [ROW[S]]

count specifies the maximum number of rows to return, while start specifies the number of rows to skipbefore starting to return rows. When both are specified, start rows are skipped before starting to count thecount rows to be returned.

When using FETCH, it is a good idea to use an ORDER BY clause that constrains the result rows into aunique order. Otherwise you get an unpredictable subset of the query's rows. For example you intend toask for the tenth through twentieth rows, but without ORDER BY the order of the results is unpredictable.

FOR UPDATE Clause

The FOR UPDATE clause has the following form:

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update.This prevents them from being modified or deleted by other transactions until the current transaction ends.That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows areblocked until the current transaction ends.

Also, if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked aselected row or rows, SELECT FOR UPDATE waits for the other transaction to complete. When the othertransaction completes, the waiting transaction will proceed if the other transaction rolled back, otherwiseit will get an error message in the default isolation mode. In write-committed or read-committed isolationmodes, the waiting transaction will read the most recently committed version of the record and can proceedwith its update.

FOR UPDATE cannot be used in contexts, such as aggregation, where returned rows cannot be clearlyidentified with individual table rows.



Examples

Retrieve rows from system tableand arrange according to tablenamecolumn.

select tablename, field, datatype, length, scale from system.fields where schema ='SYSTEM' order by tablename;

Retrieve rows from aircraft tableand arrange according to tablenamecolumn.

select tablename, field, datatype, length, scale from system.fields where tablename = 'AIRCRAFT' order by tablename;

SETSet variables that define system behavior

Synopsis

SET {CONSISTENT_READ | WRITE_COMMITTED | READ_COMMITTED};

Description

Use the SET command to change run-time configuration parameters.


Support for setting additional run-time parameters is not yet implemented.

The isolation level of a transaction determines the data that the transaction can see when othertransactions are running concurrently. The setting is in effect with the start of the next transaction, andstays in effect until the level is reset with another SET command.

The following settings are supported:

CONSISTENT_READ (the default) Same as SERIALIZABLE.

At this level a transaction that tries to update ordelete a record that was updated by a concurrenttransaction waits for the concurrent transaction toend.

If the concurrent transaction fails, NuoDBautomatically retries the update for the waitingtransaction, and, in the absence of other conflicts,succeeds.

If the concurrent transaction is committed, NuoDBgenerates an update conflict error for the waitingtransaction. The waiting transaction can then eitherroll back and retry its operations, or continue. If itcontinues and retries the change of the conflictingrecord NuoDB generates an error.

WRITE_COMMITTED At this level every update or delete statementis preceded by a SELECT..FOR UPDATE thatoperates differently from an ordinary SELECT.

Ordinarily, a SELECT returns the most recentversion of the record that was committedwhen the transaction started. If you setWRITE_COMMITTED, a SELECT..FOR UPDATE



returns the most recent version of the record andcreates a new version without changing any values.

If the most recent version of the record is not yetcommitted, the SELECT..FOR UPDATE waits untilthe transaction that created it finishes, then returnsthe most recent committed version.

Note: At the level ofWRITE_COMMITTED, NuoDB operatesin both READ_COMMITTED as wellas WRITE_COMMITTED. If youwant only READ_COMMITTED, usethe READ_COMMITTED instead ofWRITE_COMMITTED.

READ_COMMITTED At this level NuoDB allows a running transaction tosee the most recently committed version of eachrecord, regardless of whether that record was visible at the time the transaction started. As aresult, running the same query twice can producedifferent results.

Note: With one exception the behavior ofNuoDB at the READ_COMMITTED levelis like READ_COMMITTED isolation inother relational databases. The exceptionis the handling of conflicts betweentransactions that update or delete thesame record. A NuoDB transaction thatattempts to update or delete a record thatwas updated by a concurrent transactiongenerates an update conflict error, just asif the isolation level were set to the default(CONSISTENT_READ).

SHOWDisplay information about schemas, tables, domains, and status of AUTOCOMMIT isolation level.

Synopsis

SHOW [ SCHEMAS | TABLES |DOMAINS | SEQUENCES | AUTOCOMMIT ]SHOW nameSHOW TABLE [schema.]nameSHOW DOMAIN [schema.]nameSHOW SEQUENCE [schema.]nameSHOW SCHEMA name

Parameters and Options

Name What IsDisplayed

Example

SHOW Current settingsSQL> show autocommit state is off semicolon completion is required



Name What IsDisplayed

Example

current schema is ENGINEERS

SHOW SCHEMAS All schemascontaining tables SQL> show schemas;

Schemas containing tables

ENGINEERS HOCKEY SYSTEM

SHOW TABLES Tables in currentschema SQL> show tables

Tables in schema ENGINEERS

ENGINEERS

SHOW DOMAINS Domains in currentschema SQL> SHOW domains

No domains found in schema ENGINEERS

SHOWSEQUENCES

Sequences incurrent schema

SHOWAUTOCOMMIT

Status ofAUTOCOMMITisolation level (offor on)l

SQL> show AUTOCOMMIT Autocommit is off

SHOW name

SHOW TABLE[schema.]name

Schema andcolumns of thetable specified

SQL> show table hockey;

Schema HOCKEY Table HOCKEY

NUMBER integer NAME string POSITION string TEAM string

SHOW DOMAIN[schema.]name

Specified domain

SHOWSEQUENCE[schema.]name

Specifiedsequence

SHOW SCHEMAname

Specified schema

Description

Use SHOW to get information about schemas, tables, sequences, and domains. You can also use SHOWto determine the status of the setting for AUTOCOMMIT.



By default SHOW uses the current schema if none is specified.

If NuoDB cannot find a table with the exact name that is specified it performs as follows:

1. Searches for tables with similar names in the current schema, and then in all schemas.2. Searches for tables that match the first few characters of the specified name, in the current schema,

and then in all schemas.3. Generates a list of tables with same initial letter as the specified name.

Note: Keep in mind that an SQL domain is not the same as a NuoDB administrative domain. AnSQL domain is a set of types that are named and can be used as a group as if they are one type.

START TRANSACTIONStart a transaction block

Synopsis

START TRANSACTION { [ READ ONLY ] [ READ WRITE ] [ ISOLATION LEVEL { READ COMMITTED | WRITE COMMITTED | SERIALIZABLE }] }


REPEATABLE READ is not implemented.

READ UNCOMMITTED is not implemented.

Parameters

None.

Description

If the first statement you submit is START TRANSACTION, NuoDB starts a transaction using the specifiedisolation level.

If you execute a select, insert, update or other command and there is no transaction running, NuoDB startsa transaction as READ WRITE SERIALIZABLE.

Example

Start a transaction as read only, do an update, andthe transaction fails. Then start a transaction asread write and the update succeeds.

SQL> start transaction;SQL> use foo;SQL> create table t1 (n1 integer);SQL> insert into t1 (n1) values (1), (2), (3);SQL> select * from t1;

N1 ---



1 2 3

SQL> commit;SQL> start transaction read only;SQL> update t1 set n1=n1+10;Read only transactions cannot change dataSQL> commit;SQL> start transaction read write;SQL> update t1 set n1=n1+10;SQL> select * from t1;

N1 ---

11 12 13


TRUNCATEEmpty a table

Synopsis

TRUNCATE [ TABLE ] name

Parameters

name The name (optionally schema-qualified) of the tableto be truncated.

Description

TRUNCATE quickly removes all rows from a table. It has the same effect as an unqualified DELETE butsince it does not actually scan the table it is faster. This can be useful on large tables.

Although TRUNCATE can be executed inside a transaction block, there is no way to roll it back.

Examples

Truncate the table bigtable TRUNCATE TABLE bigtable;

UPDATEUpdate rows of a table

Synopsis

UPDATE [schema.] table SET column = expression [, ...] [WHERE condition ]



Parameters


column The name of a column in table.

expression A valid expression or value to assign to column.

condition Refer to the SELECT statement for a furtherdescription of the WHERE clause.

Description

UPDATE changes the values of the columns specified for all rows that meet the requirements specified bythe WHERE condition clause. You are required only to specify the columns to be modified.

Columns not specified by SET remain unchanged.

You must have write access to the table in order to modify it, as well as read access to any table whosevalues are specified in the WHERE condition.

Examples

Establish a ticket number for aparticular seat on a particularflight

update Seats set ticket_id = 346 where flight_id = 844 and seat_number = 24

UPGRADEChange or create tables and indexes. (NuoDB extension)

Synopsis

UPGRADE TABLE [schema_name.]table_name ( { column_name { data_type | domain } [ DEFAULT default_expr ] [ column_constraint [, ... ] ] | table_constraint } [, ... ] )

UPGRADE [UNIQUE ] INDEX index_name ON table ( column [, ...] )

Description

Use UPGRADE TABLE to change an existing table or, if the named table does not exist, create a newtable. See the syntax for CREATE TABLE for more details. UPGRADE TABLE does not drop existingfields.

You can use UPGRADE on a single table.



Similarly use UPGRADE INDEX to change an existing index or create a new one. You can also convert anindex from non-unique to unique, but you cannot make a unique index non-unique. It will add columns toan index.

UPGRADE is a NuoDB extension to the SQL standard.

Examples

Create a table, pet, usingUPGRADE, and insert values.

Create an index based on theage column for the pets table.

SQL> upgrade table pets (name string, age number, weight integer);SQL> show tables; Tables in schema AWH

PETSSQL> insert into pets (name,age, weight) values ('Amanda',6,26);SQL> select * from pets;

NAME AGE WEIGHT ------ ---- -------

Amanda 6 26

SQL> upgrade index petAge on pets(age);SQL> insert into pets (name, age, weight) values ('Wryly',10,40);SQL> select * from pets;

NAME AGE WEIGHT ------ ---- -------

Amanda 6 26 Wryly 10 40

SQL> show table pets;

Schema AWH Table PETS

NAME string AGE number WEIGHT integer

Index: PETAGE AGE

USESpecify a schema as the default for the current connection to NuoDB SQL

Synopsis

USE name_of_schema

NOTE TO BETA USERS

NuoDB does not generate an error if you specify a schema or database that does not exist.



Description

A NuoDB database has one or more schemas. Each table, view, domain, index, and sequence is part ofa particular schema. While you are using the default schema specified by the USE command, you canreference these objects with an unqualified name.

Use a qualified name to reference entities in schemas other than the default.

Examples

Make hockey the defaultschema.

SQL> use hockey;SQL> select number from hockey where number >40;

NUMBER -------

46 63 49 67 91 43 55 47 54 44

Use common schema insteadof the default.

SQL> use common;SQL> select number from hockey where number >40;can't find table "HOCKEY"SQL> select number from hockey.hockey where number >40;

NUMBER -------

46 63 49 67 91 43 55 47 54 44

| Glossary | 128


Glossary

This glossary defines terms as used in the context of NuoDB.agent

Part of the NuoDB management layer. Running an agent on a host makes it available as a NuoDB host.

The agent starts and stops the NuoDB processes and communicates with the broker about the state of thesystem. Every host must have an agent and every chorus must have at least one broker. An agent canalso run as a broker if necessary.

archiveProvides durability of the data handled by the storage manager, for example by writing to disk. Holds acopy of the state of a NuoDB database at any given moment.

brokerPart of the NuoDB management layer.

Manages access and control of transaction engines and storage managers, independent of any particulardatabase (or chorus) in a NuoDB system.

chorus

A set of processes that at any one moment are collaborating on a database.

A chorus is a variable number of processes running on a variable number of computers that serve a singledatabase. Transaction engines and storage managers are part of the chorus. A chorus can span domains.For example, half a chorus could run on Amazon and half on machines at the customer site, each withseparate administration of the physical machines.

consolePart of the NuoDB management layer. Can find and manage NuoDB components that are known tobrokers.

The purpose of the console is to help you manage databases and choruses in a NuoDB system. Forexample, from the console you can start and stop NuoDB processes across all instances of NuoDB.

database objectAny defined object in a database that is used to store or reference data. Some examples of databaseobjects include tables, views, sequences, and indexes.

DBA (database administrator) accountThe NuoDB DBA username and password are required to create new users and privileges in NuoDB SQL.

domain (NuoDB)

A collection of NuoDB hosts that have been provisioned to work together to support NuoDB processes.You provision a host to run NuoDB by starting an agent. At that point the host is part of a domain,regardless of whether there are any choruses running.

domain (NuoDB SQL)A kind of user-defined field type defined by NuoDB SQL For example, define a domain named money asnumeric(15,2). You can define fields as being of type money:

create table employee (id integer, name string, salary money);

launch a chorusTo launch a chorus you must start a broker, a transaction engine, and a storage manager. Passwords arerequired for each of these NuoDB processes.

Multiple Active Result Sets (MARS)Allows you to run more than one NuoDB SQL batch on an open connection at the same time. A result setis active until it is either closed or the statement that created it is closed.

nuodb

| Glossary | 129


Part of the NuoDB chorus layer. Process that runs on each NuoDB host.NuoDB SQL

ACID, transactional, and elastically scalable client/cloud relational database.

NuoDB has chorus components, management components, and standalone components.

NuoDB LoaderLoads data to and from NuoDB

NuoDB SQLSet of SQL language elements supported by NuoDB

storage managerProcess that maintains the archive for a chorus. There can be any number of storage managers running ina NuoDB domain. Each storage manager is associated with exactly one database.

transaction engineProcess that provides access to a single database. It handles requests from clients, caches data, andcoordinates transactions.

There may be any number of transaction engines running in a NuoDB chorus. Each transaction engine isassociated with exactly one database.

| Glossary | 130


Introduction To Flights Demo

Guides you through the steps to run a sample application against a NuoDB database system.

The application simulates a flight reservation system; it includes simulated flight data among airports andgenerates client reservations. Some of the actions you can perform with the tutorial:

• Vary the amount of data in the database.• Dynamically change load on the database (reads and writes) to see the effect on the transactions per

second (TPS) handled by NuoDB. • Add transaction engines to your database chorus to increase the transactions per second.

Start an agent and a chorusGetting started with the Flights program.

These commands start a NuoDB storage manager and then a transaction engine for a chorus namedFlights. (These steps must be in this exact order).

1. Make sure you have installed and configured NuoDB on your system.

2. Choose a host to run the application.

3. On the host, on the command line, start a nuoagent running as a broker as well as an agent, as follows:


4. Create an empty directory anywhere, to use as storage, such as (on MAC OS X)

mkdir ./FlightsArchive

5. Start up two additional terminal windows or command prompts.

6. Start up each of the following processes at separate command prompts or terminal windows:

nuodb –-chorus Flights --password chorus_password --archive /temp/FlightsArchive --initialize &

nuodb –-chorus Flights --password chorus_password &

Install the Flights applicationHow to start the Flights application.

Make sure you have set up NuoDB correctly.

1. Start the Flights program, which is a Java application, according to your platform requirements:

On Windowscd c:\Program Files\NuoDB\samples\flightsjava –jar flights.jar&

On Linux and Mac OS X:cd /install_dir/nuodb/samples/flightsjava –jar flights.jar &

| Introduction To Flights Demo | 132


The NuoDB Load Driver Dashboard opens as shown:

The NuoDB Load Driver Dashboard has the following functions:

• Loads data from a CSV file into a database• Generates client threads that read and change the data• Provides a graphic interface that controls the application and reports its performance.

2. Click the Settings tab



The following dialog appears:

3. If necessary, change the database URL to specify the Database URL as follows:

jdbc.com.nuodb://localhost/Flights

4. Make sure the Schema is specified as Flights

The correct settings for the Flight application are as shown:



5. Click Save.The application saves all its current settings in your home directory in an XML file calledsession.dashboard. (To begin the tutorial from a blank slate, delete this file.)

Load the Flights database and start the applicationHow to load the database for the Flights chorus and start the application

Make sure you have the Flights Load Driver Dashboard application running.

1. Select the Data tab on the Load Driver Dashboard.

The Flights database is loaded with the following data:

• Airports (hubs and connections• Airlines that fly between the airports selected• Specific flights• Seats on the flights



Note:

Before you run the Flights application you must specify a number of hub airports. Based onwhat you specify, the data about those airports and every airport they connect is stored in thedatabase. Selecting a large hub like Chicago or Atlanta generates a large database. Selectingan obscure hub like Adak Island generates a much smaller database.

Also, when you first open the Data dialog, the left-hand column has one airport name (AdakIsland in Alaska) with a check mark. Do not deselect Adak. This would mean there are no hubs.If there are no hubs there are no connections, and you cannot enter any data.

If there are no connections, exit the application, and restart the flights program without initializingthe archive.

The right hand column has one airport listed (Anchorage), which is the only destination available fromAdak Island. The fields in the center of the window show the number of hubs and connections, and thenumber of airlines, flights, and seats that can be booked on those connections in one week.

2. Select an airport in the Connections column to transfer it to the Hubs column.

3. Use the Data dialog to specify how much data to use.Select Anchorage in the Connections list.Anchorage appears in the Hubs list, along with Adak, and more choices appear in the Connectionslist. The connections list has changed to include all the airports accessible from Adak and Anchorage.(More hubs typically result in more connections and flights.)

4. Select just one or two small hubs (ADC and ANC) to minimize load time required.

(For example if you choose Boston and Chicago, which are large hubs, loading all the data can takeseveral minutes.)

5. Click the Run tab.

6. Click Load.

A message on the status line at the bottom of the dialog shows that the database has been loaded.

(Data comes from the CSV files in flights dir>\src\main\resources\data.)

7. After the database is loaded, click Start to run the Flights program.

Understanding the Flights applicationWhen you start the Flights application the dashboard shows information about transactions and lets youcontrol and balance the transaction processes.



While the application runs, one or more Flights clients send many transactions to the Flights database.The dashboard provides sliders that you can use to control and balance the following factors that affectperformance (transactions per second (TPS):

• Number of client threads• Percent of transactions that change the database (updates and inserts as distinguished from reads and

writes)• Frequency at which clients connect to the chorus.

As you adjust these factors, using the sliders on the dashboard, a real-time display of TPS shows the effectof the adjustments.

At this point, a single Flights client is making queries to the Flights database at a certain rate, which isshown as transactions per second (TPS) in the window. With the first slider (number of client threads) setto 1, there is only one client operating against the database. At the time of this screen capture the TPS is1,127 with a target of 10,000. As shown, the target number of client threads is 1, the percent update is 0,and the reconnect interval is also 0.

Shortly after the application starts running it generates a message similar to the following in the terminalwindow where you started the program:

Next flight leaves at 16:20 in 178 minutes

Flights Application DetailsDefinitions of controls and statistics in the Flights application.

Sliders

Number of client threads allows you to control the number of concurrent clients interacting with thedatabase.

Percent Update defines the percentage of transactions that actually change the database (as opposed tojust reading) during the running phase of the application (next section).

Reconnect Interval allows you to specify the frequency (in milliseconds) at which clients reconnect toNuoDB hosts. This is important when adding additional transaction engines or storage managers, to forcenew clients to connect to new hosts.

Database statistics

You can graphically display up to four database statistics simultaneously in the center sub-window. Eachgraphic has a different color.

· Database Transactions per Second (TPS ) – total and local.

o Local TPS shows the transaction rate of the local client for all threads.

o Total TPS represents all database transactions, across all clients ( See more about this in the sectionon bookers).

· Latency at Queue represents the round-trip time for a transaction to return from the server.

· Latency at Client represents the average time a request is forced to wait on the client – in its ownqueue.

Buttons for application operation

Load loads the database with all the flight data as specified in the Data window. During this phase, allbuttons are disabled (grayed out).

Start initiates the simulation of searches against (and possibly updates to) the Flights database.



Pause halts all transactions against the database. A subsequent Start will continue the transactions.

Benchmark allows you to create a file with benchmark data. See Benchmark section for more.

NOTE

Reset is not working at this time. Do not select this button or you may have to restart the dashboard.

Optimize Performance of Flights ApplicationYou can use the UI controls on the Flights dashboard to experiment with factors that can optimizeperformance of the application.

1. While the Flights application is running, observe that the TIPS is about 1,277 with one client thread.

2. Change the setting for the number of client threads from the default (1) to 4:

3. Observe a change in the TPS as four clients generate transactions. With four clients, the TPS increasesto about 2200 TPS.

4. Experiment with the slider and see how many clients maximize the TPS. At some point (100 in thisexample), you see a reduction in TPS and a corresponding increase in server latency (latency atqueue) and client latencyAt this setting, performance of the application is at a maximum. The next step is to add more resources;a second NuoDB transaction engine running on another host is required.

Add transaction engines to Flights applicationAdding transaction engines on additional hosts directly reduces the latency of the client and server. TheNuoDB broker distributes load across the servers.

1. Find another host to add to the NuoDB system for Flights.

2. Make sure that NuoDB is installed on the new host.



3. Start a NuoDB transaction engine on the new host (if one is not already running).

Be sure to specify the host running the NuoDB broker (the first host you configured) as a peer:

java -jar nuoagent.jar --peer <hostname or ip address of peer system>

4. Make sure that your firewall does not block traffic on the private network.

(For example, on Windows, select Windows Firewall in the Control Panel. In the navigation pane,select Turn Windows Firewall on or off. Turn off Windows Firewall for the private network.)

5. Enable connection for Flights clients on the new host:

nuodb –chorus Flights

6. Move the slider labeled Reconnect Interval up from zero to a higher setting.

This setting determines the frequency (in milliseconds) at which the client asks the NuoDB broker for anew connection to a transaction engine.

NOTE

Currently NuoDB brokers distribute connections using a round-robin approach. Other approacheshave not yet been implemented.

7. Observe the TPS as shown on the dashboard.

As soon as the broker begins distributing transaction load across multiple transaction engines, the TPSdips briefly as the new server acquires data, then the TPS goes up and the latencies go down.

Add UpdatesBy default, Flights clients read flight information from the database, not performing updates. For thisactivity you use the Percent Update slider on the dashboard.

1. To add updates to the kinds of transactions performed by the application, slide the Percent Updateslider up to a percentage of 50%.

This setting specifies that up to 50% of all database read transactions may generate write transactions,as clients reserve seats on flights.

2. Observe that this increase in updates leads to transaction update conflict messages, such as thefollowing, as threads conflict over access to records:

SqlException: update conflict in table FLIGHTS.SEATS

Add Client by Configuring a Flight BookerYou can add a client to the Flights application by specifying flight bookers from the Flights dashboard.

At some point, the Flights application that generates client requests may be unable to generate enoughrequests to load all the NuoDB transaction engines that are available. At that point, you can increase loadon the database by running additional hosts of the Flights client generator (referred to as a flight booker).

Typically you configure an additional host to handle increasing the load. The following script listens forrequests on port 48005, and starts up a flight booker when it is called from the dashboard. It appears onthe dashboard as a tab labeled Bookers. You can integrate new flight bookers from the dashboard byspecifying the new booker and clicking Add.

Also, the flight bookers report their statistics to the Flights dashboard. The dashboard displays theinformation graphically, providing a clear performance picture across the NuoDB system.



NOTE

A flight booker is supported only on Linux as an xinetd service.

1. Put the following script in a file in /etc/xinetd.d on the host where you want to run the broker:

# default: off # description: This is the NuoDB Flights load driver client. service NuoDB-driver { type = UNLISTED port = 48005 socket_type = stream protocol = tcp wait = no user = cloud server = /usr/bin/java server_args = -cp /opt/nuodb/samples/flights/flights.jar com.nuodb.loaddriver.TransactionGenerator --agent disable = no }

2. Start the Flights Dashboard.

3. In the field labeled New Booker, enter the hostname or ip address of the host where the booker scriptis located.

4. Select Add to add the booker to the list of remote bookers.

5. Click Run

6. Click Start on the dashboard.All the specified bookers start up on their remote servers, passing them their configuration informationso they are able to operate against the NuoDB database.

7. Go to the Settings tab and click Save. The next time you bring up the dashboard, the bookers will be listed as part of the Flights reservationsystem.

NOTE

Viewing the TPS rate of remote bookers in the dashboard is not yet supported.

Benchmark RuntimeRun tests to determine the maximum TPS with specific settings.

1. Open the Flights dashboard if it is not already open.

2. Click Settings to set warm-up time and benchmark run time (both in seconds).

3. Click Benchmark to run a time-bounded test with the current settings and report on the maximum TPSattained.

The Flights database loads (if not already loaded), warms up, and benchmark is launched.

At the end of the benchmark, the results are posted in the status bar at the bottom of the Run window.

TroubleshootingIf all of the buttons in the Run window are disabled, save your settings in the Settings window, exit thedashboard and restart the Flights application.



Restarting the tutorialHow to start the tutorial with all new data

1. Stop all NuoDB processes. (You can manually shut down the processes, or use the NuoDB Consoleto show all running servers in a chorus and shut them down.)

2. Remove the session.dashboard file.

3. Delete the archive directory for the database.

cd c:\temp\nuodbrm –r flights

4. Be sure to use the –initialize argument with the nuodb –archive command to create newstorage for the NuoDB system.

| Third Party Acknowledgments | 142


Third Party Acknowledgments

EditLine Library

Parts of NuoDB use the EditLine library that is:

Copyright (c) 1992, 1993 The Regents of the University of California. All rights reserved.

This code is derived from software contributed to Berkeley by Christos Zoulas of Cornell University.

THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND ANYEXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIEDWARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE AREDISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANYDIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ONANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDINGNEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

PostgreSQL

NuoDB SQL Reference documentation is partially based on PostgreSQL documentation.

PostgreSQL is released under the PostgreSQL License , a liberal Open Source license, similar to the BSDor MIT licenses.

PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95) PortionsCopyright (c) 1996-2010, The PostgreSQL Global Development Group Portions Copyright (c) 1994,The Regents of the University of California Permission to use, copy, modify, and distribute this softwareand its documentation for any purpose, without fee, and without a written agreement is hereby granted,provided that the above copyright notice and this paragraph and the following two paragraphs appear inall copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FORDIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOSTPROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IFTHE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING,BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR APARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, ANDTHE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

nuodb documentation

Documents