Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Unanswered: Schema vs. multiple Databases

    I am a newbie to DB2 UDB V9 for Linux, and your help is highly appreciated!

    What's the best practice of:
    (1) Schema vs. multiple Databases -- Should I create a database for each application or create a different schema in ONE database? If later case, then how "big" would the database be able to "tolerate"?
    (2) tablespace design -- In my mainframe implementation, we create a separate tablespace for each table. Do you think that will work best for DB2 on Linux, too?

  2. #2
    Join Date
    Oct 2006
    Posts
    15
    Hi
    You can create multiple schemas for a particular database. You don't have to create database for each and every application for db2. To create a schema db2 create schema <schema name>.

    Regards
    Rajesh Krishnan

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db29999
    (1) Schema vs. multiple Databases -- Should I create a database for each application or create a different schema in ONE database? If later case, then how "big" would the database be able to "tolerate"?
    The answer is "it depends". In general, I think, you should create a separate database for each application, unless you have very compelling reasons not to.

    Quote Originally Posted by db29999
    (2) tablespace design -- In my mainframe implementation, we create a separate tablespace for each table. Do you think that will work best for DB2 on Linux, too?
    The answer is, again, "it depends". Depends on the size of the table, typical access patterns, maintenance requirements (e.g. backup, restore, or load), etc.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Databases have a very different meaning on DB2 z/OS versus DB2 LUW. Unless the applications share the same data, they should be on a seperate database on DB2 LUW.

    Best practice on z/OS is to create a separate tablespace for each table. This is not the case for LUW.

    The main consideration on LUW is the page size (each different page size must have its own tablespace) and bufferpool assignment, since you assign tablespaces to bufferpools. So if you want to have a separate bufferpool for tables and indexes, or a separate bufferpool for very large tables, then they need to be in a separate tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2002
    Location
    St. Louis
    Posts
    16
    Multiple databases or schemas: If the maintenance and management and security needs are similar and compatible then I would suggest a single database with multiple schemas. If they are not compatible then I would suggest not coupling them.

    Tablespaces: IBM use to suggest a tablespace per table. Don't know if that's still the case. The abillity to backup and restore by tablespace can in situations be very useful. You also can get better granularity on bufferpools and apply memory directly to critical tables. On the other hand it feels like overkill and a whole lot more administrative work and if you have RI between tables then the tablespace backup/restore is impacted.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is one other thing that should be considered. If you have multiple databases, these will require more memory than just a single DB with multiple schemas. Each DB needs its own bufferpools, DB memory, etc. Even though putting the different applications in different DB is desirable, it might not be practical. Alot depends on the amount od hardware you will be using.

    Andy

Posting Permissions

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