By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

how to remove everything related to a user(schema)

P: n/a
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

Mar 28 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Mar 28 '07 #2

P: n/a
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.
Mar 28 '07 #3

P: n/a
On Mar 28, 10:03 am, Ian <ianb...@mobileaudio.comwrote:
%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
;
Mar 28 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.