Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: cancel reorg on large XML table

    Hi All,
    Has anyone experience to cancel reorg operation on large XML table (about 1TB), reason cancelled is due tot he transaction log full and
    insufficient space to grow during recreate index.
    If I would like to put back the original shape of the table (before I reorg) besides restore any other workaround solution ?

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2level - platform ?
    commands ? error messages ?
    more details would be welcome
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2013
    Posts
    80
    This is db2 9.7.0.8 in Windows 2003 and there's no particular error.
    And this table contains XML coumn.

    My command to reorg is
    reorg table <tab> resetdictionary; -- when it ran half way thru it causes transaction log full so got to cancel the reorg

    After this, all the connections to the table will be hang.

    I query out it actually stop at the recreate index

    so try to drop and create index

    but when come to create index it took more than 10 mins just to create 10K rows,
    Not sure what causing it took so long.

    But once the 10 min index creation gone thru, when I try to recreate index
    it just went thru very fast.

    That's all.
    Hopes I provide sufficient info you. Thanks a lot.

    Regards,

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is there anything interesting in db2diag.log?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2013
    Posts
    80
    not much from db2diag..in fact it is easy to simulate the steps I described in above.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    according your answer : do you expect we are going to re-produce the problem and try to debug ?
    have you opened a pmr to request some info from IBM ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Aug 2013
    Posts
    80
    Yes aldy opened but the support (level 2) answer is to try use large and small table reorg to simulate the events and compare.
    and find out whether the "release locking" is same or it will increase linearliy by size.

Posting Permissions

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