Migrating the database tables on Db2 on z/OS to V8.0.0

If your database is Db2® on a z/OS® system, you must complete the following steps to migrate between different versions of WebSphere® MQ File Transfer Edition V7.0.3 to V7.0.4, and WebSphere MQ File Transfer Edition V7.0.4 to IBM® MQ Managed File Transfer V8.0.0. The Db2 tables have different structures from previous releases. For example, there are new columns in some tables, and some variable characters columns can be larger, so the tables from previous releases have to be migrated to the V8.0 format.

About this task

IBM MQ Managed File Transfer V8.0 supports transferring very large files, where the size of the file is larger than can be stored in an integer (32 bit) number. There is a definition that uses BIGINT (64 bit) numbers. You can choose to use integer definitions, called ftelog_tables_zos.sql, or BIGINT definitions, called ftelog_tables_zos_bigint.sql, located in the prod/mqf/sql directory.

The BIGINT data type is available in WebSphere MQ File Transfer Edition V7.0.4 Fix Pack 3, or IBM MQ Managed File Transfer V7.5 Fix Pack 1 or later.

To enable use of BIGINT data types, you must be using Db2 V9.1 or later. INTEGER data types are used for fields which denote the sizes of files that are transferred and the table ID associated with each transfer. If you want to log transfers with file sizes greater than 2 GB, or if you want to store more than 2,147,483,648 individual transfers in your database you must use the BIGINT SQL file.

The following list outlines the processes you need to follow:

You have existing tables from V7. of the product. These tables have a schema, for example FTELOG.
Create V8 tables using a different schema name, for example, FTEV8. This allows you to copy data from FTELOG.table to FTEV8.table.
Copy the data to the new table
Set the generated ID values in the new tables
Run the fteMigrateLogger command to move the properties file to a new place in the directory structure.
Edit the logger properties file to specify the new schema (FTEV8)
Edit the existing Logger JCL to use the V8 IBM MQ Managed File Transfer libraries.
Start the logger.
Once the logger is working you can delete the FTELOG tables.

In the following description IBM MQ Managed File Transfer product is installed in the /HMF8800 directory in USS.

Procedure

If you have not already stopped your database logger, stop your database logger using the fteStopDatabaseLogger command in USS or P loggerjob.
Issue the command ls /HMF8800/mqft/sql to list the SQL files in the directory.
If you are going to use BIGINT numbers copy, ftelog_tables_zos_bigint.sql to your home directory, otherwise, copy ftelog_tables_zos.sql to your home directory.
Edit the file you moved to your home directory:
1. Change ftelog to the schema name for the new tables.
2. Ensure each index has a unique name.
  To do this, in an edit session:
  1. Exclude all lines.
  2. Find 'CREATE UNIQUE INDEX ' ALL
  3. Change _KEY _K8Y ALL NX
Check the file to make sure all of the statements are within column 71.
If the statements are not within column 71, split the line before column 71.
You might be able to use this file as input to SQL, or you might want to copy it to a PDS. To do this, edit the PDS and use the copy command, specifying the directory and file name.
Check the definitions with you Db2 administrator, as there might be site standards that you need to follow.
Carry out the following:
1. Copy the _zos_704-800.sql file, located in the /HMF8800/mqft/sql/ftelog_tables directory to your home directory.
2. Edit this file. Change FTESRC to your existing schema (FTELOG) and FTEDEST to the new schema (FTEV8).
3. Check the file to make sure all of the statements are within column 71.
  If the statements are not within column 71, split the line before column 71.
4. If you have DB2 RUNSTATS jcl for the IBM MQ Managed File Transfer tables, create a new job specifying the new schema and tables.
Some tables have a generated ID to enforce a unique identifier for each row and you need to set these identifiers.
The following tables have generated ID columns:
- AUTH_EVENT
- CALL
- CALL_ARGUMENT
- CALL_RESULT
- FILE_SPACE_ENTRY
- METADATA
- MONITOR_ACTION
- MONITOR_EXIT_RESULT
- MONITOR_METADATA
- SCHEDULE
- SCHEDULE_ACTION
- SCHEDULE_ITEM
- SCHEDULE_SPEC
- TRANSFER_CALLS
- TRANSFER_CD_NODE
- TRANSFER_CORRELATOR
- TRANSFER_EVENT
- TRANSFER_EXIT
- TRANSFER_ITEM
- TRANSFER_ITEM_ATTRIBUTES
- TRANSFER_STATS
- TRIGGER_CONDITION
To set the generated IDs of these tables to the correct value perform the following steps for each table:
1. Determine the maximum ID value in the existing data.
  You can find this value by running this SQL statement:
```
SELECT MAX(ID) FROM schema_name.table_name
```
  The value returned from this command is the maximum existing ID in the specified table.
2. Alter the table to set the ID generator to begin from a new value that is 1 higher than the value returned by the previous step.
  You can set this value by running the following SQL statement:
```
ALTER TABLE schema_name.table_name ALTER COLUMN ID RESTART WITH value
```
Edit the database properties file to specify the new schema name:
1. If your IBM MQ Managed File Transfer configuration directory is /u/userid/fteconfig you can use the USS command find /u/userid/fteconfig -name databaselogger.properties to locate the properties file for the logger.
2. Edit this file and change wmqfte.database.schema to the new schema value.
Issue the following commands to convert the directory tree structure to V8.0.0 format before you attempt to use the logger:
1. fteMigrateConfigurationOptions
2. fteMigrateLogger
  This copies the databaselogger.properties to logger.properties.
Edit existing Logger JCL to use the V8.0.0 IBM MQ Managed File Transfer libraries.
Start the logger.
Once the logger is working you can delete the V7 FTELOG tables.