Defining sqlhosts information for high-availability clusters that use Distributed Relational Database Architecture (DRDA) and secure ports

Connection Managers support Distributed Relational Database Architecture™ (DRDA®) connections for high-availability clusters. You must define sqlhosts network-connectivity information for connection management of high-availability clusters that use DRDA. If Connection Managers, database servers, or client applications are outside of a trusted network, you must also create an encrypted password file for security.

About this task

The Connection Manager's sqlhosts file must contain entries for all database servers that it connects to.

To use a file other than $ONEDB_HOME/etc/sqlhosts on a specific host, set the host's ONEDB_ SQLHOSTS environment variable to the alternative file.

Procedure

  1. On the host of each Connection Manager and database server, create sqlhosts file entries for each database server in the cluster, and specify the s=6 secure-port option.
    For example:
    #dbservername  nettype   hostname  servicename  options
     server_1      onsoctcp  host_1    port_1       s=6
     server_2      onsoctcp  host_3    port_2       s=6
     server_3      onsoctcp  host_5    port_3       s=6
  2. On the host of each Connection Manager and database server, add DRDA alias entries. Use a DRDA protocol for the nettype value, and specify the s=6 secure-port option.
    For example:
    #dbservername  nettype   hostname  servicename  options
     server_1      onsoctcp  host_1    port_1       s=6
     server_2      onsoctcp  host_2    port_2       s=6
     server_3      onsoctcp  host_3    port_3       s=6
    
     drda_1        drsoctcp  host_1    port_4       s=6
     drda_2        drsoctcp  host_2    port_5       s=6
     drda_3        drsoctcp  host_3    port_6       s=6
    
  3. On the host of each Connection Manager and database server, create sqlhosts file alias entries for each database server and each DRDA alias.
    For example:
    #dbservername  nettype   hostname  servicename  options
     server_1      onsoctcp  host_1    port_1       s=6
     a_server_1    onsoctcp  host_1    port_7
    
     server_2      onsoctcp  host_2    port_2       s=6
     a_server_2    onsoctcp  host_2    port_8
    
     server_3      onsoctcp  host_3    port_3       s=6
     a_server_3    onsoctcp  host_3    port_9
    
     drda_1        drsoctcp  host_1    port_4       s=6
     a_drda_1      drsoctcp  host_1    port_10
    
     drda_2        drsoctcp  host_2    port_5       s=6
     a_drda_2      drsoctcp  host_2    port_11
    
     drda_3        drsoctcp  host_3    port_6       s=6
     a_drda_3      drsoctcp  host_3    port_12
    
  4. On the host of each Connection Manager, add group entries for the groups of database servers and DRDA entries. Add group options to the individual entries. Use the c=1 group-entry option, so that connection-attempt starting points in the list of group members is random. Use the e=last_member group-entry option so that the entire sqlhosts is not scanned for group members.
    For example:
    #dbservername  nettype   hostname  servicename  options
     g_servers     group     -         -            c=1,e=a_server_3
     server_1      onsoctcp  host_1    port_1       s=6,g=g_servers
     a_server_1    onsoctcp  host_1    port_7       g=g_servers
     server_2      onsoctcp  host_2    port_2       s=6,g=g_servers
     a_server_2    onsoctcp  host_2    port_8       g=g_servers
     server_3      onsoctcp  host_3    port_3       s=6,g=g_servers
     a_server_3    onsoctcp  host_3    port_9       g=g_servers
    
     g_drda        group     -         -            c=1,e=a_drda_3
     drda_1        drsoctcp  host_1    port_4       s=6,g=g_drda
     a_drda_1      drsoctcp  host_1    port_10      g=g_drda
     drda_2        drsoctcp  host_2    port_5       s=6,g=g_drda
     a_drda_2      drsoctcp  host_2    port_11      g=g_drda
     drda_3        drsoctcp  host_3    port_6       s=6,g=g_drda
     a_drda_3      drsoctcp  host_3    port_12      g=g_drda
    
    A password file that is encrypted through the onpassword utility is required for connectivity through secure ports. The entries in the previously shown sqlhosts file are represented in the following password file.
    g_servers   a_server_1  user_1  password_1
    g_servers   a_server_2  user_2  password_2
    g_servers   a_server_3  user_3  password_3
    
    server_1    a_server_1  user_1  password_1
    server_2    a_server_2  user_2  password_2
    server_3    a_server_3  user_3  password_3
    
    a_server_1  a_server_1  user_1  password_1
    a_server_2  a_server_2  user_2  password_2
    a_server_3  a_server_3  user_3  password_3
    
    g_drda      a_drda_1    user_1  password_1
    g_drda      a_drda_2    user_2  password_2
    g_drda      a_drda_3    user_3  password_3
    
    drda_1      a_drda_1    user_1  password_1
    drda_2      a_drda_2    user_2  password_2
    drda_3      a_drda_3    user_3  password_3
    
    a_drda_1    a_drda_1    user_1  password_1
    a_drda_2    a_drda_2    user_2  password_2
    a_drda_3    a_drda_3    user_3  password_3
    
  5. In each database server's onconfig file, set the DBSERVERALIASES parameter to that database server's aliases.
    The onconfig file entry for server_1:
    DBSERVERALIASES a_server_1,drda_1,a_drda_1
    The onconfig file entry for server_2:
    DBSERVERALIASES a_server_2,drda_2,a_drda_2
    The onconfig file entry for server_3:
    DBSERVERALIASES a_server_3,drda_3,a_drda_3
  6. On the host of each client application, create an sqlhosts file entry for each service-level agreement (SLA) in each Connection Manager configuration file.
    The first Connection Manager's configuration file has the following entries:
    NAME connection_manager_1
    
    CLUSTER my_cluster
    {
       ONEDB_SERVER g_servers,g_drda
       SLA sla_primary_1          DBSERVERS=PRI
       SLA sla_primary_drda_1     DBSERVERS=PRI
       SLA sla_secondaries_1      DBSERVERS=SDS,HDR
       SLA sla_secondaries_drda_1 DBSERVERS=SDS,HDR
       FOC ORDER=ENABLED \
           PRIORITY=1
    }
    The second Connection Manager's configuration file has the following entries:
    NAME connection_manager_2
    
    CLUSTER my_cluster
    {
       ONEDB_SERVER g_servers,g_drda
       SLA sla_primary_2          DBSERVERS=PRI
       SLA sla_primary_drda_2     DBSERVERS=PRI
       SLA sla_secondaries_2      DBSERVERS=SDS,HDR
       SLA sla_secondaries_drda_2 DBSERVERS=SDS,HDR
       FOC ORDER=ENABLED \
           PRIORITY=2
    }
    Add the following entries to each client application's host sqlhosts file:
    #dbservername            nettype   hostname   servicename  options
     sla_primary_1           onsoctcp  cm_host_1  cm_port_1
     sla_primary_2           onsoctcp  cm_host_2  cm_port_2
    
     sla_secondaries_2       onsoctcp  cm_host_1  cm_port_3
     sla_secondaries_2       onsoctcp  cm_host_2  cm_port_4
    
     sla_primary_1_drda      drsoctcp  cm_host_1  cm_port_5
     sla_primary_2_drda      drsoctcp  cm_host_2  cm_port_6
    
     sla_secondaries_2_drda  drsoctcp  cm_host_1  cm_port_7
     sla_secondaries_2_drda  drsoctcp  cm_host_2  cm_port_8
  7. On the host of each client application, create sqlhosts file group entries for each group of SLA entries, and add group options to the SLA entries. Use the c=1 group-entry option, so that connection-attempt starting points in the list of group members is random. Use the e=last_member group-entry option so that the entire sqlhosts is not scanned for group members.
    #dbservername            nettype    hostname   servicename  options
     g_primary               group      -          -            c=1,e=sla_primary_2
     sla_primary_1           onsoctcp   cm_host_1  cm_port_1    g=g_primary
     sla_primary_2           onsoctcp   cm_host_2  cm_port_2    g=g_primary
    
     g_secondaries           group      -          -            c=1,e=sla_secondaries_2
     sla_secondaries_2       onsoctcp   cm_host_1  cm_port_3    g=g_secondaries
     sla_secondaries_2       onsoctcp   cm_host_2  cm_port_4    g=g_secondaries
    
     g_primary_drda          group      -          -            c=1,e=sla_primary_2_drda
     sla_primary_1_drda      drsoctcp   cm_host_1  cm_port_5    g=g_primary_drda
     sla_primary_2_drda      drsoctcp   cm_host_2  cm_port_6    g=g_primary_drda
    
     g_secondaries_drda      group      -          -            c=1,e=sla_secondaries_2_drda
     sla_secondaries_2_drda  drsoctcp   cm_host_1  cm_port_7    g=g_secondaries_drda
     sla_secondaries_2_drda  drsoctcp   cm_host_2  cm_port_8    g=g_secondaries_drda
    

Results

Client connection requests to @g_primary_drda are sent by drsoctcp protocol to one of the Connection Managers. If connection_manager_1 receives the request, it uses sla_primary_1_drda to provide the client application with connection information for the primary server. If connection_manager_2 receives the request, it uses sla_primary_2_drda to provide the client application with connection information for the primary server.