DB2 10 Migrations Simplified

The process of installing or migrating to a new DB2 release can be long and complex. Fortunately, IBM has greatly simplified the migration path to DB2 10.

Migrations are never easy. They require DBAs to perform many manual steps and procedures. However, IBM has recently made numerous improvements to help simplify the process. Most significantly, DB2 8 users can now migrate directly to DB2 10. That removed a major obstacle for many shops.

Other, less talked-about enhancements are also important. Here are some things to look for:

• A new program, DSNTRIN, is used to install and service DB2-supplied routines according to user-specified configuration parameters. JCL to execute this program is found in member DSNTIJRT.
• Another new program, DSNTRVFY, validates the installation and configuration of DB2-supplied routines. To execute this program, use job DSNTIJVR.
• A new core set of WLM environments is used to execute the DB2-supplied routines. This is a huge time-saver -- DBAs have spent many hours trying to determine which WLM configuration is needed to support a given supplied routine. The installed environments are:
  • DSNWLM_DEBUGGER: used by the unified debugger tool.
  • DSNWLM_DSNACICS: used by the DB2 CICS transaction invocation stored procedure.
  • DSNWLM_GENERAL: used by most DB2-supplied routines.
  • DSNWLM_JAVA: used by most DB2-supplied Java routines.
  • DSNWLM_MQSERIES: used by routines that use DB2 MQSeries functions.
  • DSNWLM_NUMTCB1: used by routines that must run serially.
  • DSNWLM_PGM_CONTROL: used by routines that are registered to an external security RACF program control (or an equivalent) and can therefore be loaded only with other similar routines.
  • DSNWLM_REXX: used by DB2 SQL procedure processor routines as well as REXX stored procedures.
  • DSNWLM_UTILS: used by DB2 utilities stored proedures.
  • DSNWLM_XML: used by DB2-supplied XML processing routines that require large amounts of memory.
• A new set of install panels are provided to specify routine configuration parameters.
• New pre-migration reports, executed from job DSNTEJ6Z, gather current subsystem parameter settings.

Take the word of someone who's been through many installations and migrations of older DB2 releases: This stuff makes a DBA's life much easier. It's nice to see IBM deliver jobs with programs that can simplify these processes.

DB2 Commands for IDAA

Last month I wrote about IBM's just-released draft Redbook on the DB2 Analytics Accelerator. Since then, I've researching the IDAA more closely.

Here's a quick list of IDAA commands:

-DISPLAY THREAD: The DISPLAY THREAD command has been enhanced to show threads that are executing on an accelerator. For example, to view all active threads running on an accelerator, issue this command:

                -DIS THREAD(*) ACCEL(*)

 

Page 45 of the draft Redbook I cited -- "Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS" -- displays the output and provides a detailed explanation.

-CANCEL THREAD: The CANCEL THREAD command can now be used to stop the processing of a thread that's executing on IDAA. (Note: You must apply maintenance PM54383 to enable the CANCEL THREAD command to support IDAA.)

-DISPLAY ACCEL: The DISPLAY ACCEL command returns a list of accelerators within a DB2 subsystem. It also provides detail status for a specific member or all members of a data sharing group. 

-START ACCEL(*): The START ACCEL command is used to startup all accelerators. ACCEL(*) within a subsystem or can be used to start a specific accelerator using ACCEL(name).

-STOP ACCEL(*): The STOP ACCEL command is used to stop all accelerators ACCEL(*) defined within a subsystem or a specifically named accelerator -- e.g., ACCEL(name).

Details about all the parameters available for these commands can be found in the DB2 10 for z/OS Command Reference (SC19-2972-04).

 

DB2 10 Conversion to Packages

A reader recently e-mailed me, explaining that his company is looking to convert all its plans that contain a DBRM to plans that contain only a package list. I pointed him to an article I wrote on this subject, "Converting to Packages."

In that article I reference an IBM Redbook. That Redbook, "DB2 9 for z/OS: Packages Revisited," was updated early this year, and it's a good resource for anyone undertaking a conversion to packages.

Chapter 4 is dedicated to converting to packages. To see how many plans in your system need to be converted, run this SQL statement:

SELECT COUNT(DISTINCT PLNAME) AS NUMBER_OF_PLANS FROM SYSIBM.SYSDBRM

The good news is that you don’t have to go and bind every DBRM into a package and then come back and rebind the plan. DB2 10 introduced the COLLID option as part of the rebind plan command. The Redbook provides a description of this option:

