New Features in OpenEdge 10.1 Database Dan Foreman Progress Expert

BravePoint 2008 Introduction- Dan Foreman Progress user since 1984 (V2.1) Guest speaker at every USA Progress Users Conference from and Also a frequent visitor to

BravePoint Russia Classes & Presentations Fall 1992 St. Petersburg Winter 1994 St. Petersburg Spring 2002 Advanced DBA Class Fall 2002 Locking/Transaction Class & Conference Fall 2005 St. Petersburg DBA Class & Conference Fall 2006 V10 Jumpstart Class & Conference March 2007 Consulting in November 2008 Extreme DBA Class

BravePoint Introduction- Dan Foreman Author of: Progress Performance Tuning Guide Progress Database Administration Guide Progress Virtual System Tables Progress V10 Database Admin Jumpstart ProMonitor – DB Monitoring Utility ProD&L - Dump/Load with Short Downtime DBA Resource Kit

BravePoint All publications, products, and my services are available from Progress Technologies Information from this presentation can be found in detail in V10 Database Administration Jumpstart

BravePoint Please note that I say V10 instead of the politically correct OpenEdge I also say 4GL not ABL Because of time limits this presentation doesnt include every new feature or deep details; for that please look at my books Before we start I need to introduce someone else

My Friend, Translator, & World Class Progress Technical Support BravePoint At work September 2004 Looks like a DB crash? Still at work November 2005

Still Working Hard on the Paris Metro BravePoint June 2008

Russia DBA Class BravePoint

And after class BravePoint

BravePoint V10.1A Feature Summary Key Event Logging After Image Management Daemon Auditing New Online Options Changes to Default Startup Options New proutil options Dump/Load Enhancements New Database Log File Format

BravePoint Key Event Logging Saves selected Database events in an internal Database table More reliable than the database log which is not backed up by probkup Potential uses: To assist technical support with troubleshooting A more secure version of the database log

BravePoint After Imaging Manager Manage the switching and archiving of AI extents without shell scripts Switch, Copy, and Empty AI Extents: When an extent is full On a specific interval Still need a script to empty target directory Come to the Extreme DBA Class tomorrow to see a demonstration

BravePoint Auditing Record changes made to the database tables in a special Auditing table Very secure Very fast compared to Replication Triggers Also 4GL Replication Triggers cannot capture SQL92 DB changes Auditing policies (rules) can be changed online

Auditing Not just database changes User Connects & Disconnects Schema Changes Utility Operations – for example binary load Even Audit Policy changes can be audited BravePoint

