Oracle® Fusion Middleware
Part 2. Installation

API Gateway stores and maintains the monitoring and transaction data read by Oracle API Gateway Analytics in a JDBC-compliant database. This topic describes how to create and configure a database for use with API Gateway Analytics. It describes the prerequisites and shows an example of creating a database. It also shows how to setup the database tables or upgrade them from a previous version.

The prerequisites for setting up the database are as follows:

Install API Gateway Analytics

You must install Oracle API Gateway Analytics. For details on how to install API Gateway Analytics, see the Install API Gateway Analytics topic.

Install a JDBC database

You must install a JDBC-compliant database to store the API Gateway monitoring and transaction data. API Gateway Analytics provides setup scripts for the following databases:

For details on how to install your chosen JDBC database, see your database product documentation.

You must add the JDBC driver files for your chosen database to your API Gateway, API Gateway Analytics, and Policy Studio installations.

API Gateway Analytics reads message metrics from a database and displays this information in a visual format to administrators. This is the same database in which API Gateway stores its audit trail and message metrics data. You first need to create this database using the database product of your choice (MySQL, Microsoft SQL Server, Oracle, or IBM DB2). For details on how to do this, see the product documentation for your chosen database.

The following example shows creating a MySQL database:

mysql> CREATE DATABASE reports;
Query OK, 1 row affected (0.00 sec)

In this example, the database is named reports, but you can use whatever name you wish.

When you have created the database, the next step is to set up the database tables. You can do this by running the dbsetup command from the following API Gateway Analytics directory:

The following example command shows setting up new database tables:

> dbsetup.bat
New database
Schema successfully upgraded to: 001-topology

You can specify the following options to the dbsetup command:

The following are some examples of using dbsetup command options:

Connect to a named database

You can use the --dbname option to connect to a named database connection configured under the External Connections node in the Policy Studio tree. For example:

> dbsetup.bat --dbname=Oracle
Current schema version: 001-initial
Latest schema version: 001-topology
Schema successfully upgraded to: 001-topology

Connect to a database URL

You can use the --dburl option to manually connect to a database instance directly using a URL. For example:

> dbsetup.bat --dburl=jdbc:mysql://localhost/reports --dbuser=root --dbpass=admin
Current schema version: 001-initial
Latest schema version: 001-topology
Schema successfully upgraded to: 001-topology

Install a database

You can also use the --dburl option to set up a newly created database instance where none already exists. For example:

> dbsetup.bat --dburl=jdbc:mysql://localhost/reports --dbuser=root --dbpass=admin
New database
Schema successfully upgraded to: 001-topology

Reinstall a database

You can use the --reinstall option to wipe and reinstall a database. For example:

> dbsetup.bat --dburl=jdbc:mysql://localhost/reports --dbuser=root --dbpass=admin 
--reinstall
Re-installing database...
Schema successfully upgraded to: 001-topology

As an alternative to using the dbsetup command, API Gateway Analytics also provides separate SQL schema scripts to set up the database tables for each of the supported databases. However, these scripts set up the new tables only, and do not perform any upgrades of existing tables. These scripts are provided in the INSTALL_DIR/system/conf/sql directory in the following subdirectories:

You can run the SQL commands in the db_schema.sql file in the appropriate directory for your database. The following example shows creating the tables for a MySQL database:

mysql> \. C:\oracle\oaganalytics\system\conf\sql\mysql\db_schema.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
...