DB2 10 Data Sharing Fix Issued

IBM recently released a Red Alert for customers who are using data sharing and DB2 10 for z/OS.
Fortunately we don't see these very often. When they are issued they should be taken very seriously. From the alert:

"IBM has become aware of potential data loss when running data sharing under any mode of DB2 10 for z/OS.

"DB2 10 for z/OS takes advantage of a CFCC/XES performance enhancement by allowing repeated READ CASTOUT CLASS requests to the CF to utilize a restart token and retrieve from the queue where the previous read left off. A defect has been identified in this implementation of the enhanced READ CASTOUT CLASS in DB2 10 for z/OS."

The alert further states that APAR PM51655 should be applied to revert the DB2 10 behavior of the READ CASTOUT CLASS to the DB2 V9 (and prior releases) behavior.

Read more about this Red Alert and the recommended action here.
 

A Better Way to Manage Remote Connections

Have you ever had a DB2 utility job fail due to some remote connection holding locks? Or maybe you couldn't make a table change due to a faulty remote connection. Or maybe you're new to DB2 and you're wondering how to cancel the connection that's holding the lock so you can continue to run jobs in your maintenance window. The STOP DDF command is often used to resolve such issues. This terminates all DB2 connections, even those that aren't using the tables you're trying to maintain. Of course, the brute force of STOP DDF has its drawbacks, so over the years many DBAs have written REXX routines to interrogate DISPLAY THREAD output and terminate specific connections.

However, DB2 10 brings a new DDF command. MODIFY DDF provides the capability to halt specific connections by defining an alias that your application will connect to. For instance, by using the stop parameter--MODIFY DDF ALIAS(alias-name) STOP--MODIFY DDF tells DB2 to stop accepting new connections. Current active connections are unaffected and inactive connections are closed, and a stopped alias is marked ineligible for auto startup when DDF starts. Then once you've completed the maintenance window, use the start parameter--MODIFY DDF ALIAS(alias-name) START--to start the alias.

What about the current active connections that are impacting your maintenance window? These can be forced off the system with the cancel parameter -- MODIFY DDF ALIAS(alias-name) CANCEL. Cancel is like the stop parameter, in that it stops new connections, marks the alias as ineligible for start and cancels current active connections.

The difference between these two MODIFY DDF parameters is basically when they're used. Schedule the stop job--MODIFY DDF ALIAS (alias-name) STOP--to run first. Then, after about five minutes, and just before you kick off the maintenance jobs, run the cancel--MODIFY DDF ALIAS(alias-name). This gives the current running jobs time to wrap up and keeps new jobs from coming in.

If you've used MODIFY DDF in your shop, please go to Comments and offer some feedback on your experience.

Storing Data in Unicode

I recently heard a podcast on codepage, unicode and internationalization. Dan Luksetich and Susan Lawson interviewed IBM distinguished engineer and DB2 developer genius Chris Crone.

Chris discusses the implications a CCSID and codepage have on data translation and how the choice of codepage can impact storage as well as CPU consumption for translation services. From there he gets into UTF-8 and UTF-16, and how each can impact the performance of your Java applications; the different types of drivers needed to connect to the database when using a remote versus a local application, and the tradeoffs that must be made when, for example, local-running COBOL batch programs need the data in EBCIDIC and Java-based applications need the data in Unicode.

After listening to this podcast I wanted to learn more about Unicode and the implications of storing data as UTF-8 or UTF-16, so I went to the place I normally go to get more information: IBM Redbooks. Sure enough, I found "DB2 UDB for z/OS Version 8: Everything You Ever Wanted to Know... and More."

Even though this publication came out with DB 2 V8, it's still a great starting point if you're planning to store data in Unicode. I began with chapter 6, "Unicode in DB2 for z/OS." Chapter 6.1 (Conversion Basics) covers conversion services and gives some of the performance implications of using multiple CCSIDs in an SQL statement. In the podcast Chris talks a bit about this as well. He also gets into ramifications that incorrect coding can have on your result set. The Redbook, meanwhile, shows you how to write SQL code that won't cause performance degradation.

I followed up with Dan because I had a question on JDBC drivers. I like the way he summarizes this topic: type 4 drivers "talk" in UTF-8; type 2 drivers talk in UTF-16. In general, if a remote application is accessing a database encoded with UTF-8, use type 4 drivers; for a local application encoded with UTF-16, use type 2 drivers. For a local-running COBOL program, use UTF-16 if possible. Mixed application environments, understandably, don't have a simple solution. In these cases you must examine the frequency of execution and weigh the costs. Chris does point out during the podcast that a locally connected type 4 driver can take advantage of the hypersockets, thus mitigating the overhead of translation.

If you have experience in the area of tuning Java applications and using JDBC driver, please register your thoughts in Comments.

IBM Introduces IDAA to Speed Analytics

Not that long ago, running a large report typically took a few hours, and that was fine. But given the critical role that business intelligence now plays in making business decisions, time is a luxury.

Reading this explanation of BI, I was reminded of some of my work in the area of tuning decision support queries. These queries eventually ran against a separate copy of the data known as a data warehouse to avoid impacting the operational data.

As the need to run analytic queries against operational data becomes greater, the fact that these queries are often still long-running (and can provide unpredictable response times) becomes a bigger issue. To streamline the process, DBAs are asked to analyze and tune the SQL, which of course, besides being time-consuming for us, doesn't always produce favorable results anyway. And when companies don't get favorable results, they may look to other platforms to provide their analytics.

Last year, IBM is came out with IBM Smart Analytics Optimizer (ISAO) for DB2 for z/OS V1.1, which ran on z/Enterprise 196 with zBX hardware. Now just 15 months later, IBM has unveiled IBM DB2 Analytics Accelerator for z/OS V2.1. IDAA runs on IBM zEnterprise 196 or 114 with IBM Netezza 1000-3 hardware. Simply put, this solution is designed to provide faster response times for long-running, unpredictable queries. IBM also touts it as a cost-saver, because it's intended to save DBAs from implementing expensive SQL tuning efforts that can require new indexes or materialized query tables (MQTs).

Like the zIIP engines, the IDAA is a workload-optimized platform designed for special purpose processing. As such, you don't need to change your application to take advantage of it.

If you have the opportunity to work with IDAA, please post a comment so others can benefit from your experience.

Using the Display Database Command

DISPLAY DATABASE is one of the most useful and versatile commands in the DBA toolbox. It's used to  display information about the status of the database, table space and/or index space. It also displays who is using an object, any locks that are being held, any claimers and any pages in error.

However, it can be challenging viewing display database command results, because it's not obvious which columns are displayed and what they mean. To get the details, look up message ID DSNT397I in the DB2 10 for z/OS MESSAGES Guide.

The columns of information change based on the type of information (USE, CLAIMERS, LOCKS, LPL, WEPR) requested.

All options have these columns in common: NAME, TYPE, PART STATUS.

USE: NAME, TYPE, PART  STATUS, CONNID, CORRID, USERID
CLAIMERS: NAME, TYPE, PART  STATUS, CONNID, CORRID, CLAIMINFO
LOCKS: NAME, TYPE, PART  STATUS, CONNID, CORRID, LOCKINFO
LPL: NAME, TYPE, PART  STATUS, LPL PAGES
WEPR: NAME, TYPE, PART  STATUS, PHYERRLO, PHYERRHI, CATALOG, PIECE

If an information parameter type isn't specified, the default columns are based on WEPR format. Here's the basic command that I’ve used for years to find any objects in a restricted state:

-DIS DB(*) SPACE(*) RESTRICT

As another example, say you've made some changes to an object and DB2 is advising a reorg. To find these notifications, issue this command:

-DIS DB(*) SPACE(*) RESTRICT ADV(AREO*)

IBM has improved DISPLAY DATABASE over the past few releases by providing some nice filtering capabilities. First, you can filter on the object name through wild cards:

-DIS DB(dbname*)          : Display all database names starting with dbname
-DIS DB(*dbname)        : Display all database names ending with dbname.
-DIS DB(*dbname*)        : Display database names that contain dbname
-DIS DB(dbname1:dbname2)    : Display all database names between dbname1 and dbname2
-DIS DB(*dbstring1*dbstring2)    : Display all database names that contain string1 and string2

This same type of filter works with the space name parameter.

When DISPLAY DATABASE is issued, it displays only 50 lines (the default setting). To increase this setting, use the LIMIT keyword. LIMIT(100), for example, would display 100 lines of output. To see all output without limit, use LIMIT(*).

A final tip: When issuing a command with a parameter such as LOCKS, you'll get all rows for the name filter, even if the object doesn't have a lock associated with it. So use the very handy ONLY parameter to return only the rows that contain locks. For example, here's how you'd display all objects on the system that have active locks:

-DIS DB(*) SPACE(*) LOCKS LIMIT(*) ONLY

So what have you learned from your own use of the display database command? Please share your experiences in Comments.