...

• The JOC Cockpit uses a database to store inventory information, status information and workflow related logs. The database provides restart capabilities for the JS7 - Cluster Service. The Controller and Agent do not use database connections.
• JS7 does not include a DBMS. Instead, it uses a DBMS that is installed by the user - with the exception of H2®, an embeded version of which is included with JS7 for evaluation purposes.
• The supported DBMS products include:
  • Embedded H2®
  • MariaDB®
  • MySQL®
  • Oracle®
  • PostgreSQL®
  • SQL Server®
• Access to the DBMS is provided by the Hibernate database access layer. This includes use of a JDBC Driver that is specific for the DBMS.
• Also check and apply the measures described in the JS7 - Database Maintenance article.
• Caveat: Users of JS1 (1.x releases) should not reuse the JS1 database schema for JS7. Instead, JS7 should be installed with its own schema.

DBMS and JDBC Driver Versions

...

In most situations it is technically possible to operate JS7 with newer versions of a DBMS or JDBC Driver if the version is actively supported by the DBMS vendor. Users frequently ask if JS7 is compatible with newer versions of a DBMS or JDBC Driver. It's the wrong question and your DBA should know the answer. Consider the following example for Oracle® that similarly applies to other DBMSs:

...

The same applies to JDBC Drivers. There might be newer versions of JDBC Drivers that e.g. include bug-fixes. Do not ask SOS if JS7 is compatible to a specific JDBC Driver version. Ask your DBMS vendor if a newer version of the JDBC Driver is compatible to the one stated above. When picking a JDBC Driver then check compatibility with the Java version in use. For Java versions that can be used with JS7 see the Which Java versions is JobScheduler available for? article.

SOS performs >5000 regression tests when it comes to compatibility of Java, DBMS and JDBC Driver versions. The versions listed above are included in the SOS regression testing. If a specific version is not stated above then this doesn't mean that JS7 will not work with this version, it means that this version is not included with SOS regression testing. SOS supports the use of newer DBMS and JDBC Driver versions not stated above as long as users prove that they are operated in a compatibility mode that corresponds to one of the versions stated above.

...

• Hibernate configuration files are used in an XML format.
• JS7 - JOC Cockpit Installation On Premises
  • During installation the relevant hibernate configuration file is created and can be manually updated later on.
  • After installation of the JOC Cockpit the hibernate.cfg.xml Hibernate configuration file is available in
    • Unix
      • /var/sos-berlin.com/js7/joc/jetty_base/resources/joc
    • Windows
      • C:\ProgramData\sos-berlin.com\js7\joc\jetty_base\resources\joc
    • A different location for the configuration directory can be specified during installation of the JOC Cockpit.
• JS7 - JOC Cockpit Installation for Docker Containers
  • The JOC Cockpit ships preinstalled from a Docker® imagecontainer image.
  • It is therefore recommended that users:
    • download/copy one of the Hibernate configuration files listed below to:
      • /var/sos-berlin.com/js7/joc/jetty_base/resources/joc
    • adjust changes to reflect their database connection. For use with H2® you can use the below sample without changes.
• By default the Hikari Connection Pool is used.

Hibernate Configuration for Embedded H2®

Download: hibernate.cfg.xml-h2 (drop the -h2 extension when storing the file)

...

...

• Consider that XML requires special characters to be used with escape characters, for example a password in the Hibernate configuration file that includes the character & is written as &. This applies to any values of XML elements and attributes in Hibernate configuration files.
  • Wrong: <property name="hibernate.connection.password">sec&ret</property>
  • Right: <property name="hibernate.connection.password">sec&amp;ret</property>

Hibernate Configuration for Embedded H2®

Download: hibernate.cfg.xml-h2 (drop the -h2 extension when storing the file)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>driver_class">org.h2.Driver</property>
  <property name="hibernate.connection.driver_class">org.h2.Driver</property>
  <property name="hibernate.connection.password"/>
  <property name="hibernate.connection.url">jdbc:h2:./resources/joc/h2/joc</property>
  <property name="hibernate.connection.username">joc</property>
  <property name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

...

• The JDBC connection URL makes use of the embedded H2® database server. The database file is will be located in /var/sos-berlin.com/js7/joc/resources/joc/h2.
• You can store the above configuration without further changes in the /var/sos-berlin.com/js7/joc/jetty_base/resources/joc directory.
• Note that for a standalone H2® database server a syntax such as jdbc:h2:tcp://<host>:<port>/jobscheduler can be used where <host> is the hostname and <port> is the H2® database port (frequently: 1521).

...

• The example uses the MySQL® database server running with hostname mysqlsrv and port 3306.

Hibernate Configuration for Oracle®

Download: hibernate.cfg.xml-oracle (drop the -oracleextension when storing the file)

