Skip to content

DBA Holistic Scheduler Consciousness

No, this isn’t a tutorial on transcendental meditation, though it takes certain Zen-like qualities. Being good at any job means knowing what to look for – an artist sees one thing, a photographer another. There’s a vast amount of information available, and we filter for what is important to what we do.

Every production DB2 environment may include scheduled processes which cause heavy resource contention and/or heavy logging. The processes may be DB2 utilities: backup, reorg or runstats. The DBA may have a task to prunes tables, with resulting heavy locking and logging. The production application may have a variety of heavy updates or long-running scheduled SQL.

Scheduling may either be via cron or a third party tool such as BMC’s Control-M. Your database backups may be handled via a third party tool such as Symantec NetBackup. Your production application may have a homegrown scheduler. Some scheduled processes may be undocumented due to an administrator leaving the shop, or the shop inheriting an undocumented system.

Scheduled, high contention processes can enter into conflict because of:

  • Poor team communication. For example, you may have a NetBackup administrator, a Control-M admin, an application administrator, and cron processes, all managed by different people who don’t actively communicate changes across the enterprise. Or, people are laid off and do not transition their work to the new administrator.
  • Lack of process schedule review by administrators – not reviewing all active scheduler interfaces on a regular basis or before special database maintenance. This can easily happen when the DBA is on an impending deadline, and when the organization has a complex process schedule with several heterogenous front ends.
  • Poor visibility of schedule utilities, meaning:
    • The DBA is not allowed access to the scheduler interface.
    • DBA is unable to contact scheduler administrators.
    • Organizational spreadsheets are out of date, or there is no enterprise schedule documentation.
    • Too many schedulers are in use by your company.
    • Production application may be an undocumented black box, so developers and admins don’t know which processes are scheduled.
    • Schedules are so complex that they are not easily reconciled.

A good database administrator develops an awareness of the large number of scheduled processes running day and night that impact the database, inside her team’s purview but possibly cutting into the jurisdiction of other departments. To gain this awareness, the DBA in a large organization will require both technical skill and social/political savvy. As you can see, database processes can be initiated by application teams, by system administrators and by DBA’s, not all of whom necessarily are talking.

Lack of awareness of scheduled processes can cause particular pain when a production database DDL update is underway. Backup, reorg or runstats can stall any ALTER TABLE statements. Some ALTER TABLE statements require that a REORG be done afterwards to make the table accessible; if other utilities such as online backup are running, a REORG may not be possible until the other utility is complete.

It is important that the database administrator have a proactive personality and seek out all enterprise scheduler administrators. The DBA should reach out to other admins first, not wait for others to push the information to them.  You should push all your special database maintenance tasks and schedule changes to ALL system administrators whose work impacts your database. You should give yourself at least 48 hours before doing special database maintenance or implementing a new schedule in order to give other administrators a chance to give you feedback.

Documentation: If you do not have a unified scheduler your entire administration staff should have a single Wiki or other collaborative database for documenting database processes. That wiki or spreadsheet needs to be updated regularly. This can be difficult if your organizational culture does not reward documentation work. You have to be a leader.

Aggressive Research: If you have a historical performance monitoring system, study of your resulting graphs may identify heavy update tasks or even utility processes that you were unaware of. In a large organization, it is wise to be open to the possibility that there are “schedule zombies”: undocumented, unmaintained and (possibly) unnecessary processes. This is especially true if you have inherited an undocumented system; in that case the only way you may understand what is running behind the front end is by study of historical database performance through a historical performance monitoring system.

Suspend AND Re-Activate. When scheduled processes need to be suspended for special database maintenance, remember to reactivate the process afterward. Yes this is common sense, but it is very easy to forget. Every suspension of process needs to be accompanied by a reactivation of scheduled process in your implementation plan or hour by hour task list.

Good luck and may the Force be with you!

Post a Comment

Your email is never published nor shared. Required fields are marked *