Topic
  • 7 replies
  • Latest Post - ‏2005-09-14T09:48:14Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic How to force a database DROP using DB2 Command from CLP?

‏2005-09-13T13:57:26Z |
How can I force a database DROP using a DB2 command from CLP? I'm trying to DROP and then CREATE a database within an ANT <exec> task, but having trouble doing this because of active connections. Or, if possible, how can I kill these active connections before DROP/CREATE?
Updated on 2005-09-14T09:48:14Z at 2005-09-14T09:48:14Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T14:17:45Z  
    You can kill active connections using the following statement:
    db2 connect to database
    db2 quiesce database immediate force connections

    This will force all connection immediately off the database.

    Next, unquiesce the db with:
    db2 unquiesce database
    Then:
    db2 drop db dbname
    Note that technically in the split second that it takes for you to unquiesce the db and issue the drop command, users can potentially connect. I am assuming that there is no heavy contention on the db and this will not occur.

    I have no direct experience with ant, but I am presuming you can issue the above as system commands?

    Mansoor
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T15:02:29Z  
    You can kill active connections using the following statement:
    db2 connect to database
    db2 quiesce database immediate force connections

    This will force all connection immediately off the database.

    Next, unquiesce the db with:
    db2 unquiesce database
    Then:
    db2 drop db dbname
    Note that technically in the split second that it takes for you to unquiesce the db and issue the drop command, users can potentially connect. I am assuming that there is no heavy contention on the db and this will not occur.

    I have no direct experience with ant, but I am presuming you can issue the above as system commands?

    Mansoor
    Thanks, but I have tried this, and I still get a error message saying: "The database is currently in use."

    Here's what I tried (from the CLP command window actually):

    db2 connect to dbname
    db2 quiesce database immediate force connections
    db2 unquiesce database
    db2 drop db dbname

    Any other suggestions?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T15:14:35Z  
    Thanks, but I have tried this, and I still get a error message saying: "The database is currently in use."

    Here's what I tried (from the CLP command window actually):

    db2 connect to dbname
    db2 quiesce database immediate force connections
    db2 unquiesce database
    db2 drop db dbname

    Any other suggestions?
    then do a DB2 LIST APPlICATIONS to see who's still using it. maybe you
    need to do a db2 terminate before you drop it.

    rikard@wollongong.no wrote:
    > Thanks, but I have tried this, and I still get a error message saying: "The database is currently in use."
    >
    > Here's what I tried (from the CLP command window actually):
    >
    > db2 connect to dbname
    > db2 quiesce database immediate force connections
    > db2 unquiesce database
    > db2 drop db dbname
    >
    > Any other suggestions?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T15:34:11Z  
    Thanks, but I have tried this, and I still get a error message saying: "The database is currently in use."

    Here's what I tried (from the CLP command window actually):

    db2 connect to dbname
    db2 quiesce database immediate force connections
    db2 unquiesce database
    db2 drop db dbname

    Any other suggestions?
    Is this the only database in the instance?
    If it is, then you can do a 'db2stop force' & db2start.

    Another thing I would suggest is to do a:
    db2 force application all. This command to is async and only forces of transactions that are idle or with auto commit on, so it may not be altogether too helpful for you.

    Also, try 'db2 list active databases' to see if you do see any active connections to the database.

    Also, try the following:
    ps -ef | grep instanceName | grep db2agent

    You might see quite a few of 'db2agent (idle)' enteries. Ignore these. The ones you are interested in are 'db2agent (Your dbname)'.
    If you see any such agents, then you do have active connections. Do NOT issue a kill -9 against these agents since that will bring your instance down.

    Write back with the ps output if the above both the above ideas fail.

    Hope this helps.

    Mansoor
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T17:47:34Z  
    then do a DB2 LIST APPlICATIONS to see who's still using it. maybe you
    need to do a db2 terminate before you drop it.

    rikard@wollongong.no wrote:
    > Thanks, but I have tried this, and I still get a error message saying: "The database is currently in use."
    >
    > Here's what I tried (from the CLP command window actually):
    >
    > db2 connect to dbname
    > db2 quiesce database immediate force connections
    > db2 unquiesce database
    > db2 drop db dbname
    >
    > Any other suggestions?
    This I have tried:

    db2 connect to dbname
    db2stop force
    db2start
    db2 drop db dbname
    db2 list applications
    db2 create db dbname

    The result from the "list applications" command:
    SQL1611W No data was returned by Database System Monitor

    The "drop db" statement completes successfully everytime, but the "create db" statement gives me the error message "SQL1047N The application is already connected to another database". This, however, only happens every second time I run the statements (?). I have absolutely no idea why...
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-13T18:31:07Z  
    This I have tried:

    db2 connect to dbname
    db2stop force
    db2start
    db2 drop db dbname
    db2 list applications
    db2 create db dbname

    The result from the "list applications" command:
    SQL1611W No data was returned by Database System Monitor

    The "drop db" statement completes successfully everytime, but the "create db" statement gives me the error message "SQL1047N The application is already connected to another database". This, however, only happens every second time I run the statements (?). I have absolutely no idea why...
    Stale connections.

    Issue a 'db2 disconnect all' between the connect and the stop force statements. That should fix it.

    Mansoor
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to force a database DROP using DB2 Command from CLP?

    ‏2005-09-14T09:48:14Z  
    Is this the only database in the instance?
    If it is, then you can do a 'db2stop force' & db2start.

    Another thing I would suggest is to do a:
    db2 force application all. This command to is async and only forces of transactions that are idle or with auto commit on, so it may not be altogether too helpful for you.

    Also, try 'db2 list active databases' to see if you do see any active connections to the database.

    Also, try the following:
    ps -ef | grep instanceName | grep db2agent

    You might see quite a few of 'db2agent (idle)' enteries. Ignore these. The ones you are interested in are 'db2agent (Your dbname)'.
    If you see any such agents, then you do have active connections. Do NOT issue a kill -9 against these agents since that will bring your instance down.

    Write back with the ps output if the above both the above ideas fail.

    Hope this helps.

    Mansoor
    u can not force stop a db...the moment all connections are disconnected the DB comes to same state as stopped.so

    stop the instance
    start the instance
    and then do what you want to..immediately before someone else connects to ur database