Happy Holidays 2016

It is that time of year again. When we all take some time to pause and spend time with our family, friends and loved ones. We take time off of work to celebrate and reflect on the past year... and to welcome the new year.

No matter what holiday tradition you celebrate, I wish you an enjoyable holiday season. Happy holidays, that is!

And we'll meet back here again in January 2017 to talk more about DB2 stuff!

Happy Holidays!

The Tao of DB2 - Part 14: A Short Lesson on Wisdom

After a long day of dealing with problems the DBA and his intern were readying themselves to head home for the evening. The mentor glanced over at his trainee and saw that something was troubling him.

"You seem to be troubled," noted the DBA.

“I guess so," replied the intern. "We did a lot today, but a lot of our time was consumed answering what seemed to be the same, or at least similar, questions over and over. Surely it must be annoying to you to answer the same question multiple times a day?”

“Hmmm,” said the Taoist DBA, “how many times have I answered that?”

The lesson here is that every DBA must be ready to assist without wanting to bask in glory. Solving problems is more important than who caused them, and even who solved them.

"Remember always the reason we are here," replied the mentor.

"Not everybody has had the same access to the information that we as DBAs have. That can mean that sometimes you will have to explain things that you probably have explained many times before. This is not a bad thing. What is worse is to not even be asked!" said the DBA.

"Again, the words of Lao Tzu can help. He teaches us..."

"So I am a sage?" questioned the intern.

"That you have to ask tells you that no, you are not yet a sage," replied his mentor. "But keep learning and you may yet be able to act as a sage soon..."

The Tao of DB2 - Part 13: Constantly Learning More

It is time, once again, to look in on our intrepid young DBA as he continues to learn database administration as an intern to an older, wiser, but soon-to-be-retiring DBA.

The intern approached his mentor and said "You have taught me a lot in the past few months. I am more prepared now when it comes to supporting the developers, database/application performance management, making database changes, backup and recovery, and more. But surely there is more to learn?"

Impressed, his mentor shook his head and said "You are securely on the path to becoming a good DBA. Knowing what one does not know is as important as knowing what one does know!" 

He continued... "With that in mind, remember the Tao and the wise words of Lao Tzu, who said 'To know, yet to think that one does not know is best; not to know, yet to think that one knows will lead to difficulty.' "

Just then a developer came into their cubicle with his hair on fire shouting about not being able to access the test databases. "Oh, I’ve seen this problem before," said the intern. " All I have to do is start the database like before and…” 

“To a hammer all problems look like nails,” snapped his mentor. "Learn this lesson well! Do not rush to a solution, even when things may seem to be obvious. Take your time, learn all there is to know about the issue, and only then respond. Though you should rely on your past experience, do not force fit that experience to every problem."

"Hmmm..." said the intern, as he displayed the status of the test database to see find a table space that was in copy pending status. "It might be better to back that up than to just force start it."

"Yes,' sighed the mentor contentedly. "Every day is an opportunity to learn something new. Patience is always a virtue and humility is a quality that all DBAs would do well to exhibit."

And then the phone rang. The intern picked it up and listened as the person on the other end started to explain a WebSphere issue. His mentor listened intently to the intern and picked up on it not being a DB2 problem. Just as the intern was about to balk at helping his mentor stopped him and politely told the person on the phone to hold on for just a moment.

"But that is a WebSphere problem, not a database issue. Surely that is not my job?" balked the intern.

The mentor clucked and explained to his protege "When you say it is not your job, soon it will not be your job." 

The lesson here is that a good DBA must be a jack-of-all-trades. Expanding your knowledge and sphere of influence is always good. The more you know, the stronger you will become at your job and the more employable you will be in the long run.

"I feel like I have to know everything," complained the intern.

"Someone has to," laughed the Taoist DBA.

The job of database administration is complex and you cannot know everything. Learn as much as possible and always be willing to take on new challenges and learn from others. Understanding who is the expert in each technology at your company will help you to tackle problems in your own management discipline. Network with other DBAs, system programmers, developers, web masters, network administrators, and so on... both within your company and externally.

Happy Thanksgiving

Today, in the United States of America, we celebrate Thanksgiving by gathering together with our loved ones and giving thanks for all we have. This includes celebrations with food, traditionally a big turkey dinner with mashed potatoes and gravy, as we watch a parade and football games...

With this in mind, I give thanks for all of the reader's of my blog, wherever you may be, and wish you a very happy day!

The Tao of DB2 - Part 12: Dealing with Change

