July 2, 2008 at 12:05 am
Dear Friends,
We are a financial institution & have a huge volume of databases in DB2 which we are currently pulling it on a daily basis using DTS to a SQL 2000 server. These DTS packages are scheduled & it runs for approximately 9 hours during night time. The process actually truncates all the tables in all databases in SQL server & re-pulls again the entire data from DB2. We do this because our table structure does not have provision for modified records(ammendments, cancellation, etc..).
Now we have been provided with a SQL server 2005 (Enterprise Edition). I just want to know whether we could replicate the DB2 data to SQL server 2005 & if possible whether the changes or modification done on records in DB2 will reflect on SQL server 2005 too? Also I would appreciate if any of you DBA's could suggest a best solution to this issue other than replication. Thanks in advance.
Regards,
Rajesh
July 2, 2008 at 6:18 am
There is nothing built into replication on the SQL Server end to pull from a DB2 database automatically. The DB2 database would at least have to be able to publish the changes in some way.
Barring having some way of knowing which records changed in the DB2 database, there is probably not much you can do.
July 2, 2008 at 12:50 pm
Replication TO MS SQL Server is better (only) handled with DB2 Tools.
* Noel
July 3, 2008 at 6:26 am
Not to offer a vender a free plug (since there are many choices), but we have just implemented HiT software's DB Moto, to replicate data from DB2 to SQL Server. It seems stable, though I am still questioning it's scalability; I think our 50+GB transfers (not all of the data is transferring between the systems, only around 53 GB) is stressing the software. Also, we are still having problems with errors (~ .06% data inconsistency in our worst table). We are using the SQL Server side of the DB2 OTLP for reporting purposes and linking SQL Server side applications to reference data.
July 3, 2008 at 6:50 am
thanks mentalWN...lol.. 😉 finally i see some light at the end of my tunnel...could you provide me with some documentation sort if possible the way you people are replicating...or gimme some hints or links atleast where i cud gather info related to this...wud really appreciate ur help...thanks & regards, Rajesh
July 3, 2008 at 7:54 am
We have three levels of replication that we are currently using in DB Moto. Basically, one type of replication is a "Refresh" where it is a truncate and a full data insert. I have this set up to occur nightly for certain tables, and as we are still fine tuning our settings (more on this below) all other tables are refreshed weekly. Then we have time-delayed "Mirrored" replications. That is to say true Journal based replications that occur every 15 minutes or every hour. DB Moto adds a procedure to DB2 to return the current LSN from the current journal for a table. It uses this for true replication. The third type we have is "Real Time", which is just "Mirrored" replications set for every minute.
In order for Mirrored replication to work, your SQL Server table needs to have an identifiable primary key, that the replication will attempt to enforce (regardless of it it is a physical primary key or not). The issue that we are having in our system is certain tables do not have physical primary keys in DB2, only logical ones, and those logical primary keys are not enforced during certain table "states" based on specific triggered processes in our DB2 OLTP. So, we receive primary key violation errors when the transactions include half of what should be (but is not) a single transaction. This makes it difficult to troubleshoot other possible errors. Again, out of 50 tables, we have around 10 that have any errors at all, with probably 5 suspect of potential real problems, with the error count to total row count (many errors cause two errors to be logged) of less than 0.06%
July 5, 2008 at 11:45 pm
Hi buddy,
thx for enlightening us with some useful tips. More or less this is what we had been expecting & require. Regarding the modes you have mentioned i have few clarifications.
1. Referesh- Truncate & full insert of all tables (approximately 7 to 8 million records or 40 GB data). This is what we have been doing using DTS in SQL 2000. The main drawback we faced in this is that the log files keep growing & occupies the full disk space(120 GB) which we need to backup & clear all those ldf files on a daily basis. Will the same problem occur if we use the refresh mode in DB motto? Please throw some light on this.
2. Time-delayed mirroring- This seems to be an ideal solution for our environment. But is there anything that I need to configure on DB2/AS400 or do i need to take help/assistance from our DB2 DBA? Did you face any primary key issue in this mode too?
3.Real time- This would be the best solution for us. I will do R&D on it since we have a test environment on both SQL & AS400. In between if you find a solution to overcome the primary key issue(we too have the same problem) do lemme know.
Thanks in advance.
hey by the way whatz ur name???
Regards,
Rajesh
July 17, 2008 at 8:14 am
One of the alternatives I'm looking into is to replicate the DB2 tables into another DB2 schema. The ETL process can then pick up the DML'd rows from the replicated tables and remove them after processing.
Should keep the replicated volume way down in storage terms while capturing the table activity.
Regards
George.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply