How to Find All Open/Active Connections in Db2 (8.X)

Db2: Get current connection details

You can use special registers as pseudo-columns within SQL statements such as triggers:

SELECT 
 CURRENT TIMESTAMP,    
 SESSION_USER,
 CURRENT CLIENT_WRKSTNNAME
FROM
 SYSIBM.SYSDUMMY1

Source: https://www.ibm.com/docs/en/db2-for-zos/11?topic=elements-special-registers

Checking for DB2 Connection before running next SQL script

You can issue the connect command without parameters and check the return code; the DB2 CLP returns 4 if an error has occurred.

db2 connect >nul
if errorlevel 0 goto :label1
db2 connect to XXX...
:label 1
db2 -tf query1.sql

However, keep in mind that this is not guaranteed to work in all cases. For example, if the network connectivity has been disrupted the database connection will remain in a valid state until you issue the next SQL statement, at which point the error will be returned. For this reason you may want to choose running some query that's guaranteed to work, instead of the connect command, to "ping" the database server:

db2 select 1 from sysibm.sysdummy1 >nul
if errorlevel 0 goto :label1
db2 connect to XXX...
:label 1
db2 -tf query1.sql

How to identify DB2 port number

On the Windows DB2 server, open a DB2 Command Window and execute the command

db2 get database manager configuration | findstr /i svce

This should provide some output like:

 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 SSL service name                         (SSL_SVCENAME) =

SVCENAME is the port that DB2 is listening on. To resolve this name to an actual port number, you need to refer to the services file, which is located at %SystemRoot%\system32\drivers\etc\services.

SQL Connection ClientInfo

According to this you can get it from the special registers, here's an older topic that references the same thing.

Edited:

This is also a nice link that lists a number of useful statements dbForums

How to close all existing connections to a DB programmatically

You get that error when you are call Open() on a connection twice. You should make all SqlConnection objects you create inside using blocks and only open them once.

If you are reusing connections "to make it faster" .NET already does that by default for you via Connection Pooling but you must dispose of the connection object to make it work.

---

Related Topics