Configuring Data Sources¶ In WSO2 Integrator: SI, datasources are defined in the <SI_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. Note The embedded H2 databases shipped with your product are suitable for development and testing environments. However, for production environments, it is recommended to use an industry-standard RDBMS such as Oracle, PostgreSQL, MySQL, MS SQL, etc. 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>/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.cj.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 WSO2 Integrator: SI accesses the database. password The password with which WSO2 Integrator: SI 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 false, the system considers the whole database session as a single transaction. Therefore, the results of all the queries you execute within a single session are visible only after the session is over. POSTGRESwso2.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 WSO2 Integrator: SI accesses the database.| | password | The password with which WSO2 Integrator: SI 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 false, the system considers the whole database session as a single transaction. Therefore, the results of all the queries you execute within a single session are visible only after the session is over. | OracleThere 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.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.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.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 WSO2 Integrator: SI accesses the database. password The password with which WSO2 Integrator: SI 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 false, the system considers the whole database session as a single transaction. Therefore, the results of all the queries you execute within a single session are visible only after the session is over. MSSQLwso2.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 WSO2 Integrator: SI 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 WSO2 Integrator: SI. 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 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. 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 WSO2 Integrator: SI 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.streaming.integrator.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. 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 <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 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