Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    56

    Unanswered: Data Replication causes locks on the source server?

    I've installed and created a whole environemnt using DB2 V7.1 Capture/Apply (pull).
    Everything works fine but... since I started (sleep time = 30 min) DB2 on the source side gets locks.
    I don't understand why, if I'm reading logs (not live tables) on the source side I would cause locks? I agree that on the target side the tables involved on the subscription set would be locked since I would be inserting/deleting/updating.

    Is that true? (cause dead locks on the source side)
    Is there ant parameter that I could use to avoid this? I'm using apply with LOADXIT option.

    Thanks

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    its should be approx. 30 sec not min.
    chk for commit_interval
    Set this value to a value less than the DB2 lockout value to prevent contention between the Capture worker and pruning threads.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2007
    Posts
    56

    Data Replication causes locks on the source server

    I'm sorry but what should be 30 sec? The sleep time I'm referring to is Capture sleeptime.
    Where should I find the commit interval parameter to be set up?
    I sitll don't understand how Capture or Apply can lock source database so bad that cause dead locks (lock escalation) if Data Replication doesn't lock any user table on the source side.

    Is there anything I could do to avoid this?
    Please help me.

    Thanks

  4. #4
    Join Date
    Jun 2006
    Posts
    471
    capture will only create locks on control and cd-tables
    apply will only create locks on control, cd and target tables
    no process is accessing source tables
    how did you discover this. is it not possible that another application is causing the lock. have you taken a snapshot when lock occured
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    May 2007
    Posts
    56

    Data Replication causes ....

    Guy Przytula,

    I set up a script that issues commands to check for locks. Also we have this script sending e-mails alerts to DBA team every time we have some critical situation.
    Yesterday I was running Capture and Apply (with LOADXIT option) and I notice lots of locks on UOW and CD tables ...another application was competing for DB2 catalog...but look what I got:

    Service: db2 locks
    Host: elmer
    State: CRITICAL

    Date/Time: Wed Jun 13 11:40:20 EST 2007

    Additional Info: DB2OWNER ASNLOAD 430 172.29 .14 .116 06/13/07 16:29:19 - EMCFAC 5 is holding exc locks. table list: CD20070514118136 CD20070514208985 CD20070514701966 CD20070514949565 IBMSNAP_UOW ORDER_LOG PRODUCT_ORD_MASTR SOPS_GROUP_T SOPS_PROCESS_T STAR_CPE_SITE_SURV SYSCONSTDEP SYSFUNCTIONS SYSTABCONST

    I TOTALLY agree with you that Capture and Apply for design should never lock source user tables..or cause lock escalation. Should I tune DB2 up to avoid this?

    Thanks so much for you reply.

  6. #6
    Join Date
    Jun 2006
    Posts
    471
    the locks are held by the application asnload
    this program is only invoked to get a full refresh and in that case we read source data and copy that to target
    why do you have a fullrefresh ?
    have you looked in asn.ibmsnap_applytrail if there have been full refresh ?
    or is the replication setup : always full refresh instead of data changes
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  7. #7
    Join Date
    May 2007
    Posts
    56

    Data Replication causes locks...

    Guy,

    Thanks for your help.
    Looks like based on your previous reply, that I'm doing a full refresh.
    I agree with you that was the cause of my lock. How can I avoid this?
    Let's start from scratch. I'll be starting Capture (warm or cold?) and then apply without the LOADXIT.
    My question is how can I avoid to read the user source tables again? Is there any parameter at Capture or/and Apply invokation time or in the control tables that could cause a full refresh?

    I promise it will be my last question :-)

    Thanks
    Ronaldo

  8. #8
    Join Date
    Jun 2006
    Posts
    471
    the loadxit parameter is only to indicate that you use your own exit program to execute the full refresh
    full refresh can be disabled : see asn.ibmsnap_register and the column disable_refresh (1=no full refresh) = apply will show errors if full refresh is needed
    the coldstart of capture will always force a full refresh
    the first start of capture after subscription will full refresh the target table to have this inline with source
    maybe try a re-synch of the subscription set (see replication center - subscription set - full refresh manual - do not execute the export/import)
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  9. #9
    Join Date
    May 2007
    Posts
    56
    I just restore a full backup from source database into target database, now I want to start Capture / Apply but I don;t want full refresh, because I have more than 400 tables (some with many millions of rows) and I tired couple times and my target log DB2 got full even I increasing to the maximun allowed.
    I know how to disable a full resfresh, update the pruncntl with binary zeros etc and also know that if a full refresh is needed I will get a error message etc.
    My question is ...for those tables that apply gives me that eeror (disable full refresh) they will have changes captured even with this message or is something that I I will never capture changes until I perform a full refresh?

    Thanks

  10. #10
    Join Date
    Jun 2006
    Posts
    471
    if full refresh is triggered, capture will not capture anymore - wait for full refresh
    do the update of pruncntl and subs_set (resynch) and this will start capture and apply again without errors anymore
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

Posting Permissions

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