How to add/update a table for replication
– First you need to create two scripts one to drop the existing subscription and replication definition and one to create the new table, replication definition and subscription.
– You also need to extract the exiting replicant table and also a user, e.g. origomw, if requested and save this info.
– Before stopping replication send an email to operations to let them know of the planned downtime.
– At this point you should suspend the replication server connection from DB2, if it’s not already down and wait 5 minutes to make sure everything is applied.
– Do a select count(*) against an existing replicant table and compare this with a wc –l of the bcp file to make sure they match in number. For a new table this is not relevant.
– Bcp out the existing table just in case something goes wrong.
– Drop a user if that was requested
– Drop subscription, rep def in rep server:
o drop subscription DB2_ASN2_RESOURCE_NB_s2 for DB2_ASN2_RESOURCE_NB_d2
o with replicate at origo_test.deFaktoReplica
o without purge
o go
o
o drop replication definition “DB2_ASN2_RESOURCE_NB_d2”
o go
– Drop the table in ASE
o Use database deFaktoReplica
o Go
o Drop table ASN2_RESOURCE_NB
o Go
– Create the table again
– Bcp the data in with a command like:
o bcp deFaktoReplica.dbo.ASN2_RESOURCE_NB in /home/origo/deFaktoReplica/init/data/SYS3.ASN2.DDX -r ” \n” -t “\t” -c -Jiso_1 -Sorigo_test –Usa –P<password> -e bcplog.txt -z -b 10000 -m 100000
o If you get problems check that the number of columns etc match and also check to see whether the row/tab delimiter is correct, sometimes there is an extra tab in the row delimiter, in that case try using –r “\t \n”.
– Add the primary key, triggers and other indexes etc into the table, and possibly also a rep server specific column like changed_date.
– Add the origomw user to the database.
– Add the replication definition and subscription.
o create replication definition “DB2_ASN2_RESOURCE_NB_d2”
o with primary at TSTA.P825RAD1
o with primary table named “DB2_ASN2_RESOURCE_NB”
o with replicate table named “ASN2_RESOURCE_NB”
o (
o “RESR_ELMT_ID” int,
o “RECORD_EFF_END_DT” datetime,
o “RECORD_EFF_END_TM” datetime,
o “RECORD_EFF_STR_DT” datetime,
o “RECORD_EFF_STR_TM” datetime,
o “SRVC_LOC_ID” numeric,
o “RESR_TYPE” char(6),
o “RESR_ID” char(22),
o “ACCT_ID” numeric,
o “RESR_EFF_STR_DT” datetime,
o “RESR_EFF_END_DT” datetime,
o “ORD_ITEM_ID” numeric,
o “FST_USG_DT” datetime,
o “REINSTATE_CD” char(4),
o “REINSTALL_DT” datetime,
o “DISCN_CD” char(4),
o “ASN_RESR_DISCN_DT” datetime,
o “VERBAL_TRANSLATION” char(20),
o “COMMENT_ID” numeric,
o “REF_SEQ_NUM” numeric,
o “INIT_INSTALL_DT” datetime,
o “LAST_CHANGE_DT” datetime,
o “RESR_GRP_TYP” char(6),
o “ORD_ITEM_SEQ” smallint,
o “PRIORITY_CD” char(1),
o “SUB_STATUS_CD” char(2),
o “PRMRY_COMP_CD” char(6),
o “SECNDRY_COMP_CD” char(6),
o “NUFS_NET_SRVC_TYP” char(3),
o “NUFS_NUM_CAT” char(2),
o “USER_ID” char(8),
o “PREV_PHONE_NUM” char(8),
o “RSU” char(6),
o “PRMRY_GRP” smallint,
o “FSL” char(6),
o “CALL_INTERCEPT” char(1),
o “RESR_SUB_GRP” char(1),
o “OWNER_ACCT_ID” numeric,
o “OWNER_SRVC_LOC_ID” numeric,
o “PAYER_REF” char(30),
o “PAYER_KURT_ID” int,
o “CREATORS_REFERENCE” char(25),
o “UPDT_LAST_MOD_TS” datetime
o )
o primary key
o (
o “RESR_ELMT_ID”,
o “RECORD_EFF_END_DT”,
o “RECORD_EFF_END_TM”
o )
o searchable columns (RECORD_EFF_END_DT)
o go
o
o
o define subscription DB2_ASN2_RESOURCE_NB_s2
o for DB2_ASN2_RESOURCE_NB_d2
o with replicate at origo_test.deFaktoReplica
o where RECORD_EFF_END_DT = ’31 dec 9999′
o go
– Activate the subscription with:
o activate subscription DB2_ASN2_RESOURCE_NB_s2
o for DB2_ASN2_RESOURCE_NB_d2
o with replicate at origo_test.deFaktoReplica
o go
– Validate subscription with:
o validate subscription DB2_ASN2_RESOURCE_NB_s2
o for DB2_ASN2_RESOURCE_NB_d2
o with replicate at origo_test.deFaktoReplica
o go
– Resume replication and ask DB2 admin to start the Repagent on their side.