1 / 27

DUG Session 4488 Creating Custom Files

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!

lydia
Download Presentation

DUG Session 4488 Creating Custom Files

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Jeff Butera Hampshire College jbutera@hampshire.edu DUG Session 4488Creating Custom Files Monday, March 10, 2008 4:05PM 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.STATUS='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.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

  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@hampshire.edu http://jeff.hampshire.edu

More Related