Welcome to Data Movement Tools & Utilities. After watching this video, you will be able to: Indicate scenarios that necessitate data movement List various tools and utilities used for Data Movement in Relational Databases Explain the purpose of backup and restore utilities to move data into and out of a database Describe import and export utilities and how they differ from backup and restore Recall how to export a table to a CSV file Explain the benefit of load utility and how it differs from import Data engineers and database administrators will often need to move data into an out of an existing database. This could be for several reasons such as: initially populating the entire database and all objects with it such as tables; creating a working copy of the database for development and testing purposes, creating a snapshot of the state of the database at particular instant in time for the purposes of disaster recovery creating a new table (or a set of tables) from data that has been extracted from external data sources or files adding or appending data into one or more existing tables Most databases support many different ways to move data in and out of the database using multiple file formats. Each database has its own tools and utilities for data movement, but they can be broadly classified into three categories: Backup and Restore Import and Export and Load Let's look at each of these. One method for moving data between databases is to perform a backup and restore. The backup operation creates a file (or a set of files) that encapsulates all of the database objects and their data. The restore operation creates an exact copy of the original database from the backup files. Backup and restore operations preserve all objects in the database, including schemas, tables, views, user defined data types, functions, stored procedures, table constraints, triggers, security settings, relationship between objects and of course the data in all the tables. Backups are often taken on a periodic basis to preserve copies of production database for disaster recovery purposes. Backup and restore operations can also be performed for creating additional copies of the database for development and test purposes. The Import operation reads data from a file and performs a series of INSERT statements against the target table. The Export operation selects data from the specified table and saves it into a target file. Depending on the database, Import and Export operations can be performed using multiple interfaces. Most databases provide at least a command line utility. Management APIs for some databases include support for import and export. In some cases, the graphical or web administration tools include a visual interface for these operations. And there are also third-party tools that allow these operations to be performed with many different DBMSes. Most databases support Multiple File formats for Importing and Exporting data. The most commonly used formats are: DEL Delimited ASCII, for data exchange among a wide variety of database managers and file managers. This common approach to storing data uses special character delimiters to separate column values. A well-known example is comma separated variable, or CSV files. ASC Non-delimited ASCII, for importing or loading data from other applications that create flat text files with aligned column data. PC/IXF PC version of the Integration Exchange Format (IXF), the preferred method for data exchange within the database manager. PC/IXF is a structured description of a database table that contains an external representation of the internal table. JSON With the popularity of JSON and REST web services, some databases and third-party tools have also started to support importing and exporting data to and from JSON files. Let's look at some examples of performing Import and Export operations from different interfaces. In DB2, the command line import and export utilities allow you to type the filenames, file formats, table names, and optionally message files for importing and exporting data. Note that the export utility in Db2 allows you to specify a SQL query, allowing you to export just a subset of the data in a specified table, if needed. As an example of a simple export, let’s look at how to export a table to a CSV file in the Db2 console. In your schema, select the table you want to export. Select the table, and then <click> click View data. Select the export button. Click Export to CSV. You can specify a name and location to save the CSV file. As an alternative to the Import utility, some databases provide Load utilities. The load utility is faster than the import utility, because it writes formatted pages directly into the database, while the import utility performs a series of SQL INSERT statements. However, it does not perform referential or table constraints checking, so you may prefer to use the Import utility if you need those extra checks. The Load utilities may also bypass database logging, which also helps with higher performance. While Import may work well for smaller tables, when it comes to very large volumes of data, the Load utilities are preferred. You can activate the Db2 import utility from the command line or by calling its API through an application or using visual database management tools. In this video, you learned that: Data movement is required for initially populating databases and tables, adding or appending data, and making copies for development test or disaster recovery BACKUP and RESTORE utilities are used to create and recover copies of entire databases including all objects like tables, views, constraints and their data IMPORT utility enables inserting data into a specific table from different formats such as DEL/CSV, ASC and IXF EXPORT utility enables saving data from a specific table into various formats like CSV LOAD utilities enable high performance insertion of data into specified tables and useful for large volumes
