Restricting DDL for a table alone in MSA database level Replication.

[DilipVoora]

repdef-and-sub-details.txtHi Experts,

I have a stored procedure that has a table 'temp_display' and the sequence of operations that happens on it are "drop, get the data into it again from main table using select into(as the EOD changes data) and then create index on it." Due to select into and index creation replication synchronization to the target is getting delayed more than an hour.

Becuase of this reason we decided not to replicate the activities that are happening on table temp_display from PDS.

I tried below options to achive the scenario but they are not helping in stopping the ddl and DSI keep going down. Is there a way to stop ddl for particular table in MSA database level replication? Please advise.

sp_setreptable ,'temp_dispaly','never' -- stopping only dml
alter replication definition with primary at PDS.PDB not replicate in (temp_display) - stopping only dml
alter connection to PDS.PDB for replicate table named temp_display set dsi_replication_ddl to 'off' - saying invalid config option

Error:

E. 2023/05/17 02:43:50. ERROR #1028 DSI EXEC(106(1) RDS1.RDB) - neric/dsi/dsiqmint.c(5094)
Message from server: Message: 1906, State 1, Severity 16 -- 'Cannot create an index on table 'temp_display', because this table does not exist in database '<RDB>'.
'.
H. 2023/05/17 02:43:50. THREAD FATAL ERROR #5049 DSI EXEC(106(1) <RDS1.RDB>) - neric/dsi/dsiqmint.c(5107)
The DSI thread for database '<RDS1.RDB>' is being shutdown. DSI received data server error #1906 which is mapped to STOP_REPLICATION. See logged data s
erver errors for more information. The data server error was caused by output command #0 mapped from input command #0 of the failed transaction.
H. 2023/05/17 02:43:50. THREAD FATAL ERROR #5273 DSI EXEC(106(1) <RDS1.RDB>) - neric/dsi/dsiqmint.c(5135)
To write the failed transaction into log, please execute 'sysadmin log_first_tran, <RDS1,RDB>'. Please analyze the transaction and provide an appropriate fix based on your analysis, then resume the connection.
I. 2023/05/17 02:43:50. The DSI thread for database '<RDS1.RDB>' is shutdown.

Attahced the database replication definition and subscription output.Command used to create database repdef:

create database replication definition <pdb_dbrepdef>
with primary at PDS.PDB
replicate DDL
replicate sqldml
replicate system procedures
go

Next thought : Create an error class, assign action to it and then alter it to the connections of RDS1 and RDS2.

Details:
ASE(PDS, RDS1,RDS2):15.7 Sp135
SRS: 15.7 SP306 ROLLUP

Regards,
Dilip Voora

[Mark_A_Parsons]

Consider updating the question to include these additional details:

• complete error message(s) related to DSI keep going down
• the complete database repdef

[DilipVoora]

Hi Mark,

Updated as advised and from my details I have it seems in MSA we can't exclude DDL for single table. Will be waiting for your response as well on this.

Regards,
Dilip Voora

[Mark_A_Parsons]

replicate sqldml encompasses replicate 'S' (select into), but the error message seems to indicate the select into is not being replicated soooo, has SQLDML been configured in the PDB? What is the output from the following (executed in the PDB):

• sp_setrepdbmode PDB
• sp_reptostandby PDB

Also, are you executing any of the following in your SQL batches in the PDB especially as it pertains to the temp_display table:

• set replication on
• set replication off
• set repmode

[DilipVoora]

I am with an understanding that, due to the exclusion of the table in database replication definition level 'select into (create + insert into)' when performed on the table "temp_display" is not replicating as needed and then I expected that the behaviour will be same for drop as well but it isn't. Correct me if this is a wrong understanding? Output of the stored procedures are below and for the latter we are not using any of those specified.

1> sp_setrepdbmode PDB
2> go

The replication mode for database 'PDB' is ' s'.
The replication threshold for 'PDB' is '20'.

1> sp_reptostandby PDB
2> go

The replication status for database 'PDB' is 'ALL'.
The replication mode for database 'PDB' is ' s'.

(return status = 0)

[sladebe]

So I got the following to work:

In the primary db

> sp_reptostandby <mydb>;

The replication status for database '<mydb>' is 'ALL'.

The replication mode for database '<mydb>' is ' off'.

sp_setrepdbmode <db>,'S','on'

sp_setrepdbmode <db>,'threshold','1'

In the repserver, enable select/into replication for the database replication definition:

alter database replication definition <db_rep_def> <br>with primary at <LOGICAL_CONNECTION>.<db> replicate 'S'  -- replicate ddl was already enabled

In the primary db, verify the setting:

> sp_reptostandby <mydb>;

The replication status for database '<mydb>' is 'ALL'.

The replication mode for database '<mydb>' is ' s'.

> sp_setrepdbmode <mydb>;
The replication mode for database '<mydb>' is ' s'.
The replication threshold for '<mydb>' is '1'. 

Then a select * into newtable from oldtable worked in the primary db, and replicated.