OPS3 Whats New in 10 1 RDBMS So

OPS-3: What’s New in 10. 1 RDBMS? So many features, so few releases Wei Qiu Principal Software Engineer

Agenda The database “ilities” § High Availability • Problem Avoidability • Visibility • Scalability

Database Consistency Checking Seen these messages before? Index order. Id in order for recid 2010 could not be deleted. Wrong key in idx 10 for record 2010. Invalid size of an index entry. 3 © 2008 Progress Software Corporation

Database Consistency Checking Or how about these… Invalid RM block for area 10 rmdoins: pbk->free went negative dbkey 4096 bkwrite: bktbl dbk 4096 not equal to bkbuf dbk -1234 bkaddr called with negative blkaddr: -1234 4 © 2008 Progress Software Corporation

Database Consistency Checking Stop problems before they happen § Shared memory overwrite protection • -Mem. Check § Physical block consistency checking • • 5 -Db. Check -Area. Check “area name” -Index Check “index name” -Table. Check ‘table name” © 2008 Progress Software Corporation

Database Consistency Checking Enabling the consistency checks § Database startup parameter § Managed via promon R&D Admin Functions • 8. Block level consistency check Current consistency check status: 1. 2. 3. 4. 5. -Mem. Check: enabled -Db. Check: enabled -Area. Check in area: "customer" enabled -Index. Check: disabled -Table. Check: disabled Enter the option to enable/disable a consistency check: 6 © 2008 Progress Software Corporation

Database Consistency Checking Performance impact § Memory checking: unnoticeable impact § Block level checking Current consistency check status: 1. 2. 3. 4. 5. -Mem. Check: enabled -Db. Check: enabled -Area. Check in area: "customer" enabled -Index. Check: disabled -Table. Check: disabled < 1% ~5% Enter the option to enable/disable a consistency check: 7 © 2008 Progress Software Corporation

Database Consistency Checking Looking for existing inconsistencies online § dbtool block consistency checking 3. Record Validation (logical validation) 5. Read or Validate Database Block(s) § Validation levels • 0: Block header info only • 1: Record header & record size • 2: Record overlap checking 8 © 2008 Progress Software Corporation

Online Tools More analysis available online § § Online dbanalys - Includes chanalys info Tools now online proutil <db> -C chanalys [ -scan ] • -scan: fewer locks, less consistent proutil <db> -C idxcheck • Idxcheck validation levels 9 Physical consistency Keys to records Records to keys Validate key order Locks associated tables contention – – – © 2008 Progress Software Corporation

The roll forward process my. Db. bak my. Db 10 © 2008 Progress Software

The roll forward process ftp my. Db ai 11 ai ai ai © 2008

The roll forward process ftp my. Db Hot Standby Roll forward ai ai X SYSTEM ERROR: Attempt to read block 18446744073709550382 which does not exist in area 8, database x. ** Save file named core for analysis by Progress Software Corporation. 12 © 2008 Progress Software Corporation

Ai Verify Ai validation before application rfutil <db> -C aiverify <type> • Partial: ai block and note header validation – Increases reliability of archived ai files • Full: partial + note data validation – Identifies point in time recovery • Running – At ai switch or on ai archival – Just before roll forward of extent § Preferably on hot standby 13 © 2008 Progress Software Corporation

Roll forward verification rfutil my. Db -C aiverify full ftp my. Db Hot Standby ai ai X rl. Note. Verify: Note dbkey is negative -1234. (14099) Trid: 358 code = RL_CXINS version = 2 (12528) Hot Stand by: • Validate/fix production db • Re-base hot standby 14 Recovery Scenario: • Roll forward to transaction © 2008 Progress Software Corporation

The problem Have you ever seen these error messages… Out of free shared memory. Use -Mxs to increase. Lock table overflow, increase -L on server. or heard these… Why can’t you improve the buffer pool hit ratio on the database? The recovery subsystem is a bottle neck. Look at that BI buffer wait %. 15 © 2008 Progress Software Corporation

Increase startup parameters online Increase startup parameters without database restart proutil <db> -C increaseto <params> § <params>: -L, -B, -bibufs, -aibufs, -Mxs § Increase, not decrease § Resource restrictions apply § New shared memory segments • Security restrictions – Servers: automatically attach quickly – Self serve: attach w/db action over time • Segment size 16 © 2008 Progress Software Corporation

Increase startup parameters online Increasing available locks online proutil my. Db -C increase -L 10000 Waiting for broker connection to newly added shared memory segments. Usr Name Type Pid 7 richb ABL 5957 The connections above have not attached to recently added shm segments. Do you wish to recheck? (y/n) (n): Increase params aborted because of shared memory allocation issue. (y): Increase params increasing lock table size (-L) from 1025 to 10016. 17 © 2008 Progress Software Corporation

More tools for high availability Replication enhancements § “Online” backup of replication target • Normal operating state required • SHR schema lock on source • ai file stores changes until complete § EMC’s SRDF* certification • Real time copies of logical data volumes • Data replication • Disaster recovery • Remote backup • Fail over/fail back *Symmetrix Remote Data Facility (SRDF) 18 © 2008 Progress Software Corporation

Promon Better organized server grouping § Promon Sv No 20 R&D Status • 17. Servers By Broker Pend. Cur. data Max. • A more organized view of existing Pid Type Protocol Logins 0 2 3 4 5 15275 15501 15509 15511 15514 Login Auto TCP TCP TCP 1 7 8 9 10 15381 15609 15617 15629 15638 Login Auto TCP TCP TCP Users Port Num Users 5 1 1 0 0 0 1 1 15 15 15 2053 1025 1026 1027 1028 5 1 1 0 0 0 1 1 5 5 5 2051 1030 1031 1032 1033 © 2008 Progress Software Corporation

