Establishing Good Database Administration for DB2

Introduction

When we speak of database administration, we often refer to a set of tasks that is related to the setup, configuration and maintenance of a database and/or its software. In order to distinguish how we can achieve this in the most optimal way, we first have to clearly define the different tasks and roles within database administration.

Reflections

Segregation of duties

In DB2 for z/OS, a clear distinction is often made between a System DBA, who installs and configures the DB2 subsystem, and an Application DBA, who manages the data in relation to the application.

Although these roles have been available for a long time, they can be handled quite differently within organizations. The application and automation of utilities is often a responsibility that can move from one team to the other.

Moreover, many DB2 for LUW support organizations do not necessarily make this distinction in the same way.

In addition to the DBA organization, recent developments with regard to continuous delivery (DevOps) and security administration (SECADM) also require us to rethink the roles within the realm of database software.

Finally, the advent of the Hadoop Ecosystem and its specific roles also continuously sheds new light on the way we work.

Change, Configuration and Release Management

Next to the roles people need to take, we also need to look into the processes we need to apply in order to maintain our DB2 database system. This requires the application of best practices. One of them is a well-controlled change, configuration and release management:

  • Change Management refers to the changes that are being applied onto a given environment. Thanks to this good practice, we should know what has changed in a database implementation.
  • Configuration Management refers to a given configuration. Hence, we should keep track of where things have changed in order to allow us to fallback to a previous situation.
  • Release Management allows us to group changes and to validate them as a significant whole.

All three aspects of the best practice should be applied both on a system level and an application level. If one of the aspects is missing, a lot of valuable information can be lost.

In practice

The new roles

Traditional roles are being challenged in the new context we are living today. Nevertheless, we should cherish the lessons we learned from the past and fit the things we used to know within a modern context.

DB2 system administration

This is a team that is specialized in the operating system. It is responsible for the installation and the configuration of the overall subsystem (DB2 for z/OS) or the instance (DB2 for LUW). This team writes out generic guidelines for application implementations as long as it is related to the physical implementation.

Security support

Security should always be handled by a separate team. The segregation of all security administrators enforces the implementation of the other database roles. Abuse of a given authorization will be impossible if the person that is assigned to a task is not granted more authority than is minimally required.

DB2 application support

This team supports a database operational implementation for an application. In practice, this means that the application support team will provide required monitoring; setup utilities (REORG, RUNSTATS, IMAGE COPY …); and deploy database structures. 

All of their practices are based on the guidelines provided by the DB2 system administration team; howver, when required they can challenge the guidelines and discuss these with the DB2 system administration team to ensure that exceptions are documented or guidelines are updated.

Next, they follow up on the application performance and escalate bottlenecks and other performance issues to the appropriate teams. In a DevOps organization, this team is part of the project team for an application and can hence interact directly with the developers and the data design team.

The (DB2) data design team

This team supports data modeling within DB2. It decides upon the degree of normalization, the entities that are being used, and the naming convention of the logical layer. The leader of this team is a data architect, who makes sure the data is correctly maintained within the context of the overall enterprise. The other members of this team act as data stewards and business analysts, guaranteeing a consistent approach to the data modeling in all applications.

Unlike the DB2 application support, the data design team is business-oriented and cannot challenge the guidelines foreseen by the DB2 system administration team. Their designs should adhere to the standards as much as possible. Any issues should be handled by the DB2 application support team.

This role is not necessarily exclusively linked to DB2, as data modeling should be platform independent at first. However, as the actual implementation approaches, a certain degree of DB2-specific features should be taken into account as these are designed to facilitate common data design challenges.

Processes and Software that can be used

Please note that the products mentioned in this section are not meant for commercial purposes, but from a type perspective. If you feel a major tool within this context is missing, please feel free to contact the author and provide additional feedback.

System CCRM

The Change, Configuration and Release Management (CCRM) of the DB2 system administration team should include the rollout and configuration of the software components that are required for the DB2 implementation.

On z/OS, DB2 system CCRM is handled by the usage of SMP/E based deployment tools. Currently, IBM even offers a GUI to facilitate the deployment of software (z/OS management facility). If you make sure to deploy all changes the proper way, you will be able to keep track of all of your changes.

On Linux, one could make use of Ansible, as was described by Frederik Engelen in his presentation for IDUG in 2015. In order to streamline the installation and configuration deployment, other tools with similar functionality (Puppet, Chef …) can be combined with server provisioning software such as FAI, Cobbler or Spacewalk.

On Windows, the CCRM of a system configuration is often maintained by SSCM, but many alternatives exist.

Database CCRM