It is time, once again, to look in on our friends, the intern DBA and his mentor. After spending so much time on performance issues, the mentor decides to check on his apprentice and make sure that he is not overwhelmed.

“Well, now that you mention it, the users want to change the data type of several columns,” stated the intern.

“The only constant is change,” replied his mentor. “Remember the Tao; as Lao Tzu tells us through experience the sage becomes aware that all things change. We must be prepared to support many different types of change requests. Some will be simple, others will be more complex. Never the less, all changes require careful planning to implement.”

“I noticed that the ALTER statement can be used to change data types; that doesn’t seem to be all that hard to me?” queried the intern.

“Read that manual carefully,” countered his mentor. “Keep the words of Lao Tzu in mind; he tells us ‘The difficult and the easy complement each other.’”

“Hmmm… I see. Looks like I can switch the data type of a column as long as things stay within the same family of data types. Like character to character... or numeric to numeric. But if I have to change a numeric column to a character or something like that, it get complex very quickly.”

“Very astute,” replied the Taoist DBA.

“So I can make some of these changes relatively easily, but it looks like all I have to do for the others is to drop the table and re-create it specifying the new data type for the column they want to change from CHAR to DATE?” asked the intern.

“Yes, that is all,” replied the Taoist DBA, shaking his head sarcastically.

The lesson here is that dropping an object drops all related objects and authority. If you drop a table, all of its indexes are dropped. And all of the grants are removed. Not to mention the data itself... It is important to understand the connectedness of all DB2 objects and what it means when we change those objects. Without this understanding, catastrophes are inevitable.

An additional lesson to be learned is that changes may need to be undone; you must be ready. A DBA should be prepared to be able to undo any change that is implemented – and then redo it again. Seriously consider a change management tool that will keep track of changes for you and automate the process. Without such a tool you will need to manually track and store your DROP and CREATE statements somehow.

Realization dawning upon him, the intern mutters “Dealing with change seems like it will be a big part of my job.”

With a grin, the Taoist DBA replies, “You have no idea how true that is.”

The Tao of DB2 - Part 11: Calm Inaction: a Difficult Lesson to Learn

The young intern DBA and his mentor were developing training for the new version of DB2 when the intern became distracted by a hubbub just outside their cubicles.

"What is going on out there?" asked the intern, out loud, to nobody in particular.

"What?" replied his mentor. "I don't hear anything." Remember, he is a practitioner of the Tao and is adept at concentration and blocking out distractions.

The intern sticks his head outside of his cubicle and sees a lead programmer/analyst he has worked with in the past scurrying around from cubicle to cubicle. He also sees a VP and somebody he doesn't recognize talking heatedly in the corner.

"Something is going on," said the intern.

"Something is always going on," said his mentor. "Get back to work."

"But I overheard somebody saying that DB2 wasn't working. Maybe we should look into that?" asked the intern.

"Do you trust the alerts you set up to warn you when there are DB2 problems?" asked the mentor.

"Sure," he replied, "you helped me set them up and we modified them just last month. I'm sure they are working."

"When all around you is in flux, sip your coffee and contemplate the situation. Not every problem requires a solution. Recall the wise words of Lao Tzu..."

"But what if they come in here and demand a solution?" the intern asked.

"Then we'll give it to them," replied the Taoist DBA, somewhat merrily.

The intern peeked outside his cubicle once more and things seemed to be calmer. "I guess it wasn't a problem with DB2, after all," he shrugged.

"No... and that should not surprise you. Sometimes the proper course of action is to do nothing. Some 'problems' solve themselves... Once again, I offer you the wise words of Lao Tzu..."

The Tao of DB2 - Part 10: Dispelling a DB2 Performance Myth

Once again we join our intrepid duo – the seasoned DBA who is a practitioner of Tao and his young apprentice to whom he is trying to teach the way of DB2…

“I have to say,” uttered the young intern, “that there sure is a lot to learn about DB2. Especially when it comes to performance.”

“And perhaps much to unlearn, too,” murmured his mentor cryptically.

“What do you mean?”

“Well, I bet you’ve heard things like ‘DB2 is a system hog’ and ‘We only need such a big CPU to make sure that DB2 works’ haven’t you?” queried the DBA.

“Sure, but isn’t that true? “Doesn’t a DBMS like DB2 mean that we have to buy more hardware to support it?” questioned the intern.

“Only if you plan not to work,” replied his mentor curtly. “If you learn how it works and then use DB2 the way it is designed to be used then things will be very efficient! A well-tuned DB2 environment with properly coded programs will not require the constant addition of CPU power or memory.”

