DB10: What`s New in the OPenEdge RDBMS

Download Report

Transcript DB10: What`s New in the OPenEdge RDBMS 

DB-10:
What’s New in the OpenEdge® RDBMS
OpenEdge 10.1B RDBMS & SQL Enhancements
Richard Banville
Tom Harris
Brian Werne
10.1B – The Enterprise Release
Removing the Limits
 Very Large Database Support
• 64 bit features
• More areas & memory
 User Activity Insight
• New VSTs
 Ongoing online maintenance improvements
• Enable AI online
2 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
3 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
Very Large Database Support
 64-bit ROWIDs
 INT64 data type
 64-bit Sequences
 32,000 storage areas per database
 Shared memory limits
 Large index key entries
4 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
10.1B Large Database Support
Implicit/Explicit Feature Availability
Feature
10.1a Migrated
New 10.1B
64-bit ROWID*
Implicit
Implicit
32,000 Areas
Implicit
Implicit
64-bit Sequences
Explicit
Implicit
Large Index Entries**
Explicit
Implicit
64 bit VST values
Explicit
Implicit
*Type II Storage Areas only
**Only available on 4K & 8K block size
5 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
64-Bit ROWIDs



10.1B removes the 2 billion row limit
• Rows per area: ~281 trillion
• Rows per DB: ~ 9 quintillion
Type II Storage Areas only
• Increased addressable space using 64-bits
User visibility limited to
•
•
•
•
•
OpenEdge Management
PROMON
Utilities
Database .lg file and PROMSGS
Find by ROWID
Maximum database size of ~32 exabytes
6 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
INT64 Data Type
64-bit Integer Datatype
 Available to all 10.1B databases
 Store integers > 2GB
 32-bit range
• -2,147,483,648
• +2,147,483,647
 64-bit range
• -9,223,372,036,854,775,808
• +9,223,372,036,854,775,807
7 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Migrating to INT64
Changing INTEGER to INT64 data type

Can change existing INTEGER fields to INT64
• Dump and load NOT required
• Manual conversion NOT required
• Index rebuild NOT required



Exclusive schema access required
Re-compile IS necessary
One way conversion only

Existing database can upgrade vst values
proutil <dbname> –C updatevst
8 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Using INT64
 All tools work with INT64
• Data Dictionary, Admin, DB Navigator, etc
• Dump/Load Data and Definitions
• Reports
 Applying incremental (delta) .df files
• No impact on target database indexes
update field
"Cust-num" of "customer" as int64
9 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Existing INTEGER to INT64
Data Dictionary GUI
 Default format


10 DB-10: What’s New in the OpenEdge® RDBMS
same as integer
Character mode
Supported
DB Navigator
Supported
© 2007 Progress Software Corporation
Enabling 64-bit Sequences


New databases - Implicitly enabled
Pre-existing databases – Explicitly enabled
• No r-code version change

Enable via dbadmin, data dictionary or:
proutil <dbname> –C enableseq64
Existing Limit Value Conversions
Old Limit Value
2,147,483,647
“?”
2,147,483,647
“?”
Cycle
NO
YES
YES
NO
New Limit Value
263 – 1 *
231 – 1 *
231 – 1
“?”
* Indicates assumption for new behavior
11 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Removal of 1,000 Area Limit
 Maximum area number of 32,000 supported
• 32,000 areas assumed
• # extents/area increased to 1024 from 1000
• Max Extent Size remains at 1TB
 Startup parameter
-maxAreas nnnnn
• Conserves memory allocation
• Restricts feature use
12 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Shared Memory



-B increased from 125 million to 1 billion for 64 bit
Kernel settings must be set properly
New startup parameter
-shmsegsize n (max shm seg size in MB)
Platform
-shmsegsize
Value Range
Max # Segs
10.1a 10.1b
Max Shm
10.1a
10.1b
32-bit
128
4096/4g
21
32
2Gb
~4Gb
64-bit
1024/1g
32768/32g
116
256
116Gb
8Tb
SHMMAX max shm seg size
SHMMNI # shm segs on system
SHMSEG # shm segs per process
13 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Large Index Key Entries
 Removes the 192* byte limit
• 3 bytes per Unicode character
 Allows 1,970* bytes of user data
• 4K and 8K block sizes only
• Includes temp-tables (-tmpbsize 1, 2, 4, 8)
• Temp tables and -Bt
 Large keys enabled by default on new
databases
*Includes component separator flags so not necessarily the number
of characters that can be stored in an index entry
14 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Enabling Large Index Key Entries
Enabling large index keys on pre-existing DBs
 Via proutil command line
proutil <dbname> –C enablelargekeys
 Enabling using Data Admin / Data Dictionary
15 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
16 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Migrating an OpenEdge Database to 10.1B
Migration Preparation




