Updates to Redbooks on DB2 10 for z/OS

I've written quite a bit about both IBM DB2 for z/OS manuals and IBM DB2 Redbooks. I generally use the manuals to educate myself about DB2 for z/OS features. When I'm looking for more in-depth details on different areas of practice within DB2, I often turn to Redbooks.

As with manuals, Redbooks are updated on a regular basis. Here's a quick list of DB2 Redbooks that I've read and used. All have been published or updated within the past year. The first two listings include links to previous blog entries I've written about these publications.

• "DB2 10 for z/OS: Configuring SSL for Secure Client-Server Communications" (published: Oct. 11, 2011; last update: Dec. 5, 2011; previous post: Getting Started with SSL)
• "Security Functions of IBM DB2 10 for z/OS" (published: Sept. 2, 2011; last update: Sept. 14, 2011; previous post: Roles and Trusted Context)
• "DB2 10 for z/OS Performance Topics" (published: June 21, 2011; last update: Dec. 6, 2011)
• "DB2 10 for z/OS Technical Overview" (published: Dec. 30, 2010; last update: Dec. 6, 2011)
• "Managing IBM DB2 10 for z/OS Using the IBM DB2 Administration Tool for z/OS Version 10" (published: April 22, 2011)
• "Extremely pureXML in DB2 10 for z/OS" (published: Jan. 28, 2011)

Star Schema and Pairwise Join

DBAs who design and organize data warehouses should take the time to read the IBM Redbook, "Enterprise Data Warehousing with DB2 9 for z/OS." In addition to offering a detailed explanation of data warehousing's evolution on z/OS, it provides a great description of star schema database design.

Star schema is commonly used when a database's primary function is to allow users to answer BI questions. The design is typically composed of a large fact table and several smaller dimension tables. These dimension tables contain values that are given IDs. These IDs are stored in the fact table in place of the real values.

One performance issue that can emerge from star join processing (JOIN_TYPE = 'S') is that DB2 filters the dimension tables before the fact table. To deal with this, these tables must be brought into memory (indicated with ACCESS_TYPE = 'T' in the plan_table), and dedicated virtual memory pools must be created to support the star join operation. The DB2 V8 administration guide has a formula to help estimate the size of the virtual memory pool based on the number of concurrent star joins along with the number of work files and the estimated number of rows in the work files. As you can imagine, as dimensions grow larger in number and size, you need to support and the size of these dimensions can have a negative impact on performance.

DB2 9 introduced pairwise join (JOIN_TYPE = 'P') to support dynamic index ANDing. The pairwise join considers each dimension as an independent object to match the fact table on single column indexes.

Pairwise join offers these benefits:
•    DB2 filters the dimension tables before accessing the fact table.
•    Poorly filtering processes on dimension tables are automatically terminated.
•    Greater parallelism.
•    The ability to fall back on RID-list failure by writing the RID pool to a work table space and continue processing.

To take advantage of pairwise join, a single column index must be created on the fact table. The STARJOIN, SJMXPOOL and SJTABLES zparms must also be set. (See the Redbook for much more about these parameters.)

To enable pairwise join:
•    Make sure all DB2 for z/OS maintenance is on. The minimum level is PUT0903.
•    Enable star schema features and pairwise processing (zparm EN_PJSJ = ON).
•    See PK76100 APAR/PTF – Rebind static SQL

If you like tuning SQL, you'll want to use the EXPLAIN command to see how DB2 is accessing the data. The DB2 Version 9.1 for z/OS Performance Monitoring and Tuning Guide has some very good information on designing and tuning star schema processing.

The pairwise join EXPLAIN output found in the PLAN_TABLE is much more complex than typical join processing. A sample SQL statement and EXPLAIN output is found on page 605 of the guide.

