DB2 Quick Tip: Checking Connection State

Posted by

Sometimes the connection state is unclear. The following can all make it fuzzy wether or not you have a valid connection:

  • A db2 error or warning related to your connection
  • A system error related to network connectivity
  • Changing VPNs or adding a VPN connection
  • Leaving a connection up overnight or over longer periods

This tip is strictly related to the DB2 command line. Various database interfaces in scripting languages or tools may have another way of doing this.

To check a connection state at the DB2 command line, you can issue:

 >db2 get connection state

   Database Connection State

 Connection state       = Connectable and Connected
 Connection mode        = SHARE
 Local database alias   = SAMPLE
 Database name          = SAMPLE
 Hostname               =
 Service name           =

There is no specific authorization needed to execute this command, unlike many DB2 commands.

The possible states include:

  • Connectable and connected – meaning you are connected to the database listed
  • Connectable and unconnected – you are not connected, but may connect
  • Unconnectable and connected
  • Implicitly connectable

In addition to the mode of SHARE seen above, the connection mode could also be EXCLUSIVE. The Hostname and the Service Name are only populated if the connection is a remote one over TCP/IP.

The Knowledge Center page on the GET CONNECTION STATE command is: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001953.html?cp=SSEPGG_10.5.0/3-5-2-4-41&lang=en

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

One comment

  1. Excellent! I don’t know how I had never stumbled across that before. I ususally just type db2 connect, but it’s not good at detecting if you’ve been forced, such as if the db has been quiesced.
    Thanks for the tip πŸ™‚

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.