difficulty dropping user 1

[randyvol]

Hello - Been a while since I had to do this, and I cannot figure out what I am missing. I need to drop a user...

Here's the scenario...
use BTEQ to create user...
create user bozo from DBC as
permanent = 20000
,password = byteme
,spool = 40000
,fallback protection
,dual after journal
,default journal table = tbl_randyjournals
,account = '$bozo';

*** User has been created.
*** Total elapsed time was 2 seconds.

As you can see, the user is created fine. BTW, logged in
as DBC when I did this.

So I created some tables in bozo, finished up and want to drop bozo and return the space...here is what I did.

modify user bozo as no fallback protection
; modify user bozo as no journal
; modify user bozo as no after journal
; delete user bozo all
;
(up to this point all operations succeed...)
drop user bozo
;
(throws result 3552 - cannot drop databases with tables, journal tables, views or macros)
hmmm...
- there are no views associated with user bozo, never created any
- there are no macros associated with user bozo, never created any
- there are no tables associated with user bozo - I dropped them
- the modify user statements appear to have removed the journals
- the delete user statement should have removed it, all that remains is the drop to take bozo out of the dictionary,
what am I missing?

 

[dnoeth]

Hi randyvol,

"the modify user statements appear to have removed the journals"
No, they didn't. There was an error, because MODIFY USER is DDL and you can't submit DDL within a multistatement, just read the error message

And even if it worked, the journal would still be there;
"modify user bozo as no journal" just sets a new default
"delete user bozo all" just drop tables/views/..., but no journal

Try this:

modify user bozo as DROP DEFAULT JOURNAL TABLE;

drop user bozo;

Dieter

 

[randyvol]

Thanks dnoeth -

Actually it was my bad formatting the statements as a
multi-statement - when I went back to the original
work, I had issued them as individual modify statements, so
there was no error message.

However, your tip in the 'try this' part makes sense and
kindled a flame in a long-unused part of my brain.

Geesh - go away for 3 years and work on SQL Server instead
full time - it really messes with the brain cells. Go figure.

Thanks again, I'll give it a try and let you know what happened !!

Randy

 

[randyvol]

Yep. That worked no more bozo !

Thanks again!