(Note: The ACCESS_TYPE = "MI" should only occur once -- the Guide errantly displays "MI" multiple times. I'm told IBM will soon correct this. The "MI" is the process of merging the RID list with pairwise join. DB2 is really doing multiple MI functions to merge all the RID lists using pairwise join; however, IBM wants to simplify the explain output, so MI is shown only once to represent all MI processing.)

More star schema and pairwise join resources:
•    APAR to enable pairwise joins (APAR PK76100)
•    List of APARs needed for pairwise processing
•    IDUG 2008 -- DB2 z/OS Optimizer: What have you done for me lately?
•    HoldData for DB2 9.1 PUT Level 0903
•    IBM presentation on DB2 9 Optimizer and sparse index processing used by the optimizer
•    IBM Redbook: Enhancing SAP by Using DB2 9 for z/OS
 

If you work with star schema processing on z/OS, please share your experiences in Comments.

Getting a Handle on REORG

I consider the REORG utility to be one of the most important tools in the DBA toolbox. It's also one of the toughest utilities to understand. REORG is used to improve data access and reclaim fragmented space. It can run on the table space as well as the index space.

In the early days of DB2, we feared REORG, because if a REORG went down, it'd mark the table space with a utility pending status. In most cases, the utility would fail due to a space shortage in either the work files or in the volumes on which these files reside. Oftentimes the only way out of this situation would be to terminate the utility -- then you'd have to recover the table space, something that could take hours (and you'd still be left with an incomplete REORG).

The introduction of the high availability option, SHRLEVEL CHANGE, was a huge enhancement, but the catch is you need enough disk space to duplicate the table space. I recall the first time I wanted to implement SHRLEVEL CHANGE; the storage management team wasn't real keen on the idea.

When TEMPLATE arrived on DB2, it helped with the dynamic allocation of datasets. It also meant that DBAs no longer had to hard-code DD statements in jobs or when setting dataset size. However, as table spaces grow you still must remember to go back and change these jobs to increase the size of the files. If you don't specify disk space parameters, DB2 estimates the size for you. To improve the accuracy of the default space estimation, make sure to run the RUNSTATS utility with the UPDATE SPACE or UPDATE ALL options before running any of these utilities: CHECK DATA, CHECK INDEX, CHECK LOB, REBUILD INDEX, REORG INDEX, REORG TABLESPACE.

Some of the TEMPLATE parameters are used to automate the primary and secondary space allocations on the datasets used by the utility. These parameters use values in the catalog that are set by RUNSTATS. So, especially if you have a high volume of growth, it's important to ensure the statistics are up to date before running a REORG. The parameters used are PCTPRIME, MAXPRIME and NBRESCND. PCTPRIME specifies the percentage of the estimated required space that is to be obtained as the primary allocation size. The default is 100 percent. However, if your table space is larger than a disk volume, you'll receive an error. You can use MAXPRIME to set the upper limit to avoid this problem with large objects. Once the primary restriction is applied (PCTPRIME or MAXPRIME), the remaining amount of space is divided by NBRSECND to determine the size of the secondary space allocations.

Two other challenges with REORG involve processing partitioned table spaces and dealing with non-partitioning indexes in a high-availability environment. DB2 version 9 introduced new online SHRLEVEL CHANGE processing that removes the BULID2 phase.  Prior to DB2 9, data that undergoes reorganization with either SHRLEVEL CHANGE or SHRELEVEL REFERENCE is unavailable to applications during the relatively lengthy BUILD2 phase. Due to the changes in how REORG processes the partitions and non-partitioning indexes to improve availability, you may in fact be forced to change the way you currently run your SHRLEVEL CHANGE reorgs.

If you're looking to better understand REORG and other DB2 utilities, I highly recommend the IBM Redbook, "DB2 9 for z/OS: Using the Utilities Suite."

Among many other things, this publication gives a detailed explanation (in Chapter 8.2) of the elimination of the BUILD2 phase in DB2 9. In general, the Redbook offers a complete guide to DB2 utilities.

So what have you found with the new DB2 9 online REORG? What do you think of other features delivered with DB2 9 and the utility suite? Please register your thoughts in Comments.

More Info on DB2 RI

The previous two weeks I've written about DB2 referential integrity (RI). My good friend Suresh Sane posted a comment to clarify my May 4 post on using RI.

Suresh also cites an IBM Redbook that he co-authored, "Data Integrity with DB2 for z/OS."

The major chapters in this publication are:

1. Introduction -- A quick review of the integrity and security built into the z/OS platform and DB2.

2. Semantic integrity -- A complete description of constraints and distinct types, the two sets of DB2 functions that help control the integrity of your DB2 data.

3. Referential integrity -- A discussion of referential constraints, RI relational model, design recommendations and much more.
4. Triggers -- A look at how triggers are used to enforce data integrity.
5. Other integrity features -- A discussion of other DB2 V8 features, including informational RI, Sequence objects and locking.
6. Recovery -- Learn the characteristics of recoverability of DB2 applications by analyzing the different recoverability functions. Topics covered include DB2 commit process, unit of work, data integrity, DB2 subsystem restart after abend, recovery of objects in error, application recovery process and point in time recovery.

Incidentally, when I read the chapter on recovery it reminded me of some situations I've encountered during my consulting years. Far too many companies do not have procedures in place to recover their data. I've actually been called into help companies retrieve their lost data. Once, I asked a DBA why his company didn't do its own recovery. Turns out they'd never done one, and the production system housed critical data that could not be reproduced.

My point is you can't afford to wait until you have a problem to figure out how to handle it. This Redbook has some good information that can help you design and implement a recovery plan. Then you can do what I like to call a recovery fire drill: Just stop a table space and delete the dataset, then step through your procedures to ensure it's recovered and in sync with all the RI related tables. You can and should "practice" recovery at least once or twice a year.

Configuring SSL with DB2 9 for z/OS

When I first installed DB2 9 for z/OS, I noticed a secondary port that supports Secure Sockets Layer (SSL). I was pleased I to see that DB2 for z/OS could participate in Web applications that require a secure connection.

Later, when I researched this feature, I found that SSL, in addition to supporting Web-based client connections, is also good for DB2-to-DB2 communication. Great, I thought--but how do I setup SSL and configure DB2 to take advantage of SSL?

If you're looking for the answer to this question, you need to check out a new document from the IBM Redbooks team. The draft Redpaper, "DB2 9 for z/OS: Configuring SSL for Secure Client-Server Communications," is intended to help network, security and database administrators with the task of setting up and configuring SSL with DB2 9 for z/OS.

The Redpaper covers these topics:
•    SSL and IP AT-TLS overview.
•    Configuring DB2 9 for z/OS server with SSL support.
•    Configuring DB2 9 for z/OS requester with SSL support.
•    Configuring Java applications using IBM Data Server Driver for JDBC and SQLJ to use SSL.
•    Configuring non-Java DB2 clients for Linux, UNIX and Windows to use SSL.
•    Configuring remote client applications to use SSL via a DB2 Connect server for Linux, UNIX and Windows.

This Redpaper provides an overview and some great diagrams to help you better understand the technology and architecture behind SSL. But what really makes it valuable is the inclusion of step-by-step instructions with sample JCL and commands.

If you've setup and configured SSL with DB2 9 for z/OS, please share your experience by posting in Comments.

Batch Modernization on z/OS

Much has been written in blogs and z/OS forums about the need to hire a younger generation to take over for the aging workforce. The reality is that fewer computer science graduates are wanting to work on the mainframe because it wasn't taught to them -- and because the mainframe wasn't part of their curriculum, they view it as outdated technology and a career dead-end.

Now, companies like IBM and CA are introducing products that are geared toward this new Xbox and PlayStation generation. And, just as Windows replaced DOS, you'll see the web browser replace ISPF and TSO. This will make the transition from developing and working on Windows and Internet Explorer much smoother and more familiar. However, while all this is great for the online transaction processing systems, what about the boring batch systems that are written in COBOL and execute using Job Control Language (JCL)? How can these batch-running programs send e-mail or save reports in PDF format so they can be shared and used by modern Web-based applications?

The recently released IBM Redbook, "Batch Modernization on z/OS," attempts to answer these questions. The publication explains why we need to implement new technologies like Java and PHP on z/OS. It also provides sample DB2 DDL and source code for implementing sample applications.

From my reading, I learned the interesting fact that some standard Java APIs are available to help you interact with z/OS services. For example:

•    MVS dataset and VSAM access to interact with z/OS specific data
•    Condition code passing for integration of Java batch jobs into z/OS job nets
•    z/OS Catalog search
•    Interaction with the MVS Console
•    Conversion of COBOL/ASM data types to Java types
•    Invoking DFSORT to effectively sort data
•    Access to z/OS Access Method Services (IDCAMS)
•    RACF APIs to integrate Java into the z/OS security model
•    Writing of Logstreams (e.g. as an Appender to LOG4J)
•    Submission of Jobs from Java

Since batch jobs do a lot with reading, writing and sorting sequential and VSAM files, it makes sense to provide these services available to Java programs. The world is changing very quickly, and the way we build and support applications on z/OS is changing just as rapidly.

As one who's repeatedly expressed his love of Redbooks, let me tell you that, from what I've read, "Batch Modernization on z/OS" is one of the most valuable Redbooks available to both traditional mainframe personnel and LUW (Linux, UNIX, Windows) pros who are getting started with z/OS. If your company is modernizing its batch systems, you owe it to yourself to download and review this publication.

And, if you or your company has already gone down the road of modernization, please take a moment to post a comment. It's always nice to learn from those who've gone through the process.

z/OS Next Generation: Learning the Basics

I recently blogged about "The Next Mainframe Generation." I think this topic resonates with many who are experiencing "right-sizing" in this down economy. I've heard some people say that IBM is providing a path only for young college students as opposed to helping experienced IT professionals who would love to learn and work on a z/OS platform.

As a consultant, I know that getting access to working software to learn the product makes getting that next job much more likely. I wish I could tell everyone how to get their hands on a free "training" environment hosting z/OS. But the next best thing, in my opinion, are IBM Redbooks publications.

Redbooks publications really are free training. Though I've long said that the IBM Redbooks library is rich with technical information, lately some readers have pointed me to some recently published Redbooks publications covering z/OS topics.

Let's start with "Introduction to the New Mainframe: z/OS Basics." Published just a few weeks ago, this Redbooks publication is designed to help students of information systems technology learn the basic concepts and prepare for a career in mainframe computing. The material introduces the mainframe environment, application and system programming on z/OS, online workloads for z/OS and much more.

Another very good series of Redbooks produced for z/OS is the "ABCs of z/OS System Programming." This 11-volume set is useful for beginning or experienced sytems programmers. Here's the list of volumes:

Volume 1: Introduces z/OS and storage concepts, TSO/E, ISPF, JCL, SDSF, and z/OS delivery and installation.

Volume 2: Covers z/OS implementation and daily maintenance, defining subsystems, JES2 and JES3, LPA, LNKLST, authorized libraries, SMP/E and Language Environment.

Volume 3: Introduces DFSMS, data set basics storage management hardware and software, catalogs and DFSMStvs.

Volume 4: This is an older volume that covers configuration in System/390 environments.

Volume 5: Covers Base and Parallel Sysplex, System Logger, Resource Recovery Services (RRS), global resource serialization (GRS), z/OS system operations, automatic restart management (ARM) and GDPS.

Volume 6: Introduces security, RACF, digital certificates and PKI, Kerberos, cryptography and z990 integrated cryptography, zSeries firewall technologies, LDAP and enterprise identity mapping (EIM).

Volume 7: Covers printing in a z/OS environment, Infoprint Server and Infoprint Central.

Volume 8: Covers z/OS problem diagnosis.

Volume 9:  Covers z/OS UNIX System Services.

Volume 10: Introduces z/Architecture, zSeries processor design, zSeries connectivity, LPAR concepts, HCD and HMC.

Volume 11: Covers capacity planning, performance management, WLM, RMF and SMF.

This extensive list of Redbooks publications covers practically every aspect of mainframe administration. I find them useful in my day-to-day job and I hope you will look into them.

IBM Redbooks: DB2 9 Distributed Functions

Often over the years I've been called in to help determine why some client/server or Web-based application accessing DB2 for z/OS isn't performing well. The problem determination process can be a challenge with so many parts involved (application design, application server, Web server, DB2 Connect server, z/OS, network).

"DB2 9 Distributed Functions" is a new draft version of the IBM Redbooks publication, "Distributed Functions of DB2 for z/OS and S/390." The updated publication dives into the world of developing applications that access DB2 for z/OS from Linux, UNIX and Windows (LUW) platforms.

Before you begin to design your application, you need a clear understanding of distributed relational database architecture (DRDA). How you process a unit of work (UOW) and DB2's two-phase commit influences performance and data integrity.

To make DB2 for z/OS accessible to distributed applications, you must perform some setup work. This Redbooks publication provides the steps for installing and configuring TCP/IP and IBM Workload Manager (WLM), and then shows, through sample form, how to get DB2 for LUW to access the catalog on DB2 for z/OS. This exercise is valuable simply as a means of verifying that DB2 for z/OS is properly configured to accept remote connections.

Security is another area that can impact application design. The publication covers DB2 security and the different techniques used to authenticate and authorize an application. Also explained are data sharing, designing for high availability and performance and problem determination techniques. You'll also find some sample programs to help get you started.

This Redbooks publication is worthwhile for anyone looking to better understand how to develop applications that run on LUW and access data in DB2 on z/OS.

Health Check for Packages

I've written extensive DB2utor blog entries about packages over the past few years. (See "IBM Redpaper: Considerations on Small and Large Packages", "Cleaning Out Obsolete Packages" and "Reducing the Impact of Long Package Lists.")

Despite my attention to this subject, I haven't provided much introductory material for those who are new to DB2 and development in programs written in languages like COBOL. A good place to start is the IBM Redbooks publication, "DB2 9 for z/OS: Packages Revisited." The beginning provides an overview of packages and details the benefits of using them.  As far as health checks, I refer to information you can gather from the DB2 catalog to:

• Create a list of invalid or inoperative packages (and then fix problems and/or remove obsolete packages).
• Create a list of PLAN statements that contain a DBRM. (This is important because an upcoming DB2 release requires that PLANs only contain packages.

When I write queries, I usually use SPUFI and I assume that I have short object names. Because of this, I like to substring the names to get more information on a screen.

Invalid or Inoperative
What follows is sample SQL listing all packages that are marked either invalid or inoperative. The package is marked invalid if a dependent object (like a table) is dropped. If a package is executed before the dependent object is recreated, DB2 marks it inoperative. Once a package is marked inoperative, it must be rebound manually using the REBIND package command:

SELECT SUBSTR(PK.COLLID,1,18) AS COLLECTION
                        , SUBSTR(PK.NAME,1,8)     AS PACKAGE
                        , HEX (PK.CONTOKEN)     AS CONTOKEN
                        , SUBSTR(PK.OWNER,1,8)     AS OWNER
                        , SUBSTR(PK.CREATOR,1,8)     AS CREATOR
                        , PK.VALID    AS VALID
                        , PK.OPERATIVE     AS OPERATIVE
                        , PK.EXPLAIN     AS EXPLAIN
                        , SUBSTR(PK.VERSION,1,26)     AS VERSION
                        , SUBSTR(PK.PDSNAME,1,44)    AS PDSNAME
FROM SYSIBM.SYSPACKAGE PK
WHERE (PK.VALID = 'N' OR PK.OPERATIVE = 'N')
ORDER BY PK.COLLECTION, PK.PACKAGE, PK.VERSION
WITH UR;

PLAN Contains DBRM
In a future DB2 release, the support of DBRMs in a PLAN is deprecated. Simply put, the PLAN can only contain packages. To help plan for the conversion, you can run the following SQL, which will list your existing PLANs that contain a DBRM. You'll want to convert the DBRM into a package and REBIND the PLAN with a package instead of a DBRM.

SELECT SUBSTR(D.PLNAME,1,8)          AS PLAN_NAME
                    , SUBSTR(D.NAME,1,8)     AS DBRM_NAME
                    , SUBSTR(D.PLCREATOR,1,8)      AS CREATOR
                    , SUBSTR(D.VERSION,1,26)         AS VERSION
                    , SUBSTR(D.PDSNAME,1,44)      AS PDSNAME
FROM SYSIBM.SYSDBRM D
ORDER BY D.PLNAME, D.NAME, D.VERSION
WITH UR;

This type of information and much more is available from the DB2 catalog. A list of the catalog tables can be found in the SQL Reference Guide. Here's a list of the DB2 manuals.

Redbook Residencies: Share Your Expertise

One thing I love about blogging on DB2 is that it keeps me in research mode. Anything I mention here comes from at least a bit of research. And in the process of doing research for this blog, I've discovered a lot of good information in IBM Redbooks.

While you may think that IBM developers write these publications, they are in fact the product of residencies. A residency is a multiweek effort where small teams explore and document product implementation, integration and operations.

And you may assume that these teams consist strictly of IBMers, they don't. You may not realize it, but you could be the next person to help IBM research and produce a Redbooks publication that will help others implement one of your solutions.

For example, are you working as a DBA with DB2 for z/OS? Perhaps you want to learn what's changed with the DB2 utilities since Version 7 and come up with some best practices on implementing and taking advantage of DB2 for z/OS 9 features. If so, you may want to look into IBM's "DB2 9 for z/OS: The Utilities Suite" residency. For this residency, you'd spend six weeks at IBM's San Jose, Calif., facility. To me participating in a residency is a win-win for any individual--and any employer. You're exposed to new technology and you build relationships with other experts in the industry. This knowledge and these friendships can help you and your company in many ways.

Learn more about IBM Redbook residencies here.