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:

1. 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.

     

     There is currently a known issue with Unicode support on SQL Server; the User Data Repository creation scripts do not currently support collations with the UTF8/UTF16 flags or the SC (Supplementary Characters) flag and do not use Unicode-compliant column types when creating the database. If you are using SQL Server, use a non UTF8/UTF16 collation and follow the steps below under "Update Column Types" to update the column datatypes to support Unicode after creating the User Data Repository database. 

2. 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

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

1. 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	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-12.8.1.jre11.jar	com.microsoft.sqlserver.jdbc.SQLServerDriver	https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server

2. 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:

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

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

2. 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>

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

   • 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="software.aws.rds.jdbc.mysql.Driver"  
             url="jdbc:mysql:aws://mydatabasehost:3306/udr_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.

   • 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"/>
   

4. 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

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

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

   XML

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

   

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

Step 5 (SQL Server Only) - Update Column Types

If you are using SQL Server, you will need to update some of the column types in the database in order to support the full range of Unicode characters in user-supplied names for saved tables and recodes. 

Using a suitable tool for interacting with the RDBMS (for example, SQL Server Management Studio), update the design of the following columns in the newly created UDR database: