Transcript OE10_DB_changes

OE10_DB_changes:
[email protected]
AGENDA:
 OpenEdge10-tietokantaan tehdyt
laajennukset eri versioissa

Ei Dataservers, ei OpenEdge SQL, vaan
OpenEdge RDBMS via ABL.



(Ei Fathom Management, Replication, Clusters)
Kokemusyhteenveto Progress V9 vs. OE10
Muutama demo
OE10 - Releases


OE10.0A - December 2003
OE10.0B - August, 2004


OE10.1A - December, 2005


(SP 03, Nov 15, 2007)
OE10.1C - February 2008


(SP 02, Oct 13, 2006)
OE10.1B - December, 2006


(SP 05, Apr 21, 2006)
(SP 03, Jan 30, 2009)
OE10.2A - November, 2008
Kokemusyhteenvetoa:
Progress V9 vs. OE10:
 Skaalautuvampi



erityisesti suuret tietokannat
64-bittiset ympäristöt
Helppokäyttöisempi



Rajoitusten poistot (2 GB, tietuemäärät)
Online operaatiot
After Image käyttöönotto
Kokemusyhteenvetoa … :

ASAII:n hyödyntäminen



Taulukohtainen tilanhallinta
Fragmentoitumisen esto
Talletusaluekohtaiset operaatiot
OE10.0A - December 2003





Online schema additions (joka versiossa)
Performance (joka versiossa)
Scalable and significantly faster index rebuild
utility
New Data Types: Big int, Float, Ulong, BLOB,
CLOB, Datetime, Datetime with Time Zone
Space allocation mechanism enhances
performance (ASAII)

“Clusters” or groups of adjacent database blocks
OE10.0A - December 2003

ASA II:




Database objects can be composed of one
or more clusters
Storage areas include unique row identifier
per area and object block
Simultaneous support for Advanced
Storage Architecture 1 and 2 (ASA1 and
ASA2) objects.
DEMO ?
OE10.0A - December 2003

Data Types (4GL)





Binary Large Objects (BLOB)
Character Large Object (CLOB)
DateTime
DateTime-TZ
Versiossa OE10.1B

INT64
OE10.0B - August, 2004
(SP 05, Apr 21, 2006)

Failover Clusters

Support for failover clusters, formerly
through the Fathom™ Clusters product, is
integrated into the OpenEdge Enterprise
RDBMS product.


Ei uusi ominaisuus
Virhetilanteessa järjestelmä siirtyy
varalaitteeseen.

Jaettu tietokantajärjestelmä
OE10.0B - August, 2004

IDXBUILD


IDXCHECK


The IDXBUILD qualifier to PROUTIL is enhanced to allow
you three additional ways to specify the indexes to rebuild:
by area, by schema owner, and by table.
The IDXCHECK qualifier to PROUTIL is enhanced to allow
you three additional ways to specify the indexes to check:
by area, by schema owner, and by table.
PROSTRCT


The PROSTRCT utility is enhanced with the REORDER AI
qualifier.
The REODER AI qualifier enables you to reorder after-image
extents so that empty extents immediately follow the
current extent in switch order.
OE10.0B - August, 2004

RFUTIL
The RFUTIL utility is enhanced with the
EXTRACT qualifier.
 The EXTRACT qualifier provides you the
ability to extract the active AI notes from a
full after-image extent.
rfutil db-name -C aimage extract
-a ai-extent -o output-file

OE10.0B - August, 2004

SSL Connections

The RDBMS brokers and servers are enhanced to accept Secure Sockets Layer (SSL)
connections.
New DB Parameters:
-ssl
Requires that all brokers and all connections use SSL
Note: SSL incurs heavy performance penalties, depending on the client, server, and
network resources and load.
-keyalias <key-alias-name>
Specifies the alias name of the SSL private key/digital certificate key-store entry to use
-keyaliaspasswd <key-alias-password>
Specifies the encrypted password to the key alias
-nosessioncache
Disables the use of SSL session caching Session Timeout
-sessiontimeout n
Specifies the length of time in seconds that an SSL session is held in the session cache
OE10.1A - December, 2005
(SP 02, ct 13, 2006)

