Distributed / Partitioned etc

[BrianTyler]

Not being a DBA, I am usually totally confused by IBM DB2 documentation.
We have a large(ish) DB2 v8 UDB transactional database on RS6000 (AIX), and each month this is copied to another machine for analysis, using a redirected restore of an FTPd backup.
Because the analyses are at a low level, we cannot delete or summarise much of the data, even though 50% of the database is dormant - the customers having left over 3 years ago.
I would like to split the database in two - half live, half dead. The production machine would only have the live data, the analysis machine would hold the dead data, and the latest live data would be copied each month - but there is no time for logical extracts and inserts. A single SQL should be able to access data for both live and dead (without unions etc).
I initially thought that Partitioned Tables would do the job where one partition holds live and one dead, but I have been told that this is not so.
Has anyone any ideas where I should look for a solution.
(Sorry it is such a long and rambling question)

Brian

 

[MarcLodge]

Brian,
I think that as you are planning on runing over two separate machine, that you will require Distributed rather than partitioned. There are 100's of articles available over the net which you can read. You might find this one from Craig Mullins to be a good starting point:

http://www.dbazine.com/mullins_drda.html

Marc

 

[BrianTyler]

Thanks Marc,
This solution could be relevant, but is probably more complex than I need.
Basically on the OLTP machine I want a database containing data for live customers only.
On the Analysis machine I want an identical database, but this time with each table split in two. The first half of the tables will contain the data for dead customers. The second half of each table would be refreshed each month from a save of the OLTP live customers.
I was hoping to have a simple, standard DB2 database for OLTP, and something like a partitioned database on Analysis. One partition for Analysis would be fixed, holding the data for the dead customers, and the other partition would be refreshed with a re-directed restore of tablespaces from backups made on Live.
A single SQL statement would then be able to scan the whole Analysis database.

Brian

 

[gregsimpson]

Brian,

Perhaps Federated Views are of use to you. with Federated views you can read tables on databases on both local and remote machines. You can use 1 SQL statement to access the Federated view, which may have a number of local and remore tables in it.

Cheers
Greg