Configure JQM

This section describes how to configure Job Queue Manager.

Before you start to follow these steps, make sure you have followed the instructions to install Job Queue Manager.

Step 1 - Configure SuperWEB2 to use an RDBMS for the User Data Repository

If you have not already done so, follow the instructions to configure a relational database store for the User Data Repository.

Step 2 - Create the JQM Database

You need to create a database for Job Queue Manager in your chosen RDBMS:

Create a new database. For example, you might call the database JQM_SuperWEB2.
- Set the default character set of your database to UTF8. This will allow Job Queue Manager to process tables containing the full range of unicode characters.
- If you are using DB2, make sure that your set the minimum page size of the database to 8192. For example, by using the following command to create the database (replace <database_name> with your chosen database name):
  SQL
```
CREATE DATABASE <database_name> USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192
```
Create a dedicated database user account and set the account password.
- If you are using Oracle or DB2 then the username of the user you are using to connect to the database must be the same as the database schema.
Grant your new user account full privileges over your new database.

To complete this step you may need to install a suitable database server or obtain access to an existing database server.

The JQM database must be accessible to both SuperWEB2 and Job Queue Manager. If these applications are running in separate Tomcat instances on separate servers, then you must ensure that both servers have access to the database server that provides the JQM database.

Step 3 - Install a JDBC Driver

JDBC (Java Database Connectivity) is a programming interface that lets Java applications access a relational database. You need to install a JDBC driver for your RDBMS, so that SuperWEB2 can communicate with the JQM database.

Download the appropriate JDBC driver for your database:

RDBMS	Recommended Driver	Example of JAR File	Driver Class	Download From
Aurora MySQL	AWS MySQL Driver	aws-mysql-jdbc-1.1.7.jar	software.aws.rds.jdbc.mysql.Driver	https://github.com/awslabs/aws-mysql-jdbc/releases
DB2	IBM DB2 JDBC Driver	db2jcc4.jar	com.ibm.db2.jcc.DB2Driver	https://www-01.ibm.com/software/data/db2/linux-unix-windows/downloads.html
MariaDB	MariaDB Connector/J	mariadb-java-client-2.4.1.jar	org.mariadb.jdbc.Driver	https://mariadb.com/downloads/#connectors
MySQL	Connector/J	mysql-connector-java-8.0.25.jar	com.mysql.cj.jdbc.Driver	https://www.mysql.com/downloads/connector/j/
Oracle	ojdbc	ojdbc11.jar	oracle.jdbc.driver.OracleDriver	https://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
SQL Server	Microsoft JDBC Driver	mssql-jdbc-7.2.2.jre11.jar	com.microsoft.sqlserver.jdbc.SQLServerDriver	https://www.microsoft.com/en-us/download/confirmation.aspx?id=57782

To connect to SQL Server you must use the Microsoft JDBC driver. Do not use the open source jTDS driver. The Microsoft driver is a JDBC 4.0 compliant driver, whereas the jTDS driver only supports JDBC 3.0. The large object support that was added in JDBC 4.0 is required to use Job Queue Manager.

To connect to DB2 you must use the JDBC 4.0 driver listed here (db2jcc4.jar). There are multiple drivers available for DB2 but only the JDBC 4.0 version has the appropriate large object support that is required to use Job Queue Manager.

MySQL on Linux uses case sensitive table names by default. You must change the lower-case-table-names setting to either 1 or 2 to use MySQL with Job Queue Manager.

