on 10-25-2017 9:45 AM
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
"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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hello Robert
There appear to be two parts to your question -
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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.