Contents
The 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 the reports database for use with Oracle API Gateway Analytics. It describes the prerequisites and shows an example of creating a reports 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:
JDBC Database Installation
You must have a JDBC-compliant database installed to store the API Gateway monitoring and transaction data. API Gateway Analytics provides setup scripts for the following databases:
-
MySQL
-
Microsoft SQL Server
-
Oracle
-
IBM DB2
For details on how to install your chosen JDBC database, see your database product documentation.
API Gateway Analytics Installation
For details on how to install Oracle API Gateway Analytics, see the topic on Installing Oracle API Gateway Analytics.
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 the 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.
In this topic, the example database is named reports
, but you can
use whatever name you wish.
The following example shows creating a MySQL database:
mysql> CREATE DATABASE reports; Query OK, 1 row affected (0.00 sec)
When you have created the reports 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
The dbsetup
utility also enables you to upgrade an existing reports
database from an initial API Gateway version
11.1.1.6.x
schema to version
11.1.2.x
schema.
![]() |
Important |
---|---|
You must upgrade version 11.1.1.6.x database schemas to 11.1.2.x for the API Gateway to function correctly. Pre-11.1.1.6.x database schema upgrades are not supported. If your existing API Gateway installation is version 11.1.2.x, you do not need to upgrade the database tables. |
The dbsetup
utility always checks the existing version, and modifies
only if an update is required. For example, to start an interactive upgrade, run
this script as follows:
> dbsetup.bat Connecting to configuration at: federated:file:///INSTALL_DIR\oaganalytics/conf/fed/ configs.xml Using Configured Database: DB Name: Default Database Connection DB URL: jdbc:mysql://127.0.0.1:3306/reports DB User: root Current schema version: 000-initial Latest schema version: 001-topology Continue with upgrade (Y, N) [N]: y Schema successfully upgraded to: 001-topology
dbsetup
uses SQL upgrade scripts for all supported databases
located in the following directory:
INSTALL_DIR/system/conf/sql/upgrade
The subdirectories are named for the upgrade applied, and the order in which they must be executed. The following upgrades are currently available:
Upgrade Name | Description |
---|---|
000-initial
|
11.1.1.6.x version of the schema. |
001-topology
|
11.1.2.x version of the schema. |
![]() |
Note |
---|---|
When you specify command-line arguments to |
You can specify the following options to the dbsetup
command:
Option | Description |
---|---|
-h, --help |
Displays help message and exits. |
-p PASSPHRASE, --passphrase=PASSPHRASE |
Specifies the configuration passphrase (blank for zero length). |
--dbname=DBNAME |
Specifies the database name (mutually exclusive with --dburl ,
--dbuser , and --dbpass ).
|
--dburl=DBURL |
Specifies the database URL. |
--dbuser=DBUSER |
Specifies the database user. |
--dbpass=DBPASS |
Specifies the database passphrase. |
--reinstall |
Forces a reinstall of the database, dropping all data. |
--stop=STOP |
Stops the database upgrade after the named upgrade. |
The following are some examples of using dbsetup
command options:
Connecting 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
Connecting 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
Installing a Database
You can also use the --dburl
option to setup 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
Reinstalling 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
folder in
the following directories:
/mysql
/mssql
/oracle
/db2
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 in 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) ...
When you have set up your database, you must ensure that the API Gateway is configured correctly for API Gateway Analytics before launching API Gateway Analytics. For more details, see Configuring Oracle API Gateway Analytics.