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 DB script paths

...

Table of Content Zone
locationtop

Step 1 - Set up the database

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

Note

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

Step 2 - Create the datasource connection for the Metrics database

A datasource is used to establish the connection to a database. By default, the datasource connection for the Metrics database is configured in the metrics-datasources.xml file. This datasource configuration points to the default  H2 databases, which is 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.

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

  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. 

    Multiexcerpt include
    MultiExcerptNamedatasourceConfig
    PageWithExcerptChanging the Default API-M Databases

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

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

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

Step 3 - Create database tables in the Metrics database 

To create the database tables, connect to the databases that you created earlier and run the scripts provided in the product pack. 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.

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

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