Processing SIRxs Journals

1
Processing SIRxs Journals

• Presented at SIR-UK ConferenceDublin,
  Ireland, June 2008
  
• Tom Shriver, DataVisor 2008

2
Processing SIRxs Journals

• Presented at SIR-UK Conference
• Dublin, Ireland, June 2008
• Tom Shriver, DataVisor 2008

3
Progress of this Presentation

• A Wee Bit of Background
• Old Journaling vs New
• Previous Capabilities and SIRxs Options
• Mostly About PQL Processing of Journal
• Will Turn to Tony for some Internals
• Especially for Schema Journaling
• Discussion / Suggestions
• There is almost no PQL Processing ofJournalled
  Schema.
  
• What do we need? Want?

4
SIR2002 ( SIRxs)Journal Access ITEMIZE FILE

• Type of header Journal data
• Database name MNYR
• 'From' Update level 6638
• DATE 09/20/07
• TIME 153731
• 'To' Update level 6639
• Date 09/20/07
• TIME 153900
• Type of header Journal schema
• Database name MNYR
• 'From' Update level 6639
• DATE 09/20/07
• TIME 153900
• 'To' Update level 6640
• Date 09/20/07
• TIME 153927

5
SIR2002 ( SIRxs)Journal Access Journal Upload

• Creates a Text File
• Bare Bones Schema Information
• Data Formatted Similar to Export format(example
  MNYR.JUL jou_upload.out)
  
• Originally Intended to Download Data from
  Satellite databases to Central Database
  
• I once wrote a PQL program to process this
  journal information

6
SIR2002 ( SIRxs) Journal Usage JOURNAL RESTORE
JOURNAL DOWNLOAD

• Apply Data from Journal to Database
• RESTORE
• Applies Updates synchronized by Update Level
• DOWNLOAD
• Applies Updates Update Level Independent

7
Whats in a SIRxs Journal File?

• Journal is a Sequential File
• Tracks Update Levels
• What happened at update level
• When to what the update level happened
• Who controlled update (Master, User)
• Schema Changes
• Data Changes
• New Records
• Modified Records
• Deleted Records

8
Journal File Headers

• Two General Types of Headers in Journal
• Update Level Header
• Detail (i.e. record specific) Headerwithin
  update level

9
Detail Records

• Copies of Database Records
• Nearly Identical Structure
• Journal has additional header info
  (new/old/deleted)
  
• New Records
• Copy of new record journaled
• Modified Records
• Copy of pre-modification record journaled
• Copy of post-modification record journaled
• Deleted Records
• Copy of pre-deletion record journaled

10
What the Journal Does Not Have

• Does Not have a Complete Schema Definition
• ImplicationsThe Journal doesnt know simple
  things like
  
• Whats the name of Record Type 3?
• Whats the value of CURRPOS in this new record?
• To interpret what journal data means, we must
  have a schema attached
  

11
Practical Implications Usage

• Better Journal Utility Functionality (e.g.
  Rollback, standard SIR reports)
  
• Better Analytic tools for various things
• User Defined Recovery /or Rollback
  functionality
  
• Audit Trails

12
Journal Processing with PQL

• New Tools to Read a Journal File
• PROCESS JOURNAL
• Steps through Journal File
• Reads Journal Headers Returns Information
• JOURNAL RECORD IS
• Reads detail record from journal and gets data
  as we expect from a RETRIEVAL
  
• Must have a SCHEMA (i.e. database) available
• Is not indexed
• No VIA, NEXT REC, etc.

13
PROCESS JOURNAL Syntax

• PROCESS JOURNAL FILENAME jou_file REVERSE
  
  
• FROM j_slevel START
  j_sdate , j_stime
  
• THRU j_elevel END
  j_edate , j_etime
  
• DATE up_date ENDDATE
  up_edate
  
• TIME up_time ENDTIME
  up_etime
  
• LEVEL up_level
• RECORD up_rt
• TYPE up_type
• USER up_user
• DATABASE up_db
• MASTER up_mst
• Above is example from PQL to right of are
  local PQL variables, not syntax notation.
  
• Options generally indicate content of journal
  headers.
  

14
PROCESS JOURNAL Update Types

• value labels up_type
• ( 1)"New record written"
• ( 2)"Before existing record
  updated"
  
• ( 3)"After existing record
  updated"
  
• ( 4)"Before Record deleted"
• (-1)"Journal Data Header"
• (-2)"Unload Schema Header
  unload!
  
• (-3)"Unload Data Header
  unload!
  
• (-4)"Journal Schema Header"
• (-5)"User Header"
• From Example PQL program to process
  journals.(see jou_summ_dublin.pql)

15
Journal Processing Example
Summary Report

• Summarize a daily journal by
• Record Type
• Type of Update
• User
• see jou_summ_dublin.pql
• Logic of PQL
• Create SubProcedures for JOURNAL REC IS
• PROCESS JOURNAL
• . EXECUTE SUBPROCEDURE rec_nn
• END JOURNAL
• INCLUDE FILE created_subprocedures.pql
• OUTPUT REPORT

16
Journal Processing Example
Customized Database Recovery

• Situation
• User was updating database late at night (9-12
  PM)
  
• Backups Begin at Midnight
• User (via Master) and BackUps (single user)
  struggled for control
  
• BackUps Wins Struggle database is a mess
• Solution
• Write a PQL program to decipher two journals
  fix database
  
• Used modified version of Summary ReportWas
  actually quite simple.

17
Journal Processing Usage Potential
Audit Trails (1)

• Problems to Overcome
• Whats an Audit Trail?All variables all
  records?Subset of important variables (e.g.
  adverse reactions)?Need User Explanation for
  Modifications? Coded? Textual Note
  Written?
• These issues can be standardized and implemented
  automatically - A place to store
  preferences - A way to automate rule
  conformance (e.g. screens) - A place to store
  audited records

18
Journal Processing Usage Potential
Audit Trails (2)

• Implementation Strategy Possibilities
• Keep all Journal Files (or one continuous file)
  andProcess when Needed
  
• Problem No Schema in Journal
• Maybe Tony could store schema in Journal
• Store Audit Data in Database
• Mirror Image Rectypes with additional high level
  KeysProblem What if keys changed? CaseID?
  
• Store Audit Data Outside Database
• Audit Database
• Audit Tabfile
• Other File Type (text, CSV, etc)

19
Audit Trail Example Audit Deleted Recs

• At DCC managers and data entry staff send emails
  to notify of deletions (because deletions show up
  in standard reports).
  
• Example jou_audit_del_dublin.pql