(888) 685-3101 ext. 2

Usually only a DBA forces or even views connections to a DB2 database. But in many small companies, people play multiple roles, and I thought I’d share a detailed introduction to how to look at and force off connections to a DB2 database. All details and examples here are provided from the DB2 command line – this can be a bash/ksh command line on Linux or UNIX, or a PowerShell or DB2 command window command line on Windows.

The terms “Connection” and “Application” are synonymous for the purposes of this article, except when part of a command. DB2 usually refers to them as applications.

Authorities Needed

Working with connections is considered a system level task and not a database level task. Because of this, the authority to work with the applications is through membership in system groups and not through privileges that can be granted with the ‘GRANT’ statement.

The minimum level of authority needed to view and understand connections is the SYSMON. The minimum level of authority needed to force connections off is SYSMAINT. Users with SYSCTRL or SYSADM will also be able to force off applications.

To give a user these authorities, you must have values set for the groups in the DBM CFG. To see what values (if any) you have set, use this command:

$ db2 get dbm cfg |grep SYS
 SYSADM group name                        (SYSADM_GROUP) = DB2IADM1
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) = DB2IMNT1
 SYSMON group name                        (SYSMON_GROUP) = DB2IMON1

In this case, the build of the server resulted in the value for SYSADM_GROUP. The values for SYSMAINT_GROUP and SYSMON_GROUP were set manually using these commands:

db2 update dbm cfg using SYSMAINT_GROUP db2imnt1
db2 update dbm cfg using SYSMON_GROUP db2imon1

A recycle (db2stop/db2start) of the DB2 instance is required after changing these before the settings will take effect.

The groups must also be created at the OS or LDAP level, depending on how your server is set up. For a Linux server, you can add the groups using syntax like this, as root:

groupadd db2imnt1
groupadd db2imon1

After that, you can add users to these groups using syntax like this:

usermod -G db2imon1 mon_user
usermod -G db2imnt1 mnt_user

That example is for Linux. Always remember to use the principle of least privilege – to give users only the minimum privileges and authorities they need to do their job.

Viewing Connected Applications

There are a large number of ways to look at connected applications

db2top

db2top is my favorite when I’m looking at locking issues. You enter into db2top using syntax like this:

db2top -d SAMPLE

Where SAMPLE is replaced by the database name. Once in db2top, you can press lower case ‘l’ to get into the sessions screen, which lists all connections:
db2top_sessions_screen

If any connections are in a “lock-wait” status, their status will show in red in the “Application Status” column.

From there, you can press upper case ‘U’ to get to the Locks screen:
db2top_locks_screen

From the locks screen, if you do have applications in a lock-wait status, you can press upper case ‘L’ to see the lock chains.

LIST APPLICATIONS

Perhaps the simplest way to look at connections is to use the ‘LIST APPLICATIONS’ command. This command can be issued at the instance level, without a database connection. It provides information like this:


$ db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
APP_SAM> db2jcc_applica 421        192.0.2.0.35576.150831211927                                   SAMPLE   18
DB2INST  db2bp          388        *LOCAL.db2bcud.150831210133                                    SAMPLE   1
APP_SAM> db2jcc_applica 434        192.0.2.0.35654.150831212125                                   SAMPLE   10
ETL_SAM> db2bp          276        *LOCAL.db2bcud.150831194844                                    SAMPLE   1
APP_SAM> db2jcc_applica 427        192.0.2.0.35630.150831212041                                   SAMPLE   18
JANEDOE> db2jcc_applica 348        192.0.2.0.53172.150831204106                                   SAMPLE   1
JSMITH   db2jcc_applica 328        192.0.2.0.60797.150831203130                                   SAMPLE   1
APP_SAM> db2jcc_applica 420        192.0.2.0.35574.150831211924                                   SAMPLE   18
APP_SAM> db2jcc_applica 58         192.0.2.0.60299.150831175723                                   SAMPLE   1
APP_SAM> db2jcc_applica 117        192.0.2.0.44096.150831183446                                   SAMPLE   1
APP_SAM> db2jcc_applica 426        192.0.2.0.35618.150831212024                                   SAMPLE   18
ETL_SAM> db2jcc_applica 393        192.0.2.0.65273.150831210307                                   SAMPLE   1
SNY      db2jcc_applica 307        192.0.2.0.53187.150831201601                                   SAMPLE   38

