

Configuring Data Sources¶

In the Streaming Integrator, there are datasources specific to both the Streaming Integrator server and Streaming Integrator Tooling. The data sources of each runtime are defined in the <SI_HOME>|<SI_TOOLING_HOME>/conf/server/deployment.yaml file.

e.g., To configure a data source in the server runtime, the relevant configurations need to be added in the <SI_Home>/conf/server/deployment.yaml file.

To view a sample data source configuration for each database type supported, expand the following sections:

Info

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

e.g., sh WSO2_SI_HOME/bin/jartobundle.sh ojdbc6.jar WSO2_SI_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) that is defined in the deployment.yaml file.

MySQL

    wso2.datasources:
     dataSources:
     name: TEST_DB
     description: The datasource used for test database
     jndiConfig:
     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

** Parameter descriptions*

Parameter	Description
`name`	A unique name for the data source.
`description`	A description of the data source.
`type`	The database type to which the data source connects.
`jdbcUrl`	The string used by the database driver to connect to the database.
`username`	the username with which ESB Streaming Integrator accesses the database.
`password`	The password with which ESB Streaming Integrator accesses the database.
`driverClassName`	The name of the driver class that establishes the connection to the database.
`minIdle`	The minimum number of active connections that can remain idle in the pool at a given time without extra connections being created. e.g., If you specify `2` as the value for this parameter, the system creates a new connection to the database only if the connection pool currently has only two or less active and idle connections.
`maxPoolSize`	The maximum number of total connections that are allowed to exist in the connection pool at a given time.
`idleTimeout`	The maximum duration of time (in milliseconds) for which the system allows a connection to the database to be idle before closing it.
`connectionTestQuery`	The test query executed on the database to check the validity of the connection.
`validationTimeout`	The maximum duration of time (in milliseconds) that is allowed between validation tests carried out for the database connection.
`isAutoCommit`	If this parameter is set to `true`, each database query you perform during a single session is treated as a separate database transaction. As a result, the result of any query is visible to other database sessions soon after it is executed. If this parameter is set to `true`, the system considers the whole database session as a single transaction. Therefore, the reults of all the queries you execute within a single session are visible only after the session is over.

POSTGRES

wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     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

| Parameter | Description | |-----------------------|---------------------------------------------------------------------------| | name | A unique name for the data source. | | description | A description of the data source. | | type | The database type to which the data source connects. | | jdbcUrl | The string used by the database driver to connect to the database. | | username | the username with which ESB Streaming Integrator accesses the database.| | password | The password with which ESB Streaming Integrator accesses the database. | | driverClassName | The name of the driver class that establishes the connection to the database. | | minIdle | The minimum number of active connections that can remain idle in the pool at a given time without extra connections being created. e.g., If you specify 2 as the value for this parameter, the system creates a new connection to the database only if the connection pool currently has only two or less active and idle connections. | | maxPoolSize | The maximum number of total connections that are allowed to exist in the connection pool at a given time. | | idleTimeout | The maximum duration of time (in milliseconds) for which the system allows a connection to the database to be idle before closing it. | | connectionTestQuery | The test query executed on the database to check the validity of the connection. | | validationTimeout | The maximum duration of time (in milliseconds) that is allowed between validation tests carried out for the database connection. | | isAutoCommit | If this parameter is set to true, each database query you perform during a single session is treated as a separate database transaction. As a result, the result of any query is visible to other database sessions soon after it is executed. If this parameter is set to true, the system considers the whole database session as a single transaction. Therefore, the reults of all the queries you execute within a single session are visible only after the session is over. |

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:
     description: The datasource used for test database
     jndiConfig:
     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:
     description: The datasource used for test database
     jndiConfig:
     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 SI_HOME/lib directory. For detailed instructions, see Adding Third Party Non OSGi Libraries.

If you are using persisted aggregations, you can include the connectionInitSql parameter instead of connectionTestQuery as shown in the following sample configuration.

Sample datasource configuration -

 - name: TEST_DB
   description: The datasource used for test database
   jndiConfig:
     name: jdbc/TEST_DB
   definition:
     type: RDBMS
     configuration:
       jdbcUrl: jdbc:oracle:thin:@localhost:1521/ORCLCDB.localdomain
       username: <username>
       password: <password>
       driverClassName: oracle.jdbc.driver.OracleDriver
       minIdle: 5
       maxPoolSize: 50
       idleTimeout: 60000
       connectionInitSql: ALTER SESSION SET NLS_DATE_FORMAT='RRRR/fmMM/fmDD'
       validationTimeout: 30000
       isAutoCommit: false