Auditing


OpenEdge Release 10.1A introduces a new core business service —
auditing.
Core Business service-idea:
 Aikaisemmin Progress oli tehnyt vastaavat palvelut oman
frameworkin (Progress Dynamics) manager-proseduureiksi













Session Manager
Configuration File Manager
Connection Manager
Profile Manager
Localization Manager
Security Manager
Repository Manager and Repository Design Manager
General Manager
Request Manager
User Interface Manager
Referential Integrity Manager
Customization Manager
Ideana on toteuttaa manager-proseduurit kaikkien sovellustyyppien
käytettäviksi
OE10.1A - December, 2005
Auditing








Audit database create, update, and delete events withut using schema triggers.
Audit database create, update, and delete events without using schema
triggers.
Audit internal events such as schema changes, audit policy changes,
database connections, authentication (login, logout), or archiving.
Audit application-defined events, typically for events with no
corresponding database operation.
Use a common auditing approach for Progress 4GL, SQL, and database
utilities.
Securely create and manage highly configurable audit policies that can
be aggregated at run time and deployed.
Leverage Audit Policy Maintenance, which is a new audit policy tool,
and/or write your own utility using open API.
Support nonrepudiation of audit data by physically preventing updates
and sealing audit data to prevent tampering both inside and outside an
OpenEdge RDBMS.
OE10.1A - December, 2005
Auditing









Create separation of audit administration duties by setting up
appropriate privileges.
Deny blank user ID access to the OpenEdge RDBMS.
Separate audit security to restrict who has access to the audit data
records and who can archive, load, and remove them.
Assert the user ID to use for auditing from external authentication
systems (not dependent on _User).
Enable optional run-time checking of OpenEdge RDBMS table and
field permissions.
Tune auditing through audit policy, index deactivation, archiving,
and the use of separate audit storage areas.
Optionally group audit data by user-defined group events and/or
user- defined application context.
Query audit data through standard query mechanisms (for
example, ProDataSets).
Leverage fast (binary) audit archiving capability and/or write your
own audit data archive utility.
OE10.1A - December, 2005

Authentication and identity management


By default, OpenEdge provides support for
authenticating and authorizing user IDs that
are defined for use as database connection
IDs in the _User table, the OpenEdge
internal authentication system.
Starting with Release 10.1A, OpenEdge also
provides support for authorizing user IDs
that are defined and authenticated in
external authentication systems that
you access in your 4GL application.
OE10.1A - December, 2005

Adding new fields and inactive indexes to
existing database tables
 In previous releases, the New Objects option
allowed you to add a new table to an online
database.
 In Release 10.1A, you can add new fields and

inactive indexes to existing tables while the
OpenEdge database remains online.
You can add new fields and inactive indexes even if
another user is accessing or modifying records in the
table you are updating. This feature is implemented
via the Data Administration tool:

Select Admin? Load Data And Definitions? Data
Definitions (.df file) option.
OE10.1A - December, 2005

SESSION:SCHEMA-CHANGE attribute



Set this attribute to “NEW OBJECTS” to activate this feature;
and to the empty string ("") or the Unknown value (?) to
deactivate this feature.
Relaxes the requirement for exclusive access to a database
in order to make the following schema changes online:

Add new sequences.

Add new tables, as well as any associated fields,
indexes, and database triggers (which must be added
within the same transaction).

Add new fields to an existing table. (You cannot define
ASSIGN triggers for new fields while the database is
online.)

Add new inactive indexes to an existing table.
DEMO ?
OE10.1A - December, 2005
Release 10.1A new startup parameters:
After-image File Management Archive Directory List
(-aiarcdir)



Specifies the directories where the AI File Management
utility writes the archived after-image files.
After-image File Management Archive Directory
Create (-aiarcdircreate)