“Hmmmm… we have some work to do to change people’s attitude around here about DB2, don’t we?” asked the intern.

“A very astute observation!” countered his mentor. “We are the custodian of the DBMS. As such, it is our duty to teach those who wish to use it how to do so effectively and efficiently.”

Things that should be part of this task include:

• Setting up training for each new developer who has never coded SQL against a relational database. This must include guidance on SQL coding, the importance of indexes, and how DB2 access differs from a flat file system. IT also should include training on Stage 1 and Stage 2 predicates, indexable and nonindexable predicates, and the importance of coding joins properly.
• Furthermore, programmers need to be trained to code every predicate necessary into their WHERE clauses. Many performance problems are caused by reading every row (or more rows than are required) into the program and checking the data like you would a master file.
• And be sure to train the developers not to be shy about asking for an index to help out their queries.

“If we educate every DB2 user on how DB2 works and proper SQL coding techniques, then we can help to alleviate the misguided perception that DB2 is a hog!” said the DBA.

“That makes sense. As long as we use DB2 as it was meant to be used things should be efficient. I’m ready to dig in and help get out the good word…” said the intern.

“Perhaps there is hope for you yet…” whispered his mentor.

The Tao of DB2 - Part 9: The Five R's

The last time we checked in on our experienced DBA and his young intern the lesson was focused on when to REORG. The intern was sent away to learn all about Real Time Stats and RUNSTATS. Let's see how he is progressing...

"So, the Real Time Stats can be used to help me figure out when it is time to run a utility, like REORG, on my DB2 objects. There are a lot of different statistics and the manuals give some good guidance on which ones to look at and when they should trigger reorganization," offered the intern.

"Well done!" exclaimed the Taoist DBA. "You have grasped this concept quickly. But now, tell me, can you think about how this might tie in with anything else you know?"

"Hmmm..." thought the intern. "If a REORG changes the composition of the table space, then a RUNSTATS should be done to get the statistics that the optimizer uses to be accurate. And maybe it could make sense to REBIND any programs that access tables in that table space."

"Yes!" said the surprised DBA. "You are getting it. It is not enough to just learn each new thing that you happen upon, but to think about how it impacts everything else you already know. You are coming along nicely! So tell me about how these things should work together."

"Well, I guess there are a couple of different approaches we could take, but I think the best approach would be to REBIND our applications over time as the data changes. We'd monitor the RTS tables using the thresholds recommended in the manual..."

The expert interrupted his apprentice "...and you can tweak them over time as we learn more about how they change.

"Yes, that makes sense. And when we REORG we can follow that with a RUNSTATS..." said the apprentice.

"Or perhaps use in-lie stats to reduce the number of runs through the data. That can reduce CPU time," chimed in his teacher.

"So we are using RTS to keep track of changes to data volume and patterns. And then we'd follow that up with a REBIND process?" questioned the intern.

"Sure, but we don't want to REBIND everything, do we?" he queried.

"No, probably not. I can use the dependency information in the DB2 Catalog to find out which packages access or change data in the objects that were reorganized. Then we can just REBIND those" said the intern.

"Good thinking, but keep in mind that the DB2 Optimizer is not perfect, so we have to be ready in case we get degraded performance after rebinding. What do you think we can do there?" asked the mentor.

"Well, if we get any problems I can always try to code up a hint to get back to the way things used to work," said the intern. 

"Sounds like you're reading old documentation to me! Sure, sometimes a hint may be needed, but what about access path stability and using the PLANMGMT options of the BIND command? That way we can have backup versions of the access paths to go back to if performance suffers." 

"I vaguely recall hearing about that. I better read up on that," said the intern.

"Yes, you should," said the mentor, "And while you're at it, noodle on this. I call what we have been discussing here The Five R's of DB2 application performance. Here is a note card that steps through The Five R's:"

"OK, I see. We start with RTS, use them to REORG, make sure we run RUNSTATS after (or during) the REORG, and then REBIND any impacted packages. But what is Recheck? Is that a DB2 utility I don't know about?" asked the intern.

"No, no, no," cautioned his mentor. "That is referring to the manual process of checking the access paths to see what changed to make sure we don't get any surprises after rebinding. And then using PLANMGMT to control things while we investigate, tweak and try again."

"Well I have a lot of work ahead of me getting this all set up," said the intern.

