DB2 Developers Guide 6th Edition

Are you new to DB2 for z/OS and looking for that ONE document that holds all the information you'd ever need to know about DB2 for z/OS? Are you an experienced DB2 for z/OS professional who wants to take advantage of new DB2 10 features? Well, there's one book for both of you: "DB2 Developers Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS," by Craig Mullins. The sixth edition was recently released and published by IBM Press.

I reviewed this book prior to publication, and you'll find my recommendation in the front pages: "Once you’ve picked up and read DB2 Developers Guide, you will know why people on the DB2 List Serve forum refer to this book as the BIBLE. You will find that the DB2 Developer’s Guide is a comprehensive guide for both the beginner and experienced in DB2 and relational database technology…I cannot say enough about the DB2 Developer’s Guide."

I'll briefly expound on my accolades. What makes this book unique is the case studies illustrating various DB2 10 features and capabilities. A good example is the chapter on Temporal Data in DB2. There's 20 pages that go through in-depth examples of how to use temporal data.

This example shows how temporal extensions to SQL are used to simplify the SQL. The code is written to display the price of the ethics course as of 2011-12-25.

SELECT PRICE

FROM COURSE

FOR BUSINESS_TIME AS OF '2011-12-25'

WHERE TITLE = 'ETHICS';

Now here's how to code this without the temporal functions:

SELECT PRICE

FROM COURSE

WHERE BUS_START < '2011-12-25'

  AND BUS_END > '2011-12-25'

  AND TITLE = 'ETHICS';

 

If you don’t understand this SQL, don’t worry. Craig will walk you through the process of creating the DB2 table and indexes, providing numerous sample SQL code snippets that utilize temporal data.

Another nice thing is that the latest DB2 features are highlighted with an icon (V9 or V10). See the Amazon page for the table of contents and other information.

Have you already purchased this book? Let me know what you think.

A Closer Look at the IDAA

IBM has come out with a draft Redbook on the DB2 Analytics Accelerator (IDAA). Having written about the IDAA last year, I'm really looking forward to reading this new IBM publication.

"Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS" examines the IDAA and details how to manage data warehousing queries on z/OS with Netezza. The publication features a high-level overview of the technology; then the process of installing and integrating the IDAA with your current DB2 10 for z/OS environment is explained in depth.

Here's the table of contents:

Part 1. Business analytics with DB2 for z/OS
Chapter 1. Data warehousing on System z
Chapter 2. The DB2 for z/OS integrated solution

Part 2. Sample DB2 Analytics Accelerator implementation
Chapter 3. The business scenario
Chapter 4. Feasibility study
Chapter 5. Configuration and installation
Chapter 6. WLM settings for DB2 Analytics Accelerator
Chapter 7. Monitoring DB2 Analytics Accelerator environments
Chapter 8. Operational considerations
Chapter 9. Using Studio client to define and load data
Chapter 10. Query acceleration management
Chapter 11. Latency management
Chapter 12. Performance considerations
Chapter 13. Security considerations

Part 3. Additional topics
Chapter 14. Analytics and reporting on System z
Chapter 15. Data sharing and disaster recovery
Appendix A. Recommended maintenance
Appendix B. Additional material

I’ll soon have a chance to work with this technology and plan to write about some of my findings. What do you think? What does it take to manage this environment on a daily basis? If you have experience with the IDAA, please register your thoughts in Comments.

An Online Roadmap for New DB2 Users

I've found some recent updates on IBM DB2 for z/OS websites. Using the DB2 for z/OS Information Roadmap as a starting point, you'll find a lot of good technical material -- particularly if you're new to DB2 for z/OS.

Notice that the tabs in the middle of the page divide the site into sections on DB2 10, DB2 9 and DB2 8. There's also a section called "new-user."

Beneath the tabs, under the Product Overview>General Information heading, you'll find four links. The first is the IBM Information Management Software for z/OS Solutions Information Center. This is where you can learn about all the solutions that manage information on z/OS, including DB2, IMS and much more.

The second link is called "DB2 for z/OS Technical Resources." I often go here to research technical questions that are related to a specific DB2 release. Again, there's information pertaining to the most recent releases, from DB2 10 back to DB2 8.

The third link is to IBM Learning Services DB2 for z/OS classroom training. Scroll down a bit further, and under the "Version 10 information" subheading you'll find this comprehensive introduction to DB2 for z/OS.

Becoming a DBA

Over the years I've often been asked how one gets a job as a DBA. In my years of consulting I have, of course, talked to many DBAs who've taken all kinds of routes to where they are now. For instance, a former coworker started as a computer operator. He advanced into systems programming, and then moved into the database group. Another former coworker was a clerk doing paperwork, but she switched into IT and progressed into database administration.

