Menu
I have a DB2 database (let's call it mydb) that I would like to delete. However, when I do db2 drop db mydb I get back
SQL1035N The operation failed because the specified database cannot be connected to in the mode requested. SQLSTATE=57019
What am I doing wrong?
724
The reason SQL won't let you drop a table in this situation is because the allocation pages/extent chain appears to be damaged or cross-linked in some way. So SQL Server thinks that there is actually data from other tables in pages/extents belonging to the problem object.
To remove a database from the current server without deleting the files from the file system, use sp_detach_db. USE master; ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [databasename] ; Note, database backups will not be deleted as part of the process documented above.
Find out the database ID from sysdatabases. Then execute - sp_lock that will show all the locks on the instance along with spid and dbid. Kill the spids with the dbid that you are trying to offline or drop. Save this answer.
System databases cannot be dropped.
You should try the following:
db2 quiesce db immediate
db2 force application all
db2 drop database mydb
'Quiesce' forces all users off the specified instance and database and puts it into a quiesced mode. (https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008635.html)
'Force application' forces local or remote users or applications off the system to allow for maintenance on a server. (https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001951.html)
If that doesn't do the trick, do 'db2stop' and 'db2start' after the 'force application' and then drop the database
71
You can try:
db2 terminate
then run the command:
db2sampl
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
