Informix

  • 1.  DBSERVERALIASES problematics with sqlhosts

    IBM Select
    Posted Tue October 26, 2021 04:23 AM
    Hi fellas,

    i have a question regarding Informix with Linux (RHEL7) and the usage of alias names for the informix server.

    For our developers we have a DEV system (e.g. olv16ffpdev) with Informix 14.10FC3WE

    We imported some databases from production there - so developers requested also to make the server responsible under the org. production name ( e.g. oldedemmp)
    So, i added the DBSEVERALIAS line in onconfig and added the name in sqlhosts BUT i have 2 lines then, with same hostname and protocoll. This causes issues due startup, but it works as wanted.
    How i do it "the right way" ?

    ------------------------------
    Marc Demhartner
    ------------------------------


  • 2.  RE: DBSERVERALIASES problematics with sqlhosts

    Posted Tue October 26, 2021 07:11 AM
    As long as you have different ports you should be ok

    Post the sqlhosts ?

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: DBSERVERALIASES problematics with sqlhosts

    Posted Tue October 26, 2021 07:18 AM
    Marc:

    Just change the port number/name for the new entry:

    elendil       onsoctcp  Elbereth        sqlexec-ssl
    elendil_tcp   onsoctcp  Elbereth        sqlexec
    elendil_prod onsoctcp Elbereth   1526

    The combination of hostname/IP and servicename/port# has to be unique in the sqlhosts file and certainly for a server.

    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: DBSERVERALIASES problematics with sqlhosts

    IBM Select
    Posted Tue October 26, 2021 10:29 AM
    Thanks for your feedback - is it then still possible to connect via the 1540/sqlexec Port for Informix under the "elendil_prod" name ? 




    ------------------------------
    Marc Demhartner
    ------------------------------



  • 5.  RE: DBSERVERALIASES problematics with sqlhosts

    Posted Tue October 26, 2021 10:48 AM
    Marc:

    On my system sqlexec isn't 1540:
    $ fgrep sqlexec /etc/services
    sqlexec         9088/tcp                        # Informix SQLI
    sqlexec-ssl     9089/tcp                        # Informix SQLI over SSL

    But, to your question, if the client's have a different sqlhosts file with the names swapped, so that, for example in my case, elendil_prod used 9088, elendil used 1526, and elendil_tcp used 9089 rather than the other way around, that would work, but the name elendil_prod will have to be included in the actual sqlhosts on one of those ports and in the DBSERVERALIASES or DBSERVERNAME. 

    Clients can use the SQLHOSTS (filename only) or INFORMIXSQLHOSTS (full file path) environment variables to point to a different sqlhosts file than the default or whatever one the engine is seeing.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 6.  RE: DBSERVERALIASES problematics with sqlhosts

    Posted Wed October 27, 2021 05:42 AM
    Just to make this more transparent:

    - the database server instance gets the server names from the configuration parameters
      DBSERVERNAME and DBSERVERALIASES.
    - it then needs to find an entry for each of the given names in the sqlhosts file.
    - each of the sqlhosts entries tells the server, which of the following to use for the given name:
        - protocol,
        - hostname (i.e. IP address, e.g. of the network card/interface or local loop back)
        - port number (for TCP/IP at least)
    - the database server then starts a "listener thread" for each of these given names,
      using the specifications (protocol, hostname, port) determined from the sqlhosts file.

    - only one process (a listener thread of a database server in our case) can listen on a given
      port number on a given IP address.
      This is the uniqueness requirement for the combination of hostname and port number
      already pointed out by Art.

    - in the given case (as I understand it), the request is to have two database server instances
      (production and development) use the same server name.
    - this requires two different sqlhosts files, one for each database server.
      (Otherwise, the same server name would appear twice in a single sqlhosts file, and thus the
      database servers using it would not know, which of the two identical names they should use.
      Probably they would both just pick the first one that matches one of the names given in their
      configuration file ...)
    - the separation of the two sqlhosts files is done via the INFORMIXSQLHOSTS environment
      variable, that tells each database server, which of the two sqlhosts file to use.

    - as only one listener process can listen on a given port (on the same machine, i.e. IP address),
      the two entries with the same name (but in the different sqlhosts files) must specify a different
      port number.

    - database clients (production clients vs. development clients) likewise need to know, to which
      hostname and port number they should connect. Usually, they use a sqlhosts file as well, and
      specifying the environment variable INFORMIXSERVER tells a client, which sqlhosts entry to
      use.
    - as the names of the two database servers are the same, a client would equally be "confused"
      when finding two sqlhosts entries with the same server name. (And likely would just pick the
      first one.)
    - the clean solution is that also the clients use different sqlhosts files. Like for the servers,
      these sqlhosts files contain an entry with the same name (and protocol and hostname), but
      with the different port numbers.
    - with that, each database client can use that same name to find the correct (and individual) port
      number, to which it needs to connect.

    - it now becomes clear, that if the two database servers would reside on different machines,
      their different sqlhosts files would contain different hostnames. With that, they could use the
      same port number. But still, because of the same server name, they need to use different
      sqlhosts files (i.e. cannot use the same sqlhosts file via a shared/mounted file system).
    - also, the database clients would need different sqlhosts files as well ... each containing the
      individual hostname for the entry with the same database server name.

    A bit of background included, but I hope can help (rather than confuse more).

    Regards, Martin
    --

    Martin Fuerderer

    Software Engineer, Software Development

    HCL Technologies Ltd.

    Frankfurter Ring 17

    80807 Munich, Germany

    www.hcltechsw.com


    ::DISCLAIMER::

    The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.






  • 7.  RE: DBSERVERALIASES problematics with sqlhosts

    Posted Wed October 27, 2021 06:51 AM
    Martin:

    Thanks, that discussion was comprehensive and I'm sure helped and will help many.

    You almost got Marc's issue correct. Here's how I saw it:
    • There's a production server, call it prod_src
    • There's a development server, call it dev_src, it has an alias dev_tcp
    • They made a copy of one of the prod_src databases on dev_srv
    • The developers, because they don't follow Best Practice, have embedded the name of the production server in their applications, so they want the development server to have an additional alias of prod_srv so they can test their apps without making any changes
    • Marc tried adding a second sqlhosts entry on dev for the prod_srv alias with the same port # as the entry for dev_srv. Obviously that didn't work.
    • Finally Marc asked if there was a way for the developers to connect to the dev_srv server using the prod_srv alias over the port normally assigned to the dev_srv port. That's when I suggested that the developers use a private sqlhosts file.

    Marc: Comments? Corrections?

    Art​

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------