Skip navigation

Solutions for Db2

2 Posts authored by: Peter Plevka Employee
Share This:

With every new version of DB2 the utilities developed by IBM, BMC or other vendors get enhanced and more complex than before. On one side this is a good thing, because we users and the business services we manage need those enhancements to support high availability environments and to be able to do all kinds of fast and online tasks with our DB2 data. Many of those processes (COPY, REORG, RUNSTATS, REBUILD etc...) are scheduled within daily or weekly batchstreams. But often it is necessary to use these utilities adhoc on single objects to solve particular problem situations. Depending on your organization, often many users work with DB2 and need to run utilities for their objects. In the meanwhile the wealth of JCL options and SYSIN parms of such utilities are almost unmanageable. This means that many different users execute one and the same REORG with different parms and options. A situation, which is not the best for a centralized DBA organization, because the availability of business services and the resource consumption of such utilities can vary a lot depending on the setting of those parms and options. Standardization on enterprise wide JCL and SYSIN settings is required, but how ?

With utility profiles, a feature of  BMC CATALOG MANAGER for DB2 the central DBA team has the option to standardize on JCL and SYSIN parms and options for all users of DB2 utilities. BMC CATALOG MANAGER for DB2 is an administrative tool for DBA’s and application developers who need to interact fast and efficient with the DB2 catalog, and act upon the data returned, for example to generate DB2 utilities for 1 or more database objects. Each DB2 utilitie’s syntax (IBM or BMC) is fully supported by utility profies, which are defined once by the central DBA, and then are available for every user. The usage of utility profiles within BMC CATALOG MANAGER for DB2 assures that only tested and optimized JCL and SYSIN parms are used by endusers. Also it is not necessary, that each and every user need to know all of those parms in order to get to a valid result with his utility run. This concept increases the efficiency and quality in the area of enterprise wide utility processes.

A utility profile is a file that contains customized specifications for the syntax of a utility.

BMC CATALOG MANAGER for DB2 supports the following types of utility profiles:

     - Site profiles, which apply to all users

     - User profiles, which are available to those users who create them

 

After setting the appropriate values for all selected utility statements, on the Command line, type PROFILE SAVEAS profileID to save the values as a utility profile. You can later reuse this profileID whenever you want to generate JCL and SYSIN for that particular utility.

Share This:

Some years ago DB2 for z/OS V8 delivered multiple enhancements in the area of partitioned tables. You can define partitioned tables without a partitioned index, or secondary indexes (also known as NPI) as DPSI (Data partitioned secondary index). It is the table data that defines the physical partitioning limits for this new index type, and not, as in the past, the limit keys defined on the partitioned index. DPSIs are a good way to get rid of those huge NPIs.

But be aware !  A change of the physical database structure from NPI to DPSI must be planned under consideration of existing SQL accesses. Depending on the WHERE clauses within your SQL, response times can suffer significantly, if a REBIND of SQL accessing a DPSI produces a worse access path.  

Wouldn’t it be nice and comfortable to understand possible access path changes BEFORE actually changing the NPI to a DPSI ?

BMC SQL Performance for DB2 comes with a unique index advisor technology, which makes it possible for you to define so called ‚what if’ indexes, and to check the access path against those cloned indexes. All catalog statistics of the original index (NPI) are automatically migrated to the cloned ‚what if’ index (DPSI). That means, the access path, calculated by the DB2 optimizer reflects the real situation on your production system, and is meaningful, because the cloned index has been created by the 'what if' index function temporarly on the production system. Based on that analysis of that new, simulated access path, you can now plan to adjust your existing index design. This functionality means less risk for such kind of schema changes, because important access paths can be verified and adjusted before the actual production change is applied. 

Filter Blog

By date:
By tag: