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

All docs This doc
Expand all   Collapse all

Versions Compared

Old Version 13

changes.mady.by.user Shani Ranasinghe

Saved on

compared with

New Version Current

changes.mady.by.user Mariangela Devona Christina Hills

Saved on

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.

...

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>
      <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>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_MB_STORE_DB</name>
   	  <description>The datasource used for message broker database</description>
      <jndiConfig>
            <name>WSO2MBStoreDB</name>
      </jndiConfig>
      <definition type="RDBMS">
            <configuration>
                <url>jdbc:sqlserver://localhost:1433;databaseName=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>true</defaultAutoCommit>
            </configuration>
      </definition>
</datasource>
    Localtab
    titleOracle
    Code Block
    languagexml
    <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>true</defaultAutoCommit>
            </configuration>
      </definition>
</datasource>
    Localtab
    titleIBM DB2
    Code Block
    languagehtml/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>true</defaultAutoCommit>
            </configuration>
       </definition>
</datasource>
    Localtab
    titlePostgreSQL
    Code Block
    languagexml
    <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>

Create the datasource connection for the

...

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

    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>net.sourceforge.jtds.jdbc.Driver</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>

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.

...

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.

...

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.