When Managed File Transfer transfers files, it provides an additional facility via which we can keep track of all the transfer that has happened on the particular 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.
When Managed File Transfer transfers files, it publishes information about its actions to a topic(SYSTEM.FTE/#) on the coordination queue manager.
This Optional Component i.e. database Logger can be configured with Oracle Database and with IBM DB2 Database for storing the logs/information which is published under the topic(SYSTEM.FTE/#) on the coordination queue Manager.
In this write up we will be describing the steps to configure the Oracle 18c database that will be used with IBM MQ Managed File Transfer for logging purpose.
*(Managed File transfer will be called as MFT henceforth)
Why Is It Important
1. The IBM MQ Managed File Transfer enables the creation of a MFT Logger which allows us to log the information about the transfer happening via IBM MQ Managed File Transfer to be stored and Logged into the database.
2. These Database records then helps us to performs audits and observe various other information that was stored.
3. These Records helps in cross validating the successful and failed transfers.
4. Enable users to perform specific action if the transfers are failed.
5. Helps to retained the entire transfer statistics which is useful for generating monthly, quarterly, half yearly or yearly transfer reports.
â€¢ IBM MQ must be installed along with MFT Components, MQ version : 220.127.116.11
â€¢ Oracle 18c must be installed, Database Version : 18.104.22.168.0
1. Go to start, and search for SQL Plus and open the window.
2. Login as “scott as sysdba” and password as “tiger” this is the default username and password that we use for the remaining steps. As attached in screenshot below.
Note : I have taken the
scott as the user for creating the MFT database logger schema. Make sure that the user you are using have sufficient access and rights or contact your database administrator for any database access related issues/doubts.
3.Execute command to run the ftelog_tables_oracle.sql file
In my system i have kept the sql script file at location
4. once the command is executed, confirm if the tables are created or not.
e.g. : check if a view with name transfer is created or not. execute command `desc transfer` and it should show the schema of that view (as shown in screenshot)
5. Now your Database setup for Oracle 18c Is complete and you can create a database logger and configure it against a coordination queue manager.
sample command for it is
fteCreateLogger -loggerType DATABASE -dbName FTEDB -dbType oracle -dbDriver "\jdbc\lib\ojdbc8.jar" dblogger1
This is how your logger.properties file should look like
6. Start the DB Logger using command
fteStartLogger LOGGER_NAME -p COORDINATION_QUEUEMANAGER_NAME
Note : Make sure that the MQMFTcredentials.xml file is configured and contains the information about the LoggerName,username and password.
Below is an attached screenshot of my MQMFTcredentials.xml file.
where DBLOGGER is the name of my Database Logger, scott is the database username tiger is the password for the user scott.