Monitoring Configuration Changes in Your Environment

It has always been very important to understand when any changes in your database's configuration are made. Change control processes have been a central part of most production environments (and many development and QA environments, too) for a long time. However, as companies have outsourced some or all of their DBAs and have implemented DevOps we have been forced to relinquish control to external DBAs or even developers.

That said, it's still extremely important to have control over your environment and awareness of what changes have been made and when these changes occurred. Some changes can have huge consequences, and new DBAs or developers often do not have the experience to know what effects the changes they are making will have.

This article presents a methodology for monitoring your database's configuration, and how you can detect when changes have been made.

 

What to Monitor

Looking at a database, there are many areas where a configuration change can have significant effects. Here are a list of areas that you may want to monitor for changes:

  • Parameters External to a Database
    • System Configuration Parameters (i.e., Linux/UNIX kernel parameters, network configuration, ioo and/or no performance options)
    • DB2 Registry Variables
    • Database Manager Configuration Parameters
    • Database Configuration Parameters

  • Database Objects
    • Bufferpools (size, options)
    • Tablespaces (performance parameters, assigned bufferpool)
    • Database Objects (existing of objects and their validity) 
    • Database Authorities
    • Object Authorities

You might consider some of these (like database objects) to fall outside of configuration management, but knowing that new objects were created, removed or invalidated can be very helpful in maintaining a healthy database.


Options for Monitoring Changes

There are three methods that you can use to collect and track configuration information:

  1. Write configuration information to files
  2. Store configuration information in a database
  3. Use an existing configuration management tool.

Each method has advantages and disadvantages; there is not a perfect solution for doing this.

 

Tracking Configuration Details with Flat Files

To use flat files, you would copy or extract all configuration information to a series of text files, and use version control software like `rcs`, Subversion or `git` to track the changes to the files. Version control software is very efficient at finding changes to configuration files, so the workflow is not particularly complicated.

For example, here are the steps necessary for tracking the database manager configuration and SAMPLE database configuration, using `git` to track changes in the environment:

  1. Generate a series of files containing configuration information.

    $ mkdir cfgwatch && cd cfgwatch
    $ db2 "get dbm cfg" > ${DB2INSTANCE}.dbmcfg.txt
    $ db2 "connect to sample"
    $ db2 "get db cfg for SAMPLE show detail" \
    > ${DB2INSTANCE}.sample.dbcfg.txt

    Assuming the name of the DB2 instance is `db2inst1`, these steps will create a directory called `cfgwatch` and will create 2 files, `db2inst1.dbmcfg.txt` and `db2inst1.sample.dbcfg.txt` in the directory. The step of connecting to the SAMPLE database is required in order to be able to use the `show detail` option which provides the configuration parameter values that are both in-memory and on disk.

  2. Perform the initial check in to serve as the baseline for your environment's configuration.

    $ git init
    $ git add .
    $ git commit -a -m "Initial commit"

  3. This step initialized a new local git repository and then commits the initial versions of the configuration files into the repository. This also assumes that you're familiar with how to use `git` and have already done the required basic configuration. 

  4. On a regular basis, re-generate the files containing the configuration information:

    $ cd cfgwatch
    $ db2 "get dbm cfg" > ${DB2INSTANCE}.dbmcfg.txt
    $ db2 "connect to sample"
    $ db2 "get db cfg for SAMPLE show detail" \
    > ${DB2INSTANCE}.sample.dbcfg.txt
    These steps overwrite the files in the `cfgwatch` directory, but this is not a problem because the files have already been checked in to the git repository.

  5. Use the `git diff` command to find any differences:

    idug-cc-chgmgt-fig1.png
    Screenshot showing output from the 'git diff' command

    If you have a terminal that supports color, it is very easy to see what changes have occurred – with previous values shown in red and the changed values show in green.

  6. Check any changes in the configuration files in to the repository:

    $ git commit -a -m "Description of configuration changes ..."

 

If this process were to be automated, step 3, step 4 and possibly step 5 would be run on a recurring basis and would provide some kind of notification (perhaps via email) if any changes are detected.

Using text files and a version control system present 2 primary issues. The first issue is ensuring that the files are generated with data in the same order every time to ensure that the order of lines in the configuration files do not show up as changes in the configuration. The examples above with the database manager and database configuration will not exhibit this problem, but if you're tracking the results of SQL statements or DDL using `db2look`, this can become a large issue.

The other issue that you will face is deciding when to "commit" changes to the version control repository. The changes can either be committed immediately after getting the new configuration, or you may wish to review changes and decide whether they are "good" before committing them to the repository.


Tracking Configuration Details in your Database

Tracking configuration information using a database seems like it could be fairly complicated, but as IBM has added administrative views to DB2 it is becoming easier. If you can combine administrative views like SYSIBMADM.DBCFG with temporal tables, it's a little easier to imagine how you can track configuration history.

