JDBC URL question

This is another (in my series) of stupid newbie questions. This one falls under the category of Things A JDBC Developer Needs To Know About Informix And No More. :-) As always I welcome gentle requests to shove off and read page x through y of some document somewhere. Equally as always, however, I've tried to do my homework. :-) My company (Jenzabar.com) is a heavy Informix shop. People will use verbal shorthand to refer to servers that you need to connect to to do work. They'll say, for example, that they're "connected to devfoo". This means, in layman-speak, to me, that they are connected to an Informix database on some unspecified port on (and here's where things get murky for me) some host named devfoo. Why would that be murky for me, you ask? Because there is the concept of the host and the (unfamiliar to me, a dude with a JDBC client that needs a URL) concept of an INFORMIXSERVER. These are often different. In JDBC terms, you might have a URL that looks like this: jdbc:informix-sqli://devnotfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devfoo ...or, I suppose, like this: jdbc:informix-sqli://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo So where can I read more about what the difference is between the INFORMIXSERVER in this URL scheme and the host? Does this mean that, for example, I could have an Informix process running on the honest-to-goodness host (on port 1001 in this case), but serving up databases somehow from the INFORMIXSERVER? (Does such a sentence even have meaning?) In such a case, then, is it more correct to say (in shorthand) that I'm connected to the INFORMIXSERVER value, or to the hostname? Thanks much, Laird --0015174be8a8a1748e04a5d6adb2

Hello. The relationship between INFORMIXSERVER and hostname will depend on your sqlhosts file. You can even have more than 1 INFORMIXSERVER on the same hostname (we call it multiple residency) ;) So be carefull, instead of using hostname, I recommend you to say "connecte to INFORMIXSERVER". In case you have only one Informix instance for each machine (host), you might create a database table, relating the two different names, and then you could easily show it to the user, right? I think that answer your doubt (our doubts are never silly, believe me)! Best regards. Em 16/06/2011 12:22, Laird Nelson escreveu: > This is another (in my series) of stupid newbie questions. This one falls > under the category of Things A JDBC Developer Needs To Know About Informix > And No More. :-) As always I welcome gentle requests to shove off and read > page x through y of some document somewhere. Equally as always, however, > I've tried to do my homework. :-) > > My company (Jenzabar.com) is a heavy Informix shop. People will use verbal > shorthand to refer to servers that you need to connect to to do work. > They'll say, for example, that they're "connected to devfoo". This means, > in layman-speak, to me, that they are connected to an Informix database on > some unspecified port on (and here's where things get murky for me) some > host named devfoo. > > Why would that be murky for me, you ask? Because there is the concept of > the host and the (unfamiliar to me, a dude with a JDBC client that needs a > URL) concept of an INFORMIXSERVER. These are often different. > > In JDBC terms, you might have a URL that looks like this: > > jdbc:informix-sqli://devnotfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devfoo > > ....or, I suppose, like this: > > jdbc:informix-sqli://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo > > So where can I read more about what the difference is between the > INFORMIXSERVER in this URL scheme and the host? Does this mean that, for > example, I could have an Informix process running on the honest-to-goodness > host (on port 1001 in this case), but serving up databases somehow from the > INFORMIXSERVER? (Does such a sentence even have meaning?) In such a case, > then, is it more correct to say (in shorthand) that I'm connected to the > INFORMIXSERVER value, or to the hostname? > > Thanks much, > Laird > > --0015174be8a8a1748e04a5d6adb2 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11

