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
- Symptoms
- 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 specify schema names, the current login
is assumed. The CREATE statement might fail if the current login is
different during the forward engineering process.
- Resolving the problem
- 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
IBM® Data
Studio 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
IBM Data
Studio 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.