Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    3

    Unanswered: ERwin & DB2/Linux: SQL0750N (Table renaming)

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    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
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •