Migrating data from SQL Server to Db2

You can migrate the data from SQL Server to Db2 and configure Jazz® Reporting Service to use the new database.

1. Create a new Db2 database.
2. Before you migrate the data to Db2, create the data warehouse schemas and tables in the new Db2 database by configuring your database with JTS Server as a data warehouse.
3. After you connect to the database, click Create Tables to create the new schemas and tables for the data warehouse.

Alternatively, you can shut down an ELM server that has the data warehouse with the Db2 database and run following command:

repotools-jts –generateWarehouseDDLScripts 

4. In the repotools-jts_generateWarehouseDDLScripts.out directory, run the SQL scripts in sequence.
5. Before you migrate the data to Db2, disable the foreign key constraints in all data warehouse tables in the Db2 database. Save the following script in disableFKConstraints.sql file in SQLLIB\BIN folder:

   BEGIN
   FOR v_row AS SELECT TABSCHEMA, TABNAME, CONSTNAME FROM
   SYSCAT.TABCONST WHERE TABSCHEMA IN
   ('CONFIG','RICALM','RIDW','RIODS') AND TYPE = 'F'
   DO
   EXECUTE IMMEDIATE 'ALTER TABLE ' || v_row.TABSCHEMA || '.' ||
   v_row.TABNAME || ' ALTER FOREIGN KEY ' || v_row.CONSTNAME || ' NOT
   ENFORCED';
   END FOR;
   END@

6. Run the following command in Db2 Command Window:

   db2 -td@ -vf C:\IBM\SQLLIB\BIN\disableFKConstraints.sql

7. Drop the Triggers in all data warehouse tables in Db2 database. Save the following script in dropTriggers.sql file in SQLLIB\BIN folder.

   BEGIN
   FOR v_row AS SELECT TRIGSCHEMA, TRIGNAME FROM SYSCAT.TRIGGERS WHERE
   TRIGSCHEMA IN ('CONFIG','RICALM','RIDW','RIODS')
   DO
   EXECUTE IMMEDIATE 'DROP TRIGGER ' || v_row.TRIGSCHEMA || '.' ||
   v_row.TRIGNAME;
   END FOR;
   END@

8. Run the following command in Db2 Command Window:

   db2 -td@ -vf C:\IBM\SQLLIB\BIN\dropTriggers.sql

9. Obtain ESF Database Migration Toolkit or comparable tool to migrate the data.
   Note: IBM provides services to migrate data. For more information, contact your IBM account representative.
10. Install and run the Database Migration Toolkit.
11. Click Settings > General tab and update the following fields:
    • In the If Table Exists: field, select Empty Data.
    • Clear the Copy Structure Only, Transfer Auto-Increment, Copy Indexes, Copy Foreign Keys,Make Relation Name Unique and Merge Schemas check boxes.
    • Click Save.
12. In the Source field, select SQL Server in the migration toolkit.
13. In the Destination field, select Db2 in the migration toolkit.
14. Select all the tables in the chosen schema.
15. Click Submit to migrate the data from the selected tables.
16. Repeat the steps 12 and 13 for the CONFIG, RICALM, RIDW, and RIODS schemas.
17. Re-create the triggers.
    1. Place createAllTriggers114.sql file in SQLLIB\BIN folder.
    2. Run this file from Db2 Command Window by using the following code:

       db2 -td@ -vf C:\IBM\SQLLIB\BIN\createAllTriggers114.sql

18. Enable the foreign key constraints in all data warehouse tables in the Db2 database. Save the following script in enableFKConstraints.sql file in SQLLIB\BIN folder:

    BEGIN
    FOR v_row AS SELECT TABSCHEMA, TABNAME, CONSTNAME FROM
    SYSCAT.TABCONST WHERE TABSCHEMA IN
    ('CONFIG','RICALM','RIDW','RIODS') AND TYPE = 'F'
    DO
    EXECUTE IMMEDIATE 'ALTER TABLE ' || v_row.TABSCHEMA || '.' ||
    v_row.TABNAME || ' ALTER FOREIGN KEY ' || v_row.CONSTNAME || '
    ENFORCED';
    END FOR;
    END@

19. Run the following command in Db2 Command Window:

    db2 -td@ -vf C:\IBM\SQLLIB\BIN\enableFKConstraints.sql

20. After the migration is successful, update the stats for all DW tables by using following command:

    RUNSTATS ON TABLE TableName

21. To compare the number of rows for all the tables for Source and Target databases, run the following queries:
    Query for SQL Server

    SELECT
       SCHEMA_NAME(sOBJ.schema_id) + '.' + sOBJ.name AS [TableNameSQLServer]
       , SUM(sPTN.Rows) AS [RowCountSQLServer]
    FROM
       sys.objects AS sOBJ
       INNER JOIN sys.partitions AS sPTN
          ON sOBJ.object_id = sPTN.object_id
    WHERE
        sOBJ.is_ms_shipped = 0x0 AND index_id < 2 AND sOBJ.type = 'U' AND SCHEMA_NAME(sOBJ.schema_id) IN ('CONFIG','RICALM','RIDW','RIODS')
    GROUP BY
       sOBJ.schema_id
       , sOBJ.name
    ORDER BY [RowCountSQLServer] DESC;

    Query for Db2

    SELECT TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) AS db2TableName, CARD AS db2RowNum
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA IN ('CONFIG','RICALM','RIDW','RIODS') AND TYPE='T'
    ORDER BY CARD DESC; 
    

22. Update the data source properties for the data warehouse with the new Db2 database.
23. Refresh the meta model for the data warehouse.
24. Reimport the reports by selecting Import ready-to-use reports.
25. Run and verify the existing data warehouse reports.