"Yes, you'll have some work because we've been short-staffed. But I think you'll find a lot of this already set up and running. Please look into it all and tell me how things are going," replied the mentor, confident that his young apprentice was learning the way...

The Tao of DB2 - Part 8: REORG and Statistics

When last we looked in on our Taoist DBA and his young mentor they were dealing with performance issues. They were dealing with performance issues as they arose... reactively. The last question the intern had asked his mentor was a good one: "So is there more that I need to know about DB2 performance management?" 

Well, today the mentor is instructing his intern on how to be more proactive, instead of reactive.

"Today, let's talk about keeping our DB2 objects well ordered," said the mentor. "What if you were doing a home repair project and needed a specific size bolt? Would you rather look for it in the pile on the left or the tray on the right?"

"Obviously, the tray," said the intern.

"Then we must work on making our DB2 table spaces and indexes look more like the tray than the pile!" nodded the mentor.

"Oh, yes, I have read all about the REORG utility and I was thinking about creating a REORG job for every table space and index and just scheduling them to run every month," said the intern.

Shaking his head solemnly, his mentor said "Well, yes, that may be better than doing nothing. But again, maybe not. Do you really know that every object needs to be reorganized once a month? What if some get disorganized very quickly, like daily or weekly? Wouldn't it benefit your applications if those objects could be reorganized more frequently?"

"I suppose so," blinked the intern.

"And what about those that are almost never modified? Why do you want to reorganize them so frequently? Then there are those that change slowly, maybe you only need to reorganize them once or twice a year. If you just blindly reorganize everything at the same time you are wasting valuable mainframe CPU cycles... and that can be costly!" countered his mentor.

"Hmmm... So what should I do?" asked the intern.

"As with most complicated things, start by researching and making a plan. Understand each DB2 object and how it is accessed and modified. Is the amount of data growing? How fast? Basically, get an idea of the activity that is being performed on each object."

Things to consider include:

• How frequently does the data change in the object? 
• Changes? Rare; Variable? No = infrequent REORG
• Variable? Yes = more frequent REORG (compression?)
• How radical is the change?
• Is the data simply updated in place?
• Does it move?
• Are there gaps in the data?
• Is downtime possible? 
• Online vs. offline REORG
• How busy is the object in terms of access and modification during the time you want to reorganize it?

"Fortunately, DB2 gives us some help here!" replied his teacher.

"Oh, yes, I saw some of these things out in the DB2 Catalog... you know, like cluster ratio and near and far off distribution statistics. Right?" asked the intern.

"Ten years ago, you would have been right. And I give you credit for at least reading and learning about the statistics in the DB2 Catalog. But these days we should be using Real Time Statistics, or RTS. The DB2 Catalog stats are only modified when you run the RUNSTATS utility. The RTS are modified as their name implies, regularly in real-time as changes are made to the data," said the mentor. "Here is some documentation on RTS, take some time to understand the statistics and how you might use them to decide when you should reorganize stuff!"

"OK, thanks. I'll learn this stuff and be back to talk about what I should do after than," said the intern.

"Good plan!" said his mentor.

The Tao of DB2 - Part 7: Dealing with Performance Issues

The last time we checked in on our DBAs (the soon-to-retire mentor and his intern) the mentor was schooling his young intern on storage and data retention issues. But there is still much to learn, as our intern will soon find out!

"Soon you will get the chance to learn about performance tuning," said the mentor, as he nodded solemnly in his chair. As if on cue, one of the programmers came stumbling into the DBA's cubicle muttering "There's a problem with DB2!"

"Guilty until proven innocent again," mumbled the senior DBA as he rocked back in his chair and smiled. His apprentice though was running back and forth from his terminal to the developer’s area back to his phone and over to his monitor. Frustrated, the intern yelled over at his mentor “How can you be so calm? Things are falling apart!”

“Things are always falling apart and yet life goes on,” muttered the senior DBA under his breath. Not only did he have years of experience as a DBA but he was also a practitioner of Tao. As the apprentice continued running around like a chicken with its head cut off the Taoist DBA calmly glided his mouse across the top of his desk, clicked, and typed something on his keyboard. In a minute or two the apprentice walked into the senior DBA’s office and said, “Boy am I glad that is over. I don’t know what exactly I did to solve that problem, but I am exhausted.”

The senior DBA shook his head and pointed to a printout he had thumb tacked to his wall that read “Don’t Panic!” He showed the apprentice the script he had run and told him why it solved the problem. A good DBA is prepared to resolve issues he has seen before because he has must be a packrat with a good memory. “That means you must learn from every action you take,” said the senior DBA. “Running around like a crazy man when things are going wrong will never help. Remaining calm and taking things one step at a time will help. And always remember the situation and the steps you took to remedy it. Things have a way of repeating themselves.”

