How to add/update a table for replication

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.