Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Exclamation Unanswered: Schema-Database relationship

    Hi all,

    DB2 7.1.0.68, AIX 5.2, DB version 9.

    I have created two databases under two different instances and I created one schema. Also have one schema owner having its own objects in both the databases.

    The problem is, I am unable to connect to the second database if I login as schema in to the system. Also I am able to see only one database in the result while executing "db2 list db directory" as schema owner.

    Can any one guide me how to overcome this...

    Thanks in advance...

    ...Ram

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Schema-Database relationship

    Hi Ram,

    your DB2 environment is linked to a single instance. If you want to connect to another instance from the command line, you can catalog the 2nd database at your instance and it should work. Check out the "db2 catalog" commands.

    Johann

    Originally posted by nagasurir
    Hi all,

    DB2 7.1.0.68, AIX 5.2, DB version 9.

    I have created two databases under two different instances and I created one schema. Also have one schema owner having its own objects in both the databases.

    The problem is, I am unable to connect to the second database if I login as schema in to the system. Also I am able to see only one database in the result while executing "db2 list db directory" as schema owner.

    Can any one guide me how to overcome this...

    Thanks in advance...

    ...Ram

  3. #3
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72

    Post

    Thanks for the response...

    I am new to DB2 and these are the first databases I ever created in real time and so this problem might be silly...

    I tried to catalog the db as instance owner using "db2 catalog..." and it is saying the db or alias already exists in the local db directory.

    I tried to do the same as schema owner but it is saying that Schema is not having catalog authority.

    This is all happening in case of second database only.

    If I login as schema and try to connect to first database, every thing is fine. Same procedure I followd for the creation of second database.

    Thanks again...

  4. #4
    Join Date
    Apr 2003
    Posts
    191
    Hi,

    the db2 catalog db commands allow aliasing. Just maybe you have trouble with cataloging and uncataloging databases, so try to catalog the 2nd database using an alias. Then check if schema user can connect.

    Johann

    Originally posted by nagasurir
    Thanks for the response...

    I am new to DB2 and these are the first databases I ever created in real time and so this problem might be silly...

    I tried to catalog the db as instance owner using "db2 catalog..." and it is saying the db or alias already exists in the local db directory.

    I tried to do the same as schema owner but it is saying that Schema is not having catalog authority.

    This is all happening in case of second database only.

    If I login as schema and try to connect to first database, every thing is fine. Same procedure I followd for the creation of second database.

    Thanks again...

  5. #5
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72
    Do you want me to catalog the database as Instance owner?

    I have both the databases with diffrent names. And alias's are same as the db names.

  6. #6
    Join Date
    Apr 2003
    Posts
    191
    Hi,

    does this mean the instance owner already can connect to either db?

    I would like to see the output from the following commands:

    instance-owner$ db2 list database directory

    instance-owner$ echo $DB2INSTANCE

    instance-owner$ db2ilist

    instance-owner$ hostname

    schema-user$ db2 list database directory

    schema-user$ echo $DB2INSTANCE

    schema-user$ hostname

    Johann

    Originally posted by nagasurir
    Do you want me to catalog the database as Instance owner?

    I have both the databases with diffrent names. And alias's are same as the db names.

  7. #7
    Join Date
    Nov 2003
    Location
    California,USA
    Posts
    72
    Hope below is self explanatory of schema,database1,database2,instance1,instance2,etc .

    Please see the last section where I am getting the problem...

    =========AS SCHEMA OWNER===============

    schema $ db2 list database directory

    System Database Directory

    Number of entries in the directory = 1

    Database 1 entry:

    Database alias = DATABASE1
    Database name = DATABASE1
    Local database directory = /home/instance1
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    schema $ echo $DB2INSTANCE
    instance1
    schema $ hostname
    myhost

    =======AS INSTANCE1 OWNER========================

    instance1 $ db2 list database directory

    System Database Directory

    Number of entries in the directory = 1

    Database 1 entry:

    Database alias = DATABASE1
    Database name = DATABASE1
    Local database directory = /home/instance1
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    instance1 $ echo $DATABASE2INSTANCE
    instance1
    instance1 $ db2ilist
    instance1
    instance2
    instance1 $ hostname
    myhost

    =============AS INSTANCE2 OWNER==================

    instance2 $ db2 list database directory

    System Database Directory

    Number of entries in the directory = 1

    Database 1 entry:

    Database alias = DATABASE2
    Database name = DATABASE2
    Local database directory = /home/instance2
    Database release level = 9.00
    Comment =
    Directory entry type = Indirect
    Catalog node number = 0

    instance2 $ echo $DATABASE2INSTANCE
    instance2
    instance2 $ db2ilist
    instance1
    instance2
    instance2 $ myhost
    myhost

    =======================================

    schema $ db2 connect to database1

    Database Connection Information

    Database server = DB2/6000 7.2.5
    SQL authorization ID = schema
    Local database alias = database1

    schema $ db2 connect to database2
    SQL1013N The database alias name or database name "database2" could not be
    found. SQLSTATE=42705

    instance2 $ db2 catalog db database2
    SQL1005N The database alias "database2" already exists in either the local
    database directory or system database directory.
    instance2 $ su - schema
    schema's Password:
    schema $ db2 catalog db isrddb
    SQL1092N "schema" does not have the authority to perform the requested
    command.

    ========================================

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Go to

    www.groups.google.com

    and paste the following string as is in the search box


    "unable to remove database or create it" group:comp.databases.ibm-db2

    The result will have a thread by Larry Menard ...

    That thread should help you ...

    Feel free to ask if you don't understand ...

    BTW, in Unix, for each instance has its own system database directory ... If you are logged on as a non-instance owner, the system directory you see will be dependent on which instance's profile you are running in the .profile file ... Looks like your user 'schema' runs db2inst's )

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Apr 2003
    Posts
    191
    Hi nagasurir,

    you got it mixed all up at this point:


    posted by nagasurir

    instance2 $ db2 catalog db database2
    SQL1005N The database alias "database2" already exists in either the local
    database directory or system database directory.

    ========================================
    Instead of this, catalog instance2 and then catalog database2 as user instance1. Have your service port ready for instance2 (you can get it from /etc/services) and then go along these lines:

    instance1 $ db2 catalog tcpip node <yournode> remote <your-server-IP> server <etc-services-port>

    instance1 $ db2 catalog db database2 at node <yournode>

    You may also want to do yourself a favour and read up on these commands first.

    Johann

Posting Permissions

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