This documentation is for WSO2 API Manager 2.6.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: Corrected the path /DOCUMENTATION-8530

...

  • WSO2AM_DB: For API-M-specific data.

  • WSO2MB_DB: For message brokering data.

  • WSO2METRICS_DB : For storing data for Metrics monitoringmetrics monitoring.
    Note that the metrics database is disabled by default in API-M 2.6.0. However, if you need to work with metrics enable the metrics DB. For more information, see Enabling Metrics and Storage Types.
Info

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

Info
titleDatabase Capacity

When planning the capacity of the underlying databases, note that the database holding the Access Tokens (WSO2AM_DB) and Statistics Data (WSO2AMANALYTICS_STATS_DB) will grow with the usage and the traffic on the gateway. To remove However, purging has been enabled by default for the ANALYTICS_DB. For more information on the removal of 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.

...

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

...

  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  

    localtabgroup
    Multiexcerpt
    MultiExcerptNamedatasourceConfig
    Expand
    titleOptionally, you can update the other elements for your database connection.
    localtab<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>
    Element
    Code Block
    languagehtml/xml
    Description
    titleMySQL
    maxActiveThe 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.

    Localtab Group
    Localtab
    titleMySQL
    Code Block
    languagehtml/xml
    <datasource>
          <name>WSO2AM_DB</name>
          <description>The datasource used for API Manager database</description>
          <jndiConfig>
       <validationQuery>SELECT 1</validationQuery>        <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
     <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false</defaultAutoCommit>     <definition type="RDBMS">
            </configuration>    <configuration>
      </definition> </datasource>
    Localtab
    titleMS SQL
    Code Block
    languagexml
    <datasource>       <name>WSO2AM_DB</name>    	  <description>The datasource used for API Manager database</description><url>jdbc:mysql://localhost:3306/WSO2AM_DB</url>
                <jndiConfig>    <username></username>
            <name>jdbc/WSO2AM_DB</name>        <<password></jndiConfig>password>
          <definition type="RDBMS">         <driverClassName>com.mysql.jdbc.Driver</driverClassName>
       <configuration>                 <url>jdbc:jtds:sqlserver://localhost:1433/WSO2AM_DB</url><maxActive>80</maxActive>
                    <username><<maxWait>60000</username>maxWait>
    			                <password></password><minIdle>5</minIdle>
                    <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName><testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT <maxActive>200<1</maxActive>validationQuery>
                    <maxWait>60000<<validationInterval>30000</maxWait>
    validationInterval>
    				<defaultAutoCommit>true</defaultAutoCommit>
                   <minIdle>5</minIdle></configuration>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
       </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>
       	  <defaultAutoCommit>false</defaultAutoCommit><description>The datasource used           </configuration>
          </definition>
    </datasource>         
    Localtab
    titleOracle
    Code Block
    languagexml
    <datasource>
          <name>WSO2AM_DB</name>
    	  <description>The datasource used for for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhostsqlserver:1521/orcl</url>
    				/localhost:1433;databaseName=WSO2AM_DB;SendStringParametersAsUnicode=false</url>
                    <username></username>
                    <password></password>
    				<driverClassName>oracle
                    <driverClassName>com.microsoft.sqlserver.jdbc.driver.OracleDriver<SQLServerDriver</driverClassName>
                    <maxActive>100<<maxActive>200</maxActive>
                    <maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1 FROM DUAL<1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false<<defaultAutoCommit>true</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>         
    Localtab
    titleIBM DB2Oracle
    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>
                    oracle:thin:@localhost:1521/orcl</url>
    				<username></username>
                    <password></password>
                    <driverClassName>com.ibm.db2.jcc.DB2Driver<				<driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                    <maxActive>80<<maxActive>100</maxActive>
                    <maxWait>360000<<maxWait>60000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>1 FROM DUAL</validationQuery>
                    <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false<
                    <defaultAutoCommit>true</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>
    
    Localtab
    titlePostgreSQLIBM 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:postgresqldb2://localhostSERVER_NAME:5432PORT/WSO2AM_DB</url>
                    <username></username>
                    <password></password>
                    <driverClassName>org.postgresql.Driver<<driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000<<maxWait>360000</maxWait>
                    <minIdle>5</minIdle>
                    <testOnBorrow>true</testOnBorrow>				
                    <validationQuery>SELECT <validationInterval>30000<1</validationInterval>validationQuery>
                    <defaultAutoCommit>false<<validationInterval>30000</validationInterval>
    				<defaultAutoCommit>true</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.

    Note

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

    Localtab Group
    Localtab
    titleMySQL
    Code Block
    languagehtml/xml
    <datasource>
          <name>WSO2_MB_STORE_DB</name>
    
    <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>
    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>
                <description>The datasource used for message broker database</description> <url>jdbc:postgresql://localhost:5432/WSO2AM_DB</url>
             <jndiConfig>       <username></username>
         <name>WSO2MBStoreDB</name>       </jndiConfig>    <password></password>
      <definition type="RDBMS">             <configuration><driverClassName>org.postgresql.Driver</driverClassName>
                    <url>jdbc:mysql://localhost:3306/WSO2MB_DB</url><maxActive>80</maxActive>
                    <username><<maxWait>60000</username>maxWait>
                    <password><<minIdle>5</password>minIdle>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
    			    <minIdle>5</minIdle><testOnBorrow>true</testOnBorrow>
    				<validationQuery>SELECT 1; COMMIT</validationQuery>
                    <testOnBorrow>true<<validationInterval>30000</testOnBorrow>validationInterval>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
          </definition>
    </datasource>
    Localtab
    titleMS SQL
    Code Block
    languagexml

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

