270 likes | 415 Views
Jeff Butera Hampshire College jbutera@hampshire.edu. DUG Session 4488 Creating Custom Files. Monday, March 10, 2008 4:05PM Hoover. Outline. Custom files: To have or not to have? Best Practices Creation, schema population Indexing Triggers WARNING: Unidata perspective!
E N D
Jeff Butera Hampshire College jbutera@hampshire.edu DUG Session 4488Creating Custom Files Monday, March 10, 2008 4:05PM Hoover
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?
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!
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
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
Best Practices Example: STUDENTS Field prefix: STU. Computed Column prefix: XSTU. Custom cofile: H08.STUDENTS Field prefix: XHSTU.
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
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
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.
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
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
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?)
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.
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?
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.STATUS='V' Indexed: 50 milliseconds Non-indexed: 1432 milliseconds
Triggers Piece of executed code Triggered by certain events Code is Unibasic subroutine/function Triggers invoked when record is Updated (written) Deleted
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
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
Triggers SUBROUTINE H08.UT.H08.CR.EVENTS(X.EXEC,X.DICT,X.FILE,X.ID,X.REC) X.EXEC=2 IF X.DICT=’’ THEN X.REC<4>=UPCASE(@LOGNAME) X.REC<5>=DATE() X.REC<6>=TIME() IF X.REC<1>=’’ THEN X.REC<1>=X.REC<4> X.REC<2>=X.REC<5> X.REC<3>=X.REC<6> 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
Triggers Where can you find more info? Unidata Documentation (thin): Developing Unibasic Applications Chapter 4 – Maintaining Data in Files Database triggers pp. 4-6
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
Summary Jeff Butera Hampshire College jbutera@hampshire.edu http://jeff.hampshire.edu