ops-10: moving v8/v9 rdbms to openedge ® 10 rob marshall principal solutions consultant

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

Rob MarshallPrincipal Solutions Consultant

© 2008 Progress Software Corporation2 OPS-10: Moving V8/V9 RDBMS to OpenEdge 10

What’s in OpenEdge RDBMS?

• BLOB, CLOB• Datetime, Datetime-TZ• INT64 (no conversion)

• 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

• Log File• New format• Significant events• Improved management

• Db I/O by User by Object• Database Describe

• 64 bit Rowids• 64 bit Sequences• 64 bit Integer Datatype• Large Index Key Entries (1970)• 32,000 areas• 8 TB Shmem

Performance Visibility

Large Database Support

Datatype Support


What’s in OpenEdge RDBMS?

• Online Schema adds• Sequences• Tables• Fields• Indexes w/fast fill

• Online space management• Enabled/Disable AI online• Enable AI Mgmt online• HA Clusters Bundled

• 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

• SSL• Auditing

High Availability

Security

Maintenance


General Migration Strategy The Fast and Easy Upgrade Physical Upgrade Tuning Opportunity

Agenda


Basic Steps

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

First detail plan, review plan, test plan THEN execute…


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



Agenda


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

© 2008 Progress Software Corporation9

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

Refer to product by product information for further details

ClientClient Application ServerApplication Server DatabaseDatabase

9109

10109

9910

91010

101010

NEW in

OpenEdge 10


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


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...



Agenda


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


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


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


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


Getting started

Separate user data from schema


Moving schema tables

Separate Schema from user data (in place):

proutil <db> -C mvsch (offline operation)

Schema Area

Old Default Area

Renames existingschema area



Separate Schema from user data:


Old Default AreaSchema

Area


Creates newschema area

Moves schemaTables & Indexes

Schema Area



Separate Schema from user data:


Old Default AreaSchema

Area


Creates newschema area

Moves schemaTables & Indexes

Schema Area

You move dataTo new areas

User

Area

User

Area

User

Area

User

AreaYou truncate

Old Default Area


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


Physical Changes

Validate first

prostrct add <db> new.st -validate

Then update

prostrct add <db> new.st

OR:

prostrct addonline <db> new.st

The Structure file format is valid. (12619)


Moving Tables and Indexes

1. Table move and Index move• Online (by primary index)

2. Dump and Load (D&L)• With or without index rebuild

• Application must be offline

Suggestion: Mix of option #1 and #2• 1st purge/archive unneeded data

• Table move small tables (number of blocks)

• D&L everything else

3 Options for Data Movement


Option #1: Table/Index Move

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

Pros and Cons


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


Index Move

proutil <db> -C idxmove[ owner-name . ] table-name . index-

name

area-name

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


Option #2: Dump and Load

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

3 Dump and Load Flavors


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


Dumping the data


Dictionary Data Dump

Database Admin tool OR: run prodict/dump_d.p(<table>, <dir>,<codepage>).

Advantages:• Fast and Easy• Parallel• No endian issues

Disadvantages:• 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


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)


Binary Dump Specified

proutil <db> -C dumpspecified <table.field> <operator> ‘field-value1 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


Binary Dump Specified

Finding the median value

define variable i as integer no-undo.define variable max-recs as integer initial 0 no-undo.define variable median 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).


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


Don’t forget SQL

• SQL Tables and ViewsThese need to be dumped and loaded as well

• You can use different commands to move the information:• sqlschema • sqldump• sqlload


Data Dump Completed.

Reorganize the Area/Object Configuration


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’).


Dump & Modify data definitions

Update .df files• Optionally delete old table

• Change table’s area information

Delete/Drop tables Load data definitions

• Data administration tool

• OR: run prodict/load_df.p(“<mytable>.df").

ADD TABLE "mytable2" AREA “Old Default Area"

DROP Table “mytable2”

ADD TABLE "mytable2" AREA “New Data Area"


Alternative Data Definition Modification

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 inactiveindexesCan 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 ]

If all data in area dumped…


Load the data back in (finally).

Remember to load all data files.

Be sure to validate data loaded.


Loading

Things to consider...

Enable large file support• In the Operating System (ulimit)

• In the Filesystem / volume groups

• In the Database


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


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)


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


Binary Load

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, …)

From a threaded dump or dumpspecified…


Tuning the Process

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

Tune for high (non-recoverable) activity

** only use -r & -i when complete data recovery possible


After the Load

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

Build Indexes (where applicable)


Reclaim space

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>

For areas that were emptied…


After the Load

Don’t Forget....

Run UPDATE STATISTICS for SQL/ODBC

Think your done…


Agenda



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


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


Relevant Exchange Sessions

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


Questions?


Thank You

ops-10: moving v8/v9 rdbms to openedge ® 10 rob marshall principal solutions consultant

Documents