Clean Address Maintenance

CLEAN_Address is a software product from Runner Technologies, Inc. It is integrated with the Banner system to provide automatic correction for mailing addresses entered into the Banner database. The service daemons for the product are run from our Banner job submission servers.

Updating address data and daemon software

Note: As of 11/2/2022 this update process has been replaced by Runner EDQ's clean_update process. Example Tickets: 125321, 130014, search "Runner EDQ" in footprints...

Step A. on banjobs6 (test) or banjobs5 (prod) sign in, then xsu to clnaddr_test / clnaddr_prod.

Step B. cd to bin dir: cd /clnaddr/clnaddr_test/clean_update (or /clnaddr/clnaddr_prod/clean_update)

Step C. run: ./clnupdate list --help to see your options

Step D. run: ./clnupdate update (this should update data and core.) take note of any errors as it goes out to Runner and pulls information in.

Step E. Verify that clean address restarted. run: ps -ef|grep cln

Step F. Next run the cleanzip stuff.

Step G. Cleanzip - still as clnaddr_test, cd to: cd /clnaddr/clnaddr_test/clnaddr/bin

Step G.1. Cleanzip - run: ./cleanzip cleanzip_TEST.ini to update the data in the Banner database.

Step G.2. Cleanzip - exit clnaddr_test and then: xsu banner

Step G.3. Cleanzip - as banner user run: sqlplus baninst1 @/banner/TEST/clnaddr/Banner_Interface/scripts/sync_postal_codes.sql (you will be prompted by sqlplus for pw, banpass can help here...)

Step F. Verification: * Verify any unverified addresses using only one of the following methods. NOTE: The address types we are verifying are hard-coded in the script $BANNER_HOME/clnaddr/Banner_Interface/scripts/batch_verify_address.sql, regardless of which method you use.

  1. Submit the Banner job SZPBVAD for each database served by the instance, or
  2. As the "banner" user, run sqlplus baninst1 @$BANNER_HOME/clnaddr/Banner_Interface/scripts/batch_verify_address.sql for each database. Note that the address types to be verified are hard-coded in the batch_verify_address.sql script.

DONE! 11/2/2022

Old way below. Left for informational/history purposes...

NOTE: This procedure has been replaced by the UC4 process flow CLNADDR_UPDATE_PROCESS. All required steps, including downloading the updated media from Runner Technologies, is scripted in the process flow.

Example: IMPORTANT! - When doing this in prod don't need to fill those in as the UC4 job will pull them over from test automatically...Also, check the most recent run of SZPBVAD in UC4 to see if it found any bad addresses. If yes, open a foot print ticket and assign the person who entered the address to fix or mark skip before running in PROD.

From FP 82731

Tech Notes:

The CLNADDR_UPDATE_PROCESS uc4 jobs asks for some parameters to be filled in using info from the EDQ footprint ticket, message below:

Product URL: http://s3.runnertech.com/clnaddr/bi-monthly/clean_address_linux.zip

Password to unzip product: MaybeFrienxxx

DPV media URL: http://s3.runnertech.com/clnaddr/bi-monthly/clean_address_dpv_unix.zip

Password to unzip DPV data : TeachBexxxxx

IMPORTANT! - When doing this in prod don't need to fill those in as the UC4 job will pull them over from test automatically...

Every two months, we receive a set of CDs from Runner Technologies containing the latest address and zip code updates from the US Postal Service. This data stays current for two months from the date of issue. The license code configured for the product indicates to the system when a warning message is issued to Banner clients; our warning message goes out after 4 months. Additionally, we receive an email from Runner Technologies Support with FTP links to download the updates. We usually use the FTP links rather than the CDs for convenience, and the instructions below will assume this.

The CD distribution generally consists of three CDs labeled as follows:

  • USPS data and the Linux executables
  • LACSlink data option
  • DPV data option

If downloaded from Runner's FTP site, there are two files in compressed tar format:

  • USPS data and the Linux executables
  • LACSlink and DPV data

As of June 2013, the CLEAN_Address software installations for PROD and TEST have been separated from the Oracle software installation and from each other.

As of February 2014, the CLEAN_Address software installations for PROD and TEST are installed on the Banner job submission servers for their respective databases.

In the instructions below, replace "test" with "prod" (also, replace "clnaddr_test" with "clnaddr_prod") where appropriate.

Updating the address data

