By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,408 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,408 IT Pros & Developers. It's quick & easy.

Looking for Experiences Running DB2 for OLTP Database (>300GB)

P: n/a
I'm looking for "real life" experiences of companies running DB2 in an
24x7 OLTP environment. Seems like most folks are using DB2 for DSS
applications, rather than OLTP. If your DB2 production environment
meets the following criteria, I'd love to hear from you:

1. Using DB2 as an OLTP database
2. Using DB2 via custom apps (not 3rd party apps like Peoplesoft etc.)
3. DB2 database instance >300GB
4. Have used DB2 for at least 1 year, preferably longer
5. Solaris or Linux platform

Thanks,

Michael
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Michael Sherf" <be****@ihwy.com> wrote in message
news:f7**************************@posting.google.c om...
I'm looking for "real life" experiences of companies running DB2 in an
24x7 OLTP environment. Seems like most folks are using DB2 for DSS
applications, rather than OLTP. If your DB2 production environment
meets the following criteria, I'd love to hear from you:

1. Using DB2 as an OLTP database
2. Using DB2 via custom apps (not 3rd party apps like Peoplesoft etc.)
3. DB2 database instance >300GB
4. Have used DB2 for at least 1 year, preferably longer
5. Solaris or Linux platform

Thanks,

Michael


I am a consultant and not prepared to reveal the names of any companies
without their permission, but I can tell you that you are off base about DB2
not being used much for OLTP. I will admit that it may be harder to find
OLTP examples on Linux because support for it is relatively new (since you
are looking for "used DB2 for at least 1 year, preferably longer") or
Solaris, because it does not have a lot of DB2 licenses.

I am not sure what the distinction is between SAP, Peoplesoft, etc, and a
custom application. Can you explain this?
Nov 12 '05 #2

P: n/a
be****@ihwy.com (Michael Sherf) wrote in message news:<f7**************************@posting.google. com>...
I'm looking for "real life" experiences of companies running DB2 in an
24x7 OLTP environment. Seems like most folks are using DB2 for DSS
applications, rather than OLTP. If your DB2 production environment
meets the following criteria, I'd love to hear from you:

1. Using DB2 as an OLTP database
2. Using DB2 via custom apps (not 3rd party apps like Peoplesoft etc.)
3. DB2 database instance >300GB
4. Have used DB2 for at least 1 year, preferably longer
5. Solaris or Linux platform


My company's database is nearly a perfect fit for that list, except
that we're just over 100GB, not 300GB. Otherwise, we're happily using
DB2 V8 on Solaris for custom web applications written in Perl, Python,
and Java servlets. Some apps are strictly OLTP, while others perform
DSS reporting, all against the same database. We migrated the data
from Informix to DB2 V7 back in late 2000, and upgraded the instance
to V8 in July 2003 (in less than 30 minutes downtime).

As far "real life experiences", I can say that administering DB2
doesn't necessarily have to be an all-consuming task in itself,
possibly allowing the DBA time to help programmers with data modeling,
SQL tuning, and various analytical projects.

I was a relatively new DBA when I took on the task, so I made some
mistakes that even the new features of DB2 V8 can't easily rectify, so
I'll list a couple of the big ones here:
- Directory-based tablespaces (SMS) are notoriously hard to migrate
without incurring downtime. By sticking to DMS tablespaces, you can
shuffle an entire tablespace all over your disks and still stay
online.
- Once a table has practically anything done to it (foreign keys,
constraints, triggers, view references), there's really no painless
way to rename it. Put a layer of aliases between your programmers and
your base tables to overcome this limitation.
- Your system temporary tablespace can never be big enough or fast
enough. Fortunately, a problem with system temporary space is actually
pretty easy to fix, even online, provided you can scrounge up the
space for it.

If you would like to know about a specific ascpect of running OLTP on
DB2, please post it to this thread instead of emailing me; the address
given died a long time ago.

Hope that helps,

Fred
Nov 12 '05 #3

P: n/a
Fred wrote:
be****@ihwy.com (Michael Sherf) wrote in message news:<f7**************************@posting.google. com>...
I'm looking for "real life" experiences of companies running DB2 in an
24x7 OLTP environment. Seems like most folks are using DB2 for DSS
applications, rather than OLTP. If your DB2 production environment
meets the following criteria, I'd love to hear from you:

1. Using DB2 as an OLTP database
2. Using DB2 via custom apps (not 3rd party apps like Peoplesoft etc.)
3. DB2 database instance >300GB
4. Have used DB2 for at least 1 year, preferably longer
5. Solaris or Linux platform

My company's database is nearly a perfect fit for that list, except
that we're just over 100GB, not 300GB. Otherwise, we're happily using
DB2 V8 on Solaris for custom web applications written in Perl, Python,
and Java servlets. Some apps are strictly OLTP, while others perform
DSS reporting, all against the same database. We migrated the data
from Informix to DB2 V7 back in late 2000, and upgraded the instance
to V8 in July 2003 (in less than 30 minutes downtime).

As far "real life experiences", I can say that administering DB2
doesn't necessarily have to be an all-consuming task in itself,
possibly allowing the DBA time to help programmers with data modeling,
SQL tuning, and various analytical projects.

I was a relatively new DBA when I took on the task, so I made some
mistakes that even the new features of DB2 V8 can't easily rectify, so
I'll list a couple of the big ones here:
- Directory-based tablespaces (SMS) are notoriously hard to migrate
without incurring downtime. By sticking to DMS tablespaces, you can
shuffle an entire tablespace all over your disks and still stay
online.
- Once a table has practically anything done to it (foreign keys,
constraints, triggers, view references), there's really no painless
way to rename it. Put a layer of aliases between your programmers and
your base tables to overcome this limitation.
- Your system temporary tablespace can never be big enough or fast
enough. Fortunately, a problem with system temporary space is actually
pretty easy to fix, even online, provided you can scrounge up the
space for it.

If you would like to know about a specific ascpect of running OLTP on
DB2, please post it to this thread instead of emailing me; the address
given died a long time ago.

Hope that helps,

Fred

Hi Fred,

I know this is a bit off-topic, apologies for that.

It seems to me you have a situation that is similar to the situation
we're trying to achieve. Since the situation you described is a working
one I was wondering if you were willing to share some issues concerning
the configuration, since I'm not that much aquinted with db2 databases.

Are you making use of a partitioned database (cluster), if so, is this
realy necessary? And have you partitioned it over multiple machines?
Are those machines equiped with a 32 bit or 64 bit architecture?
How much memory is consumed by a database of this size?

Did you make use of large bufferpools? (do you by any chance know how
large a bufferpool can be on a 64bit architecture?)

What kind of storage solution are you using? I am thinking of using SAN
and using this SAN for failover, is this wise?

We're planning to run a db2 database on a 64bit Opteron machine (ibm
325) with a SAN connected to it. A passive 325 is standing by acting as
a backup application server, in case of an emergency it can temporarily
take over database activities.
The database can grow up to 100 GB with a maximum of 6000 trans/min.
Each AMD64 CPU can make use of it's own 6GB memory.
Each customer (n users) has it's own tablespace, these are estimated
from 500 to 5000.

Does this make any sense? Or is there a much wiser/stabler/cheaper
solution that you know of?

Thank you in advance.
(If anyone else has any comments on this, feel free to place feedback).
--

Jürgen -R-
"I don't know, but I will find out!"
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.