RE: How waiting that noboby is using a table in order to drop it .

fmathews@systems.DHL.COM wrote: > > Richard Thomas wrote: > > > Sebastien Cottin wrote: > > > > Hello everybody, > > > > > > I have a small question in SQL (transactional Database) : > > > > > > I need to wait that noboby is using a table in order to drop it . > > > I don't know how to do it . > > <snip> > > > You've obviously discovered that setting LOCK MODE doesn't help. > > > > <snip> > > Hi-Richard, > > Why LOCK MODE won't wrok ?, if not worried about long transaction, I = will suggest : > > whenever error continue > > begin work; > > while (true) > > lock table b_sys in exclusive mode > if ( sqlca.sqlcode !=3D 0 ) then > -- Yes an automatic wait until it gets locked / you do Ctrl-C -- > display "Lock Error ",sqlca.sqlcode sleep 2 -- Just to let you = know -- > else > display "Locked Table ",sqlca.sqlcode > -- Do Whatever you want to do HERE -- > exit while > end if; > end while > > commit work; > > For situations involving there are other ways. > > -- > Have a nice day > > Felix K. Mathews > mailto:fmathews@systems.dhl.com > > Felix: You would think that it should work, but it doesn't. You can successfully = LOCK TABLE .. IN EXCLUSIVE MODE, but if the TblSpace was already open, = your ALTER TABLE will still fail. You have to wait until there are no = users querying from the table. Believe me, I have tried this a number of = ways, and have found this is the only way to alter tables on our = production OLTP environment. cheers RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+