Migrating the database tables on Db2 on z/OS

If your database is DB2® on a z/OS® system, you must complete the following steps to migrate between the V7.0.2 and V7.0.3 tables or between the V7.0.3 and V7.0.4 tables. You may also use these migration steps to enable use of the BIGINT data type in your database tables. The BIGINT data type is available in WebSphere® MQ File Transfer Edition V7.0.4 Fix Pack 3.

About this task

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.

Procedure

  1. If you have not already stopped your database logger, stop your database logger using the fteStopDatabaseLogger command.
  2. Back up your log database using the tools provided by Db2.
  3. Create a table space. This table space must have a page size of at least 8 KB and an associated buffer pool with a page size of at least 8 KB. Give your new table space a name. For example, FTENEWSPACE.
  4. Create the new tables. Change the schema name in the file from FTELOG to a schema name that is different to the name of your existing schema. If you want to use the BIGINT datatype instead of the INTEGER datatype use the ftelog_tables_zos_bigint.sql. Otherwise, use the ftelog_tables_zos.sql file.
  5. Migrate the data from your old schema to your new schema. To do this migration edit the ftelog_tables_zosold-new.sql file, where old is the version of the database tables that exist and new is the version of the database tables that you are migrating to. Change FTESRC to the name of your existing schema. Change FTEDEST to the name of the schema that you created in the previous step.
  6. Change the database logger properties to refer to the new database schema.
    • If you are using the stand-alone database logger, edit the databaselogger.properties file to include the following line:
      wmqfte.database.schema=schema_name
      In this example, schema_name is the name of the schema that you created in step 3.
    • If you are using the JEE database logger, you must change the schema name in the database logger EAR file. For more information, see Changing the schema name in your Java Platform, Enterprise Edition database logger.
  7. In tables with generated ID columns, set the ID generators to begin from a value one higher than the existing highest ID value. The following tables have generated ID columns:
    • AUTH_EVENT
    • CALL
    • CALL_ARGUMENT
    • CALL_RESULT
    • METADATA
    • MONITOR_ACTION
    • MONITOR_EXIT_RESULT
    • MONITOR_METADATA
    • SCHEDULE
    • SCHEDULE_ACTION
    • SCHEDULE_ITEM
    • SCHEDULE_SPEC
    • TRANSFER_CALLS
    • TRANSFER_EVENT
    • TRANSFER_ITEM
    • 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
    In these statements, schema_name is the name of the schema that you created in step 3.

Task Task

Feedback

Timestamp icon Last updated: Tuesday, 30 January 2018
http://www.ibm.com/support/knowledgecenter/SSEP7X_7.0.4/com.ibm.wmqfte.doc/db_pagesize_zos.htm