How do I set up client information for Excel spreadsheets when I am using .net in my environment?


I was listening to a presentation at IDUG last week about the value of setting client information fields for middleware servers and I wanted to know how to do this for Excel spreadsheets (and IIS connections). Ideally,  I would like to do this automatically from the DB2 client without having to visit and modify all the individual spreadsheets....

If I could find a way to get the spreadsheet name passed along in the client information fields then I could identify and work on the problematic ones with the end-user; right now, some of them have multiple spreadsheets but they all look the same at the DB2 side of things so it is hard to debug and dive down to a specific one.

We use .net in our environment.



Our best practice for customizing DB2 clients is to set properties for connections using the db2dsdriver.cfg file. This approach allows people to set the client information fields but also other properties such as isolation level, etc. For example, we have had several customers that used this approach to reduce contention in their database caused by the Excel spreadsheet reporting by setting the Excel default connection isolation to UR. 

You can find out about the supported keywords here (but be aware that there may be a problem in the official documentation as there don't seem to be any embedded links or keywords available on the linked page right now, you need to look in the table of contents window on the left. I will report this problem but can't say when it will be fixed. Sorry! :(  ).

As to your specific question, there is currently no ability in this configuration file to set values from the individual user execution context. Unfortunately, at runtime, the DB2 client only has access to what the application gives us on the connect statement, such as userid, password, database name, etc. We do not have access to the information that the end-user might have given to Excel itself such as the spreadsheet name, etc.

What is typically done to get around this limitation is to set up multiple data source name (dsn) entries (e.g. "excel-hr", "excel-payroll"), each with different values in the appropriate client information fields,  and then have the user pick the appropriate one when they connect.

Hope this helps.

Recent Stories
Things to consider when considering Db2 Native Encryption

An old Db2 Easter Egg: Setting the default isolation value for dynamic SQL

Interpreting total_extended_latch_wait_time