Populating the communications database for use with TCP/IP

If you plan to use Db2 only as a server, you do not need to populate the CDB.

About this task

Begin general-use programming interface information.

For example, Spiffy's USIBMSTODb21 subsystem works as a server for many requesters. It is not necessary for Spiffy to register those requesters in the Db2 CDB.

However, if you intend to request data, you need to enter port numbers or service names in field PORT of table SYSIBM.LOCATIONS, and IP addresses or domain names in field IPADDR of table SYSIBM.IPNAMES. The LINKNAME in table SYSIBM.LOCATIONS is used to search tables SYSIBM.IPNAMES and SYSIBM.LUNAMES.

Updates to the following tables take place whenever a new remote connection is requested, without any need to stop and restart DDF:
  • SYSIBM.LOCATIONS
  • SYSIBM.IPNAMES
  • SYSIBM.IPLIST

If you use RACF PassTickets, the LINKNAME must match the following values at the remote site:

  • LUNAME

    If the remote site is a Db2 subsystem that is defined with only an LUNAME value and no GENERIC LU name value or IPNAME value.

  • GENERIC

    If the remote site is a Db2 subsystem that is defined with a GENERIC LU name value in addition to an LUNAME value, but no IPNAME value.

  • IPNAME

    If the remote site is a Db2 subsystem that is defined with an IPNAME value, which triggers the DDF on the remote Db2 subsystem to activate TCP/IP communications support only.

Procedure

  • Insert rows into the SYSIBM.LOCATIONS table.
    The LOCATIONS table contains the port number or service name that is used to connect to remote locations. The LINKNAME column of the LOCATIONS table corresponds to a row in the IPNAMES table.

    The LOCATIONS table has the following columns that relate to TCP/IP:

    DBALIAS VARCHAR(128) NOT NULL
    The name that is associated with the server. This name is used to access a remote database server. If DBALIAS is blank, the location name is used to access the remote database server. This column does not change database object names that are sent to the remote by using a location qualifier. Use the DBALIAS column to access data at two or more different remote locations when those remote locations have the same name.
    LOCATION CHAR(16)
    The unique network location name, or DRDA RDBNAM, that is assigned to a remote or local system. You must provide location names for any systems from which you request data. This column is the primary key for this table.
    LINKNAME CHAR(8)
    Identifies the TCP/IP attributes that are associated with this location. For each specified LINKNAME, you must have a row in SYSIBM.IPNAMES whose LINKNAME matches the value that is specified in this column. Because this table is used for outbound requests, you must provide a LINKNAME or your requests fail. Do not enter blanks in this column.
    PORT CHAR(32)
    If blank, the default port, 446, is used for TCP/IP communications. Otherwise, the value can be either of the following values:
    • The port number of the remote database server. The number must be one to five characters and left-justified.
    • A TCP/IP service name. The service name is converted to a TCP/IP port number with the getservbyname socket call.

    For example, Spiffy's USIBMSTODB21 location expects a LOCATIONS table that looks like the table below. The location USIBMSTODB21 uses the default DRDA PORT, 446.

    Table 1. Spiffy's LOCATIONS table
    LOCATION LINKNAME PORT
    USIBMSTODB21 LUDB21  
    USIBMSTODB22 LUDB22  
    USIBMSTOSQL1 LUSQLDS 1234
    USIBMSTOSQL2 LUSQLDS DRDA
    For example, add the second row with this statement:
    INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME)
      VALUES ('USIBMSTODB22','LUDB22');
    Because no port number is specified, location USIBMSTODB22 uses the default DRDA port number 446.
    Tip: You do not need a row for the local Db2 in the IPNAMES and LOCATIONS tables. For example, Spiffy's USIBMSTODB21 subsystem does not require a row that shows its own LINKNAME and location name.
  • Insert rows into the SYSIBM.IPLIST table.
    The IPLIST table contains a list of multiple IP addresses that are specified for a given location.

    IPLIST has the following columns:

    LINKNAME CHAR(8) NOT NULL
    This column is associated with the value of the LINKNAME column in SYSIBM.LOCATIONS and SYSIBM.IPNAMES. The values of the other columns in the SYSIBM.IPNAMES row apply to the server that is identified by the LINKNAME column in this row.
    IPADDR VARCHAR(256) NOT NULL
    This column contains an IPv4 or IPv6 address or domain name of a remote TCP⁄IP host of the server. If using WLM domain name server workload balancing, this column must contain the member-specific domain name. If you use dynamic VIPA workload balancing, this column must contain the member-specific dynamic VIPA address.
    • An IPv4 address must be left justified and is represented as a dotted-decimal address. An example of an IPv4 address is 9.112.46.111.
    • An IPv6 address must be left justified and is represented as a colon-hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A, which can also be expressed in compressed form as 2001:DB8::8:800:200C:417A.
    • A domain name is converted to an IP address by the domain name server where a resulting IPv4 or IPv6 address is determined. An example fully qualified domain name is 'stlmvs1.svl.example.com'. The gethostbyname socket call is used to resolve the domain name.
    IBMREQD CHAR(1) NOT NULL WITH DEFAULT 'N'
    This columns indicates whether the row came from the basic machine-readable material (MRM) tape: N=no, Y=yes
  • Insert rows into the SYSIBM.IPNAMES table.
    The IPNAMES table defines the outbound security and hostnames that are used to connect to other systems through TCP/IP.

    IPNAMES has the following columns:

    LINKNAME CHAR(8)
    This value matches that specified in the LINKNAME column of the associated row in SYSIBM.LOCATIONS.
    SECURITY_OUT CHAR(1)
    Defines the security option that is used when local Db2 SQL applications connect to any remote server associated with this TCP⁄IP host. The default, A, means that outgoing connection requests contain an authorization ID without a password.
    USERNAMES CHAR(1)
    This column is used for outbound requests to control translations of authorization IDs. The values 'O' or 'B' are valid for TCP⁄IP connections.
    IPADDR VARCHAR(254)
    This column contains an IPv4 or IPv6 address or fully qualified domain name of a remote TCP⁄IP host.
    • An IPv4 address must be left justified and is represented as a dotted-decimal address. An example of an IPv4 address is 9.112.46.111.
    • An IPv6 address must be left justified and is represented as a colon-hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A, which can also be expressed in compressed form as 2001:DB8::8:800:200C:417A.
    • A domain name is converted to an IP address by the domain name server where a resulting IPv4 or IPv6 address is determined. An example fully qualified domain name is 'stlmvs1.svl.example.com'.. The gethostbyname socket call is used to resolve the domain name.
  • Insert rows into the SYSIBM.USERNAMES table.
    The USERNAMES table contains information that is needed for outbound translation only.

    Remember: Inbound ID translation and come from checking are not done for TCP/IP requesters.

    The USERNAMES table has the following columns:

    TYPE CHAR(1)
    Whether the row is for outbound translation. The value 'O' is valid for TCP/IP connections.
    AUTHID CHAR(8)
    Authorization ID to translate. If blank, it applies to all authorization IDs.
    LINKNAME CHAR(8)
    Identifies the TCP/IP network location associated with the row. A blank indicates it applies to all TCP/IP partners. For nonblank values, this value must match the LINKNAME value in SYSIBM.IPNAMES.
    NEWAUTHID CHAR(8)
    The translated value of AUTHID.
    PASSWORD CHAR(8)
    The password to accompany an outbound request. This column is ignored if RACF PassTickets, or already verified USERIDs are used.

What to do next

After you populate these tables, you can write queries that access data at a remote system.

End general-use programming interface information.