This documentation is for WSO2 API Manager 2.1.0 View documentation for the latest release.
Changing the Default API-M Databases - API Manager 2.1.0 - WSO2 Documentation

All docs This doc
||
Skip to end of metadata
Go to start of metadata

When you use WSO2 API Manager (WSO2 API-M), you need the following databases in addition to the Carbon database. By default, WSO2 API-M is shipped with embedded H2 databases for the following in addition to the Carbon database. These databases are stored in the  <API-M_HOME>/repository/database  directory.

  • WSO2AM_DB: For API-M-specific data.

  • WSO2MB_DB: For message brokering data.

  • WSO2METRICS_DB : For storing data for Metrics monitoring.

For instructions on changing the default Carbon database, see Changing the Carbon Database in the WSO2 Product Administration Guide.

Database Capacity

When planning the capacity of the underlying databases, note that the database holding the Access Tokens (WSO2AM_DB) and Statistics Data (WSO2AM_STATS_DB) will grow with the usage and the traffic on the gateway. To remove historical data see Removing Unused Tokens from the Database and Purging Analytics Data

Given below are the steps you need to follow in order to change the default databases listed above.


Step 1 - Set up the database

You can set up the following database types for the API-M-specific databases:

Note that we recommend to use Fail Over configuration over Load Balanced configuration with the MySQL clusters.


Step 2 - Create the datasource connection

A datasource is used to establish the connection to a database. By default, datasource connections for the API-M database, API-M statistics database, and the Message Brokering database are configured in the master-datasources.xml file. The datasource connection for the Metrics database is configured in the metrics-datasources.xml file. These datasource configurations point to the default  H2 databases, which are shipped with the product. After setting up new databases to replace the default H2 databases, you can either change the default configurations in the above-mentioned files or configure new datasources.

Create the datasource connection for the API-M database

Follow the steps below.

  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. 

     Optionally, 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.

    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.

    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.

    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.

    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.

    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:

        -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.

        <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.

        <datasource>
             ...
             <definition type="RDBMS">
                 <configuration>
                       ...
        			   <defaultAutoCommit>false</defaultAutoCommit>	
        			   <rollbackOnReturn>true</rollbackOnReturn>
        			   ...
                 </configuration>
             </definition>
        </datasource>

    <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>
    <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</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>         
    <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>
    <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>
    
    <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>				
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>

Create the datasource connection for the MB database (MB Store in WSO2 API-M)

Follow the steps below.

  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. Further, be sure to set the  <defaultAutoCommit>  element to false for the MB database. 

     Optionally, 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.

    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.

    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.

    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.

    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.

    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:

        -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.

        <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.

        <datasource>
             ...
             <definition type="RDBMS">
                 <configuration>
                       ...
        			   <defaultAutoCommit>false</defaultAutoCommit>	
        			   <rollbackOnReturn>true</rollbackOnReturn>
        			   ...
                 </configuration>
             </definition>
        </datasource>

    If you are using PostgresSQL, make sure to remove the <validationQuery> property from the datasource configuration.

    <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>
    <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</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>         
    <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>
    <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>
    
    <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>
    			    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
           </definition>
    </datasource>

Create the datasource connection for the Metrics database

Follow the steps below.

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

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

     Optionally, 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.

    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.

    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.

    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.

    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.

    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:

        -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.

        <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.

        <datasource>
             ...
             <definition type="RDBMS">
                 <configuration>
                       ...
        			   <defaultAutoCommit>false</defaultAutoCommit>	
        			   <rollbackOnReturn>true</rollbackOnReturn>
        			   ...
                 </configuration>
             </definition>
        </datasource>

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

Create the datasource connection for the Analytics database

This section is only applicable if you have downloaded the WSO2 API Analytics distribution to use WSO2 API Analytics with WSO2 API-M.

The API Manager integrates with the WSO2 Analytics platform to provide reports, statistics, and graphs on the APIs deployed in WSO2 API Manager. You can then configure alerts to monitor these APIs, and detect unusual activity, manage locations via geo location statistics, and carry out detailed analysis of the logs.

Follow the steps below to create the datasource connection for the Analytics database:

When working with Analytics, ensure that the WSO2AM_DB database is of the same RDBMS type as the Analytics database. For example, if the Analytics related DBs are created in MySQL, the API-M databases (WSO2AM_DB) should also be created in MySQL.

