Increasing the page size of the log database on Db2 on Windows, UNIX or Linux

If your database is Db2® on a Windows, UNIX or Linux system, and you created your log database with a page size of less than 8 KB, you must increase the page size of the database before migrating to the WebSphere MQ File Transfer Edition Version 7.0.3 or later tables.


Procedure

  1. If we 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. Use the Db2 export command to transfer the data from your log database tables to files on disk. Note: You must specify large object files for tables that include large objects. Those tables are CALL_RESULT and METADATA.
  4. Drop your log database tables.
  5. Create a table space with a page size of at least 8 KB and with an associated buffer pool with a page size of at least 8 KB. Give your new table space a name. For example, FTE8KSPACE.
  6. Edit the ftelog_tables_db2.sql file so that the commands create tables in the new table space. In the ftelog_tables_db2.sql file, change all occurrences of the text IN USERSPACE1 to IN new_tablespace_name. For example, change IN USERSPACE1 to IN FTE8KSPACE.
  7. Run the SQL commands in the ftelog_tables_db2.sql file against your database.
  8. Use the Db2 load command to transfer the exported data into the new tables. Note:

    • Map the column names based on the column names found in the input file. Ensure that the input column names and target column names match up in those tables that have changed their structure.
    • You must specify the IDENTITY OVERRIDE behavior on the identity column of all tables, except for MONITOR and TRANSFER. Specifying this behavior ensures that the row IDs are not regenerated during the load operation.
  9. Run the Db2 set integrity command with integrity status values of immediate and checked, against the following tables in the order given:

    • CALL_ARGUMENT
    • MONITOR
    • MONITOR_ACTION
    • MONITOR_EXIT_RESULT
    • MONITOR_METADATA
    • SCHEDULE_ACTION
    • SCHEDULE
    • SCHEDULE_ITEM
    • TRANSFER
    • TRANSFER_CALLS
    • TRANSFER_EVENT
    • TRANSFER_ITEM
    • TRANSFER_STATS
    • TRIGGER_CONDITION
  10. 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
    • 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. We can find this value by running this SQL statement: 
      SELECT MAX(ID) FROM FTELOG.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. We can set this value by running the following SQL statement: 
      ALTER TABLE FTELOG.table_name ALTER COLUMN ID RESTART WITH value