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 Operation Script is offered for Unix Shell to perform operations frequently used 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.
  

The Operation Script can be executed from a JS7 job to create database backups at a regular basis.

Download

Download: operate-h2.sh

The Operations Script can be stored to any location that has 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:
    • <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:

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]
    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 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

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
  
  • Exports an H2® database to a .zip compressed .archive file.
  • The export 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
  • Imports data from an export file that 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 export 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

• --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.
• --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 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.
• --file
  • 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.
• --file-versions
  • When used with the export command, 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>.

Exit Codes

• 0: operation successful
• 1: argument errors

Examples

The following examples illustrate typical use cases.

Invoking the H2 Console

# 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 the H2 Shell

# 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

# 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

# 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

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

# export database from a docker container to 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

# 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: unzip archive file to extract the resulting script.sql file
unzip -d /tmp /tmp/database-export.zip

# step 4: perform import, the database is automatically created
./operate-h2.sh import --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/script.sql

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

Backup and Restore

# backup database to .zip archive file that holds the database file
./operate-h2.sh backup --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" --user=joc --file=/tmp/database-backup.zip

# backup database to .zip archive file with up to 3 versions
./operate-h2.sh backup --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc;AUTO_SERVER=TRUE" --user=joc --file=/tmp/database-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="/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-backup.zip \
    --file-versions=3

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

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

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

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