IBM Rational Asset Analyzer

Migration Utility Guide for System z

Introduction

While the migration utility is intended to be release independent, you should always use the newest version available from the product website 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.

There are two phases to complete the database migration using the migration utility.

Create migration plan

A migration plan consists of one or more generated XML files containing the SQL statements that are run to migrate the database to a specific schema level. After the plan is complete, the database administrator can review the scheme changes and make modifications before the plan is run.

Run the migration plan

Runs the migration plan against the Rational Asset Analyzer database to be migrated. You can specify if all tasks, a single task, or a range of tasks are to be performed during a particular migration run.

The migration utility has the following features:

• The migration utility determines the existing schema level of the database and makes the appropriate adjustments.
• The migration utility automatically generates XML-based migration files that you can examine and modify before you run the migration process. The XML files enable Java method and shell script calls to be interspersed with SQL.
• You can restart the migration utility from any point in the migration process. Thus you can resolve a problem, and restart at the point of failure.
• The migration utility allows a staging directory to be specified during configuration, and it is the location where the migration plan and other output files will be written when the migration utility is run. For example, the migration utility records activity from the last command execution in the dbutil.log file. A backup copy of dbutil.log, containing information from the previous to last command execution, is created in dbutil.log.2. The staging directory is provided for those shops where the migration utility is deployed in a read-only directory.
• The migration utility supports i18n.

Getting help

As you are planning your migration, you may have questions, concerns, or circumstances specific to your environment that you should resolve before implementing your migration plan. You can contact IBM Software Support for assistance. The IBM Rational Asset Analyzer forum is another method for asking questions and obtaining assistance. The forum is located at the following URL: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=2046&cat=24

Getting Started

1. The migration utility does not create a backup copy of the Rational Asset Analyzer database. You should create a backup of your database before proceeding with the migration process.
2. The Java runtime (JRE) version 1.5 or later is required. Issue the following command in OMVS to determine the JRE level in your environment.

    java -fullversion 

3. You must have authority to create and edit UNIX System Services (USS) files. The migration utility file can be downloaded and extracted in any HFS directory where the user has read/write authority.
4. The OMVS shell is configured for JDBC access such that the JDBC drivers are accessible in the current environment. The settings that are active in your environment can be verified by using the echo command, for example:

   echo $STEPLIB
   echo $LIBPATH
   echo $CLASSPATH
   echo $DB2SQLJPROPER

5. You must have DB2 authority (for example, DBADM, SYSADM) or access to a user ID and password that has the authority to perform CREATE, UPDATE, DELETE, and other similar tasks against the database being migrated.
6. You must have DB2 authority for reading the following DB2 SYSIBM catalog tables:
   • SYSCOLUMNS
   • SYSINDEXES
   • SYSTABLES
   • SYSTRIGGERS
7. You must be logged onto TSO with a large enough REGION size to run a Java application in OMVS (such as REGION=512000). If you encounter OutOfMemory exceptions, use a larger REGION size.
8. The access path statistics for the database must be up-to-date. If in doubt, execute the DB2 RUNSTATS utility using &hlq.SDMHREXX(DMHINIT) Option 3.2.
9. Verify that all user activity is completed. Also, ensure that the WebSphere Application Server and HTTP Server are stopped.

Downloading the migration utility

Complete the following steps to download the migration utility and complete changes required to use the utility.

1. Create a directory in the OMVS shell for the migration utility files by using a command such as:

   mkdir /usr/lpp/dmh/raadbutil

2. Copy the migration utility file into the new directory.
3. Extract the migration utility file using the following commands:

   cd /usr/lpp/dmh/raadbutil
   tar xvf raadbutil_zos.tar

