Instructions for updating KCR data in cpdms_linkages

Follow these instructions to update data in the mysql database alpha-new/cpdms_linkages/KCR. This may be done at any time to update the table with the latest data from the central registry.
There are (2) options available. The express option - one script does it all - and manual procedure. While we encourage folks to familiarize themselves with both, the express option is recommended.

Express Script

1. Log in to int-cpdmsserver-central via putty
2. If you have not already, run this command to create the script:
• svn export https://source.kcr.uky.edu/svn/cpdms/cpdms/build/scripts/kcr_link_lookup
3. sudo ./kcr_link_lookup (use sysadm credentials when asked)
4. Verify that the data in the MySQL table cpdms_linkages.KCR on alpha is correct
5. Test the application that uses the data here: https://alpha-new.kcr.uky.edu/linklook/linklook.php

Archived

Manual Process

If you chose to not run the express script, here are the manual instructions:

Generate Output File of updated Central Data.

1. We suggest that you use database 21 for this. If you use 21, you can skip steps 2-5.
2. Obtain a working copy of central on ramdisk on mordor;
3. Make sure your environment variables point to the ramdisk copy of central; (source /kcr/submissions/setramdisk) and (set | grep DB) Perform the remaining functions on mordor:
4. kcr: Update calc vars (Main Menu -> Maintenance -> Update Calc Vars -> Type Y (will take a few mins)
5. kcr: Merge->Patient Update; Enter capital 'D' for death certs ONLY!
1. Note: Make sure you observe linkage records and SEERPatId counts being updated
6. cd /kcr/linkages/kcr_files
7. execute command '/kcr/applications/progs/patout/deathlink/patout NewFileName' (i.e. NewFileName = kcr20130318.csv)
1. Note: this c program generates a tab delimited file that you named as NewFileName

Download File and Update MySQL database with fresh Data

• copy newly created file to j:\linkages\KCR (scp filename.csv "user"@central:/home/kcrshare/linkages/KCR)
• Access mysql ALPHA\cpdms_linkages and perform remaining steps in sqlyog
• SQLYog: Generate backup copy of KCR table (right click - more table options - Duplicate table/structure data) then rename copy (i.e. KCR_copy_YYYYMMDD) this can be compared/restored if things go wrong
• SQLYog: Truncate KCR (right click - more table options - Truncate
• SQLYog: Load from CSV (right click - Import - Import CSV using Load Local)

SQLYog: Select and configure import of newly created tab delimited file and make sure correct number of records are created with NO errors or warnings. (to configure click the Change button in the Import pane and configure to match graphic below)

• SQLYog: Visually review file to confirm correct procedures
• Done!