H2 Tools

H2-backup.md

Backup by dumping to/restoring from SQL

SCRIPT TO '/tmp/dump.sql';

Then connect to the new database, e.g. [1], then do:

RUNSCRIPT FROM '/tmp/dump.sql';

Notes

[1] E.g.:

rlwrap java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.Shell -url jdbc:h2:/tmp/newdb -user sa

H2-cli.md

Create Database

java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.RunScript -url jdbc:h2:/tmp/eolairedb -user sa -script eolaire-server/src/main/resources/eolaireService/sql/eolaire-schema.sql
java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.RunScript -url jdbc:h2:/tmp/eolairedb -user sa -script eolaire-server/src/main/resources/eolaireService/sql/eolaire-fixture.sql

Connect to Database

rlwrap java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.Shell -url jdbc:h2:/tmp/eolairedb -user sa

H2-cluster-demo.md

See also http://www.h2database.com/html/advanced.html#clustering

Create Directories

mkdir /tmp/hsrv1
mkdir /tmp/hsrv2

Start Cluster

Start first server:

java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.Server -tcp -tcpPort 9101 -baseDir /tmp/hsrv1

Start second server:

java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.Server -tcp -tcpPort 9102 -baseDir /tmp/hsrv2

Use CreateCluster Tool

java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.CreateCluster -urlSource jdbc:h2:tcp://localhost:9101/tmp/hsrv1/test -urlTarget jdbc:h2:tcp://localhost:9102/tmp/hsrv2/test -user sa -serverList localhost:9101,localhost:9102

Should exit after cluster will be created.

Connect as a Client

rlwrap java -cp ~/.m2/repository/com/h2database/h2/1.4.183/h2-1.4.183.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost:9101,localhost:9102/./test -user sa

Clustering Diagnostics SQL

SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CLUSTER';

Execute Sample SQL

CREATE TABLE employee (id INTEGER PRIMARY KEY, name VARCHAR(64), age INTEGER NOT NULL, manager_id INTEGER, CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id) REFERENCES employee (id));

INSERT INTO employee (id, name, age, manager_id) VALUES (5, 'ed', 37, NULL);
INSERT INTO employee (id, name, age, manager_id) VALUES (1, 'alice', 19, 5);
INSERT INTO employee (id, name, age, manager_id) VALUES (2, 'cavin', 41, 5);
INSERT INTO employee (id, name, age, manager_id) VALUES (3, 'bob', 25, 5);

H2-export-to-csv.md

call CSVWRITE('/tmp/my.csv', 'SELECT * FROM mytable');