Many IBM shops have FTP locked down for supposed security reasons. But before I start ranting about sloppy security measures in IBM-i shops all over the world; locking down things like FTP while leaving file uploads wide open, *public rights on file shares, inherited authority on commands line access, SQL scripts that are just waiting for naughty hackers to tinker… #aaargh…. deep breath… deep breath… so let’s look at how we can upload database updates from vendors that only supply FTP instructions.

How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP

Problem

We need to upload and install the latest quarterly address updates to the Pitney Bowes Code 1 (Group1) address database.

The installation instructions are sparse (at best) and only include an FTP script for upload

Solution

We can grab the database file and store them on our PC desktop (or location of you choice)

We can upload the files to any IFS file in the IBMi realm and prepare to import it in to the PB database

We can use the IBM-i native FTP from the green screen command line (aka terminal mode) to read the import from file its own IBM-i IFS location and convert from *PCASCII to IBMi code page straight in the the native DB2 database ready for import.

Because we are doing it all from the command line, we are never actually using FTP to transport anything around the network (the IFS upload did that work) we are just using the native IBM-i FTP for the data conversion.

Confused?

You will be….

Upload the USPS Postal Database to IBMi with the IFS (without using FTP)

Get the latest USPS postal database from Pitney Bowes CODE1 it will be named something like

DEP052019_430_EBCDIC.ZIP

Unzip it to the location of your choice and let’s get ready to upload it to the IBM I system using IBMi AWS

Also unzip the GREENBAR documentation which will tell you the file totals. The C1P* document is the USPS document totals.

Unzip it and prepare to upload the database itself (TAPBLK01.CD) to your IBM i Power System.

I upload it to my work folder in the IFS at:

/home/littenn (obviously yours could be anywhere)

Now the new database is sitting the IFS and ready to be imported, we can start the CODE1 Import process and prepare to FTP from the IFS in to the import process import file

First, let’s launch CODE 1

 -- Call g1@@pgms/g1mm01

Take option#5 to go into the CODE1+ Name/Address Coding System

We are going to press F20 for Database Functions

Now we are going to install the database

Press F6 and the IBM I brain will whir for a few seconds, preparing itself for the magic and then it will ask:

So, we are now going to use the IBM I FTP installer to copy the uploaded from file from the IBM I IFS area into the import file in the standard DB2 database.

Native IBM I FTP will basically be doing something like this:

FTP (IP address of IBMi System)
Namefmt 1 (switch to IFS naming)
BIN (turn on BINary mode for ASCII-EBCDIC conversion)
Put  file-in-IFS   file-in-DB2    

FTP Magic:

Quickly double check the name of the USPOSTAL DATABASE file – in my case its /home/littenn/TAPBLK01.CD

Now, as per the PB instructions –

 put /home/littenn/TAPBLK01.CD /qsys.lib/g1c1files.lib/g1file.file     

Now you can exit out of FTP and return the install:

And press F6 to contunue.

It will tell you that it is — Installing US Postal Database… 

This took approximately 2 minutes on my machine. Your mileage may vary.

When the import completes, it will show an audit report like this:

This report will show a list of address totals. You should compare all these totals the to greenbar report that you checked earlier:

Total ZIPIDX records written =           106
Total CITYDB records written =            75
Total CITYNM records written =        61,232
Total COUNTY records written =         3,228
Total Z4CHNG records written =         1,149
Total LCLDB records written =          9,958
Total DTLDB records written =         15,360
Total PTRDB records written =          1,091
Total C1PAL2 records written =           124
... and so on

Now that might seem mightily complicated but it’s a nice workaround for using IFS uploads when you are not able to use FTP.

Of course, if you were able to use FTP you wold have simply bypassed all the IFS shenanigans, start the PB import process, and FTP the data direct into the import file from your PC.

But there we have it.

Hope it helps someone out there

?