Informix

 View Only
  • 1.  Cannot perform this operation on a table defined for replication

    Posted Thu June 10, 2021 10:28 AM

    On our system we have certain tables in one of our main databases that are associated with "data capture" and full-row logging (a.k.a., "log snooping").

     

    The "cdc_set_fullrowlogging" command can be used to either enable full-row logging or disable full-row logging. My experience has been that only the "informix" user can successfully run this command. If we have another user on the same database that has DBA permissions, then even if that user can create tables, etc., it cannot successfully run this "cdc_set_fullrowlogging" command. Does anyone have an insight on why this is true or how to overcome this problem?

     

    Here is a link that describes this command:

    https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.cdc.doc%2Fids_cdc_059.htm

     

    clerk11s

    John Dargan, Database Administrator II - Information Technology

    LAURA E. ROTH, Clerk of the Circuit Court

    Seventh Circuit, Volusia County, Florida

     

     

     

     

     


    #Informix


  • 2.  RE: Cannot perform this operation on a table defined for replication

    IBM Champion
    Posted Thu June 10, 2021 10:31 AM
    CDC control operations are server level operations not database level, so a user would have to be a DBSA user rather than (or in addition to) being a simple DBA or database owner. DBSA users are users who are part of the informix OS group on Linux/Unix or listed in the ONCONFIG as a DBSA user which is the preferred method.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Cannot perform this operation on a table defined for replication

    IBM Champion
    Posted Thu June 10, 2021 10:49 AM

    I would expect the users in the DBSA group to be able to run the command.  If that is not the case then I would raise a PMR to double check whether it should work or not

     

    Cheers

    Paul

     






  • 4.  RE: Cannot perform this operation on a table defined for replication

    IBM Champion
    Posted Thu June 10, 2021 11:33 AM
    What error does the user get when trying to execute cdc_set_fullrowlogging?  Could be lack of permissions.

    The user will need DBA permission in the syscdcv1 database in order to be able to connect to the database and execute the cdc_set_fullrowlogging function.  Or just have CONNECT permission to the database and EXECUTE permission on the function itself.

    Also be sure to run the function in the syscdcv1 database and not the regular database, eg.:

    execute function syscdcv1:cdc_set_fullrowlogging ("stores_demo:informix.mwrepl",1);

    Mike

    ------------------------------
    Mike Walker
    ------------------------------



  • 5.  RE: Cannot perform this operation on a table defined for replication

    IBM Champion
    Posted Thu June 10, 2021 12:17 PM
    ... and be sure to not execute this function with '0' ("off") paramter while the table still is being captured.

    The correct sequence is:
     - stop capture
     - turn off full row logging
     - perform your alters
     - turn full row logging back on
     - start new capture session

    Just saying as it currently still is possible differently and possibly looking right and a good way to get around that error, yet it might yield undesirable effects ;-)
    You shouldn't continue capturing across a schema change.  You would be able to capture across the point of the schema change afterwards if the alters were "in-place alters". (With "slow alters" I guess the behavior is somewhat undefined, but probably not good.)
    Ideally your capture session had captured all recent changes, so up to current log position, as the initial step zero in above list.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 6.  RE: Cannot perform this operation on a table defined for replication

    Posted Fri June 11, 2021 09:12 AM
    Yes, that is the correct sequence! 
    I would add to resolve the In-Place Alter, as, depending on your version, you might run into APAR IT36873: in-place alter table can lead to a flood of page out-of-seq errors in online.log when using CDC, which will freeze your system forcing for a hard reboot. I found this one on version 12.10.FC12.
    My 0.02...

    Ramon

    ------------------------------
    Ramon Rey
    ------------------------------



  • 7.  RE: Cannot perform this operation on a table defined for replication

    IBM Champion
    Posted Fri June 11, 2021 12:40 PM
    Hi Ramon,

    I'd slightly object to this statement as I think I know that that problem arose from not following that sequence ;-)

    CDC as such should be able to support outstanding in-place alters, so there's no need to resolve those - of course nothing should keep you from doing that, when convenient, if only to avoid the extra cost that's associated with every access to an old version page's rows, and to rule out any potential in-place alter prolbems down the road (however unlikely).

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 8.  RE: Cannot perform this operation on a table defined for replication

    Posted Thu June 17, 2021 11:59 AM
    In my case and specific version (IDS 12.10.FC12), if I follow the sequence, and do an update on a row afterwards, the bug (APAR IT36873 - https://www.ibm.com/support/pages/apar/IT36873) would force me to do a hard reboot of the machine, as the box would become completely frozen within 30 seconds of the issuance of the update statement. IBM support has been able to reproduce this behavior and provided me with a patch ("special build") that fixes this issue, which I'm still testing. 
    I'll test for this behavior in 14.10.xCy and report back.
    Again, $0.02.

    R​

    ------------------------------
    Ramon Rey
    ------------------------------