Lock waits during online backup

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • michael.spoden@web.de
    #1

    Lock waits during online backup

    Hi,

    how can I fix lock-waits during an online backup? Is an online backup
    in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.

    The command to perform the backup is: db2 backup db BPEDB online to /
    var/tmp


    During the backup the application hangs on just one table
    (variable_insta nce_b_t) which is a central table of the application
    and which will be used a lot. But the table is not realy large, there
    are about 50.000 rows in the table.


    Here's an output of the locks hold by the backup session:

    List Of Locks
    Lock Name = 0x02006C0000000 0000000000074
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 108
    Object Type = Internal Table Alter Lock
    Tablespace Name = USERSPACE1
    Table Schema = DB2INST1
    Table Name = VARIABLE_INSTAN CE_B_T
    Mode = S

    Lock Name = 0x02006C0000000 000000000006F
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 2
    Object Type = Internal Online Backup Lock
    Tablespace Name = USERSPACE1
    Table Schema = DB2INST1
    Table Name = VARIABLE_INSTAN CE_B_T
    Mode = X

    Lock Name = 0x02006C0000000 0000000000054
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 108
    Object Type = Table
    Tablespace Name = USERSPACE1
    Table Schema = DB2INST1
    Table Name = VARIABLE_INSTAN CE_B_T
    Mode = S

    + about 8 internal locks


    Do you have any tipps to fix this issue?

    Thanks

    Michael

    Tags: None
    • Mark A
      #2
      Re: Lock waits during online backup

      <michael.spoden @web.dewrote in message
      news:cd0df796-a625-4fd7-b7da-f7fa6f1f1f9e@p2 5g2000hsf.googl egroups.com...
      Hi,
      >
      how can I fix lock-waits during an online backup? Is an online backup
      in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.
      An online backup should allow access to the database by applicaitons for
      select, insert, update, delete.
      The command to perform the backup is: db2 backup db BPEDB online to /
      var/tmp
      >
      >
      During the backup the application hangs on just one table
      (variable_insta nce_b_t) which is a central table of the application
      and which will be used a lot. But the table is not realy large, there
      are about 50.000 rows in the table.
      >
      >
      Here's an output of the locks hold by the backup session:
      >
      List Of Locks
      Lock Name = 0x02006C0000000 0000000000074
      Lock Attributes = 0x00000000
      Release Flags = 0x40000000
      Lock Count = 1
      Hold Count = 0
      Lock Object Name = 108
      Object Type = Internal Table Alter Lock
      Tablespace Name = USERSPACE1
      Table Schema = DB2INST1
      Table Name = VARIABLE_INSTAN CE_B_T
      Mode = S
      >
      Lock Name = 0x02006C0000000 000000000006F
      Lock Attributes = 0x00000000
      Release Flags = 0x40000000
      Lock Count = 1
      Hold Count = 0
      Lock Object Name = 2
      Object Type = Internal Online Backup Lock
      Tablespace Name = USERSPACE1
      Table Schema = DB2INST1
      Table Name = VARIABLE_INSTAN CE_B_T
      Mode = X
      >
      Lock Name = 0x02006C0000000 0000000000054
      Lock Attributes = 0x00000000
      Release Flags = 0x40000000
      Lock Count = 1
      Hold Count = 0
      Lock Object Name = 108
      Object Type = Table
      Tablespace Name = USERSPACE1
      Table Schema = DB2INST1
      Table Name = VARIABLE_INSTAN CE_B_T
      Mode = S
      >
      + about 8 internal locks
      >
      >
      Do you have any tipps to fix this issue?
      >
      Thanks
      >
      Michael
      I would restart the instance and try again. If that does not work, check the
      db2diag.log for more information. Call IBM support if that does not help
      you.


        Comment

        • Jayesh Thakrar
          #3
          Re: Lock waits during online backup

          On Jun 11, 11:50 am, "Mark A" <nob...@nowhere .comwrote:
          <michael.spo... @web.dewrote in message
          >
          news:cd0df796-a625-4fd7-b7da-f7fa6f1f1f9e@p2 5g2000hsf.googl egroups.com...
          >
          Hi,
          >
          how can I fix lock-waits during an online backup? Is an online backup
          in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux.
          >
          An online backup should allow access to the database by applicaitons for
          select, insert, update, delete.
          >
          >
          >
          >
          >
          The command to perform the backup is: db2 backup db BPEDB online to /
          var/tmp
          >
          During the backup the application hangs on just one table
          (variable_insta nce_b_t) which is a central table of the application
          and which will be used a lot. But the table is not realy large, there
          are about 50.000 rows in the table.
          >
          Here's an output of the locks hold by the backup session:
          >
          List Of Locks
          Lock Name                   = 0x02006C0000000 0000000000074
          Lock Attributes             = 0x00000000
          Release Flags               = 0x40000000
          Lock Count                  = 1
          Hold Count                  = 0
          Lock Object Name            = 108
          Object Type                 = Internal Table Alter Lock
          Tablespace Name             = USERSPACE1
          Table Schema                = DB2INST1
          Table Name                  = VARIABLE_INSTAN CE_B_T
          Mode                        = S
          >
          Lock Name                   = 0x02006C0000000 000000000006F
          Lock Attributes             = 0x00000000
          Release Flags               = 0x40000000
          Lock Count                  = 1
          Hold Count                  = 0
          Lock Object Name            = 2
          Object Type                 = Internal Online Backup Lock
          Tablespace Name             = USERSPACE1
          Table Schema                = DB2INST1
          Table Name                  = VARIABLE_INSTAN CE_B_T
          Mode                        = X
          >
          Lock Name                   = 0x02006C0000000 0000000000054
          Lock Attributes             = 0x00000000
          Release Flags               = 0x40000000
          Lock Count                  = 1
          Hold Count                  = 0
          Lock Object Name            = 108
          Object Type                 = Table
          Tablespace Name             = USERSPACE1
          Table Schema                = DB2INST1
          Table Name                  = VARIABLE_INSTAN CE_B_T
          Mode                        = S
          >
          + about 8 internal locks
          >
          Do you have any tipps to fix this issue?
          >
          Thanks
          >
          Michael
          >
          I would restart the instance and try again. If that does not work, check the
          db2diag.log for more information. Call IBM support if that does not help
          you.- Hide quoted text -
          >
          - Show quoted text -
          Ideally there would/should not be any locks - however DB2 does hold
          internal locks for a few things - like some of the config files and
          other internal objects.
          I am not sure of the actual objects, their durations, etc. I would
          suggest reaching out to DB2 Support - and they can provide the
          details.

          All the same, just curious to know if these locks are causing you any
          problems?

          -- Jayesh

            Comment

            • michael.spoden@web.de
              #4
              Re: Lock waits during online backup

              Thanks Mark, thanks Jayesh.

              I've found the following in the manuals about the online option in the
              backup command:
              'During an online backup, DB2 obtains IN (Intent None) locks on all
              tables existing in SMS table spaces as they are processed and S
              (Share) locks on LOB data in SMS table spaces'

              We're using SMS tablespaces and the table which is lockes has a lob
              column. Do you think moving this table to a DMS tablespace can fix the
              problem.

              By the way, there no special entries in the db2diag.log file, just the
              starting and finishing of the backup.

              Regards

              Michael

                Comment

                • Ian
                  #5
                  Re: Lock waits during online backup

                  michael.spoden@ web.de wrote:
                  Thanks Mark, thanks Jayesh.
                  >
                  I've found the following in the manuals about the online option in the
                  backup command:
                  'During an online backup, DB2 obtains IN (Intent None) locks on all
                  tables existing in SMS table spaces as they are processed and S
                  (Share) locks on LOB data in SMS table spaces'
                  >
                  We're using SMS tablespaces and the table which is lockes has a lob
                  column. Do you think moving this table to a DMS tablespace can fix the
                  problem.
                  >
                  By the way, there no special entries in the db2diag.log file, just the
                  starting and finishing of the backup.
                  >
                  What are you trying to do in your application that is running in to the
                  locks?

                  The point of putting locks on objects in the tablespace is to prevent
                  changes (DDL) while the backup is active. DML is fine, of course.


                    Comment

                    • Mark A
                      #6
                      Re: Lock waits during online backup

                      <michael.spoden @web.dewrote in message
                      news:2c681b98-04c3-43de-831f-ac11b8d66e8d@a7 0g2000hsh.googl egroups.com...
                      Thanks Mark, thanks Jayesh.
                      >
                      I've found the following in the manuals about the online option in the
                      backup command:
                      'During an online backup, DB2 obtains IN (Intent None) locks on all
                      tables existing in SMS table spaces as they are processed and S
                      (Share) locks on LOB data in SMS table spaces'
                      >
                      We're using SMS tablespaces and the table which is lockes has a lob
                      column. Do you think moving this table to a DMS tablespace can fix the
                      problem.
                      It is worth a try.


                        Comment

                        • michael.spoden@web.de
                          #7
                          Re: Lock waits during online backup

                          Hi,

                          first tests on our test environment are successful. I've moved the
                          specific table from a SMS to a DMS tablespace. There are no more
                          shared locks on this table and no more lock-waits on the database.

                          Regards

                          Michael

                            Comment

                            Previous template Next
                            Working...