Common problems

There are some common problems and issues that you should be aware of when using DB2® Change Management Expert.
Finding the DB2 instance name
Change commands that require the DAS will request the DB2 instance name. With DB2 Version 8.2 fix pack 11 and higher, DB2 Change Management Expert will detect the instance name from the connection. However, with earlier versions of DB2, you will have to look at the server to determine the instance name. On Windows systems, the instance name is typically DB2. On UNIX systems, the instance name is typically the ID that DB2 was installed under. For example, db2inst82. Installation environments can vary.
Finding the JDBC type 4 driver
In DB2 Version 8, the DB2 type driver is called db2jcc.jar. It can typically be found under the DB2 installation directory in the Java subdirectory. To use the type 4 driver, you will also need a license file in your CLASSPATH. You can enter both of these values in the New Connection wizard by clicking Browse and selecting the files.

An additional problem related to the JDBC driver involvesh migrating workspaces between different versions of DB2 Change Management Expert. The connection information stored in the Database Explorer contains driver information that is not portable. When you migrate a workspace you must delete and recreate all of your custom connections in the Database Explorer to properly update the driver information.

Forward engineering
When you forward engineer changes, be sure to check the DDL for objects that are derived from other objects, such as views, materialized query tables, and aliases, to ensure that the correct dependent objects will be resolved. DB2 stores the CREATE statements for some derived objects in the catalog exactly as they were specified. If the creator of these statements did not fully specify schema names, the current login is assumed. The CREATE statement might fail if the current login is different during the forward engineering process. To resolve this problem either use the same creator ID when you change objects, fully specify the dependent objects in the DDL, or add the syntax SET CURRENT SCHEMA before the command.

For example, if you generate a CREATE VIEW command like CREATE VIEW MYSCHEMA.VIEW AS SELECT * FROM MYTABLE; and MYTABLE actually belongs to a schema called DB2, then when this command is deployed, DB2 will look for MYTABLE under the current login name. You might get an error saying loginname.MYTABLE is not defined. If you add a SET CURRENT SCHEMA=DB2 statement before the CREATE VIEW MYSCHEMA.VIEW AS SELECT * FROM MYTABLE syntax, then all of the referenced objects will be resolved correctly.

Start of changeWhen DB2 Change Management Expert is calculating the correct order for the CREATE statements of derived objects that contain names of other derived objects without fully qualified schema names, it is possible that DB2 Change Management Expert will put those statements in an incorrect order. To correct this problem you might need to:
  • Ensure that the login that is used to generate the Change Commands is the same as the login that was used to create the database objects.
  • Open the generated Delta DDL in the Change Command Editor and put the statements in the correct order.
End of change
The base model is out of synch with the catalog when deploying the change
The Deploy Change Commands wizard detects whether the base model matches the current database catalog. This is a good safeguard because the database catalog might now be different from the data model that this script was constructed to change. If a difference is detected, complete the following steps:
  1. Click Cancel to close the Deploy Change Commands wizard.
  2. Return to the Overview tab of the Deployment Script Editor.
  3. Click Refresh Base Model to refresh your base model. This will update your base model with any changes that have been made to the database catalog.
  4. Click Compare Base and Target Models to view the differences between the current catalog and the target model. Forward fit the changes as necessary.
  5. If you have made any changes, click Generate Change Commands to regenerate your change commands. At this point, you are ready to deploy your changes to the database catalog.
  6. Click Deploy Changes to the Target Database.
Testing server connectivity
You can test your server connectivity by creating a new connection in the Database Explorer view. You must be in the Data Perspective for that view to be visible.
Model validation
If DB2 Change Management Expert detects an error in the physical data model, it is recorded in the Problems view. If the Problems view is not visible, you can open it from the main menu by selecting Window > Show View > Problems. Or you can open it by typing Alt+shift+Q,X from the keyboard. Double-click the problem to view more details.
Deployment script validation
If DB2 Change Management Expert detects an error in the deployment script, it is recorded in the Problems view. If the Problems view is not visible, you can open it from the main menu by selecting Window > Show View > Problems. Or you can open it by typing Alt+shift+Q,X from the keyboard. Double-click the problem to view more details.
Change command validation
If DB2 Change Management Expert detects an error in a change command file, it is recorded in the Problems view. If the Problems view is not visible, you can open it from the main menu by selecting Window > Show View > Problems. Or you can open it by typing Alt+shift+Q,X from the keyboard. Double-click the problem to view more details.
Problems deleting resources in the Data Project Explorer
At times, you might still see resources even after you have deleted them in the Data Project Explorer. You might have to close and reopen the editor to refresh it. The editor can be opened from the main menu by selecting Window > Show View > Data Project Explorer.
There is not enough screen real estate to use the Comparison Editor
The Comparison Editor can take up quite a bit of space. To maximize the Comparison Editor, double-click the tab of the open editor. On Windows systems, the quick key is Alt+-,X.
Start of changeFinding data objectsEnd of change
Start of changeIf you know the physical data model in which the data object is located, open that model in the Data Model Editor. In this editor, you can type text in the filters field to filter the list and quickly move to an object, use Find (Ctrl+F) to locate an object, or drill down to locate an object. If you do not know what model the object is in, use Search (Ctrl+H). The results will be displayed in the Search View. Double-click the object to open it in an editor.End of change
Start of changeThe Run SQL wizard depends on the extensionEnd of change
Start of changeChange command files in the SQL Scripts folder must have a file extension of chx. In addition to SQL statements, the change command files can contain some DB2 commands and utilities. The different actions for the Run SQL wizard depends on the extension. Trying to run DB2 commands and utilities from a script file with an extension other than chx can cause errors.End of change
Deploying export and import commands
Whenever there is an error while deploying export or import scripts, the SQL code is always -22220, which means that the DAS (DB2 Administration server) has encountered a script error. The exact reason for the error is specified by the error code. The codes will appear in the data output view. The detailed description for a particular error code can be found in the DB2 Message Reference manual.
Import command errors
Error code 3088
The source column that was specified to be loaded into the database column name is not compatible with the database column, but the database column is not nullable.
Source table tblA:  col1 int, col2 int, col3 varchar(5), col4 
num(6, 2) 
Target table (table being loaded) tblB: col2 int, col3 varchar(5),
col4 num(6, 2) not null. 
If you selected all of the rows during the export, then, due to a data type mismatch, the VARCHAR values will fail to be inserted into col4 of the target table during the import.
This can be fixed in the following ways:
  • Edit the data file.
  • Use the customization wizard to select the last three columns from tblA, in the export statement. Use the customization wizard to add column functions as needed.
