Professional Web Applications Themes

restore redirect into one tablespace - IBM DB2

Hi, I have a database that contains many ( at least 12) tablespaces , I wish to restore the DB with re-direct but restore the user tables into only one tablespace ( container) excl sys tablespaces... Can I do this, if so what would be the 'set tablespace containers' command for this. If I cannot do this, would would be the best alternative, to move the user data into one table space. aix 5.2 db2 v8.1 thansk PS...

  1. #1

    Default restore redirect into one tablespace

    Hi,
    I have a database that contains many ( at least 12) tablespaces , I
    wish to restore the DB with re-direct but restore the user tables into
    only one tablespace ( container) excl sys tablespaces...
    Can I do this, if so what would be the 'set tablespace containers'
    command for this.

    If I cannot do this, would would be the best alternative, to move the
    user data into one table space.
    aix 5.2
    db2 v8.1

    thansk
    PS
    Peter Sands Guest

  2. #2

    Default Re: restore redirect into one tablespace

    Peter,

    I don't believe this is possible (although I'm happy to be proved wrong).

    I'd do a normal redirected restore first.

    You're going to have to increase the space allocated to the one tablespace
    you want to keep to accomodate the extra tables. Depending on the amount
    of disk space you have available on your server you can either do this
    during the redirected restore, or gradually as you move the tables from
    the other tablespaces and delete them.

    I'd then export the data from each table, drop it and then recreate it in
    the tablespace. I'd then load the table with the exported data (load not
    import to save on logging). You'll need to capture and edit the DDL using
    db2look to ensure that you put back all the indexes, triggers, views and
    the like.

    The question is : why would you want to do this. Having multiple
    tablespaces offers you the most flexibility in terms of tuning and
    recovery. Remember the unit of recovery, and the unit of allocation to
    separate buffer pools, is the tablespace. I personally set up my systems
    with each table have separate data, index and long (if needed)
    tablespaces. I set up a default set of bufferpools based on size and
    access requirements and am able to fine-tune performance.

    HTH

    Phil

    On Wed, 17 Sep 2003 08:26:22 -0700, Peter Sands wrote:
    > Hi,
    > I have a database that contains many ( at least 12) tablespaces , I
    > wish to restore the DB with re-direct but restore the user tables into
    > only one tablespace ( container) excl sys tablespaces...
    > Can I do this, if so what would be the 'set tablespace containers'
    > command for this.
    >
    > If I cannot do this, would would be the best alternative, to move the
    > user data into one table space.
    > aix 5.2
    > db2 v8.1
    >
    > thansk
    > PS
    Philip Nelson Guest

  3. #3

    Default Re: restore redirect into one tablespace

    Look at Philip's note and he's right. You can't restore redirect and
    also change table residence.
    His approach is the best with one varition.

    To move the data, before you backup/restore and redefine new tablespace
    containers; use the db2 look with -e to extract all the ddl concerning
    your tables.
    Edit the resulting file to redefine either/or the tablespace defs. and
    the file create to choose the right tablespace.
    Then run db2move to extract all the tables(s) data.

    Then use db2move in load mode on your target system to get the data back in.

    HTH, Pierre.

    Peter Sands wrote:
    > Hi,
    > I have a database that contains many ( at least 12) tablespaces , I
    > wish to restore the DB with re-direct but restore the user tables into
    > only one tablespace ( container) excl sys tablespaces...
    > Can I do this, if so what would be the 'set tablespace containers'
    > command for this.
    >
    > If I cannot do this, would would be the best alternative, to move the
    > user data into one table space.
    > aix 5.2
    > db2 v8.1
    >
    > thansk
    > PS
    P. Saint-Jacques Guest

  4. #4

    Default Re: restore redirect into one tablespace

    Quite right Pierre.

    I always forget about db2move for some reason.

    However the underlying result is the same, it just makes the task a bit
    easier.

    Thanks for that.

    Phil

    On Wed, 17 Sep 2003 14:52:59 -0400, P. Saint-Jacques wrote:
    > Look at Philip's note and he's right. You can't restore redirect and
    > also change table residence.
    > His approach is the best with one varition.
    >
    > To move the data, before you backup/restore and redefine new tablespace
    > containers; use the db2 look with -e to extract all the ddl concerning
    > your tables.
    > Edit the resulting file to redefine either/or the tablespace defs. and
    > the file create to choose the right tablespace.
    > Then run db2move to extract all the tables(s) data.
    >
    > Then use db2move in load mode on your target system to get the data back in.
    >
    > HTH, Pierre.
    >
    > Peter Sands wrote:
    >> Hi,
    >> I have a database that contains many ( at least 12) tablespaces , I
    >> wish to restore the DB with re-direct but restore the user tables into
    >> only one tablespace ( container) excl sys tablespaces...
    >> Can I do this, if so what would be the 'set tablespace containers'
    >> command for this.
    >>
    >> If I cannot do this, would would be the best alternative, to move the
    >> user data into one table space.
    >> aix 5.2
    >> db2 v8.1
    >>
    >> thansk
    >> PS
    Philip Nelson Guest

Similar Threads

  1. redirect to guest if first redirect is doesnt work for a user
    By istalcup@hotmail.com in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 17th, 03:27 PM
  2. Physical restore that doesn't require a logical restore
    By Christian Eriksson in forum Informix
    Replies: 6
    Last Post: September 24th, 11:30 AM
  3. redirect restore db2
    By xixi in forum IBM DB2
    Replies: 0
    Last Post: July 30th, 08:46 PM
  4. Replies: 3
    Last Post: July 18th, 07:47 AM
  5. Replies: 11
    Last Post: January 9th, 07:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139