how to alter a table in source (informix) and continue the replication by setting the mark capture point?
I keep getting -19816: Cannot perform this operation on a table defined for replication
I have stopped mirroring from console management.
Tried stopping source instance.
tried stopping source and target instance.
This topic has been locked.
3 replies Latest Post - 2012-02-10T11:51:24Z by SystemAdmin
Pinned topic Alter table in CDC enabled replication
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-02-10T11:51:24Z at 2012-02-10T11:51:24Z by SystemAdmin
Re: Alter table in CDC enabled replication2012-02-08T15:57:11Z in response to SystemAdminBasically the error -19816 is returned when you try to alter a table which is participating in ER (i.e. has a replicate defined on it) or is being captured by CDC.
So basically before the alter you need to stop the capturing application. If it's written according to the IBM Informix CDC documentation, it should unset the internal flag on the table by calling the stored procedure syscdcv1:cdc_set_fullrowlogging(). Once the flag is unset, you should be able to alter the table. If the application doesn't call the cdc_set_fullrowlogging(), you can call it manually (see http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.cdc.doc/ids_cdc_059.htm).
To check whether the flag is actually set or not, you can use 'oncheck -pt <dbname>:<tabname>' at the Informix side - in case it is set, it's indicated by following message in the first part of the output:
"TBLspace flagged for External Log Snooping"
Re: Alter table in CDC enabled replication2012-02-09T22:44:31Z in response to SystemAdminThanks, I could alter the table utilizing cdc_set_fullrowlogging() function.
However After I update the table, Infoshpere CDC subscription does not update the target table when source is updated.
I get -83706 error The table properties do not support capture: it is a temporary table, a view, or otherwise not logged.
oncheck -pt output shows the Log Snooping remarks.
Re: Alter table in CDC enabled replication2012-02-10T11:51:24Z in response to SystemAdminThe error "The table properties do not support capture: it is a temporary table, a view, or otherwise not logged" is returned in the case
the table you are trying to capture data from is a view, a temporary table, a raw (non-logged) table, a view to remote database, an external table or a VTI table; also in case you didn't set the fullrowlogging back to ON; and finally in case the database where the table resides is not logged. The last case can be checked by following SQL query:
$ echo "select * from sysdatabases where name='test'"|dbaccess sysmaster
is_logging 1 <--- note the '1' value; if there is a '0', database is not logged
1 row(s) retrieved.
To check whether the table is a raw (non-logged) one, you can run:
$ echo "select tabname, sysmaster:bitval(flags,'0x10') is_raw from systables where tabname='tab1'"|dbaccess test
is_raw 1 <--- note the '1' value; if there is a '0', table is not raw
1 row(s) retrieved.