The senior DBA continued, “Skill and knowledge are required to be a successful DBA, but sometimes you need to master the art of getting out of your own way. You must learn how to act without forcing conclusions.”

Although learning from past experience is important, most performance problems are not solved on the first try; many performance problems require multiple things to be changed for results to be achieved. It is important, too, to remember the DB2 performance tuning hierarchy. Start with the SQL and application code, because that is where most of the problems reside. Move to the database structures and organization after that. And finally, if the problem has not been isolated yet, move on to the system and DB2 subsystem. Furthermore, tune one thing at a time and test between every change you make!

Just then, another visitor comes by stating that things are looking grim in production for the new system. "One of the programs is just taking forever to run! It was very efficient in the test system..." 

"Oh, I am sure it was," grinned the experienced DBA as he sent his intern off to check the production access paths. 

"Hey look, here is a table space scan!" shouted the intern. "Oh, that was there in test, so it can't be the problem, right" questioned the developer. The experienced DBA directed the intern to build a new index and rebind the program and everybody was happy again. 

The lesson here is that the DB2 optimizer formulates access paths at bind time based on the statistics that measure the amount of data and its make-up. The test system obviously had less data so a scan was not a problem... but more data in production caused performance issues! It is important to build effective SQL that encompasses as many of the “needs” of the application as possible; this eases performance tuning. And as data patterns change and DB2 evolves new features, rebinding the SQL can quickly adapt the program to the data and updates. 

The intern requested of his teacher, “Teach me all the mysteries of the DB2 Optimizer.” The Taoist DBA replied “A mystery is named such because it cannot be fully understood. Sometimes that which we cannot understand 100%  is still well-designed and required. As Heraclitus teaches us 'The unseen design of things is more harmonious than the seen.'"

The lesson here is that we can (and should) take the time to understand how the Optimizer works, but we must also understand that nobody (outside of IBM) fully understands every nuance of the optimizer. We can learn general rules, one of which is to entrust the optimizer to create more efficient access than we can write ourselves. Additionally, perform as much work as possible within the SQL and always remember: the DB2 code is more efficient than my/your code!

"There is sure a lot to learn here. Even though I've tried to take your lessons to the programmers they continue to blame DB2 for their errors,” lamented the intern. 

“A monkey chatters until you give it a banana. Then the monkey chatters again tomorrow,” said the experienced DBA. "But that is a bit unfair. Those programmers have a lot on their plate and cannot become as experienced as you will become with DB2. You must exercise patience and wisdom."

"So is there more that I need to know about DB2 performance management?" questioned the intern.

"Patience," replied his mentor.

Until next time...

The Tao of DB2 - Part 6: That Storage Stuff

When we last looked in on our DBAs (the soon-to-retire mentor and his intern) the intern was learning about backup and recovery and had put together a comprehensive backup plan for the new system when it would move to production. 

As the intern reclined in his chair viewing the magnificent backup plan with pride, his mentor glanced at it and admitted that the intern had done well. Just then, the team lead for the new project came into their cubicle to talk about the production turn over plans for later in the month.

The intern was a bit surprised that things were moving so quickly; hadn't they originally planned to move to production later in the year? "Well, yes," said the team leader, "but the team decided to implement in stages and we are moving to production sooner."

"Nice of you to let us know," chuckled the expert DBA as he looked at his terrified intern. "I guess we have some work to do, don't we?"

"Oh, it shouldn't be too bad," said the team leader, "just move the stuff we've been using in test over to production and we can take it from there."

The intern, knowing his mentor, ushered the team leader out of the cubicle before his mentor could react. 

As the intern came back into the cubicle he asked "How much storage space will be needed for this database?" 

"What amount of space is required to store sand?" replied his mentor. 

"How much sand... a grain or the whole beach?" asked the intern. 

"Exactly," replied his mentor. "You will need to schedule meetings with the development team and the SMEs because they will need to give us guidance on such things."

The lesson here is to plan for production sizing and database growth early in the development cycle. Understand how the business use of the database will cause data to expand or contract. Use DB2 features to your advantage. Specify sufficient free space to allow for growth. Utilize compression to accommodate larger objects. And be sure that you have sufficient disk in place before it is needed. You don't want to try to create a production database without the right amount of disk the night before things go live in production, do you?

