DB2 9.7.2 comes with new feature - transportable schemas. They are somewhat similar to transportable tablespaces which are old Oracle feature introduced in 8i and enhanced in 10g. Most significant improvement in 10g was making them cross platform. Cross platforms transfers require more administrator work, they are not automatic.

In Oracle you need to create metadata and hand copy database files. Then import metadata and add transfered files to target database. You might need to run into various kind of issues during metadata import.

In DB2 transportable schema works as restore (possibly redirected) to new temporary database and after restore transfer procedure will change ownerships of restored tablespaces to new database. It means that it is more special kind of database restore then entire new procedure and you can easily reuse your current database restore skills.

I will compare these data transfer features between DB2 and Oracle 10g databases in short table.

Feature Oracle 10g DB2 9.7
Cross platform transports Exported tablespace can be converted by RMAN CONVERT to target platform Rules for standard DB2 backup/restore are in place.
Transport set Tablespace files copied by hand + metadata created by exp utility. Newer Oracle can use RMAN backup images too, but its more complex task. Standard backup image containing tablespaces to be transferred and SYSCATSPACE. Can be offline or online backup and can contain more tablespaces than we are going to transfer.
Export is online operation tablespaces must be in read-only mode before they can be exported. Backup images are used, its possible to transport from online backup without downtime
Import is online operation Yes, but you need to be able to grab some exclusive locks in catalog. Target database must be in recovery mode (archivelog) with no users connected.
transport set must be self contained Yes, otherwise you get ORA-29341: The transportable set is not self-contained error during export Yes
Tied to database schemas No schema restriction are in place. You can export from any schema as long transport set is self contained. You must transport entire schema.
Data compression You must manually compress and decompress data for transfer You can use COMPRESS backup option for making data smaller
Other restrictions Character data corrupts if database charsets are not similar enough. Manual schema creation is needed before we can import transportable metadata export. no support for moving XML data.

Views: 1154


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service