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.

No comments: