SQL and Development Trends
Hello, and welcome back to It’s Not Your Daddy’s DB2… our series focusing on the changing world of DB2 for z/OS development and administration. We’ve already taken a look at the macro trends impacting the industry in Part 1; and in Parts 2, 3, and 4 we examined the multitude of database and management changes impacting DB2 for z/OS. In today’s post we take a look at the development side of the coin.
Modern Coding Techniques
Mainframe application development has changed dramatically during DB2’s lifetime. In the beginning, and for a long time thereafter, most DB2 programs were written in COBOL and coded to use static SQL. This meant that the SQL was bound before it was executed and the access paths were etched in stone. And it also meant that the access paths for any SQL in almost any program were readily available to DBAs in PLAN_TABLEs.
Fast forward to the present and static SQL in COBOL is the exception rather than the rule. Oh, sure, those COBOL programs with static SQL are still there, running in CICS or in batch, but new development is not done this way anymore. Today, programmers use IDEs to build code that accesses mainframe DB2 data using distributed data access over the web or from a GUI. They are using Java and .NET to
Most modern, distributed, application development projects typically rely upon application development frameworks. The two most commonly-used frameworks are Microsoft .NET and Java/J2EE. And these frameworks use dynamic SQL, not static.
An additional contributing force is the adoption of commercial off-the-shelf applications for ERP and CRM like SAP, Peoplesoft, and Siebel. These applications are not tied to a specific DBMS but support by multiple different DBMSs, one of which is DB2 for z/OS. So these applications use dynamic SQL because that makes it easier to accomplish multi-vendor DBMS support.
So the manner in which DB2 applications are developed has changed. And that means the manner it which DB2 is managed is changing, too. Instead of relying on access paths already being there, DBAs must be develop ways of capturing access paths for dynamic SQL statements. Prepared dynamic SQL can be cached in the dynamic statement cache so that the same SQL statement can reuse the mini plan for the statement the next time it runs. And the BIND command can be used to snap the statements in the cache and find the access paths used. But the dynamic statement cache works like a bufferpool, with least recently used statements being flushed to make room for new statements… so you might not find the dynamic SQL you are looking for, at least not without helpful tools or scripts to stay on top of things.
This change has caused many organizations to experience SQL performance problems. Because dynamic SQL is easier to introduce into the system performance can become problematic. Nobody knows what dynamic SQL runs when. And it is not tracked because the DBAs are not accustomed to monitoring and tuning dynamic SQL… or they just do not have the proper tools to be able to do the job appropriately. So these black holes of dynamic SQL performance problems open up that are left to run wild.
New SQL Stuff
It is not just the new programming paradigm that causes management problems for modern DB2 usage. The amount of new SQL statements and functions and features continues to grow with each new version of DB2. This can be a mixed blessing though. It is good because expanding SQL functionality makes it easier to program and access DB2 data. But it can be problematic because it can be more confusing and difficult to learn these new features.
Exacerbating the issue is that many organizations do not invest a sufficient amount in educating their staff. If the DBAs are not trained in all of the new functionality then new SQL can cause big issues. How? Imagine a shop that just migrated to DB2 11 for z/OS, but the only education available was to read the manuals. So some of the DBAs are not knowledgeable on the new SQL code. Then a developer picks up a manual, and learns how to use a new function or arrays as parameters. Kudos to the developer for the initiative, but if problems arise there may not be anybody who knows how to support the new feature.
And there is a lot of new SQL functionality being added. If we focus on just the past three new version (DB2 Versions 9, 10, and 11), here is a representative list of new SQL programming related enhancements that have been introduced: TRUNCATE, new data types (DECFLOAT, VARBINARY), optimistic locking, FETCH CONTINUE, ROLE, MERGE, SELECT from MERGE, pureXML, FETCH FIRST & ORDER BY in subselect and fullselect, INTERSECT, EXCEPT, Indicator Variables, TIMESTAMP precision and time zones, Moving sums and averages, Inline and Non-inline SQL scalar functions, SQL table functions, extended implicit casting, RANK(), ROW_NUMBER(), XQuery, transparent archive query, IDAA/analytics, grouping sets, ROLLUP, Hadoop access…
That is a lot to learn!
Things are moving at a fast and furious pace for application developers these days. And the resultant changes can introduce problems that impact your business unless you adapt to align your management and DBA capabilities with the new development methods.