Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: slow tablespace restore

    I started a restore yesterday and its still going. I'm not sure if this process is still running or its just running slooow. I'm restoring 55 gig from tape. Its been running for about 18 hours. Can someone tell me if the restore is still running from the info below. I'm really concern about:

    Total User CPU Time used by agent (s) = 0.010000
    Total System CPU Time used by agent (s) = 0.000000

    Application Snapshot

    Application handle = 208
    Application status = Restoring Database
    Status change time = Not Collected
    Application code page = 819
    Application country code = 1
    DUOW correlation token = *LOCAL.db2fudd.040713140712
    Application name = db2bp
    Application ID = *LOCAL.db2fudd.040714195725
    TP Monitor client user ID =
    TP Monitor client workstation name =
    TP Monitor client application name =
    TP Monitor client accounting string =

    Sequence number = 0001
    Connection request start timestamp = 07-14-2004 14:57:25.331783
    Connect request completion timestamp = 07-14-2004 14:57:25.332830
    Application idle time = Not Collected
    Authorization ID = DB2FUDD
    Client login ID = db2fudd
    Configuration NNAME of client =
    Client database manager product ID = SQL07010
    Process ID of client application = 43736
    Platform of client application = AIX
    Communication protocol of client = Local Client

    Inbound communication address = *LOCAL.db2fudd

    Database name = RECORDS
    Database path = /db2fudd/db2fudd/NODE0000/SQL00001/
    Client database alias = RECORDS
    Input database alias =
    Last reset timestamp =
    Snapshot timestamp = 07-15-2004 09:19:52.557663
    The highest authority level granted =
    Direct DBADM authority
    Direct CREATETAB authority
    Direct BINDADD authority
    Direct CONNECT authority
    Direct CREATE_NOT_FENC authority
    Direct LOAD authority
    Direct IMPLICIT_SCHEMA authority
    Indirect SYSADM authority
    Indirect BINDADD authority
    Indirect CONNECT authority
    Indirect IMPLICIT_SCHEMA authority
    Coordinating node number = 0
    Current node number = 0
    Coordinator agent process or thread ID = 117162
    Agents stolen = 0
    Agents waiting on locks = 0
    Maximum associated agents = 0
    Priority at which application agents work = 0
    Priority type = Dynamic

    Locks held by application = 0
    Lock waits since connect = Not Collected
    Time application waited on locks (ms) = Not Collected
    Deadlocks detected = Not Collected
    Lock escalations = 0
    Exclusive lock escalations = 0
    Number of Lock Timeouts since connected = 0
    Total time UOW waited on locks (ms) = Not Collected

    Total sorts = Not Collected
    Total sort time (ms) = Not Collected
    Total sort overflows = Not Collected

    Data pages copied to extended storage = Not Collected
    Index pages copied to extended storage = Not Collected
    Data pages copied from extended storage = Not Collected
    Index pages copied from extended storage = Not Collected
    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool index writes = Not Collected
    Total buffer pool read time (ms) = Not Collected
    Total buffer pool write time (ms) = Not Collected
    Time waited for prefetch (ms) = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected
    Number of SQL requests since last commit = 0
    Commit statements = 0
    Rollback statements = 0
    Dynamic SQL statements attempted = 0
    Static SQL statements attempted = 0
    Failed statement operations = 0
    Select SQL statements executed = 0
    Update/Insert/Delete statements executed = 0
    DDL statements executed = 0
    Internal automatic rebinds = 0
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Internal commits = 1
    Internal rollbacks = 0
    Internal rollbacks due to deadlock = 0
    Binds/precompiles attempted = 0
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 0
    Rows read = 3
    Rows written = 0
    UOW log space used (Bytes) = Not Collected
    Previous UOW completion timestamp = Not Collected
    Elapsed time of last completed uow (sec.ms)= Not Collected
    UOW start timestamp = Not Collected
    UOW stop timestamp = Not Collected
    UOW completion status = Not Collected
    Open remote cursors = 0
    Open remote cursors with blocking = 0
    Rejected Block Remote Cursor requests = 0
    Accepted Block Remote Cursor requests = 0
    Open local cursors = 0
    Open local cursors with blocking = 0

    Total User CPU Time used by agent (s) = 0.010000
    Total System CPU Time used by agent (s) = 0.000000
    Host execution elapsed time = Not Collected
    Package cache lookups = 0
    Package cache inserts = 0
    Application section lookups = 0
    Application section inserts = 0
    Catalog cache lookups = 0
    Catalog cache inserts = 0
    Catalog cache overflows = 0
    Catalog cache heap full = 0

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    can you check the containers size increasing , by using either df or du or simply ls at the OS level.

    regards,

    mujeeb

  3. #3
    Join Date
    Apr 2004
    Posts
    190

    df containers

    The containers are devices and I get the same results for that is not in the tablespace being restored (cdrslv02) and one that is (cdrslv15). When I list the db2 application I see the restore running, so maybe its just taking a long time. You have any more suggestions?

    Thanks

    df -k cdrslv02
    Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
    /dev/hd4 1245184 1116980 11% 3060 1% /
    db2fudd@fudd:/dev:>df -k cdrslv15
    Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
    /dev/hd4 1245184 1116980 11% 3060 1% /

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by redwolf
    The containers are devices and I get the same results for that is not in the tablespace being restored (cdrslv02) and one that is (cdrslv15). When I list the db2 application I see the restore running, so maybe its just taking a long time. You have any more suggestions?

    Thanks

    df -k cdrslv02
    Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
    /dev/hd4 1245184 1116980 11% 3060 1% /
    db2fudd@fudd:/dev:>df -k cdrslv15
    Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
    /dev/hd4 1245184 1116980 11% 3060 1% /
    Maybe try using iostat to see if the devices are actively being written to. Use 10 or 20 repetitions of a few seconds each to get a good picture.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Apr 2004
    Posts
    190

    iostat results

    It does not look like anything is being written to the logical volumn, at least not to the one that crashed. Why would the restore not fail in this case ? Also, would the tablespace state change to 0x2000 (restoring) if it was working? It has never changed from 0x4000 (offline).

  6. #6
    Join Date
    Apr 2004
    Posts
    190

    Tape being read

    How can you tell if the tape is being read ? I'm supporting this database remotely. Also, when would the tablespace state change from 0x4000 (offline)? When the restore starts or after the restore ?

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    it should change to restoring,offline nothing is coming to it, it happens when the container is either damaged or not accessible.

    check the mt command.

    mt -f /dev/rmt0(put your device name) status.

    regards,

    mujeeb

Posting Permissions

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