A Step by Step guide to carry out MFT Database Migration for IBM MQ version 9.0.5 and above

Paras Mamgain
Published on 25/04/2018 / Updated on 30/07/2018

Introduction

When IBM MQ Managed File Transfer transfers files, it provides an additional facility using 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.
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.
MFT ships a number of SQL scripts for both Oracle and DB2 databases to create required tables in these database.Administrators are required to execute these SQL Scripts on their database before MFT Logger is configured.

Recently, IBM MQ Managed File Transfer has upgraded its database Schema as a result some changes were made in the SQL Script file as well. IBM MQ Managed File Transfer ships some additional SQL Script file that enables the User/Database administrator to easily migrate from older database schema to the newer database schema
*(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 Database 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. To avoid the Poor performance from an SQL query when an Oracle database query includes columns with type NCLOB.
4. Some of the Data type used in the previous Database schema are deprecated.
5. Existing Database Schema uses NCLOB(in Oracle) and LONG VARCHAR(in DB2) for storing values which requires much lesser storage space.
   e.g. : In Oracle NCLOB is being used and in DB2 Long Varchar is being used for storing the SOURCE_QUEUE and DESTINATION_QUEUE name this NCLOB/LONG VARCHAR will be replaced with NVARCHAR2(256)/VARCHAR(256) respectively.

Pre-Requisite
This document is valid for following scenarios:

1. When an Existing IBM MQ installation along with Managed File Transfer component installed in their system is being upgraded from an older version to a 9.0.5 version or above and
2. There exists a configuration where users are using MFT Database Logger with Oracle or IBM DB2.
3. No migration is involved for the IBM MQ Managed File Transfer database if a fresh database for IBM MQ Managed File transfer is being configured using new schema but there are certain steps required for migrating a database with old schema to new schema. The migration steps are documented in the section below.

Important Note :Before Executing the following migration steps (mentioned for Oracle/ IBM DB2 Database).Take a Backup for your entire database and its relevant configuration. Seek help/guidance from your Database Administrator while taking back up and before carrying out the migration process.

Steps for migrating an Oracle database in current schema to new schema
The Following steps are recommended to migrate a database from existing schema to the new schema. The steps below describe updating existing table schema by executing the sample SQL script file.
Note : Take a backup for our database and its relevant configuration which involves migration process.

1. These 4 sample SQL script files will be shipped ( name : oracle_nvarchar_migration_step_1.sql, oracle_nvarchar_migration_step_2.sql, oracle_nvarchar_migration_step_3.sql,oracle_nvarchar_migration_step_4.sql) could be found at
   location : MQ_Installation_Directory/mqft/sql
2. Before running oracle_nvarchar_migration_step_3.sql file as mentioned in the next Step (Step 3.c) make sure that step 3.a and step 3.b were processed successfully.
3. Execute the Scripts in the order
   a.oracle_nvarchar_migration_step_1.sql
   b.oracle_nvarchar_migration_step_2.sql
   c.oracle_nvarchar_migration_step_3.sql
   d.oracle_nvarchar_migration_step_4.sql
4. example : cd MQ_Installation_Directory/mqft/sql
   
   Execute SQL script files using commands as illustrated below :
   a.sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step1.sql
   b.sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step2.sql
   c.sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step3.sql
   d.sqlplus USERNAME/PASSWORD < oracle_nvarchar_migration_step4.sql
   where USERNAME/PASSWORD refers to the userId and password of a particular user which is configured to have appropriate access & privileges.
   e.g. – paras/passW0rd where paras is the user name and passW0rd is the password

Steps for migrating an DB2 database in current schema to new schema
The Following steps are recommended to migrate a database with existing schema to the new schema. The steps below describes updating existing table schema by executing the sample SQL script file.
Note : Take a backup for our database and its relevant configuration which involves migration process.

1. These 4 sample SQL script files will be shipped ( name : db2_varchar_migration_step_1.sql, db2_varchar_migration_step_2.sql,db2_varchar_migration_step_3.sql,db2_varchar_migration_step_4.sql) could be found at
   location : MQ_Installation_Directory/mqft/sql
2. Before running db2_varchar_migration_step_3.sql file as mentioned in the next Step (Step 3.c) make sure that step 3.a and step 3.b were processed successfully.
3. Execute the Scripts in the order
   a.db2_varchar_migration_step_1.sql
   b.db2_varchar_migration_step_2.sql
   c.db2_varchar_migration_step_3.sql
   d.db2_varchar_migration_step_4.sql
4. example :cd MQ_Installation_Directory/mqft/sql

Execute SQL script files using commands as illustrated below :
a.db2 -tvmf db2_varchar_migration_step_1.sql
b.db2 -tvmf db2_varchar_migration_step_2.sql
c.db2 -tvmf db2_varchar_migration_step_3.sql
d.db2 -tvmf db2_varchar_migration_step_4.sql