If our DBA team had been working through a database design review schedule they would have encountered fewer surprises like this one.

As the intern went off to schedule some meetings he heard his mentor muttering "Remember always the wise words of the Tao. As Lao Tzu says 'Reduce the size and population of the state'." After scheduling the meetings, the intern asked what his mentor meant by what he had said.

"Always remember the lesson of today. Because we were not prepared early we have had to scramble to get the information that we need," said the mentor. "Think about what else we might need."

"Maybe I should have worked with those design reviews you told me about earlier?" mused the intern.

"Yes. And as you conduct your meetings, be sure to learn about the data retention requirements for your database. How long must data remain for each of the tables before it can be purged or archived. And know well the difference between the two!"

The lesson here is to prepare a data retention plan for each and every DB2 table in your DB2 subsystem. Less data in a table space can result in more efficient access; as old data gets archived there may be more relevant rows per page, improving read efficiency; table space scans will run faster; backup/recovery and REORG will run faster; and less disk space may be needed. Purging data that is no longer needed at all, or archiving stale, or outdated, data to cheaper storage can enhance the accuracy of database reporting and queries.  Of course, the plan for a table may be "No Archiving" but you need to understand the business and regulatory requirements for data retention for all of the data in the databases you manage.

The Tao of DB2 - Part 5: Build a Backup and Recovery Plan

We join our intern DBA and his mentor where we left them last week...

The intern is feeling good about himself. He has worked with the application team on the big new project designing the database structures, protecting the data using constraints and implementing proper security protocols, and assisting the development team on a daily basis. The intern was beginning to think, that perhaps, just maybe, he might be able to handle this job.

His mentor was proud of him, but he knew that the intern had much more to learn. "Keep in mind the word of Lao Tzu, who said "Lay plans for the accomplishment of the difficult before it becomes difficult; make something big by starting with something small"

"And be ready," said the Taoist DBA “for everything, because eventually everything will happen.” The intern wept as his mentor laid out all that was still required to do.

The lesson here is to be sure that you schedule appropriate backups for each and every table space... or a means of backing things up at the system level when that is appropriate. Understand the volatility of the data and the type of recovery that may been needed for each object. Learn about incremental backups and how they differ from full backups. Know how often backups may be needed: more than once-a-day, daily, weekly, monthly, etc. And be sure to test the recoverability of your databases... it is not enough to simply make the image copy backups without ever testing that they can be used for recovery purposes!

The intern learned and worked hard to develop an appropriate backup strategy for the new system. After a few weeks of diligent effort the intern nodded his head and told his mentor "This backup stuff is pretty easy, isn't it?"

"I’m outta here," was the sole reply of his mentor, and all that remained was his spinning chair and a cooling mug of coffee.

The lesson here is that nothing is easy and that there is always more to learn and do. A local recovery plan is vitally important, but you also need to develop a disaster recovery plan, keep it up-to-date and be sure to test it at least annually. Take nothing for granted!


Always remember that contingency planning and recovery from a disaster is a complex and time-consuming endeavor.

As the intern was working on how to integrate the databases for the new system into the disaster recovery plan, a developer he was working with on the project stopped by his cubicle and told him that he couldn't access one of the test tables.

The intern logged onto DB2I and displayed the test databases noticing that one of the table spaces was stopped. "Hmmm, how did that happen," muttered the intern DBA under his breath. "I think I can just restart this," he thought, "now what is that command?" As he rifled through the DB2 Command Guide his mentor stepped back into his cubicle and looked over his shoulder as he typed:

-START DATABASE(DBNAME1) SPACENAM(TSNAME2) ACCESS(FORCE)

Fortunately his mentor had arrived just in time to smack him in the head and stop him executing the command.

The lesson here is that force fitting a resolution to a problem is not a wise approach for the DB2 professional. Why is the object stopped? Uncover the reason behind the symptom and resolve it appropriately. Find out the state of the object and the reason code that caused it to be stopped. Only when you are sure that it can be safely started should you run a START command... better yet, resolve the issue by running a RECOVER or a CHECK on the object based on the situation.

Crisis averted, the intern went back to his recovery planning and his mentor went to get a fresh cup of coffee.

The Tao of DB2 - Part 4: Protect the Data!

When we last checked in on our intern DBA and his mentor the intern was assigned to help out on the new project. He worked with the development team and created the DDL to implement the needed database structures.

Today... a developer shows up in the intern's cube and tells him that the data looks wrong. The intern rubs his forehead and brings up the data in his table editor to see what the developer is talking about. And surely enough, the data looks bad.

