I am having to create new DB2 workloads to better represent the outside world inside DB2 and I am finding it difficult to distinguish between the different connections because they either use the same authorization ID or come in through a middleware server like WebSphere or MicroStrategy.
Is there any way that I can push more outside information into DB2 ?
Many customers suffer from the same problem where authorization IDs are shared by more than one user, especially when coming through a middleware server. While using the same authorization ID for more than one user is not a good idea for security reasons, it is a reality at many customers. FYI, the long term solution to this issue for most customers would be to use the trusted context feature available in DB2 (which WebSphere already supports as an option).
The simplest solution to your problem is to leverage the DB2 client information fields:
- CLIENT USERID
- CLIENT APPLNAME
- CLIENT WRKSTNNAME
- CLIENT ACCTNG
By using these fields and providing workload definitions based on them, you can bring better discrimination between incoming work into your DB2 environment.
Some application server software, such as WebSphere and Cognos, offer a way to automatically set the DB2 client information fields differently for each transaction sent in to the database. Other applications offer ways to inject start and stop instructions or SQL into their ongoing processing and you can use these entry points to set the client information fields as you like often using internal variables from the application server software to gain insight into the individual context of each transaction.
I put together a fairly complete supplement on this approach which you can find hanging off the DB2 workload management best practices page (Implementing DB2® Workload Management in a Data Warehouse) or you can access the document directly with this link: "Setting DB2 Client Information Fields in Common Middleware Applications".