The following Operation Script is offered for Unix Shell to perform oerations 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 Script requires an H2® database and the H2® Java binary.

Download

Download: operate-h2.sh

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

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

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

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

...

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

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

# 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

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 H2 Console

Code Block

language	bash
title	Example for Invoking H2 ConsoleImporting
linenumbers	true

# invokingstep the1: H2shutdown Console for default port 8082
./operate-h2.sh consoleJOC Cockpit
/opt/sos-berlin.com/js7/joc/jetty/bin/jetty.sh stop

# invokingstep 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 directory2: (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 shellimport --url="/home/sos/h2/joc" --classpath=./h2-2.3.232.jar

...

"/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

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 linefile
./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;"

# execute SQL statement from filefile=/tmp/database-backup.zip

# backup database to .zip archive 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/database-backup.zip --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
.-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 export --url="/home/sos/h2/joc" --user=joc\
    --fileclasspath=/tmpvar/database.zip

# export database to .zip archive file with up to 3 versions
./operate-h2.sh exportsos-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/database.zip file=/var/sos-berlin.com/js7/joc/resources/joc/database-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 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 3: perform import, the database is automatically created
./operate-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 7

New Version 8

Key

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 the H2 Console

Invoking the H2 Shell

Running SQL Statements

Exporting and Importing

Switches

Exit Codes

Examples

Invoking H2 Console

Invoking H2 Shell

Backup and Restore

Exporting and Importing

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 7

New Version 8

Key

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 the H2 Console

Invoking the H2 Shell

Running SQL Statements

Exporting and Importing

Switches

Exit Codes

Examples

Invoking H2 Console

Invoking H2 Shell

Backup and Restore

Exporting and Importing