The Progress V9 Database

1. The Progress V9 Database John Harlow BravePoint, Inc. Email: jharlow@BravePoint.com

2. Agenda • Setting the Stage • Storage Areas • Performance • Utilities (new and improved) • SQL Engine (new) • Schema (Metaschema and VST Schema) • Size Limits • Miscellaneous

3. Setting the Stage • John Harlow - Instructor, Consultant • Progress User since 1984 (V2.1) • Presenter at USA Progress Users Conferences

4. Setting the Stage: Me • Scott M. Dulecki • Presenter at Explore! and regional user groups • Board Member, Midwest MFG/PRO Users Group • President, Michigan Progress Users Group • PEG member 1998061901 • Author of: • Safe Haven: Archiving in MFG/PRO

5. Introduction • DBA Tools • Progress Performance Tuning Guide (July 2002) • Progress Database Admin Guide (July 2002) • Virtual System Tables (July 2002) • V9 Database Administration (July 2002) • ProMonitor - database monitoring tool • WebProMonitor – Web Frontend for ProMonitor • Pro D&L - Accelerated Dump/Load Utility • DBA Resource Kit • STGEN - V8 to V9 Conversion Tool • All Books are Now Available Online

6. BravePoint, Inc. • Formerly United Systems, Inc (Feb 2001) • Progress Services Provider • Training (Progress, QAD, Linux, and more) • Performance Tuning & DB Administration • Internet, Java, & WebSpeed Interfaces • Custom Programming • Progress Experience • 125+ Employees • 3 members have used Progress since 1984 • Our most junior instructor has 8 years experience

7. Who Are You? • Are you currently using the V9 Database in Production? • V9.1A, V9.1B, V9.1C, V9.1D • If not when?

8. What’s New in the V9 Database • Storage Areas • Performance • Utilities (new and improved) • SQL Engine (new) • Schema (Metaschema and VST Schema) • Size Limits • Miscellaneous

9. Storage Areas - What are They • Similar concept to a Version 8 Multi-Volume database but there can be one or more Areas in a database or a "family" of V8 Databases under one "head" • Also similar to Oracle/DB2 table spaces • Logical group of database Objects (i.e. tables and indexes) • Physical group of storage units (one or more extents on disk)

10. Storage Areas - Benefits • Records per Block (1 to 256) set by Area means more efficient use of space in the Database (more details later) • Multi-database transactions may be subject to logical data corruption if 2 Phase Commit is not enabled; Storage Areas make it easier to keep all data in one database (no more split schema which is an obsolete technique) • It may be possible to improve performance by using Areas to distribute tables with heavy I/O volume over multiple disks

11. Storage Areas - Benefits • Areas make it possible to keep static (infrequently changed) and dynamic (heavy create and delete activity and subject to scattering) data separated • Doing a dump/load on a particular table (or tables) now has real value

12. Storage Area - Disadvantages • Areas make DB administration more complicated • There are NO Single Volume Databases • Even the empty DBs are Multi-Volume • probkup, procopy are Area ‘Sensitive’ • Multiple High Water Marks (HWM) to Monitor (since there can be a variable extent for each Area) - there are 36 Areas in the MFG/PRO eB Database Structure File

13. Storage Area - Disadvantages • A V9 DB will probably be larger than V8 because of the additional space allocated for each Area • Still can't add new Extents or Areas On-Line • promon does not have Area HWM information

14. Storage Areas - Physical View Index E Table E Index C-1 Index D-1 Index E-1 Index A-1 Index B-1 Table D Table B Table C Table A Area 6 Area 7 Area 8 Area 51 Extent Extent Extent Extent Extent Extent Extent Extent Extent Extent Disk Storage - Files or Raw Partitions

15. Storage Areas • Control Area (.db File) • In V8 (and before) contained Extent path names • In V9 contains _Area, _AreaExtent Tables • These tables can be queried with the 4GL • Primary Recovery Area (V8 BI Extents) • 2GB Limit is Gone! (it’s just much higher) • After-Image Areas (V8 AI Extents) • User Defined Data Areas (V8 RM & IX Blocks) • Schema Area (No V8 comparison exists)

