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…

Monday, August 01, 2016

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