The intern turns to his mentor and asks "Why is this data corrupt?" 

Remember that our expert DBA is a practitioner of Tao... so he says: "Heed the words of Lao Tzu who said 'When the consistency of the way is known, the mind is receptive to its states of change'..."   The intern looked back at his mentor with the look of a glazed donut... he did not understand.

And the Taoist DBA mutters "It is always the drunkard who knows not why he is slumped on the floor." 

Learning, the intern slowly realizes he has only himself to blame. The lesson here is to rely on the DBMS to enforce data integrity by using database constraints. When RI and CHECK constraints are defined to the database, then DB2 is receptive to changes in the state of the data and manages them appropriately. So the intern adds the appropriate constraints to the tables and helps the developers to understand the impact so that they code appropriately and do not duplicate the checks that DB2 will now be performing automatically.

After doing all of this the intern turns to his mentor and exclaims "With all of these constraints, my database will never get out of sync, will it!?!?" 

The Taoist DBA continued to balance his checkbook, looking for that missing penny.

The lesson here is that even the most meticulously cared for item can become corrupted. Even with database constraints sometimes our data will lack integrity (e.g.., Loading without enforcing constraints.) You must be prepared to get things re-synchronized. This means using the CHECK utility with the DISCARD option to remove data that does not match the constraint. By periodically running the CHECK utility we can review and ensure the validity of the data against the database's referential constraints.

Seeing his intern looking more confident the Taoist DBA decided it was time to teach the youngster humility; he knew his intern was not yet prepared for life as a DBA without a mentor. So he leaned over and whispered another Lao Tzu quote into his intern's ear: "It is easy to maintain a situation while it is still secure" and while he did this he stole the intern's lunch money from his pocket.

At about Noon the intern reached into his pocket to discover it empty. “I am hungry,” said the intern. “Yet I am not,” replied his mentor.

The lesson here is to understand the security and authorization needs of your DB2 databases and applications before you develop them, far in advance of moving to production. Build programs with knowledge of DB2’s abilities in terms of data access and protection. Know what security can be granted and revoked, how to do so, and what the impact will be of doing so. Using secondary authorization Ids can greatly simplify your DB2 security implementation. But understand all of the newer security aspects, too, such as multi-level security, SECADM, trusted contexts, and more. Don't forget about views and how they can help to implement security, too. And by all means, be sure to know your company's security personnel and cooperate with them for database protection. This is especially important if your company has moved security from DB2 to RACF or ACF2.

So the intern set about cleaning up the security on the test system. About 2 minutes after he started a developer came running into his cubicle acting like his hair was on fire. 

"The test system is hosed... nobody can access the tables!" screamed the developer.

"Hmmm..." said the intern, "I'm not sure why that should be the case. All I did was revoke the authority of this application DBA who quit last week. "Why can no one access their tables?” asked the intern of his mentor. 

"Can you touch what is not there?" asked the Taoist DBA.

The lesson here is to be very careful with DB2 security. When revoking an authorization GRANTed by a user having WITH GRANT OPTION, the REVOKE will cascade and also remove all the authority the user granted. Consider disallowing WITH GRANT OPTION, especially in production systems. And make sure you understand all of the newer options, such as the ability to REVOKE with the NOT INCLUDING DEPENDENT PRIVILEGES clause.

The intern has a mess to clean up trying to figure out who had access to what in order to get the test environment up and running again. And his mentor rocked back in his chair and grinned...

The Tao of DB2 - Part 3: The New Big Project

When last we left our protagonists, the intern had been sent away to learn more about tools and documentation, while his mentor planned for the new database and application that was in the works.

The DBA summoned his intern to his cubicle and told him, “Today I want you to work on implementing the new database for Project C that everybody here has been talking about for some time now. I’m sure you’ve heard about it, right”

“Uh, sure, the developers are all excited about working on it, but I don’t know anything about it. “Where do I begin,” asked the intern.

His mentor replied, “Begin at the beginning… and continue working until you reach the end.”

The lesson here is that every database must begin its life as a well-designed data model. Only then can it be physically designed and implemented with an understanding of the capabilities of DB2. An improperly designed DB2 database can cause horrible  performance problems.

From logical data model to physical implementation, all steps must be planned and well thought-out for success to follow. Errors in planning at the initial stages can result in disaster at later stages, requiring re-design and/or re-writing code.

