Dear all,
We have a very large table which due to disk limitations, every few weeks, we manually copy to another DB2 database using piped export/load and then backup the copied table to tape. We want to change this so that the operation is automatic. The issues are:-

1. Need to be able to view the list of available archived datasets and retrieve 1 or more of them at 1 time in order to satisfy customer queries.

2. The smaller the dataset that we backup/restore, the less range that we can query in 1 go. This could mean that multiple backup/restores need to be done to satisfy a request for data. Conversely, The larger the dataset the longer it takes to backup/restore.

3. Tape retrieval is always serial

Does anybody know of a commercial package which works with DB2 to allow easy archiving/retrieval of datasets from tape? If not, does anybody have practical experience of implementing such a system themselves?

Tivoli Storage Manager (TSM) will do what you want. This product is fairly well integrated with DB2, as you can see by looking at the last entries of the DB Config for any DB2 database. Tivoli is owned by IBM.

We're currently using TSM to backup our data to tape, however.

The problem is that we currently backup up the entire archive database to tape. This means that it's possible that we could have to restore multiple archived database images to satisfy a customer query.

What we're looking for is presumably a frontend to TSM which ideally we could just ask the commercial package to process a DB2 query, and the commercial package would determine whether it could find the data on disk, the archive database or tape, retrieve it from where it's stored and return the results.

The other thing that I forgot in our wishlist is that our data needs to be secure at all times, so we can't just export the data to a file and then back it up to TSM

OK, then you want to archive and retirieve specific tables needed to satisfy a query. I believe you can do that with DB2 for z/OS but I don't know of a product for LUW. But with cheap disk drives, this is probably less of an issue than it used to be.