Configuring Datasources

In WSO2 SP, there are datasources specific to each runtime (i.e., worker, editor, manager, and dashboard runtimes). The datasources of each runtime are defined in the <SP_HOME>/conf/<runtime>/deployment.yaml file. e.g., To configure a datasource in the worker runtime, the relevant configurations need to be added in the <SP_Home>/conf/worker/deployment.yaml file.

You need to include the database driver corresponding to the database in the WSO2_SP_HOME/lib/ directory for Stream Processor to communicate with the database.

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/

The database should be tuned to handle the total maxPoolSize(The maximum number of threads that should be reserved at any given time to handle events) which defined in deployment.yaml

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

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
		 minIdle: 5
         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
		minIdle: 5
        maxPoolSize: 50
        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
		 minIdle: 5
         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
	     minIdle: 5
         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
		 minIdle: 5
         maxPoolSize: 50
         idleTimeout: 60000
         connectionTestQuery: SELECT 1
         validationTimeout: 30000
         isAutoCommit: false

The following sections explain the default datasources configured in various WSO2 SP components for different purposes, and how to change them.

RDBMS data provider

Database Access Requirement	The RDBMS provider publishes records from RDBMS tables into generated widgets. It can also be configured to purge records in tables. In order to carry out these actions, this provider requires access to read and delete records in user defined tables of the database. For more information about the RDBMS data provider, see Generating Widgets.
Required/Optional	This is required if you select a datasource when generating the widget or use existing widgets that connect to the RDBMS data provider when you run the dashboard profile of WSO2 SP.
Default Datasource Name	`SAMPLE_DB`
Default Database	The default `H2` database location is `<SP_HOME>/wso2/dashboard/database/SAMPLE_DB`.
Tables	The default database shipped with a sample table named `TRANSACTION_TABLE`.
Schemas and Queries	The schema for the sample table is `TRANSACTIONS_TABLE (creditCardNo VARCHAR(50), country VARCHAR(50), transaction VARCHAR(50), amount INT)` The default queries can be viewed here.
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Carbon coordination

Database Access Requirement	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 required to update the coordinator and the other members in the cluster. For more information, see Configuring Cluster Coordination .
Required/Optional	This is required. However, you can also use Zookeeper coordination instead of RDBMS.
Default Datasource Name	The carbon datasources are 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.
Tables	`LEADER_STATUS_TABLE`, `MEMBERSHIP_EVENT_TABLE`, `REMOVED_MEMBERS_TABLE`, `CLUSTER_NODE_STATUS_TABLE`
Schemas and Queries	Information about the default queries and the schema can be viewed here.
Tested Database Types	MySQL, Postgres, Mssql, Oracle 11g

Stream Processor core - persistence

Database Access Requirement	This involves persisting the state of Siddhi Applications periodically in the database. State persistence is enabled by selecting the `org.wso2.carbon.stream.processor.core.persistence.DBPersistenceStore` class in the `state.persistence` section of the `<SP_Home>/conf/<worker/manager>/deployment.yaml` file. For more information, see Configuring Database and File System State Persistence.
Required/Optional	This is optional. WSO2 is configured to persist the state of Siddhi applications by default.
Default Datasource Name	N/A. If state persistence is required, you need to configure the datasource in the `<SP_Home>/conf/<worker/manager>/deployment.yaml` file under `state.persistence` > `config` > `datasource`.
Tables	N/A. If state persistence is required, you need to specify the table name to be used when persisting the state in the `<SP_Home>/conf/<worker/manager>/deployment.yaml` file under `state.persistence` > `config` > `table`.
Schemas and Queries	Information about the default queries and schema can be viewed here.
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Stream Processor - Status Dashboard

Database Access Requirement	To display information relating to the status of your SP deployment, the Status Dashboard needs to retrive carbon metrics data, registered SP worker details and authentication details within the cluster from the database. For more information, see Monitoring Stream Processor.
Required/Optional	Required
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 Requirement	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/
Required/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 Requirement	Carbon Dashboard feature uses its datasource to persist the dashboard related information
Required/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 Requirement	Business Rules feature uses database to persist the derived business rules
Required/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 Requirement	IdP client access the DB layer to persist the client id and the client secret of dynamic client registration when External IdP Client is used.
Required/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

Permission provider

Database Access Requirement	Permission provider will access the DB to persist permissions and role - permission mappings.
Required/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

Distributed Message Tracer

Database Access Requirement	The Siddhi application and the dashbioard configured for the Distributed Message Tracer solution that is shipped with WSO2 SP by default access this database to both read and write data.
Required/Optional	Optional, default is in H2. This database is only needed when Distribution Message Tracing is enabled.
Default Datasource Name	Message_Tracing_DB
Tables	SpanTable
Schemas and Queries	This database uses Siddhi Queries to insert data. It reads data from TracingListGadget, TracingSearchGadget, TracingTimelineGadget
Tested Database Types	H2, MySQL, Mssql, Oracle 11g