Some
users are very cautious about collection of statistics, some others would
rather prefer this process to be automatic.

If you
are part of the second group, this new feature is for you! However, the process
is not fully automatic: DB2 will only fill the SYSSTATFEEDBACK catalog table
with suggestions, when he/she thinks that executing RUNSTATS may be beneficial.
During the last IDUG, when asked if there is any plan to “close the loop”,
IBMers answered that there is no such plan. In other words, we should not
expect this process to be automated by DB2 itself.

A related note in the DB2 11 for
z/OS Technical Overview

Tooling available

In
Version 18.0, CA
Database Analyzer™ for DB2 for z/OS has been
enhanced with additional support for DB2 11 Optimizer recommended RUNSTATS
statistics. This tool now provides by default an extract object condition
(EOC), that evaluates the SYSSTATFEEDBACK table and currently selects all objects (at
the partition level) with a REASON value of BASIC, KEYCARD, or CONFLICT. The
sample Real Time Object Selection RTOS$S$C is provided for this purpose:

The
sample RTOS$S$C is provided,

under creator INSTALL.

The sample RTOS$S$C extracts objects

from the SYSSTATFEEDBACK table.

Create
an Action Procedure (to execute RUNSTATS)

tied to RTOS$S$C

CA
Database Analyzer generated the RUNSTATS commands

based on SYSSTATFEEDBACK
recommendations.

A couple of useful notes

The column STATS_FEEDBACK (in SYSIBM.SYSTABLES catalog table) controls whether the data is collected for a particular table (so you can exclude some specific tables from this process).

When the recommended statistics are collected, the RUSNTATS utility removes data from the SYSIBM.SYSSTATFEEDBACK catalog table.

Wednesday, December 10, 2014

Cypress is the code name for the next DB2 for z/OS release after
DB2 v11. Granting no hurricanes, or similar branding catastrophe, one can
expect the official name will be DB2 v12 (when Generally Available) – hence the
title of this article. A year and a half ago, I published a post DB2
v11 – expected timeframe & features which
is, by far, the most visited article on this blog. My “predictions” were quite
accurate, so I decided to re-iterate, providing you with an expected DB2 v12
timeframe (based on past releases dates), and a summary of the DB2 v12 “likely”
features (based on information presented during various DB2 User Groups -
including IDUG).

Although IBM
does not provide an exact schedule for their new versions of DB2, one can guess
that DB2 v12 will be available in 2016, probably in Fall as usual. Indeed,
looking at a therelease
schedule of DB2 over the last
15 years, you will notice that IBM releases consistently a new version of DB2
for z/OS every 3 years, and that the support of each version lasted 8 years –
with the exception of DB2 version 8, which had an additional year of support.
Worth mentioning as well, it’s no secret that the IBM development labs
transitioned several years ago to an Agile development methodology. This method
which tends to fix schedule (date of delivery) over amount of features
delivered, and tends to make new version delivery cycles very predictable.

Here a
graphical representation of the past DB2 for z/OS releases, with my best guess
of dates for DB2 v12 (Cypress) :

What
features can we expect?

I will start with a word of
caution: The following statements regarding IBM plans, directions, and
intent are subject to change or withdrawal without notice at IBM’s sole
discretion. In other words, the below information is “just” a summary of
information collected either on the web, or during public session in various
User Groups.

One first thing to note is that
IBM will not provide any skip-migration from DB2v10 to DB2v12. That means you
need to be on DB2v11 in order to upgrade to DB2v12. Based on comments from
IBMers, the skip-migration from DB2v8 to DBv10 was an exception, and we should
not expect any skip-migration process in the future.

APPLCOMPAT –
Application Compatibility

IBMers did mention (during IDUG)
that the APPLCOMPAT feature (available in DB2 v11) should continue to be
available in next versions of DB2. In a nutshell, IBM is updating DB2 SQL to be
more in line with SQL standards, and this may introduce problems for existing
applications. APPLCOMPAT is here to help!

You may remember a change in the
DB2 DML SQL introduced in DB2 v10 that changed the results of the CHAR’s
behavior when used to cast a decimal to a character or varchar (that is, any
result of SQL using CAST (decimal AS CHAR) or CAST (decimal
AS VARCHAR) may be different). IBM soon introduced a DSNZPARM keyword
BIF_COMPATIBILITY to let DB2 SQL behave the “good old” way and provided IFCID
366 to help monitor and locate the potential programs impacted (so application
can be corrected).

So, simply said, the APPLCOMPAT
feature is an extension of BIF_COMPATIBILITY : APPLCOMPAT lets you run a new
version of DB2, with the SQL DML behavior of a previous version. IFCIDs are
also provided to help locate and correct existing programs. It was mentioned
however, that, although this feature allows to upgrade DB2 with serenity
(postponing required application changes to a later date), application changes
have to be made, because IBM does not plan to provide application compatibility
forever. In other words, with migrating to DB2 v12, we can expect
APPLCOMPAT(V11R1) to work, but maybe not APPLCOMPAT(V10R1) any longer…