Skip to Content

Rename Table script PowerDesigner

Hi,

using DB2MVS11 (DB2 for z/OS v11) datasource. If a table will be renamed PowerDesigner is generating scripts like

...
drop table "tmp_TABLETT";
rename table TABLETT to "tmp_TABLETT";
...
insert into TABLETTSSS (C1, C2)
select C1, C2
from "tmp_TABLETT";

Searching for the definition of the rename table syntax I found
rename [table ][%OLDQUALIFIER%]%OLDTABL% to %NEWTABL%

So the %NEWTABL% will be taken from the %OLDTABL% by adding a "tmp_" prefix. Do we have the possiblity to change "tmp_" to another value and maybe restrict the length of %NEWTABL% to 15 characters in all Statements (DROP, RENAME, and INSERT)?

If I only modify %NEWTABL% variable with %[[?][-][<x>][.[-]<y>][<options>]:]<variable>% syntax only the rename table statement will be changed. Drop and Iinsert stay at "tmp_TABLETT".

Many thanks

Robert

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Nov 02, 2017 at 06:49 PM

    "tmp_" is hard coded in Powerdesigner coding engine.

    It's not a good practice to replace "tmp_" by customizing DBMS.

    The better way is store sql code in editor such as notepad. Use editor's replace function to modify table name.

    It's not difficult to automate the process.

    Add comment
    10|10000 characters needed characters exceeded

    • I see what you mean, Phillip - the table name would would have to be tested for 'tmp_' in more than just the rename statement. We would also have to make sure that we didn't accidentally Drop the wrong table as well.

  • Oct 26, 2017 at 02:07 PM

    hello Robert

    There appear to be two parts to your question -

    1. How do I limit the table names to 15 characters? The table name in the DDL is taken from the 'Code' of the table in the PDM - in the Database Definition file you can control the maximum length of table names See Maxlen, under Script\Objects\Table. Why do you want to keep your table names so short? 15 characters seems really unmanageable - you'll probably have to manually amend them to avoid duplicates, no matter how many abbreviations you use.
    2. Can I change the name of the temporary table that the script creates? I don't think you can, but why would you want to do that?

    By the way, you probably already know that you can tell PD not to create temporary tables in the DB alter script - I'm not a DBA or DB Developer, so I can't say whether or not that's a good idea :)

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2017 at 08:54 AM

    Hi George,

    the database will use "tmp_" also as prefix for internal tables, so the tables generated by PowerDesigner should not interfer with these tables. The length itself was only an additional question.

    Robert

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2017 at 11:07 AM

    It looks like you need to change the script that PD generates when renaming tables, which is in the database definition file at

    Script\Objects\Table\Rename

    I haven't tried it myself, but I think this will work. If the first 4 characters of the new table name are "tmp_", it produces a different new table name which is the string "drp_" followed by the original table name:

    .if .4:%NEWTABL% == "tmp_"
    alter table [%QUALIFIER%]%OLDTABL%
    rename to drp_%OLDTABL%
    .else
    alter table [%QUALIFIER%]%OLDTABL%
    rename to %NEWTABL%
    .endif

    Please let me know if this works :)

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 02, 2017 at 07:52 AM

    Hi George,

    we had two holidays, so sorry for the delay.

    Where should I enter your if-condition? I tried it in the

    "DB2MVS11::Script\Objects\Table\Rename"-area instead, before, and after the line containing

    rename [table ][%OLDQUALIFIER%]%OLDTABL% to %NEWTABL% ?

    I allways get in the SQL-Script for the rename-modification

    alter table TABLETT drop primary key;
    drop index INDEX_1;
    drop table "tmp_TABLETT";

    1 error(s), 0 warning(s) (1) (Table "TABLETT"):
    [syntax error] condition parsing error

    ...

    Many thanks

    Robert

    Add comment
    10|10000 characters needed characters exceeded

    • The script is meant to completely replace the existing script. Thanls for noticing that I should have put %OLDQUALIFIER% instead of %QUALIFIER% - not sure where that came from.

      If you want to keep the existing line, putting a full stop (.) at the beginning of the line will turn it into a GTL comment.

  • Nov 02, 2017 at 02:00 PM

    Hi George,

    using

    .if %.5:NEWTABL% == "tmp_
    rename [table ][%OLDQUALIFIER%]%OLDTABL% to drp_%OLDTABL%
    .else
    rename [table ][%OLDQUALIFIER%]%OLDTABL% to %NEWTABL%
    .endif

    will lead into the following SQL script to rename the TABLETT Table:

    alter table TABLETT drop primary key;
    drop index INDEX_1;
    drop table "tmp_TABLETT";
    rename table TABLETT to drp_TABLETT;
    create table ...
    insert into TABLETTSSSS (C1, C2) select C1, C2 from "tmp_TABLETT";


    On one position the drp_TABLETT will be used but on all other positions he'll use tmp_TABLETT instead of dmp_TABLETT. I think the variable %NEWTABL% must be set qlobal?

    Robert

    Add comment
    10|10000 characters needed characters exceeded