"To convert DBRMs bound within plans into packages, a new REBIND PLAN option COLLID (collection name or *) is introduced to perform the DBRM to package conversion. During the REBIND PLAN process, DB2 reads the SYSDBRM table to retrieve the DBRMs bound with the plan, binds them into packages and binds the packages into the plans. Each package is listed separately in the PKLIST, not as a collection *. Finally, the REBIND deletes the DBRMs bound into the plans. After the rebind, there are no longer any SYSDBRM or SYSSTMT records associated with the plan. The SYSPACKAGE and SYSPACKSTMT tables are populated with new package statement data."

If you've already migrated to DB2 10 and want to see which plans are using a default collection name, use this statement:

SELECT SUBSTR(NAME,1,20) AS NAME   

     , SUBSTR(COLLID,1,30) AS COLLID

FROM SYSIBM.SYSPACKAGE             

WHERE COLLID LIKE 'DSN_DEFAULT%'

Incidentally, while COLLID is a DB2 10 feature, DB2 8 and DB2 9 users can make use of it by downloading this APAR.

 

The Blurry Line Between DBAs and Developers

While DB2 for z/OS is the focus of this blog, I feel it's important to occasionally look at DB2 for LUW. More and more, mainframe DBAs must support DB2 for LUW in addition to DB2 for z/OS. As part of this mainframe DBA's own education, I've been reading the "IBM DB2 9.7 Advanced Application Developers Cookbook."

As the title spells out, this book is written for application developers who work with DB2 for LUW. Here's the chapter list:

Chapter 1: Application Development Enhancements in DB2 9.7

Chapter 2: DB2 Application Techniques

Chapter 3: General Application Design

Chapter 4: Procedures, Functions, Triggers, and Modules

Chapter 5: Designing Java Applications

Chapter 6: DB2 9.7 Application Enablement

Chapter 7: Advanced DB2 Application Features and Practices

Chapter 8: Preparing and Monitoring Database Applications

Chapter 9: Advanced Performance Tuning Tips

DBAs aren't really the primary audience here. This book is written by developers for developers. Nevertheless, many of the examples are intermixed with tasks that I would normally associate with a DBA's responsibilities. As a DBA myself, I expect a formal separation of duties, so I find this problematic.

Here's an example of what I mean: The first chapter features a discussion of how to alter and rename a column on a table. But the title of Chapter 1 is "Application Development Enhancements in DB2 9.7." That surprised me, because I consider DDL operations to be a DBA's responsibility.

On the other hand, I also realize that developers these days don't want to rely on DBAs to create tables or change table structures. And certainly a DBA's skillset isn't required to alter a table, only the authority to issue the ALTER TABLE RENAME COLUMN command. Continued improvements in database technology also blur the lines between DBAs and developers. For instance, as of DB2 9.7, the once-complicated task of altering a table can be accomplished through a single command:

 DB2 ALTER TABLE schema.tabname RENAME loc TO location

The final few chapters of the book examine application performance. I credit the author for covering this, because I believe many developers don't take the time to understand SQL coding techniques. This underscores my point about the respective duties of DBAs and developers as well.

Developers, understandably, are focused on understanding the language and APIs they work with. They don't have time to learn everything about databases, whether it's DB2 for z/OS or DB2 for LUW. And they don't necessarily understand physical table and index design options, and how even minor design changes can impact application performance. That's why I think it's important that DBA and developer tasks are strictly defined.

That said, developers can greatly benefit by learning the performance basics outlined in this book. For instance, if you're coding SQL statements, Chapter 9 ("Advanced Performance Tuning Tips") can help you. In addition to providing general rules for writing more efficient SQL statements, this chapter explains predicates in an SQL statement and offers ways to improve INSERT performance.

Despite my differences with the book's organization, I think the author does a great job of providing real-world examples that illustrate how to implement new DB2 9.7 features. There are also instructions for downloading sample code.

Chapter 3 offers a valuable look at how to take advantage of DB2's built-in encrypt/decrypt functions. I'll also highlight Chapter 6, which includes several sections dedicated to running Oracle application on DB2 for LUW. The first section discusses how to enable DB2 to run in Oracle compatibility mode. Other sub-chapters discuss using ROWNUM, support for PL/SQL exception handling and detailed steps on using IBM Migration Toolkit as well as Data Movement Tool to migrate a 4-5 terabyte Oracle database to DB2 for LUW in just two days. Finally, there's a chapter dedicated to porting multi-action triggers.

Any developers who've read this book, I'm interested in your thoughts. Please post a comment or send me a message.

May Mainframe Madness

For all the posts I've had of late about free educational and technical materials, I've been remiss to mention what my own company has been up to.

This month CA is running an event it describes as the world's largest virtual mainframe trade show: CA May Mainframe Madness 2012. By registering here, you can access webcasts, live chats and "on demand" presentations hosted by technical experts from across the mainframe world.

Here are some upcoming webcasts:

• Thursday, May 17, 10 a.m. EDT: Sheryl Larsen
        - DB2 9 and 10 SQL: Index and Optimization Enhancements
