Redesigning Tablespaces in an Existing Database

You may also like...

9 Responses

  1. Dave Nance says:

    Something you left out of consideration and as much as some people don’t like to believe it, Size matters(this isn’t always your table with the most rows, either). I have had to create additional tablespaces due to not having anymore space in a tablespace. Then you have to deal with lowering your High Water Mark as well. Not a fun process. It is much easier to think of these items prior to implementation rather than changing an existing database.

    • Ember Crooks says:

      True – this was especially a problem back before the days of “LARGE” tablespaces – when the limit on a single table in a DMS tablespace on a single partition for 4K page size was 64GB. Luckily there are a number of things we can use to deal with that – the use of LARGE tablespaces, table partitioning, database partitioning, etc. It certainly is a good idea to break out particularly large tables.

  2. Great article with even better SQL.
    There is a lot more to be said about this subject, I agree with Dave. The fact that you are currenly running on V9.7 or even on V10 does not say a lot about the current state of your tablespaces. When you make it a habit to play it save during conversion (redirected restores only) your database could still contains the heritage of the older versions (SMS, non-large non-reclaimable DMS ect) or (do not forget to mention that) tablespaces residing on ext3 filesystems.
    But again, thank you for another great article.

  3. Isaac Munoz says:

    Thanks Ember for sharing your ideas. I agree with Dave: “size matters”. In our case we split every table into their own Data, Index and LOB (if applies) tablespaces. At the end this has given us more opportunity to do either offline/online maintenance or DDL changes and addresses many of the issues you mention above: tables with High DML Activity, need for different page size (Data vs Index vs LOB), FS caching.
    Regards

  4. Naveed Shakur says:

    Hi, What is the best practice defining a DMS container, like should we define one container or it should be multiple specially if all the containers are at the same FS.

    • Ember Crooks says:

      Well, really, you should be using automatic storage on db2 10.1 and later – no need to define the storage at the tablespace level. Prior to 10.1, my opinion is that one container per fully separate I/O path – which usually means just one container. Containers can easily be grown and are not too hard to shrink.

  5. hari says:

    Hi Ember,
    General query regarding number of tablespaces . How many tablespaces does a production db contains .(what is the big number that you had observed in your experience ).

    • Ember Crooks says:

      Back in the bad old days, I saw some databases (maybe in support of PeopleSoft?) where each table was in it’s own tablespace. Actually, I talked to a large national retailer within the last year who still did it that way. The reasoning originally was that in certain conditions the entire tablespace would be locked. Today, that level of locking just doesn’t happen outside of things like offline backups, so the reasoning is not there for that method in my mind.

      The number of tablespaces can quickly multiply when you look at the categories I’ve talked about and the fact that you may need a tablespace of each page size for each of those categories.

      It’s not about hitting a hard number, but really about not shooting yourself in the foot on maintenance, and keeping your reasoning for each tablespace solid.

  1. February 25, 2014

    […] Redesigning Tablespaces in an Existing Database […]

Leave a Reply

Your email address will not be published. Required fields are marked *