Resolving Apply ASN1001E in DB2 SQL Replication

Error Message in Apply:

2016-09-08-10.51.37.843021 ASN1001E APPLY "YHDBSUB0" : "WorkerThread". The Apply program encountered an SQL error. The ERRCODE is "B20101". The SQLSTATE is "42601". The SQLCODE is "-104". The SQLERRM is "WHERE?BEGIN-OF-STATEMENT?<variable_set>". The SQLERRP is "SQLNP01F". The server name is "". The table name is "CAS.FX_ACCNT_SERIAL_JPY".

Usually this error is seen if you have an unique index or primary key set on all the columns of the table. So that there is no non-key column to update.

If they have all columns as REPL Key cols, then any normal UPDATE will be treated as KEY UPDATE. SQL Rep doesn’t support KEY UPDATE by default setting. In other words, it means that UPDATE statement could not be replicated to the target database.

Capture will keep the change in source to CD tables, so how much change data it can keep depends on the size of tablespace the CD table belongs to.

# on target db

db2 "select varchar(TARGET_OWNER,10),
varchar(TARGET_TABLE,30),
IS_KEY
from ASN.IBMSNAP_SUBS_COLS
where TARGET_TABLE='FX_ACCNT_SERIAL_JPY'"

db2 "describe table CAS.FX_ACCNT_SERIAL_JPY"

— we can see that all 33 columns are key columns

# connect to Source and check CHG_UPD_TO_DEL_INS is N

db2 connect to YHDB

db2 "select varchar(source_owner,10),
varchar(SOURCE_TABLE,50),
CHG_UPD_TO_DEL_INS
from asn.ibmsnap_register
where source_owner = 'CAS'
and SOURCE_TABLE = 'FX_ACCNT_SERIAL_JPY'"

# Here we can determine that we need to follow these steps
# Stop Capture

asnccmd CAPTURE_SERVER=YHDBS CAPTURE_SCHEMA=ASN STOP

# connect to Source and set CHG_UPD_TO_DEL_INS to Y

db2 connect to YHDB

db2 "update asn.ibmsnap_register
set CHG_UPD_TO_DEL_INS = 'Y'
where source_owner = 'CAS'
and SOURCE_TABLE = 'FX_ACCNT_SERIAL_JPY'"

# start Capture

nohup asncap CAPTURE_SERVER=YHDBS CAPTURE_SCHEMA=ASN startmode=WARMSI autoprune=yes CAPTURE_PATH=/home/db2inst1/sqlrep pwdfile=password.ini &

# Refresh table FX_ACCNT_SERIAL_JPY in target db

db2 connect to yhdb

db2 "select varchar(SOURCE_OWNER,12),
varchar(SOURCE_TABLE,40),
varchar(TARGET_TABLE,40),
MEMBER_STATE
from ASN.IBMSNAP_SUBS_MEMBR
where (SOURCE_OWNER = 'CAS'
and SOURCE_TABLE = 'FX_ACCNT_SERIAL_JPY')
or (SOURCE_OWNER = 'DB2INST1'
and SOURCE_TABLE = 'TESTREP'
) WITH UR"

— State should be S

# set MEMBER_STATE to N for corresponding tables

db2 "UPDATE ASN.IBMSNAP_SUBS_MEMBR
SET MEMBER_STATE='N'
where SOURCE_OWNER = 'CAS'
and SOURCE_TABLE = 'FX_ACCNT_SERIAL_JPY'"

db2 "UPDATE ASN.IBMSNAP_SUBS_MEMBR
SET MEMBER_STATE='N'
where SOURCE_OWNER = 'DB2INST1'
and SOURCE_TABLE = 'TESTREP'"

# We may need to restart Apply as well
# If the apply process failed to respond to the stop command, use kill -9

asnacmd CONTROL_SERVER=YHDBT APPLY_QUAL=YHDBSUB0 stop
nohup asnapply apply_path=/home/db2inst1/sqlrep CONTROL_SERVER=YHDBT APPLY_QUAL=YHDBSUB0 pwdfile=password.ini &

# Identify other tables that may have similar problems

db2 "select c.TABNAME, count(distinct c.COLNAME), count(distinct a.COLNO)
from SYSCAT.COLUMNS c,
ASN.IBMSNAP_SUBS_COLS a
where c.tabschema = 'CAS'
and a.TARGET_OWNER = 'CAS'
and c.TABNAME = a.TARGET_TABLE
and a.IS_KEY = 'Y'
group by c.TABNAME
having count(distinct c.COLNAME) = count(distinct a.COLNO)
with UR";

--
FX_ACCNT_SERIAL_JPY 33 33
KON_CURRENCY 5 5
KON_PAIR 7 7
KTP_CL_CONFIRM 14 14
--

Reference:

# On target db, check Key columns in target table

db2 "select varchar(TARGET_OWNER,10),
varchar(TARGET_TABLE,30),
IS_KEY
from ASN.IBMSNAP_SUBS_COLS
where TARGET_TABLE='KTP_CL_CONFIRM'"

# check sync state on target db, All MEMBER_STATE should be S, meaning Synchronized

db2 "select varchar(SOURCE_OWNER,12),
varchar(SOURCE_TABLE,40),
varchar(TARGET_TABLE,40),
MEMBER_STATE
from ASN.IBMSNAP_SUBS_MEMBR
order by MEMBER_STATE
WITH UR"
http://www-01.ibm.com/support/docview.wss?uid=swg21385876
Apply program fails with ASN1011E

http://www.ibm.com/support/knowledgecenter/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrslodonetable.html
Refreshing one table in a multi-table subscription set

http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_61/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrssyscommandovu.html
System commands for SQL replication
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s