Table of Contents

Introduction

The H2 Database Engine is a supported JS7 - Database. Management operations on H2® databases can be performed from the command line invoking the H2® Console or H2® Shell.

The following Managaement Operation Script is offered for Unix Shell to perform operations frequently used operations by JS7 users such as creating backups .

The script is available for Linux and MacOS® using bash shell. The script acts as a wrapper to classes provided by the H2® Java binary.
The script terminates with exit code 0 to signal successful execution, with exit code 1 for command line argument errors and other exit codes for non-recoverable errors.
The script is intended as a baseline example for customization by JS7 users and by SOS within the scope of professional services. Examples make use of H2® release 2.3.232.

Prerequisites

The Script requires an H2 database and the H2 Java binaryOperation Script can be executed from a JS7 job to backup or to export the JOC Cockpit database at a regular basis.

Download

Download: operate-h2.sh

Usage

Invoking the script without arguments displays the usage clause:

Code Block

title	Usage

Usage: h2.sh [Command] [Options] [Switches]

  Commands:
    console                    --classpath [--web-port]
    shell                      --classpath --url [--file|--sql] [--driver-class] [--user] [--password]
    export                     --classpath --url --file [--user] [--password] [--file-versions]
    import                     --classpath --url --file [--user] [--password]

  Options:
    --classpath=<path>         | required: JDBC Driver classpath, default: ./h2-2.3.232.jar
    --url=<url>                | optional: URL or path to database, default: jdbc:h2:./h2/joc
    --driver-class=<name>      | optional: JDBC Driver class, default: org.h2.Driver
    --user=<account>           | optional: database User, default: joc
    --password=<password>      | optional: database password
    --file=<path>              | optional: file for export/import and SQL statements
    --sql=<sql>                | optional: SQL statement
    --web-port=<port>          | optional: console web server port, default: 8082
    --file-versions=<number>   | optional: create versions of backup files on export, default: 1

  Switches:
    -h | --help                | displays usage
    -v | --verbose             | displays verbose output
    -p | --password            | asks for password

Commands

console
- Invokes the H2 Console for use with a browser.
- Users can specify the --web-port option and otherwise use default port 8082.
shell
- Invokes the H2 Shell for execution of SQL statements:
  - SQL statements can be specified using the --sql option.
  - Using the --file option a file can be specified holding SQL statements.
  - Without the --sql and --file options the interactive H2 Shell will be used.
export
- Exports the database specified to a .zip compressed .archive file.
- The archive file includes the script.sql file holding SQL statements that can be used to recreate the database.
- When using the --file-versions option, backup files will be stored in versions:
  - Each newly created backup file is appended the suffix -001.
  - An existing backup file with the suffix -001 is renamed to -002. A backup file with the suffix -002 is renamed to the suffix -003 etc.
  - When the number of versions specified with the option is exeeded then the earliest backup file is removed.
import
- Imports data from a previous backup. Backup files hold the full backup of the database. To restore a database it is recommended to (re)move the existing H2 database. The database will be recreated on import.
- The backup file created using the export command must be extracted to the resulting script.sql file.
- The path to the script.sql file is specified with the --file option.

Options

...

H2 ships from a single Java binary file, for example h2-2.3.232.jar.
The option specifies the path to the H2 Java binary file.

The Operation Script can be stored to any location with access to the H2® database files used by JOC Cockpit.

Prerequisites

H2 License

H2® ships with the Open Source licenses MPL 2.0 (Mozilla Public License Version 2.0)and EPL 1.0 (Eclipse Public License), for details see https://www.h2database.com/html/license.html.

H2 Database Files

The Operation Script requires access to the H2® database files used by JOC Cockpit.

The default location of H2® database files is: /var/sos-berlin.com/js7/joc/resources/joc/h2

H2 Java Binary File

H2® ships from a single Java binary file, for example h2-2.3.232.jar.

The H2® Java binary file has to be available for the Operation Script:

By default JS7 does not include an H2® Java binary file. Instead, users download H2® from https://www.h2database.com/.
- The Java binary file is specified as JDBC Driver when installing JOC Cockpit.
- The default location of the Java binary file after installation of JOC Cockpit is: /var/sos-berlin.com/js7/joc/lib/ext/joc
Users can hold a copy of the Java binary file in the directory in which the Operation Script is located.

H2 Database Connections

Should the Operation Script be executed while JOC Cockpit is active, then shared access to the H2® database is required.