As with the example in the previous section, I'll demonstrate a workflow that can be used to track changes in the database.

  1. Create tables to track the data in `SYSIBMADM.DBCFG`, using system temporal time and versioning to handle the changes:

    CREATE TABLE cfgwatch.dbcfg
    LIKE sysibmadm.dbcfg;

    -- Add the columns necessary for temporal processing
    ALTER TABLE cfgwatch.dbcfg
    ADD system_begin timestamp(12) not null
    GENERATED ALWAYS AS row begin
    ADD system_end timestamp(12) not null
    GENERATED ALWAYS AS row end
    ADD trans_start timestamp(12)
    GENERATED ALWAYS AS transaction start id;

    -- Add the system_time period to the table
    ALTER TABLE cfgwatch.dbcfg
    ADD PERIOD system_time (system_begin, system_end);

    -- Create the history table that DB2 will use when versioning rows
    CREATE TABLE cfgwatch.dbcfg_history
    LIKE cfgwatch.dbcfg;

    -- Enable versioning on the table
    ALTER TABLE cfgwatch.dbcfg
    ADD VERSIONING
    USE HISTORY TABLE cfgwatch.dbcfg_history;
  2. Use MERGE to populate/update data in the table from the current configuration:

    MERGE INTO cfgwatch.dbcfg w
    USING sysibmadm.dbcfg c
    ON (w.dbpartitionnum = c.dbpartitionnum
    AND w.member = c.member
    AND w.name = c.name)
    WHEN MATCHED AND (
    w.value <> c.value
    OR w.value_flags <> c.value_flags
    OR w.deferred_value <> c.deferred_value
    OR w.deferred_value_flags <> c.deferred_value_flags)
    THEN UPDATE SET (value, value_flags,
    deferred_value, deferred_value_flags) =
    (c.value, c.value_flags,
    c.deferred_value, c.deferred_value_flags)
    WHEN NOT MATCHED
    THEN INSERT (name, value, value_flags, deferred_value,
    deferred_value_flags, datatype,
    dbpartitionnum, member)
    VALUES (c.name, c.value, c.value_flags,
    c.deferred_value,
    c.deferred_value_flags, c.datatype,
    c.dbpartitionnum, c.member);

    This statement will perform both the initial population and can be run each time you are ready to get a new snapshot to check for changes. If there are no changes, the statement will return a SQL0100W warning because no rows were inserted or updated.

  3. To check if there are any changes in the configuration, you can run one or more of the following queries:

    -- 
    -- Quick check to see if there are any changes; adjust the time
    -- offset from '1 day' to an appropriate value based on how
    -- frequently you execute the MERGE statement.
    --
    SELECT
    count(*)
    FROM
    cfgwatch.dbcfg_history
    WHERE
    system_end > CURRENT TIMESTAMP - 1 day;

    --
    -- Get details for the parameters that have changed
    --
    SELECT
    h.value AS previous_value,
    c.value AS current_value,
    h.system_end AS change_detected_ts
    FROM
    cfgwatch.dbcfg c
    INNER JOIN cfgwatch.dbcfg_history h
    ON c.dbpartitionnum = h.dbpartitionnum
    AND c.member = h.member
    AND c.name = h.name
    WHERE
    h.system_end > CURRENT TIMESTAMP - 1 day;

    Storing the data in the the database and using DB2's temporal features may make this solution look better than having to use text files and a version control system. For example, one of the nice features of temporal tables in DB2 is that you can see what a table's data looked like as of a specific point in time:

    -- See what the configuration was as of a specific time
    SELECT
    name,
    value
    FROM
    cfgwatch.dbcfg
    FOR SYSTEM_TIME AS OF '2016-12-01-02.25.59.999999';


However, there are some fairly serious challenges with this solution:

  • Certain system configuration items are not queryable directly in the database.
  • Every configuration item will require unique `CREATE TABLE` statements, `MERGE` statements and unique queries used to determine what has changed. This may require a significant amount of development and testing.
  • You will eventually need to tune query performance as the volume of data grows in the history tables.

If you are using DB2 LUW, there is one more alternative to track configuration changes inside your database. DB2 LUW v10.1 introduced a new type of event monitor, called a change history event monitor, that can track all kinds of configuraiton changes in your database, including changes to the database and database manager configuration parameters and registry variables.  When it is active, the event monitor writes data to a series of tables in your database that you can query to find what changes have occurred to your configuration and when.

 

Tracking Configuration using a Tool

Surprisingly, there are not a lot of existing tools that are available for tracking your DB2 configuration.

IBM Data Server Manager (DSM) 2.1 provides the ability to track and monitor your database's configuration, and is compatible with both DB2 on Linux, UNIX and Windows (LUW) as well as DB2 for z/OS databases. This feature is not included in the free Base edition of DSM, so you'll need to have an Advanced edition of DB2 for LUW or either the Administration Solution Pack or DB2 Connect Advanced Edition to track changes on DB2 for z/OS.

There are other tool options; DBI Software is notable here: its tool, Brother-Panther®, keeps track of your database's configuration and ties this data to performance trends as well. Other vendors may also include configuration change tracking in their performance monitoring tools as well.


Conclusion

Maintaining awareness of your databases' configurations is critical to being able to understand when and why problems occur in your environment. Changes in who is responsible for managing databases (whether it's outsourced DBAs or developers) make this knowledge even more critical. Using any of the methods presented in this article should help you maintain control over your environment's configuration and keep your databases stable and performing well.

 


Updated December 9, 2016, to recognize the change history event monitor and reduce vendor-specific solution details. 

1 Like
Recent Stories
Scenarios for IBM z Systems-centric Data Lake Implementations

5 Things to Know about Avoiding a Data Swamp with a Data Lake

Can You Drown in a Data Lake?