Page Comparison

...

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

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

Multiexcerpt

MultiExcerptName	datasourceConfig

Optionally, you can update the other elements for your database connection.

Element	Description
`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.
default_auto_commit `defaultAutoCommit`	This property is only applicable to the 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.

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.

Localtabgroup

Localtab

title	MySQL

Code Block

language	html/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

title	MS SQL

Warning

title	Setting 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

language	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:jtds:sqlserver://localhost:1433/WSO2AM_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

title	Oracle

Code Block

language	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: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

title	IBM DB2

Code Block

language	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: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

title	PostgreSQL

Code Block

language	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: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>

...

Note

MB Store database is used by the traffic manager instance. Each traffic manager instance must be pointed to a separate MB Store database, which is local to its own instance. This database cannot be shared with other traffic manager instances. The default embedded local H2 database could be used for the MB Store. There will not be any functional issues in using the default H2 database for the MB Store database.

Follow the steps below.

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

Update the URL pointing to your database, the username and password required to access the database, and the driver details as shown below. Further, be sure to set the <defaultAutoCommit> element to false for the MB database.

Multiexcerpt include

MultiExcerptName	datasourceConfig
PageWithExcerpt	Changing the Default API-M Databases

NoteIf you areusingPostgresSQL, make sure to remove the <validationQuery> property from the datasource configuration.

Localtabgroup

Localtab

title	MySQL

Code Block

language	html/xml

<datasource>
      <name>WSO2_MB_STORE_DB</name>
      <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:mysql://localhost:3306/WSO2MB_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

title	MS SQL

Warning

title	Setting 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

language	xml

<datasource>
      <name>WSO2_MB_STORE_DB</name>
   	  <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:jtds:sqlserver://localhost:1433/WSO2MB_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

title	Oracle

Code Block

language	xml

<datasource>
      <name>WSO2_MB_STORE_DB</name>
	  <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</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

title	IBM DB2

Code Block

language	html/xml

<datasource>
      <name>WSO2_MB_STORE_DB</name>
      <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:db2://SERVER_NAME:PORT/WSO2MB_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

title	PostgreSQL

Code Block

language	xml

<datasource>
      <name>WSO2_MB_STORE_DB</name>
      <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:postgresql://localhost:5432/WSO2MB_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>

...

Localtabgroup

Localtab

title	MySQL

Code Block

language	html/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

title	MS SQL

Warning

title	Setting 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

language	xml

<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:jtds:sqlserver://localhost:1433/wso2_metrics;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

title	Oracle

Code Block

language	xml

<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

title	PostgreSQL

Code Block

language	xml

<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<1; COMMIT</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>true<<defaultAutoCommit>false</defaultAutoCommit>
            </configuration>
       </definition>
</datasource>

...

Versions Compared

Old Version 11

New Version 12

Key