The name of the authorization ID used to connect to the database is in the first column. On versions prior to DB2 10.5, it will all fit in there. With DB2 10.5, user names of more than 8 characters are finally allowed, and so they may exceed the display length, like many of the above do. The application name is the second column – db2bp means a command line application, and db2jcc_applica is a jdbc connection. The third column holds the application handle, which is a unique ID that will be critical if you are looking to force a single connection off. The fourth column holds the Application ID, which if a connection comes from a remote server, is likely to start out with the IP address of that remote server. The fifth column tells us the database connected to, which is only relevant if you have more than one database in a DB2 instance.

You can add “show detail” to get more information, but it tends to wrap oddly and be a bit frustrating to read.

SQL

To get only the information that you want, executing SQL against administrative views is an appealing option. Here’s one of my favorites:

select  substr(A.AUTHID,1,20) as AUTH_ID,
        substr(A.APPL_NAME,1,20) as app_name,
        A.AGENT_ID as APP_HANDLE,
        substr(A.APPL_ID,1,25) as APPLICATION_ID,
        substr(A.APPL_STATUS,1,15) as status,
        A.STATUS_CHANGE_TIME,
        current timestamp-A.STATUS_CHANGE_TIME as SEC_IN_STATUS,
        substr(A.CLIENT_NNAME,1,15) as CLIENT_NNAME
from SYSIBMADM.APPLICATIONS A with ur
AUTH_ID              APP_NAME             APP_HANDLE           APPLICATION_ID            STATUS          STATUS_CHANGE_TIME         SEC_IN_STATUS          CLIENT_NNAME
-------------------- -------------------- -------------------- ------------------------- --------------- -------------------------- ---------------------- ---------------
APP_SAMPL_COGNOS     db2jcc_application                    421 192.0.2.0.35576.1508      UOWWAIT         2015-08-31-14.19.27.356145           12701.411023 dev.example.com
DB2INST              db2bp                                 388 *LOCAL.db2bcud.1508312101 UOWEXEC         2015-08-31-15.46.28.767159               0.000009 a.example.com
APP_SAMPL_COGNOS     db2jcc_application                    434 192.0.2.0.35654.1508      UOWWAIT         2015-08-31-14.21.22.696011           12506.071157 dev.example.com
APP_SAMPL_JENKINS    db2stmm                                59 *LOCAL.DB2.150831175730   CONNECTED       2015-08-31-10.57.30.224098           44858.543070 b.example.com
ETL_SAMPL_OTH        db2bp                                 276 *LOCAL.db2bcud.1508311948 UOWWAIT         2015-08-31-13.48.41.368669           15747.398499 a.example.com
DB2INST              db2evml_DB2DETAILDEA                   72 *LOCAL.DB2.150831175743   CONNECTED       2015-08-31-10.57.30.246624           44858.520544 b.example.com
APP_SAMPL_COGNOS     db2jcc_application                    427 192.0.2.0.35630.1508      UOWWAIT         2015-08-31-14.20.57.644798           12531.122370 dev.example.com
USER2                db2jcc_application                    348 192.0.2.0.53172.1508      CONNECTED       2015-08-31-13.41.06.107072           20522.660096 c.example.com
APP_SAMPL_JENKINS    db2fw2                                 65 *LOCAL.DB2.150831175736   CONNECTED       2015-08-31-10.57.30.232943           44858.534225 b.example.com
USER3                db2jcc_application                    328 192.0.2.0.60797.1508      UOWWAIT         2015-08-31-13.38.14.309984           20814.457184 d.example.com
APP_SAMPL_COGNOS     db2jcc_application                    420 192.0.2.0.35574.1508      UOWWAIT         2015-08-31-14.19.24.083186           12704.683982 dev.example.com
APP_SAMPL_JENKINS    db2pcsd                                71 *LOCAL.DB2.150831175742   CONNECTED       2015-08-31-10.57.30.236762           44858.530406 b.example.com
APP_SAMPL_JENKINS    db2jcc_application                    117 192.0.2.0.44096.1508      UOWWAIT         2015-08-31-14.55.39.951393            5048.815775 user1desktop
APP_SAMPL_COGNOS     db2jcc_application                    426 192.0.2.0.35618.1508      UOWWAIT         2015-08-31-14.20.26.749110           12602.018058 dev.example.com
ETL_SAMPL_CMIPS      db2jcc_application                    393 192.0.2.0.65273.1508      UOWWAIT         2015-08-31-14.03.38.105093           14250.662075 e.example.com
APP_SAMPL_JENKINS    db2fw1                                 64 *LOCAL.DB2.150831175735   CONNECTED       2015-08-31-10.57.30.232150           44858.535018 b.example.com
SNY                  db2jcc_application                    307 192.0.2.0.53187.15083     UOWWAIT         2015-08-31-15.33.11.228202            1317.538966 f.example.com
APP_SAMPL_JENKINS    db2fw7                                 70 *LOCAL.DB2.150831175741   CONNECTED       2015-08-31-10.57.30.236171           44858.530997 b.example.com
APP_SAMPL_JENKINS    db2jcc_application                    116 192.0.2.0.44095.1508      CONNECTED       2015-08-31-11.34.45.724759           41143.042409 user1desktop
APP_SAMPL_COGNOS     db2jcc_application                    438 192.0.2.0.35675.1508      UOWWAIT         2015-08-31-14.21.53.445475           12435.321693 dev.example.com
APP_SAMPL_JENKINS    db2fw0                                 63 *LOCAL.DB2.150831175734   CONNECTED       2015-08-31-10.57.30.231286           44858.535882 b.example.com
APP_SAMPL_JENKINS    db2fw6                                 69 *LOCAL.DB2.150831175740   CONNECTED       2015-08-31-10.57.30.235567           44858.531601 b.example.com
APP_SAMPL_COGNOS     db2jcc_application                    424 192.0.2.0.35597.1508      UOWWAIT         2015-08-31-14.20.12.767364           12615.999804 dev.example.com
APP_SAMPL_COGNOS     db2jcc_application                    437 192.0.2.0.35669.1508      UOWWAIT         2015-08-31-14.21.49.028633           12439.738535 dev.example.com
APP_SAMPL_JENKINS    db2lused                               62 *LOCAL.DB2.150831175733   CONNECTED       2015-08-31-15.45.37.410392              51.356776 b.example.com

