how to remove everything related to a user(schema)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

%NAME% - 28 Mar 2007 16:14 GMT

is there a way to write a script (not stored procedure) that looks
for all the
objects (triggers, events, materialized views, indexes, stats, tables)
created
by one user and remove them all?

thanks a lot

Reply to this Message

Michael Rudolph - 28 Mar 2007 16:37 GMT

Hi,

you didn't mention your DB2 version.

for V8.2.2 (FP9) see:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602rielau/

for V9 see:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.d
oc/doc/r0022036.htm

Regards
Michael

Reply to this Message

Ian - 28 Mar 2007 17:03 GMT

> is there a way to write a script (not stored procedure) that looks
> for all the
> objects (triggers, events, materialized views, indexes, stats, tables)
> created
> by one user and remove them all?

Of course. Serge Rielau wrote a nice set of stored procedures to
do all of this.

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602rielau/inde
x.html

If you really don't want a stored proc, you could take the logic out
of the 'dropschema' procedure. You'd want to do something along the
lines of:

-- Drop Views, MQTs, Tables (in that order)
-- Generates 'drop table' statements
select
'drop table ' || rtrim(tabschema) || '.' || tabname
from
syscat.tables
where
tabschema = 'XXX"
order by
type desc;

You'd write similar statements for functions, procedures, sequences,
triggers, datatypes.

Reply to this Message

bwmiller16@gmail.com - 28 Mar 2007 17:18 GMT

> > is there a way to write a script (not stored procedure) that looks
> > for all the
[quoted text clipped - 24 lines]
> You'd write similar statements for functions, procedures, sequences,
> triggers, datatypes.

This is what I have used in the past...(its not exactly what you want
but modifiable)...

select
'DROP '||
CASE
WHEN TYPE IN ('H','S','T','U') THEN 'TABLE '
WHEN TYPE IN ('V','W') THEN 'VIEW '
WHEN TYPE IN ('A') THEN 'ALIAS ' END

||RTRIM(TABSCHEMA) || '.' || RTRIM(tabname) ||';'

from
syscat.tables where tabschema = '$SCHEMA' and
tabname not like 'SYS%' order by tabschema, tabname
;
select
'DROP TRIGGER '||
RTRIM(TRIGSCHEMA) || '.' || RTRIM(trigname)||';'
from
syscat.triggers
where tabschema = '$SCHEMA' order by tabschema, tabname
;

Reply to this Message