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

[Mark_A_Parsons]

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

repdef-and-sub-details.txt

[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.

[Mark_A_Parsons]

First (easy) solution would be to wrap all PDB operations (that you do not want replicated) in a pair of set replication off/on commands, eg:

insert into data_table ...

set replication off

drop table temp_display
select into / temp_display
create index / temp_display

set replication on

insert into data_table ...

WARNING: Incorrect/Indiscriminate use of the set replication command can lead to the RDB becoming out of sync.

----------------

Second (easy) solution would be to configure the DSI to ignore the #1906 error (unable to create index). Keep in mind this merely masks the symptom, including scenarios where you would want the #1906 to halt replication (eg, due to some other DDL replication issue). Other issues/symptoms may still pop up that'll need to be addressed.

----------------

Third solution would be to move tables (that you do not want replicated) to a separate database that has not been marked for replication. While you could just (re)use a temp database for this operation I would suggest a non-temp database if simply because I would not want to have to worry about the EOD process filling up a temp database thus causing problems for other PDS processing.

----------------

Fourth solution would be to figure out if there's a combination of configurations that would do what you want, but that would require knowing more about the overall replication requirements, eg:

• PDB is configured (repmode = 's'; replicate sqldml) to replicate select into; is this still true even if temp_display is removed from replication?
• PDB is configured to replicate DDL (otherwise the DSI could not go down with an error re: unable to run create index command); what DDL commands (not related to temp_display) need to be replicated?

Best bet at this point would be to run a series of tests to see which (if any) configurations would allow you to selectively replicate the drop table / select into / create index commands.

[DilipVoora]

Hi Mark,

Thanks for all the solutions. As I said we implemented the error class(with error action - ignore) and considering the fact that there are chances that target database will be out of synch if the drop has to be replicated we planned to implement 'set replication off for the SQL which we don't want to replicacte in the stored procedure.

Regards,
Dilip Voora

[sladebe]

1> sp_setrepdbmode PDB  -- enable/disable statement replication at the db level
The replication mode for database 'PDB' is ' s'.  -- enable select/into statement replication
The replication threshold for 'PDB' is '20'.  -- only if at least this num of rows affected

The way I read the doc page, the '20' means

the minimum number of rows that a replicated SQL statement must impact before SQL statement 
replication is activated

So change the 20 to 1 to make it actually uses statement replication for select/into? (zero means use default of 50).

sp_setrepdbmode PDB,'threshold','1'

And replication should be enabled for the temp_display table?

Note that I don't think you can use "select/into" inside a transaction (link)

If this works, this would actually be really useful for me. For years, I've been telling db users they can't run select/into without breaking replication. If I can just do

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

to let them use select/into with replication, that would be great.

Ben

[DilipVoora]

Hi Ben,

sp_setrepdbmode is already set to 'S' as we have 'select into' involved in multiple other stored procedures that are part of the nightly/ EOD job. This thread is opened mainly to understand the ways to stop replicating the select into and drop that are part of the stored procedure when statement level replciation is enabled for 'S' for PDS.

Now, we had implemented an error class to achieve synchronization (temporarily) and we are planning to recompile the stored procedure by including 'set replication off ' for the statements that doesn't need to be replicated.

Regards,
Dilip Voora