Configuring Datasources

Datasources are configured in WSO2 SP for each runtime ( i.e., worker, editor, manager, dashboard) are defined in the <SP_HOME>/conf/<runtime>/deployment.yaml file. e.g., to configure a datasource in the worker runtime, relevant configurations need to be added to the <SP_Home>/conf/worker/deployment.yaml file.

To view a sample datasource confuguration for each database type supported, click on the following links:

If the database driver is not an OSGI bundle, then it should be converted to OSGI (using jartobundle.sh) before placing it in the <SP_HOME>/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.

e.g., sh WSO2_SP_HOME/bin/jartobundle.sh ojdbc6.jar WSO2_SP_HOME/lib/

H2

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
      configuration:
        jdbcUrl: 'jdbc:h2:${sys:carbon.home}/wso2/${sys:wso2.runtime}/database/testdb;IFEXISTS=TRUE;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;MVCC=TRUE'
        username: root
        password: root
        driverClassName: org.h2.Driver
        maxPoolSize: 50
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false

MySQL

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration: 
         jdbcUrl: jdbc:mysql://hostname:port/testdb
         username: root
         password: root
         driverClassName: com.mysql.jdbc.Driver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

POSTGRES

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
      configuration:
        jdbcUrl: jdbc:postgresql://hostname:port/testdb
        username: root
        password: root
        driverClassName: org.postgresql.Driver
        maxPoolSize: 10
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false

Oracle

There are two ways to configure this database type. If you have a System Identifier (SID), use this (older) format:

jdbc:oracle:thin:@[HOST][:PORT]:SID

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port:SID
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

If you have an Oracle service name, use this (newer) format:

jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration:
         jdbcUrl: jdbc:oracle:thin:@hostname:port/SERVICE
         username: testdb
         password: root
         driverClassName: oracle.jdbc.driver.OracleDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The Oracle driver need to be converted to OSGi (using jartobundle.sh) before put into WSO2_SP_HOME/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.

MSSQL

wso2.datasources:
 dataSources:
   - name: WSO2_TEST_DB
     description: The datasource used for test database
     jndiConfig:
       name: jdbc/WSO2_TEST_DB
     definition:
       type: RDBMS
       configuration: 
         jdbcUrl: jdbc:sqlserver://hostname:port;databaseName=testdb
         username: root
         password: root
         driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The following sections explain datasource requirment and how to change the default datasource in each components:

RDBMS Data Provider

Database Access	RDBMS provider is publishing records from RDBMS table into generated gadgets and also can configure to purging the records in table. For that required access for read and delete the records for user defined table in the database.
Mandatory/Optional	Mandatory, if user select the datasource when generating the widget or uses already generated widgets which bind to RDBMS data provider in dashboard runtime.
Default Datasource Name	SAMPLE_DB
Default Database	The default 'H2' database location is '<PRODUCT_HOME>/wso2/dashboard/database/SAMPLE_DB'.
Tables	The default database shipped with sample table 'TRANSACTION_TABLE'
Schemas and Queries	Information about the default queries: Configuring Database Queries#RDBMSDataProviderRDBMSDataProvider Schema for sample table: 'TRANSACTIONS_TABLE (creditCardNo VARCHAR(50), country VARCHAR(50), transaction VARCHAR(50), amount INT)'
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Carbon Coordination

Database Access	Carbon coordination supports zookeeper and RDBMS based coordination. In RDBMS coordination, database access is required for updating the heartbeats of the nodes. In addition, Database access is requried to update the coordinator and the other members in the cluster
Mandatory/Optional	Mandatory, but Zookeeper coordination can also be used instead of RDBMS
Default Datasource Name	Carbon datasources is used. The default datasource varies depending on the deployment as follows: If SP is deployed in the standalone mode or as a minimum HA cluster, `WSO2_CARBON_DB` is the default database. If SP is deployed as a fully distributed cluster, `SP_MGT_DB` is the default database. Configuration documentation can be found in Configuring Cluster Coordination
Tables	LEADER_STATUS_TABLE, MEMBERSHIP_EVENT_TABLE, REMOVED_MEMBERS_TABLE, CLUSTER_NODE_STATUS_TABLE
Schemas and Queries	Information about the default queries and schema: https://github.com/wso2/carbon-coordination/blob/v2.0.12/components/cluster-coordinator/org.wso2.carbon.cluster.coordinator/org.wso2.carbon.cluster.coordinator.rdbms/src/main/java/org/wso2/carbon/cluster/coordinator/rdbms/util/RDBMSConstants.java
Tested Database Types	MySQL

Stream Processor Core - Persistence

