Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    29

    Unanswered: multiple databases under different instances

    Hi all,
    I have a question on the possibility to create multiple DBs with the same name under multiple different instances.

    I think Oracle allows it, but I cannot do it with DB2.

    It seems that the DB name (in the create database command) must be unique regardless of the instance where it's created and the client from which it's created.
    Is it really so?
    Isn't there a way to workaround it?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can in fact create databases with the same name in different instances, provided that they are created on different paths. Keep in mind that, since, unlike Oracle, DB2 allows you to activate multiple databases in multiple instances simultaneously, this may lead to conflicts later.

    The bigger question is, why would you want to do that? Aliases must be unique in the client database catalog, so you won't be able to define two databases with the same alias on the client, although they may have the same name on the server.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Stef73 View Post
    Hi all,
    I have a question on the possibility to create multiple DBs with the same name under multiple different instances.

    I think Oracle allows it, but I cannot do it with DB2.

    It seems that the DB name (in the create database command) must be unique regardless of the instance where it's created and the client from which it's created.
    Is it really so?
    Isn't there a way to workaround it?

    Thanks
    You can definitely create the same database in multiple instances on the same server, although it may be a bit more difficult under Windows. If you logon to the server using the instance owner id, the database directory is unique to that instance. Exactly what problem are you having?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2012
    Posts
    29
    Ok I think I solved the issue.

    I can create multiple databases with the same name under multiple different instances provided that they have different alias.
    It's not really a matter of specifying a different db path (if I use a different path without alias the db is not created as well).
    Then I can catalog each db on different clients with the same name (the real db name)

    The instances owner in my case is the same (db2admin) so I didn't try with different owners.

    Thank you all

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I think you are mixing the alias of a database used at the client vs. the name of the database at the server. On the server, you can use the same database name in different instances. At the client, each cataloged database must have a unique name in the scope of the client's database directory. Of course, you'll have to use aliases to make the names of the databases from different servers unique if the names wouldn't be unique otherwise. (Nick mentioned that in the first answer.)

    Also, the database path has to be unique for each database at a single server. Otherwise, you'd get collisions on the database files (which DB2 prevents). But that is a completely unrelated question.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2012
    Posts
    29
    Yes, maybe I was not clear but I meant more or less the same ...
    Simply I was saying that I can use same db name at server in different instances, the only thing that I have to specify when creating them is a different (unique) db alias, otherwise I get an error.
    And yes, for sure they are (and have to be) created on different paths, but only specifying a different path (without specifying a unique alias) would lead to an error as well.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Let's separate a few things more clearly:
    - a database is "created" on the server in a specific instance
    - when a database is connected to, the database alias in the client's database catalog has to be used
    - each database has to have its own, dedicated storage location

    Thus, you do not have to specify different aliases when creating the databases (in different instance). But you need to use unique aliases when you catalog a database in the client's database catalog.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jan 2012
    Posts
    29
    Knut,
    you say

    "you do not have to specify different aliases when creating the databases (in different instance)"

    but if on my server (I have no concerns with clients) I have 2 instances INST1 and INST2, and have a db named DB2TEST (alias DB2TEST) in INST1, then from CLP I execute:

    db2 attach to INST2 user ... using ...
    db2 create db DB2TEST

    I get error SQL1005N The alias DB2TEST already exists...

    while if I execute:

    db2 create db DB2TEST alias MYDB

    the database is correctly created, and now I have 2 dbs with same name (DB2TEST) and different aliases in 2 different instances.

    So to me this means that I have to specify different aliases when creating databases with same name in different instances.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Stef73 View Post
    Knut,
    you say

    "you do not have to specify different aliases when creating the databases (in different instance)"

    but if on my server (I have no concerns with clients) I have 2 instances INST1 and INST2, and have a db named DB2TEST (alias DB2TEST) in INST1, then from CLP I execute:

    db2 attach to INST2 user ... using ...
    db2 create db DB2TEST

    I get error SQL1005N The alias DB2TEST already exists...

    while if I execute:

    db2 create db DB2TEST alias MYDB

    the database is correctly created, and now I have 2 dbs with same name (DB2TEST) and different aliases in 2 different instances.

    So to me this means that I have to specify different aliases when creating databases with same name in different instances.
    To see what databases you already have in each instance (or have remotely catalogued in that instance), issue this command after you attach to each each instance:
    db2 list db directory

    If the database is list as "remote", you may also want to issue:
    db2 list node directory

    Remember that a database server also can have a client definition to another database that is located in a different instance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Markus said it all. Don't mix up client and server database directories/catalogs.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

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