Results 1 to 11 of 11

Thread: reorg index

  1. #1
    Join Date
    Sep 2002
    Posts
    41

    Lightbulb Unanswered: reorg index

    DB/OS : UDB db2 7.2 on sun solaris 8.0

    Is there any way to reorg index ? I didn't found anything that reorg index.Only option I found is to drop and rebuid index.

    Does Table reorg also reorg index ??

    Rk

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: reorg index

    Originally posted by kuckoo
    DB/OS : UDB db2 7.2 on sun solaris 8.0

    Is there any way to reorg index ? I didn't found anything that reorg index.Only option I found is to drop and rebuid index.

    Does Table reorg also reorg index ??

    Rk
    did you try the

    db2 reorg

    my two cents

    marcos oliva

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: reorg index

    Yes you can, try this:

    Reorg table <table_name> index <index_name>

    i.e

    Reorg table employee index idx_emp_no;

    dollar


    Originally posted by Maor71
    did you try the

    db2 reorg

    my two cents

    marcos oliva

  4. #4
    Join Date
    Sep 2002
    Posts
    41

    Re: reorg index

    Reorg <table_name> index <index_name>

    Command will reorg table accordignly to specific index.


    Is there any way that reorg index ?

    Thanks
    RK


    Originally posted by dollar489
    Yes you can, try this:

    Reorg table <table_name> index <index_name>

    i.e

    Reorg table employee index idx_emp_no;

    dollar

  5. #5
    Join Date
    Sep 2002
    Posts
    456

    Re: reorg index

    I didn't get your question, can you explain what exactly you want?

    dollar
    Originally posted by kuckoo
    Reorg <table_name> index <index_name>

    Command will reorg table accordignly to specific index.


    Is there any way that reorg index ?

    Thanks
    RK

  6. #6
    Join Date
    Sep 2002
    Posts
    41

    Re: reorg index

    My requirement is to reorganization of table and index on regular basis.
    Take this example.

    table : ICMTEST
    index on ICMTEST : ICMIDX1, ICMIDX2

    I want to reorg ICMTEST,ICMIDX1 & ICMIDX2.What are options available to me do this ?

    I tried,
    (1) reorg table ICMTEST ( which will reorg ICMTEST )
    (2) reorg table ICMTEST index ICMIDX1 ( which will reorg ICMTEST according to ICMIDX1 but it will not reorg index ICMIDX1 or ICMIDX2 )

    Only option I found to reorg index ICMIDX1 and ICMIDX2 is to drop and recreate index ? Is there any other way to do this task ?

    Thanks

    RK

  7. #7
    Join Date
    Sep 2002
    Posts
    456

    Re: reorg index

    Reorging is basically as the name says is re-organization of data. Optionally we can reorganize the table data based on an index key. This is also called clustering of data i.e. when table data is in same physical order as the index is. You can order data based on only one index though; which makes sense too!!!! You can not order something based on two different keys, can you?

    So, bottom line you can only reorg based on one index. Even if you drop and recreate the table it won't do anything.

    Hope I have made the point clear

    dollar

    Originally posted by kuckoo
    My requirement is to reorganization of table and index on regular basis.
    Take this example.

    table : ICMTEST
    index on ICMTEST : ICMIDX1, ICMIDX2

    I want to reorg ICMTEST,ICMIDX1 & ICMIDX2.What are options available to me do this ?

    I tried,
    (1) reorg table ICMTEST ( which will reorg ICMTEST )
    (2) reorg table ICMTEST index ICMIDX1 ( which will reorg ICMTEST according to ICMIDX1 but it will not reorg index ICMIDX1 or ICMIDX2 )

    Only option I found to reorg index ICMIDX1 and ICMIDX2 is to drop and recreate index ? Is there any other way to do this task ?

    Thanks

    RK

  8. #8
    Join Date
    Sep 2002
    Posts
    41
    Thanks Dollor.
    I understand that we can not reorg table with two different Index and I don't want to reorg table on index also.

    My point is can't we reorg index only ? Can I do following thing ?

    Reorg INDEX1
    Reorg INDEX 2 .

    I didn't found anything specific in db2 documentation to do this.

    In other databases like Oracle or even DB2/Mainframe we can do this.But nothing for UDB 7.2/UNIX.

    Thanks again !!
    RK

  9. #9
    Join Date
    Sep 2002
    Posts
    456
    I see your point now!!! To the very best of my knowledge there is nothing in UDB like that.

    dollar

    Originally posted by kuckoo
    Thanks Dollor.
    I understand that we can not reorg table with two different Index and I don't want to reorg table on index also.

    My point is can't we reorg index only ? Can I do following thing ?

    Reorg INDEX1
    Reorg INDEX 2 .

    I didn't found anything specific in db2 documentation to do this.

    In other databases like Oracle or even DB2/Mainframe we can do this.But nothing for UDB 7.2/UNIX.

    Thanks again !!
    RK

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605

    REORG index

    Hi,

    It is new command in DB2 UDB 8.1.

    Sample:
    REORG INDEXES ALL FOR TABLE schema.table_name

    Full sintax:
    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] , ...)]}

    Hope this helps,
    Grofaty

  11. #11
    Join Date
    Sep 2002
    Posts
    41
    Thanks !!

    Good point for Migrate to UDB 8.1



    RK

Posting Permissions

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