How to identify DB2 Connect and JDBC driver users?

Donna Domovic

How to identify DB2 Connect and JDBC driver users?

Hello,

We have a request from our management to identify who is using DB2 Connect or the IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems  These products can both be multiple versions.  Unfortunately, since we have the unlimited license, this software has been handed out over the years and never tracked.

Is there some way that I can identify perhaps the userids and where the connections are originating from for items that use these products to connect to the mainframe DB2 subsystems? 

In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.

Any assistance you can provide is appreciated.

Thanks,

Donna Domovic

Philip Sevetson

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
You probably need to call on whoever maintains your network, and ask them to poll the workstations for instances of DB2 Connect. (If there isn’t anyone doing such things, or if they don’t have the ability, you have a different and bigger problem.)

From: Donna Domovic [mailto:[login to unmask email]
Sent: Friday, November 09, 2018 11:03 AM
To: [login to unmask email]
Subject: [DB2-L] - How to identify DB2 Connect and JDBC driver users?


Hello,

We have a request from our management to identify who is using DB2 Connect or the IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems These products can both be multiple versions. Unfortunately, since we have the unlimited license, this software has been handed out over the years and never tracked.

Is there some way that I can identify perhaps the userids and where the connections are originating from for items that use these products to connect to the mainframe DB2 subsystems?

In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.

Any assistance you can provide is appreciated.

Thanks,

Donna Domovic

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bill Gallagher

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
I used to do something similar at a previous job, to track who was connecting to mainframe DB2 from a distributed source.

I used MXG to extract from the SMF records (DB2ACCT) the following data (these are the MXG field names, which may or may not align with the actual SMF field names):

PLAN – (filtered to only select DISTSERV, for distributed connections)
QWHAMEMN – DB2 member name/subsystem ID
QWHDRQNM – IP address of Client
QWHDSVNM – DNS name of Client
LUUVTIME – timestamp (I used as SAS PROC FORMAT to reformat this to a DB2 timestamp)
QWHCAID – AUTHID

I took this info, loaded it (LOAD RESUME YES) into a DB2 table, and then created some views on the table to summarize the data.

This was extremely useful for finding which users were actually using DB2 Connect (we had Personal Edition deployed all over the place) so that we can manage our licenses. Users who had DB2 Connect licenses but weren’t actually using it would have their DB2 Connect product “downgraded” to a non-Connect DB2 Client.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Donna Domovic <[login to unmask email]>
Sent: Friday, November 9, 2018 11:03 AM
To: [login to unmask email]
Subject: [DB2-L] - How to identify DB2 Connect and JDBC driver users?


Hello,

We have a request from our management to identify who is using DB2 Connect or the IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems These products can both be multiple versions. Unfortunately, since we have the unlimited license, this software has been handed out over the years and never tracked.

Is there some way that I can identify perhaps the userids and where the connections are originating from for items that use these products to connect to the mainframe DB2 subsystems?

In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.

Any assistance you can provide is appreciated.

Thanks,

Donna Domovic

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Philip Sevetson

How to identify DB2 Connect and JDBC driver users?
(in response to Bill Gallagher)
Better answer than mine… !!

From: Gallagher,Bill R [mailto:[login to unmask email]
Sent: Friday, November 09, 2018 1:58 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: How to identify DB2 Connect and JDBC driver users?

I used to do something similar at a previous job, to track who was connecting to mainframe DB2 from a distributed source.

I used MXG to extract from the SMF records (DB2ACCT) the following data (these are the MXG field names, which may or may not align with the actual SMF field names):

PLAN – (filtered to only select DISTSERV, for distributed connections)
QWHAMEMN – DB2 member name/subsystem ID
QWHDRQNM – IP address of Client
QWHDSVNM – DNS name of Client
LUUVTIME – timestamp (I used as SAS PROC FORMAT to reformat this to a DB2 timestamp)
QWHCAID – AUTHID

I took this info, loaded it (LOAD RESUME YES) into a DB2 table, and then created some views on the table to summarize the data.

This was extremely useful for finding which users were actually using DB2 Connect (we had Personal Edition deployed all over the place) so that we can manage our licenses. Users who had DB2 Connect licenses but weren’t actually using it would have their DB2 Connect product “downgraded” to a non-Connect DB2 Client.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Donna Domovic <[login to unmask email]>
Sent: Friday, November 9, 2018 11:03 AM
To: [login to unmask email]
Subject: [DB2-L] - How to identify DB2 Connect and JDBC driver users?


Hello,

We have a request from our management to identify who is using DB2 Connect or the IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems These products can both be multiple versions. Unfortunately, since we have the unlimited license, this software has been handed out over the years and never tracked.

Is there some way that I can identify perhaps the userids and where the connections are originating from for items that use these products to connect to the mainframe DB2 subsystems?

In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.

Any assistance you can provide is appreciated.

Thanks,

Donna Domovic

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

James Campbell

How to identify DB2 Connect and JDBC driver users?
(in response to Bill Gallagher)
The other thing you might want to capture is QWHDPRID - Client's DRDA product id. When
you migrate Db2 z/OS to a new version, this will tell you who is running old versions of
Connect - which might not be certified for the new version of Db2 z/OS.

James Campbell

On 9 Nov 2018 at 18:57, Gallagher,Bill R wrote:

>
> I used to do something similar at a previous job, to track who was connecting to mainframe DB2
> from a distributed source.
>
> I used MXG to extract from the SMF records (DB2ACCT) the following data (these are the MXG
> field names, which may or may not align with the actual SMF field names):
>  
> PLAN - (filtered to only select DISTSERV, for distributed connections)
> QWHAMEMN - DB2 member name/subsystem ID
> QWHDRQNM - IP address of Client
> QWHDSVNM - DNS name of Client
> LUUVTIME - timestamp (I used as SAS PROC FORMAT to reformat this to a DB2 timestamp)
> QWHCAID - AUTHID
>  
> I took this info, loaded it (LOAD RESUME YES) into a DB2 table, and then created some views on
> the table to summarize the data.
>  
> This was extremely useful for finding which users were actually using DB2 Connect (we had
> Personal Edition deployed all over the place) so that we can manage our licenses.  Users who had
> DB2 Connect licenses but weren´t actually using it would have their DB2 Connect product
> "downgraded" to a non-Connect DB2 Client.
>  
> Bill Gallagher | Senior Systems Engineer, DBA | Data Administration
>  
> From: Donna Domovic <[login to unmask email]>
> Sent: Friday, November 9, 2018 11:03 AM
> To: [login to unmask email]
> Subject: [DB2-L] - How to identify DB2 Connect and JDBC driver users?
>  
> Hello,
> We have a request from our management to identify who is using DB2 Connect or the
> IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems  These products
> can both be multiple versions.  Unfortunately, since we have the unlimited license, this
> software has been handed out over the years and never tracked.
> Is there some way that I can identify perhaps the userids and where the connections are
> originating from for items that use these products to connect to the mainframe DB2
> subsystems? 
> In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.
> Any assistance you can provide is appreciated.
> Thanks,
> Donna Domovic
>  
> -----End Original Message-----
>
> This message (including any attachments) may contain confidential, proprietary, privileged and/or private information.
> The information is intended to be for the use of the individual or entity designated above. If you are not the intended
> recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than
> the intended recipient is prohibited.
>
> TRVDiscDefault::1201
>
> Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your
> subscription  
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Roy Boxwell

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
The IFCID correlation header is the go to place!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 9 Nov 2018, at 17:03, Donna Domovic <[login to unmask email]> wrote:
>
> Hello,
>
> We have a request from our management to identify who is using DB2 Connect or the IBM JDBC drivers for DB2 to connect to our mainframe DB2 subsystems These products can both be multiple versions. Unfortunately, since we have the unlimited license, this software has been handed out over the years and never tracked.
>
> Is there some way that I can identify perhaps the userids and where the connections are originating from for items that use these products to connect to the mainframe DB2 subsystems?
>
> In case it helps, we have BMC APPTUNE, CA SYSVIEW for DB2, and SMF available.
>
> Any assistance you can provide is appreciated.
>
> Thanks,
>
> Donna Domovic
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Donna Domovic

RE: How to identify DB2 Connect and JDBC driver users?
(in response to Roy Boxwell)

Thanks to everyone for the information.  One follow up question if you don't mind.  

You mentioned looking at the DRDA product id.  Do you happen to know what that would be for DB2 Connect and the IBM JDBC drivers?

Thanks,

Donna Domovic

Philip Gunning

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
This may help –

http://www-01.ibm.com/support/docview.wss?uid=swg24042680IBM%20DB2%20Version%2010.5%20Fix%20Pack%208%20for%20Linux,%20UNIX,%20and%20Windows%20-%20United%20Stateshttp://www-01.ibm.com









IBM Champion for Analytics

IBM Gold Consultant



Certified Information Systems Security Professional(CISSP)

Certification Number 539059

BS Comp Sci, MBA

Certified Advanced Db2 DBA v10.5

Certified Database Adminstrator, Db2 11.1

IBM Db2 LUW Support Page -- Db2 Support Portal https://www.ibm.com/mysupport/s/topic/0TO500000001fUNGAY/db2-linux-unix-and-windows?language=en_US&productId=01t50000004WmTk%20

Db2 Analytics Page https://www.ibm.com/analytics/data-management

Skype: DB2LUW

Twitter: DB2LUW

Direct +1.610.451.5801

IDUG DB2-L Hall of Fame

www.philipkgunning.com http://www.philipkgunning.com/AskMrDB2

IBM Business Partner

Microsoft Business Partner



From: Donna Domovic <[login to unmask email]>
Sent: Saturday, November 10, 2018 7:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: How to identify DB2 Connect and JDBC driver users?



Thanks to everyone for the information. One follow up question if you don't mind.

You mentioned looking at the DRDA product id. Do you happen to know what that would be for DB2 Connect and the IBM JDBC drivers?

Thanks,

Donna Domovic



-----End Original Message-----

Attachments

  • image001.png (17.4k)

James Campbell

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
Short answer to your actual question: SQL for Db2 Connect; JCC for JDBC

The DRDA product id for IBM products includes its version. See , for example
https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_conn
ect.html
in the SQLERRP field description.

Somewhere in http://www.opengroup.org is a list of codes for non-IBM products.

http://www-01.ibm.com/support/docview.wss?uid=swg21363866
has a list of JDBC (JCC) versions and the Connect version that delivered it.

James Campbell



On 10 Nov 2018 at 5:24, Donna Domovic wrote:

>
> Thanks to everyone for the information.  One follow up question if you don't mind.  
> You mentioned looking at the DRDA product id.  Do you happen to know what that would be for
> DB2 Connect and the IBM JDBC drivers?
> Thanks,
> Donna Domovic
>
>

Wolfgang Manus

RE: How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)

As you have both CA Sysview for DB2 and SMF available, consider loading the CA Sysview Archive DB2 tables from SMF. Then you can query all DDF connection data with SQL. Helped us a great deal identifying our DDF workload, related issues, outdated DB2 clients and servers that connect to DB2.

Here's a link to the CA Sysview documentation, you probably only need the APP_DDF_DETAIL and APP_DDF_DAILY tables:

https://docops.ca.com/ca-sysview-db2/20/en/reference/archive-tables/application-archive-tables

Best regards - Wolfgang 



Donna Domovic

RE: How to identify DB2 Connect and JDBC driver users?
(in response to Wolfgang Manus)

The information everyone provided, especially the MXG/SMF fields, was extremely useful.  II truly appreciate all the input and responses. 

With that information, I was able to generate a report with the information I need and have identified all but 1 of the products.  We have DB2 Connect, JDBC drivers from IBM and another vendor, and a product that starts with DDT with 1 specific example being DDT05100.

I tried to do some searches but haven't been able to track this one down.  Does anyone know what this might be?

Thanks,

Donna Domovic

Chris Hoelscher

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
Any product with DDT shouldn’t have any … bugs

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Donna Domovic <[login to unmask email]>
Sent: Saturday, November 17, 2018 10:56 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: How to identify DB2 Connect and JDBC driver users?


The information everyone provided, especially the MXG/SMF fields, was extremely useful. II truly appreciate all the input and responses.

With that information, I was able to generate a report with the information I need and have identified all but 1 of the products. We have DB2 Connect, JDBC drivers from IBM and another vendor, and a product that starts with DDT with 1 specific example being DDT05100.

I tried to do some searches but haven't been able to track this one down. Does anyone know what this might be?

Thanks,

Donna Domovic

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability, sex,
sexual orientation, gender identity, or religion. Humana Inc. and its subsidiaries do not
exclude people or treat them differently because of race, color, national origin, age,
disability, sex, sexual orientation, gender identity, or religion.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Craig Mullins

How to identify DB2 Connect and JDBC driver users?
(in response to Donna Domovic)
I believe DDT05100 could refer to Data Direct

Regards,
Craig Mullins
MullinsConsulting.com

Sent from my iPhone

> On Nov 17, 2018, at 9:56 PM, Donna Domovic <[login to unmask email]> wrote:
>
> DDT05100