Database Configuration Details

The WSO2 Governance Registry has been tested to work with various relational database management systems including,

H2 (Embedded and Server)
Apache Derby (Embedded and Server)
MS SQL Server
MySQL
Oracle
OpenEdge
PostgreSQL
IBM DB2

The $GREG_HOME/repository/conf/registry.xml refers to a database configuration defines at $GREG_HOME/repository/conf/datasources/ master-datasources.xml as given below.

 <dbConfig name="wso2registry">
        <dataSource>jdbc/WSO2CarbonDB</dataSource>
 </dbConfig>

The server administrator can define the type of database to be used, along with the JDBC connection URL to connect to the database and a compatible driver via the database configuration parameters in the $GREG_HOME/repository/conf/datasources/ master-datasources.xml as given below.

<datasource>
   <name>WSO2_CARBON_DB</name>
      <description>...</description>
       <jndiConfig>
           <name>jdbc/WSO2CarbonDB</name>
       </jndiConfig>
       <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
       </definition>
</datasource>

Tip

The JDBC URL of the registry database is used in construction of cache keys. The cache keys are case sensitive, and therefore, you might run into caching issues if you do not consider the case.

Mandatory Configuration Elements

url - The URL of the database.
username - The name of the database user.
password - The password of the database user.
driverClassName - The fully qualified Java class name of the JDBC driver to be used.
maxActive - The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxWait - The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or <= 0 to wait indefinitely.
minIdle - The minimum number of active connections that can remain idle in the pool, without extra ones being created, or 0 to create none.

Optional Configuration Elements

validationQuery - This is used to validate the DB connection to test whether its healthy (For example, SELECT 1 or SELECT 1 FROM DUAL in Oracle).
testOnBorrow - The indication of whether objects will be validated before being borrowed from the pool.
validationInterval - The specified time interval for validations to run.

Therefor all the database configurations should be defined in the master-datasources.xml . The database configurations that are needed to be define inside the user-mgt.xml should also be pointed to the configurations defined in the master-datasources.xml. You can find the database configurations for the different databases here.

There can be more than one database configuration specified within the master-datasources.xml file. For instance, you can have something similar to the configuration below.

<datasource>
<name>WSO2_EMBED_DB</name>
<description>...</description>
<jndiConfig>
<name>jdbc/WSO2EmbedDB</name>
</jndiConfig>
<definition type="RDBMS">
<configuration>
	<url>jdbc:derby:databasetest/CARBON_TEST;create=true</url>
	<userName>su</userName>
	<password>su</password>
	<driverClassName>org.apache.derby.jdbc.EmbeddedDriver</driverClassName>
	<maxActive>80</maxActive>
	<maxWait>60000</maxWait>
	<minIdle>5</minIdle>
	<testOnBorrow>true</testOnBorrow>
	<validationQuery>SELECT 1</validationQuery>
	<validationInterval>30000</validationInterval>
</configuration>
</definition>
</datasource>
 
<datasource>
<name>WSO2_SERVER_DB</name>
<description>...</description>
<jndiConfig>
<name>jdbc/WSO2ServerDB</name>
</jndiConfig>
<definition type="RDBMS">
<configuration>
	<url>jdbc:derby://10.20.30.40:1527/derbyDBRemote;create=true</url>
	<userName>su</userName>
	<password>su</password>
	<driverClassName>org.apache.derby.jdbc.EmbeddedDriver</driverClassName>
	<maxActive>80</maxActive>
	<maxWait>60000</maxWait>
	<minIdle>5</minIdle>
	<testOnBorrow>true</testOnBorrow>
	<validationQuery>SELECT 1</validationQuery>
	<validationInterval>30000</validationInterval>
</configuration>
</definition>
</datasource>
 
<datasource>
<name>WSO2_SERVER_REMOTE_DB</name>
<description>...</description>
<jndiConfig>
<name>jdbc/WSO2ServerRemoteDB</name>
</jndiConfig>
<definition type="RDBMS">
<configuration>
	<url>jdbc:derby://10.20.30.40:1527/derbyDBRemote;create=true</url>
	<userName>su</userName>
	<password>su</password>
	<driverClassName>org.apache.derby.jdbc.EmbeddedDriver</driverClassName>
	<maxActive>80</maxActive>
	<maxWait>60000</maxWait>
	<minIdle>5</minIdle>
	<testOnBorrow>true</testOnBorrow>
	<validationQuery>SELECT 1</validationQuery>
	<validationInterval>30000</validationInterval>
</configuration>
</definition>
</datasource>

However, the server can only handle one active configuration at a time. The currentDBConfig parameter defined in the registry.xml is used to specify the database configuration that is active at present.

<currentDBConfig>wso2registry</currentDBConfig>

The value of the currentDBConfig parameter should be a valid name of a database configuration defined on the registry.xml file. An administrator can gain a number of benefits by being able to switch between database configurations.

The ability to create back-up databases that can be activated by a simple configuration change resulting in minimal downtime.
Ease of development with the ability of testing on multiple database systems.