IMPORT/EXPORT
Similar to an UNLOAD utility, an EXPORT utility reads data from a table and places it into an external file. An IMPORT utility reads an external file created by the EXPORT utility and inserts the data into a table. IMPORT and EXPORT facilities typically work with more than just the data, though. Sometimes an EXPORT data file contains the schema for the table along with the data. In such cases, the IMPORT utility can create the table and import the data using just the EXPORT data file. Sometimes the EXPORT file contains more than just a single table. Some EXPORT facilities enable the DBA to specify a single table, and then follow the relationships for that table to extract all of the related files and data.
Some IMPORT/EXPORT facilities provide UNLOAD-like features to sample, subset, and limit the data that is exported (and imported). The difference, though, is the ability to perform such functions across multiple tables and maintain referentially intact data. Not every DBMS offers IMPORT and EXPORT utilities. Some third-party vendors provide import and export products.
Although UNLOAD, LOAD, IMPORT and EXPORT are the most common methods used by DBAs to move large amounts of data from place to place, there are other methods for moving large quantities of data. ETL software can be used to move large amounts of data. ETLstands for extract, transform, and load. ETL software is primarily used to populate data warehouses and data marts from other databases and data sources.
Replication and Propagation
Another method of moving data is through replication and propagation. When data is replicated, one data store is copied to one or more data stores, either locally or at other locations. Replication can be implemented simply by copying entire tables to multiple locations. Alternatively, replicated data can be a subset of the rows and/or columns. Replication can be set up to automatically refresh the copied data on a regular basis.
Propagation, on the other hand, is the migration of only changed data. Propagation can be implemented by scanning the transaction log and applying the results of data modification statements to another data store. Initial population of a data warehouse can be achieved by replication, and subsequent population of changes by either replication (if the data is very dynamic) or propagation.
Messaging and Application Integration
Messaging software, also known as message queuing software or application integration, is another popular form of data movement. When using a message queue, data is placed onto the queue by one application or process; the data is read from the queue by another application or process. Messaging software works by providing APIs to read and write formatted messages to and from a queue. An application can read or write messages to and from the queue from any platform supported by the software.
Of course, many other methods exist for moving data—from the simple, such as using a table editing tool to highlight and copy data, to the complex, such as writing programs to read the database and write to external files or directly to other databases. But whatever methodology you use, one thing is sure, data sure moves around a lot these days!