B2: What

1
B2 Whats New in 10.1 RDBMS?

• So many features, so few releases

Richard Banville
Progress Fellow
2
Agenda
The database ilities

• High Availability
• Problem Avoidability
• Visibility
• Scalability
• Maintainability

3
Database Consistency Checking
Seen these messages before?

• Index orderId in order for recid 2010 could
  not be deleted.
• Wrong key in idx 10 for record 2010.
• Invalid size of an index entry.

4
Database Consistency Checking
Or how about these

• Invalid RM block for area 10
• rmdoins pbk-gtfree went negative dbkey 4096
• bkwrite bktbl dbk 4096 not equal to bkbuf dbk
  -1234
• bkaddr called with negative blkaddr  -1234

5
Database Consistency Checking
Seen these messages before?

• Index orderId in order for recid 2010 could not
  be deleted. (1422)
• Wrong key in idx 10 for record 2010. (14227)
• Invalid size of an index entry. (2670)

6
Database Consistency Checking
Or how about these

• Invalid RM block for area 10 (10809)
• rmdoins pbk-gtfree went negative dbkey 4096 area
  10 (822)
• Record continuation not found, fragment recid
  4096 area 10. (10831)
• bkwrite bktbl dbk 4096 not equal to bkbuf dbk
  -1234 (645)
• bkaddr called with negative blkaddr  -1234
  (762)           

7
Database Consistency Checking
Stop problems before they happen

• Shared memory overwrite protection
• -MemCheck
• Physical block consistency checking
• -DbCheck
• -AreaCheck area name
• -Index Check index name
• -TableCheck table name

8
Database Consistency Checking
Enabling the consistency checks

• Database startup parameter
• Managed via promon RD Admin Functions
• 8. Block level consistency check

Current consistency check status 1. -MemCheck
enabled 2. -DbCheck enabled 3.
-AreaCheck in area "customer" enabled 4.
-IndexCheck disabled 5. -TableCheck
disabled Enter the option to enable/disable a
consistency check
9
Database Consistency Checking
Performance impact

• Memory checking unnoticeable impact
• Block level checking

Current consistency check status 1. -MemCheck
enabled 2. -DbCheck enabled 3.
-AreaCheck in area "customer" enabled 4.
-IndexCheck disabled 5. -TableCheck
disabled Enter the option to enable/disable a
consistency check
10
Database Consistency Checking
Looking for existing inconsistencies online

• dbtool block consistency checking
• 5. Read or Validate Database Block(s)
• Validation levels
• 0 Block header info only
• 1 Record header record size
• 2 Record overlap checking
• 3. Record Validation (logical validation)

11
Online Tools
More analysis available online

• Online dbanalys - Includes chanalys info
• Tools now online
• proutil ltdbgt -C chanalys -scan
• -scan fewer locks, less consistent
• proutil ltdbgt -C idxcheck
• Idxcheck validation levels
• Physical consistency
• Keys to records
• Records to keys
• Validate key order
• Locks associated tables

contention
12
The roll forward process
myDb.bak
myDb
13
The roll forward process
ftp
myDb
ai
ai
ai
ai
14
The roll forward process
ftp
myDb Hot Standby
Roll forward
ai
ai
ai
ai
ai
ai
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.
15
Ai Verify
Ai validation before application

• rfutil ltdbgt -C aiverify lttypegt
• 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

16
Roll forward verification
rfutil myDb -C aiverify full
ftp
myDb Hot Standby
ai
ai
ai
ai
ai
ai
ai
ai
X
rlNoteVerify 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

• Recovery Scenario
• Roll forward to transaction

17
More tools for high availability
Replication enhancements

• Online backup of replication target
  (foundational work)
• Normal operating state required
• SHR schema lock on source
• ai file stores changes until complete
• EMCs SRDF certification
• Real time copies of logical data volumes
• Data replication
• Remote backup

Symmetrix Remote Data Facility (SRDF)
18
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.

19
Increase startup parameters online
Increase startup parameters without database
restart

• proutil ltdbgt -C increaseto ltparamsgt
• ltparamsgt -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

20
Increase startup parameters online
Increasing available locks online
proutil myDb -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.
21
Agenda
The database ilities

• High Availability
• Problem Avoidability
• Visibility
• Scalability
• Maintainability

22
Promon
Better organized server grouping

• Promon RD Status
• 17. Servers By Broker
• A more organized view of existing data

Sv Pend. Cur.
Max. Port No Pid Type Protocol Logins
Users Users Users Num 0 15275 Login
TCP 5 0 0 15 2053 2
15501 Auto TCP 1 0 1 15
1025 3 15509 Auto TCP 1 0
1 15 1026 4 15511 Auto TCP
1 0 1 15 1027 5 15514 Auto
TCP 1 0 1 15 1028
1 15381 Login TCP 5 0 0
5 2051 7 15609 Auto TCP 1 0
1 5 1030 8 15617 Auto TCP
1 0 1 5 1031 9 15629
Auto TCP 1 0 1 5
1032 10 15638 Auto TCP 1 0
1 5 1033
23
Promon
Improved user information

• Promon RD Other Displays
• 7. Total Locks per User
• User type display _Connect-ClientType

User Name Type PID TTY Total
Record SHR/EXCL... 5 richb SELF/ABL 15494
/dev/pts/16 1 1 1 11 richb
SELF/ABL 16101 /dev/pts/13 3 3 0
24 richb REMC/SQLC 15530 2 1
0 48 richb REMC/WTA 20182 mysystem
2 2 0 100 richb REMC/ABL 20183
mysystem 2 2 0 101 richb SQFC
20100 mysystem 5 4 0 150
richb REMC/APSV 20101 mysystem 1 1
0 175 richb SELF/APSV 20102 mysystem 1
1 0
24
Statement Caching
What code is executing against my database

