Configuring Managed File Transfer Database Logger on AWS Oracle Database Instance

Paras Mamgain

Published on 19/06/2018

Introduction

When Managed File Transfer transfers files, it publishes information about its actions to a topic on the coordination queue manager. The database logger is an optional component of Managed File Transfer that you can use to copy this information into a database for analysis and auditing purposes.
This Optional Component i.e. database Logger can be configured with Oracle Database and with IBM DB2 Database for storing the logs.
In this blog we will be describing the steps to configure the Oracle database Instance hosted in AWS that will be used with IBM MQ Managed File Transfer for logging purpose.

Why Is It Important

The MFT component of IBM MQ enables the creation of a MFT Logger which allows us to log the information about the transfer happening via MFT to be stored and Logged into the database.
These Database records then helps us to performs audits and observe various other information that was stored.

Pre-Requisite

1. IBM MQ must be installed along with MFT Components.
2. Running Oracle Database Instance in Amazon Web Services.

Configuration

1. Create an Oracle Database Instance in AWS(Amazon Web Service).
   following is a screenshot showing a DB Instance for Oracle Enterprise Edition created with a name FTEDB.
2. Once the DB Instance is created for the Oracle Enterprise Edition and it is in a running state, get the endpoint/port number for oracle db instance.following is the attached screenshot which should help you get the endpoint and port number for your Oracle Database Instance.
3. run the sql command via sqlplus commands as shown below

for windows :
sqlplus user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))/password < ftelog_tables_oracle.sql

for linux :

sqlplus 'user_name@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns_name)(PORT=port))(CONNECT_DATA=(SID=database_name)))'/password < ftelog_tables_oracle.sql
where
user_name : the user name of your DB instance administrator
dns_name : the DNS name for your DB instance
port : the port number
database_name : SID value is the name of the DB instance's database that you specified when you created the DB instance, and not the name of the DB instance.
password : the database password set by you while creating the database instance in AWS ftelog_tables_oracle.sql : SQL script file for the MFT database which is shipped as a sample with the MQ Advance Installation

Since I am using a windows platform therefore my sample query looked like

e.g. : C:\Users\Administrator>sqlplus admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ftedb.xxxxxxxxxx.aa-bbb-c.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))/admin123 < ftelog_tables_oracle.sql

following is a screenshot attached which demonstrate the execution of the above command

1. You can confirm if the tables are created by running the following sql commands.
   sqlplus admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ftedb.xxxxxxxxxx.aa-bbb-c.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=SID=ORCL)))/admin123
   select * from tab;
   this would return list of all the tables created in this database which must have the MFT specific database tables as well.
   Also attaching a screen shot for the same
2. Now you can create a MFT Database Logger using the command shown below. The MFT Database Logger command should look like following
   fteCreateLogger -loggerType DATABASE -dbName FTEDB -dbType oracle -dbDriver "C:\app\parasmamgain\product\12.1.0\dbhome_1\jdbc\lib\ojdbc7.jar" DBLOGGER1
3. Once you have executed the above create logger command then you can go and update the logger properties file present at the path shown below. This path might vary for you depending on the configuration of your installation.
4. C:\ProgramData\IBM\MQ\mqft\config\QM1\loggers\DBLOGGER1
   This is how my configuration Properties for the logger looks like after creating the MFT Database Logger.
   You can modify your database logger property accordingly
   where
   QM1 - refers to the default coordination queue manager name
   DBLOGGER1 – Database logger name

#
#Tue Jun 12 02:34:40 PDT 2018
wmqfte.logger.name=DBLOGGER1
wmqfte.database.driver=C\:\\app\\parasmamgain\\product\\12.1.0\\dbhome_1\\jdbc\\lib\\ojdbc7.jar
wmqfte.logger.type=DATABASE
wmqfte.database.name=ORCL
wmqfte.database.type=Oracle
wmqfte.queue.manager=QM1
wmqfte.oracle.port=1521
wmqfte.oracle.host=ftedb.aaaaaaaabb.cc-dddd-2.rds.amazonaws.com

Note : wmqfte.oracle.host should point to the endpoint of the oracle AWS endpoint which in my case is ftedb.aaaaaaaabb.cc-dddd-2.rds.amazonaws.com

1. Now your MFT Database Logger has been configured with the Oracle DB Instance in AWS.
2. You can Now Initiate transfers and the details for this transfers will be logged in the database configured above.
3. You can view the transfer details using the following sql command.
   sqlplus admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ftedb.xxxxxxxxxx.aa-bbb-c.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))/admin123
4. select * from transfer;
   This should show you your transfer details as displayed in the screenshot below.

Your IBM MQ Managed File Transfer Database Logger is now configured with the Oracle Database Instance in Amazaon Web Services. All the transfers records under this configuration will now be logged in this Oracle Database and can be used for the Auditing,Monitoring purpose of File Transfer.