Q and A

Asked and Answered

We need to implement two bank applications using DB2 UDB Version 7.2. The requirements for each application are as follows:

Both applications will communicate through MQSeries. A Web server will use Application 2's database for reports over the Web. Our DBA (who is very experienced with Oracle, but not DB2) has suggested to have one machine with three processors and 4GB RAM. He also suggests that we create two instances on the same machine; each instance will have each application's database. Our database availability requirements call for up to four hours of down time, so our DBA does not suggest failover support.

Is this is a good idea? Will combining two servers into one and increasing the number of instances on a server create performance problems with two large databases? Is there anything we need to consider before implementing our DBA's suggestion?

Paul Zikopoulos, IBM Canada, responds:

I like this kind of question because I can't give you a wrong answer. There's so much I would need to know to give you a definitive answer. But I'll raise some items for consideration that might help you come to a decision.

Processor. Quite frankly, I have not seen a three-way machine before (I do not doubt they exist, but I haven't seen one). I'll assume that you would use a four-way SMP box for your solution. DB2 can take advantage of powering up your machine, but this could end up being a double-edged sword for you.

You see, there's no way in DB2 to assign specific processors to specific applications, instances, or workloads. Consequently, a heavy workload on Application 1 may prevent Application 2's data from being served efficiently over the Web. On the other hand, if Application 2 wasn't producing requests to the database manager, then Application 1 could leverage all your system's resources to answer queries, which would be a good thing.

If you're willing to implement your solution on some newer hardware boxes such as IBM's Regatta machines or similar ones (such as Sun Solaris or HP-UX), you can take advantage of hard partitioning (which is similar to LPARs in z/OS land). This would enable you to assign specific resources to specific partitions (which, in turn, would house specific applications) yet use the same hardware box. Different operating systems provide low-level processor affinity, but that adds a complexity level to your application.

Memory. You seem to have two vastly different approaches for memory to two different databases. It seems to me that you're understating your memory requirements on Application 2 and overstating them on Application 1 (although, without more details, I can't know for sure. You did not mention on what operating system you plan to deploy your solution. Different operating systems have different address limits with respect to memory. For example, on AIX, the maximum amount of addressable memory is 1.8GB per instance and on Windows it's up to 4GB (if you're using DB2 Version 7.2 support for Microsoft Address Window Extensions).

If you combine your two machines, you must take into account the amount of RAM you will need. Because your environment is query-based, you should expect to see sorts and scan and other memory-hogging database operations.

You also have to pay a memory consideration for the number of concurrent users that will be accessing the database.

Have you considered a 64-bit environment?

Workload. Your workload is definitely query-based, but I don't know what kind of query. Are they canned queries or will they be mostly ad hoc? Regardless of the type of hardware and engine you have serving up the data, bad queries (usually generated by query tools that allow business analysts who are unfamiliar with SQL to ask business questions) can bring a data warehouse to its knees.

You may want to consider front-end and back-end governing to ensure timely responses to your reports. On the backend, you can leverage the DB2 Governor, which comes with DB2. You can learn more about this feature in the DB2 Administration Guide. On the front end, consider using DB2 Query Patroller.

DB2 Query Patroller greatly improves the scalability of a data warehouse by allowing hundreds of users to safely submit queries against data warehouse systems. DB2 Query Patroller acts as an agent on behalf of the end user. It prioritizes and schedules queries so that query completion is more predictable and computer resources are used efficiently.

Availability. The window you have for your databases to be unavailable may justify not planning for failover support, but when you combine the two machines, you must understand that if an unrecoverable error occurs outside of the database manager, you will lose access to both databases. This may or may not be a problem, depending on the nature or scope of your business.

There can be other considerations in addition to those I've raised, including network bandwidth for Web hosting, the application server, and so on..

My gut feeling is that it's a bad idea to put these two databases on the same box. The inability to control the processor to handle workload dynamics will leave you with a box that's hard to tune. This may work if the workload were so low that the dynamics didn't matter; but I doubt that's your situation. Your initial scenario looks off to me as well. Application 2 will likely have some serious operational issues with backup and recovery with that amount of data on a uniprocessor. In short, I'd recommend that you reexamine both situations.

The opinions expressed in this article are those of the author, not of IBM.

See a complete archive of reader/author Q&As .