Installing the Information Self-Service Tool on a Cluster: Sharing the Same Settings Across All the Nodes

This section explains how to configure the Information Self-Service Tool to store its settings on an external database.

By default, the Information Self-Service Tool stores the global settings and certain settings for each user in a local database. For example, the user “Saved Queries”, the fields selected by the administrator to display by default on a view search, etc.

If you installed Denodo on a cluster and there is an Information Self-Service Tool installed on each node of the cluster, you can configure all of them to store and retrieve these settings from a common database. Otherwise, users will use different settings depending on the node the load balancer redirects them.

Important

The Information Self-Service Tool makes use of web sessions, so when it is installed on a cluster, the load balancer should be configured to redirect all the request of the same web session to the same node (sticky sessions).

Setting Up the Common Database

Before configuring the Information Self-Service Tool you should create and configure the common database in which all the tools of the cluster will store the common settings. You can use the DBMS of your choice. You have to:

  1. Install the DBMS (if not installed yet).
  2. Create the database to be used by the ISST. An existent database could be used but it is recommended to create a new one to avoid possible conflicts with the table names.
  3. Configure a user (and its password) with create, read and write privileges on that database.

Configure the Information Self-Service Tool to Use the Common Database

This section explains how to configure the Information Self-Service Tool to use the common database. This process involves stopping the Denodo web container. I.e. all the SOAP and REST web services and web administration tools will be stopped for a few seconds.

Do these steps on each node of the cluster:

  1. Copy the database driver to the directory <DENODO_HOME>/resources/apache-tomcat/webapps/information-self-service-tool/WEB-INF/lib or in the directory <TOMCAT>/lib if you are using an external Apache Tomcat to deploy the ISST.

  2. Edit the file <DENODO\_HOME>/resources/apache-tomcat/webapps/information-self-service-tool/META-INF/context.xml

    1. Modify the configuration of the first data source to specify the desired Driver class name, the connection URI to the database, the authentication data, etc. Note, that at this point you should use the database and the user that you created previously to hold the Information Self-Service Tool metadata. For example:

      <Resource name="jdbc/selfservice-ds" auth="Container" type="javax.sql.DataSource"
          maxActive="50" maxIdle="30" maxWait="10000"
          driverClassName="your_Driver_classname"
          url="your_database_URI"
          username="your_user_name"
          password="your_password"/>
      
      1. If you are using a JDBC Driver not supporting the operation java.sql.Connection.isValid(int) (like, for example, the jTDS driver for Microsoft SQL Server), you must set the validationQuery parameter of the datasource. For instance:

        validationQuery="SELECT 1"
        
    2. Remove or comment the configuration of the second data source.

      <!--
      <Resource name="jdbc/selfservice-initialize-ds" auth="Container" type="javax.sql.DataSource"
          maxActive="50" maxIdle="30" maxWait="10000"
          driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
          url="jdbc:derby:${catalina.home}/../../metadata/self-service-database;create=true"/>
      -->
      

3. Remove the following fragment from the file $DENODO_HOME\resources\apache-tomcat\webapps\information-self-service-tool\WEB-INF\web.xml (this data source is used to automatically create the database when using the embedded Derby database).

<resource-ref>
    <description>SelfService Initial Datasource</description>
    <res-ref-name>jdbc/selfservice-initialize-ds</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
  1. Create the tables equivalent to the ones of the script <DENODO_HOME>/resources/apache-tomcat/webapps/information-self-service-tool/WEB-INF/classes/selfservice.sql or replace this script with an equivalent one for your database. When the web application is started, it checks if the required tables exist in the database, and it executes the SQL script if the tables does not exist. Example scripts are provided in the same directory for the following databases (you only need to rename the appropriate script to selfservice.sql, or manually execute the SQL contained in the script against your database server, if you want to use one of these databases):

    1. Derby 10.12.1.1
    2. MySQL 5.6.26
    3. Oracle 12c
    4. PostgreSQL 9.5.0
    5. SQL Server 2014
  2. Execute the script <DENODO_HOME>/bin/webcontainer_shutdown.

  3. Open the Virtual DataPort administration tool and log in as an administrator user.

  4. Open the VQL Shell and execute

    WEBCONTAINER STATUS
    

    In the first line of the result, you should see Web Container: stopped | If not, wait for half a minute and try again.

  5. From the VQL Shell, execute:

    WEBCONTAINER START
    

    This will start the web container, including all the Denodo SOAP and REST web services.

  6. Start the Denodo web administration tools you use and the Information Self-Service Tool as usual.