Note

The Message Broker (MB) DB should not be shared. The database should be local to each node. Therefore, when working with the MB database you can use the H2 database. However, if you wish to use another database, you can configure it as follows:

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. 

    Multiexcerpt include
    MultiExcerptNamedatasourceConfig
    PageWithExcerptChanging the Default API-M Databases

    Localtab Group
    Localtab
    titleMySQL
    Code Block
    languagehtml/xml
    <datasource>
          <name>WSO2_MB_STORE_DB</name>
          <description>The datasource used for message broker  database</description>
      <username></username>    <jndiConfig>
                <password><<name>WSO2MBStoreDB</password>name>
          </jndiConfig>
          <definition   <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>type="RDBMS">
                <configuration>
        <maxActive>200</maxActive>                 <maxWait>60000</maxWait><url>jdbc:mysql://localhost:3306/WSO2MB_DB</url>
                    <minIdle>5<<username></minIdle>username>
                    <testOnBorrow>true<<password></testOnBorrow>password>
                    <validationQuery>SELECT 1</validationQuery><driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <validationInterval>30000<<maxActive>80</validationInterval>maxActive>
                    <defaultAutoCommit>false<<maxWait>60000</defaultAutoCommit>maxWait>
    			     <minIdle>5</minIdle>
          </configuration>       </definition> </datasource>  <testOnBorrow>true</testOnBorrow>
          
    Localtab
    titleOracle
    Code Block
    languagexml
    <datasource>       <name>WSO2_MB_STORE_DB</name> 	  <description>The<validationQuery>SELECT datasource1</validationQuery>
    used for message broker database</description>       <jndiConfig>     <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>true</defaultAutoCommit>
          <name>WSO2MBStoreDB</name>       </jndiConfig>configuration>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:oracle:thin:@localhost:1521/orcl</url>
    				<username></username></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_MB_STORE_DB</name>
       	  <description>The datasource used for message broker <password><database</password>
    				<driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>description>
          <jndiConfig>
                <maxActive>100<<name>WSO2MBStoreDB</maxActive>name>
          </jndiConfig>
          <definition type="RDBMS">
     <maxWait>60000</maxWait>           <configuration>
         <minIdle>5</minIdle>                 <testOnBorrow>true</testOnBorrow><url>jdbc:sqlserver://localhost:1433;databaseName=WSO2MB_DB;SendStringParametersAsUnicode=false</url>
                    <validationQuery>SELECT 1 FROM DUAL</validationQuery><username></username>
                    <validationInterval>30000<<password></validationInterval>password>
                    <defaultAutoCommit>false</defaultAutoCommit><driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
                </configuration>    <maxActive>200</maxActive>
      </definition> </datasource>
    Localtab
    titleIBM DB2
    Code Block
    languagehtml/xml
    <datasource>       <name>WSO2_MB_STORE_DB</name>      <maxWait>60000</maxWait>
    <description>The datasource used for message broker database</description>       <jndiConfig>   <minIdle>5</minIdle>
             <name>WSO2MBStoreDB</name>       <<testOnBorrow>true</jndiConfig>testOnBorrow>
          <definition type="RDBMS">          <validationQuery>SELECT 1</validationQuery>
     <configuration>                 <url>jdbc:db2://SERVER_NAME:PORT/WSO2MB_DB</url><validationInterval>30000</validationInterval>
                    <username><<defaultAutoCommit>true</username>defaultAutoCommit>
                </configuration>
       <password></password>   </definition>
    </datasource>         
    Localtab
    titleOracle
    Code Block
    languagexml
    <datasource>
       <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>   <name>WSO2_MB_STORE_DB</name>
    	  <description>The datasource used for message broker database</description>
        <maxActive>80</maxActive>  <jndiConfig>
                <name>WSO2MBStoreDB</name>
      <maxWait>360000</maxWait>    </jndiConfig>
          <definition type="RDBMS">
                <minIdle>5</minIdle><configuration>
                    <testOnBorrow>true</testOnBorrow>
    <url>jdbc:oracle:thin:@localhost:1521/orcl</url>
    				<username></username>
                   <validationQuery>SELECT 1<<password></validationQuery>
    password>
    				<driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
                   <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>false</defaultAutoCommit> <maxActive>100</maxActive>
                </configuration>    <maxWait>60000</maxWait>
       </definition>
    </datasource>
    
    Localtab
    titlePostgreSQL
    Code Block
    languagexml
    <datasource>             <name>WSO2_MB_STORE_DB</name><minIdle>5</minIdle>
              <description>The datasource used for message broker database<<testOnBorrow>true</description>testOnBorrow>
          <jndiConfig>          <validationQuery>SELECT 1 FROM <name>WSO2MBStoreDB<DUAL</name>validationQuery>
            </jndiConfig>       <definition type="RDBMS"> <validationInterval>30000</validationInterval>
                <configuration>    <defaultAutoCommit>true</defaultAutoCommit>
                <url>jdbc:postgresql://localhost:5432/WSO2MB_DB</url></configuration>
          </definition>
    </datasource>
    Localtab
    titleIBM DB2
    Code Block
    languagehtml/xml
    <datasource>
          <name>WSO2_MB_STORE_DB</name>
       <username></username>   <description>The datasource used for message broker database</description>
          <jndiConfig>
    <password></password>            <name>WSO2MBStoreDB</name>
          <driverClassName>org.postgresql.Driver</driverClassName></jndiConfig>
          <definition type="RDBMS">
             <maxActive>80</maxActive>   <configuration>
                 <maxWait>60000</maxWait>   <url>jdbc:db2://SERVER_NAME:PORT/WSO2MB_DB</url>
                    <minIdle>5<<username></minIdle>username>
                    <testOnBorrow>true</testOnBorrow>
    			<password></password>
                    <validationInterval>30000</validationInterval><driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
                    <defaultAutoCommit>false</defaultAutoCommit><maxActive>80</maxActive>
                    </configuration><maxWait>360000</maxWait>
                    <<minIdle>5</definition>
    </datasource>
  3. Optionally, you can update the configuration elements given below for your database connection.

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.

...

titleMySQL

...

languagehtml/xml

...

  1. minIdle>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
           

...

  1.        

...

  1.  

...

  1.  

...

  1. <validationInterval>30000</validationInterval>
    				<defaultAutoCommit>true</defaultAutoCommit>
                </configuration>
     

...

  1.       </definition>
    </datasource>
    
    Localtab
    titlePostgreSQL
    Code Block
    languagexml
    <datasource>
        

...

  1.   <name>WSO2_MB_STORE_DB</name>
          

...

  1. <description>The datasource used for message broker database</description>
    

...

  1.  

...

  1.      <jndiConfig>
            

...

  1.     <name>WSO2MBStoreDB</name>
          </jndiConfig>
          

...

  1. <definition type="RDBMS">
                <configuration>
                    <url>jdbc:

...

  1. postgresql://localhost:5432/

...

  1. WSO2MB_

...

  1. DB</url>
                    

...

  1. <username></username>
                    

...

  1. <password></password>
                    

...

  1. <driverClassName>org.postgresql.Driver</driverClassName>
                    

...

  1. <maxActive>80</

...

  1. maxActive>
                    

...

  1. <maxWait>60000</

...

  1. maxWait>
                    

...

  1. <minIdle>5</

...

  1. minIdle>
                    <testOnBorrow>true</testOnBorrow>
    				<validationQuery>SELECT 