To update the address data, please perform the following steps:

  1. Download the software from Runner Technologies, and upload it to the Banner jobsub servers. The location for the software media will be /clnaddr/clnaddr_test/media for TEST. Remove the existing clean_address_linux and clean_addres_dpv directories. Use the command unzip <filename> to unpack the files.

  2. As the "clnaddr_test" user, issue the command cp -p /clnaddr/clnaddr_test/media/clean_address_dpv/datafiles/* $CLNADDR_HOME/newdata to copy the DPV and LACSlink data to the data staging area.

  3. As the clnaddr_test user, issue the command cd /clnaddr/clnaddr_test/media/clean_address_linux/bin to position yourself in the software staging directory.

  4. As the clnaddr_test user, issue the command ./copy_postal_data.sh to initiate the software and data installation.

  5. You will be reminded to become familiar with the update procedure, and asked if you want to proceed. Respond with y when prompted.

  6. You will be prompted for the location of the update source directory; respond with /clnaddr/clnaddr_test/media/clean_address_linux.

  7. You will be asked to confirm the location of the target directory for the installation; it is /clnaddr/clnaddr_test/clnaddr. If the script presents this directory to you, you may respond with y; otherwise, respond with n, and enter /clnaddr/clnaddr_test/clnaddr when prompted.

  8. If you receive any permissions errors, fix them and start this step again.
  9. You will be presented with a summary of your responses and asked to confirm whether you want to continue. If everything is correct, respond with y; otherwise, respond with n.

  10. New files will be copied to a temporary location. After this, you will be prompted to stop the CLEAN_Address daemons. To do so, go to another window on the same server and issue the following command as an operating system user with sudo privileges: sudo /etc/init.d/clnaddr_test stop. Then return to the original window and press enter.

  11. After the data and software installations are complete, you will be warned about using programs from the "compat" directory. Since we use the executables from the $CLNADDR_HOME/bin64 directory, this is not an issue; press enter to continue.

  12. While the daemons are stopped, change the password of the CLNADDR user in the Banner databases, update the $BANNER_HOME/.siboleth file on the database server for each database with the new password, and update the $CLNADDR_HOME/bin/*<SID>.ini files on the staging server with the new password.

  13. You will then be prompted to start the CLEAN_Address daemons, and the installation script will end. In your other window on this server, as a user with sudo privileges, enter the command sudo /etc/init.d/clnaddr_test start. Then return to your original window.

  14. As the "clnaddr_test" user, enter the command cd $CLNADDR_HOME/bin to locate yourself in the CLEAN_Address executables directory.

  15. As the "clnaddr_test" user, enter the command ./cleanzip cleanzip_TEST.ini to update the data in the Banner database. Repeat this step for all databases being served by this instance of the installation.

  16. As the "banner" user, run sqlplus baninst1 @$BANNER_HOME/clnaddr/Banner_Interface/scripts/sync_postal_codes.sql for each database.

  17. Verify any unverified addresses using only one of the following methods. NOTE: The address types we are verifying are hard-coded in the script $BANNER_HOME/clnaddr/Banner_Interface/scripts/batch_verify_address.sql, regardless of which method you use.

    1. Submit the Banner job SZPBVAD for each database served by the instance, or

    2. As the "banner" user, run sqlplus baninst1 @$BANNER_HOME/clnaddr/Banner_Interface/scripts/batch_verify_address.sql for each database. Note that the address types to be verified are hard-coded in the batch_verify_address.sql script.

  18. Repeat the above steps as needed for other CLEAN_Address servers.

Updating the software

The CLEAN_Address software may also need updating. Currently, we do not have a code repository set up to manage our changes to the objects that require customization in this environment. Therefore, for the time being, we will update the software using the following procedure:

  1. Stop the CLEAN_Address daemons.
  2. Navigate to the bin64 directory of the installation media: cd /clnaddr/clnaddr_test/media/clean_address_linux/bin64.

  3. Copy the contents of the directory to the bin64 directory of the CLEAN_Address home: cp -p * $CLNADDR_HOME/bin64

  4. Also copy the same contents to the bin directory: cp -p * $CLNADDR_HOME/bin

  5. Start the CLEAN_Address daemons.

To do: set up a repository for CLEAN_Address initialization files so that we can perform standard installations of the software and then merge our changes into the result.

RUNNING THE CLEAN ADDRESS JOB ON UC4:

  1. Go to UC4 (Automic)
  2. Click the request button, it is the green triangle on the top left of the screen.
  3. Search for CLNADDR_UPDATE_PROCESS
  4. Fill out the information required. Fill out just the first 2 that required.
    • *Database to update:
    • *CLEAN_Address server:
    • Product URL:
    • Password to unzip product:
    • DPV media URL:
    • Password to unzid DPV data
  5. Submit the Job.

NOTE:

  • In PROD you should not fill out any information and run the CLNDDR_UPDATE_PROCESS

NOTE2: as of 7/8/2019 more information on the UC4 process and jobs -

||||||<tablewidth="1336px"rowstyle="height:18.0pt"width="280px" height="24px" class="xl65" style="height:18.0pt;mso-ignore:

  • colspan;width:210pt">UC4 - Clean Address Update PROD||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="19px" style="width:14pt"> ||||||||<width="162px" style="mso-ignore:colspan;width:122pt">Run on demand only||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="107px" style="width:80pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||<width="64px" style="width:48pt"> ||

JOB/Alias

Description

Calendar

User ID, Scripts on Hosts banjobs5/6

PARM IN

CLNADDR_UPDATE_STEP / GetClnaddrMedia

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, step/shell to run: get_media.shl

CLNADDR_UPDATE_STEP / CopyNewdata

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: copy_newdata

CLNADDR_UPDATE_STEP / StopDaemons

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: stop_daemons.shl

CLNADDR_UPDATE_STEP / DoUpdate

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: do_update.shl

CLNADDR_UPDATE_STEP / Copy64bit

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: copy64bit.shl

CLNADDR_UPDATE_STEP / StartDaemons

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: start_daemons

CLNADDR_UPDATE_STEP / DoCleanzip

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: do_cleanzip

CLNADDR_UPDATE_STEP / DoBannerUpdate

Clnaddr Update Step

none

clnaddr_prod, /clnaddr/clnaddr_prod/update/remote_update_step.shl

UC4: localhost #2, db #1, dummy: banner_update

Note: Appears that the same remote_update_step.shl script is called with various step name/parameters which in effect execute the various scripts on the server. Interesting technique. (Just an observation. ;-)

Note2: Logs are stored in /clnaddr/clnaddr_test or prod/update/logs

---

DataAdminHowTo

DB_Administration

BTSHowto