01/04/2010
Version 2010-1
RxNorm Documentation
RxNorm, a standardized nomenclature for clinical drugs, is produced by the National Library of Medicine. In this context, a clinical drug is a pharmaceutical product given to (or taken by) a patient with a therapeutic or diagnostic intent. In RxNorm, the name of a clinical drug combines its ingredients, strengths, and form.
In RxNorm, the form is the physical form in which the drug is administered or is specified to be administered in a prescription or order. The RxNorm clinical drug name does not indicate the form in which the product was manufactured, or its form when it arrived at the dispensary. With few exceptions (for packs), the RxNorm clinical drug name does not indicate the size of a package of drug units.
RxNorm's standard names for clinical drugs are connected to the varying names of drugs present in many different controlled vocabularies within the Unified Medical Language System (UMLS) Metathesaurus, including those in commercially available drug information sources. These connections are intended to facilitate interoperability among the computerized systems that record or process data dealing with clinical drugs.
Like other vocabularies in the Metathesaurus, RxNorm is organized by concept or meaning. All concepts in RxNorm are assigned at least one semantic type from the Semantic Network (Chapter 5 of the UMLS Reference Manual). This provides consistent categorization of all concepts in RxNorm at the relatively general level represented in the Semantic Network.
RxNorm is both a source and a subset of the Metathesaurus. The scope of RxNorm is determined by the combined scope of its source vocabularies. Many relationships (primarily synonymous), concept attributes, and some concept names are added by the NLM during the creation of RxNorm forms. The concepts themselves are derived from the names provided by the source vocabularies.
3.0 Preservation of Content and Meaning from Source Vocabularies
RxNorm reflects and preserves the meanings, concept names, and relationships from its source vocabularies in the same way that the Metathesaurus preserves content and meaning. Please refer to the UMLS Reference Manual for further information on this topic.
RxNorm contains source vocabularies produced by different copyright holders. The majority of the content of RxNorm is available for use under the basic and open terms described in sections 1-11 and 13-16 of the Metathesaurus license. Please refer to the UMLS Reference Manual for further information on this topic.
4.0 Ambiguity in RxNorm Source Data
The disparate variety of source providers to the RxNorm dataset, along with their individual schedules for updating and modifying their datasets, has created the possibility of atom strings coming from source providers that look similar, but have different meanings. It is common for these ambiguous strings to be placed in separate concepts within RxNorm in order to maintain the separate level of meaning for each string.
In some cases, the ambiguity is a result of the inability to resolve the correct meaning for a given string from a particular source. For example, attributes of an atom string may conflict with the string of the atom. One attribute may refer to an "Oral Tablet", while another may refer to "Extended Release Tablet", while the string that names the atom might disagree with these attributes. This could result in the string in question being placed in a concept separate from similar looking string(s) whose meaning is more clearly apparent in the name.
Another case of ambiguity could involve different quantities of a particular drug. In the case of an inhaler, a source may provide a string for a particular brand of inhaler, without specifying the number of actuations in the container. Another source may provide this information for a similar drug string, and research at NLM will confirm the different sizes of containers for the particular brand, thus making the first string ambiguous in meaning.
As drug source providers have refined and updated their datasets over time, new information has been provided about existing drug strings, making the old information obsolete and ambiguous. RxNorm maintains this old data as RxNorm (SAB=RXNORM) atoms of term type 'OCD'.
NLM strives to review these ambiguous strings on a regular basis in both the RxNorm and UMLS data sets. In the UMLS, relationships are created between ambiguous concepts and a unique name is created for each string with multiple meanings.
RxNorm normal forms (e.g. SCD, SBD, GPCK, BPCK) are created when the meaning of the source string is clear at the appropriate level of abstraction. In this sense, SAB=RXNORM normal form atoms are never ambiguous.
The RxNorm model of a prescribable drug, which specifies the ingredient, strength, and dose form, adequately represents most prescriptions for medicines. However; some important medicines are not sufficiently or appropriately characterized by those three components alone. These have been identified as medicines that are dispensed in "Packs". Examples of medicines prescribed and dispensed as Packs include Z-Pak, Medrol Dose Pack, and various oral contraceptive packages. A Pack contains one or more SCD's or SBD's along with a quantification to represent the number of SCD's or SBD's of each type that are contained in the Pack.
Packs may be branded or generic packs. A Branded Pack would contain SBD's and/or SCD's and be provided with a Market Name, while a Generic Pack would contain one or more SCD's only. The new Term Types "BPCK" and "GPCK" have been created to properly categorize these strings in the RxNorm vocabulary. These term types in RxNorm have the following syntax:
Generic Pack (GPCK): "{'quantity' (SCD1/SBD1) / 'quantity' (SCD2/SBD2) / 'quantity' (SCDn/SBDn) } Pack"
Branded Pack (BPCK): "{'quantity' (SCD1/SBD1) / 'quantity' (SCD2/SBD2) / 'quantity' (SCDn/SBDn) } Pack [Market Name]"
Examples of BPCK's:
{12 (Ethinyl Estradiol 0.035 MG / Norethindrone 0.5 MG Oral Tablet) / 9 (Ethinyl Estradiol 0.035 MG / Norethindrone 1 MG Oral Tablet) / 7 (Inert Ingredients 1 MG Oral Tablet) } Pack [Leena 28 Day]
{10 (Chlorpheniramine 8 MG / Methscopolamine 2.5 MG / Phenylephrine 10 MG Oral Tablet) / 10 (Methscopolamine 2.5 MG / Phenylephrine 40 MG Oral Tablet) } Pack [AlleRx PE Dose Pack]
Examples of GPCK's:
{28 (Norethindrone 0.35 MG Oral Tablet) } Pack
{11 (varenicline 0.5 MG Oral Tablet) / 42 (varenicline 1 MG Oral Tablet) } Pack
Each BPCK atom has a corresponding GPCK atom. These atom pairs will be represented with relationships in the RXNREL.RRF file in the following way:
BPCK -- tradename of -- GPCK
GPCK -- has_tradename -- BPCK
In addition, relationships are created between the Drug Delivery Device term type atoms (BPCK, GPCK), to their component SCD(s) and/or SBD(s). These relationships are represented in the RXNREL.RRF file in the following way:
BPCK -- contains -- SCD
BPCK -- contains -- SBD
GPCK -- contains -- SCD
SCD -- contained_in -- BPCK
SCD -- contained_in -- GPCK
SBD -- contained_in -- BPCK
6.0 Normalizing NDC codes in RxNorm
RxNorm currently distributes NDC data from five different sources. Each of these sources provides NDC codes in a different format. Examples of these include a 14 digit "6-4-2" format, 10 digit "5-3-2", "4-4-2" or "5-4-1" formats, 12 digits with no dashes format, and the 11 digit, no dashes HIPAA format.
The NDCs asserted for the atoms of each of these sources are found in RXNSAT.RRF as values of the attribute ATN='NDC'. Since these NDCs are in separate formats, it is difficult to determine when the NDC assignments are inconsistent across sources. A single RxNorm clinical drug concept may properly have several NDCs (each corresponding to a different package size), but a single NDC would be expected to be assigned to only one RxNorm clinical drug concept (RXCUI).
The NLM is associating normalized NDC codes to RxNorm clinical drug concepts that have an atom with one of the term types 'SCD', 'SBD', 'BPCK' and 'GPCK' (SAB=RXNORM atoms). The normalized NDCs will follow the 11 digit, no dashes HIPAA format. These normalized NDCs will be associated with those RXCUIs for which the NLM can determine the "correct" NDCs. In general, "correctness" of an NDC assignment means there is consistency among the various sources in the association of an RXCUI with a particular NDC. Sometimes the RxNorm editors may refer to additional references (e.g. information provided by manufacturers) to determine “correctness” of an NDC. These normalized NLM-asserted NDC associations will be found in RXNSAT.RRF, in additon to all the source-asserted NDC associations that already appear there. The normalized NLM-asserted NDC associations are assigned SAB='RXNORM'.
6.1 Examples of NDC code normalizations
| Source-asserted NDC attribute | Normalized NLM-asserted NDC attribute |
|---|---|
| 000406-0522-05 | 00406052205 |
| 000406052201 | 00406052201 |
| 054868-5338-*3 | 54868533803 |
| 0591-0933-01 | 00591093301 |
| 60951-700-85 | 60951070085 |
6.2 Examples of RXNSAT.RRF entries for an RXCUI (ATN="NDC")
213684|||2368285|AUI|759|||NDC|MMSL|61646050116|N||
213684|||2481750|AUI|100385|||NDC|MTHFDA|061646-*501-16|N||
213684|||2481751|AUI|108916|||NDC|MTHFDA|058716-*327-16|N||
213684|||1171481|AUI|213684|||NDC|RXNORM|61646050116|N||
213684|||1171481|AUI|213684|||NDC|RXNORM|58716032716|N||
7.0 Propagating NDC codes in RxNorm
As noted above, a single NDC code should properly be associated with only a single RxCUI, though a single RxCUI can have multiple NDCs. Beginning with the February 2009 release, RxNorm will stop propagating NDC codes from the branded drugs to the corresponding unbranded drugs. In this way, NDC codes will be associated with RxNorm atoms that are within the same concept only. This has been implemented for the BPCK, GPCK, and SBD term types. Essentially all cascading of NDC codes has been discontinued, and NLM assigned NDC associations will only relate a single RxCUI to a given NDC.
NLM associates U.S. Food and Drug Administration (FDA) generated unique ingredient identifiers (UNIIs) to RxNorm (SAB=RXNORM) atoms of term type IN. The association is made by an exact string match to the RxNorm ingredient string (case insensitive) from the official FDA substance list. These UNII codes are found in RXNSAT.RRF as values of the attribute ATN='UNII_CODE'. The UNII is a non-proprietary, free, unique, unambiguous, non semantic, alphanumeric identifier based on a substance’s molecular structure and/or descriptive information. For more information on the FDA UNII codes, please refer to this FDA web page.
9.0 Duplicating Source Asserted Atoms
Atoms coming from our sources can contain multiple NDC codes for the same atom. In many cases the NDC codes associated with the atoms are describing a similar product. However, there are cases where NDC codes received from sources for a single atom carry multiple meanings. For example, a source atom may contain "CAP/TAB" as the dose form portion of the atom and have the NDC codes for both the capsule form of the drug and the tablet form of the drug associated to this one atom.
RxNorm is creating duplicates of such source asserted atoms in order to properly associate NDC codes with RxNorm forms. When an atom is duplicated, the original source atom is considered to be a 'Base' atom and it is not assigned an RxNorm form. The duplicate atom's string is created by appending '_#N' to the Base atom string where 'N' is a number from 1 to N number of duplicates for this base atom.
The duplicate atoms will carry the same source asserted relationships and attributes as the Base atom except for the NDC code attributes. The NDC code attributes of the base atom will be assigned to the duplicate atoms that represent the different meanings of the base atom. RxNorm forms will be created to reflect the true meaning of the NDC code(s) assigned to each duplicate. An NDC code from a base atom can only be assigned to one duplicate atom at a time, but a duplicate atom can carry more than one similar meaning NDC codes from the base atom. The duplicate atom's term type will be created by prepending 'MTH_RXN_' to the base atom's term type.
A Base atom can be identified by the existence of an attribute in RXNSAT.RRF where the ATN='AMBIGUITY_FLAG' and the ATV='Base'. Similarly, the duplicate atoms are identified with ATN='AMBIGUITY_FLAG' and ATV='Duplicate'. This attribute will carry the SAB of the base atom. In addition, a relationship will exist between the base atom and it's duplicate atoms in RXNREL.RRF with "includes" and "included_in" relationship attributes (RELA). This relationship will carry the SAB of the base atom.
Examples of RXNCONSO.RRF entries for a base atom and it's duplicates
727359|ENG|P||PF||N|2656259||||GS|BD|28336|Hyalgan 20mg/2ml Solution for Injection||N||
727362|ENG|P||PF||N|2719627||||GS|MTH_RXN_BD|28336|Hyalgan 20mg/2ml Solution for Injection_#2||N||
727308|ENG|P||PF||N|2719626||||GS|MTH_RXN_BD|28336|Hyalgan 20mg/2ml Solution for Injection_#1||N||
Examples of RXNSAT.RRF entries for these atoms (ATN="AMBIGUITY_FLAG")
727362|||2719627|AUI|28336|||AMBIGUITY_FLAG|GS|Duplicate|N||
727308|||2719626|AUI|28336|||AMBIGUITY_FLAG|GS|Duplicate|N||
727359|||2656259|AUI|28336|||AMBIGUITY_FLAG|GS|Base|N||
Examples of RXNREL.RRF entries for these atoms
|2656259|AUI|RO||2719626|AUI|included_in|||GS||||N||
|2719626|AUI|RO||2656259|AUI|includes|||GS||||N||
|2656259|AUI|RO||2719627|AUI|included_in|||GS||||N||
|2719627|AUI|RO||2656259|AUI|includes|||GS||||N||
10.0 Drug Dose Forms and the Modified Format for SCD and SBD Normal Forms
To accurately represent factors of time, measures or strength for certain dose forms within RxNorm, an additional format for SCD and SBD forms has been utilized along with a more succinct set of dose forms for certain categories of forms. The factor of time, measure or strength can now be represented by a Quantity Factor appended to the beginning of the SCD or SBD name. This quantity factor consists of a number followed by a unit measure. The number might represent a strength, a unit of time, or a quantity of dosages depending upon the dosage form in use at the time. This quantity factor is stored as an attribute to the RxNorm SAB atom as the value of the ATN='RXN_QUANTITY' in RXNSAT.RRF. Some dose forms have been retired and replaced by more generic dose form names, allowing for greater flexibility in representing drug names. Other dose forms have been added.
The following dose forms have been replaced by the more general dose form shown to the right of the name. The Quantity number is variable according to the clinical drug being represented.
| Old Dose Form | New Dose Form | Quantity number | Quantity Unit | Qauntity Factor Required |
|---|---|---|---|---|
| 12 hour Extended Release Capsule | Extended Release Capsule | 12 | HR | No |
| 12 hour Extended Release Tablet | Extended Release Tablet | 12 | HR | No |
| 24 hour Extended Release Capsule | Extended Release Capsule | 24 | HR | No |
| 24 hour Extended Release Tablet | Extended Release Tablet | 24 | HR | No |
| 16 Hour Transdermal Patch | Transdermal Patch | 16 | HR | No |
| 24 Hour Transdermal Patch | Transdermal Patch | 24 | HR | No |
| 72 Hour Transdermal Patch | Transdermal Patch | 72 | HR | No |
| Biweekly Transdermal Patch | Transdermal Patch | 84 | HR | No |
| Weekly Transdermal Patch | Transdermal Patch | 168 | HR | No |
Example RxNorm forms before the data transformation:
Bupropion 300 MG 24 hour Extended Release Tablet [Wellbutrin]
Clonidine 0.00833 MG/HR Weekly Transdermal Patch
And after the data transformation:
24 HR Bupropion 300 MG Extended Release Tablet [Wellbutrin]
168 HR Clonidine 0.00833 MG/HR Transdermal Patch
Example forms are shown below:
3 ML Insulin, Aspart, Human 100 UNT/ML Prefilled Syringe
2 ML hyaluronate 10 MG/ML Prefilled Syringe [Hyalgan]
Examples of RXNSAT.RRF entries for an RXCUI (ATN="RXN_QUANTITY")
104420|||2701594|AUI|104420|||RXN_QUANTITY|RXNORM|24 HR|N||
104873|||2701596|AUI|104873|||RXN_QUANTITY|RXNORM|72 HR|N||
10.1 Syrup and Elixir Dose Forms
In an effort to reduce the discrepancies related to NDC codes on RxNorm forms, beginning with the March 2008 release, RxNorm has chosen to use the Oral Solution dose form in lieu of the Syrup and Elixir dose forms. Existing RxNorm normal forms (SCD, SBD, SCDF, SBDF) using Syrup and Elixir were transformed into RxNorm normal forms using the Oral Solution dose form. In some cases, this process resulted in new RxNorm forms being created, in others, an RxNorm form already existed and was reused. The RxNorm normal forms that contained Syrup and Elixir dose forms have been archived.
11.0 Release Types and Explanation
RxNorm is released on the first Monday of each month (or the first Tuesday if the first Monday falls on a federal holiday). The monthly release is a full release of all of the RxNorm data, including all 10 sources and RxNorm forms (SAB=RXNORM atoms).
Weekly updates to RxNorm are released each Wednesday beginning with the Wednesday following the monthly RxNorm release. The weekly updates contain new data from the FDA Structured Product Labels (MTHSPL) and the RxNorm forms (SAB=RXNORM atoms) created for the new MTHSPL data. The weekly updates contain new data only and are designed to be used in conjunction with the last monthly release of RxNorm in order to create the most up-to-date picture of the RxNorm dataset.
Each weekly release published within one month's time can be systematically applied to the monthly dataset (or monthly plus weekly). In this way, only one database needs to be maintained, with each weekly update in a month appended to the existing database which is created new with each monthly release.
Both the monthly release and weekly updates contain database control files and SQL commands for use in the automation of the loading process of these files into a RDBMS. Scripts and controls are provided for the loading the RxNorm files into an Oracle database or a MySQL database.
The most complete, accurate and up-to-date drug information can be obtained from the use of these RxNorm release files.
The character set of RxNorm release files is Unicode UTF-8
11.2 RxNorm Release File Format
RxNorm uses the Rich Release Format (RRF), introduced in 2004. All Rich Release Format file names have an extension (.RRF). The RxNorm Rich Release Format is described in this documentation (usually abbreviated as RXNRRF). Refer to the UMLS Reference Manual for a complete description of the RRF.
RxNorm follows all previously established standards and practices of the latest version of the UMLS Metathesaurus. Where RxNorm may differ slightly from these practices is documented here.
11.4 Uses of Concept and Atom Identifiers
In RxNorm, each RXCUI (concept) is linked to at least one RXAUI (atom), but can be linked to many of these. Each RXAUI (atom) is linked to a single RXCUI (concept).
11.5 Concepts and Concept Identifiers
A concept is a meaning. A meaning can have different names. The goal of RxNorm vocabulary construction is to understand the intended meaning of each drug name in each source vocabulary and to link all the names from all of the source vocabularies that mean the same thing (the synonyms) to a single RxNorm Normal form. Each concept or meaning in RxNorm has a unique and permanent concept unique identifier (RXCUI).
An RXCUI will be removed from the primary RxNorm file (RXNCONSO.RRF) when it is discovered that two RXCUIs name the same concept – in other words, when undiscovered synonymy comes to light. In these cases, one of the two RXCUIs will be retained, all relevant information in RxNorm will be linked to it, and the other RXCUI will be retired.
Retired RXCUIs are never re-used or deleted. Each release of RxNorm includes files that detail any such changes from the previous edition. One RxNorm file (RXNCUI.RRF) tracks such changes from March, 2009 to the present, allowing you to check the fate of any CUI that is no longer present in RxNorm.
12.0 Data Files - RxNorm Rich Release Format (RXNRRF)
All file names begin with the letters RXN (RxNorm) and are followed by letters that denote the file contents (e.g., RXNREL=relationships, RXNSAB=source abbreviations), and then a file extension .RRF.
The data in each RxNorm entry may be represented in different "relations" or files. These files correspond to the four logical groups of data elements described in Sections 2.3 - 2.6 of the UMLS Reference Manual. The exceptions to this rule are the RXNATOMARCHIVE.RRF, RXNCUICHANGES.RRF, and RXNCUI.RRF files.
The RXNATOMARCHIVE.RRF file is a file created by RxNorm for use in tracking the archival of names (atoms) no longer provided by RxNorm drug source providers, or atoms no longer used by the RxNorm system. Only atoms from the source RxNorm (SAB=RXNORM) will be in the archive.
The RXNCUICHANGES.RRF file is a new file provided by RxNorm for use in tracking the changes to the RxNorm Concept_id (RXCUI) for source asserted atoms, RxNorm OCD atoms, and for RXCUI changes to RxNorm forms (SAB=RXNORM atoms) that have been archived in the RXNATOMARCHIVE.RRF file. For each release, this tracking is provided for data changed since the last release of RxNorm. The data file is not cumulative.
The RXNCUI.RRF file is being provided as of April, 2009. This file is created by RxNorm for use in tracking the retired RxNorm Concept Unique Identifiers (RXCUIs) in RxNorm. Tracking of this information began in March, 2009 and data will be cumulative from this date.
The RXNCONSOOCD.RRF, RXNSATOCD.RRF, and RXNSTYOCD.RRF files are being provided as of September of 2009. These files contain information pertaining to the SAB=RXNORM OCD term type atoms that were previously kept in the RXNCONSO.RRF, RXNSAT.RRF, and RXNSTY.RRF files respectively. These RRF files are stored in the 'rrf/ocd' subdirectory of the release package. If a concept in RxNorm contains OCD atoms and active atoms, then it's semantic type value will be contained in both the RXNSTYOCD.RRF and RXNSTY.RRF files.
RxNorm provides data in the following types of files:
Concepts, Concept Names, and their sources (2.2) = RXNCONSO.RRF, RXNCONSOOCD.RRF
Attributes (2.3) = RXNSAT.RRF, RXNSATOCD.RRF, RXNSTY.RRF, RXNSTYOCD.RRF
Relationships (2.4) = RXNREL.RRF
Data about RxNorm (2.5) = RXNDOC.RRF, RXNSAB.RRF, RXNCUI.RRF
Archive Data = RXNATOMARCHIVE.RRF
Concept Changes Tracking Data = RXNCUICHANGES.RRF
Each file or named table of data values has by definition a fixed number of columns; the number of rows depends on the content of a particular version of the RxNorm. The RxNorm release files follow the format of the Metathesaurus releases files with a few exceptions which are noted below.
12.2 Descriptions of Each File
The descriptions of the files appear in the following order:
a) Key data about RxNorm: Files; Columns or data elements; Documentation that explains the meaning of abbreviations that appear as values in RxNorm data elements and attributes,
b) Concept names and their vocabulary sources
c) Attributes
d) Relationships
e) Other data about RxNorm
Each file description lists the columns or data elements that appear in the file and includes sample rows from the file.
12.3 Documentation for Abbreviated Values (File = RXNDOC.RRF)
This file follows the format of the Metathesaurus MRDOC.RRF file. At this time, this file contains all of the data contained in the UMLS MRDOC.RRF file. In the future, the contents of this file may be tailored to the RxNorm subset of data only. There is exactly one row in this table for each allowed value of selected data elements or attributes that have a finite number of abbreviations as allowed values. Examples of such data elements include TTY, ATN, TS, STT, REL, RELA.
| Column | Description |
|---|---|
| KEY | Data element or attribute |
| VALUE | Abbreviation that is one of its values |
| TYPE | Type of information in EXPL column |
| EXPL | Explanation of VALUE |
Sample Records
ATN|DDF|expanded_form|Drug Doseform|
ATN|DHJC|expanded_form|HCPCS J-code|
12.4 Concept Names and Sources (File = RXNCONSO.RRF)
This file follows the general format of the MRCONSO.RRF file of the Metathesaurus. Some of the Metathesaurus fields are not provided by RxNorm and are marked as "(no value provided)". There is exactly one row in this file for each atom (each occurrence of each unique string or concept name within each source vocabulary) in RxNorm, i.e., there is exactly one row for each unique RXAUI in RxNorm. Every string or concept name in RxNorm appears in this file, connected to its language, source vocabularies, and its concept identifier (RXCUI).
| Column | Description |
|---|---|
| RXCUI | RxNorm Unique identifier for concept (concept ID) |
| LAT | Language of Term |
| TS | Term status (no value provided) |
| LUI | Unique identifier for term (no value provided) |
| STT | String type (no value provided) |
| SUI | Unique identifier for string (no value provided) |
| ISPREF | Atom status - preferred (Y) or not (N) for this string within this concept (no value provided) |
| RXAUI | Unique identifier for atom (RxNorm Atom ID) |
| SAUI | Source asserted atom identifier [optional] |
| SCUI | Source asserted concept identifier [optional] |
| SDUI | Source asserted descriptor identifier [optional] (no value provided) |
| SAB | Source abbreviation |
| TTY | Term type in source |
| CODE | "Most useful" source asserted identifier (if the source vocabulary has more than one identifier), or a Metathesaurus-generated source entry identifier (if the source vocabulary has none.) |
| STR | String |
| SRL | Source Restriction Level (no value provided) |
| SUPPRESS | Suppressible flag. Values = N, or O. N - not suppressible. O - Specific Individual names (atoms) set as Obsolete because the name is no longer provided by the original source. NLM strongly recommends that users not alter editor-assigned suppressibility. |
| CVF | Content view flag |
Sample Records
91348|ENG|P||PF||Y|707001|707001|91348||RXNORM|SCD|RX10091348|Hydrogen Peroxide 300 MG/ML Topical Solution||||
83|ENG|P||VC||Y|1960||||SNOMEDCT|PT|75368007|4-Hydroxyphenylpyruvate dioxygenase||||
83|ENG|P||VC||Y|1960||||SNOMEDCT|PT|75368007|4-Hydroxyphenylpyruvate dioxygenase||||
2809|ENG|S||PF||Y|84238|84238|2809||RXNORM|OCD|10002809|CONTACT LENS EACH||O||
12.5 Simple Concept and Atom Attributes (File = RXNSAT.RRF)
This file follows the general format of the MRSAT.RRF file of the
Metathesaurus. Some of the Metathesaurus fields are not provided by RxNorm and are marked as "(no value provided)". There is exactly one row in this table for each concept, atom, or relationship attribute that does not have a sub-element structure. Not all RxNorm concepts or RxNorm relationships have entries in this file. This file includes all source vocabulary attributes that do not fit into other categories.
| Column | Description |
|---|---|
| RXCUI | Unique identifier for concept (concept id) |
| LUI | Unique identifier for term (no value provided) |
| SUI | Unique identifier for string (no value provided) |
| RXAUI | Unique identifier for atom (RxNorm Atom ID) |
| STYPE | The name of the column in RXNCONSO.RRF or RXNREL.RRF that contains the identifier to which the attribute is attached, e.g., CUI, AUI. |
| CODE | "Most useful" source asserted identifier (if the source vocabulary contains more than one). |
| ATUI | Unique identifier for attribute |
| SATUI | Source asserted attribute identifier (optional - present if it exists) |
| ATN | Attribute name. Possible values appear in RXNDOC.RRF and are described on the UMLS Attribute Names page |
| SAB | Abbreviation of the source of the attribute. Possible values appear in RXNSAB.RRF and are listed on the UMLS Source Vocabularies page |
| ATV | Attribute value described under specific attribute name on the UMLS Attribute Names page. A few attribute values exceed 1,000 characters. Many of the abbreviations used in attribute values are explained in RXNDOC.RRF and included UMLS Abbreviations Used in Data Elements page |
| SUPPRESS | Suppressible flag. Values = E, Y, or N. Reflects the suppressible status of the attribute. See also SUPPRESS in MRCONSO.RRF and MRDEF.RRF and MRREL.RRF of the UMLS Reference Manual. |
| CVF | Content view flag |
Sample Records
178|||4996|AUI|MTHU000146|AT32525185||TYPE|MMSL|bulk compounding chemicals|N||
38|||829|AUI|38|||RXN_BN_CARDINALITY|RXNORM|single|N||
12.6 Semantic Types (File = RXNSTY.RRF)
This file follows the general format of the MRSTY.RRF file of the
Metathesaurus. There is exactly one row in this file for each Semantic Type assigned to each concept. All RxNorm concepts have at least one entry in this file. Many have more than one entry. The TUI, STN, and STY are all direct links to the UMLS Semantic Network.
| Column | Description |
|---|---|
| RXCUI | Unique identifier of concept (concept id) |
| TUI | Unique identifier of Semantic Type |
| STN | Semantic Type tree number |
| STY | Semantic Type. The valid values are defined in the Semantic Network. |
| ATUI | Unique identifier for attribute |
| CVF | Content view flag |
Sample Records
44|T109|A1.4.1.2.1|Organic Chemical|||
7624|T122|A1.4.1.1.2|Biomedical or Dental Material|||
12.7 Related Concepts (File = RXNREL.RRF)
This file follows the general format of the MRREL.RRF file of the
Metathesaurus. Some of the Metathesaurus fields are not provided by RxNorm and are marked as "(no value provided)". There is one row in this table for each relationship between concepts or atoms known to RxNorm. In addition, explicit SY RELs are provided which give the UMLS Metathesaurus CUI and AUI as the RXCUI2 and RXAUI2 fields.
Note that for asymmetrical relationships there is one row for each direction of the relationship. Note also the direction of REL - the relationship which the SECOND concept or atom (with Concept Unique Identifier RXCUI2 and Atom Unique Identifier RXAUI2) HAS TO the FIRST concept or atom (with Concept Unique Identifier RXCUI1 and Atom Unique Identifier RXAUI1).
| Column | Description |
|---|---|
| RXCUI1 | Unique identifier of first concept |
| RXAUI1 | Unique identifier for first atom |
| STYPE1 | The name of the column in RXNCONSO.RRF that contains the identifier used for the first concept or first atom in source of the relationship (e.g., 'AUI' or 'CUI'). |
| REL | Relationship of second concept or atom to first concept or atom |
| RXCUI2 | Unique identifier of second concept |
| RXAUI2 | Unique identifier for second atom |
| STYPE2 | The name of the column in RXNCONSO.RRF that contains the identifier used for the second concept or second atom in the source of the relationship (e.g., 'AUI' or 'CUI'). |
| RELA | Additional (more specific) relationship label (optional) |
| RUI | Unique identifier for relationship |
| SRUI | Source asserted relationship identifier, if present (no value provided) |
| SAB | Abbreviation of the source of relationship |
| SL | Source of relationship labels (no value provided) |
| DIR | Source asserted directionality flag. (no value provided) Y indicates that this is the direction of the relationship in its source; N indicates that it is not; a blank indicates that it is not important or has not yet been determined. |
| RG | Machine generated and unverified indicator (optional) |
| SUPPRESS | Suppressible flag. Values = Y, E, or N. Reflects the suppressible status of the relationship; not yet in use. See also SUPPRESS in MRCONSO.RRF and MRDEF.RRF and MRREL.RRF in the UMLS Reference Manual. |
| CVF | Content view flag |
Sample Records
353653|1553161|AUI|RO|102931|779853|AUI|consists_of|||RXNORM||||||
102931|779853|AUI|SY|C0351134|A2616052|AUI||||RXNORM||||||
12.8 Source Information (File=RXNSAB.RRF)
This file follows the general format of the MRSAB.RRF file of the
Metathesaurus. Please refer to the UMLS Reference Manual for more specific information regarding this file.
There is one row in this file for every source in RxNorm that is represented in this RxNorm release.
The structure of RXNSAB.RRF is as follows:
| Column | Full Name | Description |
|---|---|---|
| VCUI | CUI | CUI of the versioned SRC concept for a source |
| RCUI | Root CUI | CUI of the root SRC concept for a source |
| VSAB | Versioned Source Abbreviation | The versioned source abbreviation for a source, e.g., NDDF_2004_11_03 |
| RSAB | Root Source Abbreviation | The root source abbreviation, for a source e.g. NDDF |
| SON | Official Name | The official name for a source |
| SF | Source Family | The Source Family for a source |
| SVER | Version | The source version, e.g., 2001 |
| VSTART | Meta Start Date | The date a source became active, e.g., 2001_04_03 |
| VEND | Meta End Date | The date a source ceased to be active, e.g., 2001_05_10 |
| IMETA | Meta Insert Version | The version of the Metathesaurus a source first appeared, e.g., 2001AB |
| RMETA | Meta Remove Version | The version of the Metathesaurus a source was removed, e.g., 2001AC |
| SLC | Source License Contact | The source license contact information. A semi-colon separated list containing the following fields: Name; Title; Organization; Address 1; Address 2; City; State or Province; Country; Zip or Postal Code; Telephone; Contact Fax; Email; URL |
| SCC | Source Content Contact | The source content contact information. A semi-colon separated list containing the following fields: Name; Title; Organization; Address 1; Address 2; City; State or Province; Country; Zip or Postal Code; Telephone; Contact Fax; Email; URL |
| SRL | Source Restriction Level | 0,1,2,3,4 - explained in the License Agreement. |
| TFR | Term Frequency | The number of terms for this source in RXNCONSO.RRF, e.g., 12343 (not implemented yet) |
| CFR | CUI Frequency | The number of CUIs associated with this source, e.g., 10234 (not implemented yet) |
| CXTY | Context Type | The type of relationship label (Section 2.4.2 of UMLS Reference Manual) |
| TTYL | Term Type List | Term type list from source, e.g., MH,EN,PM,TQ |
| ATNL | Attribute Name List | The attribute name list, e.g., MUI,RN,TH,... |
| LAT | Language | The language of the terms in the source |
| CENC | Character Encoding | Character set as specified by the IANA official names for character assignments http://www.iana.org/assignments/character-sets |
| CURVER | Current Version | A Y or N flag indicating whether or not this row corresponds to the current version of the named source |
| SABIN | Source in Subset | A Y or N flag indicating whether or not this row is represented in the current MetamorphoSys subset. Initially always Y where CURVER is Y, but later is recomputed by MetamorphoSys. |
| SSN | Source short name | The short name of a source as used by the NLM Knowledge Source Server. |
| SCIT | Source citation | Citation information for a source. A semi-colon separated list containing the following fields: Author(s); Author(s) address; Author(s) organization; Editor(s); Title; Content Designator; Medium Designator; Edition; Place of Publication; Publisher; Date of Publication/copyright; Date of revision; Location; Extent; Series; Availability Statement (URL); Language; Notes |
Sample Record
1509846|C1140284|RXNORM_07AB_070702F|RXNORM|RxNorm Vocabulary|RXNORM|07AB_07070 2F|||2007AB||Stuart Nelson, M.D. ;Head, MeSH Section;National Library of Medicine;8600 Rockville Pike;;Bethesda;Maryland;United States;20894;;;nelson@nlm.nih.gov; |Stuart Nelson, M.D. ;Head, MeSH Section;National Library of Medicine;8600 Rockville Pike;;Bethesda;Maryland;United States;20894;;;nelson@nlm.nih.gov;|0||||BN,DF, ET,IN,OCD,SBDC,SBDF,SBD,SCDC,SCDF,SCD,SY|NDC,ORIG_CODE,ORIG_SOURCE,ORIG_TTY,ORIG _VSAB,RXAUI,RXCUI,RXN_OBSOLETED,RXN_ACTIVATED,RXN_BN_CARDINALITY,RXN_IN_EXPRESSED_FLAG ,RXN_QUANTITY|ENG|UTF-8|Y|Y|RxNorm work done by the National Library of Med icine|;;;;RxNorm;;;META2007AB Full Update 2007_07_02;Bethesda, MD;National Library of Medicine;;;;;;;;|
12.9 Retired RXCUI Data (File = RXNCUI.RRF)
There are one or more rows in this file for each RxNorm Concept Unique Identifier (RXCUI) that existed in a prior release but is not present in the current release. The file includes mappings to current RXCUIs as synonymous or to one or more related current RXCUI where possible. Some RXCUIs may be mapped to more than one other RXCUI as represented by multiple rows for a retired RXCUI in the file.
RXCUIs may be retired when (1) two concepts are found to be synonyms and so are merged, retiring one previously released RXCUI; (2) the concept no longer appears in any source vocabulary or (3) the concept was created as the result of an acknowledged error in a source vocabulary. In the case of (3), the RXCUI will be shown as synonymous to itself. In other words, the RXCUI was errant and there is no active RXCUI that now represents this error.
The structure of RXNCUI.RRF is as follows:
| Column | Description |
|---|---|
| RXCUI1 | Unique identifier of retired concept |
| VSAB_START | Version Source Abbreviation of the first RxNorm release RXCUI1 appeared in |
| VSAB_END | Version Source Abbreviation of the last RxNorm release RXCUI1 appeared in |
| Cardinality | The number of RXCUIs that RXCUI1 was moved or split to. |
| RXCUI2 | Unique identifier of synonymous concept for RXCUI1 |
Sample Records
106107|RXNORM_04AC_050210F|RXNORM_08AB_090302F|1|834308|
730597|RXNORM_07AC_071102F|RXNORM_08AB_090302F|1|731567|
202242|RXNORM_05AA_050617F|RXNORM_08AB_090302F|1|208400|
12.10 Archive Data (File = RXNATOMARCHIVE.RRF)
This file contains data about RxNorm SAB names (atoms) that are no longer a part of the RxNorm database because the drug data source provider is no longer providing this name to RxNorm. This file is not similar to any Metathesaurus RRF files. It is newly created by RxNorm.
The structure of RXNATOMARCHIVE.RRF is as follows:
| Column | Description |
|---|---|
| RXAUI | RxNorm Unique identifier for atom (RxNorm Atom ID) |
| AUI | Metathesaurus Unique identifier for atom |
| STR | String |
| ARCHIVE_TIMESTAMP | Date/Time Archived |
| CREATED_TIMESTAMP | Date/Time Created in this version of RxNorm |
| UPDATED_TIMESTAMP | Date/Time Updated in this version of RxNorm |
| CODE | "Most useful" source asserted identifier (if the source vocabulary has more than one identifier), or a Metathesaurus-generated source entry identifier (if the source vocabulary has none.) |
| IS_BRAND | Branded data flag (not used) |
| LAT | Language of Term |
| LAST_RELEASED | Date this name (atom) was last released from RxNorm |
| SAUI | Source asserted atom identifier [optional] |
| VSAB | Versioned Source Abbreviation |
| RXCUI | RxNorm Unique identifier for concept (concept ID) |
| SAB | Source abbreviation |
| TTY | Term type in source |
| MERGED_TO_RXCUI | RxNorm Unique identifier for concept (concept ID) which name (atom) was merged into |
Sample Records
1138734|A0540716|Cefotetan Disodium|3/15/2005 8:05:03 PM|3/10/2005 2:56:21 PM| 3/15/2005 8:05:02 PM|10203141||ENG||||203141|RXNORM|IN|203141
2057262|2057262|Testosterone / Testosterone Injectable Solution [Andro-Cyp 100]|3/17/2005 2:01:59 PM| 3/16/2005 12:23:16 PM|3/17/2005 2:01:58 PM|10363337||ENG||||363337|RXNORM|SBDF|363337
12.11 Concept Changes (File = RXNCUICHANGES.RRF)
This file provides information about changes to the concept_id (RXCUI) for atoms in RxNorm from release to release. For source asserted atoms and SAB=RxNorm OCD term type atoms, all data fields will be populated. For RXCUI changes to RxNorm forms that have been archived, only the SAB, OLD_RXCUI, and NEW_RXCUI will be provided. Full information about RxNorm forms that have been archived can be found in the RXNATOMARCHIVE.RRF file. Information is provided here as a convenience. The file provides data about changes since the last release of RxNorm, it is not cumulative.
This file is not similar to any Metathesaurus RRF file. It is newly created by RxNorm.
| Column | Description |
|---|---|
| RXAUI | RxNorm Unique identifier for concept (concept ID) |
| CODE | "Most useful" source asserted identifier (if the source vocabulary has more than one identifier), or a Metathesaurus-generated source entry identifier (if the source vocabulary has none.) |
| SAB | Source abbreviation |
| TTY | Term type in source |
| STR | String |
| OLD_RXCUI | Old RxNorm Unique identifier for concept (concept ID) |
| NEW_RXCUI | New RxNorm Unique identifier for concept (concept ID) |
Sample Records
1253224|688059|RXNORM|OCD|CEPHAPIRIN NA 20GM/VIL INJ|688055|688059|
426260|117164003|SNOMEDCT|PT|HLA-DQ2 antigen|27034|461862|
||RXNORM|||670619|702633|
12.12 Obsolete Concept Names and Sources (File = RXNCONSOOCD.RRF)
This file follows the general format of the RXNCONSO.RRF file and contains information about SAB=RXNORM OCD term type atoms only. There is exactly one row in this file for each OCD atom in RxNorm. Refer to RXNCONSO.RRF above for further explanation of this file contents.
| Column | Description |
|---|---|
| RXCUI | RxNorm Unique identifier for concept (concept ID) |
| LAT | Language of Term |
| TS | Term status (no value provided) |
| LUI | Unique identifier for term (no value provided) |
| STT | String type (no value provided) |
| SUI | Unique identifier for string (no value provided) |
| ISPREF | Atom status - preferred (Y) or not (N) for this string within this concept (no value provided) |
| RXAUI | Unique identifier for atom (RxNorm Atom ID) |
| SAUI | Source asserted atom identifier [optional] |
| SCUI | Source asserted concept identifier [optional] |
| SDUI | Source asserted descriptor identifier [optional] (no value provided) |
| SAB | Source abbreviation |
| TTY | Term type in source |
| CODE | "Most useful" source asserted identifier (if the source vocabulary has more than one identifier), or a Metathesaurus-generated source entry identifier (if the source vocabulary has none.) |
| STR | String |
| SRL | Source Restriction Level (no value provided) |
| SUPPRESS | Suppressible flag. Values = N, or O. N - not suppressible. O - Specific Individual names (atoms) set as Obsolete because the name is no longer provided by the original source. NLM strongly recommends that users not alter editor-assigned suppressibility. |
| CVF | Content view flag |
Sample Records
2809|ENG|S||PF||Y|84238|84238|2809||RXNORM|OCD|10002809|CONTACT LENS EACH||O||
2551|ENG|S||PF||Y|1607503|1607503|2551||RXNORM|OCD|2551|Ciprofloxacin opthalmic preparatio n||O||
12.13 OCD Atom Attributes (File = RXNSATOCD.RRF)
This file follows the general format of the RXNSAT.RRF file. There is exactly one row in this table for each OCD concept, atom, or relationship attribute that does not have a sub-element structure.
| Column | Description |
|---|---|
| RXCUI | Unique identifier for concept (concept id) |
| LUI | Unique identifier for term (no value provided) |
| SUI | Unique identifier for string (no value provided) |
| RXAUI | Unique identifier for atom (RxNorm Atom ID) |
| STYPE | The name of the column in RXNCONSOOCD.RRF that contains the identifier to which the attribute is attached, e.g., CUI, AUI. |
| CODE | "Most useful" source asserted identifier (if the source vocabulary contains more than one). |
| ATUI | Unique identifier for attribute |
| SATUI | Source asserted attribute identifier (optional - present if it exists) |
| ATN | Attribute name. Possible values appear in RXNDOC.RRF and are described in Appendix B.2 of the Metathesaurus documentation |
| SAB | Abbreviation of the source of the attribute. Possible values appear in RXNSAB.RRF and are listed in Appendix B.4 of the Metathesaurus documentation |
| ATV | Attribute value described under specific attribute name in Appendix B.2. of the Metathesaurus documentation. A few attribute values exceed 1,000 characters. Many of the abbreviations used in attribute values are explained in RXNDOC.RRF and included in Appendix B.3. of the Metathesaurus documentation |
| SUPPRESS | Suppressible flag. Values = E, Y, or N. Reflects the suppressible status of the attribute. See also SUPPRESS in MRCONSO.RRF and MRDEF.RRF and MRREL.RRF of the Metathesaurus documentation. |
| CVF | Content view flag |
Sample Records
1331|||1760861|AUI|1331|AT51669325||ORIG_VSAB|RXNORM|NDDF_2004_03_11|N||
327|||9866|AUI|10000327|AT45601455||ORIG_CODE|RXNORM|003263|N||
12.14 OCD Semantic Types (File = RXNSTYOCD.RRF)
This file follows the general format of the RXNSTY.RRF. There is exactly one row in this file for each Semantic Type assigned to each OCD concept. All RxNorm OCD concepts have at least one entry in this file. Some have more than one entry. The TUI, STN, and STY are all direct links to the UMLS Semantic Network.
| Column | Description |
|---|---|
| RXCUI | Unique identifier of concept (concept id) |
| TUI | Unique identifier of Semantic Type |
| STN | Semantic Type tree number |
| STY | Semantic Type. The valid values are defined in the Semantic Network. |
| ATUI | Unique identifier for attribute |
| CVF | Content view flag |
Sample Records
2040|T109|A1.4.1.2.1|Organic Chemical|||
2040|T131|A1.4.1.1.5|Hazardous or Poisonous Substance|||
2809|T074|A1.3.1|Medical Device|||
13.0 Database Creation Automation
We are providing scripts and control files to help in the creation and automation of your database loading with the RxNorm Release Files.
There are two sets of load scripts. The main directory of the zip archive contains a 'scripts' subdirectory. This directory contains the scripts for loading all of the non-OCD related data in RxNorm. This constitutes what most people are interested in. The 'ocd' subdirectory contains a separate 'scripts' directory that contains the load scripts for the OCD-related release files. This data consists of the SAB=RxNorm OCD term type atoms and their related information.
For clarity we will refer to these two separate 'scripts' directories as the 'main' scripts directory (Section 13.1) and the 'ocd' scripts directory (Section 13.2).
13.1 Main scripts subdirectory
13.1.1 Oracle database (\scripts\oracle subdirectory)
Filenames and brief descriptions follow:
| Filename | Description |
|---|---|
| populate_oracle_rxn_db.bat | The batch control file for the load process |
| rxn_index.sql | Index creation commands (optional) |
| RxNormDDL.sql | Table creation (and deletion) commands |
| RXNATOMARCHIVE.ctl | Control file for RXNATOMARCHIVE.RRF |
| RXNCONSO.ctl | Control file for RXNCONSO.RRF |
| RXNCUI.ctl | Control file for RXNCUI.RRF |
| RXNCUICHANGES.ctl | Control file for RXNCUICHANGES.RRF |
| RXNDOC.ctl | Control file for RXNDOC.RRF |
| RXNREL.ctl | Control file for RXNREL.RRF |
| RXNSAB.ctl | Control file for RXNSAB.RRF |
| RXNSAT.ctl | Control file for RXNSAT.RRF |
| RXNSTY.ctl | Control file for RXNSTY.RRF |
The populate_oracle_rxn_db.bat file contains 4 parameters, which should be customized for your particular environment. They are as follows:
| Parameter | Description |
|---|---|
| ORACLE_HOME | The location of your RDBMS software binaries (in this case, Oracle) |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| tns_name | The server name (or SID) of your database |
Once these parameters are populated, you should remove the '::' (double colon) characters from the front of each line - this will make the commands active.
Use the RxNormDDL.sql file to create the tables in your database schema (the schema must already exist), then use the .bat file to load the data into your tables. Once this is complete you may run the rxn_index.sql to create indexes on the tables, if so desired.
13.1.2 MySQL database (\scripts\mysql subdirectory)
Recommended Software Version - MySQL Server version 5.0
Filenames and brief descriptions follow:
| Filename | Description |
|---|---|
| Table_scripts_mysql_rxn.sql | Creates the tables |
| Populate_mysql_rxn.bat | Load the RxNorm files from windows into tables |
| populate_mysql_rxn.sh | Load the RxNorm files from Unix/Linux to tables |
| Load_scripts_mysql_rxn_win.sql | Windows loading script (called from populate script) |
| Load_scripts_mysql_rxn_unix.sql | Unix/Linux loading script (called from populate script) |
| Indexes_mysql_rxn.sql | Creates table indexes (optional) |
Prerequisite
The script is written to expect that there is a local MySql Server installed and configured and that you plan to load your RxNorm subset database into a database being managed by this local server. For more information on that, you are encouraged to visit http://www.mysql.com
Creating a Database
In order to load your subset into a MySQL database, you will have to either use an existing database or create a new one. To create a new database, use the following command:
CREATE DATABASE IF NOT EXISTS [database name] CHARACTER SET utf8;
Configuring and Running the Load Script
Windows
Copy the following files to the local drive, where the RRF files are saved:
- Load_scripts_mysql_rxn_win.sql
- Populate_mysql_rxn.bat
- Indexes_mysql_rxn.sql
- Table_scripts_mysql_rxn.sql
Edit the Populate_mysql_rxn.bat script and configure the five parameters at the top of the script. These are:
| Parameter | Description |
|---|---|
| MYSQL_HOME | The path to your MySQL home directory |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| host_name | The host name of your database server |
| db_name | The name of your RxNorm database schema |
MYSQL_HOME should be set to the installation directory of your MySQL server. This is likely to be something like “C:\Program Files\MySQL\MySQL Server 5.0". Remember to enclose the value in quotes if the directory name contains spaces. If your database is configured without a password you can leave the password setting blank. If your database is configured without a hostname you can leave the hostname blank or use localhost. After configuring these four parameters, this section of your script may look something like the following.
Set MYSQL_HOME= “C:\Program Files\MySQL\MySQL Server 5.0"
Set user=myuser
Set password=p4ssword
Set host_name=localhost
Set db_name =RxNorm
You are now ready to run the script. To do so, navigate to your subset directory using the Windows explorer and double-click on the “Populate_mysql_rxn.bat" file you just finished editing.
Here are some things to keep in mind when using this script:
- If your database is configured without a password, then update the script “Populate_mysql_rxn.bat“ and remove all references to the “-p%password%” parameter. Thus, a line like this:
%MYSQL_HOME%\bin\mysql –u %user% -p%password% -h%hostname% --local-infile=1 %db_name% <Table_scripts_mysql_rxn.sql >>mysql.log 2 >&1
Must be changed to:
%MYSQL_HOME%\bin\mysql –u %user% -h%hostname% --local-infile=1 %db_name% <Table_scripts_mysql_rxn.sql >>mysql.log 2 >&1 - The script will automatically create a log file called “mysql.log” in the same directory as the script. If you are planning on running the script more than once, you should consider renaming the log file before the second run, otherwise the contents of the first run will be lost.
- While the script is running, you can monitor the process from within the MYSQL command shell by using “mysql>show processlist;”
- The load may take some time. When it completes, each of the tables will be loaded.
- If you want to create indexes on the tables, then execute the following script:
Indexes_mysql_rxn.sql
At the mysql command prompt execute the command as given below.
USE <DATABASENAME>
Give the path where the Indexes_mysql_rxn.sql script is saved in the local drive.
Eg: \. C:\RXNORM\MySQL\Indexes_mysql_rxn.sql
Linux or Solaris
Copy the following files to the local drive, where the RRF files are saved:
- Load_scripts_mysql_rxn_unix.sql
- populate_mysql_rxn.sh
- Indexes_mysql_rxn.sql
- Table_scripts_mysql_rxn.sql
Edit the "populate_mysql_rxn.sh" script and configure the four parameters at the top of the script. These are:
| Parameter | Description |
|---|---|
| MYSQL_HOME | The path to your MySQL home directory |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| db_name | The name of your RxNorm database schema |
| dbserver | The name of your database server |
MYSQL_HOME should be set to the installation directory of your local MYSQL Server. If your database is configured without a password you can leave the password setting blank. After configuring these four parameters, this section of your script may look something like this.
MYSQL_HOME= /usr
user=myuser
password=p4ssw0rd
db_name=RxNorm
dbserver=ServerName
You are now ready to run the script. To do so, navigate to your subset directory and invoke the script from the command line. It may be necessary to change the file permissions to make the script executable before it can be started. For example:
chmod 775 populate_mysql_rxn.sh
And to execute the script
populate_mysql_rxn.sh
Here are some things to keep in mind when using this script.
- If your database is configured without a password, then update the script “populate_mysql_rxn.sh“ and remove all references to the –p%password%” parameter. Thus a line like this:
$MYSQL_HOME/bin/mysql –u $user –p$password $db_name <Table_scripts_mysql_rxn.sql >>mysql.log 2>&1
Must be changed to this:
$MYSQL_HOME/bin/mysql –u$user $db_name <Table_scripts_mysql_rxn.sql >>mysql.log 2>&1 - The script will automatically create a log file called “mysql.log” in the same directory as the script.If you are planning on running the script more than once, you should consider renaming the log file before the second run, otherwise the contents of the first run will be lost.
- While the script is running, you can monitor the process from within the MYSQL command shell by using “mysql>show processlist;”
- The load may take some time, when it completes, each of the tables will be loaded.
- To create indexes execute the script “Indexes_mysql_rxn.sql”.
At the shell command prompt, type:
chmod +x Indexes_mysql_rxn.sql
mysql -umyuser –pmypass -hmyhost RxNorm < Indexes_mysql_rxn.sql
13.2.1 Oracle database (\ocd\scripts\oracle subdirectory)
Filenames and brief descriptions follow:
| Filename | Description |
|---|---|
| populate_oracle_rxn_db.bat | The batch control file for the load process |
| rxn_index.sql | Index creation commands (optional) |
| RxNormDDL.sql | Table creation (and deletion) commands |
| RXNCONSOOCD.ctl | Control file for RXNCONSOOCD.RRF |
| RXNSATOCD.ctl | Control file for RXNSATOCD.RRF |
| RXNSTYOCD.ctl | Control file for RXNSTYOCD.RRF |
The populate_oracle_rxn_db.bat file contains 4 parameters, which should be customized for your particular environment. They are as follows:
| Parameter | Description |
|---|---|
| ORACLE_HOME | The location of your RDBMS software binaries (in this case, Oracle) |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| tns_name | The server name (or SID) of your database |
Once these parameters are populated, you should remove the '::' (double colon) characters from the front of each line - this will make the commands active.
Use the RxNormDDL.sql file to create the tables in your database schema (the schema must already exist), then use the .bat file to load the data into your tables. Once this is complete you may run the rxn_index.sql to create indexes on the tables, if so desired.
13.2.2 MySQL database (\ocd\scripts\mysql subdirectory)
Recommended Software Version - MySQL Server version 5.0
Filenames and brief descriptions follow:
| Filename | Description |
|---|---|
| Table_scripts_mysql_rxn.sql | Creates the tables |
| Populate_mysql_rxn.bat | Load the RxNorm files from windows into tables |
| populate_mysql_rxn.sh | Load the RxNorm files from Unix/Linux to tables |
| Load_scripts_mysql_rxn_win.sql | Windows loading script (called from populate script) |
| Load_scripts_mysql_rxn_unix.sql | Unix/Linux loading script (called from populate script) |
| Indexes_mysql_rxn.sql | Creates table indexes (optional) |
Prerequisite
The script is written to expect that there is a local MySql Server installed and configured and that you plan to load your RxNorm subset database into a database being managed by this local server. For more information on that, you are encouraged to visit http://www.mysql.com
Creating a Database
In order to load your subset into a MySQL database, you will have to either use an existing database or create a new one. To create a new database, use the following command:
CREATE DATABASE IF NOT EXISTS [database name] CHARACTER SET utf8;
Configuring and Running the Load Script
Windows
Copy the following files to the local drive, where the OCD RRF files are saved:
- Load_scripts_mysql_rxn_win.sql
- Populate_mysql_rxn.bat
- Indexes_mysql_rxn.sql
- Table_scripts_mysql_rxn.sql
Edit the Populate_mysql_rxn.bat script and configure the five parameters at the top of the script. These are:
| Parameter | Description |
|---|---|
| MYSQL_HOME | The path to your MySQL home directory |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| host_name | The host name of your database server |
| db_name | The name of your OCD RxNorm database schema |
MYSQL_HOME should be set to the installation directory of your MySQL server. This is likely to be something like “C:\Program Files\MySQL\MySQL Server 5.0". Remember to enclose the value in quotes if the directory name contains spaces. If your database is configured without a password you can leave the password setting blank. If your database is configured without a hostname you can leave the hostname blank or use localhost. After configuring these four parameters, this section of your script may look something like the following.
Set MYSQL_HOME= “C:\Program Files\MySQL\MySQL Server 5.0"
Set user=myuser
Set password=p4ssword
Set host_name=localhost
Set db_name =RxNormOCD
You are now ready to run the script. To do so, navigate to your subset directory using the Windows explorer and double-click on the “Populate_mysql_rxn.bat" file you just finished editing.
Here are some things to keep in mind when using this script:
- If your database is configured without a password, then update the script “Populate_mysql_rxn.bat“ and remove all references to the “-p%password%” parameter. Thus, a line like this:
%MYSQL_HOME%\bin\mysql –u %user% -p%password% -h%hostname% --local-infile=1 %db_name% <Table_scripts_mysql_rxn.sql >>mysql.log 2 >&1
Must be changed to:
%MYSQL_HOME%\bin\mysql –u %user% -h%hostname% --local-infile=1 %db_name% <Table_scripts_mysql_rxn.sql >>mysql.log 2 >&1 - The script will automatically create a log file called “mysql.log” in the same directory as the script. If you are planning on running the script more than once, you should consider renaming the log file before the second run, otherwise the contents of the first run will be lost.
- While the script is running, you can monitor the process from within the MYSQL command shell by using “mysql>show processlist;”
- The load may take some time. When it completes, each of the tables will be loaded.
- If you want to create indexes on the tables, then execute the following script:
Indexes_mysql_rxn.sql
At the mysql command prompt execute the command as given below.
USE <DATABASENAME>
Give the path where the Indexes_mysql_rxn.sql script is saved in the local drive.
Eg: \. C:\RXNORM\MySQL\Indexes_mysql_rxn.sql
Linux or Solaris
Copy the following files to the local drive, where the OCD RRF files are saved:
- Load_scripts_mysql_rxn_unix.sql
- populate_mysql_rxn.sh
- Indexes_mysql_rxn.sql
- Table_scripts_mysql_rxn.sql
Edit the "populate_mysql_rxn.sh" script and configure the four parameters at the top of the script. These are:
| Parameter | Description |
|---|---|
| MYSQL_HOME | The path to your MySQL home directory |
| user | The user name to use to connect to your database schema |
| password | The password to use to connect to your database schema |
| db_name | The name of your OCD RxNorm database schema |
| dbserver | The name of your database server |
MYSQL_HOME should be set to the installation directory of your local MYSQL Server. If your database is configured without a password you can leave the password setting blank. After configuring these four parameters, this section of your script may look something like this.
MYSQL_HOME= /usr
user=myuser
password=p4ssw0rd
db_name=RxNormOCD
dbserver=ServerName
You are now ready to run the script. To do so, navigate to your subset directory and invoke the script from the command line. It may be necessary to change the file permissions to make the script executable before it can be started. For example:
chmod 775 populate_mysql_rxn.sh
And to execute the script
populate_mysql_rxn.sh
Here are some things to keep in mind when using this script.
- If your database is configured without a password, then update the script “populate_mysql_rxn.sh“ and remove all references to the –p%password%” parameter. Thus a line like this:
$MYSQL_HOME/bin/mysql –u $user –p$password $db_name <Table_scripts_mysql_rxn.sql >>mysql.log 2>&1
Must be changed to this:
$MYSQL_HOME/bin/mysql –u$user $db_name <Table_scripts_mysql_rxn.sql >>mysql.log 2>&1 - The script will automatically create a log file called “mysql.log” in the same directory as the script.If you are planning on running the script more than once, you should consider renaming the log file before the second run, otherwise the contents of the first run will be lost.
- While the script is running, you can monitor the process from within the MYSQL command shell by using “mysql>show processlist;”
- The load may take some time, when it completes, each of the tables will be loaded.
- To create indexes execute the script “Indexes_mysql_rxn.sql”.
At the shell command prompt, type:
chmod +x Indexes_mysql_rxn.sql
mysql -umyuser –pmypass -hmyhost RxNorm < Indexes_mysql_rxn.sql
Last Reviewed: March 7, 2011