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.

 

 

 

 

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.

Sizing DB2 Active Logs

Last week I wrote about ways to control DB2 system checkpoints. Now I'll look at some of the system parameters used to configure the size and number of active logs and explain how this can impact system checkpoints and the overall performance of your DB2 subsystem.

When the log data set is small, it fills up quickly, prompting an active log switch that generates a checkpoint. The general guideline for reducing the number of log switches is to define sufficient space to support 10 checkpoint intervals in the current active log. Last week's post provides a detailed explanation.

The capacity of the active log can significantly impact your application's performance. If the log is too small, DB2 may have to access data in an archive log during rollback, DB2 restart or recovery. And again, small logs can cause excessive system checkpoints that drive up CPU usage and slow all applications access that DB2 subsystem.

The characteristic of the active log data set is defined during installation through panel DSNTIPL. Parameters defined include:

* Number of logs -- controls the number of active log data sets you create.
* ARCHIVE LOG FREQ -- provides an estimate of how often active log data sets are copied to the archive log.
* UPDATE RATE -- provides an estimate of how many database changes (inserts, updates and deletes) you can expect per hour.
* DB2 installation clist DSNTINST -- uses UPDATE RATE and ARCHIVE LOG FREQ to calculate the data set size of each active log data set.

It's tough to come up with an update rate for a new system when you don’t know what the workload will be. Here are some guidelines to help you make an educated estimate for an initial install:

•    Small site changes data 1,800 times per hour, and the active log is offloaded once each day.
•    Medium site changes data 3,600 times per hour, and the active log is offloaded once each day.
•    Large site changes data 36,000 times per hour, and the active log is offloaded once each day.
•    Extra-large site changes data 72,000 times per hour, and the active log is offloaded once each day.

The following space calculation formula is used by the DB2 installation clist DSNTINST to size the active log data set based on your update rate and how often you want the archive log generated. This example is based on the large site with an update rate of 36,000 changes per hour. The system job DSNTINST assumes the average record length of the changed data is 400 bytes.

megabytes = (data change log record size * data change rate per hour  * hours in archive period)
MB = (400 * 36,000 * 24)
MB = 34,560,000
CYL = 34,560,000 / 720000
CYL = 48

Learn more about clist DSNTINST and DSNTIJIN in the DB2 10 for z/OS Installation and Migration Guide.

Controlling System Checkpoints

Recently I was asked about controlling the number of system checkpoints being taken by DB2. In this case the DBA would see system checkpoints taken every couple of minutes, and the logs fill up as a result.

The system parameters used to control system logging are defined during the install and can later be changed in the DSNZPARMs. In older DB2 versions, the LOGLOAD paramenter controlled the number of log records written. If you set this parameter to 100,000, DB2 would take a system checkpoint once 100,000 records had been written. (DB2 will pause activity and take a system checkpoint to establish a point of consistency.) The problem with setting system checkpoints to occur with every X number of records is that you never know when a checkpoint is coming or how busy the system will be when it does. Depending on system activity, a checkpoint could come every hour -- or every second.

With DB2 8, IBM made it easier to control and consistently predict when and how frequently system checkpoints would occur. CHKTYPE, CHKMIN and CHKFREQ replaced LOGLOAD as the key parameters.

The CHKTYPE (checkpoint type) subsystem parameter indicates whether the interval between log checkpoints is to be based on the number of written log records, the time between checkpoints, or both. The acceptable values are LOGRECS, MINUTES and BOTH. The default is MINUTES, which is what I like to use. For details, see checkpoint parameters in panel DSNTIPL1 in the DB210 Installation and Migration Guide.

The parameter you choose to control the frequency of checkpoints (CHKFREQ or CHKMINS) is determined by the checkpoint type you use, LOGRECS or MINUTES. With LOGRECS, the valid values are between 1,000 and 16,000,000. With MINUTES, the valid values are between 1 and 60.

When choosing a checkpoint frequency value, you must consider the trade-offs between the overhead needed for frequent subsystem checkpoints and the time needed to restart a DB2 subsystem after a termination without a quiesce. In a production environment, you should set the frequency between 2 and 5 minutes. In a testing or development environment, a frequency between 15 and 20 minutes is acceptable.

Next week I'll discuss how log size can impact system checkpoint.

Do you tune system checkpoints? What settings do you employ for production and development? Please post your thoughts in Comments.

ESCAPE to Find That Special Character

Have you ever found yourself trying to search a string by finding characters like percent (%) or underscore (_)? I'm occasionally asked about this, so I figured it would be a good topic for discussion.

When you're searching for some pattern within a string, you need to use the LIKE predicate. The format of the LIKE predicate is:

match-expression LIKE pattern-expression  ESCAPE escape-expression

"match-expression" is the string to be tested for the given pattern. "pattern-expression" contains the string you're searching for.

The underscore and percent characters in the pattern have special meanings (unless "escape-expression" is specified). The percent sign returns any number of characters; the underscore returns one character.

So, to find all names that start with "TR," I'd use "TR%" as the pattern-expression. To find all four character names that start with TR, I'd use "TR__" (TR followed by two underscores).

The escape-expression is needed when you're looking for a match-expression that contains either the percent or underscore character. The idea is to pick a character to be used to tell DB2 that the next character is a special character (% or _), and to not treat that next character as a pattern used for wild card matching.

I was asked how to find a string that ends in percent sign. This is how you would find all first names that end with "%". I’m using the plus sign (+) as the escape character. You should pick a character that wouldn't normally be contained in the string.

For example:

FIRST_NAME             LAST_NAME
SARAH%                    WHITE   
AN_DREW                 GARCIA  
CHRISTOPHER           BAKER   
MEGAN                     LEWIS   
STEPHANIE               HILL    
JACOB                      HILL    
KEVIN                       HERNANDEZ
BRIANNA                  LEE     
AMANDA                  ALLEN   
HANNAH                  ROWN   


SELECT FIRST_NAME, LAST_NAME                     
FROM PROD.EMPLOYEE                    
WHERE EMPID BETWEEN 1 and 1000                   
  AND STRIP(FIRST_NAME) LIKE '%+%' ESCAPE '+'

With the preceeding data, this query would return: SARAH%    WHITE

SELECT FIRST_NAME, LAST_NAME                     
FROM PROD.EMPLOYEE                    
WHERE EMPID BETWEEN 1 and 1000                   
  AND STRIP(FIRST_NAME) LIKE 'AN+_%' ESCAPE '+'

With this preceeding data, the query would return: AN_DREW                 GARCIA