JS7 JobScheduler

Space shortcuts

Page tree

Versions Compared

Old Version 8

changes.mady.by.user Andreas Püschel

Saved on

compared with

New Version Current

changes.mady.by.user Andreas Püschel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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 backup or to export the JOC Cockpit database at a regular basis.

Download

Download: operate-h2.sh

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

...

  • 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
      languagexml
      <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.

...

Code Block
titleUsage
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

Commands


    -z | --zip                 | assumes zip compressed file on export/import

Commands

  • console
    • Invokes
    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 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
    • 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 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 filebinary 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 specified 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 command commands, specifies the path to the .zip archive file that holds the full database backupexport.
    • When used with the import command, specifies the path to the .sql script 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 command 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.

...

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

...

The following examples illustrate typical use cases.

Invoking

...

H2 Console

Code Block
languagebash
titleExample for Invoking H2 Console
linenumberstrue
# 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
languagebash
titleExample for Invoking the H2 Shell
linenumberstrue
# 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

...

Code Block
languagebash
titleExample for Exporting
linenumberstrue
# 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 .zipsql archivetext 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.zipsql --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
languagebash
titleExample for Importing
linenumberstrue
 # 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 import database from .zip archive file to extract the resulting script.sql file
unzip -d /tmp 
./operate-h2.sh import --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/database-export.zip --zip

# step 43: perform import, thedatabase databasefrom is.sql automaticallytext createdfile
./operate-h2.sh import --url="/var/sos-berlin.com/js7/joc/resources/joc/h2/joc" --user=joc --file=/tmp/scriptdatabase-export.sql

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

...

Code Block
languagebash
titleExample for Backup
linenumberstrue
# backup database to .zip archive file that holds the database filefiles
./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

...

Code Block
languagebash
titleExample for Restore
linenumberstrue
# 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 receiverestore the resulting script.sql filedatabase files
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