INGRES Pre-Training Seminar

1. INGRES Pre-Training Seminar Doug Clough SYNERGETICS Engineered Systems 27 May 2009

2. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

3. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

4. Information System Information

5. Information System Purpose Information

6. Information System Purpose Environment Information

7. Information System Purpose Environment Information Data

8. Information System Purpose Environment Information Context Data

9. Information System Purpose Environment Information Context Data

10. Information System Purpose Environment Meaning Logic Information Context Data

11. Information System Purpose Environment Meaning Logic Information Context Data “red”

12. Information System Purpose Environment Meaning Logic Information Context Data Harvesting tomatoes “red”

13. Information System Purpose Environment Meaning Logic Information Context Data It’s ripe. Go ahead and pick it. Harvesting tomatoes “red”

14. Information System Purpose Environment Meaning Logic Information Context Data “red”

15. Information System Purpose Environment Meaning Logic Information Context Data Driving in traffic “red”

16. Information System Purpose Environment Meaning Logic Information Context Data Driving in traffic STOP!! “red”

17. Information System Purpose Environment Meaning Logic Information Context Data

18. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

19. Information System Purpose Environment Meaning Logic Information Context Data Relational Database

20. Information System Purpose Environment Meaning Logic Information Context Data User Interface Relational Database

21. Information System Purpose Environment Meaning Logic Information Context Data User Interface Relational Database

22. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database

23. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database Deploy & Operate

24. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database Deploy & Operate

25. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database Deploy & Operate IngresDBM

26. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database Deploy & Operate IngresDBM IngresSQL

27. Information System Design & Implement Purpose Environment Meaning Logic Information Context Data User Interface Relational Database Deploy & Operate OpenRoad Performance IngresDBM IngresSQL

28. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

29. INGRES Configuration Overview • CLIENT installation on bay & jack (pitblade) • FULL installation on blueback (sockeye) • Multiple servers connect to PTAGIS3 … • PTAGIS3_Loader (2 instances) • PTAGIS3_User (1 instance) • Must configure … • fast_commit = ON • shared_cache = ON

30. Customized Backup Using ZFS • ZFS snapshot taken ‘instantaneously’ • Checkpoint procedure reads the snapshot • Source in /usr/ingres/snapckp • Shell script named snapckp • Developed by Simon Maybury (CA) • Adapted for use with ZFS by Todd & Doug • Customized checkpoint template file … • $II_SYSTEM/ingres/files/cktmpl.bcv

31. Use of Rules, Procedures, Events • IDL and FDVL use ‘called procedures’ • IDL – load_int_data_cdbp • Serves as ‘transaction driver’ • Performs aggregation ‘on the fly’ • FDVL – e.g. fd_tag_tag_cdbp • Compares ‘old’ and ‘new’ key values to perform either insert or update • TDS uses ‘rule-fired procs’ and ‘events’ • Procedure executes when conditions are met • TDS_EventMonitor ‘registers’ for ‘events’

32. Generating Optimizer Statistics • As ‘ptagdev’ on ‘sockeye’ …> dba> cd Statistics> vi ToDo_Blueback_Statistics.txt • Follow the instructions; log your work in this file!! • DBA_OfflineStats.pl pittag ptagis3 <Arg> • Wrapper around several INGRES utilities • <Arg> is GEN, APP, SET (Sets ‘complete’ flag) • DBA_Sysmod.pl pittag ptagis3 • Wrapper around INGRES sysmod • Must run after applying new statistics • Writes to $JOB_LOG and its own log file

33. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

34. INGRES Pre-Training Seminar • Basic concepts • INGRES Curriculum • Specific to PTAGIS • INGRES Configuration Overview • Customized Backup Using ZFS • Use of Rules, Procedures, Events • Generating Optimizer Statistics • Q & A • Homework!!

35. Homework!! • Please read …Information Systems Development in the RDBMS EnvironmentD.P. Clough, April 1993

36. Have a Great Time In Class!!