OK Laird, here's a basic Informix education. Most of this you can get piecemeal from different parts of the Informix Administrator's Guide and Administrator's Reference manuals and I would recommend that as a source for more detail. Look especially at discussions of the sqlhosts files and the configuration variables I have highlighted in upper case below. Here are the basics: An Informix database engine or server is known as an instance for simplicity and to reduce confusion as to what is meant by "server". An Informix instance can manage multiple databases whose names are also part of your JDBC URL string. A host or machine can have multiple instances running on it each of which is identified internally by a SERVERNUM integer which clients do not need to know about and a DBSERVERNAME which links the instance to a particular communications method or protocol (share memory, named stream pipe, TCP/IP using Informix's native "SQLI" protocol, or TCP/IP using IBM's DRDA protocol), IP address (or addresses), and service or port number. In addition, an Informix instance can communicate with different clients using different protocols so there is also a list of DBSERVERALIASES - additional names each linked, as is the DBSERVERNAME, to a particular protocol etc. By default, Informix's client libraries for ESQL/C (and so used by some other host languages like 4GL and Perl DBD/DBI) use the name or alias specified in the environment variable INFORMIXSERVER to identify what protocol to use and to which host and port to direct requests. ODBC uses the odbc.ini file on UNIX or the registry entries for data sources on Windows to link an ODBC data source name to the instance's name or alias etc. Java either uses the underlying ODBC mechanisms or the URL string you allude to for the same thing. So, let's break down your URL: jdbc:informix-sqli://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo software protocol: Native Informix SQLI hardware protocol: TCP/IP (default) host: devfoo.jenzabar.com<http://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo > port for initial connection request: 1001 database name: ngp servername/alias: devnotfoo Hope this helps. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jun 16, 2011 at 12:22 PM, Laird Nelson <ljnelson@gmail.com> wrote: > This is another (in my series) of stupid newbie questions. This one falls > under the category of Things A JDBC Developer Needs To Know About Informix > And No More. :-) As always I welcome gentle requests to shove off and read > page x through y of some document somewhere. Equally as always, however, > I've tried to do my homework. :-) > > My company (Jenzabar.com) is a heavy Informix shop. People will use verbal > shorthand to refer to servers that you need to connect to to do work. > They'll say, for example, that they're "connected to devfoo". This means, > in layman-speak, to me, that they are connected to an Informix database on > some unspecified port on (and here's where things get murky for me) some > host named devfoo. > > Why would that be murky for me, you ask? Because there is the concept of > the host and the (unfamiliar to me, a dude with a JDBC client that needs a > URL) concept of an INFORMIXSERVER. These are often different. > > In JDBC terms, you might have a URL that looks like this: > > jdbc:informix-sqli://devnotfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devfoo > > ....or, I suppose, like this: > > jdbc:informix-sqli://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo > > So where can I read more about what the difference is between the > INFORMIXSERVER in this URL scheme and the host? Does this mean that, for > example, I could have an Informix process running on the honest-to-goodness > host (on port 1001 in this case), but serving up databases somehow from the > INFORMIXSERVER? (Does such a sentence even have meaning?) In such a case, > then, is it more correct to say (in shorthand) that I'm connected to the > INFORMIXSERVER value, or to the hostname? > > Thanks much, > Laird > > --0015174be8a8a1748e04a5d6adb2 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071d088d296db04a5d8368e

