Comparing Two Db2 Systems

Sometimes configuration needs to be kept in sync between two or more Db2 systems. There are a variety of reasons – sometimes this is for keeping two HADR servers in sync, and other times it may be for keeping a dev, QA, or Staging system in sync with production. In any case, having an idea of what needs to be in sync and what doesn’t can be complicated. The focus here is at the system level. This post does not dig into comparison of logical objects in the database such as tables and indexes.

Issues Specific to HADR

HADR synchronizes any logged data. Explicit changes to all kinds of structures, including table spaces, are therefore replicated automatically. Changes made to the database configuration and any levels of configuration at the instance level are NOT automatically done on the standbys. Additionally, changes made to buffer pools by STMM are not replicated to the standby. This makes sense when you think about the nature of HADR and some of the non-standard configurations that are sometimes used for HADR. I often think of HADR as two identical servers. but HADR is occasionally used for replication on the same server or sometimes one HADR standby serves as standby for a large number of Db2 databases on different servers. To accommodate configurations like this, it is necessary to limit what is copied.

Buffer Pools

It therefore rests on the DBA to ensure that the database configuration, database manager configuration, Db2 registry, file systems, and buffer pools remain in sync. The easiest way to do most of this is simply to incorporate changes to any of these areas across all servers in an HADR cluster in your everyday thinking and processes. The exception to that may be the buffer pools. Often with STMM, Db2 is constantly tuning the buffer pools for optimal performance. DBAs often don’t even know about these changes. It is usually not worth removing bufferpools from STMM just for the sake of consistency on failover. Instead, the easy way to handle this is to regularly issue alter buffer pool statements to push the bufferpool sizes on the standby to match the current sizes on the primary. Issuing the ALTER BUFFERPOOL statement is logged and is therefore replicated to all standbys. You can maintain the automatic setting, even while specifying a value with syntax like this:

db2 alter bufferpool BUFF32K size 10000 automatic

Specifying both a size and the AUTOMATIC keyword essentially tells Db2 to go ahead and tune the buffer pool, but to start with the value specified. Assuming that the memory isn’t needed for other memory areas and dbmemthresh (sp?) is set to the default of 100, Db2 is unlikely to decrease the buffer pool size.

This explicit buffer pool sizing is easy to script.

Database and Database Manager Configuration

Changes to the database configuration and other configuration areas are not covered by HADR. This includes automatic tuning changes made. If our standby system is identically sized, and we want optimal performance on failover, we manually need to keep changes in these areas in sync.

Comparing HADR and Non-HADR Environments

Another thing to keep in mind with HADR is that there are some parameters that are required for HADR databases and not for non-HADR databases. Often HADR is in production. It is occasionally in one non-production environment, but rarely in all non-production environments. The most obvious examples of this are:

• BLOCKNONLOGGED – should always be set in HADR environments, rarely in non-HADR environments
• LOGINDEXBUILD – should always be set in HADR environments, rarely in non-HADR environments
• INDEXREC – should be set to RESTART, but can be set to other values in non-HADR environments
• HADR_SPOOL_LIMIT – may be set on HADR servers, but isn’t usually in non-HADR environments.

There are a few other parameters in the Db2 registry, such as DB2_HADR_ROS, DB2_HADR_SOSNDBUF, and DB2_HADR_SORCVBUF that may be set for HADR environments and not for non-HADR environments.

When comparing HADR and non-HADR environments, we may want to note the differences, but there is no need to sync up the parameter settings.

Issues Specific to Non-Production Comparisons to Production

Sizing is often a major difference in between production and non-production environments. Often a production server is the beefiest server anyone can possibly justify, while non-production servers may just be whatever spare hardware is lying around. Non-production, therefore, often has less memory, fewer CPUs, and less disk. This obiously necestates differences in areas and parameters that might normally be part of a system comparison. This affects the filesystem and tablespace details if the disk size is different. If memory and CPUs are different, then there are a number of parameters in the database manager and database configuration that SHOULD be different, along with buffer pool sizing. Part of these differences may be less noticible if STMM is in use, as many of the parameters in question may be automatically tuned.

General Areas to Compare

Db2 Registry

The Db2 registry variables are set at the instance level. The db2set command is used to set or view them. Parameters in this area can have a profound or a minor impact on how Db2 functions. Ensuring they match can be critical not just to performance, but to base functionality. I’ve seen an HADR standby that did not have DB2COMM set, and therefore on failover, nothing would be able to connect to the database on the standby. Parameters here have a vast array of purposes from controlling how locking works, tweaking optimization, or even trying new features that are not considered ready for production.

DBM CFG

The database manager configuration also holds a vast array of different kinds of parameters. Some may be required to be in line for things to function properly on a non-production system or on an HADR standby. A few may need to be sized differently for a smaller system as compared to a same-size system.

DB CFG

This is the configuration area where sizing differences may make for the most significant differences in parameter values. A careful side-by-side comparison is critical. It is posible to use the -printdbcfg option of db2look to mimic settings exactly.

$ db2look -d sample -printdbcfg -o sample.dbcfg
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: ECROOKS
-- Output is sent to file: sample.dbcfg

The output from this command gives all statements needed to mimic the db cfg, including settings that may be obvious and rarely changed.

Db2 Licensing/Edition

This is a bit of an overlooked area. Even development environments may need to be licensed depending on the licensing model used. More importantly, a few distributions of Db2 don’t have the same features as others. For example, Express-C doesn’t allow online reorgs. If you have a higher edition in non-production, but then are using Express-C in production, you may be surprised when you try to do something that worked perfectly well in lower environments. While I love the new Developer-C edition of Db2, it makes issues like this even more possible.

File systems

This is of utmost concern when comparing HADR standbys or when comparing a production environment to a non-production environment where a full backup will be restored. In those situations, something will fail if the file system sizes, ownership, and permissions are not kept exactly in sync. Even if you’re comparing a differently sized non-production environment to a corresponding production environment, verifying that file systems with the same name, ownership, and permissions exist is a good idea. It is possible and sometimes necessary to have a non-production environment with a simplified file system structure, but it is nice to keep them identical whenever possible.

Table Spaces

It should not be possible for the table space design to get out of sync on an HADR environment, since changes should be replicated for you. In other enviornments, it is possible for a tablespace to be added or even dropped in one environment, but not in the other, or for the storage paths or container paths to be added or changed in some way. This is especially true if a tablespace like SYSTOOLS is automatically added by Db2, or if a 32K temp tablespace does not exist, and one is added in response to a specific error.

Buffer Pools

Outside of the issues previously mentioned for HADR environments, buffer pools are one of the areas least likely to be a problem, particularly if they are all a part of STMM. Buffer pools are likely to be of different sizes in production and non-production enviornments, and as long as key performance indicators like the buffer pool hit ratio are in reasonable ranges, that should be just fine.

CLI Packages

This is one I’ve seen forgotten several times. Sometimes in response to an error, a DBA will bind additional CLI packages. Later, it may come to light that the same number of CLI packages are not available in another environment. By default, 3 are bound, but a number can be specified up to 30. The way to check this is to look at the system packages using a query like this:

select 
    substr(pkgname,1,18) as pkg_name
    , valid 
from syscat.packages 
where pkgname like 'SYS%' 
    and pkgschema='NULLID' 
order by pkg_name   

PKG_NAME           VALID
------------------ -----
SYSLH100           Y
SYSLH101           Y
SYSLH102           Y
SYSLH103           Y
SYSLH104           Y
SYSLH105           Y
SYSLH106           Y
SYSLH107           Y
SYSLH108           Y
SYSLH109           Y
SYSLH200           Y
SYSLH201           Y
SYSLH202           Y
SYSLH203           Y
SYSLH204           Y
SYSLH205           Y
SYSLH206           Y
SYSLH207           Y
SYSLH208           Y
SYSLH209           Y
...

In this example, 10 was specified when CLI packages were bound. There are multiple ranges we can see that in – displayed here are the SYSLH1 and SYSLH2 ranges.

Summary

Keeping two or more enviornments in sync as far as configuration is concerned is a task that pulls strongly on one of the DBA’s biggest strengths – attention to detail. There are hundreds of parameters and details to compare, even before we get down to the actual logical object level. Even if you are really on top of this task, periodic reviews of environments should be done.