how to remove everything related to a user(schema)

[%NAME%]

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

[Michael Rudolph]

Hi,

you didn't mention your DB2 version.

for V8.2.2 (FP9) see:
http://www-128.ibm.com/developerwork...dm-0602rielau/

for V9 see:
http://publib.boulder.ibm.com/infoce...c/r0022036.htm
Regards
Michael

[Ian]

%NAME% wrote:

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/developerwork...lau/index.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.

[bwmiller16]

On Mar 28, 10:03 am, Ian <[e-mail]:

%NAME% wrote:

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/developerwork...rticle/dm-0602...

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.

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
;