Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Red face Unanswered: Problem with ADD and Detach partition

    Team,

    We have a table which is particularly is used in so many select Queries as it would be accessed by customers to fetch their information. Also whenever they want they can change their information too.
    No we had to retrieve records only less than 2 years of age. So whatever is older than 24 months gets deleted.
    Also, We have a script whoch actually runs every month and adds (not ATTACH) a partition to the table.
    Problem is, we were testing the add and DETACHING by loading the table with some dummy data and running 'Select * from tab'. The ADD and DETACH scripts seems to fail.
    Can you please elaborate the reason for this and also how can I achieve the above, without having to force connections and bringing the table down as its very critical and must be available at all times.

    Please Help !

    Thank you
    Siddharth

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As we are not psychics, you will need to post the exact SQL command issued, and the complete error message. Please do not key in partial information, instead copy and paste the complete information to this thread.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2012
    Posts
    12

    Red face

    He he Nice one mate.

    I am simply using the following to add a partition:-

    db2 "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"

    now when table is not being used it goes on well. But when I load the table and just run db2 "select * from tabname" and then run the same (as above) command to add partitions following error comes.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001



    No my guess is this is happening because it cannot acquire lock on the table or syscat tables, Now my question is, is there any way so that I can add partitions to table without bringing the whole DB down or forcing off all connections as business needs this table up at all times, and we cannot bring the same down every time we have to add a partition.
    Last edited by siddharth1988; 05-28-12 at 04:41.

  4. #4
    Join Date
    Jan 2012
    Posts
    12
    Guys any replies on this. Really need some help

  5. #5
    Join Date
    Jan 2009
    Posts
    33
    What is the loctimeout value and can you find out what are the operations running on that table when you are adding that partition. can you try the same operation by including with ur in the select operation.

  6. #6
    Join Date
    Jan 2012
    Posts
    12

    Red face

    LOCKTIMEOUT=60
    HARD_LOCKTIMEOUT=120

    Its select and update operations running on the table.
    Also I have tried the sme with ur, but still I get the error. please help.

    Can i add partitions to the table even when the table is under heavy use. ADD EMPTY PARTITIONS ONLY not WITH DATA

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Which environment? Operating system, db2 release, etc?

    How is this partitioned? Why is there a need to do this often?

    To me, this is system maintenance and should be done during scheduled maintenance down-time.

  8. #8
    Join Date
    Jan 2012
    Posts
    12

    Red face

    OS: AIX
    db2 release: DB2 v 9.7

    we are collecting itineraries every month. We need to save these itnry in separate partitions ( from date of booking). so we go on adding new advance partitions for the data to come.

    We cannot share system-maint time for database related stuff. Why the hell db2 need outage for adding empty advance partition ?

  9. #9
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Adding a new partition invalidates impacted packages.
    Have you considered creating the table, populating it, then attaching it to the range-partitioned table?
    Needs 'set integrity' after the attach, but table remains on-line during attach.
    RTFM carefully
    Attaching data partitions - IBM DB2 9.7 for Linux, UNIX, and Windows

  10. #10
    Join Date
    Jan 2012
    Posts
    12
    The issue is since we are adding advance partitions, the data for that partition is still to come. For instance, the script runs today and adds 3 partitions for the next 3 months. So having a preloaded partition and "ATTACH" is out of question.

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    can still create 3 tables now, attach later..
    The attach is designed for roll-in...

  12. #12
    Join Date
    Jan 2012
    Posts
    12
    ah !!! now how shall I redirect inserts coming to the base table to these 3 new tables ? As all incoming requests from app come to base table only. Isnt there anyway to add partitions on the fly ?

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If you create the range-table manually, and want to populate it before Attach, then you insert into the range-table.

    If the range-table is already attached you populate it via the base-table.


    If your 'create table' had a range-partitioning-specification that covered all values of the partitioning column(s) with relevant clauses, or even with "Every 3 months" type of syntaxx, you might have what you need...

    RTFM for further details.

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
  •