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 --
# 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