H2 Database Engine
Version 1.1.109 (2009-03-14)
1 of 147
Table of Contents
H2 Database Engine.............................................................................................................................................................1
Quickstart .........................................................................................................................................................................11
Embedding H2 in an Application ....................................................................................................................................11
The H2 Console Application ..........................................................................................................................................11
Step-by-Step ..........................................................................................................................................................11
Installation ........................................................................................................................................................11
Start the Console ...............................................................................................................................................11
Login ................................................................................................................................................................12
Sample .............................................................................................................................................................13
Execute .............................................................................................................................................................14
Disconnect ........................................................................................................................................................15
End ..................................................................................................................................................................15
Installation ........................................................................................................................................................................16
Requirements ..............................................................................................................................................................16
Supported Platforms .....................................................................................................................................................16
Installing the Software .................................................................................................................................................16
Directory Structure .......................................................................................................................................................16
Tutorial .............................................................................................................................................................................17
Starting and Using the H2 Console ................................................................................................................................17
Firewall ..................................................................................................................................................................17
Native Version ........................................................................................................................................................18
Testing Java ...........................................................................................................................................................18
Error Message 'Port is in use' ...................................................................................................................................18
Using another Port ..................................................................................................................................................18
Starting Successfully ...............................................................................................................................................18
Connecting to the Server using a Browser ................................................................................................................18
Multiple Concurrent Sessions ...................................................................................................................................18
Login .....................................................................................................................................................................19
Error Messages .......................................................................................................................................................19
Adding Database Drivers .........................................................................................................................................19
Using the Application ..............................................................................................................................................19
Inserting Table Names or Column Names .................................................................................................................19
Disconnecting and Stopping the Application ..............................................................................................................19
Settings of the H2 Console ...........................................................................................................................................19
Connecting to a Database using JDBC ...........................................................................................................................19
Creating New Databases ...............................................................................................................................................20
Using the Server ..........................................................................................................................................................20
Starting the Server from Command Line ...................................................................................................................20
Connecting to the TCP Server ..................................................................................................................................20
Starting the Server within an Application ..................................................................................................................20
Stopping a TCP Server from Another Process ............................................................................................................21
Using Hibernate ...........................................................................................................................................................21
Using TopLink and Glassfish .........................................................................................................................................21
Using Databases in Web Applications ............................................................................................................................21
Embedded Mode .....................................................................................................................................................22
Server Mode ...........................................................................................................................................................22
Using a Servlet Listener to Start and Stop a Database ...............................................................................................22
Using the H2 Console Servlet ...................................................................................................................................22
CSV (Comma Separated Values) Support .......................................................................................................................23
Writing a CSV File from Within a Database ...............................................................................................................23
Reading a CSV File from Within a Database ..............................................................................................................23
Writing a CSV File from a Java Application ................................................................................................................23
Reading a CSV File from a Java Application ..............................................................................................................23
Upgrade, Backup, and Restore ......................................................................................................................................24
Database Upgrade ..................................................................................................................................................24
Backup using the Script Tool ...................................................................................................................................24
Restore from a Script ..............................................................................................................................................24
Online Backup ........................................................................................................................................................24
Command Line Tools ....................................................................................................................................................25
Using OpenOffice Base .................................................................................................................................................25
Java Web Start / JNLP ..................................................................................................................................................26
Using a Connection Pool ...............................................................................................................................................26
Fulltext Search .............................................................................................................................................................26
Using the Native Full Text Search ............................................................................................................................26
Using the Lucene Fulltext Search .............................................................................................................................27
User-Defined Variables .................................................................................................................................................28
2 of 147
Date and Time .............................................................................................................................................................28
Using Spring ................................................................................................................................................................28
Features ...........................................................................................................................................................................29
Feature List .................................................................................................................................................................29
Main Features .........................................................................................................................................................29
Additional Features .................................................................................................................................................29
SQL Support ...........................................................................................................................................................30
Security Features ....................................................................................................................................................30
Other Features and Tools ........................................................................................................................................30
Limitations ...................................................................................................................................................................30
Comparison to Other Database Engines .........................................................................................................................30
Derby and HSQLDB .................................................................................................................................................31
DaffodilDb and One$Db ..........................................................................................................................................31
McKoi .....................................................................................................................................................................31
H2 in Use ....................................................................................................................................................................31
Connection Modes ........................................................................................................................................................31
Embedded Mode .....................................................................................................................................................32
Remote Mode .........................................................................................................................................................32
Mixed Mode ............................................................................................................................................................32
Database URL Overview ...............................................................................................................................................33
Connecting to an Embedded (Local) Database ...............................................................................................................34
Memory-Only Databases ...............................................................................................................................................34
Connecting to a Database with File Encryption ...............................................................................................................34
Database File Locking ...................................................................................................................................................34
Opening a Database Only if it Already Exists ..................................................................................................................35
Closing the Database ...................................................................................................................................................35
Delayed Database Closing .......................................................................................................................................35
Don't Close the Database when the VM Exits ............................................................................................................35
Log Index Changes ......................................................................................................................................................36
Ignore Unknown Settings .............................................................................................................................................36
Changing Other Settings when Opening a Connection ....................................................................................................36
Custom File Access Mode ..............................................................................................................................................36
Multiple Connections ....................................................................................................................................................37
Opening Multiple Databases at the Same Time .........................................................................................................37
Multiple Connections to the Same Database: Client/Server ........................................................................................37
Multithreading Support ............................................................................................................................................37
Locking, Lock-Timeout, Deadlocks ...........................................................................................................................37
Database File Layout ....................................................................................................................................................37
Moving and Renaming Database Files ......................................................................................................................38
Backup ...................................................................................................................................................................38
Logging and Recovery ..................................................................................................................................................38
Compatibility ................................................................................................................................................................39
Compatibility Modes ................................................................................................................................................39
DB2 Compatibility Mode ..........................................................................................................................................39
Derby Compatibility Mode ........................................................................................................................................39
HSQLDB Compatibility Mode ....................................................................................................................................39
MS SQL Server Compatibility Mode ..........................................................................................................................39
MySQL Compatibility Mode ......................................................................................................................................39
Oracle Compatibility Mode .......................................................................................................................................40
PostgreSQL Compatibility Mode ...............................................................................................................................40
Auto-Reconnect ...........................................................................................................................................................40
Automatic Mixed Mode .................................................................................................................................................40
Using the Trace Options ...............................................................................................................................................41
Trace Options .........................................................................................................................................................41
Setting the Maximum Size of the Trace File ..............................................................................................................41
Java Code Generation .............................................................................................................................................41
Using Other Logging APIs .............................................................................................................................................42
Read Only Databases ...................................................................................................................................................42
Read Only Databases in Zip or Jar File ...........................................................................................................................42
Graceful Handling of Low Disk Space Situations .............................................................................................................42
Opening a Corrupted Database ................................................................................................................................43
Computed Columns / Function Based Index ...................................................................................................................43
Multi-Dimensional Indexes ............................................................................................................................................43
Using Passwords ..........................................................................................................................................................43
Using Secure Passwords ..........................................................................................................................................43
Passwords: Using Char Arrays instead of Strings .......................................................................................................44
Passing the User Name and/or Password in the URL .................................................................................................44
User-Defined Functions and Stored Procedures ..............................................................................................................44
Function Data Type Mapping ...................................................................................................................................45
Functions that require a Connection .........................................................................................................................45
Functions throwing an Exception .............................................................................................................................45
3 of 147
Functions returning a Result Set ..............................................................................................................................45
Using SimpleResultSet .............................................................................................................................................45
Using a Function as a Table .....................................................................................................................................45
Triggers .......................................................................................................................................................................46
Compacting a Database ................................................................................................................................................46
Cache Settings .............................................................................................................................................................47
Performance .....................................................................................................................................................................48
Performance Comparison ..............................................................................................................................................48
Embedded ..............................................................................................................................................................48
Client-Server ..........................................................................................................................................................48
Benchmark Results and Comments ..........................................................................................................................49
H2 ....................................................................................................................................................................49
HSQLDB ............................................................................................................................................................49
Derby ...............................................................................................................................................................49
PostgreSQL .......................................................................................................................................................49
MySQL ..............................................................................................................................................................49
Firebird .............................................................................................................................................................50
Why Oracle / MS SQL Server / DB2 are Not Listed ...............................................................................................50
About this Benchmark .............................................................................................................................................50
Number of Connections ......................................................................................................................................50
Real-World Tests ...............................................................................................................................................50
Comparing Embedded with Server Databases ......................................................................................................50
Test Platform ....................................................................................................................................................50
Multiple Runs ....................................................................................................................................................50
Memory Usage ..................................................................................................................................................50
Delayed Operations ...........................................................................................................................................51
Transaction Commit / Durability .........................................................................................................................51
Using Prepared Statements ................................................................................................................................51
Currently Not Tested: Startup Time ....................................................................................................................51
PolePosition Benchmark ................................................................................................................................................51
Application Profiling ......................................................................................................................................................52
Analyze First ...........................................................................................................................................................52
Database Profiling ........................................................................................................................................................52
Database Performance Tuning ......................................................................................................................................53
Virus Scanners ........................................................................................................................................................53
Using the Trace Options ..........................................................................................................................................53
Index Usage ...........................................................................................................................................................53
Optimizer ...............................................................................................................................................................53
Expression Optimization ..........................................................................................................................................53
COUNT(*) Optimization ...........................................................................................................................................53
Updating Optimizer Statistics / Column Selectivity .....................................................................................................54
Optimization Examples ............................................................................................................................................54
Advanced Topics ...............................................................................................................................................................55
Result Sets ..................................................................................................................................................................55
Limiting the Number of Rows ..................................................................................................................................55
Large Result Sets and External Sorting .....................................................................................................................55
Large Objects ..............................................................................................................................................................55
Storing and Reading Large Objects ..........................................................................................................................55
Linked Tables ..............................................................................................................................................................56
Transaction Isolation ....................................................................................................................................................56
Table Level Locking ................................................................................................................................................56
Lock Timeout ..........................................................................................................................................................57
Multi-Version Concurrency Control (MVCC) ....................................................................................................................57
Clustering / High Availability .........................................................................................................................................57
Using the CreateCluster Tool ...................................................................................................................................57
Clustering Algorithm and Limitations ........................................................................................................................58
Two Phase Commit ......................................................................................................................................................58
Compatibility ................................................................................................................................................................58
Transaction Commit when Autocommit is On ............................................................................................................59
Keywords / Reserved Words ....................................................................................................................................59
Standards Compliance ..................................................................................................................................................59
Run as Windows Service ...............................................................................................................................................59
Install the Service ...................................................................................................................................................59
Start the Service .....................................................................................................................................................59
Connect to the H2 Console ......................................................................................................................................59
Stop the Service .....................................................................................................................................................59
Uninstall the Service ...............................................................................................................................................60
ODBC Driver ................................................................................................................................................................60
ODBC Installation ...................................................................................................................................................60
Starting the Server ..................................................................................................................................................60
ODBC Configuration ................................................................................................................................................60
4 of 147
PG Protocol Support Limitations ...............................................................................................................................61
Security Considerations ...........................................................................................................................................61
Using H2 in Microsoft .NET ...........................................................................................................................................61
Using the ADO.NET API on .NET ..............................................................................................................................61
Using the JDBC API on .NET ....................................................................................................................................61
ACID ...........................................................................................................................................................................62
Atomicity ................................................................................................................................................................62
Consistency ............................................................................................................................................................62
Isolation .................................................................................................................................................................62
Durability ...............................................................................................................................................................62
Durability Problems ......................................................................................................................................................62
Ways to (Not) Achieve Durability .............................................................................................................................62
Running the Durability Test .....................................................................................................................................63
Using the Recover Tool ................................................................................................................................................63
File Locking Protocols ...................................................................................................................................................64
File Locking Method 'File' .........................................................................................................................................64
File Locking Method 'Socket' ....................................................................................................................................64
Protection against SQL Injection ...................................................................................................................................65
What is SQL Injection .............................................................................................................................................65
Disabling Literals ....................................................................................................................................................65
Using Constants ......................................................................................................................................................65
Using the ZERO() Function ......................................................................................................................................66
Restricting Class Loading and Usage .............................................................................................................................66
Security Protocols ........................................................................................................................................................66
User Password Encryption .......................................................................................................................................66
File Encryption ........................................................................................................................................................67
Wrong Password Delay ...........................................................................................................................................67
HTTPS Connections .................................................................................................................................................67
SSL/TLS Connections ....................................................................................................................................................67
Universally Unique Identifiers (UUID) ............................................................................................................................68
Settings Read from System Properties ...........................................................................................................................68
Setting the Server Bind Address ....................................................................................................................................68
Limitations ...................................................................................................................................................................68
Glossary and Links .......................................................................................................................................................69
SQL Grammar....................................................................................................................................................................70
Commands (Data Manipulation).....................................................................................................................................70
Commands (Data Definition)..........................................................................................................................................70
Commands (Other).......................................................................................................................................................71
Other Grammar.............................................................................................................................................................71
System Tables..............................................................................................................................................................72
SELECT...................................................................................................................................................................72
INSERT...................................................................................................................................................................72
UPDATE..................................................................................................................................................................73
DELETE...................................................................................................................................................................73
BACKUP..................................................................................................................................................................73
CALL.......................................................................................................................................................................73
EXPLAIN..................................................................................................................................................................73
MERGE....................................................................................................................................................................74
RUNSCRIPT.............................................................................................................................................................74
SCRIPT...................................................................................................................................................................74
SHOW.....................................................................................................................................................................74
ALTER INDEX RENAME.............................................................................................................................................75
ALTER SEQUENCE....................................................................................................................................................75
ALTER TABLE ADD...................................................................................................................................................75
ALTER TABLE ADD CONSTRAINT..............................................................................................................................75
ALTER TABLE ALTER COLUMN..................................................................................................................................75
ALTER TABLE ALTER COLUMN RENAME....................................................................................................................76
ALTER TABLE ALTER COLUMN RESTART...................................................................................................................76
ALTER TABLE ALTER COLUMN SELECTIVITY.............................................................................................................76
ALTER TABLE ALTER COLUMN SET DEFAULT............................................................................................................76
ALTER TABLE ALTER COLUMN SET NOT NULL...........................................................................................................76
ALTER TABLE ALTER COLUMN SET NULL..................................................................................................................77
ALTER TABLE DROP COLUMN...................................................................................................................................77
ALTER TABLE DROP CONSTRAINT............................................................................................................................77
ALTER TABLE SET....................................................................................................................................................77
ALTER TABLE RENAME.............................................................................................................................................77
ALTER USER ADMIN................................................................................................................................................78
ALTER USER RENAME..............................................................................................................................................78
ALTER USER SET PASSWORD...................................................................................................................................78
ALTER VIEW............................................................................................................................................................78
ANALYZE.................................................................................................................................................................78
5 of 147