The following is a list of database versions that are compatible with WSO2 API-M Analytics.

  • Postgres 9.5 and later
  • MySQL 5.6
  • MySQL 5.7 
  • Oracle 12c
  • MS SQL Server 2012
  • DB2
  1. Open the <API-M_ANALYTICS_HOME>/repository/conf/datasources/analytics-datasources.xml file. Note that two datasources named as WSO2_ANALYTICS_EVENT_STORE_DB and WSO2_ANALYTICS_PROCESSED_DATA_STORE_DB are configured by default to point to the H2 databases.
  2. Create two database schemas in your database server (MySQL, Oracle, etc) for the two datasources, and change the configurations of those datasources to point to the relevant schemas. A sample configuration is given below. 

    The database user you provide here requires permissions to create tables. 

    Note that you do not need to run the database scripts against the created databases as the tables for the datasources are created at runtime.


    <datasource>
        <name>WSO2_ANALYTICS_EVENT_STORE_DB</name>
        <description>The datasource used for analytics record store</description>
        <definition type="RDBMS">
            <configuration>
                <url>jdbc:mysql://localhost:3306/stats_200?autoReconnect=true&amp;relaxAutoCommit=true</url>
                <username>root</username>
                <password>root</password>
                <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                <maxActive>50</maxActive>
                <maxWait>60000</maxWait>
                <testOnBorrow>true</testOnBorrow>
                <validationQuery>SELECT 1</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>false</defaultAutoCommit>
            </configuration>
        </definition>
    </datasource>
    • If you are using Oracle, its recommended to increase the DB block size as described in http://www.oratable.com/ora-01450-maximum-key-length-exceeded/, to avoid the error 'ORA-01450: maximum key length (6398) exceeded'.

    • If you are using DB2, run this script before you start the WSO2 API-M Analytics server.

    • If you are using MySQL 5.7, open <API-M_ANALYTICS_HOME>/repository/conf/analytics/spark/spark-jdbc-config.xml and configure the stringType property under the typeMapping element which is under <database name="mysql"> element as follows.
      <stringType>VARCHAR(100)</stringType>
    If you are using MSSQL, add the SendStringParametersAsUnicode property to the database connection URL in the data source configuration in the <API-M_ANALYTICS_HOME>/repository/conf/datasources/analytics-datasources.xml file as shown below to avoid deadlock issues that are caused when the same table row is updated in two or more sessions at the same time.

    <url>SQLSERVER_JDBC_URL;SendStringParametersAsUnicode=false</url>

  3. Share the WSO2AM_STATS_DB datasource between WSO2 API-M and WSO2 API-M Analytics as follows.
    1. Open the <API-M_HOME>/repository/conf/datasources/master-datasources.xml file and make sure that a configuration for the WSO2AM_STATS_DB datasource is included. The default configuration is as follows.

      <datasource>
         <name>WSO2AM_STATS_DB</name>
         <description>The datasource used for setting statistics to API Manager</description>
         <jndiConfig>
            <name>jdbc/WSO2AM_STATS_DB</name>
         </jndiConfig>
         <definition type="RDBMS">
            <configuration>
               <url>jdbc:mysql://localhost:3306/WSO2AM_STATS_DB?autoReconnect=true&amp;relaxAutoCommit=true</url>
               <username>root</username>
               <password>root</password>
               <driverClassName>com.mysql.jdbc.Driver</driverClassName>
               <maxActive>50</maxActive>
               <maxWait>60000</maxWait>
               <testOnBorrow>true</testOnBorrow>
               <validationQuery>SELECT 1</validationQuery>
               <validationInterval>30000</validationInterval>
               <defaultAutoCommit>false</defaultAutoCommit>
            </configuration>
         </definition>
      </datasource>

      you need to enable analytics in publisher, store and gateway nodes. However, you need to add this datasource configuration in gateway nodes. Following table provides more information on Analytics usage of API Manager components in a distributed environment.

      ComponentEnable statisticsEvents PublishedRead statsDB
      Gateway_ManagerYES only if accept requestYES only if accept requestNO
      Gateway_workerYESYESNO
      Key ManagerNONONO
      PublisherYESNOYES
      StoreYESYESYES
      Traffic ManagerNONONO

      You do not need to enable analytics in Key Manager and Traffic Manager nodes as those components do not read or publish statistics. Though gateway nodes publish events, they are not reading statistics database. Therefore you are not required to add the WSO2AM_STATS_DB datasource configuration in gateway nodes. Publisher node read statistics but not publishing events. Therefore you can disable event publisher initialization at startup in publisher by setting <SkipEventReceiverConnection>   value to true in <PUBLISHER_HOME>/repository/conf/api-manager.xml.API Store nodereads statistics and also publish events. Therefore we need to keep the statsource configuration for statsDB in Store node as well.

    2. Open the <API-M_ANALYTICS_HOME>/repository/conf/datasources/stats-datasources.xml file and make sure that the same configuration in the <API-M_HOME>/repository/conf/datasources/master-datasources.xml file (mentioned in the previous sub step) is added in it.
  4. Create a schema in your database server similar to the WSO2AM_STATS_DB datasource. Make sure that this datasource points to the relevant schema. 

    The database user you provide here requires permissions to create tables.

  5. Download and copy the relevant database driver JAR file to the <API-M_ANALYTICS_HOME>/repository/components/lib directory.
  6. Start the WSO2 API-M Analytics server.

