Hi Everybody,

I am working in a bank that has independent databases at 80+ branches.
Every location takes an offline backup every day.

At last date of every month, the branches dispatch me the months last day backup.

I restore the databases (Version recovery) from the recieved backups and consolidate a few tables from each locations restored db in a central database using Import/Export for reporting purposes.

My problem is that I have to unzip backup images of 80 branches and have to change the timestamps in batch files, that is too boring.

My question is that if I switch to ARCHIVAL LOGGING at all the locations, would it be possible that I just use logs for updating changes to restored database at the consolidation server.

That is I just restore the databases once on the consolidation server (version recovery) and after that just collect the log files, place them in the restored databases logs directory and restore to End of Logs.

Pleas help me in this regard as its becoming very difficult for me to restore all the databases and month end.

Yes, but you can do it only once. After you have recovered a database by rolling it forward, you cannot do it again until you do a new RESTORE.

or leave the db in rollforward state for next run - but db is not accessible when in rollforward state

That means that I have to do a version recovery every month end by using the recieved timestamps,
and the advantage of archival logging is just point in time recovery.

The issue is that every month, I have to restore a lot of db2 databases on my head office consolidation server. I use the month end backup timestamps for that by unzipping the timestamps and making changes to batch files.

Isn't there any better solution instead of the method I am using.




Thnaks

I have to use the databases for month end consolidation (there is a program that exports latest data from some tables and then loads them in to a central database that I use for reporting).

How can I leave it in rollforward state

If you can identify the "latest data" in the tables, you can use a simple EXPORT at the sources and then LOAD at the server.

Yes I can identify the transactions date in some tables but in a number of tables same rows are update, like for customer balances table there is only one row for each customer and new balance is updated in the same row.

For those update scenarios, you could add a "last updated" column to your table. DB2 LUW V9.5 has a clause "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP" for this.

Another idea would be to use the Capture part of Replication and just extract the data changes and apply them at your target system. In fact, maybe you can even use a regular replication setup...

Adding a "last update" columns would be a major change to the databases structure for which I dont want to go and we are using version 8.2
I will look in to this
clause "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP".

Our future plan is to go for db2 replication as each server will be allotted a Live IP address and we will switch to online replication using capture and apply.

Meanwhile I will do what I am doing, or I will ask the branches to send the export files of tables and make a program at the consolidation server or a batch file that will use the export files and load data in to central db.

Thanks a lot stolze for your time and help.

Any ones else comments are appreciated