Problems with setting up replication

[Norbert Munkel]

Hello,

Replication setup:

Server 1: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst2
database: master
Server 2: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst1
database: slave

The target-tables exists and are populated with data. Replication type:
user-copy.

Server 1 captures and holds the capture control- and cd-tables.
Server 2 applies and holds apply-control tables.

Problem 1: As the target tables are populated, I would like to do
"manual refresh" and created the registrations with unchecking the
"allow full refresh" option.

If I use ReplicationCenter->subscription-set->full refresh->manual I get
an empty Form with no script generated at all. Furthermore I can't
choose any servers from the "run specifications"-Field.

How can I activate the subscription manually without using the control
center to avoid the need for automatic full refresh and make capture
start running?

Problem 2: Adding Members to subscription sets / creating subscription
sets. This works exept for one table called "external_ref". There I get
the following error:

ASN1560 The replication action ended in error. An SQL error was
encountered SQL Messag: "[IBM][CLI Driver][DB2/LINUX] SQL0802N
Arithmetic overflow or other arithmetic exception occured. SQLSTATE=22003".

Any hint would help. Thanks!

regards,

Norbert

[Mark]

Norbert Munkel <[e-mail]> wrote in message news:<zR**************@se2-cb104-9.zrh1.ch.colt.net>...

Hello,

Replication setup:

Server 1: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst2
database: master
Server 2: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst1
database: slave

The target-tables exists and are populated with data. Replication type:
user-copy.

Server 1 captures and holds the capture control- and cd-tables.
Server 2 applies and holds apply-control tables.

Problem 1: As the target tables are populated, I would like to do
"manual refresh" and created the registrations with unchecking the
"allow full refresh" option.

If I use ReplicationCenter->subscription-set->full refresh->manual I get
an empty Form with no script generated at all. Furthermore I can't
choose any servers from the "run specifications"-Field.

How can I activate the subscription manually without using the control
center to avoid the need for automatic full refresh and make capture
start running?

Problem 2: Adding Members to subscription sets / creating subscription
sets. This works exept for one table called "external_ref". There I get
the following error:

ASN1560 The replication action ended in error. An SQL error was
encountered SQL Messag: "[IBM][CLI Driver][DB2/LINUX] SQL0802N
Arithmetic overflow or other arithmetic exception occured. SQLSTATE=22003".

Any hint would help. Thanks!

regards,

Norbert

In my experience, "manual refresh" script builder does not work
properly. IBM support had the same problem when I called them, but I
found a workaround (see below)and did not pursue the problem any
further.

You can do an "automatic" refresh, which will do one refresh at the
next replication interval cycle. You can also create a script to lower
the refresh cycle to 1 minute and change it back as before after the
automatic refresh.

CONNECT TO XXXXXXXX ;

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE=0 WHERE SET_NAME='SUB_SET1'
AND APPLY_QUAL='SUB_APPLY' AND WHOS_ON_FIRST='S';

UPDATE ASN.IBMSNAP_SUBS_SET SET LASTSUCCESS=NULL, SYNCHPOINT=NULL,
SYNCHTIME=NULL WHERE APPLY_QUAL='SUB_APPLY' AND SET_NAME='SUB_SET1'
AND WHOS_ON_FIRST='S';

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE=1 WHERE SET_NAME='SUB_SET1'
AND APPLY_QUAL='SUB_APPLY' AND WHOS_ON_FIRST='S';

COMMIT ;

[Fred Nurk]

Why don't you use HADR in V8.2 instead? Much easier and no messy control
tables, etc to worry about.

Cheers,

"Norbert Munkel" <[e-mail]> wrote in message
news:zR**************@se2-cb104-9.zrh1.ch.colt.net...

Hello,

Replication setup:

Server 1: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst2
database: master
Server 2: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst1
database: slave

The target-tables exists and are populated with data. Replication type:
user-copy.

Server 1 captures and holds the capture control- and cd-tables.
Server 2 applies and holds apply-control tables.

Problem 1: As the target tables are populated, I would like to do
"manual refresh" and created the registrations with unchecking the
"allow full refresh" option.

If I use ReplicationCenter->subscription-set->full refresh->manual I get
an empty Form with no script generated at all. Furthermore I can't
choose any servers from the "run specifications"-Field.

How can I activate the subscription manually without using the control
center to avoid the need for automatic full refresh and make capture
start running?

Problem 2: Adding Members to subscription sets / creating subscription
sets. This works exept for one table called "external_ref". There I get
the following error:

ASN1560 The replication action ended in error. An SQL error was
encountered SQL Messag: "[IBM][CLI Driver][DB2/LINUX] SQL0802N
Arithmetic overflow or other arithmetic exception occured. SQLSTATE=22003".
Any hint would help. Thanks!

regards,

Norbert

[Gert van der Kooij]

In article <zR**************@se2-cb104-9.zrh1.ch.colt.net>, Norbert
Munkel ([e-mail]) says...

Hello,

Replication setup:

Server 1: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst2
database: master
Server 2: DB2 UDB Express 8.2 on SuSE Linux SLES 8
Instance: db2inst1
database: slave

The target-tables exists and are populated with data. Replication type:
user-copy.

Server 1 captures and holds the capture control- and cd-tables.
Server 2 applies and holds apply-control tables.

Problem 1: As the target tables are populated, I would like to do
"manual refresh" and created the registrations with unchecking the
"allow full refresh" option.

If I use ReplicationCenter->subscription-set->full refresh->manual I get
an empty Form with no script generated at all. Furthermore I can't
choose any servers from the "run specifications"-Field.

How can I activate the subscription manually without using the control
center to avoid the need for automatic full refresh and make capture
start running?

Maybe the ASNCLP command can help. I never used it so it's just a
guess. It's described in the pdf
http://publib.boulder.ibm.com/epubs/pdf/c1894100.pdf

[Norbert Munkel]

Hi,

Fred Nurk wrote:

Why don't you use HADR in V8.2 instead? Much easier and no messy control
tables, etc to worry about.

As I understand, HADR is fine for HA (name says it). Nice for
desaster-recovery but not the right tool to have replicas that can be
accessed (read and write). So SQL-replication seems to be what I need.
I currently use log-shipping for desaster-recovery. HADR is next if my
SLAs are going to get tighter.

Thanks anyway,

Norbert

[Norbert Munkel]

Hi,

Mark wrote:

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE=0 WHERE SET_NAME='SUB_SET1'
AND APPLY_QUAL='SUB_APPLY' AND WHOS_ON_FIRST='S';

UPDATE ASN.IBMSNAP_SUBS_SET SET LASTSUCCESS=NULL, SYNCHPOINT=NULL,
SYNCHTIME=NULL WHERE APPLY_QUAL='SUB_APPLY' AND SET_NAME='SUB_SET1'
AND WHOS_ON_FIRST='S';

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE=1 WHERE SET_NAME='SUB_SET1'
AND APPLY_QUAL='SUB_APPLY' AND WHOS_ON_FIRST='S';

COMMIT ;

this would bring asnapply to do a full refresh automatically, wouldn´t it?
As my Tables are very big I would like to do the inital sync myself
(export/load) and then tell the apply process that it is done and it can
start the normal (insert/update/delete) behaviour.

regards,

Norbert