There is no such thing as sedentary data. Once data is created, it gets moved around to support many different purposes. Just think of the various purposes for which your organization uses data. The first differentiation to come to mind is probably production data versus test data. And there are likely to be multiple different test environments, for example to support unit testing, integration testing, new product deliveries, training, and so on. But there may be multiple copies of the “same” data in each of those environments to support different applications, geographies, users, computing environments, and database management systems.
Rarely is a single copy of any piece of data “good enough.” Data is copied and transformed and cleansed and duplicated and stored many times throughout most organizations. It is common for different copies of the same data to be used to support transaction processing and analytics; test, quality assurance, and operational systems; day-to-day operations and reporting; data warehouses, data marts, and data mining; and distributed databases. And who manages all this moving data? It can vary from organization to organization, and even between different applications, but typically, the DBA group will be involved.
There are many techniques that can be used to facilitate data movement. One of the simplest ways for the DBA to move data from one place to another is to use the LOAD and UNLOAD utilities that come with the DBMS. The LOAD utility is used to populate tables with new data (or to add to existing data), and the UNLOAD utility is used to read data from a table and put it into a data file. Each DBMS may call the actual utilities by different names, but the functionality is the same or similar from product to product. For example, Microsoft SQL Server provides the BCP utility and Oracle offers SQL*Loader.
Although unloading data from one database and loading it to another is a relatively simple task, there are many factors that can complicate matters. For example, specifying the sometimes-intricate parameters required to un/load the data in the correct format can be daunting, especially when the underlying source and target table definitions differ. Many UNLOAD utilities can unload data from image copy backups, which can be beneficial because the live data is unaffected. Some LOAD utilities provide options to modify the functionality of the load process. For example, clauses to control the loading of default values or to bypass certain records in the input file are common.
Loading and unloading data may seem simple, but only from a ten-thousand-foot view. Indeed, understanding all the parameters of the LOAD and UNLOAD utilities, as well as when and how to use them, can be a Herculean task. Furthermore, unloading and loading is not very efficient for large tables, or even when many small to medium-sized tables need to be moved or refreshed. The frequent unloading and loading of data can consume a lot of CPU power.
Some DBMS products offer export and import utilities. Although similar to unload and load, import and export facilities typically work with more than just the data. For example, exported data may contain the schema for the table along with the data. In such cases, the import facility can create the table and import the data using just the export data file. Another difference is that an export file may contain more than just a single table. Nevertheless, export and import typically suffer from many of the same issues as unload and load, including inefficiency.
Another method for moving large quantities of data is ETL (Extract, Transform and Load) software, which is used primarily to populate data warehouses and data marts from other data sources. Although many DBMS vendors offer ETL software, usually it is not included with the base DBMS license. Using ETL software, it is possible to automate the extraction of data from disparate, heterogeneous sources. For example, you may need to extract data from legacy IMS databases and VSAM files on the mainframe; relational databases such as Oracle, SQL Server, PostgreSQL, and Db2 on various platforms; spreadsheets stored on the LAN; as well as external data feeds. The ETL software can be set up to recognize and retrieve the data from these diverse sources. Once retrieved, you may need to transform the data in some fashion before it is sent to the target database. ETL software can be more flexible for complex data movement than simple unload/load or export/import utilities.
A variation of ETL is ELT (Extract, Load, and Transform), which allows raw data to be loaded directly into the target and transformed there. With an ELT approach, a data extraction tool is used to obtain data from a source or sources, and the extracted data is stored in a staging area or database. You can run any required business rules and data integrity checks on the data in the staging area before it is loaded into the data warehouse. All data transformations occur in the data warehouse after the data is loaded.
Another method of moving data is through replication and propagation. When data is replicated, one data store is copied to one or more different data stores, either locally or at other locations. Replication can be implemented simply by copying entire tables to multiple locations or by copying a subset of the rows and/or columns, and it can be set up to automatically refresh the copied data on a regular basis.
Propagation is the migration of only changed data. Propagation can be implemented by scanning the database 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 ongoing changes by propagation.
Messaging software, also known as message queuing software or application integration, is another 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.
The Bottom Line
You can rest assured that your data is not just sitting there… it is constantly on the move. There are many reasons and methods for moving data and choosing the right method for each reason can be a struggle.
Of course, the methods discussed in this article are not the only ones available. Additional 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. There are also vendor products that bypass database control to copy and move data at the file level, which typically outperform standard database utilities. Furthermore, some DBMSs provide additional built-in methods for copying and moving data, such as Oracle Transportable Tablespaces.
The bottom line is that data is constantly moving from one place to another in most organizations. A lot of CPU power is dedicated to moving data all over the place. And DBAs are constantly asked to move more data – and to do it faster and more efficiently. It makes sense to keep up with the data movement needs of your organization and deploy more efficient techniques and products to keep that data moving effectively.
