Results 1 to 11 of 11

Thread: Reorg

  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: Reorg

    Hi All !

    Is there a lot of difference in the way a REORG runs for DB2 on os/390 and that for db2 on aix (or windows) ?
    On the os/390, REORG could be either REORG INDEX or REORG TABLESPACE. Besides, you have s SHRLEVEL option to influence the TS access during reorg. There are lot many options to influence LOGGING, COPY, UNLOAD etc
    What is the deal on DB2 for AIX or Windows ?
    The syntax in the manual just says
    REORG TABLE--table-name----+--------------------+------------>
    '-INDEX--index-name--'

    >-----+-----------------------+--------------------------------><
    '-USE--tablespace-name--

    Does it restrict access on the Table being reorged ? or is there a way to influence the access at the time of reorg.
    Any tips and tricks with REORG on AIX or WINDOWS ?

    thanks
    Anil

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    There are many differences in aix (or win) environment, depending if you use version v7 or v8 of database. I think you use vesion 7, which has some limitations. Please post database version and fixpack level. Please see Must Read before posting to get info about how to get this data.

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Feb 2005
    Posts
    118
    Oops !

    DB2 ver 7.2 on AIX and some databases on DB2 ver 7.2 on Windows.

    Anil

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    DB2 version 7 for Linux, Unix and Windows has some limitations. In version 8 you can do the following:

    Code:
    REORG {TABLE table-name Table-Clause | INDEXES ALL FOR TABLE table-name
    Index-Clause} [On-DbPartitionNum-Clause]
    
    Table-Clause:
      [INDEX index-name] [[ALLOW {READ | NO} ACCESS]
      [USE tablespace-name] [INDEXSCAN] [LONGLOBDATA]] |
      [INPLACE [ [ALLOW {WRITE | READ} ACCESS] [NOTRUNCATE TABLE]
      [START | RESUME] | {STOP | PAUSE} ]]
    
    Index-Clause:
      [ALLOW {READ | NO | WRITE} ACCESS]
      [{CLEANUP ONLY [ALL | PAGES] | CONVERT}]
    
    On-DbPartitionNum-Clause:
      ON {{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number
      [TO  db-partition-number] , ... ) | ALL DBPARTITIONNUMS [EXCEPT
      {DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number
      [TO db-partition-number] , ...)]}
    
    NOTE: From the operating system prompt, prefix commands with 'db2'.
          Special characters MAY require an escape sequence (\), for example:
          db2 \? change database
          db2 ? change database xxx comment with \"text\"
    Hope this helps,
    Grofaty
    Last edited by grofaty; 03-02-05 at 03:36.

  5. #5
    Join Date
    Feb 2005
    Posts
    118
    Which means as far as REORG on these environments is concerned (ver 7.2 on AIX or windows), there would be a outage during REORGs.
    IS that true, or is there a workaround ?

    Anil

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    AFAIK, you will have an outage ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    As far as I know you need to migrate to version 8 to get extra functionalitys.
    Grofaty

  8. #8
    Join Date
    Feb 2005
    Posts
    118
    While I still don't have the luxury of ver 8, do I have to REORG one index (when table has multiple indexes) at a time ? or am I better of just dropping the indexes and rebuilding them again ?

    Anil

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    You can do the following:
    REORG TABLE schema.table_name INDEX schema.index_name

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In version 7.x, when you reorg the table, all indexes are reorged.

    When you specify an index in the index clause of the reorg in version 7, that tells DB2 which index to use to order the rows of the table, and not which indexes to reorg.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Marcus_A, you are correct, I missunderstood AnilKale.

Posting Permissions

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