News, views, and items of interest on IBM's Db2 database management system and mainframes.

Friday, October 25, 2013

Say "Hello" to DB2 11 for z/OS

DB2 11 for z/OS
Generally Available Today, October 25, 2013

As was announced earlier this month (see press release) Version 11 of DB2 for z/OS is officially available as of today. Even if your
company won’t be migrating right away, the sooner you start learning about DB2
11, the better equipped you will be to embrace it when you inevitably must use
and support it at your company.

So let’s take a quick look at some of the highlights of this
latest and greatest version of our favorite DBMS. As usual, a new version of
DB2 delivers a large number of new features, functions, and enhancements, so of
course, not every new DB2 11 “thing” will be addressed in today’s blog entry.

Performance Claims

Similar to most recent DB2 versions, IBM boasts of
performance improvements that can be achieved by migrating to DB2 11. The
claims for DB2 11 from IBM are out-of-the-box savings ranging from 10 percent
to 40 percent for different types of query workloads: up to 10 percent for complex
OLTP and update intensive batch – up to 40 percent for queries.

As usual, your actual mileage may vary. It all depends upon things
like the query itself, number of columns requests, number of partitions that
must be accessed, indexing, and on and on. So even though it looks like
performance gets better in DB2 11, take these estimates with a grain of salt.

The standard operating procedure of rebinding to achieve the
best results still applies. And, of course, if you use the new features of DB2
11 IBM claims that you can achieve additional performance improvements.

DB2 11 also offers improved synergy with the latest
mainframe hardware, the zEC12. For example, FLASH Express and pageable 1MB
frames are used for buffer pool control blocks and DB2 executable code. So keep
in mind that getting to the latest hardware can help out your DB2 performance
and operation!

Programmer Features

Let’s move along and take a look at some of the great new
features for building applications offered up by DB2 11. There are a slew of
new SQL and analytical capabilities in the new release, including:

Global variables – which can
be used to pass data from program to program without the need to put data into
a DB2 table

The BIND and REBIND enhancements made in DB2 11 are important to note here,
too. Since BIND and REBIND spans application programming and database
administration, I’ll talk about here at the end of the Programming Features
section and right before we move on to talk about DBA features.

The first new capability is
the addition of the APREUSE(WARN) parameter. Before we learn about the new
feature, let’s backtrack for a moment to talk about the current (DB2 10)
capabilities of the APREUSE parameter. There are currently two options:

APREUSE(NONE): DB2 will not
try to reuse previous access paths for statements in the package. (default
value)

APREUSE(ERROR): DB2 tries to
reuse previous access paths for SQL statements in the package. If the access
paths cannot be reused, the operation fails and no new package is created.

So you can
either not try to reuse or try to reuse, and if you can’t reuse when you try
to, you fail. Obviously, a third, more palatable choice was needed. And DB2 11
adds this third option.

APREUSE(WARN): DB2 tries to
reuse previous access paths for SQL statements in the package, but the bind or
rebind is not prevented when they cannot be reused. Instead, DB2 generates a
new access path for that SQL statement.

So you can think of APREUSE(ERROR)
as functioning on a package boundary, whereas APREUSE(WARN) functions on a
statement boundary.

DBA and Other
Technical Features

There are also a slew of new in-depth technical and DBA-related
features in DB2 11. Probably the most important, and one that impacts
developers too, is transparent archiving using DB2’s temporal capabilities first
introduced in DB2 10.

Basically, if you know how to set up SYSTEM time temporal
tables, setting up transparent archiving will be a breeze. You create both the table
and the archive table and then associate the two. This is done by means of the ENABLE
ARCHIVE USE clause. DB2 is aware of the connection between the operational
table and the archive table, so any data that is deleted will be moved to the
archive table.

Unlike SYSTEM time, only
deleted data is moved to the archive table. There is a new system defined
global variable MOVE_TO_ARCHIVE to control the ability to DELETE data without
archiving it, should you need to do so.

Of course, there are more details to learn about this
capability, but remember, we are just touching on the highlights today!

Another notable feature that will interest many DBAs is the
ability to use SQL to query more DB2 Directory tables. The list of DB2
Directory tables which now can be accessed via SQL includes:

SYSIBM.DBDR

SYSIBM.SCTR

SYSIBM.SPTR

SYSIBM.SYSLGRNX

SYSIBM.SYSUTIL

