.st0{fill:#FFFFFF;}

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

 June 10, 2019

By  NickLitten

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

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

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

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

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)
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 5
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 6
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 7

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
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 8

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

We are going to press F20 for Database Functions

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

Now we are going to install the database

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

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

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

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:

How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 13
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 14
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 15
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 16

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

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

Now, as per the PB instructions –

 put /home/littenn/TAPBLK01.CD /qsys.lib/g1c1files.lib/g1file.file     
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 18

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

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

And press F6 to contunue.

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

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

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

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

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

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
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP 23

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

😊

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • The Pitney Bowes software application selection, is this something we can download and install on our IBM i ? I have never heard of this before.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

    Get In Touch

    I’m always looking for awesome input, feedback and critique!

    >