Title: Congratulations! You're a Progress DBA! Now What? Speakers: Dan Foreman, Tom Bascom

1. Title: Congratulations!  You're a Progress DBA! Now What? Speakers: Dan Foreman, Tom Bascom “Congrats!” said the boss.  “You’re now the DBA.”  After a congratulatory handshake, he stops as he heads out of your office.  “Oh, by the way… since you’re the DBA, make sure that system stuff doesn’t bother me any more.”  Great…. But now what?  You know a thing or two about databases, but do you know enough?  What do you need to pay attention to?  What can you ignore?  What should you lose sleep over, and what should make you update your resume?  In this session, Dan Foreman and Tom Bascom, two of the nicest people you will ever meet, with more than half a century of Progress experience between them, will show you the way through the challenges that lie in front of you and help bring peace to your weekends and evenings.  Highlights: •             How do I make a backup? •             Starting and stopping a database. •             None of my SQL knowledgeworks... •             After-imaging, the dba'sbestfriend! •             About killing users... •             Where are my startup parameters and how do I change them? o             What definitely needs to be changed? o             What should I never touch? •             What can I (safely) ignore?  For how long? •             When should I raise the 4-alarm fire alarm? •             Where are the log files? •             Monitoring your database. •             Where can I learn more or get help? •             It's not just a database -- there is code too!

2. Congratulations!You’re Our New Progress DBA! Now What?!? Dan Foreman, Bravepoint danf@bravepoint.com Tom Bascom, White Star Software tom@wss.com

3. A Few Words about the Speaker • Dan Foreman – Progress User since 1984 • Author of Progress Books: • Progress Performance Tuning Guide • Progress Database Admin Guide • Progress System Tables Guide • Promon – debghbSoon to be released! • And Database Admin Tools: • ProMonitor/ProCheck/LockMon • Pro Dump&Load

4. Audience Survey • How many do at least some Progress DBA work? • How many have been doing that work for less than a year? • Is anyone on a version of Progress that is a single digit number…i.e. V9, V8, etc. • Who has their cell phone in noisemaking mode?

5. The Documentation

6. The Documentation • Online Manuals: http://communities.progress.com/pcom/docs/DOC-16074 • Knowledgebase: http://knowledgebase.progress.com/

7. Other Publications • BravePoint has several • www.bravepoint.com/products-publications.shtml • White Star too • wss.com/publications/default.html

8. Starting a Database

9. Starting a Database • PROSERVE • DBMAN • Exploder

10. PROSERVE Pro Con Command Line Must Provide Details like “dbname” Dinosaur stigma • Command Line • Easy to Script • Lots of Control • Repeatable • Sequence is guaranteed guaranteed $ proservedbname –n 500 –spin 3149 –B 1000000 –L 50000 $ probiwdbname $ proaiwdbname $ prowdogdbname $ proapwdbname

11. DBMAN Pro Con Command Line Uses conmgr.properties Admin Server must be running. Uses Java • Command Line • Works with conmgr.properties • Easy to Script • Starts Writers & Watchdog $ dbmandbName -start

12. Exploder Pro Con Graphical Interface Requires Admin Server Requires conmgr.properties Ugly Confusing Incomplete Can be Unreliable (Java) But if you like that sort of thing… • Graphical Interface?

13. Stopping a Database

14. Stopping a Database • PROSHUT • DBMAN • Exploder

15. Stopping a Database • PROSHUT • DBMAN • Exploder • Delete the .lk file… • Kill -9 • Reboot server… • Trip over plug…

16. PROSHUT Pro Con What’s a Command Line? • Command Line • Easy to Script • Lots of Control • Fast $ proshut –by dbname

17. DBMAN Pro Con Command Line Uses conmgr.properties Admin Server must be running • Command Line • Works with conmgr.properties • Easy to Script $ dbmandbName -stop

18. Exploder Pro Con Graphical Interface Requires Admin Server Requires conmgr.properties Ugly Confusing Incomplete Unreliable Easy to shutdown wrong DB

19. How Do I Make a Backup?

20. How Do I Make a Backup? • PROBKUP • OS Backup • 3rd Party Tools • VM or SAN “snapshots”

21. PROBKUP • Knows where all of the parts of the database are (even the parts in memory) • Can be executed with the database online • Can eliminate some empty space in the db • Can be used to change parts of the DB Structure • Can turn on after-imaging online if you forget… $ probkup online dbnamedbname.pbk -com

22. “All The Parts of the DB” # sports.st # b /bi/sports.b1 # d "Schema Area":6,32;1 /db/sports.d1 d "Info Area":7,32;1 /db/sports_7.d1 d "Customer/Order Area":8,32;8 /db/sports_8.d1 d "Primary Index Area":9,1;8 /db/sports_9.d1 d "Customer Index Area":10,1;64 /db2/sports_10.d1 d "Order Index Area":11,32;64 /db/sports_11.d1 # a /ai/sports.a1 a /ai/sports.a2 a /ai/sports.a3 a /ai/sports.a4

23. OS Backup & 3rd Party Tools • The database must be offline or in a “quiescent state” • You are responsible for making certain of the above • You must also ensure that all parts of the database are included in the backup

24. VM or SAN “Snapshots” • The snapshot must be a consistent, point in time image of the entire database • The database will be in a crashed state when recovered • Not all snapshot products are created equally! • Progress has tested and certified EMC’s SRDF • Users report success with VMWare’s Vmotion (but beware of possible performance issues)

