This documentation is for WSO2 API Manager 2.1.0. View documentation for the latest release.

All docs This doc

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: changed MSSQL DB names

...

  1. Open the <API-M _HOME>/repository/conf/datasources/master -datasources.xml file and locate the <datasource> configuration element.

  2. Update the URL pointing to your database, the username and password required to access the database, and the driver details as shown below. 

    Multiexcerpt
    MultiExcerptNamedatasourceConfig
    Expand
    titleOptionally, you can update the other elements for your database connection.
    ElementDescription
    maxActive The maximum number of active connections that can be allocated at the same time from this pool. Enter any negative value to denote an unlimited number of active connections.
    maxWait The maximum number of milliseconds that the pool waits (when there are no available connections) for a connection to be returned before throwing an exception. You can enter zero or a negative value to wait indefinitely.
    minIdle The minimum number of active connections that can remain idle in the pool without extra ones being created. You can enter zero to create none.

    testOnBorrow

    The indication of whether objects are validated before being borrowed from the pool. If the object fails to validate, it is dropped from the pool, and another attempt is made to borrow another.
    validationQuery The SQL query that is used to validate connections from this pool before returning them to the caller.
    validationInterval The indication to avoid excess validation, and only run validation at the most, at this frequency (time in milliseconds). If a connection is due for validation but has been validated previously within this interval, it is not validated again.

    Anchor
    default_auto_commit
    default_auto_commit
    defaultAutoCommit

    This property is only applicable to the MB Store database of WSO2 APIM, where this property should be explicitly set to false. In all other database connections explained above, auto committing is enabled or disabled at the code level as required for that database, i.e., the default auto commit configuration specified for the RDBMS driver will be effective instead of this property element. Note that auto committing is typically enabled for an RDBMS by default.

    When auto committing is enabled, each SQL statement will be committed to the database as an individual transaction, as opposed to committing multiple statements as a single transaction.

    Info

    For more information on other parameters that can be defined in the <API-M_HOME>/conf/datasources/ master-datasources.xml file, see Tomcat JDBC Connection Pool.

    Warning

    The following elements are available only as a WUM update and is effective from 14th September 2018 (2018-09-14).  For more information, see Updating WSO2 Products.
    This WUM update is only applicable to Carbon 4.4.11 and will be shipped out-out-the-box with Carbon versions newer than Carbon 4.4.35. For more information on Carbon compatibility, see Release Matrix.

    ElementDescription
    commitOnReturnIf defaultAutoCommit=false, then you can set commitOnReturn=true, so that the pool can complete the transaction by calling the commit on the connection as it is returned to the pool. However, If rollbackOnReturn=true then this attribute is ignored. The default value is false.
    rollbackOnReturnIf defaultAutoCommit=false, then you can set rollbackOnReturn=true so that the pool can terminate the transaction by calling rollback on the connection as it is returned to the pool. The default value is false.

    Anchor
    connectionpool
    connectionpool
    Configuring the connection pool behavior on return
    When a database connection is returned to the pool, by default  the product rollsback the pending transactions if defaultAutoCommit=true . However, if required you can disable the latter mentioned default behavior by disabling the ConnectionRollbackOnReturnInterceptor, which is a JDBC-Pool JDBC interceptor, and setting the connection pool behavior on return via the datasource configurations by using the following options.

    Warning

    Disabling the ConnectionRollbackOnReturnInterceptor is only possible with the WUM update and is effective from 14th September 2018 (2018-09-14). For more information on updating WSO2 API Manager, see Updating WSO2 Products. This WUM update is only applicable to Carbon 4.4.11.

    • Configure the connection pool to commit pending transactions on connection return
      1. Navigate to either one of the following locations based on your OS.
        • On Linux/Mac OS:  <PRODUCT_HOME>/bin/wso2server.sh/
        • On Windows:  <PRODUCT_HOME>\bin\wso2server.bat 
      2. Add the following JVM option:

        Code Block
        -Dndatasource.disable.rollbackOnReturn=true \
      3. Navigate to the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file.
      4. Disable the defaultAutoCommit by defining it as false.
      5. Add the commitOnReturn property and set it to true for all the datasources, including the custom datasources.

        Code Block
        languagehtml/xml
        <datasource>
             ...
             <definition type="RDBMS">
                 <configuration>
                       ...
        			   <defaultAutoCommit>false</defaultAutoCommit>
        			   <commitOnReturn>true</commitOnReturn>	
        			   ...
                 </configuration>
             </definition>
        </datasource>
    • Configure the connection pool to rollback pending transactions on connection return

      1. Navigate to the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file.
      2. Disable the defaultAutoCommit by defining it as false.

      3. Add the rollbackOnReturn property to the datasources.

        Code Block
        languagehtml/xml
        <datasource>
             ...
             <definition type="RDBMS">
                 <configuration>
                       ...
        			   <defaultAutoCommit>false</defaultAutoCommit>	
        			   <rollbackOnReturn>true</rollbackOnReturn>
        			   ...
                 </configuration>
             </definition>
        </datasource>
    Localtab Group
    Localtab
    titleMySQL
    Code Block
    languagehtml/xml
    <datasource>
          <name>WSO2AM_DB</name>
          <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2AM_DB</url>
                    <username></username>
                    <password></password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
    			    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>
    Localtab
    titleMS SQL
    Warning
    titleSetting the connection URL for MS SQL

    If you are using the MS SQL configuration as shown below, note that the SendStringParametersAsUnicode parameter should be set to ‘false’ in the database connection URL. This is necessary in order to overcome a limitation in the MS SQL client driver. Without this parameter, the database driver will erroneously convert VARCHAR data into NVARCHAR, and thereby lower the database performance.

    Code Block
    languagexml
    <datasource>
          <name>WSO2AM_DB</name>
       	  <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:sqlserver://localhost:1433;databaseName=AMWSO2AM_DB;SendStringParametersAsUnicode=false</url>
                    <username></username>
                    <password></password>
                    <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
                    <maxActive>200</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>         
    Localtab
    titleOracle
    Code Block
    languagexml
    <datasource>
          <name>WSO2AM_DB</name>
    	  <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhost:1521/orcl</url>
    				<username></username>
                    <password></password>
    				<driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                    <maxActive>100</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1 FROM DUAL</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>
    Localtab
    titleIBM DB2
    Code Block
    languagexml
    <datasource>
          <name>WSO2AM_DB</name>
          <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:db2://SERVER_NAME:PORT/WSO2AM_DB</url>
                    <username></username>
                    <password></password>
                    <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>360000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>
    
    Localtab
    titlePostgreSQL
    Code Block
    languagexml
    <datasource>
          <name>WSO2AM_DB</name>
          <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/WSO2AM_DB</url>
                    <username></username>
                    <password></password>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>	
    				<validationQuery>SELECT 1; COMMIT</validationQuery>			
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>

