Hi,

I am kinda new to the DB2 world. Right now our company wants to set up DB2 for applicatoin development purpose. and the database will be used as a container for testing data. and every user will have their own set of data.

There are two solutions in my mind:
1. create a database for every developer under the same instance.
2. create only one database and every user will be granted the privilege to create tables in the database.

Could anyone help me to figure out which solution is better?

We develop on Windows and have given every develop it's own database on the database server. It's the only way when testing your code without interrupting other developers.

We've created backup and restore scripts with the needed commands (attach, backup , restore) so the developers only have to specify which db to restore.

Thanks for your reply.

I am still confusing ...

. In one database scenario, I will give every user their own tablespace, and every user has their own schema to create tables. How could they interrupt each other?
. In multiple database scenario, each user will be granted the SYSADMN so the user can create or drop database?

Which DB2 platform z/OS, ULW, AS400? Each has different pros and cons.

How are the users writing code to refer to creator/owners of tables. Is it:
- fixed qualified: select ... from owner.table
If so you'll have to give each developer their own database - otherwise everyone will share the same table. This is probably GertK's situation.

- un-qualified: select ... from table
bind ... qualifier(owner)
or dynamically qualified "select ... from " || owner_variable || ".table"
in these you can choose whether you want a shared or separate database.

A lot will depend on how hard it is to manage separate databases.

I'm not sure that I would give developers great authority. The tables they'll be using will have fixed structures. If you give the DBADM (the most I'ld give them) they'll be off creating their own stuff which won't match what others are doing.

James Campbell

Yes, that's our situation. We also have to solve production problems which we need to reproduce using a production database. A developer can restore the prod DB over his own database to find the problem without disturbing the work of other developers.

We use Object REXX scripts to backup/restore a database. A hardcoded SYSADM user/password is used within these scripts to attach/backup/restore and the scripts are tokenized so no one can read them.

We also have REXX scripts to define tables/views/sequences etc.. These scripts use the same user/pw to connect to the db.

Kind regards, Gert

We might have 50 developers or testers using the database to do testing.

the user will create their our tables and pop their own data. The database itself is just a place to hold those tables created by individual users.

James, when you say:

- fixed qualified: select ... from owner.table

The owner here would be the same for every user, right? so in my scenaro, different users won't share the same owner, since the user will create their own tables. In this case, there wouldn't be any conflict, right?

Another question is: how many databases can run at the same time under one instance. is there a limitation?

James and Gertk, Thanks a lot for your help