Troubleshooting

If you are configuring API-M Analytics with MSSQL  and you get an error when you start the API-M Analytics server stating that a table cannot have more than one clustered index, follow the steps below.

  1. Open the <API-M_ANALYTICS_HOME>/repository/components/features/org.wso2.carbon.analytics.spark.server_VERSION/spark-jdbc-config.xml file.
  2. Update the value for the <indexCreateQuery> element of the MSSQL database as shown below.

    <database name="Microsoft SQL Server">
    	<indexCreateQuery>CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}})</indexCreateQuery>
    </database>
  3. Restart the server for the above changes to take effect.

Step 3 - Create database tables

To create the database tables, connect to the databases that you created earlier and run the scripts provided in the product pack.

Create database tables in the API-M database 

The DB scripts corresponding to the database type are provided in the <API-M_HOME>/dbscripts/apimgt directory.

To create the necessary database tables:

  1. Connect to the database and run the relevant script.
    For example, run the following command to create the API-M tables in a MySQL database.

    mysql -u root -p -DWSO2AM_DB < '<API-M_HOME>/dbscripts/apimgt/mysql.sql';

    <API-M_HOME>/dbscripts/apimgt/mysql.sql is the script that should be used for MySQL 5.6 and prior versions. If you database is MySQL 5.7 or later version, use  <API-M_HOME>/dbscripts/apimgt/mysql5.7.sql script file.

  2. Restart the WSO2 API-M server.

Create database tables in the MB database 

The DB scripts corresponding to the database type are provided in the <API-M_HOME>/dbscripts /mb-store directory.

To create the necessary database tables:

  1. Connect to the database and run the relevant script. 
    For example, run the following command to create the MB tables in a MySQL database.

    mysql -u root -p -DWSO2MB_DB < '<API-M_HOME>/dbscripts/mb-store/mysql.sql';

    <API-M_HOME>/dbscripts/apimgt/mysql.sql is the script that should be used for MySQL 5.6 and prior versions. If you database is MySQL 5.7 or later version, use  <API-M_HOME>/dbscripts/apimgt/mysql5.7.sql script file.

  2. Restart the WSO2 API-M server.

Create database tables in the Metrics database 

The DB scripts corresponding to the database type are provided in the <API-M_HOME>/dbscripts/metrics directory.

To create the necessary database tables:

  1. Connect to the database and run the relevant script. 
    For example, run the following command to create the MB tables in a MySQL database.

    mysql -u root -p -DWSO2_METRICS_DB < '<API-M_HOME>/dbscripts/metrics/mysql.sql';

    <API-M_HOME>/dbscripts/apimgt/mysql.sql is the script that should be used for MySQL 5.6 and prior versions. If you database is MySQL 5.7 or later version, use  <API-M_HOME>/dbscripts/apimgt/mysql5.7.sql script file.

  2. Restart the WSO2 API-M server. 

Create database tables when the server starts

You can create database tables automatically when starting the product for the first time by using the -Dsetup parameter as follows:

  • For Windows: <API-M_HOME>/bin/wso2server.bat -Dsetup

  • For Linux: <API-M_HOME>/bin/wso2server.sh -Dsetup


For instructions on changing the default Carbon database, see Changing the Carbon Database in the WSO2 Product Administration Guide.

  • No labels