Configure - User Data Repository

These instructions explain how to set up the User Data Repository to use a relational database such as MySQL, Oracle or SQL Server.

Before starting, make sure you have installed SuperWEB2 and verified that it is working correctly.

Step 1 - Create the User Data Repository Database and Database User

The first step is to create a database for the User Data Repository in your chosen RDBMS. To complete this step you may need to install a suitable database server or obtain access to an existing database server:

Create a new database. For example, you might call the database UDR_SuperWEB2.
- Set the default character set of your database to UTF8. This will allow the User Data Repository to save tables containing the full range of unicode characters.
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.

Step 2 - 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 User Data Repository database.

Download the appropriate JDBC driver for your database:

RDBMS	Recommended Driver	Example of JAR File	Driver Class	Download From
DB2	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/au/database/technologies/appdev/jdbc-downloads.html
PostgreSQL	PostgreSQL JDBC Driver	postgresql-42.2.20.jar	org.postgresql.Driver	https://jdbc.postgresql.org
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

Copy the driver .jar file to <tomcat_home>\lib. If you are using the standard installation location, this will be C:\ProgramData\STR\SuperWEB2\Tomcat\lib

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.

Step 3 - 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.

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-udr" 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.

PostgreSQL requires an additional parameter to be included in the resource definition, defaultAutoCommit="false". Refer to the example below for PostgreSQL for more details.

Following are some examples for the different database systems:
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.
- UDR_SuperWEB2 is the name of the new database we created in Step 1 and UDR is the name of the schema you want to use.
  
  Please note that DB2 requires that the name of the currentSchema value specified in the connection string must be uppercase.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100"
          maxIdle="30"  
          maxWaitMillis="10000"  
          username="udr_user"
          password="udr_password"
          driverClassName="com.ibm.db2.jcc.DB2Driver"  
          url="jdbc:db2://mydatabasehost:50000/UDR_SuperWEB2:currentSchema=UDR;"/>
```
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 MaridDB database.
- 3306 is the port number.
- udr_superweb2 is the name of the new database we created in Step 1.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100"
          maxIdle="30"  
          maxWaitMillis="10000"  
          username="udr_user"
          password="udr_password"
          driverClassName="org.mariadb.jdbc.Driver"  
          url="jdbc:mariadb://mydatabasehost:3306/udr_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.
- udr_superweb2 is the name of the new database we created in Step 1.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100"
          maxIdle="30"  
          maxWaitMillis="10000"  
          username="udr_user"
          password="udr_password"
          driverClassName="com.mysql.cj.jdbc.Driver"  
          url="jdbc:mysql://mydatabasehost:3306/udr_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.
- UDR_SuperWEB2 is the name of the new database we created in Step 1.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100"
          maxIdle="30"  
          maxWaitMillis="10000"  
          username="udr_user"
          password="udr_password"
          driverClassName="oracle.jdbc.driver.OracleDriver"  
          url="jdbc:oracle:thin:@mydatabasehost:1521:UDR_SuperWEB2"/>
```
Click here to see an example for PostgreSQL...
The following example is for PostgreSQL. In this example:
- mydatabasehost is the hostname of the machine running the PostgreSQL database.
- 5432 is the port number.
- UDR_SuperWEB2 is the name of the new database we created in Step 1.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          defaultAutoCommit="false"
          maxTotal="100"
          maxIdle="30"
          maxWaitMillis="10000"
          username="udr_user"
          password="udr_password"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://mydatabasehost:5432/UDR_SuperWEB2"/>
```
As shown in the example above, PostgreSQL requires an additional parameter, defaultAutoCommit, which must be set to false.
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.
- UDR_SuperWEB2 is the name of the new database we created in Step 1.
XML
```
<Resource name="ds/superstar-udr" auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100"
          maxIdle="30"  
          maxWaitMillis="10000"  
          username="udr_user"
          password="udr_password"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"  
          url="jdbc:sqlserver://mydatabasehost:1433;databaseName=UDR_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-udr"
 name="ds/superstar-udr"
 type="javax.sql.DataSource"/>

Save your changes to context.xml.

Step 4 - Start SuperWEB2 and Verify UDR Configuration

Now that you have completed the configuration, start the Tomcat/SuperWEB2 service. This will automatically create the empty UDR tables in the database.

When you have done this, follow these instructions to verify that UDR is setup correctly.