IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Manage historical data > Tivoli Data Warehouse range partition migrations

IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Migrate non-partitioned tables to partitioned tables for Oracle

Use the following steps to migrate non-partitioned tables if you are using Oracle.

Review the Prerequisites and best practices. You must ensure you have enough disk space.

The Tivoli Data Warehouse user must be directly granted the following system privileges, the privileges cannot be granted via a role:

The privileges required for migration can be revoked after all the desired migrations are complete.


The migration of non-partitioned tables to partitioned tables in a Tivoli Data Warehouse Oracle database uses a stored procedure generated by the schema publication tool. The stored procedure itself uses the DBMS_REDEFINITION package to load data from the non-partitioned table to the partitioned tables.


Procedure

  1. Configure the Summarization and Pruning agent to partition tables. For detailed steps, see "Specifying range partitioned tables for the Summarization and Pruning Agent" in the IBM Tivoli Monitoring Installation and Setup Guide.

  2. Stop all Warehouse Proxy agent and Summarization and Pruning agent instances.

  3. Backup the Tivoli Data Warehouse database.

  4. Edit the schema publication tool response file.

    1. Open the response file:

      • install_dir\TMAITM6\tdwschema.rsp for 32-bit Windows systems

      • install_dir\TMAITM6_x64\tdwschema.rsp for 64-bit Windows systems

      • install_dir/arch/bin/tdwschema.rsp

    2. Configure the following variables:

      KSY_PRODUCT_SELECT = migrate

      KSY_PRODUCT_FILTER = list of products to migrate
      An optional filter to indicate that only certain specific products are included. (If you do not specify a filter, all products in the specified category are included by default.) Specify the three-letter product codes of the products you want to include, separated by commas. You can find these codes using the tacmd histListProduct command (for more information, see the IBM Tivoli Monitoring Command Reference).

      KSY_TABLE_FILTER = list of tables to migrate
      An optional filter to indicate only specific tables. This filter can be used in addition to the KSY_PRODUCT_FILTER variable. Use the following command to get the list of tables that are available for a given product. Replace each space in the attribute group name with an underscore character. For a list of table names, use the following command:
      tacmd histListAttributeGroups -t <productcode>

      KSY_SUMMARIZATION_SELECTION = list of aggregation periods to migrate
      An optional filter that can be used in addition to the KSY_PRODUCT_FILTER and KSY_TABLE_FILTER variables. This variable has an additional option when the migrate mode is used. The R option allows you to migrate the detailed tables. Other options are as follows:

      • R: Migrate detailed tables only

      • H: Hourly

      • D: Daily

      • W: Weekly

      • M: Monthly

      • Q: Quarterly

      • Y: Yearly

      Filters can be combined. For example, to migrate the detail, hourly, and daily tables for the Windows OS agent: 

        KSY_PRODUCT=KNT
KSY_SUMMARIZATION_SELECTION=R,H,D

      KSY_SQL_OUTPUT_FILE_PATH = optional file path for SQL output
      An optional path to the directory where the generated SQL files are to be written. If you do not include this keyword, the current working directory is used.

      For more details and the complete syntax for each variable, refer to the comments in the response file.

  5. Ensure the Tivoli Enterprise Portal Server is started.

  6. If you have not already, export the CANDLEHOME variable. Execute the following commands: 

      set CANDLE_HOME=install_dir

      CANDLEHOME=/install_dir
export CANDLEHOME

  7. Run the schema publication tool script to generate the scripts required for migration.

    • install_dir\TMAITM6\tdwschema.bat for 32-bit Windows systems

    • install_dir\TMAITM6_x64\tdwschema.bat for 64-bit Windows systems

    • install_dir/arch/bin/tdwschema.sh

    The following scripts are generated for migration: tdw_migrate_setup.sql, tdw_migrate_step1.sql, and tdw_migrate_step2.sql.

  8. Execute the tdw_migrate_setup.sql script and view the results to ensure it executed successfully. 

      sqlplus <TDW userid>/<password>@<Oracle SID> @./tdw_migrate_setup.sql

    Use the tdw_migrate_setup.sql script only once, even when migrating in batches. Executing this script more than once breaks the ability of the migration process to restart if an error occurs. This script contains drop statements that might fail if the objects do not already exist. Do not consider these failures as errors, they can be ignored. The expected failures might return the following messages: DB21034E and SQL0204N.

  9. Execute the tdw_migrate_step1.sql script and view the results to ensure it executed successfully. 

      sqlplus <TDW userid>/<password>@<Oracle SID> @./tdw_migrate_step1.sql

    If any errors occur after this script is executed, the errors must be resolved before running the tdw_migrate_step2.sql script. Continue to re-execute this script until all errors are resolved.

    If the tdw_migrate_step1.sql script succeeds, a message is provided. For example: 

      Partitioning table "AIXTST"."KSY_TABLE_STATISTICS"

PL/SQL procedure successfully completed.

Table AIXTST.KSY_TABLE_STATISTICS successfully migrated.

PL/SQL procedure successfully completed.

    If this script encounters an error, a message is provided on the standard output. For example: 

      Code: -20002 Message: ORA-20002: 
Table "ITMUSER630"."K4X_USGS_STREAM_FLOW" is already partitioned.

    The following error messages apply:

    • 20000: Invalid parameter passed

    • 20001: Source table does not exist

    • 20002: Table already partitioned

    • 20003: Error when determining if source table can be partitioned

    • 20004: Creation of target partitioned table failed

    • 20005: Unable to drop target table when redefinition was aborted or finishing

    • 20006: Migration aborted

    • 20007: Error while finishing the table redefinition

    • 20008: Error during final table rename

  10. Execute the tdw_migrate_step2.sql script and view the results to ensure it executed successfully. 

      sqlplus <TDW userid>/<password>@<Oracle SID> @./tdw_migrate_step2.sql

    You can execute this script multiple times to resolve any errors. This script has no effect on tables that did not migrate successfully.

    If you are migrating in batches, the tdw_migrate_step1.sql and tdw_migrate_step2.sql scripts are executed for each batch.

  11. Optionally, you can backup the database.


Results

The tables you specified are now partitioned, and the source tables have been deleted.


What to do next

If any errors occurred during the migration, review the error messages from the execution of the scripts.


Parent topic:

Tivoli Data Warehouse range partition migrations

+

Search Tips   |   Advanced Search