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.
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
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.
You will be….
Get the latest USPS postal database from Pitney Bowes CODE1 it will be named something like
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
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
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
What is IBM i Email and SPF?
How to capture IBM-i job info for submitted jobs
How to Install IBM Access Client Solutions (ACS)
5733XJ1 IBM i Access Client Solutions – QuickStartGuide
Install LANSA AXES – Automatic Web Interface for IBM i (AS/400) 5250 Applications
Updating Numeric DTAARA in RPGLE
Going the (Levenshtein) Distance in RPG Free
Don’t hardcode library names in your TURNOVER SQL source #youbigsilly
IFS File system error occurred. Error number 3021?