Transcript OPS-10: Moving V8/V9 RDBMS to OpenEdge 10 ®

OPS-10: Moving V8/V9 RDBMS to
OpenEdge® 10
Rob Marshall
Principal Solutions Consultant
What’s in OpenEdge RDBMS?
Performance
• Type II Storage Areas
• Fast Drop & Temp tables
• Increased shmem –B 1 billion
• Internal algorithmic enhancements
• Buffers, Locks, Indexing
• Improved APW scanning
• Auto Record Defrag
• Enhanced Txn Backout
• New Defaults
Datatype Support
• BLOB, CLOB
• Datetime, Datetime-TZ
• INT64 (no conversion)
2
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
Visibility
• Log File
• New format
• Significant events
• Improved management
• Db I/O by User by Object
• Database Describe
Large Database Support
• 64 bit Rowids
• 64 bit Sequences
• 64 bit Integer Datatype
• Large Index Key Entries (1970)
• 32,000 areas
• 8 TB Shmem
© 2008 Progress Software Corporation
What’s in OpenEdge RDBMS?
High Availability
• Online Schema adds
• Sequences
• Tables
• Fields
• Indexes w/fast fill
• Online space management
• Enabled/Disable AI online
• Enable AI Mgmt online
• HA Clusters Bundled
Maintenance
• Index Rebuild
• By area, table, active status
•.st file syntax checker
• AI Management
• Multi threaded utilities
• idxbuild, binary dump
• Binary Dump without index
• Binary Load Performance
• Index Fix with NO-LOCK
Security
• SSL
• Auditing
3
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Agenda




4
General Migration Strategy
The Fast and Easy Upgrade
Physical Upgrade
Tuning Opportunity
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Basic Steps
First detail plan, review plan, test plan THEN execute…
 Preparation
• Truncate BI, Disable AI, 2PC, and Replication (V9)
• Backup (V8/9)
 Install
• Install OpenEdge
• Don’t need to uninstall V8/9
• *Don’t overwrite your current Progress Directory
 Upgrade
• Upgrade DB to OpenEdge 10
• Do your backups !!!!
• Recompile/Re-deploy your ABL code if client is OpenEdge
 Run the Application and Test, Test and….. Test
5
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
The Application
 Safe
 Install OpenEdge on your existing test machine
 proutil <test db> -C conv910*
 Recompile application* and test
 Fast and Loose
 Upgrade a remote or local client site
 Recompile application* and test
 The Rollout
• Upgrade remote systems with OpenEdge
 Remote client server, Remote Application servers
 “re-deploy” newly built application (PROPATH) and test
* ABL code needs to be recompiled/re-deployed only when upgrading the client to
R10. In 3-tier configurations (with AppServer™) the client could still be V9. Not
possible via SQL or V8.
* You will have to convert a V8 db to V9 before converting to OpenEdge
6
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Agenda




7
General Migration Strategy
The Fast and Easy Upgrade
Physical Upgrade
Tuning Opportunity
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Deployment
 Preparation
•
•
•
•
Truncate BI
Disable AI, 2PC, Replication (V9)
Backup database (V8/9)
Validate backup
 Install OpenEdge 10 on server machine
• And everywhere else! (Clients must be upgraded before
the server can be)
 Recompile and re-deploy application (if need be)
• Client/Server V9 to OpenEdge 10 disallowed
• V9 Clients to R10 AppServer to R10 Database is allowed,
No SQL V9 to OpenEdge permitted
8
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Connectivity:
Progress® V9 and OpenEdge 10
 ABL mixed configurations: Progress V9 and OpenEdge
10 are supported
• One version back (clients to servers)
• One version forward (clients to Application Server)
 SQL must match
Client
NEW in
OpenEdge 10
10
10
10
9
9
Application Server
Database
10
10
9
10
10
10
9
9
10
9
Refer to product by product information for further details
9
© 2008 Progress Software Corporation
Database Conversion
 Run conversion utility
_proutil <db> -C conv910 –B 512
 Conversion runs in 5-minutes or less
 Basically just a schema upgrade
• No changes to user records or indexes
• No changes to physical structures
 Backup database
 Re-start database and application
10
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
 You are now “Good To Go” with OpenEdge
 No physical changes to existing user data
• Data remains in Type I storage areas
• Data fragmentation exists as before
 Optimize physical layout when time permits...
11
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Agenda