Me, I was hired as a developer for a company that just got DB2. I was assigned to test a new DB2 application, a project that took about four months. When it was over, I was asked if I'd like stay on the database side. In each of these case, it was a matter of someone starting in one area of a company, building relationships and taking the opportunity when it was presented.

If you really want to be a DBA, you should naturally learn as much as you can about DB2. If possible, take a data modeling class and DBA class. But beyond technical knowledge, take the small steps to put yourself in position for the opportunity. Build a good relationship with your friends in the DBA group. If you can, learn the tools they use to monitor your applications. Have them show you when they make SQL coding changes to improve a given transaction or batch job. Doing these kinds of things will make you shine.

I'd really like to hear from the DBAs out there. How did you get your break? Please take a moment and share your story in Comments.

Learning DB2 for z/OS

This recent post about the free IDUG workshop prompted a reader from India to ask about the availability of such education in his region. Fortunately, just having Internet access opens up a world of inexpensive  DB2 for z/OS resources.

For instance, IBM manuals: Whenever I need to get started with something new, the DB2 for z/OS manuals are my first stop.

Then there are the DB2 for z/OS certification guides:

• DB2 9 for z/OS Database Administration: Certification Study Guide
• DB2 9 System Administration for z/OS: Certification Study Guide: Exam 737
• DB2 10 for z/OS Database Administration: Certification Study Guide

 There's also this IBM book that makes the case for upgrading to DB2 10.

 And don't forget about DB2 blogs.

Finally, I recommend this IBM presentation by Surekha Parekh, Willie Favero and Susan Visser: "Staying in Touch with the World of DB2 for z/OS." It greatly expounds on my brief blog list, covering an array of DB2 for z/OS collaborative groups.

Additional DB2 10 Resources

DB2 10 is the most significant release IBM has shipped and this is reflected in its rapid adoption rate. Based on the number of customers I hear from who have either upgraded or say they're in the process of migrating to DB2 10, I can't think of another release that's been so quickly embraced. If you're ready to make the upgrade, you should know that IBM offers DB2 10 for z/OS migration planning workshops. Contact your local IBM team if you're interested in this type of training.

If there isn't a workshop in your area (or even if there is), check out the free IBM e-book, "DB2 10 for z/OS: The Smarter, Faster Way to Upgrade to DB2 10."

The first section of the e-book is authored by IBM distinguished engineer John Campbell, who takes you through planning for the upgrade. The second section is authored by IBM gold consultant Cristian Molaro, who highlights the potential cost savings that can be gained through upgrading to DB2 10.

If you've read this document, please register your thoughts in Comments.

 

Removing a Member from a Data Sharing Group

DB2 10 brings many resource improvements in the area of CPU reduction and virtual storage relief. For instance, Jeff Berger discusses capacity improvements in his article, "DB2 10 Conversion Mode."

Because DB2 10 can provide significant virtual storage constraint relief, some companies are considering consolidating their data sharing groups to reduce the number of members. If you're new to data sharing, I highly recommend reading the IBM document, "DB2 10 Data Sharing: Planning and Administration."

Chapter 5 walks you through the process of removing members from the data sharing group. One of the steps is to run the stand-alone utility DSNJU003 with a new set of parameters. To deactivate the member, use DELMBR DEACTIV,MEMBERID=x. RSTMBR MEMBERID=x can then be used to re-activate the member. DELMBR DESTROY disables the member so it cannot be reactivated.

More information about stand-alone utilities and DSNJU003 is available in the Utility Guide and Reference.

Note that this publication was updated in January. Unless you're reading the current manual you won't find information about the DELMBR parameter.

If you're considering consolidating your data sharing groups or have experience in this area, please post a comment so we can all learn from you.

DB2 9 End of Service Announcement

I recently took note of IBM's announcement to end marketing of DB2 9. Well, the DB2 9 end of service announcement quickly followed.