Directs the AI File Management utility to create the specified
directories.
After-image File Management Archive Interval
(-aiarcinterval)



Specifies on-demand mode archiving or the extent switch interval for
timed-mode archiving.
Cluster Mode (-cluster)

Starts a cluster-enabled database.
OE10.1A - December, 2005
Release 10.1A updated startup parameters:
 After-image Buffers (-aibufs)


Blocks in Database Buffers (-B)


The multi-user default value is the maximum value of
3000 and 8*users.
Before-image Buffers (-bibufs)


The multi-user default value is now 20.
The multi-user default value is now 20.
Number of Buffers for Temporary Tables (-Bt)

The single-user and multi-user default value is now
255.
OE10.1A - December, 2005
Release 10.1A updated startup
parameters (cont.):
 Lock Timeout (-lkwtmo)


Semaphore Sets (-semsets)


On the AppServer, the client gets a STOP
condition if this value is exceeded.
The multi-user default value is now 3.
Spin Lock Retries (-spin)

The multi-user default value is 10,000 for
single CPU systems and 6000*# of CPUs for
multi-CPU systems.
OE10.1A - December, 2005
Utility enhancements for increased
performance and online availability

Enhancements improve system availability
by


either removing the need to take the database
offline to perform maintenance, or
by increasing the performance of the utility,
thus reducing the amount of time a database
must be offline.
OE10.1A - December, 2005
Performance and online availability
 Binary Dump


PROUTIL DUMP is enhanced to work against an online database
and is multi-threaded to improve performance.
Binary Load
PROUTIL LOAD is enhanced to accept as input a list of binary
dump files generated by a multi-threaded binary dump.
-dumplist dumpfile


Specifies a file containing a list of fully qualified binary dump files to
load. Use the file created by PROUTIL DUMP, or create one.
/usr1/docsample/101A/bindump/order.bd
/usr1/docsample/101A/bindump/order.bd2
/usr1/docsample/101A/bindump/order.bd3
/usr1/docsample/101A/bindump/order.bd4
/usr1/docsample/101A/bindump/order.bd5
/usr1/docsample/101A/bindump/order.bd6
OE10.1A - December, 2005

Index Rebuild




