Are you one of those who doesn't associate DB2 with Windows?

Then you're in for a pleasant surprise. Version 8.2 of IBM's DB2 Universal Database boasts a plethora of new features that fully integrate it into your Windows environment. Let's take a look at some highlights.

(A complete review of new DB2/Windows integration can be found in the recently released IBM Redbook, DB2 UDB V8.2 on the Windows Environment, a great juicy technical book.)

Full Visual Studio .NET Integration Much has already been written about DB2's deep integration with VS.NET. Suffice it to say that working with DB2 from within your IDE has never been easier, with class libraries and even wizards for creating stored procedures.

Windows Domain Handling and User IDs
DB2 now uses the access tokens generated by Windows to acquire the user's group information and associated privileges. Since the access token, a protected object generated by Windows, is cached after authentication, this speeds up subsequent authentication for DB2 processes, of which there are many.

Use of access tokens is not enabled by default, so you'll need to hit the switch yourself. Do this by changing the DB2_GRP_LOOKUP setting in your registry, which can be done using the db2set command. You now have three token parameters to choose from when setting the group lookup method: TOKEN, TOKEN_LOCAL, or TOKEN_DOMAIN.

For example, if your current group lookup method is LOCAL and you want to use access tokens to continue looking up local groups, you would use the following command:

db2set DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL

Note, however, that if you're using client authentication on remote systems, then you can't use access tokens for group lookup, which happens only on the server. In those scenarios, you would use existing authentication types.

In addition to access tokens, DB2 improves on its Windows Domain handling by supporting other features such as nested group semantics, local group domain, and implicit trust between domains. Even handling fro User ID, and consequently authorization ID, has been greatly improved by accepting longer names and special characters.

Windows Local System Account
During installation, DB2 creates a db2admin Windows login, under which many of the processes are subsequently run. However, DB2 also uses the LocalSystem account, which contains all the necessary rights for these processes. In fact, as long as your DB2 install doesn't share resources with another machine, you can move all of DB2's processes to LocalSystem. This can simplify password maintenance if your IT policy requires password expiration for the db2admin account.

To move a process to the LocalSystem account, open the Services snap-in (Start -> Control Panel -> Administrative Tools -> Services). All DB2 services start, amazingly enough, with "DB2". Stop the one you want to change, then under the "Log On" tab, select "Log on as: Local System account". Start the service again.

Administrative Tools
While we're on the subject, DB2 is fully integrated with other Windows Admin Tools as well. For example, from Administrative Tools choose Event Viewer. In the "Source" column you should see several DB2 events. To see a complete list of DB2 processes, go no further than Task Manager.

Also look at the Performances snap-in. You have access to an enormous list of DB2 performance counters by choosing either "DB2" from the list of instances on the right or "DB2 Database Manager" from the list of Performance Objects on the left.

Activity Monitor
The new DB2 Activity Monitor snap-in, available from either the Control Center or the Start Menu (Programs -> IBM DB2 -> Monitoring Tools -> Activity Monitor), allows you to select from a list of conditions and monitor those conditions for one or more specified applications. This level of monitoring will help you spot underperforming or glitched apps that are otherwise hard to find.

Even better, you can sleuth out the problems using a variety tools incorporated within the monitor. The Redbook on Windows Integration contains a step-by-step example of this kind of detective work.

New Data Encryption Types
New with V8.2 are the DATA_ENCRYPT and DATA_ENCRYPT_CMP authentication methods for your server instance or database. Using one of these methods causes the following objects to be encrypted:

• SQL statements
• SQL program variable data
• SQL statement output data and corresponding description
• Answer data set from queries
• Large object (LOB) data streaming
• SQLDA descriptors

Note that this differs greatly from the SERVER_ENCRYPT method, which only encrypts user ID and password.

You have two ways to start using these encryption types. The first, on the DB2 server, is to type:

UPDATE DBM CFG USING AUTHENTICATION DATA_ENCRYPT

This is the equivalent of the second method, which is to go through the DB2 Control Center and change the authentication parameters under the DBM Configuration.

On the client side, you can catalog a database using the DATA_ENCRYPT authentication type, as in this example:

