DB2 protocol connection infomation

[louis.lam@guardium.com]

Hi,

I am trying to find a view or a way I can figure out was protocol
users are connecting to the database. Ie: through TCP/IP, Local,
share memory, etc. In Oracle, I know you can select on the v
$session_connec t_info and in SQL Server it is the sysprocesses view.
Is there something like it in DB2. Any suggestion or advice is
greatly appreciate.

Thank you very much,
Louis.

[Jan M. Nelken]

Re: DB2 protocol connection infomation

louis.lam@guard ium.com wrote:

Hi,
>
I am trying to find a view or a way I can figure out was protocol
users are connecting to the database. Ie: through TCP/IP, Local,
share memory, etc. In Oracle, I know you can select on the v
$session_connec t_info and in SQL Server it is the sysprocesses view.
Is there something like it in DB2. Any suggestion or advice is
greatly appreciate.
>
Thank you very much,
Louis.

Protocols are enabled via Db2 registry variable called DB2COMM - it
lists supported protocols.

First step is to examine DB2COMM variable - if it is set to TCPIP - you
are accepting tcpip connections.

Local connections (on the DB2 server - can be using IPC what platform
we are talking about?

DB2 list applications will output (among other things) application id
(applid) string which enables you to differentiate between local and
remote incoming connections.

Jan M. Nelken

[Ekta]

Re: DB2 protocol connection infomation

Hi Jan/Louis

If Application ID is: 091A4EC9.0497.0 20205134655 .
Lets examine its parts.

091A4EC9 = IP address of the client machine (hex). = 09.1A.
4E.C9 (hex) = 09.26.78.201 (Decimal)

0497 = Client port (hex)

020205134655 = Timestamp.

Special Tip:
*** App id starts with a "G"?

Well db2/390 has a requirement that the app id always be alphanumeric,
and that the first character always be alpha.

So the first byte was changed so that the first character = G through
V (G + 15).

Basically what this means is that "G = 0x0, H = 0x1.... V = 0xF".

The above rules apply after the conversion.

So, in the above example, the App ID = G91A384C = 091A384C = IP
address of 09.26.56.76.


hope this helps.
Thanks,
@Ekta.

[louis.lam@guardium.com]

Re: DB2 protocol connection infomation

Thank you Ekta and Jan, I believe LIST APPLICATION is what I needed.
Since there is only local and TCP/IP connection for DB2, or at least
that is what I know of. We run DB2 on a lots of platform and our QA
just want to confirm what or how they are connected sometime. The
only thing I don't know is how to interpret the value of the
application id. Ekat, you gave a sample on your reply, how do I
figure out what this is? C0A80221.M812.0 109A2212057 Is there
some application or tool I can use to decode it. I know that
connection was done throught TCP/IP on the local server with port
50000, because I did it.

Thanks.

db2 =list application

Auth Id Application Appl. Application Id
DB # of
Name Handle
Name Agents
-------- -------------- ---------- ------------------------------
-------- -----
DB2INST1 db2jcc_applica 232 C0A801D2.I506.0 70613151846
SAMPLE 1
DB2INST1 db2bp 125 *LOCAL.db2inst1 .070613144308
SAMPLE 1
DB2INST1 db2bp 470 *LOCAL.db2inst1 .070612215133
SAMPLE 1
DB2INST1 db2bp 453 C0A80221.M812.0 109A2212057
SAMPLE 1

[Ian]

Re: DB2 protocol connection infomation

louis.lam@guard ium.com wrote:

Thank you Ekta and Jan, I believe LIST APPLICATION is what I needed.
Since there is only local and TCP/IP connection for DB2, or at least
that is what I know of. We run DB2 on a lots of platform and our QA
just want to confirm what or how they are connected sometime. The
only thing I don't know is how to interpret the value of the
application id. Ekat, you gave a sample on your reply, how do I
figure out what this is? C0A80221.M812.0 109A2212057 Is there
some application or tool I can use to decode it. I know that
connection was done throught TCP/IP on the local server with port
50000, because I did it.
>
Thanks.
>
db2 =list application
>
Auth Id Application Appl. Application Id
DB # of
Name Handle
Name Agents
-------- -------------- ---------- ------------------------------
-------- -----
DB2INST1 db2jcc_applica 232 C0A801D2.I506.0 70613151846
SAMPLE 1
DB2INST1 db2bp 125 *LOCAL.db2inst1 .070613144308
SAMPLE 1
DB2INST1 db2bp 470 *LOCAL.db2inst1 .070612215133
SAMPLE 1
DB2INST1 db2bp 453 C0A80221.M812.0 109A2212057
SAMPLE 1
>
>

In the previous post, Ekta described how to convert the 'C0A80221' into
an IP address. It's trivial to write a quick perl/shell script to do
this.

However, one other option you have is to take an application snapshot,
which will give you your answers in plain english. This probably isn't
a better solution (snapshots return *lots* of info!), but in a pinch
it will answer your question.

$ db2 get snapshot for all applications


Application Snapshot

Application handle = 446
....
Communication protocol of client = Local Client

Inbound communication address =


Application Snapshot

Application handle = 488
....
Communication protocol of client = TCP/IP

Inbound communication address = 10.103.141.182 1541
....