This syntax requires a rather wide terminal to show nicely. Using normal SQL, you can adjust the widths, choose which columns you want to see, order on various things, count or summarize various things – it is quite useful.

To execute SQL, you have to be connected to a database, of course, which the previous two methods did not require.

GET SNAPSHOT

To get the most possible information in one human-readable file, the get snapshot command is useful. No database connection is required. You can get a snapshot for all applicaitons on a database using this syntax:

db2 get snapshot for applications on SAMPLE

You would replace SAMPLE with your database name. You can also get the snapshot for only one application using this syntax:

db2 get snapshot for application agentid 423

You get the agentid from one of the other methods here – it’s that unique identifier number referred to as the application handle.

The output from this command gives volumes of information. A sample of what it looks like is this:

            Application Snapshot

Application handle                         = 4750
Application status                         = UOW Waiting
Status change time                         = 09/04/2015 17:16:20.814976
Application code page                      = 1208
Application country/region code            = 0
DUOW correlation token                     = 192.0.2.0.61274.150902222312
Application name                           = db2jcc_application
Application ID                             = 192.0.2.0.61274.150902222312
Sequence number                            = 00008
TP Monitor client user ID                  =
TP Monitor client workstation name         = appserver.example.com
TP Monitor client application name         =
TP Monitor client accounting string        =

Connection request start timestamp         = 09/02/2015 15:23:11.979696
Connect request completion timestamp       = 09/02/2015 15:23:11.980173
Application idle time                      = 20 hours 29 minutes 27 seconds
CONNECT Authorization ID                   = ETL_APP_ID
Client login ID                            = ETL_APP_ID
Configuration NNAME of client              = appserver
Client database manager product ID         = JCC03640
Process ID of client application           = 0
Platform of client application             = Unknown via DRDA
Communication protocol of client           = TCP/IP

Inbound communication address              = 192.0.2.0 61274

Database name                              = SAMPLE
Database path                              = /db2data/db2binst1/NODE0000/SQL00001/MEMBER0000/
Client database alias                      = SAMPLE
Input database alias                       = SAMPLE
Last reset timestamp                       =
Snapshot timestamp                         = 09/05/2015 13:45:47.277970
Authorization level granted                =
   User authority:
      CONNECT authority
      LOAD authority
   Group authority:
      CREATETAB authority
      BINDADD authority
      IMPLICIT_SCHEMA authority
