Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    29

    Question Unanswered: DB2 UDB Prod questions??

    Hello friends

    I am new to DB2 UDB DBA production world.
    And I am Certified DBA for DB2 UDB V8.1 (work knowlege with test databases).

    Looking for and improve my career growth to be as PRODUCTION DBA.

    Desingn phase????
    What type of tablespace do the DBA prefer to go with (SMS or DMS)?
    What is backup strategy? and When will the DBA take full db backup?
    What is the performance parameters he has to take care maximum?
    Is there any command or utility to defragment tablespace (other than reorg)?

    Thanks
    Raj

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What type of tablespace do the DBA prefer to go with (SMS or DMS)?

    DMS usually yields better performance, especially for large tables. Using DMS to stripe containers across multiple physical drives is important for tables that are prefetched, and it enables query parallelism (even without multiple partitions). In order to place the index in a different tablespace than the table (also good for performance), DMS is required. However if you have lots of buffer pool space for an object and it will not get flushed out of the buffer pool, there is not any physical I/O going on after the first read.

    What is backup strategy? and When will the DBA take full db backup?

    This depends on the application window requirements, and also the level of update activity (insert/update/delete). Whenever possible, a full off-line backup is preferred, but sometimes only an on-line or incremental can be done in the small maintenance window available. The longer you go in-between full backups, the longer recovery takes (more logs need to processed in roll-forward recovery). At the moment, on-line backups have some quirks that make it a little dangerous for disaster recovery (especially if you ever loose your log files). IBM is working on improving on-line backups.

    What is the performance parameters he has to take care maximum?

    The main ones are the buffer pool size (this includes defining which tablespaces use which bufferpools), and heaps for connections and applications. The DB2 Performance Wizard does a decent job of tuning these parameters, at least to a good starting point. Defining a temporary tablespace that is large enough (1-3 times the size of your largest table if possible), is also very important.

    Is there any command or utility to defragment tablespace (other than reorg)?

    Indexes can be partially cleaned up on-line with the MINPCTUSED specification in the CREATE INDEX statement, but this needs to be used with caution. That is the only other way besides the REORG that I know about. The REORG command allows on-line reorg with the INPLACE option.

  3. #3
    Join Date
    Oct 2003
    Posts
    29
    Thank you Very much Marcus.

    What is the use of ERWIN tool compared to IBM's control center? I feel IBM's tool is much better than Erwin.

    What are the performance tools most DBA's rely on?

    Thanks
    Raj

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check out the thread above called:
    Read Me: Useful DB2 Stuff

    There are links to tools, some of them free.

    I thought ERWIN was mostly a design tool, but I admit that I have not seen the latest release.

  5. #5
    Join Date
    Oct 2003
    Posts
    29
    Using Unix shell scripts what are all the manual processes can be automated in daily life of DB2 UDB DBA. Please explain the manual processes.

    Thanks in advance
    Raj

Posting Permissions

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