25. Other Stuff to Backup • $DLC/properties • DB Log Files • Admin Server, AppServer, WebSpeed Logs • Structure (.st) Files • Parameter (.pf) Files • $DLC/startup.pf • repl.properties etc.

26. None of My SQL Knowledge Works

27. None of My SQL Works!?! • Progress is NOT SQL • Period. Full Stop. End of Story. • Thinking about Progress like SQL will only lead to pain and agony

28. SQL-92 Interface • SQLEXP • ODBC/JDBC • DBTOOL • UPDATE STATISTICS • SQLDUMP

29. ODBC/JDBC

30. DBTOOL http://knowledgebase.progress.com/articles/Article/P24496 $ dbtool sports DATABASE TOOLS MENU - 10.2B ------------------------------------------- 1. SQL Width & Date Scan w/Report Option 2. SQL Width Scan w/Fix Option 3. Record Validation 4. Record Version Validation 5. Read or Validate Database Block(s) 6. Record Fixup 7. Schema Validation 9. Enable/Disable File Logging Q. Quit Choice:

31. SQLEXP • Run SQL scripts • GRANT and REVOKE perms (save the scripts because the permissions are not included in a dump & load!) $DLC/bin/sqlexp -user userName -password passWord\ -dbdnName -S servicePort\ –infilescript.sql –outfilesqlexp.log

32. UPDATE STATISTICS /* genUpdateSQL.p * * mprodbName–p genUpdateSQL.p-param "tmp/updSQLstats.sql" * * sqlexp -user userName -password passWord\ * -dbdnName -S servicePort\ * -infiletmp/updSQLstats.sql -outfiletmp/updSQLtats.log */ output to value("updSQLstats.sql" ). for each _file no-lock where _hidden = no: put unformatted "UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ” “ALL COLUMN STATISTICS FOR PUB." '"' _file._file-name '"' ";" skip . put unformatted "commit work;" skip. end. output close.

33. A Few Words About “killing” Users

34. About “killing” Users… • If you are on UNIX: • “kill -9” is dangerous! • “kill -9” does not “always work”. • “kill -1” should be safe and it should work. • proshutdbname –C disconnect usr#

35. About “killing” Users… • If you are on UNIX: • “kill -9” is dangerous! • “kill -9” does not “always work”. • “kill -1” should be safe and it should work. • proshutdbname –C disconnect usr# • Windows: • We’re sorry.

36. After-Imaging – The DBA’s Best Friend

37. After-Imaging • Roll-forward recovery • A journal of transaction “notes” that can be replayed against a baseline backup to restore a database to the last completed transaction or a point in time or a specific transaction number • This is the same concept that some other databases refer to as the “redo log”

38. Why do I need after-imaging? • Protection from media loss -- such as bad tapes, a crashed disk, a destroyed data center or stolen servers…

39. Why else do I need after-imaging? • Protection from human errors: • Human error is at least as big a risk as hardware problems $ cd /db $ rm * for each order: delivered = yes. end. for each customer: delete customer. end. $ vi dbname.db … :x

40. After-Imaging Best Practices #1 • Enable after-imaging on all updateable databases • Place after-image extents on separate disks from data extents • Use 8 to 16 variable extents with “large files” enabled • Run an After Image Writer (AIW) • Switch extents as often as the business needs you to • Use the Internal AI Sequence number when naming archived AI files • Use the AIM (AIManager)

41. After-Imaging Best Practices #2 • Copy archived logs to an external location ASAP • Verify your process by continuously rolling forward • Monitor both your “empty” and “full” extents • Keep at least 30+ days of archived after-image logs • Establish a dedicated backup and recovery directory

42. Startup Parameters

43. Startup Parameters • Where to Find Them? • Which ones should be changed? • Which ones should be left alone!

44. Where to Find Them? • On the command line • In a script • In “.pf” files • In $DLC/properties/conmgr.properties • Verify by examining the “dbname.lg” file. • Search for “(333)” and examine the next 50-75 lines…

45. Tune in Yesterday Morning! • Which ones should be changed? • Which ones should be left alone! • Which ones are crucial? • Before Image Size Threshold (-bithold)

46. Important Configuration Options

47. Important Configuration Options • BI Cluster Size • Block Sizes • Storage Areas • Rows Per Block (RPB)

48. BI Cluster Size • The Default varies with release, 512KB is current • For “Workgroup” Licenses smaller is better • More, but smaller, delays • For “Enterprise” Licenses bigger is better • May increase crash recovery time a bit • But reduces the risk of painful delays during heavy processing • 32768 KB is “a good start” • proutildbname –C truncate bi –bi 32768

49. Block Sizes • DB Block (Must Dump & Load to Change) • 4KB or 8KB • NOT 1KB or 2KB • BI Block • 16KB • proutildbname –C truncate bi –biblocksize 16 • AI Block • 16KB • rfutildbname –C aimage truncate –ai 16 • AI must be disabled so do it ‘early’

50. Storage Areas • Type 2 Storage Areas are the foundation for all advanced features of the OpenEdgedatabase • Type 2 areas have Data Cluster Sizes (DCS) of 8, 64 or 512 • Data blocks in Type 2 areas contain data from just one table • Use Many (Type 2) Storage Areas • Do NOT assign tables to areas based on “function” • Instead group objects by common “technical attributes” (Rows Per Block, size, activity level) • Put Large Objects (LOBs) in separate Areas • Do NOT store data, indexes or LOBs in the “Schema Area”