Perform a backup
Test your backup
Disable Replication, AI, JTA, 2PC
Truncate bi file
• Utilities available in <install>\bin\101dbutils
101dbutils/101a_dbutil <dbname> -C truncate bi
This codebase cannot open a 32-bit DBKEY database if the BI file
is not truncated. (13711)
Use the 10.1A proutil utility to truncate the BI file. (13727)
17 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Migrating an OpenEdge Database to 10.1B
Migration Process
 Open a pre-existing OpenEdge database



with 10.1B
No longer directly accessible by pre-10.1B
executables
Not all 10.1B features implicitly enabled
PROMSGS
 For V9 Databases simply convert
proutil <dbname> -C conv910
18 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Release Compatibility

Client Server
• Remote Connection through a server allowed
• Can read, but not store (update/create), 64-bit fields
• Client disconnected if it tries to access
– dbkey or rowid > 31 bits (2 billion)
– Sequence > 31 bits
• Must re-compile if INT64 in use (otherwise OK)
– CRC changes prevent old r-code running

Direct Connect Error Message
An invalid feature 9 has been encountered in the database's
Enabled feature list. (11727)
The list of enabled features in database x contains features
that are not recognized by this codebase (11810)
19 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Backup Compatibility
 Backup/restore incompatibilities
• Backup 10.1a DB via 101a_dbutil
• Restore 10.1a backup if no recovery log data
• Restored database is always 10.1b
 Binary Load
• Compatible if no large keys
• Compatible if < 2 billions rows
• Compatible if no int64 datatypes (CRC)
20 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
10.1B Large Database Features
proutil <dbname> –C describe
Database Name
Version
Block Size
Largest Cluster
Create Date
Last Open Date
Prior Open Date
Schema Change Date
: E:\OpenEdge\101B\bigdb101B
: 150.0
: 4096
: 512
: Fri Jul 28 16:03:35 2006
: Tue Aug 08 13:50:08 2006
: Tue Aug 08 13:50:08 2006
: Fri Jul 28 16:14:56 2006
Before Imaging information
Block Size
Cluster Size (16K Units)
Last Open Date
: 16384
: 1024
: Tue Aug 08 13:26:12 2006
Backup Information
Last Full Backup Date
Last Incremental Backup
: Tue Aug 08 13:49:34 2006
: *** Not yet performed ***
21 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
10.1B Large Database Features
proutil <dbname> –C describe
Database Features
ID
----
Feature
---------------------------------
Active
------
5
Large Files
Yes
6
Database Auditing
Yes
9
64 Bit DBKEYS
Yes
10
Large Keys
Yes
11
64 Bit Sequences
Yes
22 DB-10: What’s New in the OpenEdge® RDBMS
Details
-------
© 2007 Progress Software Corporation
Reverting to OpenEdge 10.1a
23 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Reverting a Database
proutil <dbname> –C revert
 Reverts a migrated database back to 10.1A
• Single user mode only
 Back up the database before starting!!
• Changes internal structures back 10.1A
• Disables 2PC, JTA, Replication, AI
• Truncates the .bi file
24 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Sometimes Its Tough To Go Back
 Cannot revert a database if
•
•
•
•
•
INT64 data type in use
Any Type II storage area has HWM > 32 bits
Large index keys enabled
Large sequences enabled
Highest database area number exceeds 1,000
25 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Manual Revert
Reverting a database via D & L
 Not always possible to re-load
• Can’t load large key entries
• If addressing above 10.1A limits
– ROWIDs, Sequences, INT64, etc…
• Would need a data conversion routine
 Must data D&L after massage of data/structure
• Binary D&L checks CRC!
26 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
27 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
User I/O By Table and Index
New Virtual System Tables

Monitor table and index activity per user
• What I/O are users doing?
– Creates, reads, updates, deletes
• How is a query performing?

VSTs included in 10.1B databases
• Update schema of pre-existing databases
proutil <dbname> –C updatevst


Table I/O per user:
Index I/O per user:
28 DB-10: What’s New in the OpenEdge® RDBMS
_UserTableStat
_UserIndexStat
© 2007 Progress Software Corporation
User I/O by Table and Index: Availability
 VST record access from ABL and SQL
 Available in R&D section of promon
 OpenEdge Management
29 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Managing User I/O By Table and Index
Additional shared memory usage
 Shared Memory Usage:
• 32 bytes per table
• 40 bytes per index
 Control with startup parameters
Start
Range
Tables -basetable n -tablerange n
Indexes -baseindex n -indexrange n
 Can only change Start parameter at runtime
• VST _StatBase
 These are not new parameters!!
30 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
31 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Online Maintenance
 Enable ai online
probkup online <dbname> x.bak enableai
 Enable ai management online
probkup online <dbname> x.bak enableai
[ enableaiarchiver -aiarcdir dirlist]
 rfutil x -C aimage aioff introduced in 10.0a
32 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Online Maintenance
Online Space allocation
prostrct addonline <dbname> newarea.st