12
General Migration Strategy
The Fast and Easy Upgrade
Physical Upgrade
Tuning Opportunity
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Storage Areas?
 Performance, Scalability & Maintenance
 Take advantage of new features
 No adverse application effects
• Physical reorg does NOT change the
application
• Object location is abstracted from the
language by an internal mapping layer
• Different physical deployments can run with
the same compiled r-code
13
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
How to Get There
 Preparation (same as before)
• Truncate BI, disable AI, backup, validate, install…
 Before Physical Reorg
• Upgrade database to OpenEdge 10
– conversion utility
– prostrct create (a must if changing block size)
 Physical Updates (no r-code changes required)
• Separate schema from user data
• Create new storage areas
– Specify records per block
– Specify Type II cluster sizes
14
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
How to Get There
 Physical Reorg
• Spread data out amongst new areas
• Move indexes
 Online options vs offline options
• Database block size changes are offline
 After Reorg
• Reclaim Unused Space
– Truncate old data area
– Delete old data area
15
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
How to Get There
 In Place (same database)
•
•
•
•
Transformation done all at once or over time
Add new storage areas to existing database
Migrate data from old areas to new
Reclaim space from old areas
 New database
•
•
•
•
•

Transformation done in one maintenance window
Dump old data
Create new database
Load into new database
Prodel old database
Mix of Option #1 and Option #2 (custom)
• Create new database
• Move data from old database to new database
• Reclaim space by deleting old database
16
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Getting started
Separate user data from schema
17
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Moving schema tables
Separate Schema from user data (in place):
proutil <db> -C mvsch (offline operation)
Renames existing
schema area
Schema Area
Old Default Area
18
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Moving schema tables
Separate Schema from user data:
proutil <db> -C mvsch (offline operation)
Renames existing
schema area
Schema Area
Creates new
schema area
Moves schema
Tables & Indexes
19
Old Default Area
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
Schema
Area
© 2008 Progress Software Corporation
Moving schema tables
Separate Schema from user data:
proutil <db> -C mvsch (offline operation)
Renames existing
schema area
Schema Area
Creates new
schema area
Moves schema
Tables & Indexes
Schema
Old Default Area
Area
You move data
To new areas
You truncate
Old Default Area
20
User
User
User
User
Area
Area
Area
Area
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Physical Changes:
Location, Location, Location
 Create .st file with new layout
• Set records per block
 Use Type II Storage Areas
• Tables 64 or 512 block clusters
• Indexes 8 or 64 block clusters
d “Cust/Bill Indexes“:7,1;8 /d_array2/myDB_7.d1 f 512000
d “Cust/Bill Indexes“:7,1;8 /d_array2/myDB_7.d2
#
d “Customer Data“:8,16;64 /d_array2/myDB_8.d1 f 1024000
d “Customer Data“:8,16;64 /d_array2/myDB_8.d2
#
d “Billing Data“:9,32;512
/d_array2/myDB_9.d1 f 1024000
d “Billing Data“:9,32;512
/d_array2/myDB_9.d2
21
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Physical Changes
 Validate first
prostrct add <db> new.st -validate
The Structure file format is valid. (12619)
 Then update
prostrct add <db> new.st
OR:
prostrct addonline <db> new.st
22
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Moving Tables and Indexes
3 Options for Data Movement
1. Table move and Index move
•
Online (by primary index)
2. Dump and Load (D&L)
•
•

Suggestion: Mix of option #1 and #2
•
•
•
23
With or without index rebuild
Application must be offline
1st purge/archive unneeded data
Table move small tables (number of blocks)
D&L everything else
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Option #1: Table/Index Move
Pros and Cons
 Advantages:
•
•
•
•
•
Online (with no-lock access)
Dump & load in one step
Schema is automatically updated
No object # changes to deal with
Parallelism
 Disadvantages:
•
•
•
•
Only No-lock accessible during move
Moves but doesn’t “rebuild” indexes
Too slow for large tables
Changes are logged!
– BI growth may be of concern
24
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Table Move
proutil <db> -C tablemove
[ owner-name . ] table-name table-area
[ index-area ]
 Move/reorg a table by its primary index
 Move a table AND its indexes
• Preferred performance
 Fast for small tables
25
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Index Move
proutil <db> -C idxmove
[ owner-name . ] table-name . index-name
area-name




26
Move an index from one area to another
Does NOT alter/optimize index block layout
Fast but does not “rebuild” indexes
Online but changes to owning table blocked
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Option #2: Dump and Load
3 Dump and Load Flavors
 Textual Data
