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?

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

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.

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.

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.

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.

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