Transcript Quick GDR Introduction

Bertrand Russell (1872-1970) described his philosophy as a kind of
“logical atomism”, by which he meant to endorse both a metaphysical view
and a certain methodology for doing philosophy.
The metaphysical view amounts to the claim that the world consists of a
plurality of independently existing things exhibiting qualities and
standing in relations. According to logical atomism, all truths are
ultimately dependent upon a layer of atomic facts, which consist either of
a simple particular exhibiting a quality, or multiple simple particulars
standing in a relation.
The methodological view recommends a process of analysis, whereby one
attempts to define or reconstruct more complex notions or vocabularies in
terms of simpler ones. According to Russell, at least early on during his
logical atomist phase, such an analysis could eventually result in a
language containing only words representing simple particulars, the simple
properties and relations thereof, and logical constants, which, despite
this limited vocabulary, could adequately capture all truths.
Russell's logical atomism had a profound influence on analytic philosophy
in the first half of the 20th century; indeed, it is arguable that the
very name "analytic philosophy" derives from Russell's defense of the
method of analysis.
Stanford Encyclopedia of Philosophy
http://plato.stanford.edu/entries/logical-atomism/
Part I: Supporting The GDR User
Learn how to help solve a site’s data
problems using the GDR in just one
hour!
Our Advanced Objective:
• Make Meaningful Modules
End with SQL
• Everyone’s First SQL lesson
• In Structured Query Language (SQL) we:
– SELECT
A comma separated list of columns
FROM
A comma separated list of ‘tables’
WHERE
Two columns are equal (joins)
or
Some fact about a column is true
End with SQL: SELECT
• Comma separated list of columns
– Practical English: the columns of data which need
to show in the report
– Advanced: Columns, always including PATID,
which make a useful module
• Patid, name, mrn, dob
• Patid, first fever time
• Patid, delay code, case type (from peri-operative note)
End with SQL: FROM
• Comma separated list of ‘tables’
– Practical English: Essentris data sources,
usually identified in part by FULLNAME, Note
Name, or else documented in the GDR manual
– Advanced: Any of the the above plus any of the
user-defined or CCI-defined GDR code
modules
– FAQ: where is ____ data in the GDR?
End with SQL: WHERE
• Two columns are equal (joins) or
• Some fact about a column is true
– FAQ: How are __ and __ related to each other in the
GDR?
– A: Depends…
• PATID: same patient, almost always part of a join
• KEY: same ‘time’, part of a join when concurrency is an issue
• MINORIT:same row in Order Entry or on Order Flowsheets
Start with Essentris
• We already know our way around Essentris
• Identify GDR related details in Essentris (look
for “SELECT…FROM..WHERE…” patterns)
–
–
–
–
–
Admit Notes
Timed Notes
Vitals Flowsheets
Orders and Order Flowsheets
Admission/Discharge/Transfer functions
Admit Notes
• User defined template document
• Only one copy stored on the patient record
• Has labels, checkboxes and text fields
– In EDIT mode, right-click checkboxes and text fields,
select “describe item”, then double click the Label
“Item Edit History”
– Text fields can be of two types
• DATABASE:
– Reference the DBI FULLNAME
• NOTE:
– Reference the TAG String (ex: !207.ABC^5.10)
Image: Notes Describe Item
Image: Notes Describe Item
Admit Notes tangent: Consider
just One DBI View in the GDR
• 13,000 DBI Views are all the same in these ways:
– GDR Database Item VIEWNAME = FULLNAME +
‘_’ + IT# (kind of)
– Columns
•
•
•
•
•
KEY
MINORIT
PATID
DATA
OLID & NLID
Image: Single DBI Data
GDR DBI View Columns: KEY
• “Time of the data”
–
–
–
–
ADMIT note: KEY = admit datetime
Timed/Prompt Note: KEY = “Note Time”
Flowsheet: KEY = time column
KEY  LOGTIME (almost always…)
GDR DBI View Columns:MINORIT
• In Essentris components which have
dynamic or changing rows, MINORIT is
like a row number
• Order Entry
• Order Flowsheet
GDR DBI View Columns:PATID
•
•
•
•
•
Internally generated encounter number
Unique across installations
Column that joins a single patient’s data
NOT visible in Essentris
Column that is almost never shown in
Reports
GDR DBI View Columns:DATA
• DATA column shows the value that was
stored in Essentris.
• Almost always defined as varchar2(4000)
– All dates and numbers have to go through a
datatype conversion from text in order to
perform any math on the data
• Sometimes defined as Number datatype
GDR DBI View Columns:OLID
& NLID
• Literally: Old Link ID and New Link ID
• The Triple: PATID, OLID, NLID uniquely
identify a charting event in Essentris
• All data stored by one person at one time
will have the same triple.
• Reference the triple in the LOGTABLE to
find the details of the charting event
Admit Notes: One DBI example
SQL
• FULLNAME = Name
• VIEWNAME = NAME_517
• (refer to site/master files, or A_ITCONTRAST in
the GDR)
SELECT patid, data
FROM name_517
Image Name_517 Data
Admit Notes: Three DBI
example SQL (Patient Details) #1
• VIEWNAMES: NAME_517, MRN_518,
DOB_519
Admit Notes: Three DBI
example SQL (Patient Details) #1
• VIEWNAMES: NAME_517, MRN_518,
DOB_519
• SELECT NAME_517.patid, NAME_517.data,
MRN_518.data, DOB_519.data
FROM
NAME_517, MRN_518, DOB_519
WHERE NAME_517.patid = MRN_518.patid
and
NAME_517.patid = DOB_519.patid
Image: 3 DBIs #1
Admit Notes: Three DBI
example SQL (Patient Details) #2
• SELECT
FROM
WHERE
name.patid, name.data,
mrn.data, dob.data
NAME_517 name, MRN_518 mrn,
DOB_519 dob
name.patid = mrn.patid
and
name.patid = dob.patid
Image: 3 DBIs #2
Admit Notes: Three DBI
example SQL (Patient Details) #3
• SELECT name.patid,
name.data name,
mrn.data mrn,
dob.data dob
FROM
NAME_517 name,
MRN_518 mrn,
DOB_519 dob
WHERE name.patid = mrn.patid
and name.patid = dob.patid
Image: 3 DBIs #3
Admit Notes: Three DBI
example SQL (Patient Details) #4
• Without specifying the columns names:
• SELECT
*
FROM
NAME_517 name,
MRN_518 mrn,
DOB_519 dob
WHERE
name.patid = mrn.patid and
name.patid = dob.patid
Admit Notes: Note Items
• Reference the TAG string for the Text
Prompt (ex: !200.PER^6.2)
• All sites use code like the following which
comes from a template to get Note Item
data from notes:
Admit Notes: Note Items SQL
(Real Example: Peri-Operative
data)
• Code below yields one record to one patient
• SELECT
patid, key,
max(decode(tag,497,data)) Delay_Code,
max(decode(tag,2 ,data)) Case_Type,
max(decode(tag,484,data)) DT_Univ_protocol
FROM
peri_operativenote_200per
GROUP BY
patid, key
Image: P/O Note Data
Timed Notes: Note Items SQL
(Generic Example)
• Code is the same logically, but the result is many
records to one patient
• SELECT
patid, key,
max(decode(tag,1,data)) col_name_for_tag1,
max(decode(tag,2,data)) col_name_for_tag2,
max(decode(tag,3,data)) col_name_for_tag3
FROM
timed_note_view
GROUP BY
patid, key
Essentris Flowsheets
Image: Flowsheet
Parameter Flowsheets (Vitals)
• Time driven values
• Database items
• DBIs have a “many to one” relationship to patient
– Most reports require we find a single value to report:
• MIN/MAX(data or key), the value closest to a point in time, a
single LIST of values, etc
• Turn chronological DBI charting into Start and Stop times for
treatment intervals: vent use, restraints, etc
Vitals Flowsheet Database items
• Right-click, select “Describe Item” to find
the site-defined FULLNAME
• FULLNAME is the prefix of the GDR
VIEWNAME for that data
Image: Flowsheet Describe Item
GDR Database Items
• VIEWNAME = FULLNAME + ‘_’ + IT#
• Columns
–
–
–
–
–
KEY
MINORIT
PATID
DATA
OLID & NLID
Flowsheet Database Item SQL
example: TempF
• FULLNAME: TempF(DegF)
• VIEWNAME: TEMPF_DEGF__163
– SELECT patid, data
FROM tempf_degf__163
WHERE data > 100
Image: tempf #1
Flowsheet Database Item SQL
example: First Fever (a la
TempF) per patient
• SELECT patid, min(key) key
FROM tempf_degf__163
WHERE data > 100
GROUP BY patid
Image: tempf #2
- STOP • We’ve already made three modules without
even talking about what they are
– GDR Modules are Reuseable Blocks of SQL
code which accomplish a specific task
• Module1: Patient Details
• Module2: Peri Operative Data
• Module3: Time of First Fever
Putting Modules to work: Easy!
• Best Practices involves identifying a main or
“driving list” (DL)
– DL is verifiable, simple, and is the left side of all joins
in the WHERE clause
– We’ll use patient detail as the driving list
• If this were easy, the code would be:
• SELECT * from patient_details,
peri_operative_data, first_fever_time
WHERE patient_details.patid =
peri_operative_data.patid and
patient_details.patid = first_fever_time.patid
Putting Modules to Work: Easy…
• With formatting:
• SELECT
*
-- this * means: all the columns
FROM
patient_details,
peri_operative_data,
first_fever_time
WHERE
patient_details.patid = peri_operative_data.patid
and
patient_details.patid = first_fever_time.patid
Putting Modules to Work: Easy?
• With “stand in” fake code to show the logic:
• SELECT
*
FROM
(select…from…where…) patient_details,
(select…from…where…) peri_operative_data,
(select…from…where…) first_fever_time
WHERE
patient_details.patid = peri_operative_data.patid
and
patient_details.patid = first_fever_time.patid
Putting Modules to Work: Not
Easy To Read
• With REAL code to show the final resulting
code:
SELECT *
FROM
-(SELECT name.patid, name.data name, mrn.data mrn, dob.data dob FROM
NAME_517 name, MRN_518 mrn, DOB_519 dob WHERE name.patid = mrn.patid and
name.patid = dob.patid ) patient_details,
-(SELECT patid, key, max(decode(tag,497,data)) Delay_Code,
max(decode(tag,2 ,data)) Case_Type, max(decode(tag,484,data))
DT_Univ_protocol
FROM peri_operativenote_200per GROUP BY patid, key) peri_operative_data,
--(SELECT patid, min(key) key FROM tempf_degf__163 WHERE data > 100 GROUP BY
patid) first_fever_time
-WHERE patient_details.patid = peri_operative_data.patid
and
patient_details.patid = first_fever_time.patid
Putting Modules to Work: Easy?
• With “stand in” fake code to show the logic:
• SELECT
*
FROM
(select…from…where…) patient_details,
(select…from…where…) peri_operative_data,
(select…from…where…) first_fever_time
WHERE
patient_details.patid = peri_operative_data.patid
and
patient_details.patid = first_fever_time.patid
Flash Back Slide- Admit Notes:
Three DBI example SQL (Patient
Details) #4
• Without specifying the columns names:
• SELECT
*
FROM
NAME_517 name,
MRN_518 mrn,
DOB_519 dob
WHERE
name.patid = mrn.patid
and
name.patid = dob.patid
Flash Back Slide- Admit Notes:
Three DBI example SQL (Patient
Details) #4
• To really show the sameness:
• SELECT
*
FROM
(select *
(select *
(select *
WHERE
name.patid
and
name.patid
from NAME_517) name,
from MRN_518) mrn,
from DOB_519) dob
= mrn.patid
= dob.patid
Getting Back to Essentris….
• Order Entry, Orders Flowsheet
– Not a lot of user-defined Names of data objects
– “All this code has already been written”
• ADT data
– Census Reporting: The Last Frontier
Life Cycle of Orders focusing on
GDR data
• ENTERED:
– ORDERVDATANEW_6076
• ACK/VERIFIED:
– ORDERAPPROVAL_1302,
• then a “Order Definition table”
– depending on the order category and
– the configured order type (MED, IVD, TRT, INP,OUT –not
OTH)
• DELIVERED:
– “Order Delivery Table”
• based on the “Order Definition Table”
Order Entry
• Most of the data seen in OE is found in
table 6076, the order entry table
– ORDERVDATANEW_6076
• No comment
Order Approval
• PATID and MINORIT join up on
ORDERVDATANEW_6076
• PATID, MAJORIT_OA, and MINORIT_OA join
up on a “Order Definition Table” (kind of)
• MEDS Example:
– MAJORIT_OA = 2225 for MED type orders (where
OE category = ‘Medications”)
– MEDS_FLOW_DEFINITION_2225 gets a record with
the name of the MED order upon ACK
Order Flowsheets: Meds
Flowsheet
• Tables which contains data for Med Orders
Flowsheet:
– MEDS_FLOW_ANNOTATION_2231
MEDS_FLOW_COMMENT_2267
MEDS_FLOW_DEFINITION_2225
MEDS_FLOW_DELIVERY_2230
MEDS_FLOW_DOSE_2229
MEDS_FLOW_FREQUENCY_2226
MEDS_FLOW_LABEL_COMMENT_2227
MEDS_FLOW_REMARKS_2925
MEDS_FLOW_ROUTE_2228
ADT Data
• Admission/Discharge/Transfer data
A_ADTHISTORY
• HL7 interface or Staff will add records to
the ADTHISTORY table indicating patient
location (UNIT, BED)
A_UNITHISTORY
• Uses logic based on A_ADTHISTORY
table to figure out the Enter Times and Exit
Times per patient per unit
A_UNITBEDHISTORY
• Uses logic based on A_ADTHISTORY
table to figure out the Enter Times and Exit
Times per patient per unit per bed
A_PATIENTLIST
• Uses logic based on A_ADTHISTORY
table to show one representative record per
patient. Includes data like: name, mrn,
hospno, MD, admtime
• In Practice: A_PATIENTLIST = the patient
records that are loaded into the GDR
A_PATCOMMON
• Based on a script which runs per 10 mins on
the ncrontab scheduler, This table list all the
active patients in Essentris
A_ARCHLIST
• Based on a script which runs from the
ncrontab scheduler @1 AM
• Lists all the archive records on the
filesystem
EXAMPLES of USEFUL
MODULES
• Who got which note when
• OE with Status
• OE to the Order Flowsheet by way of Order
Approval
• ADTHISTORY with previous stay data
filtered
Who got which note when
select
nd.patid,
nt.note_name, nt.note_version, nt.time_flag,
lt.time note_creation_time, lt.operator,
lt.name staff_name, lt.terminal
from
cci_active.note_definition_2304 nd,
cci_active.notetypes nt,
cci_active.a_logtable1 lt
where
-- Table Joins:
substr(nd.name,0,8) = nt.NOTE_type
and
nd.patid = lt.patid and
nd.olid = lt.olid and
nd.nlid = lt.nlid
Image: who got which note when?
OE with Status
select
decode(previd,'',decode(nextid,'','Order Entered'))||
decode(previd,'',decode(nextid,'',
case when sysdate > starttime then ' Started'
when sysdate < starttime then ' Not Started'
end ))||
decode(previd,'','',decode(nextid,'','Order to DC Entered'))||
decode(previd,'','',decode(nextid,'',
case when sysdate > starttime then ' Started'
when sysdate < starttime then ' Not Started'
end ))||
decode(nextid,'','',decode(previd,'',
case when sysdate > starttime then 'Order Entered DC-ed'
else ' Order is Active' end))
status,
oe.*
from ordervdatanew_6076 oe
Image: OE with Status
OE to the Order Flowsheet by
way of Order Approval
select
oe_status.*, oa.*, mdef.*, mdel.*
from
( SQL for oe_status ) oe_status,
-ORDERAPPROVAL_1302 oa,
-(select '2225' mojorit, def.*
from cci.MEDS_FLOW_DEFINITION_2225 def) mdef,
-(select patid, minorit, min(key) first_delivery
from cci.MEDS_FLOW_DELIVERY_2230
group by patid, minorit ) mdel
where
oe_status.patid = oa.patid and oe_status.minorit = oa.minorit and
oa.majorit_oa = mdef.majorit and oa.minorit_oa = mdef.minorit and
oa.patid = mdef.patid and
mdef.patid = mdel.patid (+) and mdef.minorit = mdel.minorit (+)
ADTHISTORY with previous
stay data filtered
select
adt.*
from
a_adthistory adt,
(
select mrn, patid, begstay, lead_begstay stay_limit
from (select mrn,
lag(patid) over(partition by mrn order by begstay) lag_patid,
lead(patid) over(partition by mrn order by begstay) lead_patid,
patid,
begstay,
lag(begstay) over(partition by mrn order by begstay) lag_begstay,
lead(begstay) over(partition by mrn order by begstay) lead_begstay
from (
select
mrn.DATA mrn,
Stay_Interval.*
from
idnumber_518 mrn ,
(select patid, min(time) BegStay, max(time) EndStay
from adthistory group by patid) Stay_Interval
where
Stay_interval.patid = mrn.patid (+)
) mrn_pat_stays)
where lag_patid is null or lead_patid is null or
lead_patid <> patid
) lim
where
adt.patid = lim.patid and adt.TIME > = cast(lim.begstay as date) and
(lim.stay_limit is null or adt.time < cast(lim.stay_limit as date))
Image: ADTHISTORY2
We still need code for these
issues:
• Conditional Date formatting
– Variation in the formats of dates
– Typos in dates
– Oracle fails in bad way
• Dealing with unexpected data
– Comparing numbers
– Doing math
The Most Complicated Report
possible
Select *
From
(SELECT..FROM..WHERE..) DL -- driving list
(SELECT..FROM..WHERE..) single_dbi_data,
(SELECT..FROM..WHERE..) multi_dbi_data,
(SELECT..FROM..WHERE..) admit_note_data,
(SELECT..FROM..WHERE..) timed_note_data,
(SELECT..FROM..WHERE..) Order_data,
(SELECT..FROM..WHERE..) ADT_data,
Where
DL.patid = single_dbi_data.patid and
DL.patid = multi_dbi_data.patid and
DL.patid = admit_note_data.patid and
DL.patid = timed_note_data.patid and
DL.patid = Order data.patid and
DL.patid = ADT data.patid
Conditional Date Formatting
• Problem:
– Users are typing in dates that are not valid
Conditional Date Formatting
• Cause:
– Staff is unaware that validation codes exist
– Staff cannot add validation codes to notes, have
to get CCI to do
– CCI’s validation codes are not good enough to
prevent bad data
Conditional Date Formatting
• Fix:
– Validation Codes need to be emphasized in
training
– Users need to be able to add their own
validation codes
– CCI’s codes need to be improved
Examples
• Current Validation Codes:
ipr_format=
– "mandatory, mm/dd/yyyy|^[0-9][0-9]/[0-3][0-9]/[0-9]{4}$”
• This allows 00/39/0000 and 99/00/9999 as dates
– "mandatory, 0000|^[0-2][0-3][0-5][0-9]$“
• This doesn’t allow 1400-1900 as a time, which is wrong
Examples, Cont’d
• #please note this should all be one line:
ipr_format=
• "mandatory, hhmi ddMonYYYY|^([0-1][0-9][0-5][0-9]|[2][0-3][05][0-9]) ([0][1-9]|[1-2][0-9]|[3][0-1])
[JAN]|[Jan]|[jan]|[FEB]|[Feb]|[feb]|[MAR]|[Mar]|[mar]|[APR]|[Apr]|[a
pr]|[MAY]|[May]|[may]|[JUN]|[Jun]|[jun]|[JUL]|[Jul]|[jul]|[AUG]|[Aug
]|[aug]|[SEP]|[Sep]|[sep]|[OCT]|[Oct]|[oct]|[NOV]|[Nov]|[nov]|[DEC]|[
Dec]|[dec]) [1-2][0-9][0-9][0-9]$“
– for time: 0000-2359 are valid, 2400 and up is invalid
– for day of month: only 01-31 are valid
– for month: only Jan-Dec in either all CAPITALS, all lower case, or Initial
Caps are valid
– for year: 1000-2999 are valid
Conditional Date Formatting
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
create or replace function to_date2(strDate varchar2) return date as
ret date;
begin
if REGEXP_LIKE(strDate,'^([0]{01}[1-9]|[1-2][0-9]|[3][0-1])(jan|mar|may|jul|aug|oct|dec)\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'ddmonrr');
elsif REGEXP_LIKE(strDate,'^([0]{01}[1-9]|[1][0-9]|[2][0-8])feb\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'ddmonrr');
elsif REGEXP_LIKE(strDate,'^29feb(1992|1996|2000|2004|2008)$','i') THEN
RETURN TO_DATE(strDate, 'ddmonrr');
elsif REGEXP_LIKE(strDate,'^([0]{01}[1-9]|[1-2][0-9]|[3][0])(apr|jun|sep|nov)\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'ddmonrr');
elsif
REGEXP_LIKE(strDate,'^(01|1|03|3|05|5|07|7|08|8|10|12)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|1
6|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31)/\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr');
elsif
REGEXP_LIKE(strDate,'^(02|2)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|
25|26|27|28)/\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr');
elsif
REGEXP_LIKE(strDate,'^(04|4|06|6|09|9|11|)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|16|17|18|19|2
0|21|22|23|24|25|26|27|28|29|30)/\d{2,4}$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr');
elsif
REGEXP_LIKE(strDate,'^(01|1|03|3|05|5|07|7|08|8|10|12)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|1
6|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31)/\d{2,4} (0[0-9][0-9][0-9]|1[0-9][0-9][0-9]|2[0-3][0-9][0-9])$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr hh24mi');
elsif
REGEXP_LIKE(strDate,'^(02|2)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|
25|26|27|28)/\d{2,4} (0[0-9][0-9][0-9]|1[0-9][0-9][0-9]|2[0-3][0-9][0-9])$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr hh24mi');
elsif
REGEXP_LIKE(strDate,'^(04|4|06|6|09|9|11|)/(01|1|02|2|03|3|04|4|05|5|06|6|07|7|08|8|09|9|10|11|12|13|14|15|16|17|18|19|2
0|21|22|23|24|25|26|27|28|29|30)/\d{2,4} (0[0-9][0-9][0-9]|1[0-9][0-9][0-9]|2[0-3][0-9][0-9])$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr hh24mi');
elsif
REGEXP_LIKE(strDate,'^(02|2)/29/(1876|1880|1884|1888|1892|1896|1900|1904|1908|1912|1916|1920|1924|1928|1932|1936|1940|19
44|1948|1952|1956|1960|1964|1968|1972|1976|1980|1984|1988|1992|1996|2000|2004|2008|2012|2016|2020|2024|2028|2032|2036|20
40|2044|2048|2052|2056|2060|2064|2068|2072|2076|2080|2084|2088|2092|2096|2100|2104|2108|2112|2116|2120|2124|2128|2132|21
36) (0[0-9][0-9][0-9]|1[0-9][0-9][0-9]|2[0-3][0-9][0-9])$','i') THEN
RETURN TO_DATE(strDate, 'mm/dd/rr hh24mi');
else return to_date('31-jan-1492','dd-mon-rr');
End if;
End;
To_date2( ) in use
Part II: Supporting the GDR tool
•
•
•
•
•
•
ncrontab failures
Transactional data failures
Partition space fills up
Indexes’ statistics get old
User accounts expire/ get locked
User permissions don’t allow a certain desired
actions
• Users write monster reports or logical circles
Supporting the GDR: ncrontab
• ncron jobs (UCLA GDR1)
ucla5
%10 * * * * Perms gdr1; exit 0
ucla5 %5 * * * * patCommon -c gdr1 2>&1; exit 0
ucla5
0 4 * * * dbshm gdr1;staffm gdr1;notetypes gdr1>/dev/null
2>&1;notetypesAndtmplate gdr1; conDef gdr1;exit 0
ucla5
0 1 * * * archlist $CCSYSDIR/ccarch gdr1;exit 0
ucla5
0 5 * * * CmdCreateView -c gdr1 -s active >/dev/null; CmdCreateView -c
gdr1 -s archive >/dev/null ; CmdCreateView -c gdr1 -s view >/dev/null;
GDR_STATIC_VIEWS ; CmdCreateIndex -o Create -c gdr1 -s active ; CmdCreateIndex
-o Create -c gdr1 -s archive; exit 0
Supporting the GDR: ncrontab
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Perms gdr1
patCommon -c gdr1 2>&1
dbshm gdr1
staffm gdr1
notetypes gdr1>/dev/null 2>&1
notetypesAndtmplate gdr1
conDef gdr1
archlist $CCSYSDIR/ccarch gdr1
CmdCreateView -c gdr1 -s active >/dev/null
CmdCreateView -c gdr1 -s archive >/dev/null
CmdCreateView -c gdr1 -s view >/dev/null
GDR_STATIC_VIEWS
CmdCreateIndex -o Create -c gdr1 -s active
CmdCreateIndex -o Create -c gdr1 -s archive
Perms gdr1
• Usually set to run every 5 or 20 mins
• Checks which Essentris users have “modify” access to the
GDR (Perm #75)
• Creates or updates accounts in the GDR
patCommon -c gdr1 2>&1
• Runs every 10 mins
• Creates A_PATCOMMON table which lists all the active
patients in Essentris
dbshm gdr1
• Creates MASTERDBSHM and SITEDBSHM tables from
the config files
staffm gdr1
• Creates A_STAFFM table from the staff.m config file
notetypes gdr1>/dev/null 2>&1
• Creates the A_NOTETYPES table from notetypes config
file
notetypesAndtmplate gdr1
• Creates all the views for notes
conDef gdr1
• Creates SIDDEF, DIDDEF, AIDDEF tables
• Not sure what it’s for
archlist $CCSYSDIR/ccarch gdr1
• Usually runs once a night
• Creates A_ARCHLIST table which list all the archived
patients in Essentris
CmdCreateView -c gdr1 -s active >/dev/null
• Usually runs once a day
• Creates all the database item views in the ACTIVE schema
CmdCreateView -c gdr1 -s archive >/dev/null
• Usually runs once a day
• Creates all the database item views in the ARCHIVE
schema
CmdCreateView -c gdr1 -s view >/dev/null
• Usually runs once a day
• Creates all the database item views in the CCI or VIEW or
Union or Combo schema
GDR_STATIC_VIEWS
• Creates several helpful views
– A_LOGTABLE1
• Logtable with staff names
– A_PATIENTLIST
• List of patients loaded in the GDR
– A_ITCONTRAST
• Join of site file and master file
– A_UNITHISTORY
• Unit level summary of patient location data
– A_UNITBEDHISTORY
• Bed level summary of patient location data
These jobs create/updates indexes
in Oracle
• CmdCreateIndex -o Create -c gdr1 -s active
• CmdCreateIndex -o Create -c gdr1 -s archive