• ASCII dump
• ASCII load
• Bulk load followed by index rebuild
 Binary
• Binary dump
• Binary load
– With index rebuild
– Followed by index rebuild
 Custom (Textual or raw)
• D&L with triggers
• Buffer-Copy / Raw-data-transfer / Export/Import
• Can be tricky, you may want help
27
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dump and Load General Strategy
 Create new database structure
• Add to existing DB
• New database
 Run tabanalys
 Dump table data sequence values, _User table
 Data definitions
• Dump definitions
• Modify storage area locations
• Load definitions




Load table data
Build indexes (if needed) [10.1C can specify pack]
Run tabanalys
Backup
* If you have the disk space, creating a new db saves time
28
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dumping the data
29
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dictionary Data Dump
 Database Admin tool
 OR: run prodict/dump_d.p(<table>, <dir>,<codepage>).
 Advantages:
• Fast and Easy
• Parallel
• No endian issues
 Disadvantages:
•
•
•
•
30
2 GB File size limit** Pre 10.1C
Can’t choose dump order
Have to dump entire table
Must ensure no one changes table between D&L
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Using Binary Dump
 Advantages:
•
•
•
•
•
•
•
Fastest and Easy
No 2 GB file size limit
No endian issues
Can choose dump order (by index)
Can dump table data in portions
Multi threaded (10.1B)
Can dump multiple tables concurrently (parallel)
 Disadvantages:
• Must ensure no table changes between D&L (unless
using triggers as well)
31
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Binary Dump Specified
proutil <db> -C dumpspecified <table.field> <operator> ‘fieldvalue1 AND operator value2’ <dir> -preferidx <idx-name>
 10.1B03 allows multiple values
 Switches
• table.field MUST be lead participant in index
• Valid operators: LT, GE, LE, GT, EQ
• -preferidx determines specific index to use
• -index, -thread are ignored
 Performance
• Threaded is preferred
• Can run in parallel with many unique values
 Cautions
• Avoid using descending indexes
• There is a risk of missing a range
32
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Binary Dump Specified
Finding the median value
define variable i
define variable max-recs
define variable median
as integer
no-undo.
as integer initial 0 no-undo.
as integer
no-undo.
for each mytable NO-LOCK use-index <preferred-idx>:
max-recs = max-recs + 1.
end.
median = max-recs / 2.
do i = 1 to median:
find next mytable NO-LOCK
use-index <preferred-idx>.
end.
display i substr(field1, 1, 192).
33
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Binary Dump Threaded
proutil <db> -C dump <table> <dir> -index <index #>
-thread 1 -threadnum <n>
-dumpfile <filelist> -Bp 64
 -index <n>
• Choose index based on read order
• -index 0
– Faster dump, slower read
– Assumes coming from Type II
 -thread indicates threaded dump
• # threads automatic (# CPUs)
• –threadnum max of # CPUs * 2
• Threads only available in multi user mode
• Workgroup only supports 1 thread
 -dumpfile used as input for load
34
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Don’t forget SQL
• SQL Tables and Views
These need to be dumped and loaded as well
• You can use different commands to move the
information:
• sqlschema
• sqldump
• sqlload
35
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Data Dump Completed.
Reorganize the Area/Object Configuration
36
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dump & Modify data definitions
 Use Data administration tool
 OR:
run prodict/dump_df.p(‘ALL’, ‘<mydb>.df’, ‘ ’).
If using bulk load:
run prodict/dump_fd.p(‘ALL’, ‘<mydb>.fd’).
37
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dump & Modify data definitions
 Update .df files
• Optionally delete old table
• Change table’s area information
ADD TABLE "mytable2"
AREA “Old Default Area"
DROP Table “mytable2”
ADD TABLE "mytable2"
AREA “New Data Area"
 Delete/Drop tables
 Load data definitions