Error code 3124
The field value in row "2" and column "3" cannot be converted to a PACKED DECIMAL value, but the target column is not nullable. The row was not loaded.
Error code 3196
The input file was not found. If the data file is located on a remote machine where the export command was run, then you must issue the import command again from the Data Project Explorer. Ensure that the data file has been transferred to the machine where the import command is run, then select the import command file, for example xxx_import.chx, from the Data Project Explorer, right-click and select Run SQL to complete the import.
Export command errors
Error code 3025
A parameter that specifies a file name or path is not valid. Because the import and export commands will most likely be run on remote systems, DB2 Change Management Expert does not verify that the specified path exists. To avoid data loss, ensure that the path is valid. You can also run the export commands from the project explorer before you deploy the scripts.
Error code 3304
The table does not exist. It is important to issue the export commands before the tables are dropped. The order of the commands needs to be verified on the Change Commands tab of the Deployment Script Editor before deploying the scripts. The default data file type is DEL (Delimited ASCII, for exchange with many database managers and file managers). You can change the file type to IXF if necessary.

If the error code does not appear in the data output view, it can be found in the Administration notification log file db2dasdiag.log. Details such as the location of this file can be found in the DB2 Information Center .

Editing data preservation entries
The double-click action does not work well on Linux systems. You can use the quick key access to edit your data preservation entries instead. Type the 'e' key or type Shift-F10 to edit the selected entry. This will put the focus on the cell editor of the first column (For example, Export Commands). Pressing the 'Enter" key will move the focus to the cell editor of the second column (Import Command).
Start of changeData migrationEnd of change
Start of changeYou use the Generate Data Migration wizard to either generate change command scripts that are to be run manually or that are embedded into the deployment script.

The deployment script includes both the Generate Change Commands wizard and the Generate Data Migration wizard. When you need both data migration and data preservation and are managing the data migration with the deployment script, you must merge the import and export commands manually by using the file merge wizard page. The order in which you invoke the wizards does not matter, because both wizards have the file merge wizard page.

End of change
Start of changeSyntax errors in the Change Command EditorEnd of change
Start of changeWhen you open change commands in the Change Command Editor, syntax errors might be caused by the level of the DB2 version that DB2 Change Management Expert uses to parse the DDL. By default, DB2 V9.1 is set as the level for parsing DDL. For example, if you change the default to a lower release of DB2 and then open change commands that include syntax that was introduced for DB2 V9.1, you will get errors. You can click Window > Preferences and then expand Data, click Change Management Options, and set the value in the Default DB2 version field back to V9.1.

When you apply change commands to a model, DB2 Change Management Expert always uses the level of the DB2 version for the model for parsing the DDL.

End of change
Deploying change commands fails due to the maximum number of lock requests
If you receive the error code SQL0912N while deploying your change commands, you can solve the problem by:
  • Committing more often
  • Increasing your lock size
Important: Stop and restart DB2 before deploying your change commands. If you do not restart DB2, you might still receive the SQL0912N error.
Deploying change commands fails because of too many active database connections
DB2 limits the number of database connections that can be active at one time. The database manager configuration parameter NUMDB determines the maximum number, which is 8 by default. After you reach the maximum number of connections in the Database Explorer, you will not be able to create a new connection. Therefore, when you attempt to deploy your change commands, they might fail. The error message from DB2 is "error in the script file." This error is caused by having too many connections to DB2. Delete one of your unused or less-used connections from the Database Explorer or consider increasing the size of NUMDB.
Start of changeThe Database Explorer does not automatically reflect database changesEnd of change
Start of changeDatabase changes are not automatically reflected in the Database Explorer. To see the updates, you need to refresh the connection for the database.End of change


Feedback

Copyright IBM Corporation 2006, 2007. All Rights Reserved.