By default JOC Cockpit starts the H2® database in embedded mode which does not allow shared access.
For shared access the H2® database can be configured for mixed mode which includes to add the AUTO_SERVER=TRUE parameter to the JDBC URL.
- By default JOC Cockpit makes use of the hibernate.cfg.xml Hibernate file in the /var/sos-berlin.com/js7/joc/resources/joc directory, for details see JS7 - Database.
- Users can add the AUTO_SERVER=TRUE parameter to the JDBC URL like this:
  Code Block
  language xml
  <property name="jakarta.persistence.jdbc.url">jdbc:h2:./resources/joc/h2/joc;MODE=LEGACY;AUTO_SERVER=TRUE</property>
- For shared access the AUTO_SERVER=TRUE parameter similarly has to be used for URLs specified by the Operation Script.
It is recommended to activate shared access as this allows to take online backups while JOC Cockpit is up and running.

Usage

Invoking the script without arguments displays the usage clause:

Code Block

title	Usage

Usage: operate-h2.sh [Command] [Options] [Switches]

  Commands:
    console                    --classpath [--web-port]
    shell                      --classpath --url [--file|--sql] [--driver-class] [--user] [--password]
    backup                     --classpath --url --file [--user] [--password] [--file-versions]
    export                     --classpath --url --file [--user] [--password] [--file-versions] [--zip]
    import                     --classpath --url --file [--user] [--password] [--zip]

  Options:
    --classpath=<path>         | required: JDBC Driver classpath, default: ./h2-2.3.232.jar
    --url=<url>                | optional: URL or path to database, default: jdbc:h2:./h2/joc
    --driver-class=<name>      | optional: JDBC Driver class, default: org.h2.Driver
    --user=<account>           | optional: database User, default: joc
    --password=<password>      | optional: database password
    --file=<path>              | optional: file for backup/export/import and SQL statements
    --file-versions=<number>   | optional: create versions of files for backup/export, default: 1
    --sql=<sql>                | optional: SQL statement
    --web-port=<port>          | optional: console web server port, default: 8082

  Switches:
    -h | --help                | displays usage
    -v | --verbose             | displays verbose output
    -p | --password            | asks for password
    -z | --zip                 | assumes zip compressed file on export/import

Commands

console
- Invokes the H2® Console for use with a browser.
- Users can specify the --web-port option and otherwise use default port 8082.
shell
- Invokes the H2® Shell for execution of SQL statements:
  - SQL statements can be specified using the --sql option.
  - Using the --file option a file can be specified holding SQL statements.
  - Without the --sql and --file options the interactive H2® Shell will be used.
backup
- Backup
  - When creating backups then a .zip archive is created that holds related database files.
  - Backups are transactionally consistent and represent a full backup of the JOC Cockpit database.
  - To perform online backups consider mixed mode for connections to the database using the AUTO_SERVER=TRUE parameter with the database URL.
- Restore
  - To restore a database it is replaced from the database files previously backed up.
  - To this purpose JOC Cockpit must be stopped and must be restarted after completion of the restore operation.
  - Restoring a database includes to extract the database files from a previoulsy created backup archive and to replace existing database files.
- When using the --file-versions option, backup files will be stored in versions:
  - Each newly created backup file is appended the suffix -001.
  - An existing backup file with the suffix -001 is renamed to -002. A backup file with the suffix -002 is renamed to the suffix -003 etc.
  - When the number of versions specified with the option is exeeded then the earliest backup file is removed.
- Backups are created to restore an H2® database for the same version of H2®. For migration of H2® databases to newer versions of the database engine use the export and import commands.
export
- Export to text file holding SQL statements:
  - By default the H2® database is exported to a text file that holds SQL statements which can be used to recreate the database.
- Export to .zip archive file:
  - If the --zip switch is used, exports the H2® database to a .zip compressed archive file.
  - The archive file includes the script.sql file holding SQL statements that can be used to recreate the database.
- When using the --file-versions option, export files will be stored in versions:
  - Each newly created export file is appended the suffix -001.
  - An existing export file with the suffix -001 is renamed to -002. An export file with the suffix -002 is renamed to the suffix -003 etc.
  - When the number of versions specified with the option is exeeded then the earliest export file is removed.
- Migration of H2® databases includes to export using the H2® Java binary file of the current version of the database engine. Import is performed using the newer version of the H2® Java binary file.
import
- Import of text files holding SQL statements:
  - By default a text file holding SQL statements is imported. Such files can be created using the export command.
- Import of .zip archive file:
  - If the --zip switch is used, imports data from a compressed .zip archive file. The archive file includes text files holding SQL statements. The archive file can be created from the export command using the --zip switch.
- If the import file was created using the export command, then it holds the full export of the database. To restore a database it is recommended to (re)move the existing H2® database. The database will be recreated on import.
- The path to the import file is specified with the --file option

Options

