Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: tablespace design

    hi all,

    We are planing for converstion from SMS to DMS
    few questions related to physical layout

    1) Transactions tables and lookup tablespaces should be separate tablespaces
    2) Data and Index should have separate tablespaces

    where can i include frequently accessed tables
    and tables which is huge in size : for huge tables i can create separate tablespaces

    containers should be spread across number of LUN in the server
    Any suggest would be great help
    regds
    Paul

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    I am doing a similar task but my new TSs are still SMS as well, we are just moving extra-large and heavy accessed tables to their own TSs and see if performance would improve. We create separate BP for each TS as well with the same page size.

    How are you planing to move the tables? The way I am going to do it is:

    - export table
    - 'create table like' in new TS
    - load new table from export file
    - drop old table
    - rename new table to old name
    - create indexes
    - runstats table and indexes
    - rebind package(s) (after all tables moved)

    Pls let me know if there any better way of doing it. Thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How hard would it be to create a tablespace per table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Markham,

    When moving data from A to B, I would highly advise using load from CURSOR. This way you are NOT touching the data. When ever you export data out of the table to a file, you are risking on contaminating this data. By using Cursor you do not need file system large enough to hold your data, nor you are taking a chance with screwing up with data.

    Don't forget to check if you can utilize compression, and if yes. USE it.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Brett Kaiser
    How hard would it be to create a tablespace per table?
    Not hard, but why?

    Some tables you want to put in its own tbs, but not one to one match.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess it depends on the platform

    What happens When you do a LOAD REPLACE

    ???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Cougar8000
    you are taking a chance with screwing up with data.
    That's a new one.....

    You suggest a CURSOR? And INSERTS I guess?

    As compared to an unload and a load?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Brett Kaiser
    I guess it depends on the platform

    What happens When you do a LOAD REPLACE

    ???
    Almost the same as if you did LOAD INSERT. But this time it will drop all the data prior to the load
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by Brett Kaiser
    That's a new one.....

    You suggest a CURSOR? And INSERTS I guess?

    As compared to an unload and a load?
    I guess you never stepped on the broom twice!

    There could be a special char in the data that you have to account for. I have been burned by it once, never again.

    I generally do Cursor followed by the LOAD. Kind of like I just did

    declare c1 cursor for select * from AAA.BBBB
    DB20000I The SQL command completed successfully.

    load from c1 of cursor modified by identityoverride INSERT into AAA.BBBB nonrecoverable

    Started ==> Wed Sep 30 12:25:31 CDT 2009
    Finished ==> Wed Sep 30 12:33:21 CDT 2009

    Number of rows loaded = 41395502
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    LUW or z/OS - who is the winner

  11. #11
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    yes, load from cursor is better for one more reason as well - saving time not doing table export.

    Create indexes took longer than loading a table. And it is better to create indexes after the table is loaded than the other way around - create indexes and than load table.

    Did reorg check after table was moved to new TS and it is needed. So need to add reorg (and runstats) time for an outage.

    A piece of advice - I used 'not logged initially' before loading the table and load was 20-25% faster.
    Last edited by MarkhamDBA; 09-30-09 at 14:55.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Whom ever has a bigger lom
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by MarkhamDBA
    yes, load from cursor is better for one more reason as well - saving time not doing table export.

    Did reorg check after table was moved to new TS and it is needed. So need to add reorg (and runstats) time for an outage.

    A piece of advice - I used 'not logged initially' before loading the table and load was 20-25% faster.
    why would you need a reorg after moving it?

    If you are running load and using nonrecoverable, you are not logging. So, no need to use 'not logged initially'.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Cougar8000
    why would you need a reorg after moving it?

    If you are running load and using nonrecoverable, you are not logging. So, no need to use 'not logged initially'.
    i did reorgchk and it said i would need to do table reorg on primary key index.

    you probably right about nonrecoverable. how abt using 'not logged initially' for create indexes?

    I also changed dbm cfg UTIL_IMPACT_LIM to 100 to speed up all utilities.
    Last edited by MarkhamDBA; 09-30-09 at 15:19.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Cougar8000
    Whom ever has a bigger lom
    lom as in "protiv loma.." or different meaning?

Posting Permissions

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