Transcript MOVE-4: Upgrading Your Database to OpenEdge® 10 Gus Björklund

MOVE-4: Upgrading Your Database to
OpenEdge® 10
Gus Björklund
Wizard, Vice President Technology
Audience Survey
Release
When
Age
7.2A
long ago
ancient
8.2A
long ago
ancient
8.3A
long ago
ancient
9.0A
Dec 1998
7.5 years
9.1A
Dec 1999
6.5 years
10.0A
Dec 2004
2.5 years
10.1A
Jan 2006
0.5 years
MOVE-4: Upgrading Your Database to OpenEdge® 10
2
© 2006 Progress Software Corporation
General Upgrade Strategy
 Backup
 Keep dbanayls & promon data for reference
 Install OpenEdge 10 (no need to overwrite V9)
 Upgrade DB to 10
 Run UPDATE STATISTICS for SQL
 Do your backups !!!!
 Recompile 4GL code
 Run your application
MOVE-4: Upgrading Your Database to OpenEdge® 10
6
© 2006 Progress Software Corporation
The 5 Minute Upgrade …
MOVE-4: Upgrading Your Database to OpenEdge® 10
7
© 2006 Progress Software Corporation
Database Server Conversion Steps
 Upgrade clients first, then database server
 Preparation
• Backup database
• Truncate BI, Disable AI
 Install OpenEdge 10
 Run conversion utility
 Backup database
 Start database
MOVE-4: Upgrading Your Database to OpenEdge® 10
8
© 2006 Progress Software Corporation
The 5-Minute Rule
 Conversion program runs in-place, in 5minutes or less
 Mostly, we upgrade the schema tables
• No changes to records or indexes
• No changes to physical structures
MOVE-4: Upgrading Your Database to OpenEdge® 10
9
© 2006 Progress Software Corporation
Database convert
proutil <db> -C conv910 -B 512
probkup <db> …
MOVE-4: Upgrading Your Database to OpenEdge® 10
10
© 2006 Progress Software Corporation
What Happens after 5-minutes?
 You can run the database !
 It will very likely run better than before
 Fragmentation may still exist
 Most of your DBA scripts should work fine
 All data in Type I data areas
 Optimize when time permits ...
MOVE-4: Upgrading Your Database to OpenEdge® 10
11
© 2006 Progress Software Corporation
What if I want more performance
than I get with the fast convert ?
Do some more work !
MOVE-4: Upgrading Your Database to OpenEdge® 10
12
© 2006 Progress Software Corporation
Move data to type ii data areas …
MOVE-4: Upgrading Your Database to OpenEdge® 10
13
© 2006 Progress Software Corporation
Moving to Type II Data Areas
 Before:
• First upgrade with conversion utility
• Move schema tables
• Create type ii data areas
 Move tables and indexes
 After
• Truncate old data area
• Delete old data area
MOVE-4: Upgrading Your Database to OpenEdge® 10
14
© 2006 Progress Software Corporation
Before the move …
MOVE-4: Upgrading Your Database to OpenEdge® 10
15
© 2006 Progress Software Corporation
Moving schema tables
 proutil <db> -C mvsch
 Renumbers existing schema area
 Creates new schema area no. 6
 Copies schema tables
 Deletes old schema tables
MOVE-4: Upgrading Your Database to OpenEdge® 10
16
© 2006 Progress Software Corporation
Moving Tables and Indexes
 Tablemove and Indexmove
 Dump and Load
MOVE-4: Upgrading Your Database to OpenEdge® 10
17
© 2006 Progress Software Corporation
Storage Settings
 data block size: 4k or 8k
 bi & ai block size should match each other
 Type II Data Area Cluster Sizes
• Table areas: 512
• Index areas: 512
 Dump and load required to change data block
size
MOVE-4: Upgrading Your Database to OpenEdge® 10
18
© 2006 Progress Software Corporation
Using Table and Index Move …
MOVE-4: Upgrading Your Database to OpenEdge® 10
19
© 2006 Progress Software Corporation
Table Move
proutil <db> -C tablemove
[owner-name.]table-name table-area
[ index-area ]
Can move just table, or
table and its indexes (preferred)
MOVE-4: Upgrading Your Database to OpenEdge® 10
20
© 2006 Progress Software Corporation
Index Move
proutil <db> -C idxmove
[owner-name.]indexname area-name
MOVE-4: Upgrading Your Database to OpenEdge® 10
21
© 2006 Progress Software Corporation
After the move …
MOVE-4: Upgrading Your Database to OpenEdge® 10
22
© 2006 Progress Software Corporation
Truncating area
 proutil <db> -C truncate bi
proutil <db> -C truncate area <area-name>
 Area logically truncated
 Extents can be deleted
prostrct remove <db> <extent-type> <areaname>
MOVE-4: Upgrading Your Database to OpenEdge® 10
23
© 2006 Progress Software Corporation
If You can’t tablemove:
Dumping and Loading …
MOVE-4: Upgrading Your Database to OpenEdge® 10
24
© 2006 Progress Software Corporation
Dump and Load Strategy

Before:
• Backup
• Upgrade with conversion utility
• Move schema tables






Dump definitions and tables
Create new database with type ii data areas
Load definitions
Load table data
Build indexes
Backup
MOVE-4: Upgrading Your Database to OpenEdge® 10
25
© 2006 Progress Software Corporation
Dump data definitions
 Use Data Administration tool
 Dump all table and index definitions
