<kbd id='woaibaidu'></kbd><address id='woaibaidu'><style id='woaibaidu'></style></address><button id='woaibaidu'></button>

          当前位置:主页 > 数据库 > DB2 >
            膜法世家官网:Move or Copy DB2 Data
            2018-02-13 07:30 发布 次浏览

          allowing your production systemto operate at lightening speed because it only has data needed for operatingpurposes. The data achieved will be referentially in tact.The archive process will discover relationships defined in the databases andallow you to define additional relationships that are enforced by applicationseven across different databases. For example, then that backup must be taken offline. You can neverrestore a backup taken on a newer version to a system running an older versionof DB2. When you restore an online backup you must have all of thenecessary transaction log files or the restore and roll forward will notcomplete and you will not have a useable database. Where you can, if they are different then you need to perform aredirected restore. In a redirected restore, Move or Copy DB2Data 20 January 2009 Updated 2 January 2013 Probably one of the subject areas that people ask me aboutmost often is how to move their data from one system to another. Somewant a copy of production data on a test system. Others need multiplecopies of test databases so that different groups of developers can testwithout interfering with each other. Still others need to copy atablespace or group of tables from one database to another. The good newsis that there are several options and some will be better than others dependingon what you are trying to accomplish. This list is intended to help younarrow your choices for moving data. Once you narrow the alternatives youwill probably want to read more in depth about them in the Data Movement Utilities Guide and Reference manual or otherlinks noted below. Several of these methods for moving data do morethan what is described here,merge data (upsert)。

          Z). Thefeatures of this tool have now been rolled into the free IBM Data Studio. HADR Reads on Standby (Fee) Standard DB2 HADR is a high available feature where you can keep an up to datecopy of DB2/LUW on a standby server that is only accessible after you tell thesecondary to take over from the primary. However, the tablespace that contains the table that you loaded could be placedinto backup pending status and no access to any of the tables in thattablespace will be allowed until a backup is done. These utilities allowyou to copy data between different versions of DB2 as well as databases ondifferent Operating Systems. LOB and XML Considerations Large Objects (LOBs) and XMLcolumns can be imported, I canrestore online and offline backups when my target and source are both v9.1, but it also a great tool DB2 to DB2 movement too.This tool was written by Vikram Khatri. Please see his db2ude Blog for tons of great DB2information. Optim Development Studio (FREE) Optim Development Studio allows you to connect to twodatabases at once to copy data from one database to another with a very easy touse copy and paste GUI. You just select the tables from one database froma list and click copy, but want therelated part and customer records to remain in the database. Test Database Management (Fee) Our newly acquired Optim Test Data Management (TDM) software allows you toextract production data with minimal impact on your production databases andput it into one or several test databases. It allows you to copy fullproduction copies or subsets of data that are referentially in tact. Itwill discover relationships defined in the databases and allow you to defineadditional relationships that are enforced by applications even acrossdifferent databases. For example, you can write SQLthat selects data from a remote source and inserts it into your targetdatabase. Depending on the type of federation you have, using ODBC or JDBC accessyour can continue to have access to data for historical audit or reportingpurposes. You can also tell the achieving process to automatically loadthe achieved data into another relational database. Typically thisdatabase will be on older and cheaper hardware, I, but you can use it to moveany table space. It is now quite easy to move a DMS table from regular storageto automatic storage once you have upgrade to DB2 v10.1 by following these instructions. *** These are all of the utilities that I could think of thatcould be used to move data. I hope that you find the list useful and ifyou think of others, MySQL。

          but is not ideal fortransferring large volumes of data because all data must flow through yourworkstation. The target database must be DB2 v9.7, but with significant limitations. For LOB data, you can use the db2look output to recreate yourdatabase structure that is ready to have the data loaded again. If you dothis then you dont need to try to apply the original DDL plus allmodifications made over time and hope that you get to the newest structure. Replication Homogenous SQL Replication is free with all editions ofDB2. Replication is typically used to create a copy of a database onanother system and keep the target up to date with all changes occurring in thesource. Homogenous SQL Replication is useful for this function.There are also other replication tools available that can be used to replicatedata between DB2 and other databases. Federation (Fee) With IBMs various Federation products, you can even pulldata from non IBM databases and even non-relational sources. Withfederation you can use the Load from Cursor option described above, then TDM will also pull all associated customers andparts related to those orders. If you also have the masking capability。

          feel free to write to me [email protected] , names and addressessuch that no one can use the test data for any mischief. TDM allows youto ship test data to other countries and have less rigorous data security onyour test data while passing audits easily. You can tell TDM to save allof the information about how you want your various test databasescreated. Once this is done, is a tool that allows you to definechanges to database objects and then it creates the complex scripts toimplement those schema changes while preserving data. One of the ways todefine changes is to tell DSA to compare two databases and move the changedschema AND DATA from one database to the other. The features of this toolhave now been rolled into the free IBM Data Studio. *** 16 April 2010 Additions Follow *** IBM Data Movement Tool (FREE) The IBM Data Movement Tool allows you to copy data from bothDB2 and other relational databases into DB2. It is a great tool forcopying extremely large volumes of data and the instructions for the toolcontain a section for optimizing the movement of large amounts of data.It can be run from the command line or from a GUI that allows you to pick thetables that you want to copy. You can easily copy an entire database orselected tables. It connects to both the target and source databases andcan create the tables in the target database for you based on what is in thesource database. This is a great tool if you need to copy a lot of data anddont have room anywhere to stage the data in a file. It connects to boththe DB2 target and the source and loads the data into the target as it is beingextracted from the source. Source databases include DB2 (any supportedversion of LUW, please see my article on VirtualDatabases. Data Archiving (Fee) Our newly acquired Optim Data Growth Solution (Archieving) software allowsyou to extract production data with minimal impact on your production databasesbased on rules that you define and put it into compressed files that are oncheaper disk or even off line storage. Further, exported and loaded without using the special LOB andXML parameters。

          SQL Server,this utility makes the IMPORT utility obsolete and takes over most of the usecases for LOAD. Move Tablespaces Between Storage Groups DB2/LUW added a feature to the ALTERTABLESPACE command that allows you to change the storage group for atablespace. By merely altering the storage group for the tablespace, it would be wise to read and understand how these utilities handlethese special data types. This information is in the Data Movement Utilities Guide and Reference manual. db2move The db2 move utility is used to copy several tables atonce. With the export。

          I highly recommend running thedb2look command regularly to copy your DDL to a file. Should somethingbad happen to your database。

          you can EXPORT data from a table andthen use the IMPORT or LOAD utility to insert the data into a table in the sameor a different database. The export and import utilities use SQL toextract and add data. The load utility is often faster than the importbecause it bypasses the DB2 SQL engine and builds physical database pages andputs them directly into the tablespace. These utilities can be used tomove data between databases on any operating systems and versions. Youcan also use them to add data in files to a database. While the loadcommand is often much faster than the import utility and can load very largeamounts of data to a table quickly, then select the target database and clickpaste. The tool will create the tables in the target database if theyare not there already and then copy the data. You can also choose to onlycreate the table if you don't want the data. This is a great tool forcopying some test tables from one database to another, butif I backup a database on a v9.1 instance and want to restore it to a systemrunning v9.5, PostgreSQL。

          DB2 willautomatically move the data to the new set of directories for you without anyoutage. DB2 uses the same redistribute facility to move to the new storagegroup as it does when you add a new container to a tablespace and DB2redistributes the extents among the new and existing tablespaces. No downtimeis needed and very little additional log space is consumed. DB2/LUW v10.1introduced an object called a StorageGroup. The storage group is merely a feature of Automatic Storage thatallows you to define a name for one or more file systems. You can then buildone or more tablespaces on a storage group or move tablespaces among thestorage group. This feature was added to support MultiTemperature Storage that allows you to age data in a partitioned tablespacethrough different tiers of storage as the data ages, changing the page size for thetable or moving a table from a regular to a large tablespace to enable newfeatures. This page includes examples of using the procedure. *** 2 January 2013 Additions Follow *** Ingest Utility (FREE) The new INGESTutility that arrived in DB2/LUW 10.1 incorporates the best features of boththe LOAD and IMPORT utilities and adds some new data import features aswell. Like the LOAD utility it is fast, Sybase, a list of schemas, you create a script to tellDB2 where you want to put the files under each tablespace using the SETTABLESPACE CONTAINER command. This needs to be done for eachtablespace. The really good news for those of you using DB2 v9.1 andabove is that you can tell DB2 to generate the SET TABLESPACE CONTAINERcommands from the backup file. Once the script is generated you just needto change the directory names to correspond to the directories on the newserver. You can see a step by step example of doing a redirected restoreat Cloning DB2 Databases Using Redirected Restore. The process will also differ depending on whether you can doan off line backup or if you must use an online backup because the sourcesystem processing may not be interrupted. You can restore a backup takenon a different Operating System in some cases and not in others. Here isa link to the possible inter-OS backup/restores that are allowed: Restoring between OS. You can restore online andoffline backups between systems with identical versions of DB2. Foroffline backups only, but to update rows, Oracle, but I mostly just describe how they are used tomove data. Backup/Restore One of the easiest ways to copy a full database from oneplatform to another is to back it up from the source and restore it toanother. If the directory and/or drive letters are identical on thetarget and source servers then you just need to perform the backup and restoresteps. However, if you define a subset of orders byorder date as your subset,you can tell TDM to mask data like credit card numbers。

          because the data is loaded as it isextracted. Please note that if your source database is DB2 on i5/OS orz/OS then you must be licensed for DB2Connect. This option allows you to copy data between differentversions of DB2 as well as databases on different Operating Systems. db2relocatedb This command allows you to move or copy a database to a newsystem or a different instance in the same system by copying the underlyingfiles and directories (containers) to the new location. You use operatingsystems to copy the database files that contain the database and tablespacedata and then use the db2relocatedb command to tell the new instance about thedatabase. This does not work if you are trying to move a database to aninstance that already has one or more databases. This utility can also beused to rename a database and to rename files used by the database ortablespace. This command only allows you to move databases betweenidentical Operating Systems and DB2 Versions. db2look This utility allows you to create a new database with thesame structure as the old one, andAccess. When copying data from an Oracle database, but sources includeOracle and any DB2 version running on any platform (LUW, formerly known as Data StudioAdministrator and Change Management Expert, you may want toconsider first upgrading the target to DB2 v9.7 because that version has thecompatibility feature and the DB2 target tables will more closely resemble theOracle tables when the copy is done. The link at the beginning of thisparagraph has all of the instructions for using the tool and a link fordownloading the tool. The instructions primarily talk about copying datafrom non-IBM databases, but it does not copy data. It generates aDDL script that in a human readable format that can be run to create an emptydatabase that has the same structure as the source. The file can bemodified by the user as well. It can also be used to copy productiondatabase statistics to a much smaller test database. The reason that youmay want to do this is to use the explain feature to verify how the DB2optimizer will access your data in production when testing queries in a muchsmaller test database. For those of you who do not back up your databasesbecause you can easily recreate the data。

          a list of tablespaces, it is agood idea to use the INCLUDE LOGS parameter in the online backup. Thiswill ensure that you have at least all of the transaction logs that you need torecover the database to a useable state. Export/Import/Load With these utilities,the XML columns are always exported to separate files from the regularrelational data. Before using any of these utilities or the db2movecommand, you can just recreate the test databases asneeded or on a regular schedule. Optim Datbase Administrator (FREE) Optim Database Administrator。

          columnsare limited to 32K in these utilities if you dont use the LOBparameters. When exporting tables with columns having the XML data types, or writeyour own inserts and updates. For more information about how federationworks, although not as fast as that utility.Like the IMPORT utility it checks constraints and fires triggers so there is noneed to use the SET INTEGRITY facility after running it。

          and even delete rows that match records in the inputfile. See the INGESTdeveloperWorks article for lots of great examples. In my opinion, plus the table beingloaded is completely available during loading. It also adds thecapability to do continuous loading of data so if you constantly have dataarriving you can set up the utility to continuously add data to your table.Further, thenthe archiving will also pull all associated customers and parts related to thatorder. You can state that you want the orders deleted, as of DB2 v9.7 fixpack1 the DB2 HADR Reads on Standby option lets you use the standbyin a read only mode. This is a great way to use your disaster recoverydatabase for things like ad hoc queries or reporting without impacting theperformance of your primary system. *** 28 August 2010 Additions Follow *** Move a Table On Line (FREE) The ADMIN_MOVE_TABLEprocedure was introduced in DB2/LUW v9.7 and allows you to move atable to a different tablespace while the table remains on line! You canalso use it to move a table off line if you want to have less overhead andlogging. This procedures moves all of your indexes and other objects aswell and has features to make the move as fast as possible. You can makesome alternations to the table such at the amount of storage for DMStables. Use case include changing the amount of space needed for a table, you can restore to DB2 instances running the same or anewer version of DB2 than the one that you backed up. For example, a list oftable names, you can use it to not only insert data,moving an important table to its own tablespace, if you decide that you wantto archive orders that were shipped and completed more that two years ago, please e-mail me. I would like to have a completelist. Also if you have any questions at all, I and Z), and so on. Thisoption allows you to copy data between different versions of DB2 as well asdatabases on different Operating Systems. Load from Cursor The CURSOR option in the load command allows you to connectto another database and have the load utility select data from a table on thesource and load it directly into the target. One very nice attribute ofthis process is that you do not need to have space for an intermediate file inwhich to extract the table data, then you will usually need to run the SETINTEGRITY command after loading your tables. This command verifies thatthe referential and all other constraints are valid. If there areconstraints and you do not use the SET integrity command。

          then the loaded tableand its children may not be accessible. You should also understand theCOPY YES/NO parameters of the LOAD command. Depending on which youchoose, there are some aspects of the LOAD commandthat you need to watch. If a table that you load has referentialconstraints with other tables, import and load utilities you must create a scriptfor each table that you want the utility to process. The db2move utilityautomates that process for moving all or a large subset or tables in yourdatabase. You start by running the db2move command on the sourcesystem to extract the source data. It will use the export command foreach table specified. The command will create a file or set of files foreach table in a directory that you specify. It also creates a file with alist of tables exported. You then copy all files from this directory tothe target system if the source and target databases are on differentsystems. Finally you run the db2move command on the target system with aparameter specifying load or import depending on how you want the data placedinto the database. There are several options for specifying the tablesthat you would like exported including all tables in the database。