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
MySQL	Connector/J	mysql-connector-java-5.1.7-bin.jar	com.mysql.jdbc.Driver	http://www.mysql.com/downloads/connector/j/
Oracle	ojdbc6.jar	ojdbc6.jar	oracle.jdbc.driver.OracleDriver	http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
SQL Server	Microsoft JDBC Driver	sqljdbc4.jar	com.microsoft.sqlserver.jdbc.SQLServerDriver	http://www.microsoft.com/en-au/download/details.aspx?id=11774
DB2	IBM DB2 JDBC Driver	db2jcc4.jar	com.ibm.db2.jcc.DB2Driver	http://www-01.ibm.com/software/data/db2/linux-unix-windows/downloads.html

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.

To connect to MySQL you should 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. You must set this to either the value 1 or 2 for JQM to work correctly.

Please note that on Linux systems, this parameter defaults to 0 and therefore you must explicitly add it and set it to either 1 or 2. See the MySQL documentation for more information.

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 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.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"/>
```
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 SQL Server 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;"/>
```
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 needs to connect to SuperSERVER, so you need to create a user account in SuperADMIN for Job Queue Manager to use.

We recommend that you create a dedicated user account for use by the Job Queue Manager. This account must have access to all the datasets on your server.

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 - Configure Large Object Support

Job Queue Manager uses Spring to communicate with the database. The default Spring configuration is specific to a particular version of Oracle.

You need to check the settings and set the appropriate configuration for your database:

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

SuperWEB2 also has a version of this file (with the same name), but the one you need to edit is the Job Queue Manager version. It is located within the queuedjobservice directory.

Locate the following section:

XML

    <!-- The database specific blob handler for reading and writing (streaming) XTabJob results. This LobHandler should 
         work for any JDBC driver that is JDBC compliant in terms of the spec's suggestions regarding simple BLOB and CLOB 
         handling. This does not apply to Oracle 9i, and only to a limited degree to Oracle 10g! As a consequence, use 
         OracleLobHandler for accessing Oracle BLOBs/CLOBs. 
         See {@link LobHandler} and {@link DefaultLobHandler} for configuration requirements for your specific database in use. 
         
         Summarizing the recommended options (for actual LOB fields):
            JDBC 4.0 driver: DefaultLobHandler with streamAsLob=true.
            PostgreSQL: DefaultLobHandler with wrapAsLob=true.
            Oracle 9i/10g: OracleLobHandler with a connection-pool-specific NativeJdbcExtractor.
            For all other database drivers (and for non-LOB fields that might potentially turn into LOBs on some databases): a plain DefaultLobHandler. 
         -->
    <!-- Oracle database lob handler configuration -->
    <bean id="databaseLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
    </bean>    
    <!-- MySQL database lob handler configuration -->
    <!-- 
    <bean id="databaseLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler">
        <property name="streamAsLob" value="true"/>
        <property name="wrapAsLob" value="false"/>
    </bean>
     -->

Follow the instructions and comment out/uncomment the appropriate <bean> ... </bean> section.

The recommended MySQL, SQL Server and DB2 drivers are all JDBC 4.0 drivers, so for these database systems you will need to comment out the Oracle lob handler and uncomment the default lob handler:

XML

    <!-- Oracle database lob handler configuration -->
    <!-- 
    <bean id="databaseLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
    </bean>
    --->
    <!-- MySQL database lob handler configuration -->
    <bean id="databaseLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler">
        <property name="streamAsLob" value="true"/>
        <property name="wrapAsLob" value="false"/>
    </bean>

Save your changes.

Step 8 - 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 Saved 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 Saved 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.