Page History
...
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 | ||
---|---|---|
| ||
When planning the capacity of the underlying databases, note that the database holding the Access Tokens ( |
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.
...
Open the <
API-M
_HOME>/repository/conf/datasources/master
-datasources.xml
file and locate the<datasource>
configuration element.Update the URL pointing to your database, the username and password required to access the database, and the driver details as shown below.
Multiexcerpt MultiExcerptName datasourceConfig Expand title Optionally, you can update the other elements for your database connection. Element Description 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. 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 title MySQL Code Block language html/xml <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>true</defaultAutoCommit> </configuration> </definition> </datasource>
Localtab title MS SQL Warning title Setting 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 convertVARCHAR
data intoNVARCHAR
, and thereby lower the database performance.Code Block language xml <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:sqlserver://localhost:1433/;databaseName=WSO2AM_DB<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 title Oracle Code Block language xml <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>true</defaultAutoCommit> </configuration> </definition> </datasource>
Localtab title IBM DB2 Code Block language xml <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>true</defaultAutoCommit> </configuration> </definition> </datasource>
Localtab title PostgreSQL Code Block language xml <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> <validationQuery>SELECT 1; COMMIT</validationQuery> <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.
...
Open the <API-M
_HOME>/repository/conf/datasources/master
-datasources.xml
file and locate the <datasource>
configuration element.
...
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.
Open the <
API-M
_HOME>/repository/conf/datasources/master
-datasources.xml
file and locate the<datasource>
configuration element.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 tofalse
for the MB database.Multiexcerpt include MultiExcerptName datasourceConfig PageWithExcerpt Changing the Default API-M Databases Localtab Group Localtab title MySQL Code Block language html/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 title MS SQL Warning title Setting 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 convertVARCHAR
data intoNVARCHAR
, and thereby lower the database performance.Code Block language 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:jtds:sqlserver://localhost:1433/;databaseName=WSO2MB_DB<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 title Oracle Code Block language 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: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 title IBM DB2 Code Block language html/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 title PostgreSQL Code Block language 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: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.
Open the <
API-M
_HOME>/repository/conf/datasources/metrics
-datasources.xml
file and locate the<datasource>
configuration element.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 MultiExcerptName datasourceConfig PageWithExcerpt Changing the Default API-M Databases
Localtab Group | |||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
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 |
...
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:
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 language powershell 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.- 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 |
Excerpt Include | ||||||
---|---|---|---|---|---|---|
|
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:
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 language powershell 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.- 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:
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 language powershell notemysql -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.- Restart the WSO2 API-M server.
Excerpt | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||
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
|