Wednesday, July 27, 2016

The Tao of DB2 - Part 1: Achieving Balance and Understanding with DB2

This blog post begins a multi-part series of posts as we look in on a new DB2 DBA learning from a long-time DB2 DBA who is preparing to retire...

Our expert DB2 DBA sure has his work cut out for himself, but he understand the Tao of DB2 and is armed with database Te... He recognizes the Li of the application programs and how they work with DB2... He can remove tweaks that work against the Wu-Wei of Tzu_jan high performance DB2 applications.

"One could study for ten thousand years and travel the four corners of the globe and yet still be ignorant," said the expert DBA.

"I do not understand," said the young intern.

The lesson here is that DB2 is a large, complicated piece of systems software. No single person can fully understand everything it is and everything it can do without help. Knowing where to look for answers when you are stuck is much better than trying to memorize everything there is to know about DB2. Because just when you think you understand it fully, along comes a new use case or a novel way of using DB2 that you have yet to encounter. And new versions will come out regularly making the old way of doing things obsolete and introducing new and different techniques and features.

You must always be open to learning - never assume that you know everything!

Just then, the phone started ringing. The end-users were howling, the system was not responding. “What should I do?” screamed the intern. The old DBA pulled up his PDS member, typed SUB, and pressed ENTER

“Oh, has this happened before?” asked the intern. 

“How many times?” said the DBA.

The lesson here is that you can minimize conflicts through written standards and goals, and you can prepare for recurring problems with predefined scripts.

As the intern smiled, confident that he was learning the Tao of DB2, a frazzled programmer barged into the DBA's cubicle. “I followed the standards to the letter, but it doesn’t work,” spat the frustrated programmer. 

“The best thing about standards,” said the bemused DBA, “is that there are so many of them from which to choose.”

The lesson here is that even though it is advisable to create DB2 standards that apply to the most common situations, there will be times when you will be better off making exceptions to the standard. 

And the DBA sent his intern off to read some manuals...

Be sure to check in with us next time to follow the ongoing saga of the retiring DBA and his quest to teach his intern...

Tuesday, July 12, 2016

DB2 for z/OS Webinar: Who Did What to Which Data When?

Today's blog post is to let you know about an upcoming webinar (July 21, 2016) that I am conducting with CorreLog on the topic of database auditing for DB2 for z/OS. 

Unless you have been living under a rock these past few years you will have heard at least something about the many data breaches, hacks, and security issues that have been prevalent in the news. These issues have resulted in numerous industry and governmental regulations that organizations must understand and comply with. Data professionals, in particular, need to be vigilant in the tools and techniques that are available for better protecting their company’s data, and tracking those that access it. One of the most important of these techniques is database auditing. 

Database auditing provides a facility for tracking the use of database resources and authority. When auditing is enabled, each audited database operation produces an audit trail of information including information such as what database object was impacted, who performed the operation and when. The comprehensive audit trail of
database operations produced can be maintained over time to allow DBAs and auditors, as well as any authorized personnel, to perform in-depth analysis of access and modification patterns against data in the DBMS.

With this basic information on database auditing in mind, you should be able to readily see how auditing can help your organization answer questions like:

  • “Who accessed or changed critical data?” and 
  • “When was the data actually changed?” and perhaps even
  • “What was the old content prior to the change?” 

Your ability to answer such questions can make or break a compliance audit. Of course, these are just the high-level details. To delve more deeply into the important issues involving database auditing requires time and research... or you can attend our upcoming webinar and get up to speed quickly on the essentials you need to know!

The webinar, entitled Who Did What to Which Data When? will elaborate on database auditing details and issues such as the data breach trends and how costly they can be, an overview of pertinent regulations and their impact, and the various types of database auditing methods with their pros and cons... all with a specific focus on mainframe DB2. And there will also be an overview and demo of CorreLog's database auditing offering for DB2 for z/OS. 

You can learn all of this and more on Thursday, July 21, 2016, at 11:00 am Eastern Daylight Time (EDT).

But you have to register to attend, so be sure to click here to register.

Friday, July 08, 2016

Good Old Partitioned Table Space Confusion

Partitioned table spaces have been with us in the world of DB2 forever, but even as we embark on moving to Universal table spaces, there still exists some confusion "out there" in terms of the behavior of partitioning... 

For example, consider this question:

If a table is in a partitioned table space, say four partitions, for example, then to process the table in batch can I run four instances of the batch program in parallel, one against each partition?

This type of question exhibits some confusion about how partitioned table spaces work. First of all, let's assume that we are talking about classic partitioned table spaces and/or Universal range-partitioned table spaces. 

Given that assumption, yes, you can run four instances of a batch program in parallel if you so desire. Of course, that is not necessary to get DB2 use parallel tasks to read the data. The best approach is to BIND the program (package) specifying the DEGREE(ANY) parameter. Then DB2 will choose the degree of parallelism for the batch program. This is simpler than any alternative approach you might choose to code up because DB2 handles it all for you.

If you still wish to run four instances of the batch program you, of course, can. In that case you would probably want to BIND using DEGREE(1). In order for this to work the way I infer that you intend it to work, however, you may have to modify the program somewhat. I assume that you wish each job to process only against one of the four partitions. To accomplish this, you must provide some way for the program to identify and process only the data from one of the four partitions based on the partitioning key range specified in the partitioning index. 

For example, you might choose to input parameters to the batch run specifying the key range for that program to process. As long as the program adheres to that key range you should only process data from the one partition that holds that data.

Of course, that means that your programmers must be diligent in applying the key range. Perhaps you could set up 4 views that apply the key range for each partition and then use only those views in each program. But that means you will have multiple versions of the same program. You could also specify the range as an input variable and then use only one program. But that means you have to make sure that you are sending the proper range to the input variables.

So just go with the first approach and let DB2 do it for you when it makes sense...