Question Re: Rename Table script PowerDesigner in Technology Q&A

Rename Table script PowerDesigner

robert_kratschmann — Wed, 25 Oct 2017 08:45:10 GMT

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

Re: Rename Table script PowerDesigner

GeorgeMcGeachie — Thu, 26 Oct 2017 14:07:37 GMT

hello Robert

There appear to be two parts to your question -

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.
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 🙂

Re: Rename Table script PowerDesigner

robert_kratschmann — Fri, 27 Oct 2017 08:54:11 GMT

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

Re: Rename Table script PowerDesigner

GeorgeMcGeachie — Fri, 27 Oct 2017 11:07:03 GMT

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 🙂

Re: Rename Table script PowerDesigner

robert_kratschmann — Thu, 02 Nov 2017 07:52:23 GMT

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

Re: Rename Table script PowerDesigner

GeorgeMcGeachie — Thu, 02 Nov 2017 11:13:18 GMT

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.

Re: Rename Table script PowerDesigner

robert_kratschmann — Thu, 02 Nov 2017 14:00:30 GMT

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

Re: Rename Table script PowerDesigner

former_member200945 — Thu, 02 Nov 2017 18:49:18 GMT

"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.

Re: Rename Table script PowerDesigner

GeorgeMcGeachie — Mon, 06 Nov 2017 11:59:56 GMT

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.