Through a application server where you open dedicated connections to database, you get information about your SQL using applications server user-id only. How do you tie those information back to the end user who initiated the transaction?
One option is to use DB2 trusted context connection where you tie web users to the existing application server’s user ids.
The other approach is to use DB2 JCC driver’s APIs to set value of following special registers.
- CLIENT_APPLNAME
- CLIENT_USERID
- CLIENT_WRKSTNNAME
- CLIENT_ACCTNG
The above special registers can be set using JCC driver’s APIs.
void setDB2ClientApplicationInformation (String info) throws java.sql.SQLException; void setDB2ClientUser (String user) throws java.sql.SQLException; void setDB2ClientWorkstation (String name) throws java.sql.SQLException; void setDB2ClientAccountingInformation (String info) throws java.sql.SQLException;
DB2 9.5 also has a system stored procedure through which you can set above special registers.
CALL SYSPROC.WLM_SET_CLIENT_INFO(‘vikram’, ‘bluestar.db2ude.com’,
‘auditor’, ‘Accounting department’, ‘AUTOMATIC’)
Through your web request, you can capture IP address and put it in CLIENT_WRKSTNNAME register and similarly you can also set above registers to the meaningful values so that you can tie them to your registered web user.
What do you get from above?
Through DB2 audits, you can get all of above special registers tie to the SQLs that are coming from the end-user and tie them instead of using application server’s user-ids.
The above should form part of the best practices of your application development in DB2.