And the intern went off to work with the data analysts and subject matter experts to get the data model. The intern had previously learned about the data modeling tool and understood what it could do. He used it to produce a quick set of DDL for the database.

“Does this DDL look right,” asked the intern of his mentor.

“Can one know what one does not know? The vessel is not empty, nor is it full. We may even know not where the vessel is…” the Taoist DBA rather cryptically muttered.

The lesson here is to define each column carefully. The logical data model does not specifically force any component of the physical database design, and there are some things the logical model will not provide, such as access patterns, locking details, etc. Furthermore, the DBA must verify the details of the logical model. Was care and precision taken when defining whether a value must be known? Without this knowledge the nullability of the columns cannot be accurately coded. What about column defaults? Do the domains match the data and the constraints of the database?

No, one cannot simply press a button and create accurate database DDL without thinking and understanding. So the intern went off to communicate further with the subject matter experts to get a better understanding of the requirements.

Better-informed, the intern begins to make some appropriate changes but then gets stumped. “I do not know which of these options to choose. Should I just use the defaults,” moaned the intern.

“I’m for whatever is right,” exclaimed his mentor. “Choose wisely and we will not have this conversation again. The Tao teaches us to ‘Use what is naturally useful!’”

The lesson here, of course, is to understand every option at your disposal before choosing any parameter. Use the one that best matches the needs of the database, the application, or the situation. Avoid DB2’s defaults. When you don’t know the options off the top of your head, consult the documentation!

Confused, the intern asked “But when should I partition the data?” His mentor slapped him and asked if it would have been beneficial for a partition to have existed between them. 

The lesson here is to partition when the amount of data would benefit from managing it in “pieces” or to access it in parallel. There is no “mythical” cutoff of pages/rows after which you must partition, other than size limitations (the largest segmented TS is 64 GB). And today, most table spaces should be Universal anyway.

The database DDL created and implemented the intern worked with the developers to get test data for the database. When the intern asked his mentor “Why will DB2 not allow SPACES in this DATE column,” the DBA smiled and tried to answer the intern in writing. But he wrote his answer with his finger instead of a pen.

The lesson here: No kludging! Use the features of DB2 as they were designed and meant to be used.  For example, use DATE, TIME, and TIMSTAMP for chronological data; use numeric data types for numbers, character data types for alphanumeric; use referential constraints (declarative RI) and CHECK constraints instead of using programs to enforce constraints; and so on…

Progress is being made… but tune in next time to continue following our intern as he works on his first new database project…

The Tao of DB2 - Part 2: Beyond the Manuals

When last we left our intern DBA his mentor, the experienced DBA, had sent him off to read the DB2 manuals. What a thankless task!

The intern was busily reading manuals on line while he downloaded the PDF versions to read later. But he was not gaining a lot of insight from the task. Although he was used to technical jargon, the manuals were difficult to consume and understand. He threw his hands up in frustration and went back to his mentor...

"These manuals seem to take the most mundane thing and draw them out into a long, complicated explanation. Am I missing something here?" opined the intern DBA.

"Aaaah," his mentor replied, "so you have been reading the manuals! Sometimes the manuals are not easy to understand until you get used to the terminology and the writing style. But do not panic!"

"So I am not crazy for thinking these are difficult to use to learn DB2?" asked the intern.

"Alas, you are not crazy for that reason, but we have not ruled out other reasons yet. The manuals are precise and are written to cover as many aspects of DB2 as possible. But don't give up... there is a lot of good information there... you just need to augment it with other material and learn by doing (with a little guidance)," stated the expert DBA in a soothing and convincing voice. "It is now time for your next lesson... tell me about the production customer system."

"But, but..." stammered the intern "I haven't worked on that system yet!"

"Yet you should know how to get some information, shouldn't you?"

The intern took a moment, rolled up his sleeves and remembered that there was valuable information in the DB2 Catalog... and he also remembered that his mentor had shown him how to get to the application documentation on the intranet...  His mentor smiled.

"The mouse in the trap squeals with pain,” explained the expert DBA, “not first examining the cheese for a spring. The lesson here is to know your data and monitor your systems. Understand how your applications work and what features of DB2 that they use. The more you know about the design and intent of your systems the better you will be at managing them."

And his mentor sent the intern away to learn more about the applications and the tools that were available to monitor and manage DB2 applications and systems.

This was important as the next step was to enlist the intern to work on the new database that he knew was about to be started.

So tune in next time as we watch our intern learn more about DB2 and database design...

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 and pressed . 

“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 following the on-going sage of the retiring DBA and his quest to teach his intern...