Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    31

    Unanswered: Why does IBM suggest one table per database?

    I rather new to DB2 though I've been an MS SQL DBA for well over a decade, and I'm having a hard time grasping the concept of table spaces. I've been reading IBM's notes on Table Spaces here - IBM Information Management Software for z/OS Solutions Information Center - but it says something that I find most illogical:

    As a general rule, you should have only one table in each table space. It is also best to keep only one table space in each database.
    I've written databases with many dozens of tables, and though I'm used to the idea of schema's in MS SQL to keep similar tables organize, but I find it odd that IBM suggests having one table per database. Am I reading this right??? If so this is fundamentally different from every other RDBMS I've worked with. I mean if I need to write a system with 20 or 30 tables then per IBM's 'best practices' I must have 20-30 databases each with a single table space and single table?

    Thanks for any info on this or where my logic is flawed. This really has me scratching my head...

    Sam

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A DB2 table space corresponds pretty closely to a MS-SQL file group. The mechanics are different, but the principles are very much the same.

    I have a number of zOS DB2 instances with over 1000 table spaces and 30,000 tables.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be better to point out that databese name in DB2 z/OS is not related with the qualifier of table name.

    For example, see Syntax and Description of CREATE TABLE statement.
    DB2 10 - DB2 SQL - CREATE TABLE

    Syntax

    Code:
    >>-CREATE TABLE--table-name------------------------------------->
    
           .-,------------------------------.           
           V                                |           
    >--+-(---+-| column-definition |------+-+-)-----+--------------->
       |     +-| period-definition |------+         |   
       |     +-| unique-constraint |------+         |   
       |     +-| referential-constraint |-+         |   
       |     '-| check-constraint |-------'         |   
       +-LIKE--+-table-name-+--+------------------+-+   
       |       '-view-name--'  '-| copy-options |-' |   
       +-| as-result-table |--+------------------+--+   
       |                      '-| copy-options |-'  |   
       '-| materialized-query-definition |----------'   
    
       .-------------------------------------------------------------.   
       V  (1)                                                        |   
    >---------+----------------------------------------------------+-+-><
              +-+-IN--+----------------+-table-space-name-+--------+     
              | |     '-database-name.-'                  |        |     
              | '-IN DATABASE--database-name--------------'        |     
    ...
    ...
    ...
    ...
    ...

    Description

    table-name
    Names the table. The name, including the implicit or explicit qualifier, ...
    ...

    ...
    ...
    ...

    IN database-name.table-space-name or IN DATABASE database-name
    Identifies the database and table space in which the table is created. Both forms are optional.

    ...

    If you specify neither a table space or a database, a database is implicitly created with the name DSNxxxxx, where xxxxx is a five-digit number.
    A table space is also implicitly created.

    ...
    ...

    You may also want to see last half of this page.
    DB2 10 - Introduction - DB2 databases

    DB2 databases

    ...
    ...
    ...

    Reasons to define a database

    In DB2 for z/OS®, a database is a logical collection of table spaces and index spaces.
    Consider the following factors when deciding whether to define a new database for a new set of objects:

    ...
    ...
    ...

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I like Stealth_DBA's explanation here:
    http://www.dbforums.com/db2/1649380-...-database.html

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by samalex01 View Post
    I rather new to DB2 though I've been an MS SQL DBA for well over a decade, and I'm having a hard time grasping the concept of table spaces.
    Are you really using DB2 z/OS, or are you using DB2 LUW (Linux, UNIX, Windows)? These are two completely different products, so make sure you looking at the correct doc.

    As mentioned, DB2 z/OS tablespaces are VSAM Linear Data Sets, which are quite a bit different than any kind of file system on a Windows or UNIX, etc. In DB2 z/OS you don't reorg tables, you rerog tablespaces, so that is also a major reason why it is recommended that each table have its own tablespace. Indexes automatically have their own index spaces (each a separate VSAM data set.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Marcus_A View Post
    Are you really using DB2 z/OS, or are you using DB2 LUW (Linux, UNIX, Windows)? These are two completely different products, so make sure you looking at the correct doc.
    Good point! samalex01 must be using LUW.

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Unfortunately it is not possible to use more than 3 tablespaces per table:
    1 for the data
    1 for the index
    another 1 for the BLOB's

    Do not make the mistake to take me too serious
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dr_te_z View Post
    Unfortunately it is not possible to use more than 3 tablespaces per table:
    Not true. For a partitioned table you can have a separate tablespace for each partition.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    Not true. For a partitioned table you can have a separate tablespace for each partition.
    I could kick myself.... but I won't. You are absulotely right!
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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