Topic
8 replies Latest Post - ‏2013-01-24T03:26:35Z by bluey01
starndawn
starndawn
37 Posts
ACCEPTED ANSWER

Pinned topic deactivate database, there is still a connection to the database

‏2013-01-19T10:34:16Z |
deactivate database, there is still a connection to the database, then it's failed to make offline backup.

Here are the commands:
db2 connect to test
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 UNQUIESCE DATABASE
db2 TERMINATE
db2 DEACTIVATE DB test
db2 BACKUP DATABASE test TO $BACKUPHOME/offline/ WITH 4 BUFFERS BUFFER 1024 COMPRESS WITHOUT PROMPTING

Here is the log:
DB20000I The QUIESCE DATABASE command completed successfully.
DB20000I The UNQUIESCE DATABASE command completed successfully.
DB20000I The TERMINATE command completed successfully.
SQL1495W Deactivate database is successful, however, there is still a connection to the database.
SQL1035N The database is currently in use. SQLSTATE=57019
Updated on 2013-01-24T03:26:35Z at 2013-01-24T03:26:35Z by bluey01
  • mwandishi
    mwandishi
    47 Posts
    ACCEPTED ANSWER

    Re: deactivate database, there is still a connection to the database

    ‏2013-01-21T09:51:29Z  in response to starndawn
    Hi Stardawn,

    It might help if you added a: db2 list applications show detail before & after the deactivate. A force ( stop force, force all or quiesce .. force ) makes no guarantees. It could be that the application is in a state whereby it can't respond to the force, or it could be hung?

    You may need a PMR for this but I would add an additional list applications show detail to see what the app is. Once you have that you can grab some snapshots to see what it is doing. Support will likely ask for some stack traces of the offending application(s).

    Is this a partitioned database ?

    Best regards,
    Stephen Levett.
    • starndawn
      starndawn
      37 Posts
      ACCEPTED ANSWER

      Re: deactivate database, there is still a connection to the database

      ‏2013-01-23T14:05:33Z  in response to mwandishi
      Hi Stephen,

      Yes, I have considered that, here is the info from list applications command. We have websphere connected to database. It's not a partitioned database. Can I deactivate the database twice in the script? Sometimes I rerun the script successfully.

      DB2ADMIN java 8 GA06EA0B.P2B7.14A683084502 9075 1 0
      7150 UOW Waiting Not Collected test /db2home/db2rtest/db2rtest/NODE0000/SQL00003/
      DB2ADMIN java 205 GA06EA0B.P1B7.19A683084500 0394 1 0
      25423 UOW Waiting Not Collected test /db2home/db2rtest/db2rtest/NODE0000/SQL00003/
      DB2ADMIN java 469 GA06EA0B.CEAF.12A682174013 7469 1 0
      8551 UOW Waiting Not Collected test /db2home/db2rtest/db2rtest/NODE0000/SQL00003/
      DB2ADMIN java 1193 GA06EA0B.CBAF.13A682174006 0884 1 0
      5371 UOW Waiting Not Collected test /db2home/db2rtest/db2rtest/NODE0000/SQL00003/
      DB2ADMIN java 794 GA06EA0B.C8AF.12A682174000 5331 1 0
      25422 UOW Waiting Not Collected test /db2home/db2rtest/db2rtest/NODE0000/SQL00003/
      • mwandishi
        mwandishi
        47 Posts
        ACCEPTED ANSWER

        Re: deactivate database, there is still a connection to the database

        ‏2013-01-23T15:01:13Z  in response to starndawn
        Hi Stardawn,

        As Andy & I suggested the force isn't immediately closing all the connections. It is likely they are in a critical path which cannot be interrupted. This explains why sometimes the force works; clearly their interrupt priority is lower than that of the force when it is successful.

        I would suggest coding a sleep after the force / deactivate followed by a check to see if anything is connected. If the check returns 0 apps then you know is should be safe to continue.

        Looking at the list applications output they are all in UOW Waiting, I'd expect apps in this state to respond to a force.

        Best regards,Stephen Levett
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: deactivate database, there is still a connection to the database

    ‏2013-01-21T18:00:52Z  in response to starndawn
    You also need to understand that FORCE is asynchronous. All connections are not necessarily stopped when the the command finishes. Some maybe put into a rollback and that can take time. You script needs to account for this.

    Andy
    • starndawn
      starndawn
      37 Posts
      ACCEPTED ANSWER

      Re: deactivate database, there is still a connection to the database

      ‏2013-01-23T14:06:39Z  in response to SystemAdmin
      Hi Andy,

      Do you mean sleep for a while after deactivating the database?
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: deactivate database, there is still a connection to the database

    ‏2013-01-22T10:29:14Z  in response to starndawn
    Hi,

    Do you use HADR?
    Try

    db2 stop hadr on database test
    • starndawn
      starndawn
      37 Posts
      ACCEPTED ANSWER

      Re: deactivate database, there is still a connection to the database

      ‏2013-01-23T14:08:10Z  in response to SystemAdmin
      No, it's not HADR. We don't want to use db2stop to stop the instance, because there are some other database running on this server.
      • bluey01
        bluey01
        50 Posts
        ACCEPTED ANSWER

        Re: deactivate database, there is still a connection to the database

        ‏2013-01-24T03:26:35Z  in response to starndawn
        I understand you frusttration with this - espscially in a development environment. Its a pity there is not a good way to force a database into a state that you can either drop or backup a database.

        We also have several databases on an instance, and some we want to drop and replace on a regular basis and we often hit this issue and it causes more work or delays.

        Also check to see what privilege those open connections have - does that userid have DBAdmin access. If you can lower the access you may have more luck with them being forced.