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 db2imon1A 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 db2imon1After 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 SAMPLEWhere 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:
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:
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 SAMPLEYou 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 423You 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.