The IMPORT and EXPORT utilities may be used to transfer data between DB2 databases, and to and from DRDA host databases.
DataPropagator Relational (DPROPR) is another method for moving data between databases in an enterprise.
The following topics provide more information:
Compatibility considerations are most important when loading or importing/exporting data between Intel-based and UNIX-based platforms.
For more information, see the following topics:
PC/IXF is the recommended format for transferring data between DB2 databases. PC/IXF files allow the Load utility or the Import utility to process numeric data, normally machine dependent, in a machine independent fashion. For example, numeric data is stored and handled differently in Intel** and other hardware architectures.
To provide compatibility of PC/IXF files between all products in the DB2 family the EXPORT utility creates files with numeric data in Intel format, and the IMPORT utility expects it in this format.
| Note: | Depending on the hardware platform, DB2 products convert numeric values between Intel and non-Intel formats (using byte reversal) during both export and import operations. |
Multiple Part Files: UNIX-based implementations of DB2 do not create multiple-part PC/IXF files during export. However, they will allow you to import such a file that was created by DB2 for OS/2. When importing this type of file, all parts should be in the same directory, otherwise an error is returned by the utility.
The single-part PC/IXF files created by the UNIX-based implementations of DB2 export utility can be imported by DB2 for OS/2.
DEL files have differences based on the operating system on which they were created. The differences are:
Since DEL export files are text files, they may be transferred from one operating system to another. File transfer programs handle the above differences if you transfer the file using the text mode. Using the binary mode to transfer the file does not convert row separator and end-of-file characters as required.
If character data fields contain row separator characters, these will also be converted during the file transfer. This conversion will cause an inappropriate change to the data and as a result, when the file is imported into a database on the different platform, data shrinkage or expansion may occur. For this reason, we recommend that you do not use DEL export files to move data between DB2 databases.
Numeric data in WSF format files is stored using Intel machine format. This format allows Lotus WSF files to be transferred and used in different Lotus operating environments (for example, Intel-based and UNIX-based systems).
As a result of this consistency in internal formats, exported WSF files from DB2 products can be used by Lotus 1-2-3 and Symphony running on a different platform. DB2 products can also import WSF files that were created on different platforms.
Transfer WSF files between operating systems platforms in binary, not text mode.
Do not use the WSF file format to transfer data between DB2 databases, since a loss of data may occur. Use the PC/IXF file format instead.
db2move is a tool that can help move large numbers of tables between DB2 databases located on workstations. db2move queries the system catalog tables for a particular database and compiles a list of all user tables. The tool then exports these tables in PC/IXF format. The PC/IXF files can be imported or loaded to another local DB2 database on the same system, or can be transferred to another workstation platform and imported or loaded to a DB2 database on that platform.
db2move calls the DB2 export, import, and load APIs depending on the action requested by the user. Therefore, the requesting user ID must have the correct authorization required by the APIs or the request will fail. Also, db2move inherits the limitations and restrictions of the APIs. db2move is found in the misc subdirectory of the sqllib directory.
The syntax of the tool is:
db2move dbname action [options...]
The dbname is the name of the database. The action must be one of: EXPORT, IMPORT or LOAD. The options are:
This is an EXPORT action only. If specified, only those tables created by the creators listed with this option are exported. If not specified, the default is to use all creators. When specifying multiple creators, each must be separated by commas; no blanks are allowed between creator IDs. The maximum number of creators that can be specified is 10. This option can be used with the "-tn" table-names option to select the tables for export.
The wildcard character, asterisk (*), can be used in table-creators and can be placed anywhere in the string.
This is an EXPORT action only. If specified, only those tables whose names match exactly to those in the specified string are exported. If not specified, the default is to use all user tables. When specifying multiple table-names, each must be separated by commas; no blanks are allowed between table-names. The maximum number of table-names that can be specified is 10. This option can be used with the "-tc" table-creators option to select the tables for export. db2move will only export those tables whose names are matched with specified table-names and whose creators are matched with specified table-creators.
The wildcard character, asterisk (*), can be used in table-names and can be placed anywhere in the string.
Valid options include INSERT, INSERT_UPDATE, REPLACE, CREATE, and REPLACE_CREATE.
Valid options include INSERT and REPLACE.
This option shows the absolute path names where LOB files are created (as part of EXPORT) or searched for (as part of IMPORT or LOAD). When specifying multiple lobpaths, each must be separated by commas; no blanks are allowed between lobpaths. If the first path runs out of space (during EXPORT) or the files are not found in the path (during IMPORT or LOAD), the second path will be used. Each subsequent path will be used for the same reasons should the same conditions exist.
If the action is an EXPORT and lobpaths are specified, all files in the lobpath directories are deleted, the directories are removed, and new directories are created. If not specified, the current directory is used for the lobpath.
Both user ID and password are optional. However, if one is specified, both must be specified. If db2move is run on a client connecting to a remote server, user ID and password should be specified.
Both user ID and password are optional. However, if one is specified, both must be specified. If db2move is run on a client connecting to a remote server, user ID and password should be specified.
The following are several examples showing the db2move:
This will export all tables in sample; the defaults are used for all options.
This will export all tables created by "userid1" or user IDs LIKE "us%rid2"; and, table-name is "tbname1" or table-names LIKE "%tbname2".
This example is applicable for Intel-based platforms only. This will import all tables in sample; any LOB files are to be searched for using lobpaths "D:\LOBPATH1" and "C:\LOBPATH2".
This example is applicable for UNIX-based platforms only. This will load all tables in sample; any LOB files are to be searched for using the lobpath subdirectory in the userid subdirectory of the the home directory or in the tmp subdirectory.
This will import all tables in sample in REPLACE mode; the user ID and password are used.
Usage notes:
The db2move LOAD action is not supported in DB2 Universal Database where partitioned databases may be used.
Notes when using EXPORT:
"nnn" is the table number. "c" is a letter of the alphabet. "yyy" is a number ranging from 001 to 999.
These files are created only if the table being exported contains LOB data. If created, these LOB files are placed in the "lobpath" directories. There are a total of 26 000 possible names for the LOB files.
Notes when using IMPORT:
Notes when using LOAD:
You may be working in a more complex environment where you need to move data between a host database system and the workstation environment. In such an environment, you may work with DB2 Connect; as the gateway for the data from the host to the workstation as well as the reverse.
The following section discusses the considerations when importing and exporting data using DB2 Connect.
The import and export utilities let you move data from a DRDA server database to a file on the DB2 Connect workstation or vice versa. You can then use this data with any other application or RDBMS that supports this import/export format. For example, you can export data from DB2 for MVS/ESA into a delimited ASCII file and later import it into a DB2 for OS/2 database.
You can perform export and import functions from a database client or from the DB2 Connect workstation.
Notes:
To export to a DRDA server database:
To import data from a DRDA server database:
With the DB2 Connect program, import or export operations must meet the following conditions:
If these conditions are violated, the operation will fail and an error message will be generated.
If you import and export mixed data (columns containing both single-byte and double-byte data), consider the following:
The function of the SQLQMF utility with DDCS for OS/2 has been replaced by the DB2 Connect Import/Export functions. The advantages are:
Refer to the Command Reference for further information on using these commands.
Replication allows you to copy data on a regular basis to multiple remote databases. If you need to have updates to a master database automatically copied to other databases, you can use the replication features of DB2 to specify what data should be copied, which database tables the data should be copied to, and how often the updates should be copied. The replication features in DB2 are a part of a larger IBM solution for replicating data in small and large enterprises--IBM Relational Data Replication (IBM Replication).
The IBM Replication tools are a set of IBM DataPropagator Relational (DPROPR) programs and DB2 Universal Database tools that copy data between distributed relational database management systems:
Based on the DPROPR V1 offering, IBM Replication tools allow you to copy data automatically between DB2 relational databases, as well as nonrelational and non-IBM databases.
You can use the IBM Replication tools to define, synchronize, automate, and manage copy operations from a single control point for data across your enterprise. The replication tools in DB2 Universal Database offer replication between relational databases only. The tool set manages the copying (replication) of data in a store-and-forward manner.
Replication allows you to give end-users and applications access to production data without putting extra load on the production database. You can copy the data to a database local to an end-user or application, rather than have them access the data remotely. A typical replication scenario involves a source table with copies in one or more remote databases, for example, a central bank and its local branches. A change occurs in the "master" or source database. At a predetermined time, an automatic update of all of the other DB2 relational databases takes place and all the changes are copied to the target database tables.
The replication tools allow you to customize the copy table structure. You can use SQL when copying to the target database to subset, aggregate, or otherwise enhance the data being copied. You can also create the copy tables structure to fit your needs: read-only copies that duplicate the source table, show data at a certain point in time, provide a history of changes, or stage data to be copied to additional target tables. Additionally, you can create read-write copies that can be updated by end-users or applications and have the changes replicated back to the master table. You can replicate views of source tables and views of copies. Event-driven replication is also possible.
The replication tools currently support DB2 on MVS/ESA, AS/400, AIX, OS/2, VM and VSE, Windows NT, HP, and the Solaris Operating environment. You can also replicate to non-IBM databases, such as Oracle, Microsoft SQL Server, and Lotus Notes.
There are two components of the IBM Replication tools solution: IBM DPROPR Capture and IBM DPROPR Apply. You can setup these two components with the DB2 Control Center. The operation of these two components, and the monitoring of them, happen outside of the Control Center.
The IBM DPROPR Capture program captures the changes from the source tables. A source table can be an external table containing SQL data from a file system or nonrelational database manager loaded outside DPROPR; an existing table in the database; or, a table that has previously been updated by the IBM DPROPR Apply program, which allows changes to be copied back to the source or to other target tables.
The changes are copied into a change data table, where they are stored until the target system is ready to copy them. The Apply program then takes the changes from the change data table and copies them to the target tables.
You use the Control Center to set up the replication environment, define source and target tables, specify the timing of the automated copying, specify SQL enhancements to the data, and define relationships between the source and the target tables.
For more information, see the Replication Guide and Reference, S95H-0999.