• Wednesday, May 23, 10 a.m. EDT: Craig Mullins and Steve Crocker
         - Getting to DB2 10 for z/OS

Here are some presentations:

• On Demand – Craig Mullins
         - DB2 Top 10 Lists Part 1
         - DB2 Top 10 Lists Part 2
• On Demand – Steen Rasmussen
       - Tips for Better DB2 for z/OS Performance
       - Do You Really Want NOT to LOG?
       - The DB2 10 catalog – A Revolution

And on Tuesday, May 29 at 1 p.m. EDT, there's a live chat roundtable with Sheryl Larsen, Steen Rasmussen, Scott Jessee and Dave Schipper. The moderator is none other than yours truly.

Once you've registered, you'll get a better idea of the virtual trade show format (you can also view these photos: Image 1, Image 2, Image 3, Image 4, Image 5). Stop by the "Database Management booth" in the "exhibit hall" to ask questions, or check out the "House Of Mainframe lounge" to network with peers, check for messages, join a group or sign up for a chat.

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.

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.

 

Fix Removes Implicit DBADM Privileges

Given the concerns over security compliance, IBM has steadily removed implicit authorities over the past few years. This direction of course extends to implicit authorities given to DBAs.

With that in mind, note the release of IBM APAR PM26977.

Database administration authority (DBADM) currently has these implicit privileges:

• System privilege STOPALL.
• USE privilege on system resources BUFFERPOOL and STOGROUP.
• System commands: STOP DB2, START/STOP/MODIFY DDF, START/STOP RLIMIT,CANCEL THREAD/DDF THREAD and RESET GENERICLU.

APAR PM26977 removes these implicit system privileges from DBADM. Once the fix is applied to DB2 10, these grant commands must be explicitly issued (e.g., allowing a DBA with DBADM authority to STOP DB2).

New Manual Covers DB2 10 for z/OS Security

Every few months, I visit IBM's DB2 product library and refresh all of my PDF manuals.

IBM was busy during the latter part of 2011. The majority of manuals have been updated since September, meaning I refreshed almost my entire DB2 10 manual library. Even if you're still on DB2 9 or DB2 8, you should check your own list, because these manuals have been refreshed as well.

In the course of my library upkeeping, I also found a new manual that you should be aware of: "DB2 10 for z/OS: Managing Security."

Since I haven't yet read this manual, I'll give you the roughest of outlines by citing some chapter titles:

Chapter 2. Managing access through authorization IDs and roles

Chapter 3. Managing access though RACF

Chapter 4. Managing access through row permissions and column masks

Chapter 5. Managing access through trusted context

Chapter 6. Managing access through data definition control

Chapter 7. Managing access through exit routines

Chapter 8. Protecting data through encryption and RACF

Chapter 9. Auditing access to DB2

 With the obvious interest customers have security and compliance issues, and given the many security-related enhancements in DB2 10, it's easy to understand why IBM came out with this new document. Diving into this information is one of my top priorities for the early new year. I'm really looking forward to reading this manual and learning more about DB2 10 for z/OS security.

DB2 10 Audit Policy

The DB2 trace facility provides a method to collect monitoring, auditing and performance information about your data, application and system. DB2 10 introduces the capability to define an audit policy to generate trace records based on an audit category.

DB2 audit policies are defined by inserting rows into the SYSIBM.SYSAUDITPOLICIES table. Each policy is specific to one of these categories:

CHECKING:         Logs audit access denied attempts as well as external security authentication failures.

VALIDATE:           Logs instances when a trusted connection is established or when a new user is using the connection.

OBJMAINT:         Logs instances when a table is altered or dropped. This includes cloned tables and implicitly created tables for XML columns.

EXECUTE            Logs instances when the given object schema, name and type is accessed during the first operation performed by each unit of work. Also records bind time information about SQL statements that include tables identified by object schema, name and type.

CONTEXT:           Audits all utilities.

SECMAINT:         Audits grant, revoke, create trusted context and alter trusted context.

SYSADMIN:         Logs instances of operations being performed using an administrative authority (install SYSADM, SYSADM and SYSCTRL; install SYSOPR and SYSOPR) to perform system administration tasks.

DBADMIN:          Logs instances of operations being performed using an administrative authority (system DBADM, DBADM, DBCTRL, SECADM, ACCESSCTRL, SQLADM, DBMAINT, PACKADM and DATAACCESS) to perform database administration tasks.

Once a policy is defined in SYSIBM.SYSAUDITPOLICIES, it can be activated by using the START TRACE command for an audit with the policy parameter AUDTPLCY(policyname). To learn more about audit policy and see examples, see the DB2 10 for z/OS Administration Guide.