Informix 10 Data Base Links Across Servers
Poster asked how to query a remote database (db@server:'owner'.table) across separate Informix 10 instances/hosts, since the syntax only worked locally. Replies confirmed the same syntax works across hosts: the remote server must be defined in the local sqlhosts, and trust/authentication must be set up — the user existing on the remote host plus .rhosts or /etc/hosts.equiv (with sqlhosts options like s=2), or a PAM-enabled port with the sysuser database. Synonyms were suggested to hide the remote name, and a step-by-step rsh/dbaccess test procedure and a blog link were given. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
To All,
Is there a way to access a database link across servers?
I have a view that is using the following syntax to obtain data from another
database. I am trying to get let's say HR data from a HR server to use in an
application on a Financial Server with the following:
SELECT A.DEPTID
, A.MANAGER_ID
FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A
This view works as long as the databases are on the same server.
Would someone provide me with the expertise/syntax as to setup a dblink across
servers in Informix 10.
Yes this is possible ... basically have to have entries for the server in
the sqlhosts file .. and then either hosts.equiv or .rhosts files on the
other server.
If you want you can contact me off list and I can give you an example ...
or call me and we can talk through it ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"STEVEN SCHIEFELBEIN" <sschiefelbein@geico.com>
To:
ids@iiug.org
Date:
01/26/2009 10:33 AM
Subject:
Informix 10 Data Base Links Across Servers [14630]
Sent by:
ids-bounces@iiug.org
To All,
Is there a way to access a database link across servers?
I have a view that is using the following syntax to obtain data from
another
database. I am trying to get let's say HR data from a HR server to use in
an
application on a Financial Server with the following:
SELECT A.DEPTID
, A.MANAGER_ID
FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A
This view works as long as the databases are on the same server.
Would someone provide me with the expertise/syntax as to setup a dblink
across
servers in Informix 10.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
That same syntax will work between server instances also, even if the
servers are on different hosts. As long as the remote server is defined in
the local server's sqlhosts file it will just work. The syntax is:
<database>@<servername>:<tablename>.<columnname>
Art
On Mon, Jan 26, 2009 at 10:31 AM, STEVEN SCHIEFELBEIN <
sschiefelbein@geico.com> wrote:
> To All,
>
> Is there a way to access a database link across servers?
>
> I have a view that is using the following syntax to obtain data from
> another
> database. I am trying to get let's say HR data from a HR server to use in
> an
> application on a Financial Server with the following:
>
> SELECT A.DEPTID
> , A.MANAGER_ID
> FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A>
> This view works as long as the databases are on the same server.
>
> Would someone provide me with the expertise/syntax as to setup a dblink
> across
> servers in Informix 10.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--001636c5a82f08c5fb0461659817
create synonym 'hrprdadm'.PS_DEPT_TBL for
HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL
SELECT A.DEPTID
, A.MANAGER_ID
FROM PS_DEPT_TBL
"STEVEN
SCHIEFELBEIN"
<sschiefelbein@ge To
ico.com> ids@iiug.org
Sent by: cc
ids-bounces@iiug.
org Subject
Informix 10 Data Base Links Across
Servers [14630]
01/26/2009 10:32
AM
Please respond to
ids@iiug.org
To All,
Is there a way to access a database link across servers?
I have a view that is using the following syntax to obtain data from
another
database. I am trying to get let's say HR data from a HR server to use in
an
application on a Financial Server with the following:
SELECT A.DEPTID
, A.MANAGER_ID
FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A
This view works as long as the databases are on the same server.
Would someone provide me with the expertise/syntax as to setup a dblink
across
servers in Informix 10.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You should include the error you're getting when you try across hosts.
I assume it would be an authentitaction problem. If yes, follow this rules:
1- The user must exist on the remote host
2- The user must be trusted on the remote host (.rhosts or /etc/hosts.equiv)
or...
3- ... use a remote port configured for PAM and use sysuser database to
configure "trust" relations...
3) is the most complex, but also most flexible configuration.
2) is very simple, but unfortunately IDS uses the same files as the "r"
commands...
Regards,
On Mon, Jan 26, 2009 at 3:31 PM, STEVEN SCHIEFELBEIN <
sschiefelbein@geico.com> wrote:
> To All,
>
> Is there a way to access a database link across servers?
>
> I have a view that is using the following syntax to obtain data from
> another
> database. I am trying to get let's say HR data from a HR server to use in
> an
> application on a Financial Server with the following:
>
> SELECT A.DEPTID
> , A.MANAGER_ID
> FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A>
> This view works as long as the databases are on the same server.
>
> Would someone provide me with the expertise/syntax as to setup a dblink
> across
> servers in Informix 10.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015174c431c3dcfcc04616d1862
Does anyone have an example of setting up PAM between 2 Aix boxes ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"Fernando Nunes" <domusonline@gmail.com>
To:
ids@iiug.org
Date:
01/26/2009 08:52 PM
Subject:
Re: Informix 10 Data Base Links Across Servers [14638]
Sent by:
ids-bounces@iiug.org
You should include the error you're getting when you try across hosts.
I assume it would be an authentitaction problem. If yes, follow this
rules:
1- The user must exist on the remote host
2- The user must be trusted on the remote host (.rhosts or
/etc/hosts.equiv)
or...
3- ... use a remote port configured for PAM and use sysuser database to
configure "trust" relations...
3) is the most complex, but also most flexible configuration.
2) is very simple, but unfortunately IDS uses the same files as the "r"
commands...
Regards,
On Mon, Jan 26, 2009 at 3:31 PM, STEVEN SCHIEFELBEIN <
sschiefelbein@geico.com> wrote:
> To All,
>
> Is there a way to access a database link across servers?
>
> I have a view that is using the following syntax to obtain data from
> another
> database. I am trying to get let's say HR data from a HR server to use
in
> an
> application on a Financial Server with the following:
>
> SELECT A.DEPTID
> , A.MANAGER_ID
> FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A>
> This view works as long as the databases are on the same server.
>
> Would someone provide me with the expertise/syntax as to setup a dblink
> across
> servers in Informix 10.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015174c431c3dcfcc04616d1862
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Not exactly an example of ant you want:
http://informix-technology.blogspot.com/2007/11/informix-user-authentication-pam
-for.html
Check part II of the article. It has some examples of configuring the
sysuser database for cross instance queries.
If any of the articles (part I at least) looks visually weird that's because
the images are offline and blogspot makes something strange. It should be ok
only next week.... sorry for that.
Regards.
On Tue, Jan 27, 2009 at 1:05 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Does anyone have an example of setting up PAM between 2 Aix boxes ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "Fernando Nunes" <domusonline@gmail.com>
> To:
> ids@iiug.org
> Date:
> 01/26/2009 08:52 PM
> Subject:
> Re: Informix 10 Data Base Links Across Servers [14638]
> Sent by:
> ids-bounces@iiug.org
>
> You should include the error you're getting when you try across hosts.
> I assume it would be an authentitaction problem. If yes, follow this
> rules:
>
> 1- The user must exist on the remote host
> 2- The user must be trusted on the remote host (.rhosts or
> /etc/hosts.equiv)
> or...
> 3- ... use a remote port configured for PAM and use sysuser database to
> configure "trust" relations...
>
> 3) is the most complex, but also most flexible configuration.
> 2) is very simple, but unfortunately IDS uses the same files as the "r"
> commands...
>
> Regards,
>
> On Mon, Jan 26, 2009 at 3:31 PM, STEVEN SCHIEFELBEIN <
> sschiefelbein@geico.com> wrote:
>
> > To All,
> >
> > Is there a way to access a database link across servers?
> >
> > I have a view that is using the following syntax to obtain data from
> > another
> > database. I am trying to get let's say HR data from a HR server to use
> in
> > an
> > application on a Financial Server with the following:
> >
> > SELECT A.DEPTID
> > , A.MANAGER_ID
> > FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A> >
> > This view works as long as the databases are on the same server.
> >
> > Would someone provide me with the expertise/syntax as to setup a dblink
> > across
> > servers in Informix 10.
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0015174c431c3dcfcc04616d1862
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015174c3b6ad6cd09046180a9b9
Normally , I follow by this step:
1. open port between hosts such as firewall or access list . try by ping
command
server1$ ping server2
2. open user .rhosts. Example:
server1: /usr/users/informix/.rhosts (.rhosts in user home)
server2 informix
server2: /usr/users/informix/.rhosts
server1 informix
3. try rsh between host
server1$ rsh server2 ls
server2$ rsh server1 ls
** no password input required **
4. in SQLHOSTS should put an option (column 5) , r=0,s=2 (enable .rhosts
authen)
dbportserver1 onsoctcp server1 1932 r=0, s=2
5. use dbaccess to test
dbaccess > connect > connect > (should remote port)
** user blank for user and passwd **
** no error happens **
test2:
server1$ echo "select count(*) from systables" |dbaccesssysmaster@dbportserver2
Regares,
Jakkrit A.
________________________________
From: Fernando Nunes <domusonline@gmail.com>
To: ids@iiug.org
Sent: Wednesday, January 28, 2009 8:12:25 AM
Subject: Re: Informix 10 Data Base Links Across Servers [14647]
Not exactly an example of ant you want:
http://informix-technology.blogspot.com/2007/11/informix-user-authentication-pam
-for.html
Check part II of the article. It has some examples of configuring the
sysuser database for cross instance queries.
If any of the articles (part I at least) looks visually weird that's because
the images are offline and blogspot makes something strange. It should be ok
only next week.... sorry for that.
Regards.
On Tue, Jan 27, 2009 at 1:05 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Does anyone have an example of setting up PAM between 2 Aix boxes ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From:
> "Fernando Nunes" <domusonline@gmail.com>
> To:
> ids@iiug.org
> Date:
> 01/26/2009 08:52 PM
> Subject:
> Re: Informix 10 Data Base Links Across Servers [14638]
> Sent by:
> ids-bounces@iiug.org
>
> You should include the error you're getting when you try across hosts.
> I assume it would be an authentitaction problem. If yes, follow this
> rules:
>
> 1- The user must exist on the remote host
> 2- The user must be trusted on the remote host (.rhosts or
> /etc/hosts.equiv)
> or...
> 3- ... use a remote port configured for PAM and use sysuser database to
> configure "trust" relations...
>
> 3) is the most complex, but also most flexible configuration.
> 2) is very simple, but unfortunately IDS uses the same files as the "r"
> commands...
>
> Regards,
>
> On Mon, Jan 26, 2009 at 3:31 PM, STEVEN SCHIEFELBEIN <
> sschiefelbein@geico.com> wrote:
>
> > To All,
> >
> > Is there a way to access a database link across servers?
> >
> > I have a view that is using the following syntax to obtain data from
> > another
> > database. I am trying to get let's say HR data from a HR server to use
> in
> > an
> > application on a Financial Server with the following:
> >
> > SELECT A.DEPTID
> > , A.MANAGER_ID
> > FROM HRPRD@psprod_tli:'hrprdadm'.PS_DEPT_TBL A> >
> > This view works as long as the databases are on the same server.
> >
> > Would someone provide me with the expertise/syntax as to setup a dblink
> > across
> > servers in Informix 10.
> >
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0015174c431c3dcfcc04616d1862
>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015174c3b6ad6cd09046180a9b9
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.