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

  4. 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
  5. 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
  6. 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.

  7. 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

  8. Now your MFT Database Logger has been configured with the Oracle DB Instance in AWS.
  9. You can Now Initiate transfers and the details for this transfers will be logged in the database configured above.
  10. 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
  11. 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.

Join The Discussion

Your email address will not be published.