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: DOCUMENTATION-8502

...

  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. 

    Multiexcerpt
    MultiExcerptNamedatasourceConfig
    Expand
    titleOptionally, you can update the other elements for your database connection.
    ElementDescription
    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
    titleMySQL
    Code Block
    languagehtml/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
    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>
       	  <description>The datasource used for API Manager database</description>
          <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
          </jndiConfig>
          <definition type="RDBMS">
                <configuration>
                    <url>jdbc:jtds:sqlserver://localhost:1433/WSO2AM_DB</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>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
    titleIBM 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: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
    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>
                    <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>

...