Another regular area of improvement for new DB2 version is
enhanced IBM DB2 Utilities, and DB2 11 is no exception to the rule. DB2 11
brings the following improvements:

LOAD – additional zIIP processing, multiple partitions
can be loaded in parallel using a single SYSREC and support for extended RBA
LRSN.

REPAIR – new REPAIR CATALOG capability to find
and correct for discrepancies between the DB2 Catalog and database objects.

DSNACCOX – performance improvements

Additionally, there is a new command to externalize Real
Time Statistics. You can use ACCESS DATABASE … MODE(STATS) instead of stopping
and starting a database object or forcing a system checkpoint to externalize RTS.

DB2 11 also delivers a bevy of new security-related enhancements,
including:

Better coordination between DB2 and RACF,
including new installation parameters (AUTHEXIT_CHECK and
AUTHECIT_CACHEREFRESH) and the ability for DB2 to capture event notifications
from RACF

New PROGAUTH bind plan option to ensure the
program is authorized to use the plan.

The ability to create MASKs and PERMISSIONs on
archive tables and archive-enabled tables

Column masking restrictions are removed for
GROUP BY and DISTINCT processing

Online schema changes are still being introduced to new
version of DB2 amd DB2 11 offers up some nice functionality in this realm. Perhaps
the most interesting new capability is DROP COLUMN. Dropping a column from an
existing table has always been a difficult task requiring dropping and
recreating the table (and all related objects and security), so most DBAs just
left unused and unneeded columns in the table. This can cause confusion and
data integrity issues if the columns are used by programs and end users. Now,
DROP COLUMN can be used (as long as the table is in a UTS). Of course, there
are some other restrictions on its use, but this capability may help many DBAs clean
up unused columns in DB2 tables.

An additional online schema change capability in DB2 11 is
support for online altering of limit keys, which enables DBAs to change the
limit keys for a partitioned table space without impacting data availability.

Finally, in terms of online schema change, we have an
improvement to operational administration for deferred schema changes. DB2 11
provides improved recovery for deferred schema changes. With DB2 10, when the REORG
begins to materialize pending change it is no longer possible to perform a recovery
to a prior point in time. DB2 11 removes this restriction, allowing recovery to
any valid prior point.

In terms of Buffer Pool enhancements, DB2 11 offers up the new
2GB frame size for very large BP requirements.

There are also a lot of features added to DB2 11 to support
Big Data and analytical processing. Probably the biggest is the ability to
support Hadoop access. If you don’t know what Hadoop is, this is not the place
to learn about that. Instead, check out this link.

Anyway, DB2 11 can be used to enable applications to easily
and efficiently access Hadoop data sources. This is done via the generic table
UDF capability in DB2 11. Using this feature you can create a variable shape of
UDF output table.

This capability allows access to BigInsights, which is IBM’s
Hadoop-based platform for Big Data. As such, you can use JSON to access Hadoop
data via DB2 using the UDF supplied by IBM BigInsights.

DB2 11 also adds new SQL analytical extensions, including:

GROUPING SETS can be used for GROUP BY
operations to enable multiple grouping clauses to be specified in a single
statement.

ROLLUP can be used to aggregate values along a
dimension hierarchy. In addition to aggregation along the dimensions a grand
total is produced. Multiple ROLLUPs can be coded in a single query to produce multidimensional
hierarchies in a result set.

CUBE can be used to aggregate data based on
columns from multiple dimensions. You can think of it like a cross tabulation.

And finally, new version (V3) of IBM DB2 Analytics
Accelerator (IDAA) is part of the mix, too. IDAA V3 brings about improvements
such as:

The ability to store 1.3 PB of data

Change Data Capture support to capture changes
to DB2 data and propagate them to IDAA as they happen

Of course, there are additional features and functionality
being introduced with DB2 11 for z/OS. A blog entry of this nature on the day
of GA cannot exhaustively cover everything. That being said, two additional
areas are worth noting.

Extended log record addressing – increases the
size of the RBA and LRSN from 6 bytes to 10 bytes. This avoids the outage that
is required if the amount of log records accumulated exhausts the capability of
DB2 to create new RBAs or LRSNs. To move to the new extended log record
addressing requires converting your BSDSs.

DB2 11 for z/OS brings with it a bevy of interesting and
useful new features. They range the gamut from development to administration to
performance to integration with Big Data. Now that DB2 11 is out in the field
and available for organizations to start using it, the time has come for all
DB2 users to take some time to learn what DB2 11 can do.