PROUTIL IDXBUILD is multi-threaded to improve
performance.
-threadnum n (Default: # of CPU)
activeindexes | inactiveindexes
Online Index Activation

PROUTIL IDXACTIVATE enables you to activate an
inactive index while your database is online.
OE10.1A - December, 2005

Database structure maintenance – PROSTRCT:



PROSTRCT STATISTICS can be run against an online
database. The output provides a snapshot of the database at
the time the command is run.
PROSTRCT ADDONLINE allows you to add extents to
an online database.
The -validate option to PROSTRCT ADD, PROSTRCT
ADDONLINE, and PROSTRCT CREATE

allows you to verify the syntax of your structure file, and to
confirm with your operating system that sufficient disk space is
available to create the extents defined in your structure file,
before you execute the command.
OE10.1A - December, 2005

Log file truncation


PROLOG is enhanced with a -online option
that enables you to truncate a log file while
the database is online.
PROQUIET without locking

PROQUIET is enhanced with a nolock
qualifier to the enable option that enables
you to quiet the database without waiting
for shared memory latches.
OE10.1A - December, 2005
After-image file management

The utility archives FULL AI extents to a userspecified location, maintains a log file to aid in ROLL
FORWARD.



Is tightly integrated with Fathom Replication.
The automatic mode allows users with little or no
experience with after imaging to quickly get started
and the utility handles AI extent archival.
In the manual mode, the user has greater control
over the process of archiving AI extents.
OE10.1A - December, 2005
After-image file management
rfutil mydb -C aiarchiver enable
proserve mydb
-aiarcdir /usr1/aiarchives/mydb/,
/usr2/aiarchives/mydb/
-aiarcinterval 120
 DEMO ?
OE10.1A - December, 2005
Saving Key Database Events
 Saving key events can provide a database
administrator or a Progress technical support
engineer an efficient record of critical events in the
history of your database
proutil mydb -C enablekeyevents ”Key Event Area”
_KeyEvt table fields:
_KeyEvt-Date
_KeyEvt-Procid
_KeyEvt-ThreadId
_KeyEvt-MsgId
_KeyEvt-Usrtyp
_KeyEvt-Usrnum
_KeyEvt-Event
DATETIME-TZ
INTEGER
INTEGER
INTEGER
CHARACTER
INTEGER
CHARACTER
OE10.1B - December, 2006
(SP 03, Nov 15, 2007)
New startup parameters:
 Maximum Area Number (-maxAreas)


Lets you specify the highest area number available
for use when the database is online.
No INT64 (-noint64)

Lets you execute Release 10.1B r-code with
Release 10.1A executables, for applications that
have INT64 data type incompatibilities.
OE10.1B - December,
2006
Release 10.1B provides essential RDBMS enhancements
that increase capacity and availability:
 Removal of the 2 billion row limit — large table
support.


This feature removes the historical 2 billion row limit.
Large table support depends on the following 64-bit
features:





64-bit dbkeys
64-bit data type support
64-bit sequence value support
Database utility support
VST support
OE10.1B - December, 2006

proutil <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 ***
OE10.1B - December, 2006
Revert utility—Conversion to 10.1A database
The number of addressable rows in a Type
II storage area increases to more than 9
quintillion, which is 9 followed by 18 zeros, or
2>63-1.



The physical limits of storage areas will be
reached prior to this addressable limit.
The expanded the size of the dbkeys alters
the format of before-image notes, afterimage notes, and backup records.
OE10.1B - December, 2006
INT64 data type
 This data type is separate from the existing
32-bit data type INTEGER.
 The SQL signed 64-bit integer is BIGINT.
 The INT64 data type is compatible with all
current functions and statements that
currently support the 32-bit integer.
 You can change existing INTEGER fields to
INT64 without dumping and loading the
column and without rebuilding any indexes
that include the modified field.
OE10.1B - December, 2006
64-bit sequence value support
 In concert with the introduction of the
INT64 data type, sequence support is
expanded to include INT64.


New databases created with Release 10.1B
have this support by default.
For databases migrated from a previous
release, you can enable support for 64-bit
with the PROUTIL ENABLESEQ64
command.
OE10.1B - December, 2006
Large key entries

The size of an index key is increased to
approximately 2000 bytes in with a minimum block
size of 4K.
 Before 192 byte limit:
The total length of the fields in an index exceeds max key size. (129)
A PROGRESS index consists of one or more fields. For an individual
record, the sum of the lengths of all fields in an index cannot be
more than 188.

To enable support of the increased index key size in
existing databases with a minimum blocksize of 4K,
use either the Data Administration tool or use the
PROUTIL ENABLELARGEKEYS command.
OE10.1B - December, 2006
Increased maximum area number
 The maximum area number available is
increased to 32,000.


Prior to Release 10.1B, the maximum area
number for a database was 1000.
You can set the maximum area number
to a smaller value with the Maximum
Areas (-maxareas) database startup
parameter.
OE10.1B - December, 2006
Shared memory allocation




In Release 10.1B and forward, shared memory
segment allocation methodologies are dynamic. Prior
to this release, shared memory segments had a fixed
maximum size; now, the optimum size of shared
memory segments is dynamically calculated at
runtime.
The broker attempts to allocate the largest possible
segment size and the fewest number of segments to
meet the shared memory requirements.
The result is that larger amounts of shared memory
can be allocated more efficiently.
An added benefit is that you can make more selfservice database connections with large shared
memory.
OE10.1B - December, 2006



You can specify the maximum size of
shared memory segments with the shmsegsize <n> server parameter.
Specifying shared memory segment size can
improve performance.
Increasing the size of the shared memory
segments decreases the number of segments
allocated, in turn decreasing the system
resources needed to manage the segments.
OE10.1B - December, 2006
Turn on AI while online
 The PROBKUP utility now includes the ability
to enable after-imaging during the execution
of a full online backup.
probkup online dbname output-device
enableai

rfutil x -C aimage aioff (introduced in 10.0a)
Enhanced Record Lock Release
 Improves logout and transaction end
OE10.1B - December, 2006
Monitoring user I/O by object

Release 10.1B includes two new VSTs that let you monitor table
and index activity on a per user basis. These VSTs provide
insight into individual user activity, and provide data for
evaluating query efficiency.
_UserTableStat
_UserIndexStat


For databases created with prior releases, you must update
your VSTs with PROUTIL UDPATEVST to include these tables.
Huom! Protop sisältää nämä

http://www.greenfieldtech.com/articles/protop.shtml
OE10.1B - December, 2006
Runtime Memory And Block Checking




A means of protecting the database from hardware
and software “bugs”
Earlier detection (in memory) of data
inconsistencies at a block level before disk
Enabled through startup parameters
Enabled/disabled online through

promon 8 R&D 8 Administrative Functions 8
“8. Adjust consistency checks”
OE10.1B - December, 2006

Database level “-DbCheck”


Area level “-AreaCheck <area name>”


all index blocks of the specified index
Table level “-TableCheck <table name>”


all index and record blocks in the specified area (except LOB
blocks)
Index level “-IndexCheck <index name>”


all index and record blocks (except LOB blocks)
all record blocks of the specified table (except LOB blocks)
Memory protection “-MemCheck”

any potential memory violations in the buffer pool of all index
and record blocks
OE10.1C - February 2008
(SP 03, Jan 30, 2009)
Internet Protocol (IP) version (-ipver)
startup parameter
 Use the Internet Protocol (IP) version (-ipver)
startup parameter to specify the IP version
your client or server will use for network
communications.
 ipv4 (ipv6)
OE10.1C - February 2008
Client database request statement caching
 This functionality enables the database administrator
to monitor ABL and SQL driven database activity
using PROMON or the _Connect Virtual System
Table (VST).
OpenEdge Release 10 Monitor (R&D)
Client Database-Request Statement Caching Menu

The database administrator tracks client activity and
identifies line numbers within an ABL program (or
SQL statement) associated with database requests.
OE10.1C - February 2008
Database resilience
 The OpenEdge RDBMS expands the range of
internal validations used to ensure database
consistency in both index and data blocks
during forward processing.
 Validations within utilities that scan the
database have been similarly enhanced, and
can be run online as part of routine health
checks.
OE10.1C - February 2008
Increase startup parameters online
 The following startup parameters can be increased
while your database server is online:





Blocks in database buffers (-B)
After-image buffers (-aibufs)
Before-image buffers (-bibufs)
Lock table entries (-L)
Shared-memory overflow size (-Mxs)
The set parameter value increases with PROUTIL
INCREASETO.
proutil db-name -C increaseto
-B n -L n -bibufs n -aibufs n -Mxs n

OE10.1C - February 2008
Large system files
 The ABL INPUT FROM and OUTPUT TO
statements have been updated to allow for
files greater than 2GB, if the platform allows
it.
OUTPUT TO largetable.d.
FOR EACH largetable NO-LOCK:
EXPORT largetable.
END.
OUTPUT CLOSE.
OE10.1C - February 2008
Index rebuild packing factor


Max % of space used
Avoids costly index block splits
proutil <db> -C idxbuild –pfactor
<60 – 100>
OPS-3: What’s New in
10.1 RDBMS?
OE10.2A - November, 2008
New platform—Windows 64-bit
OpenEdge Release 10.2A includes support for a new
operating system, Microsoft’s Windows Server 2008
x64, or Windows 64-bit.
 This first release of OpenEdge on Windows 64-bit is
considered a “server-only” release. This release
provides access to memory in excess of 2GB to
database servers, WebSpeed, AppServer, batch
clients, and character clients.
./..
