Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: deadlock when dropping view

    Salutte,

    I do have views that are based on other views. Those views are generated new on daily basis.
    Looks like that:

    View_ALL consists of:
    view_01
    view_02
    ....and so on

    view_01 consists of:
    table_2010_01_01
    table_2010_01_02
    table_2010_01_03

    I need to recreate view_01 whenever a new table is added. In test environnment its working without any probs.
    But in production I get a deadlock:
    <<DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001
    >>
    In my automatic script first view_01 will to be dropped then recreated....at the end view_all is dropped and then recreated.

    I do not know where the deadlock comes from, I tried different times of day, there should be noone accessing any of these views.

    Any idea?

    Thankx.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Reason code 68 indicates a lock timeout, not a deadlock. Apparently, the view you are trying to drop or any of those that are dependent on it are being used by active transactions.

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    How can I identify the application which blocks?

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Command Reference.pdf
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Oct 2009
    Posts
    24
    and for not dba's? i'm not dealing every day with db2, just need a quick command.
    thanks.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you had that pdf file, you would have had your answer 2 hours ago GET SNAPSHOT FOR LOCKS on DBNAME or download db2 monitor
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    am I understanding this right, you are adding a new table every day and then rebuilding the views?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Oct 2009
    Posts
    24
    yes, every day a new table to monthly view.
    I know the old monthly views dont need to be rebuild, but the script was created like that

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    that is NUTZ!!! Can I suggest talking to your DBA
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Oct 2009
    Posts
    24
    he's gone and we dont have a new one..though...

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    get one(a good one) and save yourself a lot of headaches now and in the future. Will, also, save your company money.

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I would love to see a good reason to have a daily table, and then create a view that consolidates all those daily tables together.

    Dav might be onto something
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    in fact, if you want off hour support from one I might be able to help you out.
    Dave

  14. #14
    Join Date
    Oct 2009
    Posts
    24
    I just try to get my applications work and to fit customer's needs. The company let the dba go without recruiting a new one. I just look for a usable alternative to solve problems.

    We have daily tables because the application didn't support partitioning at the time. We are talking about 1 million records per day with 900 columns, but we also needed to put records of different days together, so views were the only way. It's still on the roadmap to chnage to partitions.

    Back to the lock:
    I have 2 types of tables/view which treated nearly equally (daily recreation), but only type causes the lock problems. The ALL_View of that type is used in a stored procecure, might that fact be the root of locks? I can't imagine any other data access, since the script is running before people get to work.

    Thanks guys.

  15. #15
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    yauza, 1 mil per day and 900 columns?!?! With proper design DB2 should still handle it as one table, but I guess you are past this.

    You need to figure out what is holding locks. Users do not have to be there for it to happen. You can have a run away process, or simply a job that opened a lock and never commited its work.

    Is your company looking for a replacement? Sounds like you need one yesterday.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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