--classpath
- H2® ships from a single Java binary file, for example h2-2.3.232.jar.
- The option specifies the path to the H2® Java binary file, by default the current directory is used: ./h2-2.3.232.jar.
--url
- Specifies the location of the H2® database. The JS7 - Database using H2® is located with JOC Cockpit from the <jetty-base>/resources/joc/h2 directory. The name of the database is joc.
- The URL is prefixed with jdbc:h2: followed by the relative or absolute path to the database. If the prefix is omitted, it will be added by the script.
- Examples:
  - jdbc:h2:/var/sos-berlin.com/js7/joc/resources/joc/h2/joc
  - /var/sos-berlin.com/js7/joc/resources/joc/h2/joc
--driver
- Specifies the class name of the H2® JDBC Driver, default: org.h2.Driver.
--user
- Specifies the account for the H2® database, default: joc.
--password
- Specifies the password used for the account indicated with the --user option for the H2® database, default: no password.
- Password input from the command line is considered insecure. Consider use of the -p switch offering a secure option for interactive keyboard input.
--file
- When used with the backup and export commands, specifies the path to the .file that holds the full database export.
- When used with the import command, specifies the path to the file used for import. This offers to restore the database from a previous full export.
- When used with the shell command, specifies the path to the .sql script file that holds SQL statements that should be executed.
--file-versions
- When used with the backup and export commands, specifies the number of versions for which backup files will be stored
--sql
- When used with the shell command, specifies the SQL statement that should be executed.
- Consider to apply quotes as with --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;". SQL statements should be terminated by semicolon.
--web-port
- When used with the console command, specifies the port used by the H2® Console, default: 8082.

Switches

-h | --help
- Displays usage.
-v | --verbose
- Displays verbose log output that includes invocation of the H2® Java binary.
-p | --password
- Asks the user for interactive keyboard input of the password used for the account specified with the --user option..
- The switch is used for secure interactive input as an alternative to use of the option --password=<password>.
-z | --zip
- Creates/reads a compressed archive file when used with export and import commands.

Exit Codes

0: operation successful
1: argument errors

Examples

The following examples illustrate typical use cases.

Invoking H2 Console

Code Block

language	bash
title	Example for Invoking H2 Console
linenumbers	true

# invoking the H2 Console for default port 8082
./operate-h2.sh console

# invoking the H2 Console for the port specified using the Java binary from the current directory
./operate-h2.sh console --web-port=8081 --classpath=./h2-2.3.232.jar

Invoking H2 Shell

Code Block

language	bash
title	Example for Invoking the H2 Shell
linenumbers	true

# invoking the H2 Shell using the Java binary from the current directory
./operate-h2.sh shell --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --classpath=./h2-2.3.232.jar

Running SQL Statements

Code Block

language	bash
title	Example for Running SQL Statements
linenumbers	true

# execute SQL statement from the command line
./operate-h2.sh shell --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;"

# execute SQL statement from file
./operate-h2.sh shell --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/run.sql

Exporting and Importing

Code Block

language	bash
title	Example for Exporting
linenumbers	true

# export database to .zip archive file that holds the script.sql file
./operate-h2.sh export --url="

...

/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc

...

 --file=/tmp/database-export.zip --zip

# export database to .sql text file with up to 3 versions
./operate-h2.sh export --url="/var/sos-berlin.com/js7/joc/resources/joc

...

Specifies the class name of the H2 JDBC Driver, default: org.h2.Driver.

...

Specifies the account for the H2 database, default: joc.

...

Specifies the password used for the account specified with the --user option for the H2 database, default: no password.
Password input from the command line is considered insecure. Consider use of the -p switch offering a secure option for interactive keyboard input.

...

When used with the export command, specifies the path to the .zip archive file that holds the database backup.
When used with the import command, specifies the path to the .sql script file used to restore the database.
When used with the shell command, specifies the path to the .sql script file that holds SQL statements that should be executed.

...

When used with the shell command specifies the SQL statement that should be executed.
Consider to apply quotes as in --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;". SQL statements should be terminated by semicolon.

...

When used with the console command specifies the port used by the H2 Console, default: 8082.

...

When used with the export command, specifies the number of versions for which backup files will be stored

Switches

-h | --help
- Displays usage.
-v | --verbose
- Displays verbose log output that includes invocation of the H2 Java binary.
-p | --password
- Asks the user for interactive keyboard input of the password used for the account specified with the --user option..
- The switch is used for secure interactive input as an alternative to use of the option --password=<password>.

Exit Codes

0: operation successful
1: argument errors

Examples

The following examples illustrate typical use cases.

...

/h2/joc" --user=joc --file=/tmp/database-export.sql --file-versions=3

# export database from a docker container to a .zip archive stored on a volume
docker exec -ti js7-saas-2-7-2 /bin/bash \
    /opt/sos-berlin.com/js7/script/operate-h2.sh export \
    --classpath=/var/sos-berlin.com/js7/joc/lib/ext/joc/h2-2.3.232.jar \
    --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" \
    --user=joc \
    --file=/var/sos-berlin.com/js7/joc/resources/joc/database-export.zip \
    --file-versions=3 --zip

Code Block

language	bash
title	Example for Invoking H2 ConsoleImporting
linenumbers	true

 # invoking the H2 Console for default port 8082
./h2.sh console

# invoking the H2 Console for the port specified using the Java binary from the current directory
./h2.sh console --web-port=8081 --classpath=./h2-2.3.232.jar

Invoking H2 Shell

Code Block

language	bash
title	Example for Invoking the H2 Shell
linenumbers	true

# invoking the H2 Shell using the Java binary from the current directory
./h2.sh shell --url="/home/sos/h2/joc" --classpath=./h2-2.3.232.jar

...

 step 1: shutdown JOC Cockpit
/opt/sos-berlin.com/js7/joc/jetty/bin/jetty.sh stop

 # step 2: (re)move existing database
cd /var/sos-berlin.com/js7/joc/resources/joc
mv h2 h2.backup
mkdir h2

# step 3: import database from .zip archive file
./operate-h2.sh import --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/database-export.zip --zip

# step 3: import database from .sql text file
./operate-h2.sh import --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/database-export.sql

# step 4: start JOC Cockpit
/opt/sos-berlin.com/js7/joc/jetty/bin/jetty.sh start

Backup and Restore

Code Block

language	bash
title	Example for Running SQL StatementsBackup
linenumbers	true

# execute SQL statement from backup database to .zip archive file that holds the commanddatabase linefiles
./operate-h2.sh shellbackup --url="/homevar/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" --user=joc --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;"file=/tmp/database-backup.zip

# backup executedatabase SQLto statement.zip fromarchive file with up to 3 versions
./operate-h2.sh shellbackup --url="/homevar/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" --user=joc --file=/tmp/run.sql

Exporting and Importing

Code Block

language	bash
title	Example for Exporting
linenumbers	true

# export database to .zip archive file that holds the script.sql file
./h2.sh export --url="/home/sos/h2/joc" --user=joc --file=/tmp/database.zip

# export database to .zip archive file with up to 3 versions
./h2.sh exportdatabase-backup.zip --file-versions=3

# backup database from docker container to a directory mounted from a volume
docker exec -ti js7-saas-2-7-2 /bin/bash \
    /opt/sos-berlin.com/js7/script/operate-h2.sh backup \
    --classpath=/var/sos-berlin.com/js7/joc/lib/ext/joc/h2-2.3.232.jar \
    --url="/homevar/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" \
    --user=joc  \
    --file=/tmp/var/sos-berlin.com/js7/joc/resources/joc/database.zip-backup.zip \
    --file-versions=3

Code Block

language	bash
title	Example for ImportingRestore
linenumbers	true

# step 1: shutdown JOC Cockpit
/opt/sos-berlin.com/js7/joc/jetty/bin/jetty.sh stop

# step 2: (re)move existingthe database
mvcd /homevar/sos/h2 /home/sos/-berlin.com/js7/joc/resources/joc
mv h2 h2.backup
mkdir h2

# step 23: unzip .zip archive file to receiverestore the script.sql file database files
cd /var/sos-berlin.com/js7/joc/resources/joc/h2
unzip /tmp/database-backup.zip

# step 3: perform import, the database is automatically created
./h2.sh import --url="/home/sos/h2/joc" --user=joc --file=/tmp/script.sql

# step 4: start JOC Cockpit
/opt/sos-berlin.com/js7/joc/jetty/bin/jetty.sh start

Space shortcuts

Page tree

Versions Compared

Old Version 4

New Version Current

Key

Introduction

Prerequisites

Download

Usage

Commands

Options

Prerequisites

H2 License

H2 Database Files

H2 Java Binary File

H2 Database Connections

Usage

Commands

Options

Switches

Exit Codes

Examples

Invoking H2 Console

Invoking H2 Shell

Running SQL Statements

Exporting and Importing

Switches

Exit Codes

Examples

Invoking H2 Shell

Backup and Restore

Exporting and Importing

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 4

New Version Current

Key

Introduction

Prerequisites

Download

Usage

Commands

Options

Prerequisites

H2 License

H2 Database Files

H2 Java Binary File

H2 Database Connections

Usage

Commands

Options

Switches

Exit Codes

Examples

Invoking H2 Console

Invoking H2 Shell

Running SQL Statements

Exporting and Importing

Switches

Exit Codes

Examples

Invoking H2 Shell

Backup and Restore

Exporting and Importing