Jim Youmans
asked on
Use IBM MQ with DB2 to replicate table level data
I am currently running DB2 10.5 on Windows. I have 4 servers: Dev, Test, Stage, and Prod. I am migrating each to a new server that will be running DB2 11.5 on Windows.
I was told I should be able to use IBM MQ to replicate the data from 10.5 to 11.5 (we have the correct version to use this). I have looked at the tutorials and such but still have some questions. I just need to copy the data from old to new.
I am looking at doing it this way to avoid the downtime from an offline backup/restore. I am trying to avoid a 10 hour downtime.
Where do I need to set the MQ server up? Do I set up the MQ server on one server and put clients on all the others? Or do I need it set up on each server? On on just one of the server pairs?
Is the replication at the table level or the database level? If I have 45 tables in 10 schemas do I need to set up a queue for each? Or per schema?
Any help would be greatly appreciated. Thank you!
Jim
I was told I should be able to use IBM MQ to replicate the data from 10.5 to 11.5 (we have the correct version to use this). I have looked at the tutorials and such but still have some questions. I just need to copy the data from old to new.
I am looking at doing it this way to avoid the downtime from an offline backup/restore. I am trying to avoid a 10 hour downtime.
Where do I need to set the MQ server up? Do I set up the MQ server on one server and put clients on all the others? Or do I need it set up on each server? On on just one of the server pairs?
Is the replication at the table level or the database level? If I have 45 tables in 10 schemas do I need to set up a queue for each? Or per schema?
Any help would be greatly appreciated. Thank you!
Jim
ASKER
Thank you sir!
Old days, one can install MQ on both source and target server and setup QREP (Q Replication) easily.
Nowadays, it is not the case anymore. IBM put QREP into a product called IIDR (IBM InfoSphere Replication Server) and things get complicated.
Nowadays, it is not the case anymore. IBM put QREP into a product called IIDR (IBM InfoSphere Replication Server) and things get complicated.
ASKER
I have a production system that I need to migrate to a new server and DB2 version. It is currently running 10.5 and I am moving it to 11.5. This is not hard to do but it requires a few hours of down time due to the fact I have to take an offline backup in order to restore to 11.5. The db takes about 2-3 hours to backup and another 2-3 hours to restore. So I am looking at 4 to 6 hours of downtime if all goes right and more if not. The business won't approve that since the system is 24/7.
In both SQL Server and MySQL this is pretty easy to handle. But DB2 is a pain.
Do you have any suggestions?
Thanks.
Jim
In both SQL Server and MySQL this is pretty easy to handle. But DB2 is a pain.
Do you have any suggestions?
Thanks.
Jim
Hi Jim,
I don't remember your environment. Aren't you running HADR? If so, you should be able to create your new database on the new server and use the HADR hooks to sync them, then switch it to be your primary.
I don't remember your environment. Aren't you running HADR? If so, you should be able to create your new database on the new server and use the HADR hooks to sync them, then switch it to be your primary.
ASKER
Even if one is 10.5 and one is 11.5? That is what is giving me the issue. Restore has to be from an offline backup as far as I can tell.
If you follow what I mentioned in your another question, you should be able to wrap up the whole thing in less an hour.
To be more clear, let's use an example: say A is your current box, and B new.
There are different upgrade routes, below it is one of them just for simplicity.
Test run:
1) Install Db2 10.5 on B
2) transfer an online backup from A to B
3) restore on B, rollforward and complete
4) Install 11.5 on B and upgrade the instance and database
timing Step4 which will be the rough total downtime
Real run:
1) Install Db2 10.5 on B
2) transfer an online backup from A to B
3) restore on B, and rollforward (but NOT complete)
4) for any new archive log(s) generated, transfer from A to B,
and continue rollforward and apply the archive log(s)
repeat this step, till step5 below
5) On the day to cutover, shutdown Db2 on A
6) transfer the remaining new archive log(s) from A to B
7) On B, apply the logs and complete the rollforward
8) Install 11.5 on B and upgrade the instance and database
Step6,7,8 will be the total downtime
There are different upgrade routes, below it is one of them just for simplicity.
Test run:
1) Install Db2 10.5 on B
2) transfer an online backup from A to B
3) restore on B, rollforward and complete
4) Install 11.5 on B and upgrade the instance and database
timing Step4 which will be the rough total downtime
Real run:
1) Install Db2 10.5 on B
2) transfer an online backup from A to B
3) restore on B, and rollforward (but NOT complete)
4) for any new archive log(s) generated, transfer from A to B,
and continue rollforward and apply the archive log(s)
repeat this step, till step5 below
5) On the day to cutover, shutdown Db2 on A
6) transfer the remaining new archive log(s) from A to B
7) On B, apply the logs and complete the rollforward
8) Install 11.5 on B and upgrade the instance and database
Step6,7,8 will be the total downtime
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I wish I could help with this one, but I'd be purely guessing. I've never used MQ.