Coordinator member number                  = 0
Current member number                      = 0
Coordinator agent process or thread ID     = 993
Current Workload ID                        = 1
Agents stolen                              = 0
Agents waiting on locks                    = 0
Maximum associated agents                  = 18
Priority at which application agents work  = 0
Priority type                              = Dynamic

Lock timeout (seconds)                     = -1
Locks held by application                  = 1
Lock waits since connect                   = 0
Time application waited on locks (ms)      = 0
Deadlocks detected                         = 0
Lock escalations                           = 0
Exclusive lock escalations                 = 0
Number of Lock Timeouts since connected    = 0
Total time UOW waited on locks (ms)        = 0

Total sorts                                = 4
Total sort time (ms)                       = 0
Total sort overflows                       = 0

Buffer pool data logical reads             = 45653
Buffer pool data physical reads            = 1434
Buffer pool temporary data logical reads   = 0
Buffer pool temporary data physical reads  = 0
Buffer pool data writes                    = 0
Buffer pool index logical reads            = 874980
Buffer pool index physical reads           = 365
Buffer pool temporary index logical reads  = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes                   = 0
Buffer pool xda logical reads              = 0
Buffer pool xda physical reads             = 0
Buffer pool temporary xda logical reads    = 0
Buffer pool temporary xda physical reads   = 0
Buffer pool xda writes                     = 0
Total buffer pool read time (milliseconds) = 874
Total buffer pool write time (milliseconds)= 0
Time waited for prefetch (ms)              = 1737
Unread prefetch pages                      = 0
Direct reads                               = 714
Direct writes                              = 0
Direct read requests                       = 25
Direct write requests                      = 0
Direct reads elapsed time (ms)             = 11
Direct write elapsed time (ms)             = 0

Number of SQL requests since last commit   = 298
Commit statements                          = 5
Rollback statements                        = 2
Dynamic SQL statements attempted           = 35
Static SQL statements attempted            = 4
Failed statement operations                = 4
Select SQL statements executed             = 23
Xquery statements executed                 = 0
Update/Insert/Delete statements executed   = 4
DDL statements executed                    = 2
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds                 = 0
Internal rows deleted                      = 0
Internal rows inserted                     = 0
Internal rows updated                      = 0
Internal commits                           = 1
Internal rollbacks                         = 0
Internal rollbacks due to deadlock         = 0
Binds/precompiles attempted                = 0
Rows deleted                               = 37678
Rows inserted                              = 37678
Rows updated                               = 0
Rows selected                              = 102394
Rows read                                  = 798897
Rows written                               = 75356

UOW log space used (Bytes)                 = 0
Previous UOW completion timestamp          = 09/04/2015 17:04:03.145338
Elapsed time of last completed uow (sec.ms)= 31.673666
UOW start timestamp                        = 09/04/2015 17:04:15.770032
UOW stop timestamp                         =
UOW completion status                      =

Open remote cursors                        = 0
Open remote cursors with blocking          = 0
Rejected Block Remote Cursor requests      = 0
Accepted Block Remote Cursor requests      = 23
Open local cursors                         = 0
Open local cursors with blocking           = 0
Total User CPU Time used by agent (s)      = 114.707947
Total System CPU Time used by agent (s)    = 0.000000
Host execution elapsed time                = 159.639309

Package cache lookups                      = 39
Package cache inserts                      = 19
Application section lookups                = 1350
Application section inserts                = 229
Catalog cache lookups                      = 189
Catalog cache inserts                      = 23
Catalog cache overflows                    = 0
Catalog cache high water mark              = 0

Workspace Information


Most recent operation                      = Close
Cursor name                                = SQL_CURSH200C1
Most recent operation start timestamp      = 09/04/2015 17:16:20.808561
Most recent operation stop timestamp       = 09/04/2015 17:16:20.814974
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Number of OLAP functions                   = 0
Number of OLAP function overflows          = 0


