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.

 

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).

DB2 10 Information APARs

Last week I listed some recently updated IBM Redbooks. When I looked more closely at one of these publications, the "DB2 10 for z/OS Performance Topics" Redbook, I found a great of list of APARs. IBM considered these DB2 10 for z/OS information APARs important enough to list in appendix A:

• II10817 – STORAGE : Info APAR for storage usage errors

• II14334 – LOBs : Info APAR link all the LOB support delivery

• II14401 – MIGRATION : Info APAR for fallback to DB2 9 from DB2 10

• II14426 – XML : Info APAR link all the XML support delivery

• I14619 – MIGRATION DDF : Info APAR for DB2 10 DDF migration

Informational APARs can help you keep track of issues related to a specific area of DB2. If you're moving to DB2 10, you can receive notifications when any of these APARs are updated. Just go any individual APAR page and select the Register link in the top right corner.

Finding a DB2 for z/OS APAR

Those new to z/OS may be unfamiliar with the terms APAR and PTF.

IBM tracks customer problems through Authorized Program Analysis Reports (APARs). An APAR is processed by an IBM change team. Once IBM fixes the problem and the customer verifies the fix, a Program Temporary Fix (PTF) is produced and the APAR is closed. 

As you can imagine, if you're having a problem, there's a good chance that some other customer has already experienced the same issue. So before contacting IBM Support, search the IBM System z Technical Help database (and select APARs) to see if a fix has been created for your particular problem. All APARs in the database include a PTF number, so when you find what you're looking for, you can verify with your IT team (in our case, your DB2 systems programmer) whether or not the fix has been applied.

While the above link is one way to access the APAR database (I'll list some additional ways in a bit), there's a fairly new option that you may find more convenient: Simply download an IBM Software Support toolbar to your IE or Firefox browser.

By installing this toolbar, you can enter search text in an IBM-specific search engine. If you use this to search for APARs, I strongly suggest selecting the Support Home icon before entering your APAR document number.

The toolbar is particularly useful if you encounter an SQL code that isn't in the codes manual. Such SQL code is likely introduced through an APAR, and thus not yet updated to the codes manual.

When searching for specific SQL code, it's best to be specific. For example, I searched on sqlcode -583. Entering "-583" returned 2,150,000 hits. Entering "583" returned 1,460 hits.  However, entering "sqlcode583" returned just six hits, and "SQL0853" returned only four. I also found that searching on "SQL0583" was the one way I could get a link to SQL code text as it appears in the manual. And by using SQLCODE as the prefix (SQLCODE583), I located an APAR containing this code. The point is, in any single situation, you may need to play around with search to get the result you're looking for.

Here are some other ways to get to APAR database:

1.  IBM Support Portal -- By using this particular URL, all DB2 for z/OS information is highlighted for you. You'll see it under the Notifications, Training, Flashes & Alerts and Overview Top Ten headings.

2. DB2 for z/OS Search (with a sample search result)

Of course, if you're interested in results beyond IBM.com, you can use a search engine like Google. Closed APARs generally appear on the public Internet -- e.g., I got 346 hits for "PM13552." Googling allows you to locate discussion forums and other blogs that may have discussed your SQL code.

Learn more about APARs, fixes and PTFs here.