Migrating a Db2 database to the new schema

How we migrate a database with the existing schema to the new schema, by using the sample SQL script file.


Before starting

Take a backup of the database, and its relevant configuration information, that we are going to migrate and refer to the Db2 entity relationship diagram.

Attention: In the Db2 database, the LongVarchar data type now has a limit of :

  • 2000 bytes in the SOURCE_FILENAME and DESTINATION_FILENAME columns, in the TRANSFER_ITEM and SCHEDULE_ITEM tables
  • 4000 bytes, or 256 bytes for all the remaining columns, depending on the purpose of each column

If, for any reason, we want to increase the size of these database columns, we can change the script file and increase the size of the corresponding column.


The following four sample SQL script files are located in <MQ_Installation_Directory>/mqft/sql:

  • db2_varchar_migration_step_1.sql
  • db2_varchar_migration_step_2.sql
  • db2_varchar_migration_step_3.sql
  • db2_varchar_migration_step_4.sql


Procedure

  1. Carry out the following tasks in order:
    1. Run db2_varchar_migration_step_1.sql
    2. Run db2_varchar_migration_step_2.sql
    3. Run db2_varchar_migration_step_3.sql
    4. Run db2_varchar_migration_step_4.sql

    Important: Before running step 1.c, ensure that steps 1.a and 1.b have run successfully.

  2. Issue the command cd <MQ_Installation_Directory>/mqft/sql
  3. Process the SQL script files, using the following commands in order:
    1. Run db2 -tvmf db2_varchar_migration_step_1.sql
    2. Run db2 -tvmf db2_varchar_migration_step_2.sql
    3. Run db2 -tvmf db2_varchar_migration_step_3.sql
    4. Run db2 -tvmf db2_varchar_migration_step_4.sql


What to do next

If you receive some errors while creating new tables or new columns, caused by temporary table spaces, we can resolve these problems as follows:

    Error:
    SQL State [54048], Error Code [-1585], Message [DB2 SQL Error: SQLCODE=1585, SQLSTATE=54048, SQLERRMC=null in the trace file of logger

Explanation:

 One of the following conditions could have occurred:

1.  The row length of the system temporary table exceeded the
    limit that can be accommodated in the largest system
    temporary table space in the database.

2.  The number of columns required in a system temporary table
    exceeded the limit that can be accommodated in the largest
    system temporary table space in the database.

    Link:
    Message SQL1585N.

    Solution:
    Create a system temporary tablespace for each page as SMS (System Managed). In that case, your query always finds a tablespace with the appropriate page size.

    Example:
    The following SQL commands resolve the preceding issue:
    CREATE BUFFERPOOL BP4K pagesize 4K
CREATE SYSTEM TEMPORARY TABLESPACE STB_4 PAGESIZE 4K BUFFERPOOL BP4K  
CREATE BUFFERPOOL BP8K pagesize 8K
CREATE SYSTEM TEMPORARY TABLESPACE STB_8 PAGESIZE 8K BUFFERPOOL BP8K
CREATE BUFFERPOOL BP16K pagesize 16K
CREATE SYSTEM TEMPORARY TABLESPACE STB_16 PAGESIZE 16K BUFFERPOOL BP16K
CREATE BUFFERPOOL BP32K pagesize 32K
CREATE SYSTEM TEMPORARY TABLESPACE STB_32 PAGESIZE 32K BUFFERPOOL BP32K

Parent topic: MFT database logger tables