Statement type                             = Dynamic SQL Statement
Statement                                  = Close
Section number                             = 1
Application creator                        = NULLID
Package name                               = SYSSH200
Consistency Token                          =
Package Version ID                         =
Cursor name                                = SQL_CURSH200C1
Statement member number                    = 0
Statement start timestamp                  = 09/04/2015 17:16:20.808561
Statement stop timestamp                   = 09/04/2015 17:16:20.814974
Elapsed time of last completed stmt(sec.ms)= 0.001412
Total Statement user CPU time              = 0.007974
Total Statement system CPU time            = 0.000000
SQL compiler cost estimate in timerons     = 106
SQL compiler cardinality estimate          = 145
Degree of parallelism requested            = 8
Number of agents working on statement      = 11
Number of subagents created for statement  = 11
Statement sorts                            = 0
Total sort time                            = 0
Sort overflows                             = 0
Rows read                                  = 145
Rows written                               = 0
Internal rows deleted                      = 0
Internal rows updated                      = 0
Internal rows inserted                     = 0
Rows fetched                               = 145
Buffer pool data logical reads             = 0
Buffer pool data physical reads            = 0
Buffer pool temporary data logical reads   = 0
Buffer pool temporary data physical reads  = 0
Buffer pool index logical reads            = 14
Buffer pool index physical reads           = 0
Buffer pool temporary index logical reads  = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads              = 0
Buffer pool xda physical reads             = 0
Buffer pool temporary xda logical reads    = 0
Buffer pool temporary xda physical reads   = 0
Blocking cursor                            = YES
Dynamic SQL statement text:
select * 
 from "SCHEMA1"."SOME_TABLE"


Memory usage for application:

  Memory Pool Type                         = Application Heap
     Current size (bytes)                  = 6422528
     High water mark (bytes)               = 39518208
     Configured size (bytes)               = 1048576

Agent process/thread ID                    = 993
  Agent Lock timeout (seconds)             = -1
  Memory usage for agent:

    Memory Pool Type                       = Other Memory
       Current size (bytes)                = 589824
       High water mark (bytes)             = 2752512
       Configured size (bytes)             = 67548065792

There is a wealth of information there, including information about where the connection is coming from, IDs associated with it, current state and how long it has been idle, last SQL statement it executed, and performance statistics about what it has done.

If you run a snapshot for all applications, the output consists of a section like that for every connection - which can be quite a lot of data. Most or all of the details here can be accessed through other ways, but I like it when I'm communicating information about a connection to developers or systems people since it's so easily in a readable format. It's also sometimes easier to grab a snapshot for applications to write the data out to a file before taking an action to resolve a problem - then the data is all there to look at later. If someone requests that I force off one or more connections, I often grab an app snapshot first to have the data in case someone else has questions later.

db2pd

db2pd has some advanatages and disadvantages - first, it's a very lightweight interface. It also can run even if the DB2 instance is hung or not responding. However, the output is more likely to change with fixpacks, and is not always all that pleasing to the eye. Here's what it looks like:

$ db2pd -alldbs -applications

Database Member 0 -- Database SAMPLE -- Active -- Up 5 days 03:01:23 -- Date 2015-09-05-13.58.46.588325