• produces .df files
MOVE-4: Upgrading Your Database to OpenEdge® 10
26
© 2006 Progress Software Corporation
Dump/Load Choices
 Dictionary Dump (to text)
• Dictionary load
• Bulkload
 Custom Dump Programs
• Custom loader
 Custom buffer-copy Programs
 Binary Dump
• Binary load
MOVE-4: Upgrading Your Database to OpenEdge® 10
27
© 2006 Progress Software Corporation
Dictionary Dump
 Easy
 Slow
 2 GB File size limit
 Can’t choose dump order
 Have to dump entire table
MOVE-4: Upgrading Your Database to OpenEdge® 10
28
© 2006 Progress Software Corporation
Bulkload
 proutil <db> [-yy n] -C BULKLOAD fd-file -B
1000
 Use dictionary or custom code to export data
• 2 GB file limit
 Loads one table at a time
 Faster than dictionary load
 Slower than binary load
MOVE-4: Upgrading Your Database to OpenEdge® 10
29
© 2006 Progress Software Corporation
Custom Dump Programs
 Have to write yourself (but not too hard)
• Use EXPORT and IMPORT statements
 Can dump subset of data
 Can run multiple loads concurrently
• but not into type i data areas
MOVE-4: Upgrading Your Database to OpenEdge® 10
30
© 2006 Progress Software Corporation
buffer-copy
 Have to write yourself
 Planning required
 Can do while applications use old database
 Can copy part of data
 Can choose dump order via index
 No dump file needed
 Can load multiple tables concurrently
MOVE-4: Upgrading Your Database to OpenEdge® 10
31
© 2006 Progress Software Corporation
Binary Dump and Load
 Easy
 No 2 GB file size limit
 Can choose dump order (by index)
 Can dump part of data
 Can load multiple tables concurrently
MOVE-4: Upgrading Your Database to OpenEdge® 10
32
© 2006 Progress Software Corporation
Using Binary Dump and Load …
MOVE-4: Upgrading Your Database to OpenEdge® 10
33
© 2006 Progress Software Corporation
Binary Dump and Load



Dump from type I data areas
Create new database structure
Load in to type ii data area

proutil <db> -C dump <table> . –index 0
• Dump performance vs Read performance
• Choose an index based on read order instead
• 10 x differences
proutil <db> -C load <table>.bd build
• Load with build indexes
• Load to truncated or new area

– (truncate rather than “emptied”)
MOVE-4: Upgrading Your Database to OpenEdge® 10
34
© 2006 Progress Software Corporation
Large Databases: Dump and Load
Fastest Overall Process
 Binary dump and load
 Multiple streams (3-5 per CPU)
 Dump on smallest index (# blocks)
 Index rebuild in same step as load
• saves one phase
• may be faster: YMMV
 Parallel load into separate type II data areas
when possible
• But a minor point
MOVE-4: Upgrading Your Database to OpenEdge® 10
35
© 2006 Progress Software Corporation
Large Databases: Dump and Load
Fastest Overall Process




Tune for high activity:
Dump with
• –RO (ymmv) and high –B
Load with
•
•
•
•
•
high –B, –r
1-2 APW’s per CPU,
-pwqdelay 10, -pwscan 1024, -pwwmax 1024
16K BI blocks, large clusters,
no ai/2PC
At every step, spread activity across disks /
controllers
MOVE-4: Upgrading Your Database to OpenEdge® 10
36
© 2006 Progress Software Corporation
After the load …
MOVE-4: Upgrading Your Database to OpenEdge® 10
37
© 2006 Progress Software Corporation
After Loading You Need To
 build indexes
•
•
•
•
can do in groups or all at once
by area, by table, by owner
by area might make sense
don’t forget sort scratch space (-SS)
 backup your database
MOVE-4: Upgrading Your Database to OpenEdge® 10
38
© 2006 Progress Software Corporation
Index Rebuild After Load

Index Rebuild by area, table, or schema
proutil <db> -C idxbuild table <table>
proutil <db> -C idxbuild area <area>

Other values
•
•
•
•
•
-SG 64 (sort groups)
-SS filename (scratch space location list)
-TM 32 (merge buffers)
-TB 32 (temp block size)
-B 1000
MOVE-4: Upgrading Your Database to OpenEdge® 10
39
© 2006 Progress Software Corporation
Review …
MOVE-4: Upgrading Your Database to OpenEdge® 10
40
© 2006 Progress Software Corporation
Summary
 10.1A RDBMS is
better, stronger, faster
even with NO application changes
 Conversion is quick
• Optimise at your leisure
 Upgrade when you go home
MOVE-4: Upgrading Your Database to OpenEdge® 10
41
© 2006 Progress Software Corporation
More Info
 Related sessions
• DB–8: Highly Parallel Dump and Load
• MOVE–14: Migrating Your Authentication
System to OpenEdge 10.1A and Beyond
• INNOV–1: OpenEdge 10.1A Overview
• INNOV–12: OpenEdge Database Product
Roadmap
MOVE-4: Upgrading Your Database to OpenEdge® 10
42
© 2006 Progress Software Corporation
Want
Answers
MOVE-4: Upgrading Your Database to OpenEdge® 10
43
© 2006 Progress Software Corporation
MOVE-4: Upgrading Your Database to OpenEdge® 10
44
© 2006 Progress Software Corporation