Parameter	Description
name	A unique name for the data source.
description	A description of the data source.
type	The database type to which the data source connects.
jdbcUrl	The string used by the database driver to connect to the database.
username	the username with which ESB Streaming Integrator accesses the database.
password	The password with which ESB Streaming Integrator accesses the database.
driverClassName	The name of the driver class that establishes the connection to the database.
minIdle	The minimum number of active connections that can remain idle in the pool at a given time without extra connections being created. e.g., If you specify `2` as the value for this parameter, the system creates a new connection to the database only if the connection pool currently has only two or less active and idle connections.
maxPoolSize	The maximum number of total connections that are allowed to exist in the connection pool at a given time.
idleTimeout	The maximum duration of time (in milliseconds) for which the system allows a connection to the database to be idle before closing it.
connectionInitSql	The test query executed on the database to check the validity of the connection. You can use this parameter instead of the `connectionTestQuery` parameter when you are using persisted aggregations. This is because when you use persisted aggregation with an Oracle database, the database connection session time format needs to be changed to `RRRR/fmMM/fmDD`. This is addressed when you use the `connectionInitSql` parameter.
connectionTestQuery	The test query executed on the database to check the validity of the connection.
validationTimeout	The maximum duration of time (in milliseconds) that is allowed between validation tests carried out for the database connection.
isAutoCommit	If this parameter is set to `true`, each database query you perform during a single session is treated as a separate database transaction. As a result, the result of any query is visible to other database sessions soon after it is executed. If this parameter is set to `true`, the system considers the whole database session as a single transaction. Therefore, the reults of all the queries you execute within a single session are visible only after the session is over.

MSSQL

wso2.datasources:
 dataSources:
     description: The datasource used for test database
     jndiConfig:
     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 tables explain the default data sources configured in the Streaming Integrator 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 server for the Streaming Integrator.
Default Datasource Name	`SAMPLE_DB`
Default Database	The default `H2` database location is `<DASHBOARD_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)` You can also view default schemas and queries .
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	`WSO2_CARBON_DB`
Tables	`LEADER_STATUS_TABLE`, `MEMBERSHIP_EVENT_TABLE`, `REMOVED_MEMBERS_TABLE`, `CLUSTER_NODE_STATUS_TABLE`
Schemas and Queries	See information about schemas and queries.
Tested Database Types	MySQL, Postgres, Mssql, Oracle 11g

Streaming Integrator 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 `<SI_Home>/conf/<server>/deployment.yaml` file. For more information, see Configuring Database and File System State Persistence.
Required/Optional	This is optional. MWARE ESB 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 `<SI_Home>/conf/<server>/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 `<SI_Home>/conf/<server>/deployment.yaml` file under `state.persistence` > `config` > `table` .
Schemas and Queries	See information about schemas and queries.
Tested Database Types	H2, MySQL, Postgres, Mssql, Oracle 11g

Streaming Integrator - Status Dashboard¶

Database Access Requirement	To display information relating to the status of your Streaming Integrator deployment, the Status Dashboard needs to retrieve carbon metrics data, registered Streaming Integrator server 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	See information about schemas and queries.
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 application runtime and access the existing tables if a user-defined carbon data source or JNDI property in a siddhi application. Documentation can be found in [Siddhi Extensions Documentation](https://siddhi-io.github.io/siddhi-store-rdbms/api/latest/).
Required/Optional	Optional
Default Datasource Name	No such default Datasource. User has to create the datasource in the Siddhi application
Tables	No such default tables. User has to define the tables
Schemas and Queries	See information about schemas and queries.
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_RESOURCES`
Schemas and Queries	information about schemas and queries.
Tested Database Types	H2, MySQL, Postgres

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	See information about schemas and queries.
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
Required/Optional	Mandatory for external IdP client
Default Datasource Name	`DB_AUTH_DB`
Tables	`OAUTH_APPS`
Schemas and Queries	See information about schemas and queries.
Tested Database Types	H2, MySQL, Oracle 11g

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	See information about schemas and queries.
Tested Database Types	H2, MySQL, Mssql, Oracle 11g , Postgres

Top