If you are using MySQL on either Windows or Linux, check the my.ini (Windows) or my.cnf (Linux) configuration file for the parameter lower-case-table-names. This configuration file is located in the MySQL program data directory (for example: C:\ProgramData\MySQL\MySQL Server 5.6 on Windows or /etc/mysql/ on Linux.

Make sure the parameter is set to either 1 or 2. See the MySQL documentation for more information.

If you are using the DB2 JDBC driver (4.16 or above), then you may encounter an error similar to the following in the SuperWEB2 logs on startup:

CODE

WARNING [main] org.apache.tomcat.util.scan.StandardJarScanner.processURLs Failed to scan [file:/C:/ProgramData/STR/SuperWEB2/lib/pdq.jar] from classloader hierarchy
    java.io.FileNotFoundException: C:\ProgramData\STR\SuperWEB2\Tomcat\lib\pdq.jar (The system cannot find the file specified)

This is due to the DB2 JDBC driver containing a reference to a missing JAR file, pdq.jar. pdq.jar is not required by SuperWEB2, and the issue can be resolved by removing the reference to it from the MANIFEST.MF file within the driver itself, as described in IBM's support documentation: https://www.ibm.com/support/pages/javaiofilenotfoundexception-thrown-apache-tomcat-application-server-ibm-data-server-driver-jdbc-and-sqlj

Please contact WingArc support if you require assistance to resolve this issue.

Copy the driver .jar file to <tomcat_home>\lib

If you are running Job Queue Manager and SuperWEB2 in separate Tomcat instances, copy the .jar file to the <tomcat_home>\lib directory in both instances.

Step 4 - Configure the Data Source

The next step is to configure the data source by making changes to two configuration files: server.xml and context.xml.

If you are running Job Queue Manager and SuperWEB2 in different Tomcat instances, then you need to complete these steps in both instances.

server.xml

Open the file <tomcat_home>\conf\server.xml in a text editor.

Locate the <GlobalNamingResources> ... </GlobalNamingResources> section of the configuration:

XML

<GlobalNamingResources>
<!-- Editable user database that can also be used by
     UserDatabaseRealm to authenticate users
-->
<Resource name="UserDatabase" auth="Container"
          type="org.apache.catalina.UserDatabase"
          description="User database that can be updated and saved"
          factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
          pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>

Add the following section before the closing </GlobalNamingResources> tag:
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="USERNAME"
      password="PASSWORD"
      driverClassName="CLASSNAME"  
      url="URL"/>
```
You will need to customise this as follows:
- Replace USERNAME and PASSWORD with the username and password of your new database user.
- Replace CLASSNAME with the driver class for the database you are using (see the table above to find the correct value for your database driver).
- Replace URL with the JDBC connection string for your database. Depending on the database you are using, you may need to include the schema you wish to use in the connection string; see the example below for more details.
All the examples shown here are for Tomcat 8 and above only. If you are using an earlier version of Tomcat, change maxTotal to maxActive and change maxWaitMillis to maxWait.

Following are some examples for the different database systems:
Click here to see an example for Aurora MySQL...
The following example is for Aurora MySQL. In this example:
- mydatabasehost is the hostname of the machine running the Aurora MySQL database.
- 3306 is the port number.
- jqm_superweb2 is the name of the new database we created in Step 2.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="software.aws.rds.jdbc.mysql.Driver"  
      url="jdbc:mysql:aws://mydatabasehost:3306/jqm_superweb2?useUnicode=true&characterEncoding=UTF-8"/>
```
Click here to see an example for DB2...
The following example is for DB2. In this example:
- mydatabasehost is the hostname of the machine running the DB2 database.
- 50000 is the port number.
- JQM_SuperWEB2 is the name of the new database we created in Step 2 and JQM is the name of the schema you want to use.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="com.ibm.db2.jcc.DB2Driver"  
      url="jdbc:db2://mydatabasehost:50000/JQM_SuperWEB2:currentSchema=JQM;"/>
```
Click here to see an example for MariaDB...
The following example is for MariaDB. In this example:
- mydatabasehost is the hostname of the machine running the MariaDB database.
- 3306 is the port number.
- jqm_superweb2 is the name of the new database we created in Step 2.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="org.mariadb.jdbc.Driver"  
      url="jdbc:mysql://mydatabasehost:3306/jqm_superweb2"/>
```
Click here to see an example for MySQL...
The following example is for MySQL. In this example:
- mydatabasehost is the hostname of the machine running the MySQL database.
- 3306 is the port number.
- jqm_superweb2 is the name of the new database we created in Step 2.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="com.mysql.cj.jdbc.Driver"  
      url="jdbc:mysql://mydatabasehost:3306/jqm_superweb2?useUnicode=true&amp;characterEncoding=UTF-8"/>
```
It is important to include the useUnicode and characterEncoding parameters. These settings ensure that the connection to MySQL supports the full range of Unicode characters.
Click here to see an example for Oracle...
The following example is for Oracle. In this example:
- mydatabasehost is the hostname of the machine running the Oracle database.
- 1521 is the port number.
- JQM_SuperWEB2 is the name of the new database we created in Step 2.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="oracle.jdbc.driver.OracleDriver"  
      url="jdbc:oracle:thin:@mydatabasehost:1521:JQM_SuperWEB2"/>
```
Click here to see an example for SQL Server...
The following example is for SQL Server. In this example:
- mydatabasehost is the hostname of the machine running the SQL Server database.
- 1433 is the port number.
- JQM_SuperWEB2 is the name of the new database we created in Step 2.
XML
```
<Resource name="ds/superstar-jqm" auth="Container"
      type="javax.sql.DataSource"
      maxTotal="100"
      maxIdle="30"  
      maxWaitMillis="10000"  
      username="jqm_user"
      password="jqm_password"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"  
      url="jdbc:sqlserver://mydatabasehost:1433;databaseName=JQM_SuperWEB2"/>
```
Save your changes to server.xml.

Make sure your RDBMS is configured to use a sufficiently large database connection pool to support the connections from SuperWEB2. Learn more.

context.xml

Open the file <tomcat_home>\conf\context.xml in a text editor.

Add the following section before the closing </Context> tag:

XML

<ResourceLink
 global="ds/superstar-jqm"
 name="ds/superstar-jqm"
 type="javax.sql.DataSource"/>

Save your changes to context.xml.

If you are running Job Queue Manager and SuperWEB2 in different Tomcat instances, make sure you have configured server.xml and context.xml in both instances before continuing.

Step 5 - Configure SuperWEB2 to use Job Queue Manager

Go to the Tomcat instance that is hosting SuperWEB2 and open <tomcat_home>\webapps\webapi\WEB-INF\config\web-application-config.xml in a text editor.

Locate the following section:

XML

<!-- 
<import resource="data-access-config-jqm.xml" />
-->

Remove the comments:

XML

<import resource="data-access-config-jqm.xml" />

Save your changes.
Open <tomcat_home>\webapps\webapi\WEB-INF\web.xml in a text editor.

Locate the following section:

XML

<!-- <resource-ref>
<description>JQM DB Connection</description>
<res-ref-name>ds/superstar-jqm</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> -->

Remove the comments:

XML

<resource-ref>
<description>JQM DB Connection</description>
<res-ref-name>ds/superstar-jqm</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Save your changes.
Open <tomcat_home>\webapps\webapi\WEB-INF\classes\configuration.properties in a text editor.
Locate the following section:
XML
```
download.enableQueueManager=
```
Set the value to true:
XML
```
download.enableQueueManager=true
```
Save your changes.

Step 6 - Configure the Connection Between Job Queue Manager and SuperADMIN

Job Queue Manager runs all tabulations using a specified user account (rather than under the account of the user who queued the job), so you need to create a user account in SuperADMIN for Job Queue Manager to use.

We recommend that this be a dedicated account used by Job Queue Manager only (and not for any other purpose). This account will need to have access to all datasets on your server that are available for users to schedule tabulation jobs.

Once you have created this account, you need to configure Job Queue Manager so that it knows which sever to login to and the username and password to use.

Go to the Tomcat instance that is hosting Job Queue Manager and open <tomcat_home>\webapps\queuedjobservice\WEB-INF\data\.repository\administrationServerCatalog.xml in a text editor.

Locate the following section:

XML

<sa:administrationServer id="defaultprofile">
  <sa:host corbaport="9230" registryport="9234" rmiport="9231">localhost</sa:host>
  <sa:configServer protocol="http" host="localhost" port="9000" basePath="/v1/config" />
</sa:administrationServer>

This section specifies the port details and host name of the SuperADMIN server. You may need to update the details for your system. For example:
- You will need to change the port details if you are using non-standard ports.
- You will need to change the hostname from localhost if you are running Job Queue Manager on a different server to SuperADMIN.
Save your changes, if any.
Open <tomcat_home>\webapps\queuedjobservice\WEB-INF\config\common\service-config.xml in a text editor.

Locate the following sections:

CODE

    <bean id="jobResultProvider" class="au.com.str.jobqueue.server.springImpl.producer.XTabResultProviderTableManagerImpl">
    <property name="administrationServerUserName" value="user1"/>
    <property name="administrationServerPassword" value="user1"/>

CODE

    <bean id="platformHealthMonitor" class="au.com.str.jobqueue.server.springImpl.queue.PlatformHealthMonitorTableManagerImpl">
    <property name="administrationServerUserName" value="user1"/>
    <property name="administrationServerPassword" value="user1"/>

Change the values of administrationServerUserName and administrationServerPassword in both places to the username and password of the user account you want to use to connect Job Queue Manager to SuperADMIN/SuperSERVER.

The first section sets the username and password for providing the results of the cross tabulation back to SuperWEB2. The second section is the username and password for periodically checking that SuperSERVER is still running. You can use the same account for both cases or a different account.
Save your changes.

Step 7 - Restart Tomcat and Validate the Configuration

Once you have completed the configuration, restart the Tomcat or SuperWEB2 service. If you have installed the Job Queue Manager on a different machine to the one running SuperWEB2 then you will need to restart Tomcat on both machines.

You can now validate the configuration by logging in to SuperWEB2 and creating a table that will trigger Large Table Mode and invoke Job Queue Manager.

Using the sample Retail Banking dataset, you can trigger Large Table Mode by creating a table with the lowest level of the Area field (postcode) in the rows and all values of Age in the columns. You will see a message similar to the following:

SuperWEB2 also displays a message on the table to indicate that you are currently in Large Table Mode:

When you are ready to submit the query, select the download format from the drop-down list and click Queue Job to submit the query to Job Queue Manager:

You will be prompted to enter a table name:

Once you have supplied a table name and clicked Queue Job, you will be returned to the Table View. You can check the status of your query in the Manage Tables page:

You can also log out of SuperWEB2 and come back later. Your query will continue running in the background and you can retrieve the results from the Manage Tables page.

The times shown in the Submission Date column are in UTC/GMT by default. You may wish to configure this to display in your local timezone instead.

If the job does not run successfully, see the troubleshooting section for tips on resolving the issue. You should also check the log files for any errors (in particular, check the SW2.log file for any errors relating to missing tables). If there are any error messages, refer to the troubleshooting steps for next steps.