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

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.

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

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

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.

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

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

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)

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

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