Federation of DB2 Instances Across LPAR's

[spencer]

My client is going through a large project to replace existing data
warehouse physical infastructure which is running DB2 8.1.6. In the
past we had a much more distributed environment where we had certain
portions of the data warehouse on a physically different server. We
utilized federation to allow the joining of tables on seperate
instances. We found that this worked ok in situations where the tables
were not that large say under 100,000 rows. We now have the need to be
able to join tables on seperate instances with more then 30 Million
rows. While it works in the current environment it takes a very long
time to ship the rows and thus it has become our bottleneck.

In our new physical infrastructure our database instances will all be
on the same physical hardware but will exist in different LPAR's. Will
we see improved performance when using federation to join across the
database instances? My thoughts are that we would see a vast
improvement as the data doesn't need to be shipped across the network.
Can anyone offer any confirmation on if my thoughts are correct or not?

Spencer

[Mark A]

<sp*****@tabber t.net> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

My client is going through a large project to replace existing data
warehouse physical infastructure which is running DB2 8.1.6. In the
past we had a much more distributed environment where we had certain
portions of the data warehouse on a physically different server. We
utilized federation to allow the joining of tables on seperate
instances. We found that this worked ok in situations where the tables
were not that large say under 100,000 rows. We now have the need to be
able to join tables on seperate instances with more then 30 Million
rows. While it works in the current environment it takes a very long
time to ship the rows and thus it has become our bottleneck.

In our new physical infrastructure our database instances will all be
on the same physical hardware but will exist in different LPAR's. Will
we see improved performance when using federation to join across the
database instances? My thoughts are that we would see a vast
improvement as the data doesn't need to be shipped across the network.
Can anyone offer any confirmation on if my thoughts are correct or not?

Spencer

Depends on how fast your old network interface was, and how much other
traffic was on the network. For example, if you had a private gigabit
Ethernet connection between the two machines, I would think it would pretty
fast.

With LPAR, each logical machine has its own IP address, so I am not sure
what the actual path between the machines would be. For example, would all
communications between the LPAR's just go out to the network and back again?
I really don't know the answer myself. You might need to ask someone in an
AIX forum (I am assuming that use AIX).

[spencer]

Actually it is going to be on HP-UX hardware and I believe we only had
a 100MB between the two machines.
Spencer

[Mark A]

<sp*****@tabber t.net> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Actually it is going to be on HP-UX hardware and I believe we only had
a 100MB between the two machines.
Spencer

I would try a HP/UX forum. The communication between LPAR's is via TCP/IP.

[Mark A]

> > Actually it is going to be on HP-UX hardware and I believe we only had

a 100MB between the two machines.
Spencer

I would try a HP/UX forum. The communication between LPAR's is via TCP/IP.

I should have said that "I assume the communication between two different
databases on different servers that are LPAR'ed on the same physical box is
via TCP/IP." I don't know for a fact.

[RdR]

Hi,

Our experience with federation is that up to 3 million rows, on a 70 MSU
machine or equivalent (about 350 MIPS), it starts to degrade in spite of use
of Materialized Query Tables, fine tuning, using separte disk i/os, cache
and any fine tuning possible. I think and I want to be corrected if I am
wrong, this is a natural issue as of now with federation, as data grows
bigger, federation goes slower. Even if you save on time on shipping the
data, the queries with joins on big tables will still be your bottleneck.

What we do is replicate the current data we need using replication tools (in
DB2 you have Data Propagator or Q Replication) to replicate data out of the
big bunch of data to a smaller subset (same data structure, etc.) and do the
federation on the subset of data we are replicating to. There will be a lot
of architechting and setting up schedules to replicate data but that was the
most efficient way we found on how to deal with federation of big tables.

Hope this helps.

RdR

<sp*****@tabber t.net> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

My client is going through a large project to replace existing data
warehouse physical infastructure which is running DB2 8.1.6. In the
past we had a much more distributed environment where we had certain
portions of the data warehouse on a physically different server. We
utilized federation to allow the joining of tables on seperate
instances. We found that this worked ok in situations where the tables
were not that large say under 100,000 rows. We now have the need to be
able to join tables on seperate instances with more then 30 Million
rows. While it works in the current environment it takes a very long
time to ship the rows and thus it has become our bottleneck.

In our new physical infrastructure our database instances will all be
on the same physical hardware but will exist in different LPAR's. Will
we see improved performance when using federation to join across the
database instances? My thoughts are that we would see a vast
improvement as the data doesn't need to be shipped across the network.
Can anyone offer any confirmation on if my thoughts are correct or not?

Spencer

[Larry]

100MB wouldn't be optimal. GB Ethernet would be. Don't know network hw
well enough to know how much faster LPAR to LPAR communications would be.

Larry Edelstein

sp*****@tabbert .net wrote:

Actually it is going to be on HP-UX hardware and I believe we only had
a 100MB between the two machines.
Spencer

[spencer]

So your telling me that the two instances would still communicate via
TCP/IP? That does not seem right to me. Is there no gain by having
these on the same physical box then?
Spencer

[Mark A]

Here is a post from comp.sys.hp.hpu x newsgroup today and the answer. If the
answer below is correct, you might be better off with the old configuration
and then add a Gigabit Ethernet card on each machine and run a private
Ethernet connection between them (assuming they are reasonably near each
other).

If one physical machine is LPAR'ed to run two HP/UX systems, and
there is a TCP/IP connection between the two servers, does the IP
path go out to the hub/router on the network and back, or is there a
faster IP path within the machine between the two LPAR's.

IIRC, down the stack, past the driver, out the NIC, to the switch,
nothing but net. TCP/IP communications between LPARs is just like
communication between two completely separate hosts.

rick jones

[Larry]

It has nothing to do with TCP/IP. Databases always use TCP/IP to
communicate.

It is related to the hw/network that the traffic must pass through in
order to get from one to the other.

Larry Edelstein

sp*****@tabbert .net wrote:

So your telling me that the two instances would still communicate via
TCP/IP? That does not seem right to me. Is there no gain by having
these on the same physical box then?
Spencer