On Thu, Jun 16, 2011 at 2:12 PM, Art Kagel <art.kagel@gmail.com> wrote: > OK Laird, here's a basic Informix education. Great! Most of this you can get > piecemeal from different parts of the Informix Administrator's Guide and > Administrator's Reference manuals and I would recommend that as a source > for > more detail. Happy to do so; it's just hard to know where to start (it's the "piecemeal" part). I also now have in my hands a Redbook that compares Informix to SQL Server--which I don't particularly care about, but it has the happy side effect of giving an overview of the architecture. > An Informix database engine or server is known as an instance for > simplicity > and to reduce confusion as to what is meant by "server". An Informix > instance can manage multiple databases whose names are also part of your > JDBC URL string. A host or machine can have multiple instances running on > it each of which is identified internally by a SERVERNUM integer which > clients do not need to know about and a DBSERVERNAME which links the > instance to a particular communications method or protocol (share memory, > named stream pipe, TCP/IP using Informix's native "SQLI" protocol, or > TCP/IP > using IBM's DRDA protocol), IP address (or addresses), and service or port > number. OK, that makes sense. Question: if I'm connecting to a TCP port 1001, haven't I already pretty much established the communications protocol (TCP + SQLI) and port number (1001)? In this case, why do I need to specify a name that serves to group this information? Can't it just be inferred from the fact that someone answered the phone on 1001 and understood my request in SQLI format? > Java either uses the underlying ODBC mechanisms or the URL string you > allude to for the same thing. > I guess my point is: can't all that information be inferred WITHOUT the INFORMIXSERVER variable, if I'm connecting with the JDBC driver to a particular host on a particular port speaking a particular communications protocol? (Clearly it cannot, or you wouldn't have this INFORMIXSERVER variable as a requirement, but I don't see how.) > jdbc:informix-sqli://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo > > software protocol: Native Informix SQLI > Yep. > hardware protocol: TCP/IP (default) > Yep. > host: > devfoo.jenzabar.com< > http://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo>; > OK, yep. > port for initial connection request: 1001 > Yep. > database name: ngp > Yep. > servername/alias: devnotfoo > OK, this is where I pause. :-) I'm already connected to *something* on host devfoo, port 1001, speaking SQLI--you refer to this as the "initial connection", suggesting that perhaps other connections (by me? by...?) will be made soon--so what extra value does devnotfoo add here? What does it convey? Surely it doesn't just convey TCP/SQLI/port1001, because I've already got that. If instead it conveys that I should be looking up this configuration from the sqlhosts file, or otherwise dereferencing it into the various settings it stands in for, what happens if those settings (which include things like communications protocol) specify a different port, or a shared memory configuration? I'm already hooked up via TCP to port 1001, so what good would any alternate information here do me? (A quick side note to say how much I appreciate the community's help here. I'm really REALLY grateful.) Best, Laird --0015174be8a8cb17da04a5d8a6ca

See below, inline: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jun 16, 2011 at 2:44 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Thu, Jun 16, 2011 at 2:12 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > OK Laird, here's a basic Informix education. > > Great! > > Most of this you can get > > piecemeal from different parts of the Informix Administrator's Guide and > > Administrator's Reference manuals and I would recommend that as a source > > for > > more detail. > > Happy to do so; it's just hard to know where to start (it's the "piecemeal" > part). I also now have in my hands a Redbook that compares Informix to SQL > Server--which I don't particularly care about, but it has the happy side > effect of giving an overview of the architecture. > > > An Informix database engine or server is known as an instance for > > simplicity > > and to reduce confusion as to what is meant by "server". An Informix > > instance can manage multiple databases whose names are also part of your > > JDBC URL string. A host or machine can have multiple instances running on > > it each of which is identified internally by a SERVERNUM integer which > > clients do not need to know about and a DBSERVERNAME which links the > > instance to a particular communications method or protocol (share memory, > > named stream pipe, TCP/IP using Informix's native "SQLI" protocol, or > > TCP/IP > > using IBM's DRDA protocol), IP address (or addresses), and service or > port > > number. > > OK, that makes sense. Question: if I'm connecting to a TCP port 1001, > haven't I already pretty much established the communications protocol (TCP > + > SQLI) and port number (1001)? In this case, why do I need to specify a name > that serves to group this information? Can't it just be inferred from the > fact that someone answered the phone on 1001 and understood my request in > SQLI format? > Under most conditions, yes it can, and really the instance doesn't care which of its names (aliases) you use to connect to it. In JDBC and ODBC land, the INFORMIXSERVER has been mostly redundant and there for historical reasons. In the case of ESQL/C library based apps the INFORMIXSERVER may also be redundant if the user/application connects to the database using a name that specifies the server (so: CONNECT TO "ngp@devnotfoo" rather than using CONNECT TO "ngp"; which then has to know the default INFORMIXSERVER to connect to in order to find database 'ngp'. However, I cheated a bit for simplicity. INFORMIXSERVER does not necessarily name an Informix instance. It could name a server group, a Connection Manager instance, or even a group referencing several Connection Manager instances. Groups have been around since the early Informix 7.xx days and are used either to identify Enterprise Replication relationships between servers and/or to create a connection name that can refer to any of several servers in case one or more of them are offline, in which case the connection request would attempt to connect to each server in the group sequentially until one responds. In this case only the name is used to retrieve the definition of the server group. That definition has the host/port/protocol information for each instance in the group. Similarly, since Informix 11.10 introduced the Connection Manager, which handles load balancing including prioritizing preferences as to which users are connected to which servers first, the INFORMIXSERVER name might actually be the name of a Connection Manager instance which will be managing many such names each of which refers to a different SLA preference lists. The Connection Manager will have a single host, and port (and the protocol will be Connection Manager protocol over TCP/IP) but be managing many SLA names. So here too, the name is important. Connection Manager works with ODBC/JDBC connections also while server groups do not. Using Connection Manager, the CM connection only supplies the actual connection details for the server instance that it selects for this connection request and the underlying library closes the port and reconnects directly to the supplied server instance using that information. > > > Java either uses the underlying ODBC mechanisms or the URL string you > > allude to for the same thing. > > > > I guess my point is: can't all that information be inferred WITHOUT the > INFORMIXSERVER variable, if I'm connecting with the JDBC driver to a > particular host on a particular port speaking a particular communications > protocol? (Clearly it cannot, or you wouldn't have this INFORMIXSERVER > variable as a requirement, but I don't see how.) > See above. > > > jdbc:informix-sqli:// > devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo > > > > software protocol: Native Informix SQLI > > > > Yep. > > > hardware protocol: TCP/IP (default) > > > > Yep. > > > host: > > devfoo.jenzabar.com< > > http://devfoo.jenzabar.com:1001/ngp:INFORMIXSERVER=devnotfoo>; > > > > OK, yep. > > > port for initial connection request: 1001 > > > > Yep. > > > database name: ngp > > > > Yep. > > > servername/alias: devnotfoo > > > > OK, this is where I pause. :-) I'm already connected to *something* on > host devfoo, port 1001, speaking SQLI--you refer to this as the "initial > connection", suggesting that perhaps other connections (by me? by...?) will > be made soon--so what extra value does devnotfoo add here? What does it > Ahh, just an old net-dog spouting netspeak. If you are interested: When one attempts to connect to a TCP/IP service's listener over its defined port, it responds by opening a new connection on a new port number, returns that new port number to the client requesting the connection, and goes back to listening for connection requests. At the sockets network library level the client calls the connect() system call, the listening service is blocked on the listen() system call until the connection comes in. When listen() returns, the service task creates the secondary connection (called "binding a port" to the client) using the bind() system call (TLI library functions for this are equivalent but named differently). All further communications from that client are conducted over that secondary connection. I was just differentiating the final data connection and port from the initial connect request and its port. So, in your case, your apps only use the 1001 port for the initial connection request, they actually communicate with Informix over that private secondary connection port. > convey? Surely it doesn't just convey TCP/SQLI/port1001,