Thoughts on the IDUG Conference

During the opening keynote speech at the recent IDUG NA 2014, IBM's Leon Katsnelson, director of Cloud & Mobile, discussed technology that's changing the way applications are developed. Leon brought to the stage intern Luiz Aoqui to demo an application that's helping senior citizens with independent living.

What was amazing is that an entire application was developed and pushed to the cloud within minutes. Watch the demo.

I learned lots of new terms and technology during this presentation. The application was built using BlueMix, which enables developers to rapidly build, deploy and manage cloud applications while tapping into a growing portfolio of IBM software services.

Some of the services used by the demoed application include BLU Acceleration for Cloud, which supports in-memory technology using IBM DB2 with BLU Acceleration (which was used in this application to generate reports). In addition, Cloudant, a NoSQL database as a service (DBaaS), was used to persist the streaming data. The demo also discussed the need to notify someone when a fall has occurred. Also, Twilio, a cloud-based API that's used to develop communications systems, was used to enable the notification system used in the demo application.

Seeing how simple it was to add these services in the cloud and then in only minutes incorporate them into an application just amazed me. Seeing a database essentially dragged and dropped into that mix was very cool as well. I really feel like I was watching the future unfold before my eyes. If the BlueMix platform is successful, it will dramatically change the way applications are built.

Of course it isn't just software development that continues to change at a rapid pace. In my own presentation at IDUG, “With the Emergence of Big Data, Where do Relational Technologies Fit?” I discussed the evolution of the mainframe, from its role of providing central control to facilitating distributed client/server and then to the cloud and platform as a service. At the same time development has moved from waterfall to rapid application development to agile methodology supporting faster deployment and closer customer driver requirements.

No doubt, these are exciting times in the world of technology. I look forward to seeing where hardware, software -- and society -- will be in 10 years.

Why is DB2 Rewriting my SQL?

The EXPLAIN statement provides a view into what DB2 determines is the optimum access path for accessing the data. The valid statements that can be processed by the EXPLAIN statement are SELECT, MERGE and INSERT, as well as search forms of UPDATE and DELETE. The information obtained from the EXPLAIN statement is placed in a set of user tables known as the EXPLAIN tables. The DB2 for z/OS Managing Performance guide has details about using EXPLAIN as well as lists of tables.

With DB2 11, column changes have been made to some existing tables. New tables have also been added to the family of EXPLAIN tables. For a complete list of changes, see Chapter 12 ("EXPLAIN Table Changes in Version 11) in the What's New guide.

As IBM brings new features such as temporal and archive tables to the database, it becomes more important to your tuning efforts to know when DB2 is modifying your SQL. These modifications to the EXPLAIN tables in DB2 11 are designed help you identify these changes:

* The PLAN_TABLE includes a new column, EXPANSION_REASON, that identifies the particular global variable or special register that's used to implicitly modify the query to support temporal or archive table processing.

* The DSN_PREDICATE_TABLE is an existing table with a change to the description. The description is: The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query. It is also used as input when you issue a BIND QUERY command to override predicate selectivities for matching SQL statements.

When DB2 rewrites the SQL, the ADDED_PRED values identify the actions that DB2 performed.

A list of ADDED_PRED values follows. I've added my own notes in parentheses.

blank -- DB2 didn't add the predicate.

'B' -- For bubble up. (Note: DB2 copies predicates from sub-select or table-expression  into the outer query to help filter rows more quickly.)

'C' -- For correlation. (Note: When a sub-select is written without a correlation, the optimizer determines that a correlated sub-select will perform better than a non-correlated query.)

'J' -- For join. (Note: When the optimizer detects that related tables are being processed without providing join criteria, join criteria is added.)

'K' -- For LIKE for expression-based index. (Note: The optimizer detected an expression index and modified the SQL to take advantage of this index.)

'L' -- For localization.

'P' -- For push down. (Note: The optimizer detected a predicate from an outer query that can be used in a sub-select or table expression.)

'R' -- For page range.

'S' -- For simplification.

'T' -- For transitive closure.

Hopefully IBM will soon publish additional documentation on these values and how they're being used to improve performance.

Redbook Details Key DB2 11 Enhancements

IBM just released a draft version of the Redbook, "DB2 11 for z/OS Performance Topics." The publication details key functions for those migrating from DB2 Version 10.

DB2 10 was a major release that provided out of the box CPU savings, and this direction continues with DB2 11. Most of the DB2 11 CPU reductions are built into the DB2 engine and do not require application changes. You can take advantage of these enhancements even in CM mode (after REBIND for static SQL and PREPARE for dynamic).

IBM points to these CPU reductions for customers migrating from DB2 10:

• Online transaction workloads savings of up to 10 percent.
• Update-intensive batch workload savings up to 10 percent.
• Complex reporting queries savings of 25-40 percent when processing compressed tables. 
• Queries executed on DB2 Analytics Accelerator can see up to 10 percent savings.
• zIIP eligibility can contribute to cost reduction as the majority of asynchronous SRB processing under DB2 address space as well as the RUNSTATS and LOAD utilities are now zIIP-eligible.

Of course there's far more than I can include here, but to give you a broad idea of the material covered in this publication, here's the chapter list:

Chapter 1: Introduction

Chapter 2: Subsystem

Chapter 3: Synergy with z platform

Chapter 4: Availability

Chapter 5: DB2 Analytics Accelerator

Chapter 6: Data sharing

Chapter 7: SQL

Chapter 8: Application enablement

Chapter 9: Distributed environment

Chapter 10: Utilities

Chapter 11: Installation and migration

Chapter 12: Monitoring

If a DB2 10-to-DB2 11 migration is in your near future -- or if you've already completed one -- this Redbook is well worth your time. I'd appreciate hearing about your impressions of the document, as well as your views on DB2 11's most compelling enhancements. Please share your thoughts in comments.

The Changing Enterprise Application

The world of enterprise application development is rapidly changing. Most companies today have moved to an agile development methodology in an attempt to become more responsive to customers' needs. Craig Mullins, the renowned database systems development expert, was kind enough to interview me for this Enterprise Systems Media piece. We discuss how software developers can most effectively serve customers and, as product manager for CA Technologies, I explain our approach to providing DB2 database management solutions. I hope you'll give it a read.

On a related note, the new IBM Redbook, "Integration Throughout and Beyond the Enterprise," does an excellent job of explaining emerging application development patterns and how they're applied to critical tasks like supporting mobile devices and modernizing legacy applications.

Now, not every DBA is an application architect who must know every detail about how applications access the database. Nonetheless, I feel it's very important for DBAs to at least be aware of these trends and how they will impact their jobs. You may not care exactly how all these phones and tablets get to your database, but the reality is they will result in thousands of new transactions hitting your database, so you should have a clear understanding of what is going on. This Redbook doesn't tell you how to monitor and tune these applications, but it will give you an idea of the architecture being used, which can provide insight into tuning opportunities.

What are your thoughts about mobile devices, cloud computing and the impact of these new methods of application data access? Please share your thoughts in comments.