The Migration Utility is a Java-based tool for automatically migrating the Rational Asset Analyzer database to a newer schema level. For example, you can use the Utility to upgrade your Rational Asset Analyzer database schema from 550 to 552.
While the Migration Utility is intended to be release independent, you should always use the newest version available from the product's Web site in the event fixes have been made to older database migrations. Updated versions of the migration utility will always be able to perform older migrations.
The Migration Utility has two distinct phases:
The purpose for splitting this up into two steps is to allow database administrators the opportunity to review the schema changes before they are made, and to modify the plan if they deem necessary.
The Migration Utility has the following features:
Before you perform migration, verify that all of the following conditions are met:
java -fullversion
To deploy the Migration Utility, do the following steps:
/config/*.cfg /lib/*.jar /META-INF/*.mf dbutil.bat /migrate/*.xsd /migrate/v550/*.xml /migrate/v552/*.properties and *.xml SetupJdbc.bat /verify/ /verify/v550/*.xml /verify/v552/*.xml
dbutil.bat -getInfoYou should see output similar to the following:
JVM version detected: [#.#.#] Database os detected as [UDB] Url: jdbc:db2:<database-alias> Version: ### OS: UDB Dist-enabled: true_or_false
From the Migration Utility directory, run the following command to create a Migration Plan:
dbutil.bat -createMigratePlan targetVersionwhere targetVersion is one of the following: v552
Below is a sample of the contents of a migration plan file:
....
<Task step='v312.40' class='Sql' desc='Recreate trigger on DMH_DPL_WEB_MODULE' autocommit='true'>
DROP TRIGGER DMH.DMHTRDW1;
CREATE TRIGGER DMH.DMHTRDW1
AFTER DELETE ON DMH.DMH_DPL_WEB_MODULE
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
DELETE FROM DMH.DMH_D_WEB_MDL_IIL
WHERE ASSET_ID = O.ASSET_ID;
</Task>
<Task step='v312.41' class='Sql' desc='Update version attribute' autocommit='true'>
DELETE FROM DMH.DMH_ATTR_NAME
WHERE ATTR_TYPE_ID = 500;
INSERT INTO DMH.DMH_ATTR_NAME (ATTR_TYPE_ID, DESCRIPTION)
VALUES (500, 'Version');
</Task>
....
|
From the Migration Utility directory, run the following command to run a Migration Plan:
dbutil.bat -runMigratePlan
As an alternative to automatically executing all Task steps in the Migration Plan, you can further control which Task steps are run by providing additional parameters to the -runMigratePlan command. The following examples illustrate this technique:
dbutil.bat -runMigratePlan v552.1-v552.9
dbutil.bat -runMigratePlan -v552.8
dbutil.bat -runMigratePlan v552.3-
dbutil.bat -runMigratePlan v552.10
When you run -runMigratePlan in a step-by-step fashion, you can monitor DB2 resources in the environment as each Task step is run, and restart in the event of a failure. You should never run a Task step out of sequence. For example, do not run Task step v###.3 until Task steps v###.1 and v###.2 have been completed.
An SQL Warning can be produced if there is no data from a table for a given task (such as an empty table, zero rows, etc.). You can generally ignore warnings of this nature and continue. For example:
INSERT INTO DMH.DMH_FLD_BTCD_NEW
(ASSET_ID, PHYSICAL_ID, ACCESS, FREE_TEXT, NAME, FULL_NAME, TYPE)
SELECT
ASSET_ID, PHYSICAL_ID, ACCESS, CAST(FREE_TEXT as VARCHAR(500)), NAME, FULL_NAME, TYPE
FROM
DMH.DMH_FIELD_BYTECODE
STATUS: WARNING
DELETE FROM DMH.DMH_SYSTEM_ATTR WHERE SYS_ATTR_ID = 500 STATUS: WARNING (SQL-100)
If any Task step in the migration fails, you are prompted to either ignore the problem and continue, or to stop the Migration Utility.
Typically, if any Task step fails, do the following steps:
dbutil.bat -runMigratePlan v312.11-
dbutil.bat -runMigratePlan v312.10-
While you were running the Migration Utility, an SQL error was reported, and you stopped the Utility. Within the dbutil.log, the error is reported as follows:
---------------------------------------------------------------------- [v410.31] - timestamp Delete some tables ---------------------------------------------------------------------- DROP TABLE DMH.DMH_MYTABLE1 STATUS: OK COMMIT STATUS: OK DROP TABLE DMH.DMH_MYTABLE2 [IBM][CLI Driver][DB2/NT] SQL0204N "DMH.DMH_MY_TABLE2" is an undefined name. SQLSTATE=42704 STATUS: ERROR Exception occurred while running the last command. Continue (Rollback occurs if 'N')? [Y(es)/N(o)] N
To determine the steps needed to get around the issue and restart the migration utility do the following steps:
dbutil.bat -runMigratePlan v410.32-
Do the following steps to complete the migration:
You can also get command-line usage information by running the dbutil.bat command with no parameters.
You can run the following commands from the Migration Utility directory. You should ensure that the environment settings in /config/User.cfg are updated and accurate.
| -createMigratePlan | ||||||||
|---|---|---|---|---|---|---|---|---|
|
To build the files that are used for input to the Migration Utility, execute the following command: dbutil.bat -createMigratePlan targetVersionwhere targetVersion is any of the following: v552. For example, if the database is currently at v550 and the targetVersion is v552, use the following command: dbutil.bat -createMigratePlan v552and expect output similar to this: JVM version detected: [#.#.#] Database os detected as [UDB] Database information detected... Version: 550 OS: UDB Dist-engable: true_or_false Database version appears to be at level v550 ---------------------------------------------------------------------- Verifying current schema of database as 'v550' ---------------------------------------------------------------------- Current database version verified. ---------------------------------------------------------------------- Creating migration plan from current version 'v550' to target version 'v552'. ---------------------------------------------------------------------- Creating migration plan for version 'v552' Finished without errors. The Migration Utility creates a migrate_plan directory, which contains XML files for each schema level that will be accommodated to get to the targetVersion. For the above example, the following files are generated: Migrate_v552.xml If the database is at a particular schema level and you attempt to create a Migration Plan for the same schema level, no Migrate_v*.xml file is created. For example, a database currently at v552 where targetVersion is the same: dbutil.bat -createMigratePlan v552 JVM version detected: [#.#.#] Database os detected as [UDB] Database information detected... Version: 550 OS: UDB Dist-engable: true_or_false Database version appears to be at level v552. Target version 'v552' is less than or equal to the current database version 'v552'. No plan created. Finished without errors. The Migration Utility deletes the migrate_plan directory and all Migrate_v*.xml files on subsequent executions of the -createMigratePlan command. | ||||||||
| -getInfo | ||||||||
|
To report values that the Migration Utility is using from the User.cfg file, run the following command: dbutil.bat -getInfo [ -args ]where -args are optional and can be one of the following values:
The optional -args simply override what is defined in User.cfg. If you want to quickly get information about a different Rational Asset Analyzer database without having to edit User.cfg, you can specify the dbUrl/user/pw of that database as override parameters. Run the command with no -args to see output similar to the following: JVM version detected: [#.#.#] Database os detected as [UDB] Url: jdbc:db2os390:database_alias Version: version OS: UDB Dist-enabled: true_or_false | ||||||||
| -runMigratePlan | ||||||||
|
To perform database migration by using a Migration Plan, run the following command: dbutil.bat -runMigratePlan [ optional-run-steps ]where optional-run-steps can take one of the following forms:
If you use -runMigratePlan without optional-run-steps, all Task steps in the /migrate_plan/Migrate_v*.xml files are run (that is, the entire Migration Plan that is available at the time of execution). At the completion of a -runMigratePlan run (in this case, migrating to v552), you should see the following output immediately after the last echoed SQL statement: ---------------------------------------------------------------------- Verifying current schema of database as 'v552' ---------------------------------------------------------------------- Current database version verified. Finished without errors. | ||||||||
| -verifySchema | ||||||||
|
To verify that a particular Rational Asset Analyzer database contains the expected tables, columns, indexes, and triggers for a given schema level, run the following command: dbutil.bat -verifySchema versionwhere version is any of the following: v552. For example, the following command: dbutil.bat -verifySchema v552produces the following output: JVM version detected: [#.#.#]
Verifying database with the following properties:
targetVersion = 'v552'
dbUrl = 'jdbc:db2:<database-alias>'
user = 'userid'
pw = 'password'
os = 'WINDOWS'
isDistEnabled = 'true_or_false'
tSchema = 'your_schema_name'
dbDriver = 'COM.ibm.db2.jdbc.app.DB2Driver'
Baseline comparison showed 0 differences.
Finished without errors.
A list of differences, if any, is displayed and identifies the missing or extra database objects (tables, columns, indexes, and triggers). For example, the following command: dbutil.bat -verifySchema v552produces the following output for a database with discrepancies: JVM version detected: [#.#.#]
Verifying database with the following properties:
targetVersion = 'v552'
dbUrl = 'jdbc:db2:<database-alias>'
user = 'userid'
pw = 'password'
os = 'WINDOWS'
isDistEnabled = 'true_or_false'
tSchema = 'your_schema_name'
dbDriver = 'COM.ibm.db2.jdbc.app.DB2Driver'
Baseline comparison showed 2 differences.
1: Database is missing index 'ASSI001' in table 'DMH_ASSETS'
2: Database is missing trigger 'DMHTRSB7' in table 'DMH_DB2_PROC'
It is recommended that you investigate and resolve the highlighted differences. The verification is automatically performed at the start of a -createMigratePlan command and at the end of each completed migration level of a -runMigratePlan command. |
| Table | Primary Key Index | Alternate Index |
|---|---|---|
| DMH_BP_MODEL | MODEL_ID | |
| DMH_BP_PROCESS | MODEL_ID + PROCESS_ID | |
| DMH_BR_POLICY | POLICY_ID | |
| DMH_BR_POLICY_RS | PLOICY_ID + RULE_SET_ID | |
| DMH_BR_RULE | RULE_ID | |
| DMH_BR_RULE_SET | RULE_SET_ID | |
| DMH_BR_RS_RULE | RS_RULE_ID | |
| DMH_BT_CATEGORY | CATEGORY_ID | |
| DMH_BT_RELATD_TERM | TERM_ID_1 + TERM_ID_2 | |
| DMH_BT_SYNONYM | TERM_ID_1 + TERM_ID_2 | |
| DMH_BT_TERM | TERM_ID | |
| DMH_FILE_LITERAL | LITERAL_ID + FILE_ID | RDILTF2 |
| DMH_FILE_SYMBOL | SYMBOL_ID + FILE_ID | RDISTF2 |
During the creation of a Migration Plan, the Migration Utility reads a version-specific /migrate/v*/Migrate.xml file and outputs a /migrate_plan/Migrate_v*.xml file. Details for the /migrate/v*/Migrate.xml file are presented here for informational purposes only. Do not modify these files unless instructed to do so by IBM support. However, you should review and update the /migrate_plan/Migrate_v*.xml files as appropriate for your environment.
The general format of the /migrate/Migrate.xml files are as follows:
<TaskSequence version='version-number'...>
<!-- Define vars used in this file -->
<Vars>
...zero or more variable definitions...
</Vars>
...zero or more task definitions...
</TaskSequence>
|
This is a sample Migrate.xml file:
<TaskSequence version='v311'...>
<!-- Define vars used in this file -->
<Vars>
<Var name='dbName' value='$C{User.Database.name}'/>
<Var name='tschema' value='$C{$User.Database.tschema}'/>
<Var name='tsprefix' value='$C{$User.ZOS.tsprefix}'/>
<Var name='isDistEnabled' value='$C{User.Database.isDistEnabled}'/>
...
</Vars>
<Task class='Sql' if='${isDistEnabled}' desc='Create a new table.'>
CREATE TABLE ${tschema}.DMH_NEW_TABLE1 (
COLUMN_1 INTEGER NOT NULL,
COLUMN_2 INTEGER NOT NULL,
PRIMARY KEY (COLUMN_1))
%IF[${isZOS}]
IN ${tschema}.${tsprefix}%COND[${singleTS}==true?TBS:NEW]
%ENDIF
;
</Task>
<Task class='Sql' if='! ${isDistEnabled}' desc='Create a new table.'>
CREATE TABLE ${tschema}.DMH_NEW_TABLE2 (
COLUMN_1 INTEGER NOT NULL,
COLUMN_2 INTEGER NOT NULL,
PRIMARY KEY (COLUMN_1))
;
</Task>
<Task class='v311.ContainerUpdate' if='${isDistEnabled}' desc='...'/>
</TaskSequence>
|
Given the above file, the corresponding generated /migrate_plan/Migrate_v311.xml file for a z/OS database with distributed tables enabled might look like the following file:
<TaskSequence version='v311'...>
<!-- Define vars used in this file -->
<Vars>
<Var name='dbName' value='DMHDB'/>
<Var name='tschema' value='DMH'/>
<Var name='tsprefix' value='DMHTS'/>
...
</Vars>
<Task step='v311.1' class='Sql' desc='Create a new table' autocommit='true'>
CREATE TABLE DMH.DMH_NEW_TABLE1 (
COLUMN_1 INTEGER NOT NULL,
COLUMN_2 INTEGER NOT NULL,
PRIMARY KEY (COLUMN_1))
IN DMH.DMHTSNEW
;
</Task>
<Task step='v311.2' class='v311.ContainerUpdate' desc='...' autocommit='true'>
</Task>
</TaskSequence>
|
The following transformations are done from the /migrate/Migrate.xml file to the /migrate_plan/Migrate_v*.xml file:
©Copyright IBM Corporation 2005, 2009. All rights reserved.