16. Storage Areas • Area 6 is the Schema Area. It contains: • The Database Master block • Object to Area mapping information • Sequence value block • Schema tables, indexes, and SQL views • It is the “default” Area • May contain user data but not recommended • If no other Area is created, Area 6 will contain all data (as is the case after conversion from V8 with proutil conv89) • V9.1D - mvsch option on proutil

17. Storage Areas • The Physical Area layout is described in the Structure (.st) file • Areas can have many extents • An Extent is a separate file just like V8 • Only the last extent of an area may be variable size • Each Area grows independently • Some options use the .st file to create and update DBs (prostrct, prorest, procopy) • Area contents (what tables goes to what Area) are defined in the Dictionary and/or Data Definitions (.df) - example on next page

18. Storage Areas

19. Storage Areas • A Table or Index Object can be placed in any data Area • Data Areas can contain indexes and/or tables • An Area can be dedicated to one object • Objects cannot span Areas • Simpler is better - do not go to extremes • My recommendation: 19 data Areas maximum; Why?

21. Records per Block • Records per block may be different in each Area: • 1, 2, 4, 8, 16, 32, 64, 128, or 256 • The DBKey/Recid/Rowid format varies but is transparent to applications • Benefits • Improved storage utilization • Allows use of large block size (8k) for better performance • Disadvantage • Higher Rec/Blk = Lower Maximum Area Size

22. Preparing for Storage Areas • V8.3 Table and Index I/O Monitoring • -tablebase & -tablelimit • -indexbase & -indexlimit • _tablestat & _indexstat VSTs • Identify Static vs Dynamic vs Historical Data • Identify Large Tables • Identify tables that are frequently and easily scattered • Identify tables with large record sizes • Identify unused indexes

23. Options to Convert to V9 • proutil conv89 • Usually takes less than 5 minutes • All Data & Indexes are stored in the Schema Area • Will NOT take advantage of Storage Areas • Moving Tables and Indexes still leaves a Large Schema Area (V9.1D proutil/mvsch) • Dump/Load • Will take ……longer than conv89 • Can take full advantage of Areas through easy modifications to the .df file

24. V9 Structure (.st) Files # Schema Area # Records/Block:64 # Located in the /db directory d “schema area”,64 /db # Data Area # Area Name: data1 Area Number: 10 # Records/Block:128 d “data1”:10,128 /db1/data_10.d1 f 100000 d “data1”:10,128 /db2/data_10.d2 f 100000 d “data1”:10,128 /db3/data_10.d3 # Index Area #Area Name:index1Area Number: 20 #Records/Block:Not given because intended for index blocks d “index1”:20 /db4/ f 100000 d “index1”:20 /db4/ # Variable size BI extent b /bi/prod.b1 # After Image Extents a /ai/data.a1 f 100000 a /ai/data.a2 f 100000 a /ai/data.a3 f 100000

25. V9 Utilities - Table Move • proutil <db> -C tablemove <table> <area> • Rebuilds the Indexes during the move since Recids are no longer unique to a DB (but are unique to an Area) • Off-line as well as on-line • On-line: Will Exclusive Lock the entire Table for the duration of the move • One Move = One Trx = Bigger BI/AI Files • Monitor the process with the _UserStatus VST; the Table Lock can be monitored with promon or the _Lock VST

26. V9 Utilities - Index Move • proutil <db> -C indexmove <Index def> <area> • Index def: <owner>.<table>.<index> • Run off-line or on-line • On-line: • Only read operations will be allowed • The table will be SHARE locked • Benefits: flexibility and availability • Monitor the process with the _UserStatus VST

27. V9 Utilities - Index Compact • proutil <db> -C indexcompact<owner>.<tablename>.<indexname> • Combines partially filled adjacent index blocks • Runs off-line or on-line • Index Fix + Index Compact = Index Rebuild • Benefits: • Performance - fewer disk i/o’s • Reduces index size • Availability - index not locked • DB Corrupting Bug thru V9.1D (no patches)