db2 catalog database MYDB at node MYNODE authentication DATA_ENCRYPT

The distinction of DATA_ENCRYPT_CMP is that it encrypts at the node level. This means that any client connecting to a database in that node will be forced to encrypt the data if it's able--but can still access the database if it doesn't support data encryption.

Security Plug-Ins
One of the more interesting aspects of DB2 V8.2 is the use of external plug-ins for handling security. You can use dynamically loadable library (.dll) files to handle everything from identification and authentication to authorization based on group memberships. This allows for some interesting new possibilities:

• Completely customized security mechanisms.
• Third-party plug-ins.
• Multiple authentication types used at the same time.

Using C or C++, you can write either a userid/password or a GSS-API plug-in for a variety of applications. For example, you can:

• Restrict users based on workstations or IP address ranges.
• Restrict access to certain databases by a particular time, for instance to prevent interfering with a nightly batch job.
• Implement your company's unique security policy.
• Authenticate an intranet userid/password pair against an internal LDAP directory.
• Remap a set of user IDs to a single ID to speed authentication overhead.

Flexible Stored Procedures
In previous versions of DB2, procedures required a C or C++ compiler. Now SQL procedures can execute native SQL statements. External procedures can also take advantages of .NET CLR languages, such as C# and VB.NET. For more information on DB2 Stored Procedures, check out our "Introduction to DB2 Stored Procedures" and our "Walkthrough: Creating Stored Procedures in DB2".

Backup Enhancements
Chief among these is the Configure Automatic Maintenance wizard, available through the Control Center or Health Center. In addition to setting the type of maintenance and times, you can assign notification email recipients in the event that something goes wrong. Thanks to integration with the Health Center, you can trust that the health of your backup is being tested and monitored regularly.

In addition, DB2 provides self-tuning backup and restore. This allows you to automatically choose the best buffer count, buffer size, and parallelism settings for backup/restore based on memory available, number of processors available, and database configuration.

High Availability Disaster Recovery (HADR)
DB2's disaster recovery system gives you a huge safety net for your mission-critical applications. Where data recovery usually requires mad scrambling by panicked DBAs, HADR uses real-time replication to give you an emergency switchover system.

The process goes something like this:

• DBA sets up two active systems, a primary and a standby.
• Database transaction logs are continuously shipped from primary to standby via TCP/IP.
• Standby system applies the transactions, thus replicating primary system.
• Some time in the hypothetical future, Something Bad happens.
• Standby system automatically takes over and becomes the new primary.
• Applications are rerouted via the DB2 client reroute feature.
• DBA has a cuppa joe, reads Dilbert, gets around to fixing the original primary.
• Once everything's back up and running, the problem system is resynchronized and restored, then rechristened as the primary again.
• DBA is feted for setting up an exceptionally capable disaster recovery plan using the HADR GUI.

After some initial setup, like initiating archive logging on your primary system and cloning your database to the standby system, you have two options for loading the HADR GUI:

• From the DB2 Control Center, select Tools -> Wizards -> Set up High Availability Disaster Recovery (HADR) Databases.
• Right-click the database and select High Availability Disaster Recovery -> Set Up.

Both of these kick off the setup wizard, which steps you through the entire process, as any good wizard should. It even allows you to copy objects not included in the database backup image, such as stored procedures and UDF. You can find a thorough walkthrough of the process in the Redbook DB2 UDB V8.2 on the Windows Environment.

Bonus Reasons to Love DB2 on Windows
That's not all, but we could be writing until we run out of electrons. Dig into these topics if you get a chance:

• DB2 UDB integrates with the Microsoft Cluster Server.
• IBM offers DB2 V8.2 Windows certification—not many of its competitors do!
• Performance: DB2's got it, on Windows. Check out recent IBM-on-Windows TPC-C benchmarks and Total Cost of Ownership and similar data running on the x86_64, and of course running on Itanium
• For more gritty detail on DB2 in Windows-land, check IBM's dedicated page

Conclusion
These new features and enhancements barely scratch the surface of all the changes you'll find in DB2 UDB V8.2. Windows users have a lot to be happy about. Thanks to its robust Windows integration, DB2 has become a major player in Windows-based database technologies.