Hi DAW,

The purpose of this request is to improve the quality of information between Db2 and application.
I have also filled a suggestion form (WBR-15760) for this, but wanted to post here as well as other users may give their inputs as well.

I would like to request to have two CLI/ODBC functions exposed/implemented.

SQLSetConnectAttr
DB2: https://www.ibm.com/support/knowledg.../r0000646.html
MSSQL: https://docs.microsoft.com/en-us/sql...ql-server-2017

and

SQLGetConnectAttr
DB2: https://www.ibm.com/support/knowledg.../r0000600.html
MSSQL: https://docs.microsoft.com/en-us/sql...ql-server-2017

and off-course the corresponding attributes constants as well
DB2: https://www.ibm.com/support/knowledg...current_schema

Why?

I am interesting in using a few attributes available (at least on DB2), that are pretty usefull
for auditing, or Informational purposes, that complements the standard information.

On Db2 we can identify the application user and environment information, besides the user the application uses
to connect to the database.

Normally, when troubleshooting a db issue, DBA has the:
username
Application-Name and
workstation (name or IP address)
to identify the application connections.

But, those attributes captured automatically by Db2-Client, are becoming more and more useless now a days, because

Applications are coded to use a single user to connect to the databases. But Apps may have dozens or thousands of diff users.
Applications may be running in a single shared host, like RDP or Citrix environment. So you can't use the workstation to distinguish them.
and last, Application Names are becoming more and more the same, when managed by an Application Server like our WebApp.exe.
You may have 3 different WebApps, using same db, but on db2 side, all of them are identified as "WebApp.exe" as if they were all the same.
Same happens with other frameworks as well (not a DF exclusive issue here)

So, This is a nightmare for auditing, and troubleshooting purposes.

The solution ?

DB2 has 4 more attributes that could be overriden by the application, using SQLSetConnectAttr cli function:
  • SQL_ATTR_INFO_WRKSTNNAME
  • SQL_ATTR_INFO_ACCTSTR
  • SQL_ATTR_INFO_APPLNAME
  • SQL_ATTR_INFO_USERID



And, those attributes could be changed on the fly, even after the connection is already stablished. So this becomes pretty handy, in Web environment.
One could use "onAttach" hook procedure, and update the end-user information, so the real end-user activity could be easily identified and tracked on Db2 side.

The output bellow shows a regular connection attributes, without setting them:

Code:
Attribute          Value
------------------ ------------------------
APPLICATION_HANDLE 948
APPLICATION_NAME   TestProject.exe
APPLICATION_ID     *LOCAL.DB2.190420150952
CLIENT_WRKSTNNAME  -
CLIENT_ACCTNG      -
CLIENT_USERID      -
CLIENT_APPLNAME    -
SESSION_AUTH_ID    SAMUEL
CLIENT_HOSTNAME    SAMUEL-ALW
And this, the output withing the same connection, with the attributes set:

Code:
Attribute          Value
------------------ ------------------------
APPLICATION_HANDLE 948
APPLICATION_NAME   TestProject.exe
APPLICATION_ID     *LOCAL.DB2.190420150952
CLIENT_WRKSTNNAME  192.168.5.24
CLIENT_ACCTNG      Marketing
CLIENT_USERID      samucael
CLIENT_APPLNAME    Marketing WebService
SESSION_AUTH_ID    SAMUEL
CLIENT_HOSTNAME    SAMUEL-ALW


Now, for the Get side. This is the other face of the coin. In order to best cross referene application error logs, with DB2 error logs.
Db2 identifies the connections on its error logs using 2 values.. Application-ID and Application_handle. That's it.

We could enhance our application logs if we have those same values available. This would make easier the troubleshooting. Specially on web environment.
Using the SQLGetConnectAttr, we could retrieve those 2 identifiers in DF code, and have them writen in our application logs a well .
I would even go further.. I would like to see those same identifiers also logged in WebApp.log as well.

The corresponding attributes are :
  • SQL_ATTR_DB2_APPLICATION_HANDLE
  • SQL_ATTR_DB2_APPLICATION_ID



I understand that exposing the complete set of attributes on Set layer could be "dangerous", and might affect DAW CK standard design and behavior.
But the attributes describe here are Informational only, and do not imposes any risk in that sense. So, DAW may need to expose only the attributes they "think" that are safe for developers to change them.
Or implement an upper layer with specific Methods for each.

Regards