...

Localtab Group
Localtab
titleMySQL
Code Block
languagehtml/xml
<datasource>
      <name>WSO2_METRICS_DB</name>
      <description>The MySQL datasource used for WSO2 Carbon Metrics</description>
      <jndiConfig>
            <name>jdbc/WSO2MetricsDB</name>
      </jndiConfig>
      <definition type="RDBMS">
             <configuration>
                 <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                 <url>jdbc:mysql://localhost:3306/WSO2METRICS_DB</url>
                 <username>root</username>
                 <password>root</password>
                 <maxActive>50</maxActive>
                 <maxWait>60000</maxWait>
                 <minIdle>5</minIdle>
                 <testOnBorrow>true</testOnBorrow>
                 <validationQuery>SELECT 1</validationQuery>
                 <validationInterval>30000</validationInterval>
                 <defaultAutoCommit>true</defaultAutoCommit>
            </configuration>
      </definition>
</datasource>
Localtab
titleMS SQL
Warning
titleSetting the connection URL for MS SQL

If you are using the MS SQL configuration as shown below, note that the SendStringParametersAsUnicode parameter should be set to ‘false’ in the database connection URL. This is necessary in order to overcome a limitation in the MS SQL client driver. Without this parameter, the database driver will erroneously convert VARCHAR data into NVARCHAR, and thereby lower the database performance.

Code Block
languagexml
<datasource>
      <name>WSO2_METRICS_DB</name>
      <description>The MSSQL datasource used for WSO2 Carbon Metrics</description>
      <jndiConfig>
            <name>jdbc/WSO2MetricsDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <driverClassName>net.sourceforge.jtds.jdbc.Driver</driverClassName>
                <url>jdbc:sqlserver://localhost:1433;databaseName=wso2WSO2METRICS_metricsDB;SendStringParametersAsUnicode=false</url>
                <username>sa</username>
                <password>sa</password>
                <maxActive>200</maxActive>
                <maxWait>60000</maxWait>
                <minIdle>5</minIdle>
                <testOnBorrow>true</testOnBorrow>
                <validationQuery>SELECT 1</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>true</defaultAutoCommit>
            </configuration>
      </definition>
</datasource>       
Localtab
titleOracle
Code Block
languagexml
<datasource>
      <name>WSO2_METRICS_DB</name>
      <description>The Oracle datasource used for WSO2 Carbon Metrics</description>
      <jndiConfig>
            <name>jdbc/WSO2MetricsDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <driverClassName>oracle.jdbc.OracleDriver</driverClassName>
                <url>jdbc:oracle:thin:@localhost:1521/wso2_metrics</url>
                <username>scott</username>
                <password>tiger</password>
                <maxActive>100</maxActive>
                <maxWait>60000</maxWait>
                <minIdle>5</minIdle>
                <testOnBorrow>true</testOnBorrow>
                <validationQuery>SELECT 1 FROM DUAL</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>true</defaultAutoCommit>
                <databaseProps>
                    <property name="SetFloatAndDoubleUseBinary">true</property>
                </databaseProps>
            </configuration>
      </definition>
</datasource>
Localtab
titlePostgreSQL
Code Block
languagexml
<datasource>
      <name>WSO2_METRICS_DB</name>
      <description>The MSSQL datasource used for WSO2 Carbon Metrics</description>
      <jndiConfig>
            <name>jdbc/WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:postgresql://localhost:5432/wso2_metrics</url>
                <username></username>
                <password></password>
                <driverClassName>org.postgresql.Driver</driverClassName>
                <maxActive>80</maxActive>
                <maxWait>60000</maxWait>
                <minIdle>5</minIdle>
                <testOnBorrow>true</testOnBorrow>
				<validationQuery>SELECT 1; COMMIT</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>false</defaultAutoCommit>
            </configuration>
       </definition>
</datasource>

...