Results 1 to 8 of 8
  1. #1
    Join Date
    May 2010
    Posts
    88

    Unanswered: online reorg mechanism

    Hi,
    I referred this blog to understand the mechanism for online reorg.

    Online Reorg

    When they say it create a shadow table-space for the users to work when online reorg is in progress, where does this shadow table-space is created. I mean, where we need to have sufficient disk space for the online reorg to complete, like /temp or any other place?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What version and platform of DB2 are you talking about ?

    The page you are referring to is for DB2 zOS


    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2010
    Posts
    88
    Sorry for not mentioning the mandatory details. I am on DB2 V9.1, AIX 5.3

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you look at the REORG TABLE Statement description, there is a USE <tablespace name> and here is the description ,which should answer your query

    USE tbspace-name
    Specifies the name of a system temporary table space in which to store a temporary copy of the table being reorganized. If you do not provide a table space name, the database manager stores a working copy of the table in the table spaces that contain the table being reorganized.

    For an 8 KB, 16 KB, or 32 KB table object, if the page size of the system temporary table space that you specify does not match the page size of the table spaces in which the table data resides, the DB2 database product will try to find a temporary table space of the correct size of the LONG/LOB objects. Such a table space must exist for the reorganization to succeed.

    For partitioned tables, the temporary table space is used as temporary storage for the reorganization of data partitions in the table. Reorganization of the entire partitioned table reorganizes a single data partition at a time. The temporary table space must be able to hold the largest data partition in the table, and not the entire table. When the ON DATA PARTITION clause is specified, the temporary table space must be able to hold the specified partition.

    If you do not supply a table space name for a partitioned table, the table space where each data partition is located is used for temporary storage of that data partition. There must be enough free space in each data partition's table space to hold a copy of the data partition.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    BTW, the above is only for OFFLINE reorg (with no access or read-only access).

    For inplace reorg, as the name indicates, the reorg is done in-place and no shadow table is created.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2010
    Posts
    88
    Thanks for quick reply sathyaram but without a shadow copy in online reorg, how can users have access to data?
    The link I shared is for online reorg where it does talk about the shadow copy.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by db2cap View Post
    Thanks for quick reply sathyaram but without a shadow copy in online reorg, how can users have access to data?
    The link I shared is for online reorg where it does talk about the shadow copy.
    As mentioned earlier, the link you shared was for zOS. The concepts are a bit different in LUW.

    See Guy's link above.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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