Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted February 25, 2013

Managing the Database Administrator

By Lockwood Lyon

How The Team Should Operate

The most important goal for a tech support team is to give management the data they need to prioritize the team's work.

Typically management will work from a spreadsheet or project plan that includes task categories and team assignments. To do this well, management needs an important piece of information: the variability of the DBA's work. This usually translates to whether the tasks that need to be done have a fixed amount of effort and can be pre-scheduled or consist of a variable amount of work that unpredictably arrives. In other words, are the DBAs being reactive, active, or proactive?

Some work tends to have a fixed or well-known duration and can usually be easily assigned and scheduled. Other work tends to arrive at random, uncontrolled times; there may be no way to estimate the amount of effort required.

The DBA's manager assigns the fixed work (usually projects, meetings, etc.) to DBA resources at a level that allows sufficient additional available hours for important variable work, such as production issues and emergencies. In more mature environments, this may be implemented as a Gantt chart showing the next several months of resource consumption. This can be extremely valuable for estimating the availability of DBA resources for future projects, or for pre-planning some discretionary time that may be required.

This method of managing task and resource allocation dovetails nicely with the DBA efforts discussed earlier to implement automation and autonomics and define standard processes that can be easily outsourced. DBAs will work to convert variable, non-standard work into fixed, scheduled work, moving away from reactive tasks. Management will see fewer variable tasks and more fixed tasks, and more easily be able to assign DBA resources.

Team Assignments: The Generalists

Assigning tasks to DBAs is based upon task complexity, schedules and service level agreements, and the DBA's expertise. Some DBAs tend to be generalists. New DBAs start out this way, and only develop specialized in-depth knowledge later in their careers. For the generalist the best tasks are those that are defined with standardized procedures.

For example, consider database backup and recovery. Most IT shops use regularly scheduled standard backup procedures (e.g., DB2 image copies), although few have actually tested the recovery time of these objects and analyzed whether their backup procedures are sufficient (or necessary) for their recovery requirements. Tasks related to backup and recovery are a natural for the database generalist. Some possibilities include:

  • Create a standardized process (or verify that one exists) that ensures all database objects (tables and indexes) have current image copies and/or recovery procedures.
  • Test the recovery time for critical objects and document the results.
  • Meet with any development teams implementing new tables to ensure that upcoming backup and recovery requirements are captured and documented at an early stage.
  • Review new hardware and software options for data recovery. For example, DB2 allows image copies of indexes.

Another area where the generalist can be used is implementing autonomics. These are processes that are DBMS-based so that the system is self-healing and self-tuning. These range from simple scripts to complicated processes, and can be applied to applications, systems, or support software.

One excellent example in a DB2 z/OS environment is real-time statistics. DB2 dynamically generates these data distribution statistics. An example of autonomics would be to use real-time statistics during execution of the Reorg utility, where they may be queried and the results used to decide whether or not to execute the Reorg.

Another set of tasks good for the generalist would be to automate reactive or simple reporting processes. Implementing automation makes the DBA team more valuable, and IT management wants its knowledge workers doing tasks that add value. These might include detailed systems performance tuning, quality control, cost/benefit reviews of potential new applications and projects, and more. Management understands that a DBA spending time on trivial tasks represents a net loss of productivity.

The advantage of automation isn't merely speed; automating tasks helps move the DBA away from reactive tasks such as reporting and analysis toward more proactive functions.

Here's a typical list of processes many DBAs still manually perform that can be replaced by an automated reporting or data gathering process of some kind:

  • Executing an EXPLAIN process for SQL access path analysis
  • Generating performance reports such as System Management Facility (SMF) accounting and statistics reports
  • Verifying that new tables have columns with names and attributes that follow standard conventions and are compatible with the enterprise data model and data dictionary
  • Verifying that access to production data is properly controlled through the correct authority GRANTs
  • Monitoring application thread activity for deadlocks and timeouts
  • Reviewing console logs and DB2 address space logs for error messages or potential issues.

With automation in place, DBAs now can schedule data gathering and report generation for later analysis, or guide requestors to the appropriate screens, reports or jobs. This removes the DBA from the reactive rut and generates time for proactive tasks such as projects, architecture, planning, systems tuning, and more.

Along with choosing specific tasks to automate, you'll probably need to learn one or more automation tools or languages. REXX is an example of a popular language for online or batch access to DB2 data. There are many examples and ideas for automated processes in articles, presentations, and white papers.

Team Assignments: The Specialists

For the DBA specialist, more complex assignments are in order. Management will typically assign specialist tasks such as systems performance tuning or software migration to the DBA best equipped to handle it, or to one wishing to improve their skills with their more knowledgeable teammate as guide and mentor.

In more general terms, specialists are best assigned to tasks requiring multiple areas of expertise or management of a customer interface. Some examples include:

  • Being the lead DBA assigned as technical support to a team designing a new mission-critical application.
  • Collecting a suite of SQL related to a specific application or set of related tables and analyzing the set for possible performance enhancements such as additional indexes.
  • Creating a set of performance measures that can be used before and after major changes to the DBMS or an application in order to measure changes in resource usage.

Another good task for the specialist is creating dashboards: programs and processes that display performance indicators to the viewer.

In the DBMS environment, simple measures such as the number of tables, indexes, or columns moved to production, or the number of hours spent on a project concentrate on single, one-dimensional tasks. Dashboards gather and display data over time, allowing one to see trends and project capacity needs, as well as predict if issues or resource shortages may occur.

Data gathered for dashboards usually falls across several dimensions. Some examples are:

  • The User dimension. This includes application transaction rates, data throughput, and real and perceived transaction elapsed times.
  • The System dimension, which includes resource usage (CPU, DASD, etc.), availability of high-performance access paths (e.g., existence of indexes), and data distribution statistics such as clustering.
  • The DBA dimension. This area is specific to the DBA staff, and includes scheduling of utilities such as reorg and copy, along with some subjective items such as how well the DBA knows the tables, the SQL, and the application.

Summary

While there are many types and teams and gradations of expertise across the team, considering the DBA team members as generalists and specialists allows management to direct tasks where they will be executed appropriately, while giving each type of DBA work that is rewarding.

Source(s):

IBM     IBM Database Information Center     2012     

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp     

Wikipedia     Technical Support: Overview     2013     

http://en.wikipedia.org/wiki/Technical_support

http://en.wikipedia.org/wiki/Technical_support     

Wikipedia     Database Administrator: Definition     2013     

http://en.wikipedia.org/wiki/Database_administrator

http://en.wikipedia.org/wiki/Database_administrator     

See all articles by Lockwood Lyon



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.