Excessive z-lock time with LOAD command?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mike.lopiano@fmr.com
    #1

    Excessive z-lock time with LOAD command?

    There is a significant gap (~ 3.5 minutes) in the time our LOAD
    command indicates that the BUILD phase is complete and the time we
    have control returned to our script.

    I ran the test below on a dedicated system with no other processes
    running. Our tablespaces are on Veritas filesystems. Any insight/
    suggestions welcome...

    From the LOAD output:

    SQL3515W The utility has finished the "LOAD" phase at time
    "02/20/2008
    13:31:55.301939 ".

    SQL3500W The utility is beginning the "BUILD" phase at time
    "02/20/2008
    13:31:55.304641 ".

    SQL3213I The indexing mode is "INCREMENTA L".

    SQL3515W The utility has finished the "BUILD" phase at time
    "02/20/2008
    13:32:38.044731 ".


    Number of rows read = 437645
    Number of rows skipped = 0
    Number of rows loaded = 437645
    Number of rows rejected = 0
    Number of rows deleted = 0
    Number of rows committed = 437645

    db2 =db2 (cont.) =DB20000I The SQL command completed
    successfully.
    db2 =>
    Wed Feb 20 13:36:08 EST 2008
    Tags: None
    • Arun Srinivasan
      #2
      Re: Excessive z-lock time with LOAD command?

      On Feb 20, 12:46 pm, mike.lopi...@fm r.com wrote:
      There is a significant gap (~ 3.5 minutes) in the time our LOAD
      command indicates that the BUILD phase is complete and the time we
      have control returned to our script.
      >
      I ran the test below on a dedicated system with no other processes
      running. Our tablespaces are on Veritas filesystems. Any insight/
      suggestions welcome...
      >
      From the LOAD output:
      >
      SQL3515W  The utility has finished the "LOAD" phase at time
      "02/20/2008
      13:31:55.301939 ".
      >
      SQL3500W  The utility is beginning the "BUILD" phase at time
      "02/20/2008
      13:31:55.304641 ".
      >
      SQL3213I  The indexing mode is "INCREMENTA L".
      >
      SQL3515W  The utility has finished the "BUILD" phase at time
      "02/20/2008
      13:32:38.044731 ".
      >
      Number of rows read         = 437645
      Number of rows skipped      = 0
      Number of rows loaded       = 437645
      Number of rows rejected     = 0
      Number of rows deleted      = 0
      Number of rows committed    = 437645
      >
      db2 =db2 (cont.) =DB20000I  The SQL command completed
      successfully.
      db2 =>
      Wed Feb 20 13:36:08 EST 2008
      Can you paste the load command ? Do you use temp tablespaces to build
      indexes? That would mean more time to transfer the indexes from the
      temp tablespaces to it's parent tablespace..

        Comment

        • mike.lopiano@fmr.com
          #3
          Re: Excessive z-lock time with LOAD command?

          On Feb 20, 2:09 pm, Arun Srinivasan <arunro...@gmai l.comwrote:
          On Feb 20, 12:46 pm, mike.lopi...@fm r.com wrote:
          >
          >
          >
          >
          >
          There is a significant gap (~ 3.5 minutes) in the time our LOAD
          command indicates that the BUILD phase is complete and the time we
          have control returned to our script.
          >
          I ran the test below on a dedicated system with no other processes
          running. Our tablespaces are on Veritas filesystems. Any insight/
          suggestions welcome...
          >
          From the LOAD output:
          >
          SQL3515W  The utility has finished the "LOAD" phase at time
          "02/20/2008
          13:31:55.301939 ".
          >
          SQL3500W  The utility is beginning the "BUILD" phase at time
          "02/20/2008
          13:31:55.304641 ".
          >
          SQL3213I  The indexing mode is "INCREMENTA L".
          >
          SQL3515W  The utility has finished the "BUILD" phase at time
          "02/20/2008
          13:32:38.044731 ".
          >
          Number of rows read         = 437645
          Number of rows skipped      = 0
          Number of rows loaded       = 437645
          Number of rows rejected     = 0
          Number of rows deleted      = 0
          Number of rows committed    = 437645
          >
          db2 =db2 (cont.) =DB20000I  The SQL command completed
          successfully.
          db2 =>
          Wed Feb 20 13:36:08 EST 2008
          >
          Can you paste the load command ? Do you use temp tablespaces to build
          indexes? That would mean more time to transfer the indexes from the
          temp tablespaces to it's parent tablespace..- Hide quoted text -
          >
          - Show quoted text -
          Here is the abbreviated command (removed some columns):

          LOAD FROM File.del of del modified by coldel| tempfiles path '/
          filesystem1/archive/dbconfig/TEMPFILES'
          INSERT INTO Daily1 (
          date,
          time,
          ..... upd_d
          ) NONRECOVERABLE cpu_parallelism 6

          Running DB2 V8 FixPak #12.

            Comment

            Previous template Next
            Working...