DB2 9 end of service is scheduled for June 27, 2014, so IBM is giving you ample time to plan your upgrade. DB2 8 end of service, however, is only a few months away. If you've already made the decision and started migrating to DB2 9, you probably won't switch gears now (and shouldn't need to). If you haven't started planning to get off of DB2 8 though, your next move should be to DB2 10.

Granted, many companies use unsupported versions of DB2 and other IBM solutions, but there are important benefits to keeping your software reasonably current. For instance, check out this article by David Stephens. Relatedly, here's a good piece by Julian Stuhler on how to justify upgrading to IBM DB2 10 for z/OS.

So does this announcement alter your migration plans? Please post a comment and let us know.

Managing a Virtual Environment

IBM has a facility that establishes performance-monitoring parameters through a set of profile tables. These parameters are activated and deactivated through the START PROFILE command. I covered the profile tables and commands in a previous blog entry.

More recently, I learned about an extension to these tables that allow you to setup a virtual environment to model the access path that DB2 selects, based on a target system on which the SQL will run. I discussed this new facility last week.

I want to follow up on last week's post because I've since done some testing. After inserting the rows into the profile tables based on APAR PM26575 and then starting the profile, I did an EXPLAIN so I could examine the access path. However, when I checked the REASON column found in the user.DSN_STATEMENT_TABLE table to see if the optimizer used the profile setup to model this environment, I found it was blank. The virtual environment defined in the profile tables wasn't being used. I didn't know why this was the case, so I decided to check on the profile status, which is set when the START PROFILE command is issued. In the STATUS column found in SYSIBM.DSN_PROFILE_HISTORY, I discovered that the status was set to "REJECTED - INVALID SCOPE SPECIFIED." Huh? I went back to the APAR to see if I missed something. Then I noticed a separate APAR listed for DB2 10.

Upon examining the DB2 10 APAR, I found two DSNZPARMs that weren't commented on in the DB2 9 version APAR. They are: DSN6SPRM.SIMULATED_CPU_SPEED and DSN6SPRM.SIMULATED_CPU_COUNT. I figured these parameters had to be the same in both DB2 9 and DB2 10. I changed the parameter settings from the default of OFF to the values from the target, reassembled the DSNZPARMS then used the SET SYSPARM command to load them into memory. Finally I issued the START PROFILE command and found the status was ACCEPTED.

Now I have a virtual environment set up with the CPU information along with the memory information (RID POOL size, SORT POOL size, buffer pool size) and the catalog statistics about the objects taken from the target system. I issued the EXPLAIN statement again and found the reason contained the text "PROFILEID 6475." I was happy to see this because it confirmed that the optimizer is using this profile to define the access path.

Remember that you can define multiple profiles and change the bufferpool- and memory-related values in the profile tables. However, CPU Speed and CPU Count must be defined in the zparms. If you want to play what-if games (e.g., go from 5 to 10 CPUs), you'll need to modify the zparms, assemble and then reload. Monitoring the memory size of buffer or rid pools is more dynamic because you can change the information in the profile table and restart the profile to take effect.

If you're using this service I'd be interested in knowing more. Please post a comment so everyone can benefit from your experiences.

Production Modeling Made More Accurate

One significant challenge DBAs face is validating the access path of SQL from test to production environments. Even if the SQL looks great when run in a test environment, it may perform poorly once it's moved to the production system. Oftentimes this is because the code simply doesn't use the same access path in production that it does in test. 

The DB2 optimizer uses metrics stored in the catalog based on the runstats utility. To conduct testing in a non-production environment, you must have these same statistics. Third-party vendors have helped by providing tools that copy the statistics from one subsystem to another. This means we can prototype the SQL statement and come close to duplicating the access path. However, this is not full proof, since vendor solutions can't calculate the IBM hardware statistics that also figure into the optimizer's metrics:

• Number of CPUs
• Speed of CPUs
• RID POOL size
• SORT POOL size
• Buffer pool size

Now IBM is introducing its own production modeling function, which takes into account these hardware considerations. It's available via APAR PM26475.

To capture the metrics needed from the production system, apply the APAR and then execute this SQL statement:

SET CURRENT DEGREE='ANY';
  EXPLAIN ALL SET QUERYNO=6475 FOR
  SELECT * FROM SYSIBM.SYSDUMMY1;

SELECT HEX(SUBSTR(IBM_SERVICE_DATA,25,2)) AS CPU_COUNT,
            HEX(SUBSTR(IBM_SERVICE_DATA,69,4)) AS CPU_SPEED,
            HEX(SUBSTR(IBM_SERVICE_DATA,13,4)) AS RIDPOOL,
            HEX(SUBSTR(IBM_SERVICE_DATA,9,4))  AS SORT_POOL_SIZE

 FROM PLAN_TABLE WHERE QUERYNO=6475;

Here's the output in SPUFI:

CPU_COUNT      CPU_SPEED        RIDPOOL              SORT_POOL_SIZE
           000A         0000001C      000000FA                          009C4000

The APAR walks you through the process of storing this data and activating the GLOBAL PROFILE used by your test system's EXPLAIN function. With this solution in place, the access path you put into production should be the same one you created in test.