2015-02-02

The Usefulness of Java/.NET Impact Analysis
on DB2 for z/OS

 

Introduction

The past few months I was reflecting on the following subject: "how can we keep track of the origin of Java and .NET queries on DB2 for z/OS?" I discussed the subject with several people and came to the conclusion that the answer was somehow different than expected.

First of all, I had to reconsider the question itself, although the theme will be raised in many mainframe enterprises across the globe in the same way. 

We, as mainframe DBA, are used to ways of checking the impact of a database change in a PL/I, C or Cobol program. All three languages that typically make use of static SQL and create packages for which the impact analysis is fairly simple as embedded in the package implementation. For dynamic SQL, we had the tradition to keep the amount of it restricted, thus that we had full control over it. Up to recently very few dynamic SQL occurred outside controlled reporting tools or REXX programs for which we could easily read the objects being used from the source code.

This changed drastically in the past 10 years. Nowadays, the IBM Universal JDBC driver and data server driver allow direct connections to DB2 for z/OS from almost any application that is granted authority on the database. Java and .NET programs are now the major source of dynamic SQL. This dynamic SQL comes in huge amounts and is not controlled in many enterprises.

 

Application Domains

As application DBA one has to consider the impact of a database change on an application. As our traditional tools do not give the information we had for static SQL, for dynamic SQL, we had to rely on the experience of the application developer. Hence the question rose, whether we couldn't identify the programs (classes/packages) that make usage of these database objects as we did for static SQL?

The question was asked to both Java and .NET developers and they all gave clues on how to identify database objects in their source code, but none of the solutions proofed to be concise and all were related to the technologies being used to call the database.

Knowing this, we had to change our focus. No longer we had to search for a way to retrieve the objects from the source code. No, we had to focus on identifying the origin of a query starting from DB2 and collect all queries coming from this source to define the domain of a given application.

This revealed the magic word 'DOMAIN'. Although Domain Driven Design is de facto a standard on many platforms, DB2 for z/OS hasn't been affected mostly, as the mainframe was considered as being the 'enterprise domain' and holds in many places a single schema or only a few very large ones referring technical domains rather than application domains. With the advent of large quantities of dynamic SQL on DB2 for z/OS, the classic schema setup should be reconsidered.

The schema setup should depend on how DB2 for z/OS is addressed. If DB2 for z/OS is considered as data server feeding a Enterprise Data Bus (using stored procedures), the classic setup may continue to exist, as the outside API is controlled by native objects. 

However, when JDBC/ODBC applications are allowed to directly select data from DB2 for z/OS, the organization of DB2 should be adapted to the respective application domains, e.g. by implementing a schema per application domain and foresee views when cross-domain reads are required. Thus all objects that are being used by an application are within a given DB2 schema, as they are all part of that application domain. Unsurprisingly, this kind of setup simplifies the impact analysis for static SQL too and allows a uniform database approach whatever the underlying database.

But what about the exact packages/classes being impacted by a change? How can we communicate to the development that they need to adapt one or the other process? For now we can only tell them that their application is somewhere impacted? This leads us to a second aspect of this story: SQL monitoring.

 

SQL Monitoring and Object Analysis

For Dynamic SQL, monitoring is vital. As coding becomes more and more complex, one can no longer keep up with the technical changes that are linked to the wide variety of coding that is used to address ODBC or JDBC. As DB2 for z/OS DBA we should be able to focus (mainly, not exclusively!) on the database software and not on the development procedures. Hence we should capture the dynamic SQL statements from the dynamic statement cache and store them following a few principles.

In order to capture dynamic SQL, IFCID 318/316 should be activated along with SMF Compression (zParm SMFCOMP). This will enable dynamic statement cache explain and dynamic SQL monitoring. Alternatively, one could make use of a SQL Monitor such as IBM Query Monitor or CA Detector, but these tend to consume more CPU than trace based monitoring.

One way to capture dynamic statements is to explain the statements cache regularly. This could work, but ignores the possibility that your statement cache was flushed between the explain intervals, thus denying you access to part of the statements being issued.

Another way to capture dynamic statements is to make usage of monitoring tools with historical monitoring, such as IBM's SQL end-to-end monitoring feature from Omegamon for DB2 Performance Expert that copies near-term history data to a DB2 LUW repository in which you can keep the volatile performance data for a longer period. Similar options exist from other vendors, but I did not have the chance to learn them yet.

Once the statements are stored, the SQL syntax could be analyzed to extract the database objects being used. Along with the client information (to be set in the data source or the connection; eg. for JDBC see the client* properties) this allows you to build an impact analysis tool. This provides you a complete view on the impact of your changes using a granularity that is controlled by the developers themselves.

Conclusion

Forget about 'Database Impact Analysis for DB2 for z/OS .NET/Java programs' and welcome the idea of 'Database Impact Analysis for dynamic SQL'. This blog described a concept that could be used to create a database object usage overview for all dynamic SQL, not only the one used in Java and .NET. 

The issues related to the maintenance of dynamic SQL are clearly not restricted to the queries themselves, but relate also to the structure and the usage of the database itself. As DB2 for z/OS DBAs we should learn the principles of Service-Oriented Architecture and Domain Driven Design as more and more we will be confronted with the reasons why these theories emerged and are still relevant after several decades.

'If you can't beat them, join them', you might think, but there is nothing to beat. Along with us, developers try to provide high quality end products and we can only help them if we understand what they are doing and if they understand what we are doing and especially why it is important.

Getting an insight on how database objects are used is clearly an advantage in this task and the value has been proven over the past 30 years of DB2 for z/OS.



No comments:

Post a Comment