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 . 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 . 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 . 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 .
- 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:
- From the main menu, click .
- Drill down through Data Management and
expand Key Migration.
- Double-click On Add.
- 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 . 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