Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2009
    Posts
    42

    Unanswered: lock conflict between online backup and load

    Hi,

    We're running DB2 8 on AIX. Load and online backup can't execute at the same time, snapshot shows that load is waiting for an internal lock (B lock). Do you know why this is happening and what I can do to avoid running into this issue.

    Thank you!

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Please check the following technote:
    IBM - Internal B locks

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    Thank you. What else can we do to avoid this issue?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    You can try scheduling loads and online backup at different times. If the tables exist in a specific tablespace, then you can skip this tablespace during backup execution (this will have to be tablespace level backup). Another option is to move the tables you're loading to a new tablespace and hope that db2 will process it last (the order tablespaces are backed up is based on the tablespace size). If the new tablespace is small compared to others, then it'll be processed last. This option will allow you to perform database level backup.

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    Is there any other way to avoid this issue?

    You said if the tablespace is small, it'll be processed last. What determines tablespace size? How does online backup place B-locks?

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You are asking wrong question. Instead of trying to resolve this problem once and for all you are trying to find a half-baked solution to it that will backfire sooner then later.

    The only sure way to avoid this problem if not to encounter it. i.e. check your schedule and revise it. In your script put checks to make sure there are no other jobs are running that could prevent backup to finish. If there are, based on your business needs, either kill them or send your backup to sleep for some time and then perform the check again.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Apr 2009
    Posts
    42
    But this is online backup - I should be able to execute it while other jobs are running! I have 3 systems and only one of them is experiencing this problem. Why is that? I need to understand what's happening with internal locks and then take action. Has anyone else experienced this problem with B-locks?

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Have you read what Bella posted? Read it and if still have questions let us know which part does not seams right to you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Apr 2009
    Posts
    42
    I read this technote, but I need to understand more about B-lock. Things such as:
    How are they placed? On all tablespaces at once or tablespace by tablespace.
    How are they removed? When entire db backup is done or when each tablespace is done
    How tablespace size is determined (db2girl mentioned this in the prior post but it's not clear)
    Why only 1 out of 3 systems have this issue or it's more noticable?

    Thank you!

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Internal B locks are acquired one at a time when the backup begins to process the particular tablespace. The order that tablespaces are processed is based on the tablespace size (size = number of used pages * page size). The locks are all released at the end of backup.

    I suspect that the reason this issue exists/more noticeable in one of three env is due to tablespace sizes. The one where this issue exists has tables created in tablespace(s) that are larger compared to the rest. If this is the case, then you can create a new tablespace and move the tables. If the new tablespace is relatively small, it will be locked closer to the end and thus lock-wait will be significantly reduced.

  11. #11
    Join Date
    Apr 2009
    Posts
    42
    Thank you!

    Has anyone experienced this problem with other versions of DB2? We have this problem with DB2 8.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I believe internal B locks are acquired/released the same way in all versions of DB2.

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Yes, in my prior shop we had this issue. Resolved it by doing what I recommended earlier. Until then you are at a mercy of a chance.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Apr 2009
    Posts
    42
    What is the easiest way to see if there are any B-locks on some tablespace?

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2zip
    What is the easiest way to see if there are any B-locks on some tablespace?
    I will take the liberty to quote my esteemed colleague, db2zip:

    Quote Originally Posted by db2zip
    snapshot shows that load is waiting for an internal lock (B lock).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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