DB2 Tools corner: Prevent DB2 utilities from running with incorrect parameters

All right, who did this?

Learn about the latest feature in the IBM® DB2® Utilities Enhancement Tool for z/OS®, the Utility Monitor. The Utility Monitor helps you to establish and maintain company-wide best practices for controlling DB2 utilities. By using the Utility Monitor, you can control which DB2 utilities are run by whom and which utilities run with the syntax and parameters that you need, saving you valuable time and resources.

Share:

Jennifer Nelson (JNelson@rocketsoftware.com), Product Specialist and Product Manager, Rocket Software

Jennifer Nelson photoJennifer Nelson is a product specialist and product manager for several DB2 tools with Rocket Software, Inc. in Austin, Texas. She currently works as a business parter with IBM, assisting the technical sales team with potential and existing customers. She has 14 years of experience in the mainframe industry, working with DB2 and mainframe storage with large mainframe vendors. She has a B.A. in political science from the University of Texas at Austin.



16 September 2010

Also available in Chinese

Overview

"All right, who did this?"

Ever realize, after the fact, that someone ran a REORG with LOG NO? Or didn't run a REORG with KEEPDICTIONARY? Or ran a QUIESCE against business critical objects and caused an outage? Sometimes, running DB2 utilities with certain parameters or running utilities on specific objects can cause unforeseen problems later. And no one owns up to being the culprit. What if you could prevent these issues from happening in the first place? By using the new Utility Monitor from DB2 Utilities Enhancement Tool V2.1 in PTF UK60173, you can enforce the use or disuse of specific utility parameters or prevent utilities from running on specific objects. The Utility Monitor can even track the utility activity, keeping an audit trail of who executed which utility against which objects. Now you'll know "who did this."

DB2 Utilities Enhancement Tool provides a policy in which rules can be defined to determine which threads to block and cancel. With the new Utility Monitor, you can now define rules to determine which utilities to monitor, which syntax parameters to change, whether to restrict a utility from running, and whether to journal any Utility Monitor activity to DB2 tables. All of this can be accomplished from within one policy.

A brief look at the policy

When DB2 Utilities Enhancement Tool is installed and customized, a default policy is created in the SABPSAMP library. The policy is composed as an XML document. XML is a language that tags elements of the policy document that then allows DB2 Utilities Enhancement Tool to parse and understand the policy rules. The policy rules dictate how DB2 Utilities Enhancement Tool intercepts a DSNUTILB utility and the actions it must perform.

The policy includes elements, attributes, values, and hierarchical relationships between elements. While XML is beyond the scope of this article, the following definitions briefly introduce the XML components that are used within the policy:

ELEMENT
An element is a tag that defines a characteristic of a document. Elements that are nested within another element are called child elements.
ATTRIBUTE
An attribute is the name-value pair that is associated with an element. Attributes enhance the specification of an element
VALUE
An attribute has values, or valid options, much like a parameter has options, that can be used within an attribute.

By using XML tags within the policy, you can modify text, add text to the utility syntax statement, or remove text from it. You can substitute different values based on the presence of existing values. You can even fail a utility based on various values, such as object name, user ID, or the presence of some specific utility syntax. Messages that indicate that the syntax was changed are displayed in the job output. All of this activity can be tracked by logging rows to DB2 Utilities Enhancement Tool Journal tables for later review.

The following example shows a small portion of a complete policy. In this policy, the REORG TABLESPACE and LOAD utilities are being monitored for specific syntax parameters.

Example: Sample portion of a policy for use with the Utility Monitor
<!-- --------------------------------------------------------------- -->
<!-- DEFINE THE RULES FOR THE UTILITY MONITOR                 -->
<!-- --------------------------------------------------------------- -->

    <PRACTICE NAME="STANDARDS_1">
        <UTILITY NAME="REORG_TABLESPACE">
           <MONITOR>
             <SYNTAX VALUE="LOG NO" SUBSTITUTE="LOG YES"/>
             <SYNTAX ADD="KEEPDICTIONARY"/>
           </MONITOR>
        </UTILITY>
        <UTILITY NAME="LOAD">
           <MONITOR>
             <SYNTAX VALUE="LOG YES" SUBSTITUTE="LOG NO"/>
           </MONITOR>
        </UTILITY>
    </PRACTICE>
.
<DB2SYSTEM SSID="D91A" ACTION="MONITOR_UTILITY">
       <USE_PRACTICE NAME="STANDARDS_1"/>
        <INCLUDE>
          <RULE TABLESPACE="ABPDB1%.ABPQA2%"/>
        </INCLUDE>
</DB2SYSTEM>                              
.

How the Utility Monitor works

DB2 Utilities Enhancement Tool can monitor DB2 utility activity. When DB2 Utilities Enhancement Tool is active, it reads the policy rules into memory to determine which utilities to monitor and which actions to perform. As each DSNUTILB program is invoked and run on a DB2 subsystem, DB2 Utilities Enhancement Tool checks the utility that is being run against the policy rules to determine if it should be monitored. If the utility that is being run matches the criteria within the policy, the policy rules are enforced.

By using the sample policy in the previous example DB2 Utilities Enhancement Tool will perform the following actions on DB2 subsystem D91A:

  • The ACTION value set to "MONITOR_UTILITY" instructs DB2 Utilities Enhancement Tool to monitor utility syntax statements.
  • Because the INCLUDE rule defines a table space with the pattern ABPDB1%.ABPQA2%, the Utility Monitor will monitor the utility only if the object that is acted upon by the running DB2 utility matches that pattern. The INCLUDE rule provides a way for the policy administrator to qualify which utility jobs will be monitored.
  • If a utility is running against an object whose table space name matches the pattern that is specified in the INCLUDE rule, the Utility Monitor next evaluates the monitoring criteria that is specified in the PRACTICE element. The PRACTICE specification determines which utilities are to be monitored and which syntax rules to enforce for each utility.

The UTILITY element is another way to qualify which jobs are monitored. The NAME attribute specifies a particular utility command. Only utilities that match the NAME attribute in an active PRACTICE are eligible for monitoring by DB2 Utilities Enhancement Tool.

If the utility that is being run matches any of the UTILITY NAMEs within the PRACTICE rule, such as a REORG TABLESPACE, the Utility Monitor will perform the following actions, according to the previous example:

  • DB2 Utilities Enhancement Tool changes LOG NO to LOG YES, if LOG NO is specified in the utility syntax. If LOG NO is not specified, no action is taken.
  • KEEPDICTIONARY is added, if it is not already specified within the utility syntax.
  • The Utility Monitor actions are written to the DB2 Utilities Enhancement Tool Journal tables by default, which enables DBAs to view the actions that were performed on specific objects or specific utilities across time.

Why monitor DB2 utilities?

By activating the new Utility Monitor in PTF UK60173, you can now ensure that your DB2 utilities are running with the proper utility syntax. With the Utility Monitor, you can control not only which utilities are run, but who can run the different kinds of DB2 utilities and the parameters with which they must be run. You can also restrict a DB2 utility from running if it violates the rules that are defined within the policy. Not only will you be able to know "who did this," but you will be able to define policy rules to prevent unforeseen problems in the future. You can take complete control of your DB2 utilities.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=522314
ArticleTitle=DB2 Tools corner: Prevent DB2 utilities from running with incorrect parameters
publish-date=09162010