using multiple Informix DB Servers w/same name, different Host IPs in ODBC

[cjones]

I need to solve a situation where we have many detached sites running
the same software built around and Informix database. The Database
Server Name at all these sites is the same. I need to access these
sites from my pc. I have no problem setting up an ODBC Data Source to
one of them and getting in but when I set up the Data Source to the
second, which has the same Server Name but a different Host Name (IP
address) Window will change the IP address in the previously set up
ODBC Data Source to that I put in the second ODBC Data Source. I've
found Windows seems to only associate one Host Name (IP address) with
one Server Name. I need to get around this. In fact I'd like to be
able to have connections to several of these sites' databases
simultaniously if possible. I give each of them a unique ODBC Data
Source Names.

Help! and Thanks.

[da...@smooth1.co.uk]

You can't.

Informix server name is mapped via sqlhosts (file on UNIX, registry on
windows) to a single host/port number
configuration. This must match the DBSERVERNAME entry in the
$INFORMIXDIR/etc/$ONCONFIG file on
UNIX/Windows.

If the remote sites are UNIX then telnet into the sites and run command
from there.

Otherwise you will need to

- Create a seperate unique DBSERVERALIAS in the $ONCONFIG file on each
site

- add an entry in sqlhosts file on each site to map the DBSERVERALIAS
to the hostname and service (port number).
This should be a TCP/IP sqlhosts entry.

- Restart the database server to start another listener for the alias.

- Add each data source with the unique DBSERVERNAME to your Windows PC
so that you can access
each one.

I don't know if you currently use DBSERVERLIAS but you can have more
than one of them (up to 32 I think).

Which IDS version are you using? There were problems with too many
DBSERVERALIASES in early 7.x releases
up to 7.31.UD?.

Informix Database Server version and which platform the database
servers are on would help. But the
general idea is to add unique DBSERVERALIASES at each site and restart
the database servers to pick up the
change.

If this is UNIX and all configs are the same but hostnames are
different then you could develop a script to
automatically add the DBSERVERALIAS based on the hostname to the
onconfig file. (Save the old one to $ONCONFIG.$HOSTNAME.DDMMYYHH:MM:SS.
Any use grep -v to remove the alias before hand in
case some idiot runs the command more than once!).

You might need to change NETTYPE entries as well.

So the procedure would be

a) stop the database server
b) Run the script and manually check results (2-3 mins max - just ls
-lrt to get name of the backup of the
old onconfig and diff the old and new to make sure the change is done
ok).
c) Add new service name to /etc/services or window services file.
c) start the database server and use onstat -g glo to make sure the
extra NET VPs are started.
d) netstat -a to make sure that the informix server is listening on the
new port (something listening on the
new service name).
e) export DBSERVERNAME=new alias name
f) dbaccess sysmaster to check you can connect ok. select * from
sysdatabases to check.

Should be 15 mins outage max. Practice beforehand on a test server.

[cjones]

Thanks for your reply. Politics prevents me from changing the Informix
databases so adding aliases to the Host/DB Server is out. I guess I'll
have to rig something else. I can get to them all one at a time. The
hosts are Unix but I'm not really a Unix or Informix guy. I work
mostly in the Windows world.

Your the fellow loosing your job aren't you? I feel for you, I've been
there and can sympathize with the conditions surrounding your job loss
too. I take it your in the UK. I'm in America. A lot different today
from the late 90's when Y2K had everyone scared and IT jobs were
everywhere.

I really appreciate your in-depth answer. At least fully understand
what's going on and have a good idea on the fix--even if I seriously
doubt that will ever happen.

This does bring to mind another question. It might be outside your
area of expertise but if you know please let me know. If this was a
Microsoft SQL Server would I be having the same problem?

[cjones]

I have a related issue you might be able to help me with. Can you
explain the following sqlhost file found on the UNIX box? I think I
know but, well, I don't. Any comments I'd appreciate.

g_gso1 group - - i=1
#accounting ontlitcp gso1 1526 g=g_gso1
accounting1 ontlitcp gso1 1527 g=g_gso1
g_gso2 group - - i=2
#accounting ontlitcp gso2 1526 g=g_gso2
accounting2 ontlitcp gso2 1527 g=g_gso2
g_gso3 group - - i=3
accounting ontlitcp gso3 1526 g=g_gso3
accounting3 ontlitcp gso3 1527 g=g_gso3

[da...@smooth1.co.uk]

The looks like Enterprise Replication.

>From what I can see you have 3 groups and the g= at the end defines
which group the Informix server is in.

You really need to get someone involved who is an enterprise
replication expert.

I don't know much about ER - something I keep thinking about playing
with!

[cjones]

Thanks, I think I have a handle on it now. Good luck in your job
search.

[Denis Melnikov]

Try to utilize hosts file (it located in %windir%\system32\drivers\etc).
Do not use IP in Data Source definition but hostname. And change
IP in hosts file.