Hi
Try using these queries
The first one will help you in building constraints back but use proper filter critrias
select rtrim('alter table ' || lower(rtrim(da.grantee)) ||
'.' || lower(ltrim(rl.tbname)) ||
' add constraint ' || lower(rl.relname) ||
' foreign key (' ||
CASE WHEN lower(ltrim(rtrim(rl.fkcolnames))) LIKE '% %'
THEN substr(lower(ltrim(rtrim(rl.fkcolnames))),1,locate(' ',ltrim(rl.fkcolnames))) ||
',' ||
substr(lower(ltrim(rtrim(rl.fkcolnames))),locate(' ',ltrim(rl.fkcolnames)))
ELSE lower(ltrim(rtrim(rl.fkcolnames)))
END ||
') references ' || lower(rtrim(da.grantee)) ||
'.' || lower(ltrim(reftbname)) ||
' (' ||
CASE WHEN lower(ltrim(rtrim(rl.pkcolnames))) LIKE '% %'
THEN substr(lower(ltrim(rtrim(rl.pkcolnames))),1,locate(' ',ltrim(rl.pkcolnames))) ||
',' ||
substr(lower(ltrim(rtrim(rl.pkcolnames))),locate(' ',ltrim(rl.pkcolnames)))
ELSE lower(ltrim(rtrim(rl.pkcolnames)))
END ||
') on delete ' ||
CASE rl.deleterule WHEN 'A' THEN 'no action'
WHEN 'C' THEN 'cascade'
WHEN 'R' THEN 'restrict'
WHEN 'N' THEN 'set null'
ELSE 'booboo bubba '
END ||
' on update ' ||
CASE rl.updaterule WHEN 'A' THEN 'no action'
WHEN 'R' THEN 'restrict'
ELSE 'booboo bubba '
END)
from syscat.dbauth da,
sysibm.sysrels rl
where lower(da.grantee) = 'nbercea' and
lower(da.createtabauth) = 'y' and
lower(rl.creator) = 'cse'
order by da.grantee, rl.tbname, rl.relname with ur;
The second will help you in dropping constraints
select rtrim('alter table ' || lower(ltrim(rtrim(creator))) || '.' || lower(ltrim(rtrim(tbname))) || ' drop constraint ' || ltrim(relname))
from sysibm.sysrels
where lower(creator) = 'desired_schema_name'
order by tbname, relname with ur;
beware first run first script and then make a .sql out of it
then run the second and then make a .sql out of it
hope this will help you out
-----Original Message-----
From: sandeepjayawant [mailto:db2udbdba-ezmlmshield-x20217197.[Email address protected]
Sent: Thursday, April 26, 2007 10:24 AM
To: LazyDBA Discussion
Subject: Dropping columns and Foreign keys
Hi Guys,
Supposing ¹I have a situation ¹where I have to drop a column from the table
and many tables refer to this table (foreign key constraints).
Now when i drop this table and recreate the table with the new table
definition. All the foreign key constraints which were refering to this
table are automatically dropped,even if i set the rule "on delete
restrict".
What should be the method to achieve this objective? As if the table is
refered by many tables it would be hard to keep a track of all the foreign
keys to be created.
Following example wil make the situation more clear:
DB2 CREATE TABLE TABA (COL1 INTEGER NOT NULL , COL2 INTEGER)
DB2 ALTER TABLE TABA ADD CONSTRAINT TABAPK PRIMARY KEY ¹(COL1)
DB2 CREATE TABLE ¹TABB (COL1 INTEGER NOT NULL, COL2 INTEGER)
DB2 ALTER TABLE TABB ADD CONSTRAINT TABB_FK FOREIGN KEY (COL1) REFERENCES
TABA (COL1) on delete restrict.
Now when I drop the table TABAA ¹the constraint TABB_FK ¹is dropped. Even
if i recreate the table TABA ¹still the constraint is missing.
************************************************************
HSBC Software Development (India) Pvt Ltd
HSBC Center Riverside,West Avenue ,
25 B Kalyani Nagar Pune ¹411 006 INDIA
Telephone: +91 20 26683000
Fax: +91 20 26681030
************************************************************
-----------------------------------------
*******************************************************************
This e-mail is confidential. It may also be legally privileged.
If you are not the addressee you may not copy, forward, disclose
or use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return e-mail.
Internet communications cannot be guaranteed to be timely,
secure, error or virus-free. The sender does not accept liability
for any errors or omissions.
*******************************************************************
"SAVE PAPER - THINK BEFORE YOU PRINT!"
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
DB2 & UDB email list listserv db2-l LazyDBA home page