1. Log on to the product's management console and select "Data Sources."
2. Click on the "Add Data Source" link in the "Data Sources" page.
3. Specify the required options in the "New Data Source" page. Depending on the data source type, the required fields may vary. Discussed below are the two types available.
RDBMS
If you select the "Data Source Type" as RDBMS, the following screen will appear.
This is the default RDBMS data source configuration provided by WSO2. Users can also write your own RDBMS configuration by selecting the Custom Data Source option. The following fields should be provided when defining the default RDBMS data source.
- Data Source Type : RDBMS
- Name : Name of the data source (This value should be unique.)
- Data Source Provider : Discussed below.
- Driver : The JDBC driver to be used.
- URL : The connection URL to be passed to the JDBC driver to establish a connection.
- User Name : The connection user name to be passed to the JDBC driver to establish a connection.
- Password : The connection password to be passed to the JDBC driver to establish a connection.
- Expose as a JNDI Data Source : Discussed below.
- Data Source Configuration Parameters : Additional properties can be defined when creating an RDBMS data source. More information is given below.
The meaning of many fields is similar to the DBCPconfiguration guide, to which you can refer for more information: http://commons.apache.org/dbcp/configuration.html
4. Once the values are entered, click "Save".
5. You have created a carbon data source. Also refer to edit and delete Data Sources .
Data Source Provider
There are two types of data source providers. You can use the default provider or link an external provider. For the 'default' Data Source Provider, given connection properties Driver, URL, User Name and Password need to be entered as follows:
If you need to add a data source supported by an external provider class such as "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource", select the "external" option and enter the name and value of connection properties by clicking "Add Property". Given below is an example data source of External Data source Provider with entered values.
JNDI Data Source
Java Naming and Directory Interface (JNDI) is a Java application programming interface (API) providing naming and directory functionality for Java software clients to discover and look up data and objects via a name. It helps decouple object creation from the object look-up. When you have registered a data source with JNDI, others can discover it through a JNDI look-up and use it.
To expose a data source as a JNDI data source, select the "Expose as a JNDI Data Source".
Data Source Configuration Parameters
In the RDBMS data sources, Tomcat JDBC Connection Pool (org.apache.tomcat.jdbc.pool) is used and "advanced data-source properties" help users define additional parameters when creating data sources from a database. These properties can be accesses by clicking on the "Data source configuration parameters" option in the "Add New Data Source" window as follows.
Available advanced data-source property details are described below.
| Property Name | Description |
|---|---|
| Transaction Isolation | The default TransactionIsolation state of connections created by this pool.
|
| Initial Size | (int) The initial number of connections created when the pool is started. Default value is 0. |
| Max. Active | (int) The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100. |
| Max. Idle | (int) The maximum number of connections that should be kept in the pool at all times. Default value is 8. Idle connections are checked periodically (if enabled) and connections that have been idle for longer than minEvictableIdleTimeMillis will be released. (also see testWhileIdle) |
| Min. Idle | (int) The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail. Default value is 0. (also see testWhileIdle) |
| Max. Wait | (int) Maximum number of milliseconds that the pool waits (when there are no available connections) for a connection to be returned before throwing an exception. Default value is 30000 (30 seconds). |
| Validation Query | (String) The SQL query used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server). |
| Test On Return | (boolean) Used to indicate if objects will be validated before returned to the pool. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. The default value is false. |
| Test On Borrow | (boolean) Used to indicate if objects will be validated before borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval. Default value is false. |
| Test While Idle | (boolean) The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. The default value is false and this property has to be set in order for the pool cleaner/test thread to run (also see timeBetweenEvictionRunsMillis). |
| Time Between Eviction Runs Mills | (int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections. The default value is 5000 (5 seconds). |
| Minimum Evictable Idle Time | (int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction. The default value is 60000 (60 seconds). |
| Remove Abandoned | (boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned. The default value is false. |
| Remove Abandoned Timeout | (int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have. |
| Log Abandoned | (boolean) Flag to log stack traces for application code which abandoned a Connection. Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated. The default value is false. |
| Auto Commit | (boolean) The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default (If not set then the setAutoCommit method will not be called.) |
| Default Read Only | (boolean) The default read-only state of connections created by this pool. If not set then the setReadOnly method will not be called. (Some drivers don't support read only mode, ex: Informix) |
| Default Catalog | (String) The default catalog of connections created by this pool. |
| Validator Class Name | (String) The name of a class which implements the org.apache.tomcat.jdbc.pool.Validator interface and provides a no-arg constructor (may be implicit). If specified, the class will be used to create a Validator instance which is then used instead of any validation query to validate connections. The default value is null. An example value is com.mycompany.project.SimpleValidator. |
| Connection Properties | (String) The connection properties that will be sent to our JDBC driver when establishing new connections. Format of the string must be [propertyName=property;]* NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here. The default value is null. |
| Init SQL | The ability to run a SQL statement exactly once, when the connection is created. |
| JDBC Interceptors | Flexible and pluggable interceptors to create any customizations around the pool, the query execution and the result set handling. |
| Validation Interval | (long) avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds). |
| JMX Enabled | (boolean) Register the pool with JMX or not. The default value is true. |
| Fair Queue | (boolean) Set to true if you wish that calls to getConnection should be treated fairly in a true FIFO fashion. This uses the org.apache.tomcat.jdbc.pool.FairBlockingQueue implementation for the list of the idle connections. The default value is true. This flag is required when you want to use asynchronous connection retrieval. Setting this flag ensures that threads receive connections in the order they arrive. During performance tests, there is a very large difference in how locks and lock waiting is implemented. When fairQueue=true there is a decision making process based on what operating system the system is running. If the system is running on Linux (property os.name=Linux. To disable this Linux specific behavior and still use the fair queue, simply add the property org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true to your system properties before the connection pool classes are loaded. |
| Abandon When Percentage Full | (int) Connections that have been abandoned (timed out) wont get closed and reported up unless the number of connections in use are above the percentage defined by abandonWhenPercentageFull. The value should be between 0-100. The default value is 0, which implies that connections are eligible for closure as soon as removeAbandonedTimeout has been reached. |
| Max Age | (long) Time in milliseconds to keep this connection. When a connection is returned to the pool, the pool will check to see if the now - time-when-connected > maxAge has been reached, and if so, it closes the connection rather than returning it to the pool. The default value is 0, which implies that connections will be left open and no age check will be done upon returning the connection to the pool. |
| Use Equals | (boolean) Set to true if you wish the ProxyConnection class to use String.equals and set to false when you wish to use == when comparing method names. This property does not apply to added interceptors as those are configured individually. The default value is true. |
| Suspect Timeout | (int) Timeout value in seconds. Default value is 0. Similar to to the removeAbandonedTimeout value but instead of treating the connection as abandoned, and potentially closing the connection, this simply logs the warning if logAbandoned is set to true. If this value is equal or less than 0, no suspect checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message gets logged and a JMX notification gets sent once. |
| Alternate User Name Allowed | (boolean) By default, the jdbc-pool will ignore the DataSource.getConnection(username,password) call, and simply return a previously pooled connection under the globally configured properties username and password, for performance reasons. The pool can however be configured to allow use of different credentials each time a connection is requested. To enable the functionality described in the DataSource.getConnection(username,password) call, simply set the property alternateUsernameAllowed to true. Should you request a connection with the credentials user1/password1 and the connection was previously connected using different user2/password2, the connection will be closed, and reopened with the requested credentials. This way, the pool size is still managed on a global level, and not on a per schema level. The default value is false. |
Infor
To find more details on data source configuration parameters, refer to http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
Custom Data Source
If you select the "Data Source Type" as Custom, the following screen will appear.
- Data Source Type : Custom
- Custom Data Source Type : Described below.
- Name : Name of the data source (This value should be unique)
- Description : Description of the data source
- Configuration : XML configuration of the data source
Custom Data Source Type
The actual type of the custom data source can be either "DS_CUSTOM_TABULAR" or "DS_CUSTOM_QUERY". There are two options cover most of the common business use cases.
Custom Tabular Data Sources
Tabular data sources are used when there is a data source which represents its data using tables, where a set of named tables contains data rows that can be queried later.
To implement tabular data sources, the interface "org.wso2.carbon.dataservices.core.custom.datasource.TabularDataBasedDS" is used. A sample implementation of tabular custom data source can be found at InMemoryDataSource.
A tabular data source is typically associated with an SQL data services query. This is done by internally using our own SQL parser to execute SQL against the custom data source. A sample data service descriptor on how this is done can be found at InMemoryDSSample. Also, this is supported in Carbon data sources, with the data source reader implementation "org.wso2.carbon.dataservices.core.custom.datasource.CustomTabularDataSourceReader". A sample Carbon data source configuration file can be found at <CARBON_HOME>\repository\conf\datasources\custom-datasources.xml.
Custom Query Data Sources
Custom query based data sources are created when there is a data source which has some form of a query expression support.
To implement , the interface "org.wso2.carbon.dataservices.core.custom.datasource.CustomQueryBasedDS" is used. Any non-tabular data source can be created using the query-based approach. Even if the target data source does not have a query expression format, users can create their own and it. For example, any NoSQL type data source can be supported using this type of a data source.
A sample implementation of a query-based custom data source can be seen at EchoDataSource. A sample data service descriptor with custom query data sources can be found in action in InMemoryDSSample. This is supported in Carbon data sources, with the data source reader implementation org.wso2.carbon.dataservices.core.custom.datasource.CustomQueryDataSourceReader. A sample of a Carbon data source configuration file can be found at <CARBON_HOME>\repository\conf\datasources\custom-datasources.xml.
In the "init" methods of all custom data sources, user-supplied properties will be parsed to initialize the data source accordingly. Also, a property named "__DATASOURCE_ID__", which contains a UUID to uniquely identify the current data source, will be passed. This can be used by custom data source authors to identify the data sources accordingly. For example, scenarios like data source instances communicating within a server cluster for data synchronization.
Shown below is an example configuration of a custom data source of type 'DS_CUSTOM_TABULAR'.
4. Once the values are entered, click "Save".
5. You have created a carbon data source. Also refer to edit and delete Data Sources .