4. Verify that the extraction was successful. The following files and directories should be in the directory:

   dbutil.sh
   MigUtil_win.htm
   MigUtil_zOS.htm
   SetupJdbc.sh
   
   /META-INF/*.mf
   /config/*.cfg
   /lib/*.jar
   
   /migrate/*.xsd
   ...
   /migrate/v601/*.properties and *.xml/
   /verify/v220/
   ...
   /verify/v601/*.xml

5. Review the sections in this document that are specific to the release you are migrating for additional information.
6. If the OMVS shell is not configured for JDBC access, the SetupJdbc.sh file can be used to set up the necessary environment variables. The settings that are active in your environment can be verified by using the echo command, for example:

   echo $STEPLIB
   echo $LIBPATH
   echo $CLASSPATH
   echo $DB2SQLJPROPERTIES

7. Update the /config/User.cfg file to specify your environment and database settings. Comments are provided in the file to describe the parameter fields. You will be required to provide values for several parameters, including the following:
   • Database name
   • Schema name
   • DB2 location name
   • User ID
   • Password
   • Database driver
   • Database locale
   • Buffer pool for indexes
   • Buffer pool for non-LOB table spaces
   • Buffer pool for LOB table spaces
   • DB2 storage group
   • Table space prefix name

   Comments are provided in the User.cfg file to describe the parameter fields and provide sample values. In most cases, you can review the values for these fields currently being utilized for your installation in the following locations:

   • Member DMHPARMS in data set &hlq.CONFIG.REXX
   • wsaaConfigDir/config/Common.cfg
   • &hlq.SDMHREXX(DMHINSTL) Option 0
8. After you have completed configuration, issue the following command from the migration utility directory to verify your settings:

   dbutil.sh -getInfo

   You should see output similar to the following:

   JVM version detected: [#.#.#]
   Url: jdbc:db2os390:your_DB2_location
   Version: ###
   OS: DB2_ZOS

Creating a migration plan

To create the migration plan, issue the following command from the migration utility directory:

dbutil.sh -createMigratePlan targetVersion

See Appendix A. dbutil.sh command reference for details on specifying the dbutil.sh command.

After the create migration plan process completes, an XML file is created for each schema level between the starting and target versions. For example, if the starting version is v552 and target is v600, the migration will contain a plan for v554 and v600.

On z/OS, if the generated migration plan files are not output correctly, such as they are not readable, or part of the files such as translated strings appear unintelligible, the problem might be because of one of the following:

• The system is using a code set other than Cp1047 or Cp939 on Japanese machines
• The Locale on your machine makes it impossible to correctly determine the correct code set

To fix this problem, you can manually specify the code set that is used on your system by setting the EXT_FILE_ENCODING variable in SetupJdbc.sh file.

Migration plan considerations

To ensure a smooth migration, ensure that you understand the entire process before creating and running a migration plan. Review the entire migration utility instructions contained in this document. There are considerations for pre-migration, migration, and post-migration. It is best to be prepared for each step in advance. The Database schema changes topic contains details related to product changes that affect data. It also contains details about the database changes for each release.

Because the migration utility does not create a backup copy of the Rational Asset Analyzer database, there is no overall rollback feature. There are several intermittent commits that allow for some recovery. In case there are problems, you should create a backup of your database to recover data.

You can choose as a strategy to run the entire migration plan at once or it can be done in steps. A simple migration, such as dropping obsolete objects or creating a small set of new object is appropriate for running the entire migration at once. However, for more complex migrations, the step approach is a better choice. The following are examples of a complex migration:

• Migrating across multiple versions
• Migrating to Ration Asset Analyzer Version 6.0 from an earlier version
• Migrating large amounts of inventory data

The step approach makes it easier to recover and restart the process when an error occurs. When you run the migration plan in a step-by-step fashion, you can monitor DB2 resources in the environment for each step and fix any necessary issues. When using this approach, never run a Task step out of sequence.

Migration plan files

The XML files are placed in a directory named migrate_plan. If a staging directory is specified in the config/User.cfg configuration file, the XML files are put in the staging directory.

The XML files are named Migrate_version.xml. Each migration XML file contains a list of SQL statements that will be executed by the migration utility.

Before continuing review and update these files to handle any environment-specific requirements, such as modify the SQL to handle especially large tables that might fill up the log file, update or add INDEX statements if you have performed customization.

Details for the /migrate/version/Migrate.xml file are described below for informational purposes only. Do not modify these files unless instructed by IBM support.

The /migrate/Migrate.xml files have the following format:

<TaskSequence version='version-number'...>       
 <!-- Define vars used in this file -->     
  <Vars>         
 ...zero or more variable definitions...       
</Vars>       
 ...zero or more task definitions...  
</TaskSequence>  

Example

The following is an example of a Migrate.xml file followed by a corresponding Migrate_version.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}'/> 
            ...
 	</Vars>

  	<Task class='Sql' 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='v311.ContainerUpdate' 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_version.xml file:

• All variables and expressions are resolved.
• Task steps where the IF attribute resolves to 'false' are removed from the migration plan.
• Task step names are automatically added to all Task elements

Implementing the migration plan

If you have not already made a backup copy of your database, create the backup now before proceeding.

To start the migration plan, issue the following command from the migration utility directory:

dbutil.sh -runMigratePlan task_steps

See Appendix A. dbutil.sh command reference for details on specifying the dbutil.sh command.

After the migration utility starts, the current level of the database is determined based on the existence of specific tables, columns, indexes, and triggers.

The migration utility checks to see if there are any extra or missing tables, indexes, columns, or triggers and reports any discrepancies. Messages are written to the console and dbutil.log file located in the migration utility installation directory.

If any errors occur while implementing the migration plan, contact IBM Software Support before proceeding.

Completing the migration

Procedure

1. On the ISPF Install menu, select Option 2, Bind DB2 packages and plans.
2. If your DB2 subsystem is not configured for distributed DB2, edit the BIND PLAN control statements. The PKLIST parameter specifies each package name beginning with an asterisk. Edit all package specifications to remove the asterisk and period, as shown in the following example:

   Unedited Version	Edited Version
   BIND PLAN(DMHP0700) QUALIFIER(DMH) - PKLIST(*.DMH.DMHDCOM - *.DMH.DMHDROL - *.DMH.DMHD233 - . . .	BIND PLAN(DMHP0700) - QUALIFIER(DMH) - PKLIST(DMH.DMHDCOM - DMH.DMHDROL - DMH.DMHD233 - . . .

3. Submit the job by issuing the SUB command.
4. Check each step in the JESYSMSG JES2 for a condition code of 0000.
5. On the ISPF Install menu, select Option 3, Grant DB2 privileges.
6. You are presented with a JCL stream. Edit the SQL statements if you need to modify access to the database.
7. Submit the job by issuing the SUB command.
8. Check the steps in the JESYSMSG JES2 output for a condition code of 0000. If this is not the first run of the JCL to grant DB2 privileges, the DMHTEP2 step name might result in a condition code of 0004. To verify the condition code for your job, review the SYSPRINT DMHTEP2 output, scanning for SQLCODE = 562 on any of the GRANT statements. Below is an example of the output messages for this condition for package DMH.DMHDCOM:

   ***INPUT STATEMENT:  GRANT ALL ON PACKAGE DMH.DMHDCOM TO PUBLIC;
   SQLWARNING ON GRANT    COMMAND, EXECUTE  FUNCTION
   RESULT OF SQL STATEMENT:
   DSNT404I SQLCODE = 562, WARNING:  A GRANT OF A PRIVILEGE
   WAS IGNORED BECAUSE THE GRANTEE ALREADY HAS THE PRIVILEGE FROM
   THE GRANTOR
   DSNT418I SQLSTATE = 01560 SQLSTATE RETURN CODE

   You can ignore this condition.
9. If there are any errors (except SQLCODE = 562) correct them, and then resubmit the job.
10. Using the ISPF Init menu, select Option 3 Utilities, then Option 2 Submit RUNSTATS.
11. Submit the job by issuing the SUB command.
12. Check each step in the JESYSMSG JES2 output for a condition code of 0000.
13. If there are any errors, correct them, and then resubmit the job.
14. Optionally, after all the migration tasks have successfully completed, you can return to the OMVS shell and delete the migration utility files and directory, using the following command: rm -rf /usr/lpp/dmh/directory_name. If you specified a staging directory in the User.cfg file, you should delete that directory as well.

Database schema changes

Remember that the migration path is dependent on the current version of the database and the level being migrated to. You should review the changes starting with the "from" version level and each interim level until you reach the final target version level. For example, if you are migrating from v552 to v600, you should review changes for v554 and v600.

The following notes highlight particular changes and user actions, when required, for specific migration levels. This information should be reviewed and understood prior to the start of any migration effort.

• Database schema changes for v420
• Database schema changes for v421
• Database schema changes for v510
• Database schema changes for v511
• Database schema changes for v552
• Database schema changes for v554
• Database schema changes for v600
• Database schema changes for v601

Database schema changes for v420

WebSphere Application Server distributed scan roots

If you have WebSphere Application Server scan roots defined in the database, you will need to manually update those scan roots through the web UI after performing this migration. This is because necessary information about these crawl roots are specified in XML files on the distributed host, which is not accessible from here.

Runtime environments specified for distributed scan roots

If you have mapped any runtime environments to distributed scan roots in Admin.cfg, you will need to manually create these runtime environments again through the web user interface and associate these with the scan roots through the UI. This is because the associations specified in Admin.cfg and the associated runtime environment XML files are not accessible from here.

Distributed sites

If you have previous scanned distributed assets, you must run the distributed scanner once to update the contents of the DMH_SITE table. Failure to do so may result in 404 errors when attempting to view distributed source code through the web interface. Note that you do not need to actually scan any distributed scan roots, but rather simply start the scanner. The DMH_SITE initialization occurs at the beginning of all scans

Database schema changes for v421

Rebuilding run units

Performance improvements for processing and storing executable names, such as run unit names and aliases, have been implemented in the V4.2.1 PTF. The majority of the changes are handled automatically using the Migration Utility. However, as a result of these changes, all run units must be rebuilt in order to re-establish their calling hierarchies. The Migration Utility will change the Analysis status of each run unit in order to force it to be rebuilt during the next invocation of the MVS postprocessor. Therefore, after all PTF installation, migration and configuration tasks are completed, and the WebSphere Application Server has been restarted, the MVS postprocessor should be started using the option presented in the drop-down list on the Analysis queue information pag, for exampleDatabase > Analysis queue. You should complete this post-migration task prior to any other user activity, such as: additional inventory and impact analysis. There is a reminder message that will be produced at the end of the migration for this level.

Analyzing programs that contain BMS references

Performance improvements for handling program references to BMS maps and map sets have been implemented in the V4.2.1 PTF. However, existing metadata does not contain enough details to completely and automatically retrofit the necessary changes. Therefore, additional manual effort is required to migrate the data for existing programs that contain CICS SEND and RECEIVE MAP commands. We encourage users to reanalyze the files listed in the BMS_Reference.txt, either through the browser interface by queuing the Container or individual Files for analysis, or via the Batch Update JCL provided in &hlq.SDMHCNTL(DMHJCLBU) or &hlq.CONFIG.CNTL(DMHJCLBU). If you do not reanalyze the identified programs, the BMS map and map set definition results presented on the "Application" and "Site" details pages will be incorrect. There is a reminder message that will be produced at the end of the migration for this level.

Database schema changes for v510

Considerations and changes

Starting with v510, when a missing copy or include file condition is detected, the WebSphere Studio Asset Analyzer scanners will consistently report message DMH1310E, instead of outputting one of several other messages, such as AES1026E, AES1027E, AES1036E, AES1038E, DMH3200E, DMH3201E, IGYLI0048, IGYLI0049, IBM1848I, IBM1851I, and IBM3841I. These messages are now obsolete and will, over time with re-analysis of assets, be replaced with reporting only DMH1310E when a missing file error occurs.

Any existing source files that are reporting missing files will have Analysis status=Error and are likely already under investigation for resolution. You should prioritize reanalyzing the files listed in the Obsolete_Messages.txt, either through the browser interface by queuing the Container or individual Files for analysis, or using the Batch Update JCL provided in &hlq.SDMHCNTL(DMHJCLBU) or &hlq.CONFIG.CNTL(DMHJCLBU). This will eliminate the obsoleted messages from the DMH_ACTIVITY_LOG table, ensure accurate reporting of the missing components, and take advantage of the performance improvement for presenting the error data in the browser interface. There is a reminder message that will be produced at the end of the migration for this level.

Summary of database changes

In addition, if distributed asset support is enabled:

The following are new triggers if distributed asset support is enabled:

Database schema changes for v511

Summary of database changes

Database schema changes for v552

Summary of database changes

The following are new component types in v552:

• 70, Import file
• 400, Category
• 401, Term
• 430, Model
• 431, Process
• 460, Policy
• 470, Rule set
• 480, Rule

Database schema changes for v554

To provide support for multiple named tables in SQL DECLARE CURSOR statements, the SQL_TABLE_ID has been dropped from the DMH_SQL_CURSOR table to eliminate the limitation of associating a single DB2 table to the cursor. The potential 1 - n DB2 tables are now recorded in a new relationship table, DMH_SQL_CURSOR_TAB. During migration, programs found to contain EXEC SQL DECLARE CURSOR statements will be queued to be analyze again.. After running the Analysis queue processor, the user interface is expected to reflect EXEC SQL FETCH statement references to the 1 - n DB2 tables comprising the cursor. Following migration, but before running the Analysis queue, these DB2 table references from cursors will not be visible in the user interface.

If the database was manually migrated to v554 to include an interim development driver to resolve the issue described above, you might receive the following warning message specific to the DMHTRCU1 trigger. If you are migrating to v600, you can ignore this warning. The DMHTRCU1 trigger will be replaced when the migration tasks for v600 are performed.

Comparison of current database schema with expected schema at this particular level showed 1 differences.
      1: Trigger target tables on 'DMHTRCU1' was expected to be 'DMH_ACTIVITY_LOG-D,DMH_ACTUAL_PARM-D,DMH_ANALYSIS_QUEUE-D,DMH_CHANGE_FOCU
      S-D,DMH_CHANGE_IMPACT-D,DMH_LINK_SOURCE-D,DMH_LINK_TARGET-D,DMH_CONTROL_XFER-D,DMH_CU_CHAR_ATTR-D,DMH_CU_DATA_USE-D,DMH_CU_INCLUDE-D
      ,DMH_CU_LABEL-D,DMH_CU_NUM_ATTR-D,DMH_CU_STMT-D,DMH_DATA_ELMT_ALAS-D,DMH_DATA_RECORD-D,DMH_DATA_STORE-D,DMH_DATA_STORE_DEA-D,DMH_ENT
      RY_POINT-D,DMH_FORMAL_PARM-D,DMH_LIT_FOR_DEA-D,DMH_SQL_COLUMN-D,DMH_SQL_CURSOR-D,DMH_SQL_CURSOR_TAB-D,DMH_SQL_TABLE-D,DMH_SQL_PROC-D
      ,DMH_RUN_UNIT_CNTNT-D,DMH_RUN_UNIT_CNTNT-U,DMH_RUN_UNIT_CNTNT-U' but was actually 'DMH_ACTIVITY_LOG-D,DMH_ACTUAL_PARM-D,DMH_ANALYSIS
      _QUEUE-D,DMH_CHANGE_FOCUS-D,DMH_CHANGE_IMPACT-D,DMH_LINK_SOURCE-D,DMH_LINK_TARGET-D,DMH_CONTROL_XFER-D,DMH_CU_CHAR_ATTR-D,DMH_CU_DAT
      A_USE-D,DMH_CU_INCLUDE-D,DMH_CU_LABEL-D,DMH_CU_NUM_ATTR-D,DMH_CU_STMT-D,DMH_DATA_ELMT_ALAS-D,DMH_DATA_RECORD-D,DMH_DATA_STORE-D,DMH_ 
     DATA_STORE_DEA-D,DMH_ENTRY_POINT-D,DMH_FORMAL_PARM-D,DMH_LIT_FOR_DEA-D,DMH_SQL_COLUMN-D,DMH_SQL_CURSOR-D,DMH_SQL_TABLE-D,DMH_SQL_PRO
      C-D,DMH_RUN_UNIT_CNTNT-D,DMH_RUN_UNIT_CNTNT-U,DMH_RUN_UNIT_CNTNT-U'.
 These differences could indicate custom changes made to the database.
      These differences should be investigated before proceeding with the migration plan.      
Proceed creating migration plan?  Continue? {Y/N}

If you are not migrating to v600, you should drop and create the trigger again as described in the installation instructions provided along with the development driver.

Summary of database changes

Database schema changes for v600

Considerations and changes

The following items should be reviewed.

Container and File scanning consolidation

The migration utility does not merge the two sets of Containers and Files. Therefore, all existing distributed asset metadata in the database will be dropped. The original scan roots will be detected and queued again for analysis. After running the Analysis queue processor, the J2EE asset data is expected to be in the same state as before migration.

Additional considerations:

• Custom updates to data in any of the distributed asset tables will be lost.
• Running the Analysis queue will remove any nonexistent distributed assets from:
  • The DMH_LINK_SOURCE and DMH_LINK_TARGET tables (User-defined relationships)
  • The DMH_CHANGE_FOCUS and DMH_CHANGE_IMPACT tables (Impact analysis projects)
  and trigger the UDRs and IA projects to be flagged as "stale".

HTTP_IP_NAME setting

For any Sites already in the inventory, the value for the HTTP_IP_NAME column of the DMH_SITE table should be reviewed to validate it properly reflects the scanner daemon URL. In prior releases of Rational Asset Analzyer, the user interface allowed setting this column to any value a user wanted. As a result, there may be a non-blank value in the column that is not relevant in V6.0.0. You must first update the HTTP_IP_NAME column(s) before starting the Analysis queue processor for re-scanning the distributed assets. In some cases, this might be a simple update to change the port number (from http://somehost:9080 to http://somehost:8080). In other cases, you may want to set the HTTP_IP_NAME to an empty string because the Site will not be used for scanning distributed assets. These changes can be accomplished using the SQL UPDATE statement (e.g. UPDATE XXX.DMH_SITE SET HTTP_IP_NAME ... ). Refer to DB2 SQL Reference guides for further information about syntax, rules, and examples.

Support for scanning WebSphere Application Server has been removed. During migration to V6.0.0, all existing metadata and database objects for WebSphere Application Server assets will be dropped.

Business Rule Mining The database objects for Business Rule Mining have changed since their original introduction in the Technology Preview. There is no migration path for the Technology Preview. Therefore, during migration to V6.0.0, all existing business rule objects in the database will be dropped.

Support for scanning WebSphere Application Server has been removed.

During migration to V6.0.0, all existing metadata and database objects for WebSphere Application Server assets will be dropped.

Business Rule Mining

The database objects for Business Rule Mining have changed since their original introduction in the Technology Preview. There is no migration path for the Technology Preview. Therefore, during migration to V6.0.0, all existing business rule objects in the database will be dropped.

Users and user attributes

As groundwork for future expansion and persistence of additional user preferences and settings, the DMH_USER_CHAR_ATTR and DMH_USER_SETTING tables have been dropped from the data model. During migration, user names from the DMH_USER_CHAR_ATTR table will be assigned an ID and inserted into the DMH_USER table. Corresponding user attributes stored in the DMH_USER_CHAR_ATTR table will be moved to the DMH_USER_ATTR table, which has a foreign key to the DMH_USER table.

Custom Query inventory

Review the data model changes for v600. They are presented below in Summary of Changes. Following migration, inspect the Is valid column on the Custom query summary page, as this information indicates if the corresponding custom query contains valid SQL for the current Rational Asset Analyzer database. Possible values are:

Yes (valid)

This is valid state.

No

No identifies a custom query that is no longer valid because the underlying database schema has changed.

Unknown

Unknown indicates a custom query has not be executed since a database migration has occurred, and therefore, it is not known whether it is valid or not. To revalidate custom queries that are in the Unknown state, execute the custom query to re-establish a Yes or No state for the Is valid column, or execute the Validate table action (requires a user ID with Administrative authority).

SQL Cursor and DB2 Table reference resolution

To provide support for multiple named tables in SQL DECLARE CURSOR statements, the SQL_TABLE_ID has been dropped from the DMH_SQL_CURSOR table to eliminate the limitation of associating a single DB2 table to the cursor. The potential 1 - n DB2 tables are now recorded in a new relationship table, DMH_SQL_CURSOR_TAB. During migration, programs found to contain EXEC SQL DECLARE CURSOR statements will be queued for re-analysis. After running the Analysis queue processor, the user interface is should reflect EXEC SQL FETCH statement references to the 1 - n DB2 tables comprising the cursor. Following migration, but before running the Analysis queue, these DB2 table references from cursors will not be visible in the user interface.

Distributed asset support always enabled

Previously optional creation of distributed asset database objects is now standard and mandatory. As a result of migration, the Rational Asset Analyzer database will contain all the database objects for distributed asset support.

Summary of database changes

Tables listed below have undergone modifications to their definitions, such as added, changed, deleted, and renamed columns or primary keys. The table itself may have been renamed, as indicated, and/or had indexes added, changed, and dropped.

If you did not have distributed asset support enabled in V511, consider the following list of tables for distributed assets as new, rather than changed, for V600.

If your site did not have distributed asset support enabled in V511, disregard the following list.

Auxiliary tables listed below have been removed from the database because either the CLOB column has been dropped from the base table, or the base table has been dropped altogether. If your site did not have distributed asset support enabled in V511, disregard the following list.

Database schema changes for v601

Processing of DSN=NULLFILE

The JCL analyzer has been modified to eliminate building unnecessary relationship links between Job step and Data set assets when a dummy data set is specified on a DD statement. As a result, during migration, any Batch jobs in the inventory found to be using DSNAME=NULLFILE and/or DSN=NULLFILE will be queued for analysis again. Post-migration, no row will exist for DMH_DATASET.DATASET_NAME = NULLFILE, and the DMH_DD_CONTAT.DATASET_ID will be zero for those job step DDs formerly pointing to NULLFILE. Post-migration user activity is required to run the Analysis queue processor to analyze the effected Batch jobs again.

Orphaned data in DMH_RUN_UNIT_CNTNT table

For cases where Run units were manually deleted from the inventory (for example, not through the user interface Action link), there arises a condition where rows in the DMH_RUN_UNIT_CNTNT table reference Run unit IDs that no longer exist in the inventory. This causes the Run units with missing source count to be incorrect and misleading. To resolve this condition, the Migration Utility will drop the existing component delete trigger (for example, DMHTRRN1) and re-create it to provide explicit delete behavior for the DMH_RUN_UNIT_CNTNT table. Also during migration, any rows found in the DMH_RUN_UNIT_CNTNT table that do not have a corresponding RUN_UNIT_ID row in the DMH_RUN_UNIT table will be removed.

No user activity is required for this update.

Annotation text corrections

For certain distributed asset types, such as enterprise application, web application, EJB-JAR, J2EE client, and J2EE RAR file, annotation text was sometimes incorrectly related to a Container asset rather than a File asset. During migration, the annotation text is updated so that it is correctly related to the File. If there is already annotation text of the same type related to the File, the one incorrectly related to the Container will be deleted.

No user activity is required for this update.

New and changed project types

The DMH_PROJECT_TYPE table has been updated to collapse all the previous Impact analysis type rows into a single type (for example, 1100), and to introduce a new type for Business rule mining projects (for example, 1200). During migration, any Impact analysis projects found to be using PROJECT_TYPE_ID 1000, 1001, 1002, 1003, 1004, 1039 or 1040 will be updated to use PROJECT_TYPE_ID 1100.

Post-migration user activity is required to manually update any Custom queries that reference the assetType="Project" to instead use assetType="IaProject".

Business rule mining

A User details page has been added to RAA and this page allows activation of a Business rule mining (BRM) project. The User details page is accessed by clicking on the down arrow next to the user name in the upper-right of the browser page, then selecting View my profile.

Once a user has selected a BRM project, the BRM project will be indicated as the active project for the user. To enable this capability, a new attribute (ATTR_TYPE_ID = 726, DESCRIPTION=Business rules mining project to user relationship) has been inserted into the DMH_ATTR_NAME table for use in representing project-user relationships. For example, an entry in the DMH_USER_ATTR table, with ATTR_TYPE_ID = 726 and pointing to the user's DMH_USER row, is created to indicate the particular relationship.

Once an active project has been established by a user, the user can add Business term properties to the BRM project by choosing Actions -> Include in active BRM project from the Business term property details page. The BRM project will then show all the Business rules that use these Business term properties in the Business rule mining project details page. These rules can then be exported to iLog JRules or JRules for COBOL from the Actions menu.

A new relationship table, DMH_BR_RULE_FACT, has been added tot he data model for storing the relationship between a Business rule and the Business term properties used by that rule. This allows RAA to determine which Business rules belong to a given Business rule mining project. During migration, a status indicator row will be inserted into the DMH_SYSTEM_ATTR table to signal an automatic refresh of the DMH_BR_RULE_FACT table. The data refresh will occur during post-migration RAA start-up initialization.

No user activity is required for these updates.

Support for scanning Java source has been removed

During migration, all existing meta data and database objects for Java source scanning will be dropped. Data related to Java bytecode scanning is unaffected.

No user activity is required for this update.

DMH_ANALYSIS_PARMS table

A new table, DMH_ANALYSIS_PARMS, has been added to the data model. This table will augment the DMH_ANALYSIS_QUEUE.ANALYSIS_PARMS column when analysis parameters are too large to fit into the VARCHAR(80) column, such as Container scan load paths on type 12 Analysis queue records.

No user activity is required for this update.

DMH_ASSET_FILE and DMH_ASSET_CONT tables

Two new tables, DMH_ASSET_FILE and DMH_ASSET_CONT, have been added to the data model. These tables will be used to:

• Ensure data integrity for distributed assets when Files and Containers are deleted.
• Provide a means to determine when symbol scanning was included for a particular File.
• Enable incremental scanning of distributed assets and symbol scans.

No user activity is required for this update.

New database integrity check queue item

A new type 22 Analysis queue record has been introduced to the data model, and is a place-holder for future functionality. The intent is to search for data abnormalities in the database, and report and/or repair them as deemed appropriate.

No user activity is required for this update

Summary of database changes

The following tables list new triggers and trigger packages:

• Table 37
• Table 38

The Table 40 lists tables have undergone modifications to their definitions, such as added, changed, and deleted columns. The table itself may have Indexes added and changed, and/or now be declared as VOLATILE.

Appendix A. dbutil.sh command reference

-createMigratePlan

To build the files that are used for input to the migration utility, use the following command:

dbutil.sh -createMigratePlan targetVersion

-createMigratePlan

targetVersion

Indicates the target version the database is being migrated. Specify either v311, v312, v410, v420, v421, v510, v511, v550, v552, v554, v600, or v601. If a version that is specified is the same as the current version of the database, no migration files are created.

If the database is currently at v312 and the target version is v421, issue the following command:

 dbutil.sh -createMigratePlan v421

and expect output similar to this:

JVM version detected: [#.#.#]

Database information detected...
Version: 312
OS: DB2_ZOS

Database version appears to be at level v312

----------------------------------------------------------------------
Verifying current schema of database as 'v312'
----------------------------------------------------------------------
Current database version verified.

----------------------------------------------------------------------
Creating migration plan from current version 'v312' to target version 'v421'.
----------------------------------------------------------------------
Creating migration plan for version 'v410'
Creating migration plan for version 'v420'
Creating migration plan for version 'v421'
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 migrate to the target version. For the above example, the following files are generated:

Migrate_v410.xml
Migrate_v420.xml
Migrate_v421.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_yversion.xml file is created. For example, a database is currently at the v312 level and the following command is issued:

dbutil.sh -createMigratePlan v312

The following message is received, indicating that a migration plan was not created.

JVM version detected: [#.#.#]

Database information detected...
Version: 312
OS: DB2_ZOS

Database version appears to be at level v312.
Target version 'v312' is less than or equal to the current database version 'v312'.
No plan created.
Finished without errors.

-getInfo

Use the dbutil.sh -getInfo command to return information about the database.

The dbutil.sh -getInfo has the following format:

dbutil.sh -getInfo [ -args ]

The optional -args simply override what is defined in User.cfg file. If you want to get information about a different Rational Asset Analyzer database without having to edit User.cfg, use the -args parameters to specify the database connection information.

The values for the -args

-dbUrl ?

? = database URL

-user ?

? = user id

-pw ?

? = password

-tschema ? table

? = schema name

-dbDriver ? database

? = driver class name

Below is sample output obtained from issuing the dbutil.sh command with no -args

JVM version detected: [#.#.#]
      Url: jdbc:db2os390:your_DB2_location
      Version: ###
      OS: DB2_ZOS

-runMigratePlan

Use the dbutil.sh -runMigratePlan command to perform the database migration based on a migration plan. The dbutil.sh -runMigratePlan has the following format:

dbutil.sh -runMigratePlan [ task_steps ]

task_steps

This is an optional parameter that specifies the task steps to be executed. One or a range of task steps can be specified. If no Task steps are specified on the command, the entire migration plan is executed. That is, all XML files in the /migrate_plan/Migrate_v*.xml are executed. Do not run Task steps out of sequence. For example, do not run Task step v552.3 until Task steps v552.1 and v552.2 are complete.

At the completion of a -runMigratePlan run (in this case, migrating to v510), you should see the following output immediately after the last echoed SQL statement.

----------------------------------------------------------------------
       'v510.##' - timestamp
       null
      ----------------------------------------------------------------------

      ----------------------------------------------------------------------
       Verifying current schema of database as 'v510'
      ----------------------------------------------------------------------
      Current database version verified.

      ----------------------------------------------------------------------

      **********************************************************************
      NOTICE:  POST-MIGRATION ACTION REQUIRED
      To complete migration for the v5.1.0 level, you should perform the
      following:
      1) Review the list of files identified in the generated file
      Obsolete_Messages.txt and outline a plan to reanalyze these assets.
      Please refer to the releasenotes.txt for further information.
      **********************************************************************

      Finished without errors.

The following examples, show how various specifications of the command can be used to perform step-by-step migration.

• To run Task steps v311.1 through v311.100, specify dbutil.sh -runMigratePlan v311.1-v311.100
• To run all Task steps up to and including 311.100, specify dbutil.sh -runMigratePlan -311.100
• To run all Task steps including and after v311.100, specify dbutil.sh -runMigratePlan v311.100-
• To run only Task step v311.100, specify dbutil.sh -runMigratePlan v311.100
• To run Task steps from v311.1 to v411.100, specify dbutil.sh -runMigratePlan v311.100-v411.100

-verifySchema

Use the dbutil.sh -verifySchema to verify that a particular Rational Asset Analyzer database contains the expected tables, columns, indexes, and triggers for a given schema level. A list of differences, if any, is displayed and identifies the missing or extra database objects (tables, columns, indexes, and triggers). It is recommended that you investigate and resolve the highlighted differences.

The dbutil.sh -verifySchema has the following format:

dbutil.sh -verifySchema version

where version is any of the following values: v310, v311, v312, v410, v420, v421, v510, v511, v550, v552, v554, v600.

The following example shows the output for verifying schema for the v310 level. Issuing the dbutil.sh -verifySchema v310 command produced the following output:

 JVM version detected: [#.#.#]
Verifying database with the following properties:
    targetVersion = 'v310'
    dbUrl = 'jdbc:db2os390:your_DB2_location'
    user = 'userid'
    pw = 'password'
    os = 'zOS'
    tSchema = 'your_schema_name'
    dbDriver = 'your_DB2_driver'
    isDistEnabled = 'true_or_false'
current_date current_time com.ibm.dmh.db.DbInfo
INFO: Database os detected as vendor=IBM, os=DB2_ZOS, version=your_db2_version
Baseline comparison showed 0 differences.
Finished without errors.

The following example shows the output issuing the same command for a database where there are two differences between the current database and the migration level. A message is displayed for each difference. All differences should be resolved before continuing with the migration process.

JVM version detected: [#.#.#]
Verifying database with the following properties:
    targetVersion = 'v312'
    dbUrl = 'jdbc:db2os390:your_DB2_location'
    user = 'userid'
    pw = 'password'
    os = 'zOS'
    tSchema = 'your_schema_name'
    dbDriver = 'your_DB2_driver'
    isDistEnabled = 'true_or_false'
current_date current_time com.ibm.dmh.db.DbInfo
INFO: Database os detected as vendor=IBM, os=DB2_ZOS, version=your_db2_version
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'

Appendix B. Warnings from implementing a migration plan

The following examples, show the DELETE of row that does not exist followed by an example of an INSERT from or to an empty table.

DELETE FROM DMH.DMH_SYSTEM_ATTR   WHERE SYS_ATTR_ID = 500 STATUS: WARNING  (SQL-100)

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 

Appendix C. Handling migration plan failures

About this task

If a Task step fails, do the following steps before continuing with the migration.

Procedure

1. Stop the migration utility.
2. Open the dbutil.log file.
3. In the log file, find the Task step number for the step that failed, for example, v312.10. A COMMIT would have been performed at the completion of the last successful Task step.
4. Identify the failing SQL statement and resolve the condition causing the failure.
5. Pay close attention to which SQL statements, if any, in the current Task step succeeded. Any SQL statements that are executed after the last successful COMMIT call will have been automatically rolled-back.
6. Identify any remaining SQL statements that should have been run in the failed Task step by locating the Task step in the appropriate /migrate_plan/Migrate_version.xml file. Continue the migration by doing one of the following:
   • Manually execute any remaining SQL statements from the Task step that failed, excluding any that succeeded and were successfully committed prior to the failure.

     Then, restart the migration utility at the next migration step. For example, if the failure occurred in Task v312.10, start the migration at Task V312.11 by issuing the following command: dbutil.sh -runMigratePlan v312.11-.

   • Update the /migrate_plan/Migrate_version.xml file to remove the SQL statements that were successfully executed for the Task step that eventually failed.

     Save the file and start the migration utility at the Task step that failed. For example, if the failure occurred in Task v312.10, start the migration at Task V312.10 by issuing the following command: dbutil.sh -runMigratePlan v312.10-.

Example

The following is an example of a Task step failure and resolution. The dbutil.log below contained the following error

--------------------------------------------------------------------
[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:

• Note the Task step number in which the failure occurred is v410.31. This is Task step #31 in Migrate_v410.xml migration plan file.
• Note the first SQL statement was run successfully and the change was committed.
• Make a determination as to why the SQL failed (such as: table has already been removed, table name is incorrect), and resolve the condition.
• Locate Task step v410.31 in /migrate_plan/Migrate_v410.xml.
• Manually run the corrected SQL statement and the remaining SQL statements, if any, that comprise Task step v410.31.
• Restart the Utility to begin running at the Task step immediately following the (previous) failed Task step, for example:

  dbutil.sh -runMigratePlan v410.32-

Notices

This information was developed for products and services offered in the U.S.A. IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service.