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: 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 and bad address'es. 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
The CLNADDR_UPDATE_PROCESS uc4 jobs asks for some parameters to be filled in using info from the EDQ footprint ticket, message below:
Password to unzip product: MaybeFrienxxx
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 labelled 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:
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.
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.
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.
As the clnaddr_test user, issue the command ./copy_postal_data.sh to initiate the software and data installation.
You will be reminded to become familiar with the update procedure, and asked if you want to proceed. Respond with y when prompted.
You will be prompted for the location of the update source directory; respond with /clnaddr/clnaddr_test/media/clean_address_linux.
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.
- If you receive any permissions errors, fix them and start this step again.
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.
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.
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.
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.
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.
As the "clnaddr_test" user, enter the command cd $CLNADDR_HOME/bin64 to locate yourself in the CLEAN_Address executables directory.
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.
As the "banner" user, run sqlplus baninst1 @$BANNER_HOME/clnaddr/Banner_Interface/scripts/sync_postal_codes.sql for each database.
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.
Submit the Banner job SZPBVAD for each database served by the instance, or
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.
- 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:
- Stop the CLEAN_Address daemons.
Navigate to the bin64 directory of the installation media: cd /clnaddr/clnaddr_test/media/clean_address_linux/bin64.
Copy the contents of the directory to the bin64 directory of the CLEAN_Address home: cp -p * $CLNADDR_HOME/bin64
Also copy the same contents to the bin directory: cp -p * $CLNADDR_HOME/bin
- 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:
- Go to UC4 (Automic)
- Click the request button, it is the green triangle on the top left of the screen.
- Search for CLNADDR_UPDATE_PROCESS
- 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
- Submit the Job.
- In PROD you should not fill out any information and run the CLNDDR_UPDATE_PROCESS