Database Access	Persist the state of Siddhi Applications Periodically into the database, if user select org.wso2.carbon.stream.processor.core.persistence.DBPersistenceStore class in <SP_Home>/conf/<worker/manager>/deployment.yaml file under `state.persistence` see Configuring Database and File System State Persistence.
Mandatory/Optional	Optional, uses File system persistence as default.
Default Datasource Name	N/A, user have to specify datasource to be used in persisting the state in < SP_Home >/ conf /<worker/manager>/deployment. yaml file under `state.persistence > config > datasource`
Tables	N/A, user have to specify table name to be used in persisting the state in < SP_Home >/ conf /<worker/manager>/deployment. yaml file under `state.persistence > config > table`
Schemas and Queries	Information about the default queries and schema: https://github.com/wso2/carbon-analytics/blob/master/components/org.wso2.carbon.stream.processor.core/src/main/resources/queries.yaml
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Stream Processor - Status Dashboard

Database Access	Keep carbon metrics data and registerd sp-worker details and athentication details within the cluster. See Monitoring Stream Processor for more information
Mandatory/Optional	Mandatory
Default Datasource Name	WSO2_STATUS_DASHBOARD_DB, WSO2_METRICS_DB
Tables	METRIC_COUNTER, METRIC_GAUGE, METRIC_HISTOGRAM, METRIC_METER, METRIC_TIMER , WORKERS_CONFIGURATIONS, WORKERS_DETAILS
Schemas and Queries	Information about the default queries and schema: https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.status.dashboard.core/src/main/resources/queries.yaml
Tested Database Types	H2, MySQL, Mssql, Oracle 11g (Postgres is tested with Carbon-Metrics only)

Siddhi RDBMS Store

Database Access	It gives the capability of creating the tables at the siddhi app runtime and access the existing tablesif a user defined carbon datasource or JNDI property in a siddhi app. Documentation can be found in https://wso2-extensions.github.io/siddhi-store-rdbms/api/4.0.15/
Mandatory/Optional	Optional
Default Datasource Name	No such default Datasource. User has to create the datasource in the Siddhi app
Tables	No such default tables. User has to define the tables
Schemas and Queries	Information about the default queries and schema: https://github.com/wso2-extensions/siddhi-store-rdbms/blob/v4.0.15/component/src/main/resources/rdbms-table-config.xml
Tested Database Types	H2, MySQL, Mssql, Oracle 11g, DB2, PostgreSQL

Carbon Dashboards

Database Access	Carbon Dashboard feature uses its datasource to persist the dashboard related information
Mandatory/Optional	Optional
Default Datasource Name	WSO2_DASHBOARD_DB
Tables	DASHBOARD_RESOURCE
Schemas and Queries	https://github.com/wso2/carbon-dashboards/tree/master/features/org.wso2.carbon.dashboards.api.feature/src/main/resources/sql
Tested Database Types	H2, MySQL

Business Rules

Database Access	Business Rules feature uses database to persist the derived business rules
Mandatory/Optional	Mandatory
Default Datasource Name	BUSINESS_RULES_DB
Tables	BUSINESS_RULES, RULES_TEMPLATES
Schemas and Queries	https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.business.rules.core/src/main/resources/queries.yaml
Tested Database Types	H2, MySQL, Oracle 11g

IdP Client

Database Access	IdP client access the DB layer to persist the client id and the client secret of dynamic client registration
Mandatory/Optional	Mandatory for external IdP clients
Default Datasource Name	DB_AUTH_DB
Tables	OAUTH_APPS
Schemas and Queries	https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/authentication/org.wso2.carbon.analytics.idp.client/src/main/resources/queries.yaml
Tested Database Types	H2

RDBMS Data Provider

Database Access	RDBMS data provider read the table data for given query and push into Widgets, and purge the data in the table.
Mandatory/Optional	Mandatory, default is in H2
Default Datasource Name	SAMPLE_DB is the default in H2
Tables	TRANSACTION_TABLE
Schemas and Queries	https://github.com/wso2/carbon-analytics/blob/v2.0.250/components/org.wso2.carbon.data.provider/src/main/resources/queries.yaml
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Permission Provider

Database Access	Permission provider will access the DB to persist permissions and role - permission mappings.
Mandatory/Optional	Mandatory, default is in H2
Default Datasource Name	PERMISSIONS_DB
Tables	PERMISSIONS, ROLE_PERMISSIONS
Schemas and Queries	https://github.com/wso2/carbon-analytics-common/blob/v6.0.52/components/permission-provider/org.wso2.carbon.analytics.permissions/src/main/resources/queries.yaml
Tested Database Types	H2, MySQL, Mssql, Oracle 11g