I'm using ERwin Data Modeler 4.1.2522 with DB2 UDB (Linux) 7.2.8. When I try to forward a model change to the database I get an error. For example changing the datatype of a column from DATE to TIMESTAMP results in following script:

RENAME TABLE CUSTOMERS TO CUSTOMERS123456789;

CREATE TABLE CUSTOMERS...

INSERT INTO CUSTOMERS(...) SELECT (...) FROM CUSTOMERS123456789

DROP TABLE CUSTOMERS123456789;

When I run the script I get this error:
[IBM][CLI Driver][DB2/LINUX] SQL0750N The source table cannot be renamed because it is referenced in a view, summary table, trigger, SQL function, SQL method, check constraint, or referential constraint. SQLSTATE=42986

It seems that it is not possible to rename a table in DB2 when there exists a reference to this table. With Oracle the Erwin generated script works perfectly. Does Erwin not support this feature when working with DB2?

Any ideas?

Thanks in advance,
Andreas

I don't know the answer, but maybe Oracle allows one to rename a table even if it is referenced by another object. DB2 does not allow it.

You are correct about the restrictions on the RENAME command.

Two questions/suggestions:

1. Does ErWin know about the other objects that reference this table? The reason I ask is that PowerDesigner (similar to ErWin) won't perform the right steps if referencing objects were create outside of PD.

2. Do you have an option to create temporary tables vs. renames? This is a PD option.

3. As opposed to using the direct ODBC interface into DB2, generate the SQL. We use this option becuase it allows a quick "eyeball" and a test run before we hit production. If never found tools like this to be 100% and you sometimes have to help them or, based upon what's generated, prepare your own fix.

Fred