Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2016
    Posts
    29

    Unanswered: how to overcome downtime when db2 alter table is performed

    Hello ALL,

    I have asked by my customer, is it possible for "no downtime or minimizing the downtime" when alter table is performed.

    In our environment deployments happen from WAS to db2 db where alter table is performed for couple of tables. As we know if alter table is performed Exclusive lock will happen and application will not able to access the table.

    Our architect told to check can we have "primary and secondary" databases with HADR or any other sync technique and perform the alter table without downtime ?

    Then I asked: If we perform alter on table in secondary DB which activity takes for 20 min for example, how can we move 20 Min data/transactions to secondary and make it primary, to do deployment on primary. And also if Alter table have applied on secondary "will data compatible to copy from primary to altered secondary ? "

    Our architect : Is it not possible to capture data for 20 min changes and insert to altered secondary ? "Which i am not sure"


    Below are my doubts.
    ==============
    1.) Is above request works/possible ?
    2.) In HADR secondary database can be accessed for deployment ? if not suggest me any DB sync methods.
    3.) After altering table in secondary, can we copy messing data and make it primary without downtime or less downtime?

    Please suggest any possible ways.

  2. #2
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    1) Not possible with HADR
    2) Consider Q replication, sql replication -- higher version 10.1 onwards DDL changes can be replicated not sure have option to disable it
    3) How u r going to alter secondary, you cannot in HADR

  3. #3
    Join Date
    Apr 2016
    Posts
    29
    Thanks Mpaul,

    If i am using Q replication between Adb & Bdb databases for example.
    For our deployment i have stopped replication, and added new columns on Bdb which took 20 min for example and Adb is active.
    And enabled replication, will Q replication replicates missing data of 20 min to Bdb, or as table got altered replication will fail ?

    Thanks,

  4. #4
    Join Date
    Dec 2013
    Location
    The Great State of South Dakota
    Posts
    6
    Quote Originally Posted by arjun1217 View Post
    Thanks Mpaul,

    If i am using Q replication between Adb & Bdb databases for example.
    For our deployment i have stopped replication, and added new columns on Bdb which took 20 min for example and Adb is active.
    And enabled replication, will Q replication replicates missing data of 20 min to Bdb, or as table got altered replication will fail ? ,
    Qrep will resume from the restart point that is stored in the Restart MQ Queue. So the changes will replicate the 20 minutes. Also, Qrep will replicate DDL changes.

    Edit
    I had created a hyperlink in my comment but I can see the link is not clearly highlighted. Here is the url discussing Qrep and DDL changes. https://www.ibm.com/support/knowledg...ddlhandle.html

  5. #5
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    One problem you are going to have trying it with two servers, is that at one point, when you need to get them both in sync again, you are going to have down time.

    You can look into using the procedure ADMIN_MOVE_TABLE to see if this will do what you want. I do not know how well it behaves with HADR.

    Andy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •