How to NOT generate the first "drop temporary table" statement? (modify script)

[m***@public-files.de]

Hi all,

When generating a modify script (for DB2)... the first drop-statement for temporary tables should not be generated.

for example:
1) drop table "tmp_TBL_PERSON"; <== NOT NEEDED!
2) create table "tmp_TBL_PERSON"...
3) insert into "tmp_TBL_PERSON"...select...from TBL_PERSON
4) drop table TBL_PERSON;
5) create table TBL_PERSON
6) insert into TBL_PERSON...select...from "tmp_TBL_PERSON"
7) drop table "tmp_TBL_PERSON";

The drop-statement at the end (see 7) can be controlled via configuration. (Dialog "Apply Model Changes to Database"; "Modify objects in the database" -> "Backup tables"->"Drop temporary tables")

BUT it seems, that you can not disable the generation of the first drop-statement (see 1).

Any help would be appreciated,

Michael

PS: I use PowerDesigner v16.1

[Sean S]

Hi, Michael. I'm afraid I don't know the answer to your question, but I can tell you how I got around the same issue with Oracle.

I wasn't able to determine how to stop PowerDesigner from attempting to drop the non-existent temp table, so I instead changed the Delete Table script PowerDesigner uses for Oracle to an anonymous block that tests for existence of a table before attempting to delete it. This stopped errors from firing when my script would try to drop a non-existent table.

I'm not familiar with DB2, so I don't know know how to translate the concept, but if you don't hear of a better solution, you might give it a try.

For Oracle, I find the Drop Table Script by going to Database -> Edit Current DBMS, and then drilling down to Script -> Objects -> Table -> Drop. Make sure you make a backup copy of your DBMS configuration file before editing it.

Cheers,
Sean

Post by m***@public-files.de
Hi all,
When generating a modify script (for DB2)... the first drop-statement for temporary tables should not be generated.
1) drop table "tmp_TBL_PERSON"; <== NOT NEEDED!
2) create table "tmp_TBL_PERSON"...
3) insert into "tmp_TBL_PERSON"...select...from TBL_PERSON
4) drop table TBL_PERSON;
5) create table TBL_PERSON
6) insert into TBL_PERSON...select...from "tmp_TBL_PERSON"
7) drop table "tmp_TBL_PERSON";
The drop-statement at the end (see 7) can be controlled via configuration. (Dialog "Apply Model Changes to Database"; "Modify objects in the database" -> "Backup tables"->"Drop temporary tables")
BUT it seems, that you can not disable the generation of the first drop-statement (see 1).
Any help would be appreciated,
Michael
PS: I use PowerDesigner v16.1