Add on line allows new areas when ai enabled.
Prompt allows you to continue
Rolling forward…
SYSTEM ERROR: Area 21 was not found in the
database. (13708)
Please add the missing area according to the structure
file in the source database, then use the roll forward
retry utility to continue the operation. (13709)
33 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Miscellaneous

Defaults
• Database size changed from 1K to 8K (Unix)
• Temp table block size defaults to 4K (-tmpbsize)

Backout Buffer Enhancement – Part II
• Improved rollback for OLTP

Enhanced Record Lock Release
• Improves logout & transaction end (-lkrela disables)

Binary Load w/Build Indexes
• Improved Performance
• Uses same index rebuild code
34 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
35 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
•
•
•
•
Wide table support
Multi database query
Query extensions
Availability features
36 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Wide Table Support
Removing limits
 Table limits
• Removal of 500 column / field access limit
 New limit is effectively 5000 columns
• Limited by 32K maximum record size
• Increased compatibility with ABL
• Equivalent or better to other SQL vendors
37 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
•
•
•
•
Wide table support
Multi database query
Query extensions
Availability features
38 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Multi-Database Query
Cross database server-side joins
SQL
Client
DBNavigator
- JDBC URL
Crystal Reports – ODBC DSN
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
DB
Primary
39 DB-10: What’s New in the OpenEdge® RDBMS
Read
Only
Auxiliaries
© 2007 Progress Software Corporation
Connecting to an Auxiliary Database
 Catalog – an alias for a database
 Connecting to an auxiliary database
CONNECT ‘/usr/wrk/sports2000’ AS CATALOG mysports;
• Must be on same host as Primary database
 Disconnecting a catalog
DISCONNECT CATALOG mysports;
40 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
4 part naming – multi-database query
Four Level Naming
 Four level naming convention
catalog.schema.table.column-name
 Example
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
 Applies to schema, tables, columns, stored
procedures
41 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Multi-Database : automatic connections
SQL
Client
DBNavigator
- JDBC URL
Crystal Reports – ODBC DSN
dbname[-mdbq:config1]
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
Properties
DB
[config1]
Primary
42 DB-10: What’s New in the OpenEdge® RDBMS
Read
Only
Auxiliaries
© 2007 Progress Software Corporation
Multi-DB configuration:JDBC -DBNavigator
jdbc:datadirect:openedge://localhost:6748;databaseNa
me=sports2000 [-mdbq:config1]
43 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
ODBC – Multi-DB configuration
44 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Crystal – with Multi-DB Query
Auxiliary databases
Primary database
45 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Connection types
OpenEdge SQL
Server
connection
OpenEdge SQL
Client connection
Primary
SQSV
REMC
Auxiliary
SQFA
SQFC
46 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
OpenEdge Management User Activity View
– Primary Database
Primary database connections
47 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
OpenEdge Management User Activity View
– Auxiliary Database
Auxiliary database connections
48 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
•
•
•
•
Wide table support
Multi database query
Query extensions
Availability features
49 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Group By Expression
Using “Group By” with an expression
 Grouping by alias
SELECT CONCAT (State, City) AS "CityState",
COUNT (city)
FROM Pub.Customer
GROUP BY "CityState";
Alias
 Grouping by expression
SELECT CONCAT (State, City),
COUNT (city)
FROM Pub.Customer
GROUP BY CONCAT (State, City);
50 DB-10: What’s New in the OpenEdge® RDBMS
Expression
© 2007 Progress Software Corporation
Limiting Rows in a Result Set
SELECT TOP
 Returns a maximum of n rows to client
SELECT TOP 5 name FROM Pub.Customer
ORDER BY balance DESC;
 Is interpreted last
• After GROUP BY, DISTINCT, aggregate
phrases
 Performance boost on large tables
• Where n is small, relative to rows in table
51 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Agenda
 Large Database Support
 Migration & Compatibility
 User I/O by Table / Index
 Online Maintenance
 OpenEdge SQL Enhancements
•
•
•
•
Wide table support
Multi database query
Query extensions
Availability features
52 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Availability Features
Online schema changes added in 10.1B
• ALTER SEQUENCE
• REVOKE
• DROP VIEW
• CREATE SYNONYM
• DROP SYNONYM
53 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
In Summary
 Tremendous amount of stuff in



OpenEdge 10.1B
The OpenEdge Database can
support all your data
Availability and scalability
improvements
Many new SQL features
54 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Questions?
55 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
For More Information, go to…
 PSDN
 Progress eLearning Community
• Database Administration
• Advanced Database Administration
• Using OpenEdge SQL
 Documentation
56 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Relevant Exchange Sessions
 DB-12: Moving to OpenEdge
 DB-13: Progress VLDB
 DB-14: OpenEdge Database Run-time



Security Revealed
DB-15: Inside the Recovery Subsystem
DB-21: RDBMS Roadmap and
Info Exchange
DB-22: Zero to 30,154 in Twenty Days
57 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation
Thank you for
your time
58 DB-10: What’s New in the OpenEdge® RDBMS
© 2007 Progress Software Corporation