• List recent client statements
• Promon RD 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
• .is are in-lined

25
Statement Caching
Where is that ABL code executing
User number 24 User name
richb User type
REMC/ABL Login date/time
03/06/08 1530 Statement
caching type ABL Program
Stack Statement caching last updated 03/06/08
1535 Statement cache information 39
proc7 /usr1/richb/x.p
26 proc6 /usr1/richb/x.p
22 proc5 /usr1/richb/x.p
18 proc4
/usr1/richb/x.p
14 proc3 /usr1/richb/x.p
10 proc2 /usr1/richb/x.p
6 proc1 /usr1/richb/x.p
3
/usr1/richb/x.p
26
Statement Caching
Whats that SQL code executing
User number 23 User name
richb User type
REMC/SQLC Login date/time
03/06/08 1542 Statement
caching type SQL Statement Statement
caching last updated 03/06/08 1542 Statement
cache information select count() from
pub.customer
27
Statement Caching
VST support - _Connect

• proutil ltdbgt -C updatevst
• Need to load new schema fields
• _Connect vst
• _Connect-CachingType
• _Connect-CacheLastUpdate
• _Connect-CacheInfoType
• _Connect-CacheLineNumber32
• _Connect-CacheInfo32

28
Agenda
The database ilities

• High Availability
• Problem Avoidability
• Visibility
• Scalability
• Maintainability

29
Scalability
I want more

• Large file support for bulk load (gt 2Gb)
• Independent of DB large file status
• IPv6 support
• More ip addresses
• only 30 ip addrs left (7 yrs)
• Routing improvements
• Required by government contracts
• Configuration
• -ipver IPv4 (default) or IPv6
• Property file ipver
• Explorer option

30
Scalability
I want more

• IPv6 support
• Mixed mode (dual stack)
• IPv6 can convert IPv4 address
• Not available on windows
• Avoiding confusion
• -minport/-maxport
• use IPv6 configured H hostnames

31
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

32
Agenda
The database ilities

• High Availability
• Problem Avoidability
• Visibility
• Scalability
• Maintainability

33
Roll forward protection
myDb.bak
myDb
ai
ai
ai
ai
ai
ai
ai
ai
34
Roll forward protection
myDb
Roll forward
ai
ai
ai
ai
ai
ai
ai
ai
X
The database was last changed Mon Apr 1
153738 2008. The after-image file expected
Mon Apr 1 153345 2008. Those dates don't
match, so you have the wrong copy of one of
them. roll forward open /usr1/x.a4 error -1.
35
Roll forward protection
myDb
ai
ai
ai
ai
ai
ai
ai
ai
X
In the .lg file rfutil -C roll forward session
end. Single-user session begin for richb on
/dev/pts/101. Begin Physical Redo Phase at 256 .
36
Roll forward protection
Non interruptible roll forward

• rfutil ltdbgt -C roll forward oplock
• Prevents stray database connections
• Prostrct add allowed
• Automatic disablement
• At roll forward completion
• Explicit disablement
• rfutil ltdbgt -C roll opunlock
• Recovers db
• Stops the roll forward process

37
Roll forward protection
rfutil ltdbgt -C roll forward oplock a myDb.a1
myDb
ai
ai
ai
ai
ai
ai
ai
ai
- 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.
Connection attempts
38
Index Rebuild
More control

• Index rebuild packing factor
• Max of space used
• Avoids costly index block splits
• proutil ltdbgt -C idxbuild pfactor lt60 100gt
• Examine utilization in idxanalys

39
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
40
Space Allocation
Alter free space search of record free chain

• Rec space search depth (10.1b05)
• New depth search algorithm
• RD ? Administrative Functions Menu ? Adjust
  Latch Options ?
• 2. Record Free Chain Search Depth Factor 5
• -recspacesearchdepth ltfactorgt

41
SQL Stored Procedures
64 bit stored procedure support.

• 64 bit JVM availability
• Java 1.5 certification
• Can use same drivers
• Additional schema
• _SysProcBin, _SysProcText
• 32 bit databases have schema already
• Databases created in 64 bit environment
• proutil ltdbgt -C enablestoredproc

42
Binary Dump Specified - Improved
Binary dump specified with between range
dumping

• proutil ltdbgt -C dumpspecified ltfield-infogt
• ltop1gt ltlow-valuegt
• AND ltop2gt lthigh-valuegt ltdirgt
• Option values GT, GE, LT, LE, EQ
• Dump specific ranges
• Improved parallelism
• proutil db C dumpspecified cust.custnum
  GE 5 and LE 100 /dumpdir

43
In Summary

• Weve made it even easier
• to achieve high availability
• to see whats going on
• to maintain

44
Relevant Exchange Sessions

• OPS-1 How Healthy is Your Database Today?
• OPS-18 Data Management Roadmap
• OPS-19 Whats IPV6 and Why Should I Care?
• OPS-28 A New Spin on Some Old Latches

45
?
Questions
46
Whats here that is also in 10.1B03?

• Database Consistency Checking
• -memCheck, 0DbCheck, -AreaCheck, -IndexCheck,
  -TableCheck
• Dbtool Read or Validate Database Blocks
• Rfutil
• aiverify
• roll forward oplock
• Promon
• Servers by Broker
• Total Locks per User
• Record lock removal on NO-LOCK reads

47
Thank You
48
(No Transcript)