Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013

    Unanswered: DB2 version 7.2 workgroup edition fixpak 13 on OS/2

    Hello everybody,

    Not really a database expert, more system admin that needs some hands on help.
    We are running DB2 on OS/2 version 7.2, workgroup edition fixpak 13.

    I have a few questions:

    If you run a DB2 reorg on a table without specifying a TEMPSPACE to use.
    What is the effect. From what I read in the docs and online forums the reorg can take longer if the table is bigger then the TEMPSPACE ?

    Other question is. It should be possible to run a reorg on table on database that is in use ?
    However very often when I run a reorg I get a -911 message. Any idea how we can work around this ? It seems from what I could find
    DB2 can not get a lock on the table.

    We just ran a reorg on table and it seems DB2 was "hanging" on the reorg.
    On a test machine the reorg on the same table completed in 1 minute and 30 seconds.
    On production server it was not not finished after 8 minutes. Can you somehow see what the status is of a reorg ?

    Thank you!


  2. #2
    Join Date
    Aug 2008
    Toronto, Canada
    If you don't specify TEMPSPACE, db2 will use the tablespace where the table resides. In general, reorg executes faster if you don't use TEMPSPACE (the tablespace where table resides needs to have enough free space to store another copy of the table or reorg will fail with tablespace full error). v7 is way too old... don't remember what kind of locks get acquired during an offline reorg, possibly other applications can read data from the table being reorged if "allow read" is specified/allowed. But at some point during reorg, db2 will need to acqure a Z-lock (super exclusive) on the table and, if it can't get it, it will timeout with sql0911n RC=68 (if locktimeout db cfg is set to some value other than -1) or will appear to hang / in lock-wait (if locktimeout db cfg = -1). Application and lock snapshots will tell you what's happening.

  3. #3
    Join Date
    Apr 2012
    Provided Answers: 27
    DB2 Version 7 for LUW had only an OFFLINE REORG function (that is to say, it was designed to operate when the table being reorganized was not in use by any other application).

    The Version 7.x command reference states this for REORG (among other things):

    "Be sure to complete all database operations and release all locks before
    invoking REORGANIZE TABLE. This may be done by issuing a COMMIT
    after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK.
    After reorganizing a table, use “RUNSTATS” on page 525 to update the table
    statistics, and “REBIND” on page 465 to rebind the packages that use this
    table. The reorganize utility will implicitly close all the cursors."

  4. #4
    Join Date
    Aug 2008
    Toronto, Canada
    Quote Originally Posted by db2mor View Post
    The Version 7.x command reference
    Where did you find this dinosaur?

Posting Permissions

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