Isolating DB2 for z/OS Accounting Data at the WebSphere Application Level

Back in the 1990s, almost all of the DB2 for z/OS-accessing transactional applications I encountered were of the CICS variety. Often, a given DB2 subsystem served as the database manager for multiple CICS-based applications. In such cases, isolation of DB2 accounting information (i.e., database activity figures obtained from DB2 accounting trace records) at the application level was not difficult. DB2 accounting trace records contain multiple identifier fields, and several of these are suitable for separating CICS-DB2 data access information along application lines. For example, one could set things up so that CICS-DB2 application A uses DB2 plan X, while application B uses plan Y. Thereafter, have your DB2 monitor generate an accounting long report (depending on the monitor used, this may be called an accounting detail report) with data aggregated (i.e., ordered or grouped -- again, the term will depend on the DB2 monitor used) at the DB2 plan level, and there's your application-specific view of database activity. You could also have application A run in CICS region X, and application B in region Y, and aggregate information in a DB2 monitor-generated accounting report by region ID (the corresponding DB2 accounting identifier might be referred to as connection identifier by your monitor). It's also easy to separate DB2 accounting information by CICS transaction name (your monitor might call this identifier a correlation name) and by DB2 authorization ID (so, CICS transactions associated with application A might use the DB2 authorization ID X, while transactions associated with application B use the DB2 authorization ID Y). These identifiers are generally usable with online DB2 monitor displays of thread activity as well as with batch-generated accounting reports, and they are as useful for CICS-DB2 applications today as they were 20 years ago.

While CICS-based applications are still a major component of the overall DB2 workload at many sites, lots of organizations have seen access to DB2 from applications running in WebSphere Application Server (WAS) increase at a rapid clip (for some companies, the large majority of DB2 for z/OS-accessing transactions are WAS-based). As is true of organizations that use CICS with DB2, organizations that use WAS with DB2 like to get an application-level view of database activity. How that can be accomplished is the subject of this blog entry.

First, let's look at the simplest situation -- one that's quite common: different WAS-based applications connect to a given DB2 for z/OS system using different authorization IDs. A master data management application might use DB2 authorization ID X, while a customer care application uses ID Y. In that case (as previously mentioned for CICS applications), you have your DB2 monitor generate an accounting long report with data ordered by primary authorization ID, and you're set. Easy. [You can further have your DB2 monitor, if you want, include or exclude data for an accounting report by an identifier such as authorization ID. Additionally, as pointed out already, authorization ID and other DB2 accounting identifiers are generally usable for differentiating data in online monitor displays as well as in monitor-generated reports.]

Sometimes, authorization ID isn't a granular-enough identifier for application-level isolation of DB2 accounting information. That is true when several WAS-based applications connect to a DB2 for z/OS system using the same authorization ID. This situation is not as unusual as you might suppose. More than a few organizations go this route as a means of simplifying security administration. How do these folks get application-specific DB2 accounting information? Let's consider some DB2 accounting data identifier possibilities:

• Plan name -- This probably won't do it for you. If you're using the type 4 JDBC driver for your application (the one that is used for programs that access DB2 data via the DB2 for z/OS distributed data facility, aka DDF), all applications will be associated with the same DB2 plan: DISTSERV. If you use the type 2 JDBC driver (an option when WAS is running in the same z/OS LPAR as the target DB2 system), it is possible to provide a differentiating plan name for an application, but in my experience people tend to go with the default plan name of ?RRSAF for all type 2 JDBC-driver using applications. [Note that an application running in WAS for z/OS and accessing a DB2 subsystem on the same z/OS LPAR can use either the type 2 or type 4 JDBC driver.]
• Requesting location -- A possibility, yes, but not if your organization runs -- as plenty do -- multiple applications in one instance of WAS. Multi-application WAS instances are particularly common in a z/OS environment, because a) z/OS LPARs often have a very large amount of processing capacity, and b) the sophisticated workload management capabilities of z/OS facilitate the hosting of multiple applications in one LPAR.
• Main DB2 package -- Probably not granular enough. WAS-based applications typically issue SQL statements in the form of JDBC calls, and when that's the case the main DB2 package for all applications will be one associated with the JDBC driver.
• Transaction name -- More than likely, too granular.
• End user ID -- Also too granular, and perhaps not a differentiator if the same end user utilizes several applications.

At this point you might be thinking, "So, what's left?" I'll tell you what identifier fits the bill for numerous organizations that use WAS together with DB2 for z/OS: workstation name. Truth be told, this did not initially occur to me when I pondered the database activity differentiation question in the context of WAS-based applications that use the same DB2 authorization ID. I got hung up on the term "workstation," and thought of that as being an identifier that would be tied somehow to an actual physical device. Silly me. As succinctly and plainly explained by a WAS-guru colleague of mine, "it's just a string" -- a label. And, it's a string that can easily be set for a WAS-based application, through several means:

• Via the WAS administration console GUI (in which case it would be an extended property of an application's data source).
• Via the IBM Data Server Driver for JDBC (the driver provides a JAR file that contains the DB2Connection class, and that class supports the Java API setDB2ClientWorkstation).
• Via application code, for JDBC 4.0 and above (you would use the Java API setClientInfo).

[Note that with regard to the second and third options in the list above, option three (the setClientInfo Java API) is recommended over option two (the Data Server Driver method), because setDB2ClientWorkstation was deprecated with JDBC 4.0.]

Once the workstation name has been set for your WAS-based applications, you can direct your DB2 monitor to generate accounting reports with data ordered by workstation name, and voila -- there's your application-specific view of database activity (and workstation name should also show up in your DB2 monitor's online displays of thread activity).

Want more information on this topic? You can find plenty -- with examples -- in an IBM redbook titled, DB2 for z/OS and WebSphere Integration for Enterprise Java Applications (downloadable at http://www.redbooks.ibm.com/abstracts/sg248074.html?Open). In particular, check out sections 5.5 and 8.2 of this document.

And one more thing: while I've described workstation name as a means of separating DB2 accounting information along the lines of WAS-based applications, you should keep in mind that identifiers provided by Java client information APIs can also be very useful for workload classification in a z/OS WLM policy.

Some DB2 for z/OS people who are relatively new to the client-server application scene may be a little uneasy about such applications, thinking that they can't monitor and control them as they could the DB2 transactional applications of old. In fact, the monitoring and controlling facilities you want are there. Use them, and rest a little easier.