Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: partitioned table

    db2 ese 9.5 fp9 on P/server
    we have partitioned tables and rotating these each month by use of a script
    we never had a problem with this procedure
    recently we notice : when adding a new partition : partition PART05 already exists.
    we execute : detach partition
    export detached table
    check syscat datapartitions where schema=tableschema and name=tablename and partitionname=partition name of partition we detached
    we wait for entry to disappear
    the script leaves the loop (no entry in syscat anymore with this names) and attach the new partition for next month
    what would be the safest way to check if the detach is completely finished (even rebuild indexes) and new attach can be done ?
    we have acc/prd environment and sometimes this occurs in acc and sometimes in prd..
    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

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    Is the issue that the partition is taking a long time to detach or is it never detaching?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The entry in SYSCAT.DATAPARTITIONS is supposed to be removed after the asynchronous index cleanup completes. May be you can add a small delay, 30-60 seconds after the record is removed to account for whatever synchronization issues there may be.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Apr 2012
    Posts
    156
    Just double checked a script I wrote a while ago for doing the same thing and if I understand you correctly, the partition is detaching, you are just getting hung up by the asynchronous index cleanup. The time required for this is dependent on the sizes of your non-partitioned indexes, so a simple sleep is hit or miss. Anyway, I checked my script and I have a loop that runs after the detach. I run a db2 list applications show detail , then check the output for Asynchronous Index Cleanup, if found my script sleeps for 60 seconds, then does another list applications show detail (you could do a find on the index or table name). When the asynchronous process is not found it proceeds to the next step. This particular system only has a dozen or so partitioned tables with monthly maintenance. Been working without any issue for quite some time now.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Maybe you can set your DIAGLEVEL to 4 and see if there is an entry (indicating all the work is done) you can use as a trigger for the next step.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    thanks for all update.
    we have this change in behavior since migrating to V10
    we loop on syscat.datapartitions and wait for the row with this part to disappear, and continue with next step. probobaly we have to insert another sleep after this to be sure, the asynch processes have completely stopped. we also noticed that detach is also asynch. in V9.5 we used to start export on detached table, after the detach command. now we also get a message on export, that the detach has not completely finished. another sleep has to be introduced, or a loop on status of syscat.datapartitions to become normal instead of L , as I could read in manual.
    a lot of testing has to be done to get the script full-proof again...
    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

Posting Permissions

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