A Quick Start Guide to Configure IBM DB2 with IBM MQ Managed File Transfer
Paras Mamgain
Published on 13/03/2018
Introduction
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 IBM DB2 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.
Pre-Requisite
• IBM MQ must be installed along with MFT Components, MQ version : 9.0.3.0
• IBM DB2 must be installed, Database release level : 10
Configuration
To run the commands you will have to open a command prompt,
Go to start -> All Programs -> IBM DB2 ->Open DB2 Command Window (Administrator)
(for more details see the screenshot attached below)
1.Install the IBM DB2. Choose appropriate USERID/PASSWORD. In this article, we choose db2admin/db2admin as USERID/PASSWORD and DB2 as instance name (here we are using IBM DB2 10.5).
Username:db2admin
Password:db2admin
Db2 instance name : DB2
Post Installation
To run all the following commands, Go to start -> All Programs -> IBM DB2 ->Open DB2 Command Window (Administrator) . (see the screenshot attached below)
2.Post installation open a command and check to see the instances available.
>db2ilist
=DB2
3.Set the db2 instance as DB2 using following commands:
>set db2instance=DB2
To verify above run
>db2 get instance
Error: If DB21061E Command line environment not initialized, error returns then execute foll. Command
>db2cmd
Then a console opens run the following commands inside that console and continue using that console
> set db2instance=DB2
>db2 get instance
=The current database manager instance is: DB2
- Once an instance is set then you can create a database using following command
>db2 create database
i.e. > db2 create database FTEDB
to check/confirm about the property of database(FTEDB) which you created, run following
>db2 list database directory
5.Activate the database you created using following command
>db2 activate db
i.e. db2 activate db FTEDB
6.Connect to the database using following command
>db2 connect to database-name
7.now you need to run the sql script that is provided with the IBM MQ installation
Location :MQ Installation directory\mqft\sql\ftelog_tables_db2.sql
Using following command
>cd MQ Installation directory\mqft\sql\
>db2 -tvmf ftelog_tables_db2.sql
8.Once all the database objects are created inside the schema via the script you will need to assign the privileges to the user.
Run following command to provide access to the user(db2admin) to use the schema created(FTELOG).
>db2 GRANT DATAACCESS ON DATABASE TO USER DB2ADMIN
9.After this you can create a MFT Configuration (which involves setting up coordination Queue Manager and Command Queue Manager) along with the agents
10.Create MFT Logger and start the logger as illustrated below(Step 11):
11.Create a MFT Database Base Logger ,
see https://www.ibm.com/support/knowledgecenter/en/SSFKSJ_9.0.0/com.ibm.wmqfte.doc/create_logger_cmd.htm for more details
Example for creating a DB Logger is shown below :
fteCreateLogger -loggerType DATABASE -dbName FTEDB -dbType DB2 -p PRMFTDEMO2 -loggerQMgr PRMFTDEMO2 -dbDriver "C:\Program Files (x86)\IBM\SQLLIB\java\db2jcc4.jar" DBLOGGER2
where FTEDB is the name of the Database
DB2 is the name of Database Provider being Used (DB2 / Oracle)
PRMFTDEMO2 is the name of Queue Manager
C:\Program Files (x86)\IBM\SQLLIB\java\db2jcc4.jar location of DB2 Jar
DB2LOGGER is the name of the MFT logger
12.RUN runmqsc Command (as shown in the screen shot attached below)
Command : runmqsc QUEUEMANAGER_NAME < FILELOCATION/FILENAME.mqsc
13.Now restart the database using
>db2stop
>db2start
14.start your Logger
>fteStartLogger -p PRMFTDEMO2 DBLOGGER2
15.continue with your transactions/transfers and if everything works fine you will see your data about transfer being logged into the database.
Sample query for initiating a transfer is as follows:
>fteCreateTransfer -p PRMFTDEMO2 -t text -sa AGENT1 -sm PRMFTDEMO2 -da AGENT2 -dm PRMFTDEMO2 -de overwrite -df "C:\Users\IBM_ADMIN\Desktop\dest\New Text Document.txt" "C:\Users\IBM_ADMIN\Desktop\src\test.txt"
Error and Troubleshooting:
1. After transfer , If there is an Error like in the MFT Logger’s Log
SQL State [54048], Error Code [-1585], Message [DB2 SQL Error: SQLCODE=1585 , SQLSTATE=54048, SQLERRMC=null in the trace file of logger
Refer :
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql01585n.html
Sample Solution :
Then you must create the bufferpools as described below in the db2 database :
>db2 CREATE BUFFERPOOL BP4K pagesize 4K;
>db2 CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K BUFFERPOOL BP4K;
>db2 CREATE BUFFERPOOL BP8K pagesize 8K;
>db2 CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K BUFFERPOOL BP8K;
>db2 CREATE BUFFERPOOL BP16K pagesize 16K;
>db2 CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K BUFFERPOOL BP16K;
>db2 CREATE BUFFERPOOL BP32K pagesize 32K;
>db2 CREATE SYSTEM TEMPORARY TABLESPACE STB_32 PAGESIZE 32K BUFFERPOOL BP32K;
- If No Transfer is being logged then check if following property is being added to the logger.properties files
wmqfte.database.schema=
example : wmqfte.database.schema=FTELOG
where FTELOG is the default schema name in the ftelog_tables_db2
** DBLOGGER2 refers to the name of the LOGGER in use
** PRMFTDEMO2 refers to the QueueManager in use (here Command QM,Coordination QM,Agent QM all are using the same Queue Managers)
** Incase of Schema is being used while creating database , do not forget to add property wmqfte.database.schema=.