Information Systems:Health Canada Drug Product Database
Revision as of 15:26, 12 May 2022 by Johnt (talk | contribs) (→Updating Health Canada files on Bart)
Overview
Health Canada makes information on drugs and natural health products available through database extracts that can be downloaded from their website. There are two sets; the drug files are refreshed at the beginning of every month; the NHP files less often. We build a database (e.g. set of tables) from these files so that the information can be displayed in Web Orders (and in Display Items, Work with Items in Infonet).
Consult the official pages for the drugs and NHP extracts for more information.
Instructions for updating our database
Downloading the extract (drugs)
- Go to Health Canada’s web site, www.hc-sc.gc.ca
- Click on ‘English’
- Under ‘Main Menu’, click on ‘Drugs & Health Products’
- Click on ‘Drug Products’
- Click on ‘Drug Product Database (DPD)’
- On the right hand side, click on ‘Access the Drug Product Database’
- On the upper part of the page, click on ‘DPD Data Extracts’
- Check the date under ‘Last Updated’ (right hand column). Don’t continue with this process unless this has changed. There shouldn’t be an issue as it is usually updated early on the first work day of the month.
- Before saving the new files - move old data files (NOTE: do not touch the files starting with NHP) - (superserver/staging/Health Canada) to archive folder - yes to replace
- Download ‘ALL FILES’ (bottom of the list - do ALL 4) to your PC; both active (allfiles.zip) and inactive (allfiles_ia.zip) and (allfiles_ap.zip) - Open then Open
- As of April 1, 2022 they now also have 4 other files that need to be extracted and saved version 1 of the Therapeudic class
- Unzip and copy to SuperServer / staging / Health Canada
Downloading the extract (Natural Health Products)
NOTE: These records were last updated by HC in Sept. 2017. They are working on a new data extract - TBD (as of Aug 2018 - still no progress)(as of Sept 2019 still no progress on getting new extracts for NHP).
- Go to Health Canada’s web site, www.hc-sc.gc.ca
- Click on ‘English’
- Under ‘Main Menu’, click on ‘Drugs & Health Products’
- Click on ‘Natural Health Products’
- On the right hand side, click on ‘Licensed Natural Health Products Database’
- Scroll down to the bottom of the page, and click on ‘Access the Licensed Natural Health Products Database Data Extract’
- Health Canada updates these files less often the drug product ones. Check the last updated date (at the top of the list of files) to see if it is necessary to continue. If the dates are the same, then don't do anything else. Check the next time.
- Go to SuperServer / staging / Health Canada and move the previous files to the archive folder.
- Download ‘All NHP Files’ to your PC.
- Unzip and copy to SuperServer / staging / Health Canada
Updating Health Canada files on Bart
Note: Need to use John T - user account to access the PL system and Web Orders Dev. We have had issues with the Health Canada data files. They are apparently extracted manually, which means there can be errors. One month there were no record delimiters; which meant we couldn’t use them. Another time instead of all related files being zipped together, each was zipped separately, then zipped together. Another issue is that users EODJOB and SHEILAV cannot access SUPERSERVER from the i, so the scheduled job has been changed to run under JOHNT (2014Dec04).
To validate the data files, first manually run the rebuild in the test environment (PL):
- From a command line in ASW (easiest to do in Mocha), key in 88, press Enter, key in PL, and press Enter twice.
- Key in SBMJOB CMD(CALL PGM(HCDBBLD01C)) JOBQ(QS36EVOKE) and press enter (Note: just press enter ONCE!). (This is run from job queue QS36EVOKE so that production jobs are not delayed.) This program is being submitted to run in batch – you won’t see it on your screen.
- To see this job running, key in WRKACTJOB SBS(QBATCH) and press enter (Note: just press enter ONCE!).
Work with Active Jobs BART
10/01/14 12:16:27
CPU %: .0 Elapsed time: 00:00:00 Active jobs: 563
Type options, press Enter.
2=Change 3=Hold 4=End 5=Work with 6=Release 7=Display message
8=Work with spooled files 13=Disconnect ...
Current
Opt Subsystem/Job User Type CPU % Function Status
QBATCH QSYS SBS .0 DEQW
QDFTJOBD SHEILAV BCH .0 PGM-HCDBBLD01C RUN
Parameters or command
===>
F3=Exit F5=Refresh F7=Find F10=Restart statistics
F11=Display elapsed data F12=Cancel F23=More options F24=More keys
- Press F5 or F10 to refresh. Process will take approx. 6 minutes to complete.
- When completed the “RUN” will disappear
- Key in the command DSPFD UWDASWVOLD/HCDBDATAP and press enter. Then press page down until you see ‘Current number of records’ (about three times). There should be around 26,000 records in it (as of Nov 2014; it will increase with time). F3 to exit
Display Spooled File
File . . . . . : QPDSPFD Page/Line 2/11
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1
Number of key fields . . . . . . . . . . . : 1
Record format . . . . . . . . . . . . . . . : HCDBDATAR
Key field . . . . . . . . . . . . . . . . : HCPRDC
Sequence . . . . . . . . . . . . . . . : Ascending
Sign specified . . . . . . . . . . . . : UNSIGNED
Zone/digit specified . . . . . . . . . : *NONE
Alternative collating sequence . . . . : No
Sort Sequence . . . . . . . . . . . . . . . : SRTSEQ *HEX
Language identifier . . . . . . . . . . . . : LANGID ENU
Member Description
Member . . . . . . . . . . . . . . . . . . : MBR HCDBDATAP
Member level identifier . . . . . . . . . : 1140529130244
Member creation date . . . . . . . . . . : 05/29/14
Text 'description' . . . . . . . . . . . : TEXT Health Canada Data Base - consolidated
Expiration date for member . . . . . . . : EXPDATE *NONE
Access path maintenance . . . . . . . . . : MAINT *IMMED
Access path recovery . . . . . . . . . . : RECOVER *NO
Member size . . . . . . . . . . . . . . . : SIZE *NOMAX
Current number of records . . . . . . . . : 25717
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys
- When this program has completed, go into Web Orders test (https://orders.unipharm.com/unidev/uw_logind.pgm) to make sure the files have been correctly built.
- Should also check in ASW for the printer output file for job QPQUPRFIL - this should typically only be 2 pages (one page is header), and the second page typically only shows items in error. Stocked can then be checked as needed and item file maintenance updated.
If the files are correct in test, then they can be updated in production – not now, but tonight when stores are not using Web Orders.
- Go into ASW production by keying 88, enter, VA, enter, enter.
- Key in WRKJOBSCDE and press Enter.
- Page down to job HCDATABASE.
Work with Job Schedule Entries BART
10/01/14 12:34:49
Type options, press Enter.
2=Change 3=Hold 4=Remove 5=Display details 6=Release
8=Work with last submission 10=Submit immediately
Next
-----Schedule------ Recovery Submit
Opt Job Status Date Time Frequency Action Date
2 HCDATABASE SAV 09/05/14 06:00:00 *ONCE *NOSBM
__ IMRCHDJOB HLD USER DEF 04:30:00 *WEEKLY *SBMRLS 10/02/14
__ IMRSCKSTS1 SCD *ALL 05:30:00 *WEEKLY *SBMRLS 10/02/14
__ IMRSCKSTS2 SCD 10/01/14 12:35:00 *ONCE *SBMRLS 10/01/14
__ INTERREPLN HLD *TUE 05:00:00 *WEEKLY *NOSBM 10/07/14
__ MAXAVA SCD *SAT 20:00:00 *ONCE *NOSBM 10/04/14
__ MXIOSTATS2 SAV *TUE 12:30:00 *ONCE *SBMRLS
More...
Parameters or command
===>
F3=Exit F4=Prompt F5=Refresh F6=Add F9=Retrieve
F11=Display job queue data F12=Cancel F17=Top F18=Bottom
- Use option 2 to change it.
Change Job Schedule Entry (CHGJOBSCDE)
Type choices, press Enter.
Job name . . . . . . . . . . . . > HCDATABASE Name
Entry number . . . . . . . . . . > 572242 000001-999999, *ONLY
Command to run . . . . . . . . . SBMJOB CMD(CALL PGM(HCDBBLD01C)) JOBQ(QS36EV
OKE)____________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
Frequency . . . . . . . . . . . *ONCE *SAME, *ONCE, *WEEKLY...
Schedule date . . . . . . . . . '10/02/14' Date, *SAME, *CURRENT...
Schedule day . . . . . . . . . . *NONE *SAME, *NONE, *ALL, *MON...
+ for more values _____
Schedule time . . . . . . . . . '06:00:00' Time, *SAME, *CURRENT
Bottom
F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel
F13=How to use this display F24=More keys
- Set it to run on the first Saturday after doing this data refresh (this is highly recommended to ensure the job has enough time to run without causing issues) the format is MM/DD/YY), and press enter twice.
- Send an email to the I.T. Group to let them know that the HC Database will be running this Saturday.
Check it the next business day to make sure the files have been correctly built.
- Key in the command DSPFD UWDASWPRDD/HCDBDATAP and press enter. Then press page down until you see ‘Current number of records’ (about three times). There should be around 26,000 records in it (as of Nov 2014; it will increase with time). (Note that this command is slightly different than the previous one in that it looks at production instead of development.)
- Go into Web Orders production. (spot check several items, suggestion to look for DIN’s that are newer (higher numbers)).
- Should also check in ASW for the printer output file for job QPQUPRFIL - this should typically only be 2 pages (one page is header), and the second page typically only shows items in error. Stocked can then be checked as needed and item file maintenance updated.
Additional Notes
Changes to the drug extract (Sept. 2017)
Consult the official page for an explanation of the structure of the drug extract.
There are three main changes to the drug extract:
- A new set of files for dormant products (not marketed for the last 12 months). These are now recognized by the current program.
- New columns to each file in a set (mostly French versions of existing columns). These are ignored by the current program.
- UTF-8 encoding. Some files are encoded in UTF-8-BOM, which bombs the program.