• Data administration tool
• OR: run prodict/load_df.p(“<mytable>.df").
38
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Alternative Data Definition Modification
If all data in area dumped…
 Truncate objects for fast move/delete
proutil <db> -C truncate area “Old Default Area”
Warns then deletes data (but NOT schema)
 Rebuild/activate empty indexes (if moving)
proutil <db> -C idxbuild inactiveindexes
Can be done ONLINE, not just the build but the activate
 Move “empty” tables/indexes to new area
proutil <db> -C tablemove <table> <area> [ index-area ]
39
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Load the data back in (finally).
Remember to load all data files.
Be sure to validate data loaded.
40
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Loading
Things to consider...
 Enable large file support
• In the Operating System (ulimit)
• In the Filesystem / volume groups
• In the Database
41
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Bulkload
proutil <db> -C bulkload <fd-file> -B 1000 –i –Mf 10
 Data input from dictionary or custom data dump
• Mentioned here for completeness only
 Drawbacks:
• 2 GB file limit (pre 10.1C)
• Loads one table at a time (single user)
• Does not insert index entries
– Requires index rebuild as separate step
• No advantage over other loads
• Slower than all other loads
42
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Dictionary Load
 Data Administration Tool
 OR:
run prodict/load_d.p(‘table1’, ‘table1.d’).
 Data input from dictionary or custom data dump
• 2 GB file limit per load (pre 10.1C)
 Load data in parallel (to separate tables)
• Inserts index entries
• Index tree not perfect
 Performance close to binary load + index rebuild
• (when loading multiple tables)
43
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Binary Load
proutil <db> -C load <table>.bd [build]
 Load to new or truncated area
• Truncated rather than “emptied”
 Parallel load to different tables
• Same or different areas without scatter! When using
Type II Areas
 Optionally load with build indexes
• Somewhat better performance
44
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Binary Load
From a threaded dump or dumpspecified…
proutil <db> -C load <table>.bd
-dumplist <filename>
 Dump List File:
/usr1/db/mytable.bd
/usr1/db/mytable2.bd
/usr1/db/mytable3.bd
 Must load ALL dumps (.db, db2, .db3, …)
45
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Tuning the Process
Tune for high (non-recoverable) activity
 Dump with
• –RO, high –B and/or -Bp
• Dump on index with fewest # blocks (if possible)
 Load with
•
•
•
•
High –B, –r** or –i**
BIW, 1.5 APW’s per CPU,
Very Large BI clusters with 16K BI blocks
No AI/2PC
 Spread data, BI and temp files across disks /
controllers
** only use -r & -i when complete data recovery possible
46
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
After the Load
Build Indexes (where applicable)
proutil <db> -C idxbuild [ all |table <table> | area <area>
schema <owner> | activeindexes | inactiveindexes] [thread n] [-threadnum n] [-T <dir>] [-TM n] [–TB
<blocksize>] [-B n] [-SG n] [-SS <file>] [-pfactor n]
 Many new idxbuild choices
 Helpful parameters
• -SG 64 (sort groups)
• -SS filename (file containing sort file list)
• -TM 32 (merge buffers)
• -TB 31 (temp block size)
• -B 1000
 Run tabanalys
• validate # records
 Backup your database
47
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Reclaim space
For areas that were emptied…
proutil <db> -C truncate area <area-name>
Warns then deletes data
proutil <db> -C truncate area
Only truncates “empty” areas (but all of them)
 Area logically truncated (option #2)
 Extents can be deleted
prostrct remove <db> d <old-area-name>
48
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
After the Load
Think your done…
Don’t Forget....
Run UPDATE STATISTICS for SQL/ODBC
49
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Agenda




50
General Migration Strategy
The Fast and Easy Upgrade
Physical Upgrade
Tuning Opportunity
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Tuning Opportunity
 -Bt (temp tables are Type II storage areas) – client
parameter
 10.1B+ changes default Temp table block size
• From 1K to 4K
• –tmpbsize 1 restores old behavior – client parameter
 Monitor BI Cluster Size
• BI notes are bigger in OpenEdge 10
 BI grow
51
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
In Summary
 Conversion is quick
• Physical Upgrade at your leisure
• Lots of physical re-org options
 Rollout can be simple
 10,000+ customers on OpenEdge
52
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Relevant Exchange Sessions








53
OPS-1: DBA 101 - How Healthy is Your Database Today?
OPS-8: Alerts, Alarms, Pages and Harbingers of Trouble…
OPS-14: Effective OpenEdge Database Configuration
OPS-18: Data Management and Platforms Roadmap
OPS-19: What is IPv6 and Why Should I Care
OPS-20: Data Management and Platforms Info Exchange
OPS-23: OpenEdge Performance Basics
OPS-28: A New Spin on Some Old Latches
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
?
Questions
54
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
Thank You
55
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation
56
OPS-10: Moving V8/V9 RDBMS to OpenEdge 10
© 2008 Progress Software Corporation