Introduction

Over the years, there has been a lot of evolution in DB2 for z/OS : packages, compression, datasharing, triggers, large objects, unicode support, memory management, PBG tablespaces …

In the 90’s, focus was on scalability and availability. As for the RBA extension, most of the DB2 limits have all been multiplied by huge factors.

At the beginning of the 21st century, focus took direction of autonomic computing. Based on the increasing number of objects to manage, every company has been forced to automate processes in order to be more efficient and effective.

DB2 helped DBA’s with the delivery of very nice features : RTS, bufferpool simulation, virtual indexes… All can be used by technicians to analyze the situation and nobody dares to think working without them anymore.

Having some ingredients does not mean you are ready to cook!

However, though there have been a lot of improvements in collecting information, bringing all ingredients together is still time consuming: a real pain for all DBA’s.

For instance in V11, the management of the mapping table required for a REORG utility is considered “autonomic”. But we still have to manage the backup, the reorganization of tablespaces and/or indexspaces. Some parameters or options let us select objects more precisely but the implementation of a pseudo autonomic process is still expensive in terms of development time.

The performance management is also better detailed with all these new tables (predicat, virtual index, sort, …) but the complexity of the information is also more difficult to integrate. DBAs surely want to get clear advice to increase their access paths quality.

“All you need” is to Collect, Transform and Build …

Now that you analyzed your issue, just need to implement it…

Collect the required data. Ever wanted to collect the data from a BSDS ? Ever wanted to collect the output of DB2 commands? Ever wanted to collect data from all your configuration as a whole ? Of course DB2TEP and SPUFI are your friends and you are not afraid to code some hundreds lines of REXX to interpret the data you need.  Readable ? Maintainable ? Flexible ?  Not really, isn’t it ?

Transform data into information. Who never got worked up trying to extract a specific value from a DSN message ? Who never got worked up trying to calculate with numbers shortened once in K, once in M, once not ? Who never got worked up with dates and timestamps displayed using an unlimited number of formats ? Of course, REXX, JCLs and smart programs like DFSORT have no secret for you.  Hope there will be no changes to apply when you will be out of office,  isn’t it ?

Build your report. Now that you got all ingredients, just need to mix them.   Again, how easy can you  use output of a collect action as input to another one ?  Ever tried to extract object’s ids from a DSN message, trigger a utility on them and analyze the utility output in order to filter the results ? And when you finally transferred all you need to bring it together in a nice report, who never faced the boss’ requirement : shouldn’t we also take into account information from this subsystem ? Back to      square one L.

As you can see, all those processes are not complex; they are complicated.

          Click4DB : Your assistant from issue to delivery

DB2 contains a lot of source of information, most of them need transformation to be understandable and usable in a decision process. You also need to present or exchange results in standard format with colleagues, customers.

Click4DB software integrates all these concepts with the following guidelines :

• Make the job in a minimum of clicks
• Provide professional outputs without extra manipulation
• Manage independently 1, 2 or any number of DB2’s without code modification

With Click4DB, complicated processes are drastically reduced to a simple “mouse click”.

Concretely : how to find the best index to solve my performance query problem ?

Many performance problem can be solved with virtual indexes. Do you use it frequently?

Usually, you have the choice between multiple columns and how to order them. The candidate columns can be determined from the ‘WHERE’, ‘GROUP BY’ and/or ‘ORDER BY’ clause.

Suppose that you want to evaluate all potential indexes based on maximum 5 candidate columns: you will have about 325 combinations of virtual indexes to generate (this represent more than 8.000 fields to insert in DSN_VIRTUAL_INDEX). Insert all the information in DSN_VIRTUAL_INDEX via ISPF PANEL or SPUFI is a time consuming task.

Let’s see how Click4DB make this process “autonomic” :

 www.youtube.com/embed/1mow_c3DANk

Remark : this scenario is based on the IDUG presentation ‘Tuning with virtual indexes a Brute-Force-Approach ‘ by Rolf Drees.

How does Click4DB differentiate from other tools?

In Click4DB you will find more than a hundred scenarios to quickly and easily achieve many of the classics DBA tasks. Our major sources of inspiration :

• Our DB2 experience
• Major customer pains
• Your intervention on forum or during DB2 events
• New DB2 features

All domains of applicative DBA tasks or system engineer tasks are covered by Click4DB. We encapsulate all the processes of collecting information, transform them and present the results. You have only to input some variables values to execute one of our ‘autonomic process’.

Should you want to compute the optimal ratio for a global-bufferpool based on datasharing health check formula ? Yes Click4DB can :

Should you want to simulate the impact of a new active log on your DB2 recovery process ? Yes, Click4DB can:

Should you want to visualize the update activity by one (or multiple DB2 in case of datasharing) on a tablespace based on SYSLGRNX information? Yes, Click4DB can:

Should you want to know all the active IFCID’s on 3 DB2’s in one click ? Yes, Click4DB can:

We can continue this list with more than 100 items, but we know that your time is limited (the full list is available on demand at info@dbizners.com).

Summary

This article showed a small subset of Click4DB capabilities. As explained, Click4DB’s concept has been designed so that domains of application are unlimited.  Any suggested solution you may read on IBM’s DB2 books, Redbooks, IDUG’s presentation, Technical blogs can be implemented within Click4DB in a very short time… provided it is not already in there !  All our processes are designed to spare time and ease your daily work.

What could you do now ?

Visit our website :

 http://www.dbizners.com/

Ask for a free demo

Ask for a recovery or a performance audit to figurate the power of Click4DB. All the deliverables are based on existing scenarios. Scenarios are flexible and able to comply to the specific rules of your company.

Get a 3 months free license :  submit us a DB2 “autonomic process” to realize Click4DB’s capabilities. Should your idea be selected as of general interest (and not already present in Click4DB of course), then you will get a free license!

Follow us on Linkedlin : https://www.linkedin.com/company/12953943/

About the authors : Alain Pary & Bernard Jabas

Since 1990, we worked with DB2, we have experienced the DB2 versions from 2.3 to 12. We worked on various DB2 related projects : dbrm to packages conversion, datasharing implementation, utilities automation, security externalization .... We participated to community activities like national GSE and IDUG as speaker or follower.

Bernard Jabas & Alain Pary

DBIZNERS Co-Founders