28. Enhancements to Utilities • Most utilities are now ‘Area Aware’ • Example: proutil idxbuild will only scan the blocks in the Area where the table exists • proutil dbanalys/tabanalys/ixanalys can now report individual Areas (this is undocumented) • proutil <db> -C dbanalys <area name>

29. Enhancements to Utilities • procopy & probkup • Target Area Numbers must Match • Area Names don’t need to Match • Target Records per Block must Match • prorest - the # of Areas must match • Uses .st File (if one exists) in Target Directory

30. Enhancements to Utilities • proutil truncate area • If no Area is specified, all Areas that have no Storage Objects have the HWM moved to ‘ground zero’ • If an Area name is specified, the data is in the Area is removed (after confirmation) • In each case there is no impact on the physical extents

31. V9 Performance Improvements • Deferred Transaction Start • “transaction-begin” BI note write is deferred • No “transaction-begin” or “transaction-end” notes are written for “read only” transactions • This optimization is invisible to applications (you can’t forget the 4gl transaction scoping rules) • Benefits: • BI clusters may be reused sooner; Smaller BI log • Improved performance for “empty” transactions • ODBC Connections & many 4gl programs won’t cause an open transaction

32. V9 Read Buffers • Processes such as large reports reports can evict and replace many shared buffers • Read buffers might be able prevent this • Read buffers are taken from the shared pool • Read buffers remain accessible to all users • When read buffers are enabled: • Read operations will use the read buffers • Write operations will use the public -B pool

33. V9 Read Buffers • New client startup parameter -Bp n enables read buffers per user • # of buffers can be changed at runtime: do for _myconnection transaction : find first _MyConnection. _Myconn-NumSeqBuffers = 10. end. run bad-report.p. do for _myconnection transaction : find first _MyConnection. _Myconn-NumSeqBuffers = 0. end.

34. V9 Read Buffers • Per Client maximum is 64 (until V9.1C) • if more are requested, only 64 are granted • Total read buffers of all users cannot exceed 25% of -B • when this limit is reached further requests are denied and the public buffers will be used • V9.1C -Bpmax Broker parameter • Problems: • How to Allocate? VERY difficult question • No way to monitor how many buffers in use and how they are being used

35. New Virtual System Tables • VSTs are Automatically Enabled in all V9 Databases • New VSTs • _AreaStatus HWM Info • _MyConnection Info about my connection • _StatBase Reset table/index I/O limits • _UserStatus Monitor some online utilities

36. V9 Size Limits • Database size: BIG! • Primary Recovery Area (BI) size: 16-32TB • Maximum Number of Areas: 1,000 • Data Area size: Varies • 16TB (1 row per block, 8k block size) • 8GB (256 rows per block, 1k block size) • Data Area block size: 8,192 bytes • Extent size: Still 2 gigabytes until V9.1C • proutil <db> -C EnableLargeFiles

37. V9 Size Limits • Record size: 32,000 bytes (unchanged for now) • Records per Area/Table: 2,147,483,647 • Depends on Block Size & Record/Block Value • Number of Records per block: 256 • Number of Indexes: 32,000 • Number Sequences: 2,000 (with 8k blocks) • Increased from 100 • Number of concurrent users: 10,000 • Number of concurrent transactions: 10,000

38. What V9 is NOT • Multi-Threaded BI Access • Buffer Cache (-B) per Area • 2GB File Size Limit Removed • Temp Files • Dump (.d) files • DB, BI, and AI Extents (until V9.1C) • ‘Pure’ 24 x 7 Operation • Schema Changes • Adding new Extents • Reactivating indexes after proutil idxfix

39. For More Information • BravePoint’s V9 Database Administration Jumpstart Class is designed for V6-V8 DBAs that want find out everything they need to know for moving to V9 • Dan Foreman’s V9 Database Administration Jumpstart publication • Dan Foreman • danf@prodb.com