Tuesday, September 13, 2016

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

Monday, August 22, 2016

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.

Monday, August 15, 2016

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.




Monday, August 08, 2016

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

Thursday, August 04, 2016

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…