Master of Parameters Stupid Title, Right? What would you call it?

1. Master of ParametersStupid Title, Right? What would you call it? Presented by: Dan Foreman

2. Dan Foreman • Progress User since 1984 • Author of several Progress related Publications • Progress Performance Tuning Guide • Progress Database Administration Guide • Progress System Tables • Author of several Progress DBA Tools • ProMonitor • Pro Dump&Load • Balanced Benchmark • Getting closer to retirement! I probably have one or two birthdays left that have a zero in the number

3. Dan Foreman – Part 2 • Interests • Playing Basketball (risky for an old guy) • Cycling (my trike is made in the UK) • Backcountry skiing, also called randonee or ski mountaineering • Japan (日本) – I used to live there • World Travel

4. Progress History Test – Unix Platforms • Plexus • Arete / Arix • Convergent Technology • Fortune • Sanyo Icon • DEC (VMS & Ultrix) • NCR • AT&T (Unix PC) • Harris • ICL • NEC • Olivetti • Siemens Nixdorf • Prime • Pyramid • Sequent • Sun • Unisys • Honeywell • Silicon Graphics • Wicat • Apollo • MIPS

5. Obsolete OS formerly supported by Progress • BSD • Ultrix • BTOS/CTOS • Xenix • OS/1 • Netware • OS/400 • Apple/UX • SunOS • MS-DOS • HP9000

6. Agenda • Why am I doing this? • Parameter Classes • New Stuff (V12 & V11.*) • Important Stuff (wide range of versions) • Underutilized Parameters • Undocumented (or poorly documented ) Parameters • Obsolete Parameters • Parameter Settings • Parameter Trivia & Humor • Resources • Questions

7. Why am I doing this? • I enjoy finding a new topic for a presentation • However that entails some risk since I don’t know how interesting the topic will be to everyone • Therefore you are Beta Testers for this session because it be presented again @ PUG Challenge EMEA in Barcelona later this month • Technical reasons: • Parameters can reveal features that you might not previously have been aware of • There are some useful undocumented parameters • Awareness of new parameters/features may be an incentive to upgrade your version Progress

8. Important Parameters • Ultimate! • -pf Parameter File • Client • -rereadnolock • -rand because the Progress RANDOM Function isn’t Random • Database • -omsize • -bithold (and maybe –bistall)

9. Parameter Use Classes • Database Startup • 4GL Client Startup • SQL • Utilities • Some other Categories • Security • Remote Client Configuration & Connections • Log File Maintenance

10. Trivia & Humor • The –z* “family parameters was possibly named by a former Progress Developer named Amnon • Amnon is from Israel & commanded a tank during the 6-day war • He said that this set of parameters is (in a thick middle east accent) “zecret” so they all start with “z” • Normally I’d put this slide later in the session but it’s important to know the meaning of “z” when we start seeing some members of the z-family

11. Underutilized Parameters • -B2 – Alternate Buffer Cache • The ABC allows you to actually “lock” a table(s) and/or indexes into a Buffer Cache in Shared Memory • Assuming the table (or index) “fits” in the ABC, this would totally eliminate: • LRU Latch Activity • Database Reads (except to populate the ABC) • Quick Trick to pre-populate the ABC • proutil <db> -C dbanalys <area-name> • It is usually a non-trivial amount of work to analyze & implement an ABC but the payoff can be tremendous

12. New Parameters in V12.0 • To facilitate Large Object (LOB) monitoring • -baselob Starting LOB# for statistics range • -lobrangesize Number of LOBs monitored • -baseuserlob Starting LOB# per Client • -userlobrangesize Number of lobs per Client • -ttbaselob Starting LOB# for statistics range • -ttlobrangesize Number of LOBs per Client • -picanap <milliseconds> Maximum nap time when blocked to access PICA queue; Default: 1 ms

13. New Parameters in V12.0 • -retryAfterCatch • Retry After Error Catch • Client startup parameter • Reverts to the old behavior after an error is caught in a FOR EACH block. • Since V12.0 the default action after an error is caught in a FOR EACH block will be NEXT. • The original behavior was RETRY.

14. New Parameters in V12.0 • -ssj[0|1] Database server side join support [Login Broker] • 0 — Server-side JOIN processing is disabled for this server. Default for a non-threaded DB server. • 1 — Server-side JOIN processing is enabled for this server. Default for a threaded DB server. • Server-side JOIN is only enabled for certain queries if the tables to be joined are in the same logical database and there are ten or fewer tables. • For 12.0 Server-side JOIN is limited to queries that use NO-LOCK. • -threadedServer[0|1] Threaded Server [Login Broker] • 0 indicates that servers spawned by the broker are not threaded. • 1 indicates that the servers spawned by the broker are threaded. Servers spawned by this database broker service remote client requests using individual threads, one thread per remote connection. The default is 1. • -threadedServerStack[Kbytes] Threaded Server Stack [Login Broker] • Specifies the size of a stack allocated for each thread spawned by a threaded server.

15. New Parameters in V12.0 • Autonomous Update Statistics (AUS) • A way to keep SQL Query Optimizer Statistics up-to-date automatically • https://www.progress.com/papers/whitepaper-autonomous-update-statistics • -SQLAutoStats • -SQLAutoStatsThreshold

16. New V11.7 Parameters (since 11.7.1) • DB Log Maintenance • These parameters give us a Progress supported method to archive & truncate the DB log without writing scripts • -lgArchiveDir • -lgArchiveEnable • -lgTruncateFrequ • -lgTruncateSize • -lgTruncateTime More log related parameters on the next slide.

17. New V11.7 Parameters (since 11.7.1) • -limitLgPayload • DB Startup Parameter • reduce the payload, by stripping out certain parts of the .lg entry • -omitLgMsgs • DB Startup Parameter • Ability to filter out, i.e. don’t write the DB .lg, to up to 16 log file message numbers

18. New V11.7 Parameters (since 11.7.1) • -hashLatchFactor • V11.7.3 • Mentioned by Tom Bascom in his Latch Contention presentation

19. DB Parms Between V11.6.2 and V11.7.1 • These parameters give us the ability to limit the amount of shared memory consumed at DB startup • -baseuserindex • -baseusertable • -userindexrangesize • -usertablerangesize • -cdcsize • -numCheckpointStats • -stslogginglevel • -usernotifytime • Database Client Notification

20. DB Parms Between V11.6.2 and V11.7.1 • Diagnostic Family; used to create a log separate from the DB for capturing select DB problem events • Valid triggering events are: • locktable • bithold • Syserror • Parameters • -diagDir • -diagEvent • -diagEvtLevel • -diagFS • -diagFormat • -diagPause • -diagPrefix

21. Client ParmsBetween V11.6.2 and V11.7.1 • -ttbaselob • -ttlobrangesize • -emptyFields • -lgTruncateSize • -noUnicodePrinters • -nodupttidxerror • -retryAfterCatch • -ssj • -zbdumpOE11enc • -zm2

22. Between V11.5.0 and V11.6.2 • Related to Security • -nohostverify SSL • -nosessioncache SSL • -nosessionreuse SSL • -secsize • Security Cache Size (Authentication Gateway)

23. V11.6.0 • -SQLTruncateTooLarge {ON|ALL|OFF|OUTPUT} • Authorized Data Truncation (ADT) • Client Connection, Database Startup Parameter • OUTPUT - Set this parameter to OUTPUT to truncate the data returned to the user when its size exceeds the column size. All SQL operations such as comparison, sorting use actual data and only the output is truncated. • ON/ALL - Set this parameter to ON or ALL to perform SQL operations on truncated data. Also the data returned to the user is also truncated when its size exceeds the column ize. • OFF - When the parameter value is set to OFF, data will not be truncated and an error is returned. • -SQLWidthUpdate ON Autonomous Schema Update • Database Startup Parameter • Set the value of this parameter as ON, during startup, to resolve the SQL width problem by updating the SQL column width in schema automatically when Authorized Data Truncation (ADT) occurs for data in a column. Once the value of the parameter is set, it is remembered for the lifetime of the server and for all connections. If this parameter is set to ON during startup, the Authorized Data Truncation (_SQLTruncateTooLarge) parameter is also enabled irrespective of whether ADT was enabled during startup unless specified otherwise using connection URL.

24. V11.5 • _DbParams Virtual System Table • Has info about all DB startup options • Is also a mechanism that can be used to update many startup parameters online (and more are being added in each release) • _DbParams-Name • _DbParams-Value • _DbParams-dtype • _DbParams-Desc • _DbParams-Msg-Num • _DbParams-Is-Default • _DbParams-Is-Modifiable

25. Undocumented (and Useful) Parameters • For this presentation “Undocumented” doesn’t mean that you can’t find info in a Kbase article or some other source • It means that the parameter doesn’t appear in the Progress Documentation titled Startup Command and Parameter Reference

26. Undocumented (and Useful) Parameters • -zprofile(V8.2) • -profile (V9 onward) • Application Profiler • 4GL Client Parameter

27. Undocumented (and Useful) Parameters • -zqil & -zqilv • QIL = Query Info Log (V = Verbose) • 4GL Client Parameter • The one thing this parameter does that can’t be done is show how many fields in a bracketed query are actually bracketed. • Broken in some odd way in V11.4 • Full details & examples can be found in Dan’s Progress Performance Tuning Guide

28. Undocumented (and Useful) Parameters • -zextendSyncIO • proutil -C bigrow, Unix only • Since 11.3.0 • BI extents written are flushed from the file system cache with an fdatasync() call and buffered I/O is only used again once Clusters growth/formatting is complete. • This rework also extends to the runtime database when, for example, a long running transaction necessitates the formatting of new BI Clusters added to the BI Ring. • The -zextendSyncIO parameter restores the original behavior: bigrow uses a buffered file handle to grow the BI file and the buffered file handle does unbuffered I/O • Very important if you rely on the Furgal Storage Device Test

29. Typo in the Formal Documentaiton << haha • -direction (-directio)

30. Swiss Army Parameters • -F has 3 different uses in varying and increasing degrees of danger • Start promon “lock free” (promon –F) • Emergency Shutdown (proshut –F) • Force Access (proutil truncate bi –F)

31. Trivia & Humor • My favorite candidate for the most strangely named parameter is: -norecover It’s can be used on probkup to backup the DB without initiating crash recovery… • So would you trust a backup that was run with something like –norecover ? • BTW are there any DBAs here who don’t when this parameter might be helpful?

32. Longest Parameter Names (as of V12.0) • Longest Parameter Names • -nsClientPicklistExpiration(26) Winner! (but no info in docs) • -strictEntityResolution(22) XML Parser • -SQLAutoStatsThreshold(21) (but no info in docs) • -entityExpansionLimit(20) XML Parser • -SOAPEndpointPassword(20) • -SQLTruncateTooLarge(19) • -recspacesearchdepth(19) • -threadedServerStack(19) • -lgTruncateFrequency(19) • -compileroptionsfile(19)

33. Obsolete Parameters • -N Network Protocol (IPX, NetBUI, DecNet, etc.) • -XRO Read Only Target for OpenEdge Replication

34. Parameter Values & Arguments in V12.1 • Parameter Old New value • -aibufs 20 64 • -bibufs 20 64 • -lruskips 0 100 • -lru2skips 0 100 • -pica 64K 128K • -prefetchDelay 0 1 • -prefetchFactor 0 100 • -prefetchNumRecs 16 64 • -prefetchPriority 0 1 • -Mm 1024 8192 • -Mxs 16K 32K

35. Parameter Values& Arguments • -tablebase • Can be set to a negative value to report on System Tables (not VSTs) in addition to application tables • Sometimes System Tables have unusual or unexpected activity but it would be impossible to detect/monitor that activity with the _tablestat VST without a negative starting value

36. Parameter Values & Arguments • -minport • -maxport • Default values can be too “narrow” when there are lots of DB & Login Brokers supporting Remote Clients

37. I saved the best for last George Potemkin, a friend and true Progress expert from Russia has maintained an excellent list of Progress parameters that can be found at this URL community.progress.com/community_groups/openedge_rdbms/f/18/t/58282

38. Anything You’d Like to Add to the Discussion?

39. Thank You! Questions? • dforeman@bravepoint.com • dforeman@progress.com • danf@prodb.com / dan@wss.com • Mobile: +1 541 908 3437 • UTC/GMT -8 (when I’m home) • Please thank the conference organizers for their hard work in putting together an excellent conference