SMS or DMS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

netzorro - 01 Jan 2008 18:03 GMT

Hi all,
without considering DMS in a device, normally, the simplest choices are SMS
or DMS in files.
Is there any performance improvement using a DMS tablepsace container in a
file instead of a SMS ?

Also, if a SMS tablespace container is used, does it make sense to
reorganize the tables?

Thanks
Diego

Reply to this Message

Phil Sherman - 01 Jan 2008 19:00 GMT

> Hi all,
> without considering DMS in a device, normally, the simplest choices are SMS
[quoted text clipped - 7 lines]
> Thanks
> Diego

Reorgs always make sense. Data disorganization, usually caused by
inserts/updates is "repaired" by reorg. Deletes leave extra freespace
which can increase the I/O load to process the data (when compared to a
freshly reorg'd table).

Depending on your platform, DMS (files) may have some performance
advantages over SMS. Performance and tuning guides have good
presentations of the differences. A big downside to DMS is that you need
to pay a lot more attention to space management.

Phil Sherman

Reply to this Message

netzorro - 01 Jan 2008 19:31 GMT

>> Hi all,
>> without considering DMS in a device, normally, the simplest choices are
[quoted text clipped - 21 lines]
>
> Phil Sherman

Thanks for your answer, my point is in a file dms you can have "holes". But
is you have one file for each table and index (in SMS) the reorg does not
seem to make sense.
diego

Reply to this Message

Mark A - 01 Jan 2008 19:37 GMT

> Thanks for your answer, my point is in a file dms you can have "holes".
> But is you have one file for each table and index (in SMS) the reorg does
> not seem to make sense.
> diego

The holes are not in the files, they are in the pages. DB2 stores data in
pages (4K, 8K, 16K, 32K) depending on the size chosen when the database was
created.

Or in some cases there are no holes when you actually want a hole in the
page (percent free) for new inserts to be placed in the correct sequence (if
you have a clustering index). A reorg will re-established the designated
percent free.

Reply to this Message

Mark A - 01 Jan 2008 19:30 GMT

> Hi all,
> without considering DMS in a device, normally, the simplest choices are
[quoted text clipped - 8 lines]
> Thanks
> Diego

When you create a table (or when DB2 creates a system temporary table inside
a query) DB2 spends a lot more time finding the optimal space inside the
tablespace with DMS. This includes (but is not limited to) trying to use
contiguous space for the table inside the tablespace. In other words, DB2
can create a SMS table faster.

For regular tables that are not created during a DB2 query execution, DMS is
the preferred choice. For system temporary tables and user temp tables, you
don't want to spend the extra time having DB2 find the optimal space within
the tablespace for a table that will only be used once, so SMS is preferred.

If you have a small database where the entire database is in the bufferpool
at all times, then it will not make any difference.

If your data is larger than the bufferpools but the bufferpool hit ratio is
very high (98%+), it will probably not make any difference. However, DMS
gives you the flexibility to put indexes and tables in different
tablespaces, which could be an important determining factor in your
bufferpool hit ratio.

Reply to this Message

Jean-Marc Blaise - 01 Jan 2008 22:06 GMT

> Hi all,
> without considering DMS in a device, normally, the simplest choices are
[quoted text clipped - 8 lines]
> Thanks
> Diego

Hi Diego,

From version 8.2.2 (8.2 FP9), IBM has introduced Automatic Storage
Management, which is the default in version 9 when creating new databases.
This makes things pretty simple: DMS for everything except system and user
temporary tablespaces. You just have to think of the total planned size for
your db and decide how many file systems to use (fs are on different disks):
create db mydb automatic storage yes on path1,path2,path3 ... dbpath on
path4. DB2 will take care of all containers, spreading for each tablespace 1
container per storage path, extending tablespaces automatically as needed.
Therefore, you only have to monitor your storage path usage.

REORG is independant of tablespace type and DB2 auto-maintenance can reorg
tables for you, only the ones needed.

Hope this helps,

Jean-Marc

Reply to this Message