DUG Session 4488 Creating Custom Files

1
DUG Session 4488Creating Custom Files

• Jeff Butera
• Hampshire College
• jbutera_at_hampshire.edu

Monday, March 10, 2008 405PM Hoover
2
Outline

• Custom files To have or not to have?
• Best Practices
• Creation, schema population
• Indexing
• Triggers
• WARNING Unidata perspective!
• Survey time Do you have any? Many?

3
To Have or Not?

• Custom files a necessity (?!)?
• Don't add data elements to Datatel files
• Get away from USER fields
• Don't misuse existing data fields
• Create custom co-files of Datatel's
• Create pure custom files as needed
• Stored computed columns are GREAT!

4
What Does Hampshire Have?

• Custom co-files sample
• H08.PERSON
• H08.STUDENTS
• H08.STUDENT.TERMS
• Pure custom sample
• H08.CR.DIV.DATA
• H08.PS.VEHICLES
• H08.WORK.FILE
• We have 138 custom files

5
Best Practices

• Have custom file naming convention
• H08.filename
• Have custom prefix naming convention
• Xxxx.fieldname (pure custom)?
• XHpre.fieldname (Datatel cofiles)?
• Don't use PERSON, ADDRESS as examples!
• Consistency is key

6
Best Practices

• Example STUDENTS
• Field prefix STU.
• Computed Column prefix XSTU.
• Custom cofile H08.STUDENTS
• Field prefix XHSTU.

7
Best Practices Custom Cofiles

• LIST DICT H08.PERSON
• XHPER.DIR.PHONE 13 Dir Phone 8L S
• XHPER.DIR.POBOX 15 Dir Box 6L S
• XHPER.DIR.ROOM 17 Dir Room 8L S
• XHPER.DIR.TITLE 14 Dir Title 60L M
• XHPER.DIR.TYPE 11 Dir Type 14L S
• XHPER.DIR.YEAR 29 Dir Year 6L S

8
Best Practices Pure Custom

• LIST DICT H08.CR.DIV.DATA
• XCDD.CREATED.BY 51 Created? 10L S
• XCDD.DIV 65 Division 2L S
• XCDD.EVAL.DATE 7 D4/ Eval Date 10R S
• XCDD.FF.IDS 10 FF IDS 10L M
• XCDD.FF.TYPES 11 Type 8L M
• XCDD.FILE.DATE 33 D4/ File Date 10R S

9
Creation

• Files created in toolkit on FS
• Choose application wisely (tree read)?
• Freeze field placement
• File type
• File as blob
• File on App Server
• File NOT created when saving out!
• Created under 'data' in R18. Not good.
• We move all custom files, alter VOC.

10
Creation
11
Creation - Schema

• Elements added on DEL
• Add your key first good practice
• Avoid multipart keys in ETK
• Detail to DEP for details
• Internal size on DEL now important
• Note 'Display Size' on DEP too
• Can view on FIQ

12
Creation - Schema
13
Creation - Schema
14
Creation Computed Columns

• Computed columns defined on DCC
• Values calculated on the fly at runtime
• Often needed, but sometimes slow
• Stored computed column on DSCC
• Extended Link Attribute Entry DDLA
• Update Stored Computed Column USCC
• Don't have to use Datatel functionality

15
Creation Stored Computed Column

• Create data element to store value
• Write batch process to calculate and store value.
• Run batch process periodically (once/day) on
  changed records.
• If updating once/day or less won't work, consider
  a database trigger.
• Datatel doesn't support realtime (yet?)?

16
Creation Stored Computed Column

• Realtime with trigger is good!
• Database triggers can be invoked whenever a
  record is udpated or deleted
• Easy to recalculate stored computed column values
  anytime record is written
• Totally outside ETK.
• AE or MODIFY forces recalculation.

17
Creation Indexing

• Vastly improve queries
• Index data elements used often
• Indicies defined on FIDX (toolkit)?
• Indicies built on UTBI/UTBA
• Index anything you routinely query
• How often do we reindex?

18
Creation Indexing
19
Creation Indexing
20
Performance with Indexing

• H08.CR.DIV.DATA pure custom file
• Defined 20 indicies on it
• 45721 records in file
• SELECT H08.CR.DIV.DATA WITH XCDD.STU.PROGREP.STATU
  S'V'
• Indexed 50 milliseconds
• Non-indexed 1432 milliseconds

21
Triggers

• Piece of executed code
• Triggered by certain events
• Code is Unibasic subroutine/function
• Triggers invoked when record is
• Updated (written)?
• Deleted

22
Triggers

• Create subroutine with 5 arguments
• EXECSTAT (0,1,2)?
• DICT.FLAG (, DICT)?
• FILE (STUDENTS)?
• ID (0023450)?
• RECORD
• Compile and GLOBALLY catalog
• CREATE.TRIGGER FILENAME SUBNAME UPDATE

23
Triggers

• On ALL custom files, I use triggers for
• ADD/CHG OPER, DATE, TIME
• Why not Envision?
• We use triggers on some Datatel files too!
• We use WORKORDERS for Phys Plant and IT
• PPWP (Web Params) only allows single Plant Type
  and single email address for web work orders
• No problem worked around with a trigger

24
Triggers

• SUBROUTINE H08.UT.H08.CR.EVENTS(X.EXEC,X.DICT,X.FI
  LE,X.ID,X.REC)?
• X.EXEC2
• IF X.DICT THEN
• X.REClt4gtUPCASE(_at_LOGNAME)?
• X.REClt5gtDATE()?
• X.REClt6gtTIME()
• IF X.REClt1gt THEN
• X.REClt1gtX.REClt4gt
• X.REClt2gtX.REClt5gt
• X.REClt3gtX.REClt6gt
• END
• END
• RETURN
• BASIC CUSTOM.SOURCE H08.UT.H08.CR.EVENTS
• CATALOG CUSTOM.SOURCE H08.UT.H08.CR.EVENTS FORCE
• CREATE.TRIGGER H08.CR.EVENTS H08.UT.H08.CR.EVENTS
  UPDATE

25
Triggers

• Where can you find more info?
• Unidata Documentation (thin)
• Developing Unibasic Applications
• Chapter 4 Maintaining Data in Files
• Database triggers pp. 4-6

26
Summary

• Custom files are easy
• Keep your custom data separate
• Index things you query against often
• Think about stored computed columns for
  CPU-intensive computed columns
• Be consistent naming files/fields

27
Summary

• Jeff Butera
• Hampshire College
• jbutera_at_hampshire.edu
• http//jeff.hampshire.edu