Promon Improved user information § Promon R&D Other Displays • 7. Total Locks per User Name 5 11 24 48 100 101 150 175 richb richb Type SELF/ABL REMC/SQLC REMC/WTA REMC/ABL SQFC REMC/APSV SELF/APSV PID 15494 16101 15530 20182 20183 20100 20101 20102 TTY /dev/pts/16 /dev/pts/13 mysystem mysystem Total 1 3 2 2 2 5 1 1 Record SHR/EXCL. . . 1 3 1 2 2 4 1 1 1 0 0 0 0 § User type display – _Connect-Client. Type 21 © 2008 Progress Software Corporation

Statement Caching What code is executing against my database § List recent client statements • Promon R&D Status 18. Client Database-Request statement Cache – By user/server/all users current and future. – Last line or entire stack – ABL info obtained from DEBUG-LIST output §. i’s are in-lined 22 © 2008 Progress Software Corporation

Statement Caching Where is that ABL code executing 23 User number User name User type Login date/time : : 24 richb REMC/ABL 03/06/08 15: 30 Statement caching type Statement caching last updated : ABL Program Stack : 03/06/08 15: 35 Statement cache information : 39 26 22 18 14 10 6 3 : : : : proc 7 /usr 1/richb/x. ped proc 6 /usr 1/richb/x. ped proc 5 /usr 1/richb/x. ped proc 4 /usr 1/richb/x. ped proc 3 /usr 1/richb/x. ped proc 2 /usr 1/richb/x. ped proc 1 /usr 1/richb/x. ped © 2008 Progress Software Corporation

Statement Caching What’s that SQL code executing 24 User number User name User type Login date/time : : 23 richb REMC/SQLC 03/06/08 15: 42 Statement caching type Statement caching last updated : SQL Statement : 03/06/08 15: 42 Statement cache information : select count(*) from pub. customer © 2008 Progress Software Corporation

Statement Caching VST support - _Connect proutil <db> -C updatevst • Need to load new schema fields § _Connect vst • • • 25 _Connect-Caching. Type _Connect-Cache. Last. Update _Connect-Cache. Info. Type _Connect-Cache. Line. Number[32] _Connect-Cache. Info[32] © 2008 Progress Software Corporation

Scalability I want more § § Large file support for bulk load (> 2 Gb) • Independent of DB large file status IPv 6 support • More ip addresses – only 30% ip addrs left (7 yrs) • Routing improvements • Required by government contracts • Configuration – -ipver IPv 4 (default) or IPv 6 – Property file: ipver= 27 © 2008 Progress Software Corporation

Scalability I want more § IPv 6 support • Mixed mode (dual stack) –

Internal Performance Improvements The need for speed § “clean” shutdown indicator • Avoids long redo in roll forward • Last 2 clusters always redone § Improved read operation concurrency • Latch enhancements and usage optimization § Multi-user bi activity optimization • Avoid rollback “block jump” notes • Fewer notes written 29 © 2008 Progress Software Corporation

Roll forward protection my. Db. bak my. Db ai 31 ai ai © 2008

Roll forward protection my. Db Roll forward ai ai X ** The database was last changed Mon Apr 1 15: 37: 38 2008. ** The after-image file expected Mon Apr 1 15: 33: 45 2008. ** Those dates don't match, so you have the wrong copy of one of them. roll forward open /usr 1/x. a 4 error: -1. 32 © 2008 Progress Software Corporation

Roll forward protection my. Db ai In the. lg file: ai ai X rfutil -C roll forward session end. Single-user session begin for richb on /dev/pts/101. Begin Physical Redo Phase at 256. 33 © 2008 Progress Software Corporation

Roll forward protection Non interruptible roll forward rfutil <db> -C roll forward oplock § Prevents “stray” database connections • Prostrct add allowed § Automatic disablement • At roll forward completion § Explicit disablement rfutil <db> -C roll opunlock • Recovers db • Stops the roll forward process 34 © 2008 Progress Software Corporation

Roll forward protection rfutil <db> -C roll forward oplock –a my. Db. a 1 my. Db ai ai Connection attempts: - Access to the database during roll forward process is not allowed because it will modify the database. - Write access to the database will not be allowed until the roll forward operations have completed. 35 © 2008 Progress Software Corporation

Index Rebuild More control § Index rebuild packing factor • Max % of space used • Avoids costly index block splits proutil <db> -C idxbuild –pfactor <60 – 100> § Examine utilization % in idxanalys 36 © 2008 Progress Software Corporation

Index Fix Interface Specific choices § Idxfix uses idxbuild interface • Select indexes to fix by – Table, schema, area or activation state Select one of the following: All (a/A) - Fix all the indexes Some (s/S) - Fix only some of the indexes By Area (r/R) - Fix indexes in selected areas By Schema (c/C) - Fix indexes by schema owners By Table (t/T) - Fix indexes in selected tables By Activation (v/V) - Fix selected active or inactive indexes 37 © 2008 Progress Software Corporation

SQL Stored Procedures 64 bit stored procedure support. § 64 bit JVM availability • Java™ 1. 5 certification • Can use same drivers • Additional schema – _Sys. Proc. Bin, _Sys. Proc. Text • 32 bit databases have schema already • Databases created in 64 bit environment proutil <db> -C enablestoredproc 38 © 2008 Progress Software Corporation

Binary Dump Specified - Improved Binary dump specified with “between” range dumping proutil <db> -C dumpspecified <field-info> <op 1> <low-value> AND <op 2> <high-value> <dir> § § § Option values: GT, GE, LT, LE, EQ Dump specific ranges Improved parallelism proutil db –C dumpspecified custnum GE 5 and LE 100 /dumpdir 39 © 2008 Progress Software Corporation