Using an External Database with Verastream Process Designer

  • 7021659
  • 14-Apr-2010
  • 11-Nov-2019

Environment

Verastream Process Designer R4 through R6 SP2

Situation

Verastream Process Designer (VPD) uses the Apache Derby database by default. For enhanced performance, you can configure VPD to use a different external database. This technical note explains how to use MySQL as VPD's database.

Resolution

The following examples configure VPD Process Server to use MySQL as the external database. The steps depend on your VPD version.

VPD R6 SP2

This section is for VPD Release 6 Service Pack 2. Complete the following steps to configure the VPD Process Server to use MySQL as the external database.

  1. Install MySQL:
      • Run the installer and choose a developer standard configuration.
    1. Create the tables that are required by Apache ODE:
      • Download the ODE database schema from http://ode.apache.org/sql/ode-1.3.4-jpa-mysql55.sql. Save the schema as a text file.
      • Run the mysql.exe commands below to create and initialize the ODE database:

    C:\MyDownloads>c:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe -u root -p
    Enter password: ****************
    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Your MySQL connection id is 2
    Server version: 8.0.15 MySQL Community Server - GPL

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> create database ode;
    Query OK, 1 row affected (0.00 sec)

    mysql> exit
    Bye

    C:\MyDownloads>"c:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root –p ode < ode_openjpa_mysql-1.3.2.sql
    Enter password: ****************
    C:\MyDownloads>

    1. Important: Be sure that the Verastream Process Server service has been started at least once, but is stopped before you proceed.
    1. Copy the MySQL JDBC connector from C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.15.jar and copy it to the ProcessServer\services\composite\lib directory. (The .jar will be automatically added to the Process Server classpath.)
    1. In the ProcessServer\services\composite\runtime\WEB-INF\conf\ode-axis2.properties file, uncomment the last two lines to enable external MySQL support:

    # Uncomment to enable external database support.
    #ode-axis2.db.mode=EXTERNAL
    #ode-axis2.db.ext.dataSource=java:comp/env/jdbc/ode-ds

    1. In the ProcessServer\services\composite\runtime\WEB-INF\jetty-env.xml file, uncomment the "ode-ds" resource and set credentials to enable MySQL JDBC connections in Jetty:

    <Configure class=" org.eclipse.jetty.webapp.WebAppContext">
     <!-- Uncomment to enable support for an external mysql database. -->
     <!--
     <New id="ode-ds" class=" org.eclipse.jetty.plus.jndi.Resource">\
    <Arg>jdbc/ode-ds</Arg>
      <Arg>
       <New class=" com.mysql.cj.jdbc.MysqlConnectionPoolDataSource">
        <Set name="Url">jdbc:mysql://localhost:3306/ode?autoReconnect=true</Set>
        <Set name="User">root</Set>
        <Set name="Password"></Set>
       </New>
      </Arg>
     </New>
     -->
    </Configure>

    7.    In the ProcessServer\services\composite\runtime\WEB-INF\web.xml file, uncomment the "resource-ref" at the bottom to enable JDBC connections in Jetty:

    <!-- UNCOMMENT TO USE APPSERVER DATASOURCE
        <resource-ref>
            <res-ref-name>jdbc/ode-ds</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            <res-sharing-scope>Shareable</res-sharing-scope>
        </resource-ref>
    UNCOMMENT TO USE APPSERVER DATASOURCE -->

    1. Configuration is complete. Start the Verastream Process Server service.

    Note: If you skip any of the above steps you will see errors in the console (or logs) when you try to start the server.


    VPD R6 SP1 or earlier

    This section is for VPD versions earlier than R6 SP2. Complete the following steps to configure the VPD Process Server to use MySQL as the external database.

    1. Install MySQL:
      1. For this example, obtain mysql-noinstall-5.1.45-win32.zip from http://www.mysql.com/downloads/mysql/#downloads.
      2. Extract the zip file and then run bin\mysqld.exe to start the database server.
    2. Create the tables that are required by Apache ODE:
      1. Download the ODE database schema from http://ode.apache.org/war-deployment.data/ode_openjpa_mysql-1.3.2.sql. Save the schema as a text file.
      2. Run the mysql.exe commands below to create and initialize the ODE database:
    C:\MyDownloads>mysql-5.1.45-win32\bin\mysql.exe -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.1.39-community MySQL Community Server (GPL)

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> create database ode;
    Query OK, 1 row affected (0.00 sec)

    mysql> exit
    Bye

    C:\MyDownloads>mysql-5.1.45-win32\bin\mysql.exe -u root ode < ode_openjpa_mysql-1.3.2.sql
    C:\MyDownloads>
    1. Important: Be sure that the Verastream Process Server service has been started at least once, but is stopped before you proceed.
    2. Download the MySQL JDBC connector from http://www.mysql.com/downloads/connector/j/. Extract the .jar file from the downloaded archive file and copy it to the ProcessServer\services\composite\lib directory. (The .jar will be automatically added to the Process Server classpath.)
    3. In the ProcessServer\services\composite\runtime\WEB-INF\conf\ode-axis2.properties file, uncomment the last two lines to enable external MySQL support:
    # Uncomment to enable external database support.
    #ode-axis2.db.mode=EXTERNAL
    #ode-axis2.db.ext.dataSource=java:comp/env/jdbc/ode-ds
    1. In the ProcessServer\services\composite\runtime\WEB-INF\jetty-env.xml file, uncomment the "ode-ds" resource to enable MySQL JDBC connections in Jetty:
    <Configure class="org.mortbay.jetty.webapp.WebAppContext">
     <!-- Uncomment to enable support for an external mysql database. -->
     <!—
     <New id="ode-ds" class="org.mortbay.jetty.plus.naming.Resource">\
    <Arg>jdbc/ode-ds</Arg>
      <Arg>
       <New class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
        <Set name="Url">jdbc:mysql://localhost:3306/ode?autoReconnect=true</Set>
        <Set name="User">root</Set>
        <Set name="Password"></Set>
       </New>
      </Arg>
     </New>
     -->
    </Configure>
    1. In the ProcessServer\services\composite\META-INF\service-ctx.xml file, uncomment the "configurationClasses" property to enable JettyPlus, which is required for external database support:
    <!-- Uncomment to enable JettyPlus (required for external database support). -->
        <!--
        <property name="configurationClasses">
         <list>
          <value>org.mortbay.jetty.webapp.WebInfConfiguration</value>
          <value>org.mortbay.jetty.plus.webapp.EnvConfiguration</value>
          <value>org.mortbay.jetty.plus.webapp.Configuration</value>
          <value>org.mortbay.jetty.webapp.JettyWebXmlConfiguration</value>
          <value>org.mortbay.jetty.webapp.TagLibConfiguration</value>
         </list>
        </property>
        -->
    1. In the ProcessServer\services\composite\runtime\WEB-INF\web.xml file, uncomment the "resource-ref" at the bottom to enable JDBC connections in Jetty:
    <!-- UNCOMMENT TO USE APPSERVER DATASOURCE
        <resource-ref>
            <res-ref-name>jdbc/ode-ds</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            <res-sharing-scope>Shareable</res-sharing-scope>
        </resource-ref>
    UNCOMMENT TO USE APPSERVER DATASOURCE -->
    1. Configuration is complete. Start the Verastream Process Server service.

    Note: If you skip any of the above steps you will see errors in the console (or logs) when you try to start the server.

    Using Other Databases

    The configuration in this technical note is specific to MySQL, so the steps may vary depending on which database you are using. Some things to consider when configuring VPD for another database include:

    • Different database vendors have their own JDBC connectors, the configuration of which will be specific to that product.
    • The configuration data in the jetty-env.xml file will almost certainly need to change.
    • The web.xml file may also need to be adjusted.

    Additional Information

    Legacy KB ID

    This article was originally published as Attachmate Technical Note 10093.