Introduction

• The JOC Cockpit makes use of uses a database to store inventory information, status information and workflow related logs. The database is required for provides restart capabilities with for the JS7 - Cluster Service. The Controller and Agent do not make use of database connections.
• JS7 does not include a DBMS, instead. Instead, it make use of uses a DBMS that is installed by the user - with the exception of H2® for which JS7 includes an embedded version of the DBMS that ships , 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 to use of a JDBC Driver that is specific for the DBMS.
• Consider to Also check and to apply the measures for described in the JS7 - Database Maintenance article.

DBMS and JDBC Driver Versions

Operation of JS7 is supported for the below stated DBMS versions listed in the table below.

The JDBC Drivers for a number of DBMSs are included with JS7. However, due to restrictions for placed on bundling of JDBC Drivers with Open Source software for some DBMS products, users have to download the JDBC Driver from the vendor's web site themselves:

...

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

• Oracle® ships a newer version of their DBMS compatible to previous versions, e.g. the 19c release is compatible to 18c and down to 12c and 10g.
• Compatibility includes that your DBA identifies the technical compatibility switch in the DBMS to run the 19c version compatible to 18c, 12c etc. This means your DBA is in control of compatibility.
• SOS documents that JS7 is compatible to Oracle® 12c and 18c. This boils down to the fact that your DBA can run any newer version of the DBMS in a compatibility mode that supports 12c or 18c.
• The question if SOS will ship a JS7 release specifically for 19c would mean that the product is no longer compatible to earlier releases of the DBMS. This will not happen. The JS7 is not too demanding when it comes to the DBMS that , which is considered as a datastore only.

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 were 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 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. Versions stated The versions listed above are included with 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 does support 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 from in an XML format.
• JS7 - JOC Cockpit Installation On Premises
  • During installation the respective 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® image.
  • It is therefore recommended to that users:
    • download/copy one of the
    below
    • Hibernate configuration files listed below to:
      • /var/sos-berlin.com/js7/joc/jetty_base/resources/joc
    • Adjust adjust changes to reflect your their database connection. For use with H2® you can use the below sample without changes.
• By default the Hikari Connection Pool is used.

...

• The JDBC connection URL makes use of the embedded H2® database server. The database file is located in /var/sos-berlin.com/js7/joc/resources/joc/h2.
• You can store the above configuration without further changes to in the /var/sos-berlin.com/js7/joc/jetty_base/resources/joc directory.
• Note : that for use with a standalone H2® database server use 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).

...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">org.mariadb.jdbc.Driver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:mariadb://mariadbsrv:3306/jobscheduler</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</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>

Explanation:

• The example makes use of uses the MariaDB® database server running for with hostname mariadbsrv and port 3306.
• Consider Note that the InnoDB storage engine is used as the JS7 requires a transactional DBMS.

...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">org.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:mysql://mysqlsrv:3306/jobscheduler</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</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>

Explanation:

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

...

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

Explanation:

• The example makes use of uses the Oracle® database listener running for with hostname oraclesrv and port 1521. The database Service Name is xe.
• Should If you want to use a Service ID instead of a Service Name, then use this the URL syntax: jdbc:oracle:thin:@oraclesrv:1521:xe
• Should If you want to directly specify additional settings as typically used from tnsnames.ora, then use this 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.
• Should 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. Find You will find further information with 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.myco.com)(PORT=1604)) (ADDRESS=(PROTOCOL=TCP)(HOST=tst-db2.myco.com)(PORT=1604))) (CONNECT_DATA=(SERVICE_NAME=mydb1.myco.com)(SERVER=DEDICATED)))

...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:postgresql://postgresqlsrv:5432/jobscheduler</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</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>

Explanation:

• The example makes use of uses the PostgreSQL® database server running for with hostname postgresqlsrv and port 5432.

...

• Note that the JDBC Driver for SQL Server® has to be downloaded separately as it does not ship with JS7.
• The example makes use of uses the SQL Server® database server running for with hostname sqlserversrv and port 1433.
• Note that use of the above parameters with the JDBC URL is required: sendStringParametersAsUnicode=false;selectMethod=cursor
• For Windows environments, access to SQL Server can be configured to use integrated security, i.e. to use using the account that the JOC Cockpit Windows Service is operated forwith. 
  • 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 to in the C:\Windows\System32 directory or to in the bin directory of the Java JDK/JRE in use.

Database Setup

In a first step First of all, users have to create a database schema and account for JS7. The database schema has to support the Unicode character set.

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

Setup for MariaDB®/ MySQL®

...

In case that separate owner accounts and run-time accounts should are to be used for database access it is recommended to first create that the database objects are created first using the owner account and then to perform the installation of JOC Cockpit is performed using the run-time account:

...

For secure connections without use of passwords consider information from 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 then consider to use using UTF based character encoding and a collating sequence that supports UTF , - for example:

# set up database
CREATE DATBASE JS7
...
CHARACTER SET AL32UTF8;

# consider linguistic sorting for Unicode with WHERE clauses
ALTER SESSION SET NLS_COMP='LINUGUISTIC';