Applications:
Address            AppHandl [nod-index] NumAgents  CoorEDUID  Status                  C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid                                                            WorkloadID  WorkloadOccID CollectActData          CollectActPartition     CollectSectionActuals  
0x0000000203510080 59       [000-00059] 1          189        UOW-Waiting             0        0          0        0          *LOCAL.DB2.150831175730                                          0           0             N                       C                       N  
0x0000000204880080 276      [000-00276] 1          206        UOW-Waiting             0        0          0        0          *LOCAL.db2inst.150831194844                                      1           100           N                       C                       N  
0x0000000204010080 72       [000-00072] 1          202        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175743                                          0           0             N                       C                       N  
0x0000000204260080 65       [000-00065] 1          195        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175736                                          0           0             N                       C                       N  
0x00000002048C0080 328      [000-00328] 1          209        UOW-Waiting             0        0          995      14         192.0.2.130.60797.150831203130                                1           110           N                       C                       N  
0x0000000204550080 4749     [000-04749] 1          1622       ConnectCompleted        0        0          0        0          192.0.2.183.61273.150902222311                                0           0             N                       C                       N  
0x0000000204220080 58       [000-00058] 1          22         UOW-Waiting             0        0          0        0          192.0.2.170.60299.150831175723                                1           1             N                       C                       N  
0x00000002042A0080 71       [000-00071] 1          201        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175742                                          0           0             N                       C                       N  
0x0000000206420080 7051     [000-07051] 1          1590       UOW-Waiting             0        0          222      1762       192.0.2.195.53562.150904005324                                1           1971          N                       C                       N  
0x0000000204380080 64       [000-00064] 1          194        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175735                                          0           0             N                       C                       N  
0x0000000204240080 70       [000-00070] 1          200        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175741                                          0           0             N                       C                       N  
0x0000000204410080 63       [000-00063] 1          193        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175734                                          0           0             N                       C                       N  
0x0000000204480080 69       [000-00069] 1          199        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175740                                          0           0             N                       C                       N  
0x0000000204650080 9174     [000-09174] 1          14906      UOW-Waiting             0        0          517      1145       192.0.2.183.54540.150904235504                                1           2559          N                       C                       N  
0x0000000204460080 62       [000-00062] 1          192        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175733                                          0           0             N                       C                       N  
0x0000000205730080 9200     [000-09200] 1          1687       UOW-Waiting             0        0          103      1119       *LOCAL.db2inst.150905001500                                      1           2567          N                       C                       N  
0x0000000204CD0080 6542     [000-06542] 1          14764      UOW-Waiting             0        0          545      1187       192.0.2.183.49538.150903184214                                1           1808          N                       C                       N  
0x0000000204360080 68       [000-00068] 1          198        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175739                                          0           0             N                       C                       N  
0x00000002050E0080 2364     [000-02364] 1          1638       UOW-Waiting             0        0          126      1017       *LOCAL.db2inst.150901192354                                      1           652           N                       C                       N  
0x0000000204E70080 364      [000-00364] 1          221        UOW-Waiting             0        0          464      1087       192.0.2.130.60827.150831204956                                1           126           N                       C                       N  
0x0000000204250080 61       [000-00061] 1          191        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175732                                          0           0             N                       C                       N  
0x0000000204230080 8850     [000-08850] 1          14790      UOW-Waiting             0        0          895      1172       192.0.2.109.46190.150904200002                                1           2460          N                       C                       N  
0x0000000204470080 67       [000-00067] 1          197        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175738                                          0           0             N                       C                       N  
0x0000000204450080 60       [000-00060] 1          190        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175731                                          0           0             N                       C                       N  
0x0000000204350080 8849     [000-08849] 1          14781      ConnectCompleted        0        0          0        0          192.0.2.109.46189.150904200001                                0           0             N                       C                       N  
0x00000002044D0080 66       [000-00066] 1          196        ConnectCompleted        0        0          0        0          *LOCAL.DB2.150831175737                                          0           0             N                       C                       N  
0x00000002023C0080 8941     [000-08941] 1          216        UOW-Waiting             0        0          0        0          *LOCAL.db2inst.150904205939                                      1           2495          N                       C                       N  
0x00000002064A0080 4750     [000-04750] 1          993        UOW-Waiting             0        0          684      1184       192.0.2.183.61274.150902222312                                1           1354          N                       C                       N  

External Connection Attributes
Address            AppHandl [nod-index] ClientIPAddress                          EncryptionLvl SystemAuthID                                                                                                                    
0x0000000203510080 59       [000-00059] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204880080 276      [000-00276] n/a                                      None          ETL_SAMPLE_WTF                                                                                                                   
0x0000000204010080 72       [000-00072] n/a                                      None          db2inst1                                                                                                                         
0x0000000204260080 65       [000-00065] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x00000002048C0080 328      [000-00328] 192.0.2.130                           None          JSMITH                                                                                                                        
0x0000000204550080 4749     [000-04749] 192.0.2.183                           None          ETL_SAMPLE_STUFF                                                                                                               
0x0000000204220080 58       [000-00058] 192.0.2.170                           None          APP_SAMPLJENKINS                                                                                                               
0x00000002042A0080 71       [000-00071] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000206420080 7051     [000-07051] 192.0.2.195                           None          JOHNDOE                                                                                                                        
0x0000000204380080 64       [000-00064] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204240080 70       [000-00070] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204410080 63       [000-00063] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204480080 69       [000-00069] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204650080 9174     [000-09174] 192.0.2.183                           None          COROBINSON                                                                                                                      
0x0000000204460080 62       [000-00062] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000205730080 9200     [000-09200] n/a                                      None          ETL_SAMPLE_STUFF                                                                                                               
0x0000000204CD0080 6542     [000-06542] 192.0.2.183                           None          ETL_SAMPLE_STUFF                                                                                                               
0x0000000204360080 68       [000-00068] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x00000002050E0080 2364     [000-02364] n/a                                      None          ETL_SAMPLE_WTF                                                                                                                   
0x0000000204E70080 364      [000-00364] 192.0.2.130                           None          JSMITH                                                                                                                        
0x0000000204250080 61       [000-00061] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204230080 8850     [000-08850] 192.0.2.109                           None          APP_SAMPLJENKINS                                                                                                               
0x0000000204470080 67       [000-00067] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204450080 60       [000-00060] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x0000000204350080 8849     [000-08849] 192.0.2.109                           None          APP_SAMPLJENKINS                                                                                                               
0x00000002044D0080 66       [000-00066] n/a                                      None          APP_SAMPLJENKINS                                                                                                               
0x00000002023C0080 8941     [000-08941] n/a                                      None          ETL_SAMPLE_ATS                                                                                                                   
0x00000002064A0080 4750     [000-04750] 192.0.2.183                           None          ETL_SAMPLE_STUFF                                                                                                               

