Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: db2 replication help needed

    Hello Experts,
    I have a 30 GB production database on DB2, my company wants to add a new server for reporting purpose and replicate the data from production system to reporting server every 15 minutes or so,(may be different interval for different subscription set).
    Please Note : 1) Production db tables has lot of identity columns and foreign keys as well.
    2) There won't be any structure difference in both the dbs.

    What would be best (free of cost ) options to implement this ? Replication, log shipping, or any other options? please let me know the pros and cons and thinns to consider..

    Regards
    Sunil

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    For 30 gb of data you can go for db2 native sql replication zero cost,
    only house keeping you need to do is the transaction logs, sql replication generates double the current logs which you have now, apart from that every thing is smooth, what is the avg growth of you database ??
    We are handling abt 700gb of database with sql replication.
    don,t worry abt PK and FK in sql replication, only thing is that each tables in target should have a unique key
    regds
    Paul

  3. #3
    Join Date
    Aug 2009
    Posts
    12
    thanks a lot MAthew for quick reply,
    Is it mandatory to have unique keys on all tables? some of my tables might not have it..
    the average growth would be around 500mb-1GB a month.. but we will definitely have huge transcations parallely going on.
    I have few qns though, hope you can suggest me something.
    1) tables are referentially integrated so we are bit worried about the transcation consistency.
    2) how will we handle identity columns? identity columns are "generated by default" in the database.
    3) What are the major factors affecting Performance

    4) we have around 500 tables to be replicated, will it be possible to replicate these many table after every 15 minutes (lets say we put them in a single subscription set)

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    unique key Paul is refering is required on reporting DB tables , moreover for reporting server why you require RI definied.
    Take care while deciding which tables to keep in same subscription set
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Oct 2007
    Posts
    246
    mandatory to have UK: while mapping the tables it will ask for unique index in target tables this is only coz all DML activity would be fast, create while mapping then drop the index
    1)Don,t worry of referentially tables are mapped column by column
    2)whatever data which is coming in source table will be replicated to target every thing
    3) Nothing much as already said Transactions logs will be generated double
    4)Yes its possible to replicate 500 or even more than that, but don,t go for single sub set, having multiple sub set
    hope this help
    redgs
    Paul

  6. #6
    Join Date
    Aug 2009
    Posts
    12
    hi Paul, this is regrading the 4th qns.
    my customer don't want to break tables into diff sub set to avoid transcation inconsistency as almost all tables are related, they want to replicate all tables in 1 sub set. will it be possible to replicate 500 tables in 1 go after every 15 minutes?

  7. #7
    Join Date
    Oct 2007
    Posts
    246
    its possible but the best partices is to create multiple sub set which will help you in full refresh , there will be no transaction inconsistency the tables can be lay in target according to user wish
    regds
    Paul

  8. #8
    Join Date
    Aug 2009
    Posts
    12
    I have heard somewhere that DB2 has a 200 tables limit per subscription set, is it still true in V9.5..? If this is true then we will have to divide tables in diff sub sets. :-(

    Now the second option ..
    Sorry I am too naive to understand you clearly :-(
    lets say we break tables into different sub set, and we replicate one set after every 15 mins and another after every 1 hour, so how is possible to maintain transcation consistency when two related tables are part of two different sub set.
    And as I said in our case we are finding it difficult to divide tables in diff sub set bcz they are related (some weired business rules) , so we are kinda stuck.

    Regards
    Sunil

  9. #9
    Join Date
    Oct 2007
    Posts
    246
    if both the sub set is running and each sub set lag time is set to 15mins then source and target lag time will be 15 mins.
    As is said prev relationship of tables in source is not going to harm you in any ways in target, i would suggest you to try out in test envi, take fews tables maybe 20 create different sub set and check
    regds
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •