Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Change Data Capture (CDC) putting DB2 z/OS spaces in AREO status

    Posted Tue September 24, 2024 02:17 PM

    Wanted to see if anyone had similar issues and how to resolve it.  Recently purchased Change Data Capture which will collect data from our DB2 z/OS Z16 (v12/v13 tablespaces).   Everytime a database change is made to a table, that space immediately goes into AREO status(advisory reorg) and the CDC collector stops until a reorg has completed.  Wanted to see if anyone experienced similar situations and if they were able to resolve it somehow.  



    ------------------------------
    Kristin Rangel
    Senior Project Manager - Data, Governance & Master Data Management
    NJ Courts
    Trenton
    ------------------------------


  • 2.  RE: Change Data Capture (CDC) putting DB2 z/OS spaces in AREO status

    Posted Wed September 25, 2024 03:50 AM

    Hi Kristin,

    generally the answer is yes in terms of replication suspending but my experience is a remapping of the table and then a restart of the sub is needed. I have not specifically checked for an AREO status and things working post a reorg.

    Most column format or data type changes will cause the subscription to fail and the table has to be remapped and subscription restarted.  When adding columns the setting "Add Column schema change" i.e. ADDCOLUMNISSCHEMACHANGE plays a part. A setting of No continues replication with no data replicated for the new column only. Also the behaviour is different if the underlying table is in BRF format or RRF format.  In any case if your backout was to drop the newly added column then that would definitely break the subscription even if the new column was not a part of the table mapping in subscription. If any change needed a table to be dropped and recreated then that would also break the subscription and a remapping will be needed before a restart. so there are considerations. 



    ------------------------------
    Dhiren Chaudhary
    Natwest Group
    ------------------------------

    Original Message


  • 3.  RE: Change Data Capture (CDC) putting DB2 z/OS spaces in AREO status

    Posted Wed September 25, 2024 03:53 AM

    Hi!

    Silly question I know but when you say "a data base change" there are for me two meanings:

    1) An ALTER to the schema of the table/tablespace

    2) A change to the user data by INSERT, UPDATE, DELETE

    If you mean (1) then that is how Db2 works - not just with CDC! Any change to a schema will always put the tablespace into Advisory Reorg Pending to force the DBA to actually action the change throughout the tablespace by doing a REORG. This has the side affect of causing data propagator (CDC) to stop as it cannot handle this "state" of the table/tablespace at all! If you think about it, the only thing it has to work with is the Db2 log and it "knows" nothing about the change to the table/tablespace that was done. This then forces the CDC to be off until the required REORG is done and the changed table/tabelspace gets reregistered/updated in the CDC system. You could also have a look at the ZPARM RESTRICT_ALT_COL_FOR_DCC with options YES/NO and default NO. It specifies whether to allow ALTER TABLE ALTER COLUMN for CDC tables. If it is set to YES you will get an SQLCODE -148 if ALTERing a CDC object using any of the options SET DATA TYPE, SET DEFAULT or DROP DEFAULT. This is to stop changes that switch off CDC by being made by accident. These things have to planned in advance!

    If you mean (2) then I have no idea - as that should not be!

    Hope that helps a bit!



    ------------------------------
    Roy Boxwell
    Senior Software Architect
    Software Engineering GmbH
    Duesseldorf
    +4921196149675
    ------------------------------

    Original Message


  • 4.  RE: Change Data Capture (CDC) putting DB2 z/OS spaces in AREO status

    Posted Wed September 25, 2024 09:01 AM

    Thank you both for responding.  Yes, I was referring to a simple ALTER TABLE ADD COLUMN or ALTER TABLE ALTER COLUMN execution.  What you said makes complete sense, change happens, the logs know about it, but the tool now does not know what to do therefore stops the collector.  

    We were just trying to figure out a possible easier way to tackle these changes now, since where I work, we have always had a very flexible database deployment schedule.  Now that we need to incorporate reorgs into changes and also restart the collector, we're trying to figure out a scheduled deployment cycle now.   Always fun =)



    ------------------------------
    Kristin Rangel
    Senior Project Manager - Data, Governance & Master Data Management
    NJ Courts
    Trenton
    ------------------------------

    Original Message