How do I push more end-user identification into DB2 for monitoring or control purposes?

Question:

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 ?

Answer:

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".

0 Comments
Recent Stories
Verson 3.0.4 of IBM Graphical WLM tool now available in developerWorks

A quick summary of available Db2 controls for system resources

Managing resource consumption for multiple databases under the same DB2 instance