• The example applies to use of a MySQL® JDBC Driver. Such drivers do not ship with JS7 as they are not available from open source compatible license. Users can download such drivers from the vendor's site.
• For use with MySQL® databases the MariaDB® JDBC Driver can be used that ships with JS7. The class name for this driver is org.mariadb.jdbc.Driver, see example for MariaDB®.

Hibernate Configuration for Oracle®

Download: hibernate.cfg.xml-oracle (drop the -oracleextension when storing the file)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@//oraclesrv:1521/xe</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
  <property name="hibernate.hikari.connectionInitSql">ALTER SESSION SET NLS_COMP='LINGUISTIC'</property>
 </session-factory>
</hibernate-configuration>

...

• The example uses the Oracle® database listener running with hostname oraclesrv and port 1521. The database Service Name is xe.
• If you want to use a Service ID instead of a Service Name, then use the URL syntax: jdbc:oracle:thin:@oraclesrv:1521:xe
• If you want to directly specify additional settings typically used from tnsnames.ora, then use the URL syntax: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=oraclesrv)(PORT=1521)))(CONNECT_DATA=(SID=XE)(GLOBAL_NAME=XE.WORLD)(SERVER=DEDICATED)))
• The NLS_COMP setting is specified for use with UTF databases.
• If you want to use Oracle Wallet®, then specify empty values for the <property name="hibernate.connection.username"> and <property name="hibernate.connection.password"> elements. You will find further information in the How to connect to an Oracle database without using passwords article.

  
  

• The JDBC Driver URL for Oracle^® RAC databases can be added like this:

  Code Block
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=tst-db1.mycoexample.com)(PORT=1604)) (ADDRESS=(PROTOCOL=TCP)(HOST=tst-db2.mycoexample.com)(PORT=1604))) (CONNECT_DATA=(SERVICE_NAME=mydb1.mycoexample.com)(SERVER=DEDICATED)))

...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:sqlserver://sqlserversrv:1433;sendStringParametersAsUnicode=falsetrue;selectMethod=cursor;databaseName=jobscheduler</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2005Dialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

...

• Note that the JDBC Driver for SQL Server® has to be downloaded separately as it does not ship with JS7.
• The example uses the SQL Server® database server running with hostname sqlserversrv and port 1433.
• Note that use of the above parameters with the JDBC URL is required: sendStringParametersAsUnicode=falsetrue;selectMethod=cursor
• For Windows environments, access to SQL Server can be configured to use integrated security, i.e. using the account that the JOC Cockpit Windows Service is operated with. 
  • Specify empty values for the <property name="hibernate.connection.username"> and <property name="hibernate.connection.password"> elements.
  • Add to the JDBC URL: integratedSecurity=true
  • The account used for the JOC Cockpit Windows Service has to be configured with SQL Server® to allow access to the database.
  • The JDBC Driver distribution usually ships with a library for authentication purposes with a name such as sql_jdbc.dll or  mssql-jdbc_auth-9.2.1.x64.dll or similar. This library should be added to a location that is specified with the Windows PATH environment variable for the JOC Cockpit Windows Service or simply stored in the C:\Windows\System32 directory or in the bin directory of the Java JDK/JRE in use.

...

The following sections provide some common examples that are neither complete and nor exhaustive. Database administrators might apply their own standards for setting up a database for JS7.

Setup for H2®

When using the JS7 - JOC Cockpit - Installation Using the Graphical Installer for Linux and Windows then the database will be created automatically.

As a prerequisite users have to download the H2 JDBC Driver, for example from http://www.h2database.com/html/download-archive.html:

• JDBC Driver version 1.4.200 is reported to work with JS7.
  • At the time of writing versions 2.* are reported not to work with JS7 due to Bug 578086 - IDENTITY function no longer supported in H2 database.
• The JDBC Driver .jar file, for example h2-1.4.200.jar
  • can be specified with the JOC Cockpit graphical installer,
  • can be added to an existing JOC Cockpit installation, see chapter Individual JDBC Driver Versions.
    • In this situation users can run the following commands
      • cd $JETTY_BASE | cd %JETTY_BASE%
      • ../install/joc_install_tables.sh | ..\joc_install_tables.cmd

    • It is required to navigate to the JETTY_BASE directory to run the above commands in order to have the database being created in ./resources/joc/h2.as specified by the H2® Hibernate configuration file.

H2® ships with a console application that by default is available for browsers from port 8082. It is not required to run the H2® console application, however, find the following information how to invoke the console application.

• JOC Cockpit has to be stopped as the embedded H2® database can be used by a single client only.
• The console application and the database are started by invoking the H2® .jar file like this:
  
  • cd $JETTY_BASE | cd %JETTY_BASE%
  • java -jar ./lib/ext/joc/h2-1.4.200.jar -webAllowOthers -tcpAllowOthers