...

  1. 1; COMMIT</validationQuery>
    			    

...

  1. <validationInterval>30000</validationInterval>
                    

...

  1. <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
           </definition>
    </datasource>

...

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

Create the datasource connection for the Analytics database

Note

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:

Warning

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.

...

Optionally, add DB configurations

...

testOnBorrow

...

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.

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.

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

    <API-M_HOME>/dbscripts/mb-store/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/mb-store/mysql5.7.sql script file.

  2. Restart the WSO2 API-M server.

...

Create the datasource connection for the Analytics database

Note

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:

Warning

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.

Excerpt Include
Configuring APIM Analytics
Configuring APIM Analytics
nopaneltrue

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 in the <API-M_HOME>/dbscripts/mb-store directoryapimgt 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 API-M tables in a MySQL database.

    Code Block
    languagepowershell
    mysql -u root -p -DWSO2MBDWSO2AM_DB < '<API-M_HOME>/dbscripts/mb-storeapimgt/mysql.sql';
    Note

    <API-M_HOME>/dbscripts/mb-store/mb-storeapimgt/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/mb-store/mb-store/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/metricsmb-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.

    note
    Code Block
    languagepowershell
    mysql -u root -p -DWSO2DWSO2MB_METRICS_DB < '<API-M_HOME>/dbscripts/metricsmb-store/mysql-mb.sql';

    <API-M_HOME>/dbscripts/metrics/metrics/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/metrics/metrics/mysql5.7.sql script file.

  2. Restart the WSO2 API-M server. 
Excerpt
hiddentrue

Note to writers: Add this content at the end of the page if any of the additional databases is relevant to your product.

Changing the identity/storage/product-specific databases

>The topics above show how to change the WSO2_CARBON_DB, which is used to store registry and user manager information. If you changed the product-specific database that comes by default or set up a separate database for identity or storage related data, the instructions are the same. In summary:

  1. For the identity databaseAdd it to the <PRODUCT_HOME>/repository/conf/datasources/<check the correct path and add file name (something like master-datasources.xml)>
    For the storage databaseAdd it to the <PRODUCT_HOME>/repository/conf/datasources/<check the correct path and add file name (something like master-datasources.xml)
    For the product-specific databaseAdd it to the <PRODUCT_HOME>/repository/conf/<check the correct path and add file name (something like api-manager.xml)
  2. Create the database tables using the following scripts:

    For the identity databaseUse <PRODUCT_HOME>/dbscripts/identity/<check the correct path and add file name (something like mysql.sql)>
    For the storage databaseUse  <PRODUCT_HOME>/dbscripts/storage/<check the correct path and add file name (something like /mysql/resoucre.sql)>
    For the product-specific databaseUse  <PRODUCT_HOME>/dbscripts/<check the correct path and add file name (something like /apimgt/mysql.sql)> 
  3. Update the following configurations, with the defined datasourcename of the corresponding database configuration you added to the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file.

    • If you are setting up a separate database to store identity relateddata, update the following configurations in the <PRODUCT_HOME>/repository/conf/identity.xml file.

      Code Block
      languagexml
      <JDBCPersistenceManager>
      	<DataSource>
      		<!-- Include a data source name (jndiConfigName) from the set of datasources defined in master-datasources.xml -->
      		<Name>jdbc/WSO2CarbonDB</Name>
      	</DataSource>
      </JDBCPersistenceManager>

      Note to writers: For step 3, add content on the XML files which include references on any of the additional databases and require edits that are relevant to your product similar to the given example.