Migrating data from SQL Server to Oracle

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

1. Create an Oracle database.
2. Before you migrate the data to Oracle, create the data warehouse schemas and tables in the new Oracle database by configuring your database with JTS Server as a data warehouse(DW). The JTS server version needs to be the same as the source DW db version.
   For example, if you are migrating the DW db version 6.0.6.1, then the JTS server must also be on version 6.0.6.1.
3. After you start JTS, run the setup. At the step-7 of the setup process that is Configure Data Warehouse, update the JDBC location to point to the data warehouse database created in step-2. Update the JDBC password, and click Test Connection.
4. When the DW is connected, click Create Tables to create the new schemas and tables for the data warehouse. Alternatively, you can use the generateWarehouseDDLScripts command to generate the data warehouse DDL creation scripts. For more information, see Repository tools command to generate data warehouse creation scripts.
5. Before you migrate the data to Oracle, disable the foreign key constraints in all data warehouse tables in the Oracle database by using the following code:

   BEGIN
   FOR TABNAME IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS') AND CONSTRAINT_TYPE = 'R' AND STATUS = 'ENABLED')
   LOOP
   EXECUTE IMMEDIATE 'ALTER TABLE ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" DISABLE CONSTRAINT ' || TABNAME.CONSTRAINT_NAME;
   END LOOP;
   END;

6. Disable the triggers in all data warehouse tables in the Oracle database by using the following code:

   BEGIN
   FOR TABNAME IN (SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS') AND STATUS = 'ENABLED')
   LOOP
   EXECUTE IMMEDIATE 'ALTER TRIGGER ' || TABNAME.OWNER || '."' || TABNAME.TRIGGER_NAME || '" DISABLE';
   END LOOP;
   END;

7. 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.
8. Install and run the Database Migration Toolkit.
9. 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.
10. In the Source field, select SQL Server in the migration toolkit.
11. In the Destination field, select Oracle in the migration toolkit.
12. Select all the tables in the chosen schema.
13. Click Submit to migrate the data from the selected tables.
14. Repeat the steps 12 and 13 for the CONFIG, RICALM, RIDW, and RIODS schemas.
15. Enable the triggers in all data warehouse tables in the Oracle database by using the following code:

    BEGIN
    FOR TABNAME IN (SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS') AND STATUS = 'DISABLED')
    LOOP
    EXECUTE IMMEDIATE 'ALTER TRIGGER ' || TABNAME.OWNER || '."' || TABNAME.TRIGGER_NAME || '" ENABLE';
    END LOOP;
    END;
    

16. Enable the foreign key constraints in all data warehouse tables in the Oracle database by using the following code:

    BEGIN
    FOR TABNAME IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS') AND CONSTRAINT_TYPE = 'R' AND STATUS = 'DISABLED')
    LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || TABNAME.OWNER || '."' || TABNAME.TABLE_NAME || '" ENABLE CONSTRAINT ' || TABNAME.CONSTRAINT_NAME;
    END LOOP;
    END;

17. After the migration is successful, run the following command:

    BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CONFIG');
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'RICALM');
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'RIDW');
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'RIODS');
    END;

18. To compare the count of tables in the source and target databases, run the following queries:
    Query for SQL Server

    select count(name) from sys.objects where schema_name(schema_id) in ('CONFIG','RICALM','RIDW','RIODS') and type='U';

    Query for Oracle

    SELECT COUNT(table_name) FROM DBA_TABLES WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS');

19. 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 Oracle

    SELECT OWNER || '.' || TABLE_NAME AS TableNameOracle, NUM_ROWS AS RowCountOracle FROM DBA_TABLES WHERE OWNER IN ('CONFIG','RICALM','RIDW','RIODS') ORDER BY RowCountOracle DESC;

20. In the ELM installation directory, create a new folder that is named Oracle and put the jdbc jar file in the folder.
21. Start the ELM server.
22. Update the data source properties for the data warehouse with the new Oracle 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.