• It is required to navigate to the JETTY_BASE directory to run the above commands in order to have the database being created in ./resources/joc/h2.as specified by the H2® Hibernate configuration file.
• The H2® console application should launch a browser window if executed from the local machine and otherwise will displays a URL to connect to the application.
• For login users should specify
  • the URL: jdbc:h2:./resources/joc/h2/joc as indicated from the above Hibernate configuration file
  • the user name: joc
  • an empty password.

Setup for MariaDB®/ MySQL®

...

# grant all permissions
GRANT ALL ON js7.* TO 'jobscheduler'@'%';

# alternatively grant individual permissions
GRANT CREATE, CREATE VIEW, DROP, ALTER, EXECUTE, SELECT, UPDATE, INSERT, DELETE on js7.* to 'jobscheduler'@'%';

...

For secure connections without use of passwords refer to the information in the JS7 - How to make JOC Cockpit connect to an Oracle database using Wallet® article.

Database

When creating the database consider using UTF based character encoding and a collating sequence that supports UTF - for example:

...

JS7 makes use of a connection pool to allow a larger number of JOC Cockpit user sessions to share database connections from the pool.

• By default a JOC Cockpit instance REST Web Service is configured to use up to 10  connections from the pool.  As As a rule of thumb 1 connection can be shared by approx. 3 simultaneous users.
  • Adjust this setting to the max. number of connections required for sharing between simultaneous users of the JOC Cockpit.
  • Note that connections will be disconnected and returned to the pool if they are not used for a longer time.
• Additional connections are used by JS7 Services.Consider  Consider the total number of connections allowed when setting up your JS7 database.
  • Some DBMS products by default limit the number of connections per user account and process. JOC Cockpit is running from a single process.
  • Oracle by default limits the connections according to the formula: (1.5 * number of processes) + 22
• When operating a JOC Cockpit cluster additional connections for the REST Web Service will be created per JOC Cockpit instance.

Hibernate allows one out of a number of Connection Pool implementations to be chosen.

• By default the Hikari Connection Pool is used with JS7.

  • The Hikari setting for the maximumPoolSize is included with the Hibernate configuration file as this determines the max. number of open database connections:

    Code Block
    language xml title Hikari Connection Pool recommended settings collapse true
    <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property> <property name="hibernate.hikari.maximumPoolSize">10</property>

  • Additional property elements can be used in a Hibernate configuration file to add further settings for Hikari to a Hibernate configuration fileto the Hikari connection pool.
• Alternatively e.g. the C3P0 Connection Pool can be used.

  • Use of C3P0 suggests a number of additional settings that better match use with JS7 than the default values:

    Code Block
    language xml title C3P0 Connection Pool recommended settings collapse true
    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> <property name="hibernate.c3p0.maxConnectionAge">14400</property> <property name="hibernate.c3p0.maxStatementsPerConnection">0</property> <property name="hibernate.c3p0.maxStatements">0</property> <property name="hibernate.c3p0.checkoutTimeout">0</property> <property name="hibernate.c3p0.maxIdleTime">14400</property> <property name="hibernate.c3p0.acquireRetryAttempts">1</property> <property name="hibernate.c3p0.minPoolSize">5</property> <property name="hibernate.c3p0.acquireIncrement">1</property> <property name="hibernate.c3p0.statementCacheNumDeferredCloseThreads">1</property> <property name="hibernate.c3p0.numHelperThreads">3</property> <property name="hibernate.c3p0.idleConnectionTestPeriod">1800</property> <property name="hibernate.c3p0.maxPoolSize">10</property> <property name="hibernate.c3p0.initialPoolSize">5</property>

  • Additional property elements can be used to add further settings. However, close reading of the C3P0 docs is recommended before applying changes.

...

When installing or updating the JOC Cockpit, you can specify the JDBC Driver that should be used by the installer, for details see:

• JS7 - JOC Cockpit - Headless Installation on Linux /and Windows
• JS7 - JOC Cockpit - Installation Using the Graphical Installer for Linux /and Windows Graphical Installer

The following sections explain how to add a JDBC Driver to an existing installation without updating the JOC Cockpit from the installer.

...

Consider applying changes to your Hibernate configuration file that reflect change of the JDBC Driver. You will find the hibernate.cfg.xm Hibernate xml Hibernate configuration file in the JOC Cockpit configuration directory:

...

Restart the JOC Cockpit to apply your changes.

Adding a JDBC Driver to a

...

Container installation

When using the JOC Cockpit Docker® image container image then technically no installer is included that allows to specify the JDBC Driver to be used. Instead, images are preinstalled. In addition, there is no access to the container's file system before running the container which makes it impossible to add or replace a JDBC Driver before JOC Cockpit is started.

...

• /var/sos-berlin.com/js7/joc/resources/joc/lib
• The /var/sos-berlin.com/js7/joc/resources/joc directory directory is typically mounted to a Docker volume a volume when running the container - for details see the JS7 - JOC Cockpit Installation for Docker Containers article. If the lib sub-directory does not exist then you can create it to store the JDBC Driver (*.jar) file.

...