Lookup IP address host using SQL

Added as part of the recent Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a new scalar function that returns the hostname for an IP address.

The syntax for this scalar function, DNS_LOOKUP_IP, is simple, and can be used in one of two ways:

01 VALUES DNS_LOOKUP_IP(IP_ADDRESS => '123.456.789.12') ; 02 VALUES DNS_LOOKUP_IP('123.456.789.12') ;

Line 1: The scalar function can be used with the parameter name.

Line 2: Or not.

It is a question of personal preference which example to use, as there is just one parameter passed to the scalar function.

Let me try this to lookup the host for the following IP address:

01 VALUES DNS_LOOKUP_IP(IP_ADDRESS => '172.217.1.142')

The IP address I used is the one that is returned when I "ping" google.com from my PC.

The result is:

00001 ------------------------- dfw28s23-in-f14.1e100.net

If no result is returned the IP address is not valid.

What if I wanted to look up my own host?

Rather than having to look up the IP of this computer I can use system global variable: CLIENT_IPADDR

01 VALUES DNS_LOOKUP_IP(IP_ADDRESS => SYSIBM.CLIENT_IPADDR)

Notice that I had to give the library name as well as the global variable's name. If I did not I get the following message:

SQL State: 42703 Vendor Code: -206 Message: [SQL0206] Column or global variable CLIENT_IPADDR not found.

You may not. If you do qualify CLIENT_IPADDR with its library, SYSIBM.

I am not going to show my result as mine will differ from yours, as you are not attached to my host.

A better way to illustrate how this works is to use the DNS_LOOKUP Table function to provide IP addresses that the DNS_LOOKUP_IP scalar function can then look up.

The following statement will look up the IP addresses for google.com and ibm.com, and then look up the hostnames:

01 SELECT 'google.com' AS "Domain", 02 ADDRESS_SPACE_TYPE AS "Type", 03 IP_ADDRESS, 04 DNS_LOOKUP_IP(IP_ADDRESS) AS "DNS lookup" 05 FROM TABLE(QSYS2.DNS_LOOKUP('google.com')) 06 UNION 07 SELECT 'ibm.com',ADDRESS_SPACE_TYPE,IP_ADDRESS, 08 DNS_LOOKUP_IP(IP_ADDRESS) 09 FROM TABLE(QSYS2.DNS_LOOKUP('ibm.com'))

Line 1: The first column of the results will be the domain I looked up.

Line 2: Returns the IP address type, IPV4 or IPV6.

Line 3: The returned IP address.

Line 4: Here I use the DNS_LOOKUP_IP to look up the hostname of the IP address that was returned by DNS_LOOKUP.

Line 5: I am looking up the DNS for "google.com".

Line 6: Here I am using an UNION to combine the results from the two DNS lookups.

Lines 7 and 8: Same columns as I defined on lines 1 – 4.

Line 9: DNS look up for "ibm.com".

Domain Type IP_ADDRESS DNS lookup ---------- ---- ------------------------- ------------------------------- google.com IPV4 142.251.40.78 dfw28s34-in-f14.1e100.net google.com IPV6 2607:f8b0:4000:803::200e dfw28s38-in-x0e.1e100.net ibm.com IPV4 23.64.138.216 a23-64-138-216.deploy.static... ibm.com IPV6 2600:1404:ec00:1289::3831 g2600-1404-ec00-1289-0000-00... ibm.com IPV6 2600:1404:ec00:128f::3831 g2600-1404-ec00-128f-0000-00...

This is a useful addition to be able to validate that an IP resolves to the correct host.

 

You can learn more about the DNS_LOOKUP_IP scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.