The Database CCRM is owned by the DB2 application support team, as they supervise the runtime of an application. Application CCRM includes the deployment of database structures, data maintenance procedures and data.

Ideally data structures and data maintenance procedures should be released alongside the application code. Many enterprises split the data structure deployment from the application code deployment, but that can lead to application failures. In order to avoid SQL errors in production, it is better to keep both together.

Database Structures

In order to deploy data structures, we should make use of a CCRM tool like the one used to deploy the application code. This deployment could be supported by tools that make use of ‘object comparison’ features.

Popular tools for database CCRM are Liquibase and FlyWayDB. Both make use of metadata stores to compare one input set to another one and generate DDL accordingly. These tools can be easily integrated in code deployment tools such as Maven and Jenkins or other proprietary SCM tools such as Rational Team Concert or ClearCase. These complement the change management options from Liquibase and FlywayDB with Configuration and Release Management.

IBM uses similar technology within Data Studio’s physical data model comparison, but unfortunately can not be integrated in batch as the services are only available interactively. Alternatively, you could allow interactive DDL generation and integration of the DDL in a more batch-oriented tool.

Unlike what you might expect, the above solution(s) could also apply to DB2 for z/OS. However, for DB2 for z/OS, a large ecosystem exists for platform specific tools. CA, IBM and BMC offer their variants of the object comparison in multiple flavors. These shouldn’t be used as standalone tools though, as object comparison on itself only supports change management and not configuration and release management.

In order to make use of the comparison or DDL deployment facilities properly, tools should have a batch interface that can be customized from within an SCM tool such as Changeman ZMF or CA Endevor.  A deployment package will then contain the final DDL which should be processed by the object comparison tool against the target database upon promotion/installation/shipment of the package to the target environment.

The previous DDL will then be available from the backout libraries, allowing you to fall back to your previous schema if necessary.

Data Maintenance Procedures

Data maintenance procedures are the procedures that keep the DB2 environment in good shape. Both on LUW and z/OS these procedures can be planned or automated to a certain degree. DB2 for z/OS will require more manual maintenance routines than DB2 LUW, but the principles remain the same.

As maintenance routines make use of the same platform as the application and operate in the same context, the deployment of maintenance routines should be aligned with application development.

In a DevOps organization, it is especially important that the complete set of maintenance routines is validated against the business needs for availability. It could be a significant problem if a backup was taken too early or if a REORG ran in the middle of very heavy batch. Although utilities are not supposed to influence the runtime in DB2, they can influence performance as they make use of the same CPUs.

Hence, the introduction of maintenance routines in the SCM of the application development really makes sense. Given that applications expect continuous delivery and the deployment page is expected to be very fast, the usage of an SCM to do proper maintenance shouldn’t pose many issues either.

Data

The final aspect of Database Administration that is often forgotten is the deployment of data; especially when it comes to the establishment of a test environment. In order to be able to test an application properly, relevant data should be available in relevant amounts. That data can be generated or derived/copied from production environments.

In order to make the best test environments, an application team should decide on what should be copied, how it should be copied and when. Depending on these three factors, two different solutions exist: one can clone and transform a complete or a subset of an instance or a subsystem; or one can choose to incrementally fill up databases following the demand of the end user.

Choosing a good strategy to copy your data becomes even more important when regulatory restrictions oblige you to mask your data when used environments other than production. Data is only as secure as its weakest link, and this is often a test environment. It is not hard to imagine that developers copy sensitive data into a DB2 Express-C database on their personal laptop, thereby corrupting your trusted zone.

Steve Thomas discussed the odds of cloning objects in DB2 for z/OS at the IDUG conference in Austin in May, 2016. He pointed out the concerns must be taken into account. Chris Eaton discussed a similar subject back in 2011 for DB2 LUW, and although software has evolved since then, his findings are still very relevant today and crucial for build a good test data management procedure.

Before you choose one or the other, consider carefully all aspects of your testing: what do you wish to test? Who has access to this test environment? Which type of data am I working with?

Conclusion

Database administration is not a simple task that can be reduced to the mere deployment of DDL. It requires knowledge of different aspects of the enterprise. All these qualities cannot be found within a single place in the organization, but should be collected from different sections that enforce each other.

DevOps should not be an excuse to eliminate the classic DBA role; on the contrary, it is an opportunity for an organization to spread the knowledge of their DB2 installation throughout the enterprise and to enforce the roles of those people that have in depth expertise on that domain.

 

 

1 Like
Recent Stories
SQL Tuning for Developers – Part 1

The Key To Mastering Db2 Locking On Row-Store Tables

Lessons Learned of Locking and Latches by Adrian Burke