Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Question Unanswered: System vs Database Manage Tablespace

    Hi

    Can some one enlighten me as to the operational advantage between defining a tablespace to be manage by the System vs Database.

    Peace!

  2. #2
    Join Date
    Jan 2004
    Location
    Egypt
    Posts
    61
    This applies on DB under AIX and Linux systems

    There are many factors that affect DB2 I/O performance:

    1st is the Disk load balancing ( OS & paging space on physical disks, DB2 filesystems on other physical disks )

    2nd Access time of the logical partion of db2 filesystems ( under AIX you can assign allocation type Center/Outer/Core/Inner )
    try to use the Center part of the disk ( give minimum access time)

    3rd Buffer Pool size of the Database, as much as 70% of the machine memory should be allocated to the DB buffer pools ( in case that the machine is a native DB server )

    4th Type of Table Space ( SMS or DMS ) for unix systems DMS is much faster than DMS specially in large databases

    5th Wait I/O should not be more than 25% of the CPU usage, otherwise there shoud be a need for I/O tunning on the system
    hany heggy,
    IBM certified Professional, AIX system support
    www.melodyhits.tv

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have heard numbers that suggest using DMS tablespaces can increase I/O performance by about 10% where the table is read in its entirety with sequential prefetch. If a small number of rows are accessed randomly via an index, there is very little, if any, difference. I am just quoting what I have heard and have not seen any comprehensive objective test results.

    However, some people suggest SMS for system temporary tablespaces, because the create table overhead is lower than on DMS (DMS spends more time optimizing the placement of the data of disk). This extra DMS overhead would not be noticeable on complex decision support queries (and might pay for itself with better performance on actual data I/O), but could be noticeable on shorter queries and OLTP transactions that needed small temporary tablespaces (for cursors, sorts, etc).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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