Intro
The guide will demonstrate how to import IP2Location data (DB26 IPv6) in CSV form into IBM Db2 and then query the data in a PHP page.
First of all, you will need to download the IP2Location DB26 IPv6 CSV file.
Download commercial version at https://www.ip2location.com/download?code=DB26IPV6
Extract out the IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV file from the downloaded zipped file. We’ll store ours in C:\myfolder but you can choose any folder you wish.
Important Note
We will not cover installation of Db2 or PHP in this guide. We will assume you have already set up Db2 and PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using a Windows 10 machine.
Preparing the CSV data for import
We will be using a Perl script to create an index field in the CSV data to speed up queries later.
Create a new Perl file called createindex.pl and paste the following code into it:
use strict;
my $dir = "./";
my $filename = $dir . "IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV";
my $filename2 = $dir . "INDEXED.CSV";
my $padchar = "0";
my $padlen = 39;
open IN, "<$filename" or die "Cannot open input file";
open OUT, ">$filename2" or die "Cannot open output file";
while (<IN>)
{
my $line = $_;
if ($line =~ /^"([^"]+)","([^"]+)",(.+)$/)
{
my $ipnumfrom = $1;
my $ipnumto = $2;
my $others = $3;
my $resultfrom = $padchar x ($padlen - length($ipnumfrom));
$resultfrom .= $ipnumfrom;
my $resultto = $padchar x ($padlen - length($ipnumto));
$resultto .= $ipnumto;
print OUT '"' . $resultfrom . '","' . $resultto . '",' . $others . "\n";
}
}
close OUT;
close IN;
Run the Perl script by calling the below command in command prompt:
perl createindex.pl
Importing the CSV data into Db2
In the Db2 Command Line Processor, run the following command to create the database.
CREATE DATABASE ip2loc
After creating the database, you need to connect to it by running the below command. Our username is “db2admin” and password is “12345”. Change it to your own credentials.
CONNECT TO ip2loc USER db2admin USING "12345"
Next, run the following command to create the table.
CREATE TABLE db26ipv6(ip_from CHAR(39) NOT NULL, ip_to CHAR(39) NOT NULL, country_code CHAR(2), country_name VARCHAR(64), region_name VARCHAR(128), city_name VARCHAR(128), latitude DOUBLE, longitude DOUBLE, zip_code VARCHAR(30), time_zone VARCHAR(8), isp VARCHAR(256), domain VARCHAR(128), net_speed VARCHAR(8), idd_code VARCHAR(5), area_code VARCHAR(30), weather_station_code VARCHAR(10), weather_station_name VARCHAR(128), mcc VARCHAR(256), mnc VARCHAR(256), mobile_brand VARCHAR(128), elevation INT, usage_type VARCHAR(11), address_type CHAR(1), category VARCHAR(10), district VARCHAR(128), asn VARCHAR(10), "as" VARCHAR(256), PRIMARY KEY (ip_to))
Now that we have a table, we will commence the import of data from our CSV file into the table.
LOAD FROM "C:\myfolder\INDEXED.CSV" OF DEL INSERT INTO db26ipv6
After the import is done, one last command is needed to make the table ready for queries.
SET INTEGRITY FOR db26ipv6 IMMEDIATE CHECKED
Querying the data in PHP
Now, create a PHP file called test.php in your website.
Paste the following PHP code into it and then run it in the browser:
<?php
$ip = '2600:1F18:45B0:5B00::';
function ip62long($ipv6) {
return (string) gmp_import(inet_pton($ipv6));
}
function queryIP2Location($myip) {
$database = 'ip2loc';
$user = 'db2admin';
$password = '12345';
$conn = db2_connect($database, $user, $password);
if ($conn) {
if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
$myip = '::FFFF:' . $myip;
}
if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
$ipnum = ip62long($myip);
}
// pad ipnum to 39 digits with zeroes in front so we can do string comparison
$ipnum = str_pad($ipnum, 39, '0', STR_PAD_LEFT);
$stmt = db2_prepare($conn, 'SELECT * FROM db26ipv6 WHERE ? <= ip_to ORDER BY ip_to LIMIT 1');
if ($stmt) {
if ($rs = db2_execute($stmt, [$ipnum])) {
if ($result = db2_fetch_assoc($stmt)) {
return $result;
}
else {
die("No record found.<br>\n");
}
}
else {
die("Error: " . db2_stmt_errormsg() . "<br>\n");
}
}
else {
die("Error: " . db2_stmt_errormsg() . "<br>\n");
}
db2_close($conn);
}
else {
die("Error: Could not connect.<br>\n");
}
}
$myresult = queryIP2Location($ip);
echo 'COUNTRY_CODE: ' . $myresult["COUNTRY_CODE"] . "<br>\n";
echo 'COUNTRY_NAME: ' . $myresult["COUNTRY_NAME"] . "<br>\n";
echo 'REGION_NAME: ' . $myresult["REGION_NAME"] . "<br>\n";
echo 'CITY_NAME: ' . $myresult["CITY_NAME"] . "<br>\n";
echo 'LATITUDE: ' . $myresult["LATITUDE"] . "<br>\n";
echo 'LONGITUDE: ' . $myresult["LONGITUDE"] . "<br>\n";
echo 'ZIP_CODE: ' . $myresult["ZIP_CODE"] . "<br>\n";
echo 'TIME_ZONE: ' . $myresult["TIME_ZONE"] . "<br>\n";
echo 'ISP: ' . $myresult["ISP"] . "<br>\n";
echo 'DOMAIN: ' . $myresult["DOMAIN"] . "<br>\n";
echo 'NET_SPEED: ' . $myresult["NET_SPEED"] . "<br>\n";
echo 'IDD_CODE: ' . $myresult["IDD_CODE"] . "<br>\n";
echo 'AREA_CODE: ' . $myresult["AREA_CODE"] . "<br>\n";
echo 'WEATHER_STATION_CODE: ' . $myresult["WEATHER_STATION_CODE"] . "<br>\n";
echo 'WEATHER_STATION_NAME: ' . $myresult["WEATHER_STATION_NAME"] . "<br>\n";
echo 'MCC: ' . $myresult["MCC"] . "<br>\n";
echo 'MNC: ' . $myresult["MNC"] . "<br>\n";
echo 'MOBILE_BRAND: ' . $myresult["MOBILE_BRAND"] . "<br>\n";
echo 'ELEVATION: ' . $myresult["ELEVATION"] . "<br>\n";
echo 'USAGE_TYPE: ' . $myresult["USAGE_TYPE"] . "<br>\n";
echo 'ADDRESS_TYPE: ' . $myresult["ADDRESS_TYPE"] . "<br>\n";
echo 'CATEGORY: ' . $myresult["CATEGORY"] . "<br>\n";
echo 'DISTRICT: ' . $myresult["DISTRICT"] . "<br>\n";
echo 'ASN: ' . $myresult["ASN"] . "<br>\n";
echo 'AS: ' . $myresult["as"] . "<br>\n"; // this is lowercase due to the name being a reserved keyword
?>