Topic
3 replies Latest Post - ‏2012-02-10T11:51:24Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic Alter table in CDC enabled replication

‏2012-02-07T00:30:04Z |
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.

source informix
target sqlserver

Any ideas.
Updated on 2012-02-10T11:51:24Z at 2012-02-10T11:51:24Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Alter table in CDC enabled replication

    ‏2012-02-08T15:57:11Z  in response to SystemAdmin
    Basically 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"

    Cheers,
    Tomas
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Alter table in CDC enabled replication

      ‏2012-02-09T22:44:31Z  in response to SystemAdmin
      Thanks, 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.
      • SystemAdmin
        SystemAdmin
        1143 Posts
        ACCEPTED ANSWER

        Re: Alter table in CDC enabled replication

        ‏2012-02-10T11:51:24Z  in response to SystemAdmin
        The 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
        Database selected.
        name test
        partnum 1048925
        owner informix
        created 02/10/2012
        is_logging 1 <--- note the '1' value; if there is a '0', database is not logged
        is_buff_log 0
        is_ansi 0
        is_nls 0
        is_case_insens 0
        flags -12288
        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
        Database selected.
        tabname tab1
        is_raw 1 <--- note the '1' value; if there is a '0', table is not raw
        1 row(s) retrieved.