# consider linguistic sorting for Unicode with ORDER BY clauses
ALTER SESSION SET NLS_SORT='LINUGUISTIC';

Note:

• Consider Note that NLS_COMP and NLS_SORT have to use the same value.

...

Frequent practice includes to set setting up two accounts: an owner account that owns the schema & objects and a run-time account that is granted permissions to access objects of the owner schema. Alternatively a single account can be used that owns the schema.

# add owner account
CREATE USER JS7_OWNER IDENTIFIED BY JS7_OWNER;
GRANT CONNECT, RESOURCE TO JS7_OWNER;

# add run-tine account
CREATE USER JS7_USER IDENTIFIED BY JS7_USER;
GRANT CONNECT, RESOURCE TO JS7_USER;

In case that If separate owner accounts and run-time accounts should are to be used then it is recommended to first create that the database objects are created first using the owner account and then to perform the installation of JOC Cockpit is performed using the run-time account:

...

• Owner Account
  • Tables, Views, Sequences: CREATE, DROP, ALTER
  • Procedures: EXECUTE
• Run-time Account
  • Tables, Views: SELECT, INSERT, UPDATE, DELETE
  • Sequences: SELECT
• If a single account is used then all above permissions have to be assigned to this account.

There are number of ways how to assign of assigning permissions, e.g. by use of roles. One option is to create the objects in the owner schema and to generate the respective GRANT commands and CREATE SYNONYM commands for the run-time account like thisas follows:

# generate SQL statement to grant permissions to run-time account
SELECT DISTINCT
       CASE
            WHEN object_type = 'SEQUENCE' THEN 'GRANT SELECT'
            WHEN object_type IN ('TABLE', 'VIEW') THEN 'GRANT SELECT,INSERT,UPDATE,DELETE'
       END
       || ' ON JS7_OWNER.'|| object_name || ' TO JS7_USER;' 
  FROM dba_objects 
 WHERE object_type IN ('TABLE','VIEW','SEQUENCE') and owner='JS7_OWNER';

...

When creating the database then consider to use using a UTF based character encoding and collating sequence, for example:

...

# add an account
create user JS7_USER with password 'JS7_USER';

In case that If separate owner accounts and run-time accounts should be used for database access it is recommended to first create the database objects using the owner account and then to perform the installation of JOC Cockpit using the run-time account:

...

In PostgreSQL® a role is created that carries permissions and that is assigned to the user. The commands to create for creating a role and to assign assigning it to the user are like this:

...

For secure connections without use of passwords consider refer tothe information from in the JS7 - How to connect to an SQL Server database without using passwords article.

Database

When creating the database then consider to use a UTF based character encoding and collating sequence, for example:

...

# add an account
create user JS7_USER with password = 'JS7_USER';

In case that If separate owner accounts and run-time accounts should are be used for database access then it is recommended to first create that the database objects are created using the owner account and then to perform that the installation of the JOC Cockpit is then carried out using the run-time account:

• Connect to the database using an SQL Server® client and the owner account.
• Run the mssql.sql script that which is available from the db sub-directory when extracting the JOC Cockpit installer .tar.gz/.zip archive.

...

• By default a JOC Cockpit instance is configured to use up to 10  connections from the pool. 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 to allow the total number of connections allowed when setting up your JS7 database.

...

• 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 to add further settings for Hikari to a Hibernate configuration file.
• 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, however, close reading of the C3P0 docs is recommended before applying changes.

...

If you have to use an individual version of a JDBC Driver that is not included with JS7, for example for use with SQL Server® or , or if you have to use a specific version of the Oracle JDBC Driver®, then you can add the JDBC Driver to the JOC Cockpit.

• Consider Note that conflicting JDBC Drivers could be included with JS7. Such conflicts can originate from the fact that two JDBC Drivers use the same class name as specified with the hibernate.connection.driver_class attribute value from in a Hibernate configuration file.
• In case of conflicts remove existing JDBC Driver (*.jar) files when adding your version of a JDBC Driver.

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

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

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

...

Find JDBC Drivers from the following location locations of the JOC Cockpit configuration directory:

• Unix
  • /var/sos-berlin.com/js7/joc/jetty_base/lib/ext/joc
• Windows
  • C:\ProgramData\sos-berlin.com\js7\joc\jetty_base\lib\ext\joc
• A Note that a different location for the configuration directory can be specified during installation of JOC Cockpit.

From this location you will find the JDBC Driver files indicated with in the above table. 

• Add your JDBC Driver (*.jar) file to this location
• Optionally remove existing JDBC Driver (*.jar) files from this location, for example to replace the Oracle® JDBC Driver that ships with JS7 by a newer version.

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

• 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 JOC Cockpit.

Restart the JOC Cockpit to apply your changes.

...

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

Consider to apply applying changes to your Hibernate configuration file as explained from described in the previous chaptersection. Then restart the JOC Cockpit container.

On startup the JOC Cockpit checks the above directory for the existence of *.jar files. If a JDBC Driver is found then:

• the *.jar file will be copied to ./jetty_base/lib/ext/joc 
• any existing JDBC Driver *.jar files in this directory are removed.

...