Trusted Connection Attributes
Address            AppHandl [nod-index] TrustedContext                                                                                                                   ConnTrustType                RoleInherited                                                                                                                   
0x0000000203510080 59       [000-00059] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204880080 276      [000-00276] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204010080 72       [000-00072] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204260080 65       [000-00065] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002048C0080 328      [000-00328] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204550080 4749     [000-04749] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204220080 58       [000-00058] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002042A0080 71       [000-00071] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000206420080 7051     [000-07051] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204380080 64       [000-00064] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204240080 70       [000-00070] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204410080 63       [000-00063] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204480080 69       [000-00069] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204650080 9174     [000-09174] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204460080 62       [000-00062] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000205730080 9200     [000-09200] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204CD0080 6542     [000-06542] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204360080 68       [000-00068] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002050E0080 2364     [000-02364] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204E70080 364      [000-00364] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204250080 61       [000-00061] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204230080 8850     [000-08850] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204470080 67       [000-00067] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204450080 60       [000-00060] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x0000000204350080 8849     [000-08849] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002044D0080 66       [000-00066] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002023C0080 8941     [000-08941] n/a                                                                                                                              non trusted                  n/a                                                                                                                             
0x00000002064A0080 4750     [000-04750] n/a                                                                                                                              non trusted                  n/a                                                                                                                             

Autonomous Routine Connections
Address            AppHandl [nod-index] Status          Autonomous Routine Handl [nod-index] Status                 

Anonymous Block Connections
Address            AppHandl [nod-index] Status          Anonymous Block Handl [nod-index] Status   

See what I mean? A lot of the information from other connections is there, but it's not the easiest thing to read.

Forcing Off Connections

There are situations that may require one or more connections to be forced off. If LOCKTIMEOUT is -1, then it's easy to have one connection blocking out others. Even with a reasonable LOCKTIMEOUT, sometimes applications do not properly commit and release locks, and might need to be forced off. If you're trying to do an offline backup, an upgrade, a fixpack, or server maintenance, you may need to force off all connections.

When forcing off one or more connections, be very cautious. This will be perceived as an outage by whatever connections are forced. Some applications don't respond well to being forced off the database, and don't easily pick up new connections.

To force off all connections to all databases in a DB2 instance, use:

$ db2 force applications all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

The reason it tells you that it is asynchronous is because all connections will rollback before disconnecting. There is no way to avoid this behavior (other than forcing the whole instance down), because it is required for transactional integrity. It can sometimes take hours for a connection to rollback, though most smaller and less active databases it will be much shorter.

Forcing the connections for one database in an instance without forcing them for all databases on the instance does not have its own command. This still annoys me. But there's a trick you can use with the quiesce command to do this:

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.4
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

$ db2 quiesce db immediate force connections
DB20000I  The QUIESCE DATABASE command completed successfully.
$ db2 unquiesce db
DB20000I  The UNQUIESCE DATABASE command completed successfully.

You need all three of these commands to accomplish forcing all connections off for a database. Do not leave the database quiesced, because you don't actually want the database quieced - you just want the force action of that command.

Finally, if you want to just force a single connection, use:

$ db2 "force application (8)"
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

There is no connection required for this command, and the '8' in the example above is the application handle from the methods of looking at applications above.

Share This