Unanswered: DB2 version 7.2 workgroup edition fixpak 13 on OS/2
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 ?
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.
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."