BravePoint Default DB Startup Options -spin: ( 6000 * # of CPUs ) -bibufs:20(old value was 5) -aibufs:20(old value was 5) -semsets:3 (old value was 1) -B:3000 or (max(3000, -n * 8))

BravePoint New proutil options zerostats – online zeroing of selected promon/VST statistics featurelist – show database table, can also be queried in the 4gl dbdes/describe – summary information about the database _proutil & _rfutil merged into _dbutil

BravePoint Dump/Load Enhancements Multi-Threaded Binary Dump Multi-Threaded Index Rebuild

BravePoint New Online Options Adding DB, BI, or AI Extents Online prostrct addonline Truncate Database Log Online prolog –online Add New Fields to an Existing Table Online Existing users continue running OK New users need different R-code (CRC chg)

BravePoint New Online Options Add Index to an Existing Table Online and Activate the Index Online

BravePoint New Log File Format P-2624 T-4716 I Usr 6: (453) Logout by Dan Foreman on CON:. P-2908 T-5832 I SHUT 6: (542) Server shutdown started by Dan Foreman on CON:. P-5776 T-2064 I Usr 5: (453) Logout by Dan Foreman on CON:. P-944 T-6136 I BROKER 0: (2248) Begin normal shutdown P-944 T-6136 I BROKER : (334) Multi-user session end. Tue Nov 14 05:07: P-4776 T-1960 I DBUTIL : (451) procopy session begin for Dan Foreman on CON:. P-4776 T-1960 I DBUTIL : (334) procopy session end.

BravePoint New Log File Format Date on every line Fixed column format Process Id Thread Id Severity Indicator (Info,Warning,Fatal) Original Message# and Text Log Simplifier on BravePoint Web Site

BravePoint OpenEdge Replication Role Reversal Feature If you failover from Source to Target it is now very easy to failback once the problem is fixed

BravePoint V10.1B Feature Summary 64-bit DBKEYs 64-bit Sequences 64-bit Integers Storage Areas Large Index Keys Change Shared Memory Segment Size User I/O by Table/Index Enable AI Online

BravePoint bit Features DBKEY/ROWID No more 2 Billion Record/Fragment Limit Max Theoretical DB Size: 32 exabytesSequences Converted DB: proutil -C EnableSeq64 INT64 Data Type Can change existing INTEGERs in the Dictionary or Delta.df (no dump/load required) 9,223,372,036,854,775,807 (9 quintillion)

BravePoint Storage Areas Old limit was 1000 Who needs this many Areas? It will probably be more useful when: Certain utilities such as probkup can operate on a specific Area When Horizontal Partitioning is supported

BravePoint Large Index Keys Max Key Size increased from 192 bytes to 1,970 bytes Main Reason: Unicode requires 3 Bytes per Character 4k and 8k DB Blocksizes Only For converted databases: proutil -C EnableLargeKeys Can also do in the Data Dictionary

BravePoint Shared Segment Size Progress will automatically try to put all Shared Memory items (-B, -L, -n, -bibufs, -aibufs, etc.) into as few Shared Memory segments as possible Performance is usually a little better with fewer Segments

BravePoint User I/O by Table/Index New VSTs _UserTableStat _UserIndexStat Reads, Creates, Deletes, Updates For converted databases: proutil -C updatevst To see statistics for all tables must use: -tablerangesize, -indexrangesize -basetable, -baseindex

BravePoint Enable AI Online Enabling AI Online is combined with an Online Progress Backup probkup online db db.bak enableai

V10.1B SP01 Display Total Locks by User in promon BravePoint /11/08 Status: Lock Table 23:18:56 Usr Name Trans id Type Table RECID Flags Trans State 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active 5 Dan 1482 REC X L Active

V10.1B SP01 Buffer Cache Memory Overwrite Check -MemCheck promon (R&D > #4 Admin > #8 Block Check) Record/Index Block Checking -DbCheck -AreaCheck area name -Index Check index name -TableCheck table name promon (R&D > #4 Admin > #8 Block Check) BravePoint

V10.1B SP01 Block Validation Check in dbtool Old:5. Read database block(s) New:5. Read or validate database block(s) proutil dumpspecified actually works! Now more than 2 Dump Streams are possible BravePoint

V10.1B SP02 rfutil aiverify - Verify Archived AI Logs Partial option (AI Blk & Note Headers) Full option (Same as Partial option plus Note data) Freeze a DB During Roll Forward rfutil roll forward oplock rfutil roll forward opunlock Type 2 Area Space Allocation bug fix BravePoint

V10.1B SP03 Reduced Latch Contention (Read Operations) Phase 1 BravePoint

V10.1C Features – The Best Statement Cache What user is running what program! Shows:.p name, line#, and calling program For SQL, show the Query Long Redo Phase fixed! Reduced Latch Contention (Read Operations) Phase 2 BravePoint

V10.1C - Platforms HP Itanium 64-bit now supported RedHat 4.0 (32 & 64-bit) is supported Virtualization: VMWare and rPath BravePoint

V10.1C Features IPv6 Support Increase (some) Shared Memory Parameters Online proutil increaseto (no decrease option) -B, -aibufs, -bibufs, -L, -Mxs Protrace + ABL Stack Trace proGetStack Utility Not mentioned in Debugger Documentation BravePoint

V10.1C Features Proutil idxbuild –pfactor Maximum % Utilization of IX Blocks Default is100 Range idxcheck now better than idxfix More checks are performed BravePoint

V10.1C Features Client Temp Files can be > 2gb INPUT FROM OUTPUT TO srt, lbi, DBI, rcd (new) Bulk Load.d Files > 2gb Backup of OE Replication Target DB Only if the Replication Agent is running Release notes for SP01 and SP02 dont show any new features BravePoint

V10.2 Features There are no interesting database features in V10.2A…this release is about the graphical interface BravePoint

Questions?

BravePoint a Œ+ A=3;!_