Two databases in one PHD
Robert J.M. Edis
robert.edis@creatcomp.com
Fri, 25 Feb 2000 15:09:08 -0500
G'day again Mark
When I couldn't get the PHD to work with two separate databases I asked the
dba to create a db link for the tables I need from DB2 in DB1. He then
created synonyms for the specific tables so I wouldn't have to qualify them.
The synonyms were declared as private.
The result of this was that Quiz 'hung' when I tried to access one of these
tables. The dba changed the synonyms from private to public and then Quiz
couldn't 'see' thet table at all!
We have a db link to an Oracle 8.05 RDBMS on another machine from our
standard Oracle 7.3.4 RDBMS and this works fine. DB1 and DB2 are both
Oracle 7.3.4 RDBMS's on that same machine. We are investigating.
I think I need to resolve the problem using a db link as the main
application (Visibility) doesn't qualify the tables used. As the other
RDBMS, also a Visibility database, has tables with names common to the
primary RDBMS, I can only access these tables with full table
qualifications. The db link method will get around this.
Regards,
Blue
-----Original Message-----
From: Fry, Mark
To: 'Robert J.M. Edis'
Sent: 2/25/00 5:53 AM
Subject: RE: Two databases in one PHD
Another (probably useless) approach I've just come up with:
What might be useful to you is to create a database link, then use that
name instead of the tns connect descriptor.
Within sqlplus:
sql>create database link blue123
connect to DB1 identified by DB1 using 'DB1.world;
(where the DB1.world is the tnsnames entry)
then you can do things like
sql> select count(*) from order_headers@blue123;
Whatever...
Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com
-----Original Message-----
From: Fry, Mark
Sent: 25 February 2000 10:05
To: Robert J.M. Edis
Subject: RE: Two databases in one PHD
I guess I should have read this message before replying to the last!
Not quite sure why PDL is complaining here. Strange...
OK. Put your environment variables back to the way they were (when your
dictionary *did* compile), and just make the change from DB1 to
DB1.world
(same for DB2) in your dictionary.
I recreated your problem on a machine here with Oracle 7.3.3 and
PowerHouse
733d3, and the tnsname rather than the SID cured it.
Fingers crossed...
Cheers!
Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com
-----Original Message-----
From: Robert J.M. Edis [mailto:robert.edis@creatcomp.com]
Sent: 24 February 2000 16:28
To: 'Fry, Mark '
Subject: RE: Two databases in one PHD
G'day again Mark
I changed the tnsnames.ora file to include the (GLOBAL_NAME=DB1) syntax.
I modified my envionment variables to the following:
$ echo $LD_LIBRARY_PATH
/usr/oracle/product/7.3.4/lib
$ echo $ORACLE_HOME
/usr/oracle/product/7.3.4
$ echo $ORACLE_SID
DB1
$ echo $SHLIB_PATH
/usr/oracle/product/7.3.4/lib:/usr/cognos/ph733d3/lib
$ echo $PATH
/usr/oracle/product/7.3.4/bin:/home/lantan/v63oln:/home/lantan/v63oln/qu
iz:/
binn
$ echo $TNS_ADMIN
/usr/oracle/product/7.3.4/network/admin
I then tried to recreate the test PHD and got following error doing so.
$ rm r*.pdc
$ setdict
Usage: setdict dictionary-name
is not a valid dictionary
$ pdl
P D L (7.33.D3)
Copyright 1997 COGNOS INCORPORATED
Licensed VISIBILITY for customer: 5199320001 GENERAL ELECTRIC ENERGY
UNIX
> create dictionary rdbms_test
0 Errors 0 Warnings.
> Database DB1 type ORACLE open ORACLE@$VORA1@DVEP/$VORA2 owner a
^^^^
*E* Expected : CANCEL CREATE EXIT QUIT REVISE SAVE SET SHOW USE <eol>
APPLICATION ASC DATABASE DESCRIPTION ELEMENT FILE LOAD PERMIT SYSTEM
TRANSACTION USAGE HELP
Is this because the variable SHLIB_PATH was changed from pointing only
to
the PowerHouse shared libruary to the Oracle path first and then the PH
path?
Regards,
Blue
-----Original Message-----
From: Fry, Mark
To: 'Robert J.M. Edis'; 'powerh-l@list.swau.edu'
Sent: 2/24/00 4:20 AM
Subject: RE: Two databases in one PHD
Hi Robert,
What happens if you get to a unix shell and enter
sqlplus $VORA1/$VORA2@DB1
or
sqlplus $VORA1/$VORA2@DB2
Does sqlplus connect OK, or do you get a similar message from TNS?
The syntax you have used in your dictionary looks fine to me.
Is your Oracle TNS listener daemon started?
Are all your environment variables set correctly? (below is an example
of
c-shell syntax I use for Oracle 8 - the same variables are used for
Oracle 7
too)
setenv LD_LIBRARY_PATH /isv/oracle/8.0.3/lib
setenv ORACLE_HOME /isv/oracle/8.0.3
setenv ORACLE_SID ORASOL80
setenv SHLIB_PATH $ORACLE_HOME/lib:$SHLIB_PATH
setenv PATH $ORACLE_HOME/bin:$PATH
What about your $VORA1 and $VORA2 environment variables? Have you
checked
that they contain your username and password?
Here are the entries from tnsnames.ora that I use [hostnames have been
changed to protect the innocent :)]:
ORASOL80 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <hostname of a Cognos ORACLE server>)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80)
)
)
ORASOL80.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <same hostname as above>)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80.world)
)
)
The only real difference I can see is that I have a GLOBAL_NAME set and
you
do not. Might be worth setting it to see if it makes a difference.
Lots of questions, but no definite solutions (so far).
Regards,
Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com
-----Original Message-----
From: Robert J.M. Edis [mailto:robert.edis@creatcomp.com]
Sent: 23 February 2000 16:49
To: 'powerh-l@list.swau.edu'
Subject: Two databases in one PHD
G'day all
Environment: HP/UX 10.20, PH 7.33.D3, Oracle 7.3.4
I am trying to put two database contections in the same PHD. Both
RDBMS's
have the same structure, including table and columns names but a for
different organisations.
My environment variables ORACLE_SID, ORACLE_TERM, ORACLE_HOME and
TNS_ADMIN
are set correctly. The PATH points to .../oracle.7.3.4/bin etc.
My PHD declarations are:
Database DB1 type ORACLE open ORACLE@$VORA1@DB1/$VORA2 owner xxx
Database DB2 type ORACLE open ORACLE@$VORA1@DB2/$VORA2 owner xxx
The tnsnames.ora entries are:
DB1.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = DB1)
))
DB2.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = DB2)
))
Problem:
> acc table1 in db1
*W* SQL START DBE error (ORACLE@$VORA1@DB1/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB1 wasn't found in the data dictionary.
> acc table1 in db2
*W* SQL START DBE error (ORACLE@$VORA1@DB2/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB2 wasn't found in the data dictionary.
>
I looked at the knowledgebase at the Cognos support site and that is how
I
derived the logic above (document: KB57519CS). The document has some
typograhpical errors but I worked around those.
Anyone have an idea as to why this is not working?
Blue
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Subscribe: "subscribe powerh-l" in message body to
majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to
majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Subscribe: "subscribe powerh-l" in message body to
majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to
majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.