Common problems

Some common problems and issues can occur when you use Optim™ Database Administrator.
Finding the DB2® instance name
Change commands that require the IBM® Data Server Client will request the DB2 instance name. Optim Database Administrator will detect the instance name from the connection. 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, db2inst95. Installation environments can vary.
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 are 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 looks 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 the referenced objects will be resolved correctly.

When Optim Database Administrator 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 Optim Database Administrator 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 commands in the SQL and XQuery Editor and put the statements in the correct order.
Testing server connectivity
You can test your server connectivity by creating a database connection in the Administration Explorer view.
Model validation
If Optim Database Administrator 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.
Change management script validation
If Optim Database Administrator detects an error in the change management 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 Optim Database Administrator detects an error in the change commands in the Commands section of the Change Management Script Editor, it is recorded in the Error Log view. If the Error Log view is not visible, you can open it from the main menu by selecting Window > Show View > Error Log. Or you can open it by typing Alt+shift+Q,L from the keyboard. Double-click the error to view more details.
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.
Lack of screen space when using the Compare Editor
The Compare 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.
Finding data objects
If 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.
The Run SQL wizard depends on the extension
SQL script files in the SQL Scripts folder must have a file extension of sql or ddl. The script files can also contain some DB2 commands and utilities. The different actions for the Run SQL wizard depend on the extension. Trying to run DB2 commands and utilities from a script file with an extension other than sql or ddlcan cause errors.
Deploying unload and reload commands
If an error occurs when unload or reload commands are deployed, the SQL code is always -22220. This code means that the IBM Data Server Client has encountered an error in the script. The exact reason for the error is specified by the error code. The error 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.
Reload 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 the rows during the unload, then, due to a data type mismatch, the VARCHAR values will fail to be inserted into col4 of the target table during the reload.
Fix this problem in one following ways:
  • Edit the data file.
  • Use the Customize Data Preservation wizard to select the last three columns from tblA, in the unload statement. Use the 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 unload command was run, then you must issue the reload command again from the Data Project Explorer. Ensure that the data file has been transferred to the machine where the reload command is run, then select the reload command file, for example xxx_import.chx, from the Data Project Explorer, right-click and select Run SQL to complete the reload.
unload command errors
Error code 3025
A parameter that specifies a file name or path is not valid. Because the reload and unload commands are most likely run on remote systems, Optim Database Administrator does not verify that the specified path exists. To avoid data loss, ensure that the path is valid. You can also run the unload commands from the project explorer before you deploy the scripts.
Error code 3304
The table does not exist. The unload commands must be issued before the tables are dropped. The order of the commands needs to be verified in the Commands section the Change Management 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 is not in the data output view, you can find the code in the Administration notification log file (db2dasdiag.log). For more information, such as the location of this file, see DB2 Information Center.

Mapping unload and reload tables during data migration
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 edit action puts the focus on the cell editor of the first column (For example, Export Commands). Pressing the 'Enter" key moves the focus to the cell editor of the second column (reload Command).
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 Data Source 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 Data Source Explorer or consider increasing the size of NUMDB.
The Administration Explorer and Object List do not automatically reflect database changes
Database changes that are not made by using the Data Object Editor or the Change Management Script Editor (made outside of the product) are not automatically reflected in the Administration Explorer and Object List. To see the updates, you need to refresh the connection for the database, or refresh any one of the object type folders under the database in the Administration Explorer.
Turn off automatic key migration
Key migration is on by default. If columns are being added to parent tables when you add a new foreign key, turn automatic key migration off to eliminate this behavior. To turn off automatic key migration, complete the following steps:
  1. From the main menu, click Windows > Preferences.
  2. Drill down through Data Management and expand Key Migration.
  3. Double-click On Add.
  4. Clear the Migrate key automatically check box, click Apply, and then click OK.
Lack of backward compatibility for change management scripts
Change command scripts that are created or edited in the Change Management Script Editor in Optim Database Administrator V2.2.3 are not compatible with earlier versions of Optim Database Administrator.
Support of projects and change management scripts created in earlier releases of Optim Database Administrator
Only data design projects and change commands scripts that were created in Optim Database Administrator V2.2.1 or later releases are compatible with Optim Database Administrator V2.2.3. Optim Database Administrator V2.2.3 converts the files in the project to the new format that is used. Any errors that occur during conversion are displayed. You are prompted to continue with or cancel the conversion. If you choose to continue the conversion, the objects that are causing the problems are ignored and only the valid objects are retained.
Administration Explorer does not open when using a workspace created in earlier releases of Optim Database Administrator
Optim Database Administrator V2.2.3 supports workspaces that were created in earlier releases of Optim Database Administrator. However, the first time that you use a workspace that was created in an earlier release, the Administration Explorer does not open. To open the Administration Explorer, on the main menu, click Window > Show View > Administration Explorer. If you do not see Administration Explorer in the list, click Other, expand Data, select Administration Explorer, and click OK.

The Administration Explorer opens. When you use this workspace again, the Administration Explorer will be displayed.

Commands are not running when Kerberos authentication is used
When Secure Shell (SSH) protocol is used to access remote database servers, the protocol relies on underlying RXA and RSE libraries. Neither of these libraries support Kerberos authentication.
The following operations are not supported in Optim Database Administrator if you are using Kerberos authentication:
  • Activate
  • Add database partition
  • Backup database
  • Catalog
  • Create database
  • db2set
  • Drop database
  • Drop database partition number
  • Export
  • Force
  • Get
  • Get instance
  • HADR setup
  • Import
  • Load
  • Queisce
  • Rebind
  • Recover
  • Reorg
  • Restart
  • Restore
  • Rollforward
  • Runstats
  • Set client
  • Start instance
  • Stop instance
  • Stop rollforward
  • Unquiesce
  • Update
  • Update options

Feedback