Configuring WSO2 API-M with API-M_ANALYTICS by using MSSQL Database In Windows Environment


WSO2 APIM Analytics collects statistics of WSO2 API Manager, analyzes them and publishes them in a meaningful manner. WSO2 API manager embedded with the ability to view statistics of the operations carried out such as usage comparison, monitoring Throttled Out Requests, API last access time and so on. To view so, the user has to configure an analytics server with API Manager and it allows to view statistics based on the given criteria. By default APIM Analytics is configured to port offset 1,the APIM is running on offset 0.

* Downloading WSO2 API-M and API-M Analytics

Follow the instructions below to download the binary distribution of WSO2 API-M Analytics.The binary distribution contains the binary files for both MS Windows, and Linux-based operating systems. You can also download, and build the source code.

  1. Go to the WSO2 API Manager page.
  2. Click DOWNLOAD SERVER to download the WSO2 API Manager product pack.
  3. Click DOWNLOAD ANALYTICS to download the WSO2 API-M Analytics product pack.

* Installing WSO2 API-M Analytics and API-M

Take the following steps to install WSO2 API_M and APIM Analytics on Windows. Before ensure that you have met the Installation Prerequisites including Setting up JAVA_HOME using the system properties.(Installations are similar to WSO2 DAS )

  1. Download the latest version of the API-M and API-M Analytics.
  2. Extract the archive file to a dedicated directory for the API-M and API-M Analytics, which will hereafter be referred to as <API-M_HOME> and<API-M-ANALYTICS_HOME> respectively.
  3. Installing and setting up snappy-java.
  • Download the snappy-java_1.1.1.7.jar from here.
  • Copy the jar to <PRODUCT_HOME>\repository\components\lib (Both in <API-M_HOME> and<API-M-ANALYTICS_HOME>).
  • If the product server is currently running, restart it to apply the changes.

* Configuring WSO2 API Manager to publish statistic

Follow the instructions below to do the required configurations for WSO2 API-M to publish statistics in the WSO2 API-M Analytics server.

  1. Open the <API-M_HOME>/repository/conf/api-manager.xml file.
  2. Under the <Analytics> sub element, set the Enabled parameter to true.<Enabled>true</Enabled>
  3. If API-M and API-M_ANALYTICS are configured in the same instance, the default values configured for <DASRestApiURL> and <DASServerURL> under the <Analytics> sub element in <API-M_HOME>/repository/conf/api-manager.xml file will work. You can configure those elements further if required. You can find the “Configure the following parameters if required” section under Configuring WSO2 API Manager to publish statistics in Standard Setup of WSO2 APIM analytics configuration documentation.
  4. Save the changes.
  5. Configuring the Log Analyzer

Follow the steps below to configure the Log Analyzer.

  • Add DAS_AGENT to the end of the root logger in the <API- M_HOME>/repository/conf/log4j.properties file as shown below.
    log4j.rootLogger=<other loggers>, DAS_AGENT.
  • Find the Configuring the Log Analyzer in in Standard Setup of WSO2 APIM analytics configuration documentation and make sure that the configurations need to be modified if required.
  • If the WSO2 API Manager was started before these configurations were done, restart it in order to apply the changes.

6. Configuring Database MSSQL

You need to set up MSSQL before following the steps to configure your product with MSSQL. For more information, see Setting up Microsoft SQL

  • Download and copy the MSSQL database driver JAR file to <ANALYTICS_HOME>/repository/components/lib directory as well a <APIM_HOME> /repository/components/lib directory.

Setting up datasource configuration

  1. Open <API MANALYTICS_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 for the two datasources, and change the configurations of those datasources to point to the relevant schemas. A sample configuration is given below.

<datasource>
<name>WSO2_ANALYTICS_EVENT_STORE_DB</name>
<description>The datasource used for registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration> <url>jdbc:sqlserver://<IP>;databaseName=<DB_NAME>;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE</url>
<username>tester</username>
<password>tester</password>
<driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
</configuration>
</definition>
</datasource>

3. Share the WSO2AM_STATS_DB datasource between WSO2 API-M and WSO2 API-M Analytics as follows. 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 registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration> <url>jdbc:sqlserver://<IP>;databaseName=<DB_NAME>;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE</url>
<username>tester</username>
<password>tester</password>
<driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
</configuration>
</definition>
</datasource>

4. Open the <API-ANALYTICS_HOME>/repository/conf/datasources/stats-datasources.xml file where a datasource named WSO2AM_STATS_DB is configured by default to point to H2 databases 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.

5. 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.
Start the WSO2 API-M Analytics server by using the -Dsetup parameter as follows:
<API-M-ANALYTICS_HOME>/bin/wso2server.bat -Dsetup

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.
* Open the <API-M Analytics_HOME> /repository/components/features/ org.wso2.carbon.analytics.spark.server_VERSION/ spark-jdbc-config.xml file.
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>

* Restart the server.
* You can create database tables automatically when starting the product for the first time by using the -Dsetup parameter as follows:
<PRODUCT_HOME>/bin/wso2server.bat -Dsetup

Note:

The WSO2 API-M analytics is basically similar to the WSO2 DAS having the API-M analytics features installed.
When starting the two servers always start the APIM Analytics server first and then start the API Manager server.

Open the <APIM_HOME>/repository/conf/datasources/master-datasources.xml file. This file contains the different datasources used by WSO2 API Manager. By default, the API Manager connects to the local H2 database and it is recommended to use a separate RDBMS server for a production deployment.

Follow the steps below to integrate the API Manager with an external database (in this case, an external MSSQL Server). Create the required databases.

Update the existing WSO2AM_DB with the configuration given below.
Add two new entries, WSO2GOV_DB and WSO2UM_DB, as shown below.
Create the required databases.

<datasource>
<name>WSO2AM_DB</name>
<description>The datasource used for registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration> <url>jdbc:sqlserver://<IP>;databaseName=<DB_NAME>;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE</url>
<username>tester</username>
<password>tester</password>
<driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
</configuration>
</definition>
</datasource>

<datasource>
<nameWSO2GOV_DB</name>
<description>The datasource used for registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration> <url>jdbc:sqlserver://<IP>;databaseName=<DB_NAME>;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE</url>
<username>tester</username>
<password>tester</password>
<driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
</configuration>
</definition>
</datasource>

<datasource>
<name>WSO2UM_DB</name>
<description>The datasource used for registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration> <url>jdbc:sqlserver://<IP>;databaseName=<DB_NAME>;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE</url>
<username>tester</username>
<password>tester</password>
<driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>30000</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
</configuration>
</definition>
</datasource>

Make sure to create APIs and publish in publisher of API-M after you followed stated database configurations.

Thivya Mahenthirarasa

Written by

Software Engineer at WSO2, Just enjoy your life with ensuring that no one gets hurt because of you.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade