DB2 Import Export Load

The so-called data movement in DB2, including:
1. Importing data (Import)
2. Exporting the data (export)
3. Loading of data (load)
Both import and load are used to save data from a file in a format to a table in a database using DB2 's related commands
Exporting refers to saving data from tables in a DB2 database to a file in a certain format.
The role of data movement:
If you want to transfer data between different database management systems, data movement is often the most practical method, because any kind of database management system supports several commonly used file formats, through this common interface, it is easy to achieve the transfer of data between different systems.
Of these three commands, export is the simplest, because the data is transferred from the table to the file, usually without errors, and no illegal data.
Before explaining the command, first introduce the format of the file, there are four kinds of file formats for DB2 data movement:
1. asc--the non-bound ASCII file, is an ASCII character stream. The rows in the data flow are delimited by row delimiters, and each column in the row is defined by the start and end positions. For example:
Ten Head Office Corporate New York
New England Eastern Boston
Mid Atlantic Ten Eastern Washington
Atlantic Eastern Atlanta
Great Lakes Midwest Chicago
Wuyi Plains Midwest Dallas
Pacific Western San Francisco
Mountain 290 Western Denver
2. The del--bound ASCII file is also an ASCII character stream. Rows in the data flow are delimited by row delimiters, and column values in rows are delimited by column delimiters. The file type modifier can be used to modify the default values for these delimiters. For example:
Ten, "Head Office", "Corporate", "New York"
"New England", "Eastern", "Boston"
"Mid Atlantic", "Eastern", "Washington"
"South Atlantic", "Eastern", "Atlanta"
"Great Lakes", "Midwest", "Chicago"
Wuyi, "Plains", "Midwest", "Dallas"
"Pacific", "Western", "San Francisco"
"Mountain", "290", "Western", "Denver"
3. wsf--(Work sheet format) is a worksheet type for data exchange with the Lotus series software.
4. pc/ixf--is an adapted version of the Integrated Interchange Format (integration Exchange FORMAT,IXF) data Exchange architecture, consisting of a number of column-variable-length records, including header records, table records, column descriptor records for each column in the table, and one or more rows per row in the table Data logging. The PC/IXF file record consists of the fields that contain the character data.
Part I: Exporting data (export)
Example one: Export all the data in the ORG table to the file C:\ORG. TXT.
Export to C:\org.txt of del select * from org
Wherein, of Del represents the type of file exported to, in this case exported to a non-bounding text file; the next select * from org is an SQL statement that queries the result of the data being exported.
Example two: Changing the format control of a del format file
Export to C:\staff.txt of Del Modified by coldel$ chardel "Decplusblank select * from staff
In this example, the modified clause is used to control the various symbols, Coldel represents the spacer between the fields, the default is a comma, and now the $ number; Chardel indicates what symbol references are used for string fields, which are enclosed by a pair of double quotes by default, and are now enclosed in a pair of single quotes. Decplusblank indicates that for a decimal data type, a space is substituted for the first plus sign, because by default, the decimal data is preceded by a positive number.
Example three: Exporting data to a file in ASC format
The Export command does not support ASC format files, so if you want to export a structured format such as ASC, you need the programmer to do the conversion operation, the idea is to convert various data types into fixed-length strings, and then merge the fields to be exported into a field.
For example, CREATE table n for the following structure:
CREATE Table N (a int,b date,c time,d varchar (5), E char (4), F double)
Then insert two data:
INSERT into n values (2004-10-21 ', ' 23:12:23 ', ' abc ', ' hh ', 35.2)
INSERT into n values (5, ' 2004-1-21 ', ' 3:12:23 ', ' BC ', ' HHH ', 35.672)
To export these two data to a file in a structured format, do the following:
Export to C:\test.txt of Del Select char (a) | | char (b) | | char (c) | | char (d,5) | | e | | char (f) as TMP from N
The result of this export is very similar to the ASC format file, except that there is a pair of double quotation marks on each line, so we can use the Text tool (such as WordPad, Notepad, etc.) to remove the double quotation marks, or ignore them, and control the format (ignoring the double quotation marks) at the time of import.
The format in the file is:
"2004-10-2123.12.23abc hh 3.52E1"
"5 2004-01-2103.12.23BC hhh 3.5672E1"
Example four: The export of big data
Export to D:\myfile.del of Del lobs to d:\lob\ lobfile lobs modified by Lobsinfile select * from Emp_photo
This command exports the data from the Emp_photo table to the D:\myfile.del file, with the result:

;
The third field is the Blob type, where only one flag is saved, the equivalent of a pointer, and the true LOB data is saved in the D:\lob directory of lobs.001, lobs.002 、...... such as a series of files. In the command, specify the path after which the large object data is saved (note that the path must already exist beforehand, otherwise it will be lobs), lobfile specify what file the large object data is saved in, do not specify the extension, and DB2 automatically append according to the amount of data. 001,. 002 Extensions, Also do not forget to add the modified by Lobsinfile clause.
Example five: Save the export information in a message file.
Export to D:\AWARDS.IXF of IXF messages D:\msgs.txt select * FROM staff where dept = 20
This example exports the DEPT=20 data in the staff table to the D:\awards.ixf file, and all the exported information is stored in the D:\msgs.txt file (whether it's a success, warning, or failure message) so that the administrator can find the problem by observing the information file.
Example six: Renaming an exported data column.
Export to D:\awards.ixf of IXF method N (C1,C2,C3,C4,C5,C6,C7) messages D:\msgs.txt SELECT * from staff where dept=20
By default, each column of data exported is automatically named with the corresponding field name in the table, and we can rename each column by the method n clause, noting that this clause is valid only in IXF and wsf format files and cannot be used in a text file.
Import of data
Example VII: Import the data from the Org.txt file in the C packing directory into the org table
Import from C:\org.txt to del insert into org
The format of the Import command and the Export command is basically in the corresponding relationship, the import corresponds to the Export,from to, the file name and the document format represent the same meaning, but the import command supports files in ASC format, and the Export command is not supported. In addition, at the end of the Export command is an SQL statement that selects the data to be exported, and the import command is not the SQL statement at the end, but the way the data is inserted and the target table name.
Example VIII: Importing data from an ASC format file
Import from C:\org2.txt of ASC method L (1 5,6 19,20 25,26 37,38) insert INTO org
Where the method l clause is used to specify the starting and ending positions of each field in a text file, separated by a space between each start and end position, separated by commas.
In addition to the L method, there are N methods and P methods, which are described below.
Example nine: Import data using the N method and create a new table.
First export a use case file:
Export to D:\org.ixf of IXF method N (a,b,c,d,e) SELECT * from org
In this case, there are five columns of data in the Org.ixf file, and the corresponding column names are a, B, C, D, E
Then import the data from the file into a new table
Import from D:\org.ixf of Ixf method N (d,e,b) replace_create into Orgtest
The command selects three columns from the file to import into the table, in the order that it does not follow the order of the columns in the file. The narrative of the Replace_create method is shown below.
Insert methods are:
INSERT Method-Appends new data to the existing data in the table.
Insert_update mode-This method can only be used for tables with primary keys, if the inserted data and the original data primary key does not conflict, then directly inserted, if the primary key conflict, then replace the original data with the new data.
REPLACE Mode--First delete the existing data in the table, and then insert the data into the empty table.
Replace_create mode-Indicates that if a table exists, the data in the table is deleted, the data is inserted into the empty table, and if the table does not exist, the table is created based on the fields in the file, and then the data is inserted into the table. This way you can only insert data from a file in the IXF format into a table.
Example ten: Using the P method to import data
Import from D:\org.ixf of Ixf method P (4,5,2) replace into orgtest
This example performs the same effect as example nine, except that the N method is replaced by the P method, and the number of the column is indicated in the list following the P method, which does not need to be indicated by the column name. In addition, this example uses replace to insert data, which deletes existing data from the table and then inserts data into the empty table.
Example 11: Import of NULL values
For files in IXF format, it is very convenient to import null values because the information about the null value is already logged. However, it is difficult for ASC format files because DB2 inserts spaces directly instead of null values. To do this, DB2 provides a clause to control: NULL indicators
Import from C:\org2.txt of ASC MODIFIED by nullindchar=# method L (1 5,6 19,20 25,26 37,38) NULL indicators (0,0,0,0,38 ) Replace into org
In this example, the null indicators clause is followed by a list, indicating that no null value exists for the first four fields, and the fifth field starts with a 38 column, there may be a null value, and the MODIFIED by nullindchar=# clause indicates that the fifth field in the file encounters a # number , it is represented as a null value.
Just say this, and I hope you will add, next time we talk about the load command.
Loading (load)
Loading the command format is similar to importing, the command keyword is load, but the subsequent parameters are much more than the import command, and you can refer to the DB2 documentation yourself for detailed usage.
Loading is similar to importing in that the data in the input file is moved into the target table, and the differences between them are interpreted progressively in the instance.
The target table must already exist before loading.
The load performance is higher than the import, which is explained in detail later in conjunction with the example.
The mount operation is not logged to the log, so the log file cannot be used for roll forward operations.
Loading is divided into 4 stages:
1. Loading phase
Two things happen at this stage: The data is stored in the table, and the index keys are collected and sorted. At load time, the DBA can specify how long to generate a consistent point.
It is a checkpoint for loading tools. If the mount is interrupted during execution, it can resume execution from the last consistent point.
2. Construction phase
During the build phase, indexes are created based on the index key information collected during the Mount phase. If an error occurs during the build phase, the Mount tool restarts, and it will start again at the start of the build phase.
3. During the delete phase, all rows that violate a unique or PRIMARY KEY constraint are deleted and copied to an exception table (if the appropriate option is specified in the statement). When the input row is rejected, a message is generated in the message file.
4. Index copy phase
If the system temporary tablespace is specified for index creation during the mount operation, and the READ ACCESS option is selected, the index data is copied from the System temporary table space to the original tablespace.
All four phases of the mount process are part of the operation and the mount operation is completed only after all four phases have been completed. Messages are generated at each stage, and once an error occurs at one of these stages, these messages can help the DBA analyze and resolve the issue.
Each time the import operation inserts a row of data, it checks to see if the constraint is met and is logged into the log file.
Below we look at some of the load command-specific features, the import command can also do is no longer detailed to say.
Example 12: Loading from a cursor type file
Define a cursor
Declare mycur cursor FOR SELECT * FROM org
Create a new table with cursor compatibility
CREATE TABLE org2 like org
Load from cursor
Load from Mycur of the cursor insert INTO ORG2
In addition to being loaded from the cursor, it can be mounted from files, pipelines, and devices. The import command can only be imported from a file.
Example 13: About the exception table
A user-defined exception table can be used to store rows that do not follow a unique constraint and a main code constraint. If the exception table is not specified when loading, the rows that violate the unique constraint are discarded and will no longer have the opportunity to recover or modify.
Experiment with the staff table in the sample database
1. Create a table with the same structure as the staff table STAFF1
CREATE TABLE STAFF1 like staff
2. Insert part of the data from the staff table into the STAFF1
INSERT into STAFF1 SELECT * from the staff WHERE id<=160
3. Create a table staffexp with the same structure as the STAFF1, as the exception table
CREATE TABLE staffexp like STAFF1
4. Add a column to the exception table because the exception table is the same structure as the normal table, which is the last one or two columns (column name arbitrary), the first column is the timestamp type, the time when the exception record was inserted, and the second column is the large text type (at least 32K size). Saves the specific constraint information that caused the record to be rejected. In this example, only one timestamp column is added.
ALTER TABLE staffexp ADD COLUMN time TIMESTAMP
5. Create a unique index for the STAFF1 table
CREATE UNIQUE INDEX idxstaff on STAFF1 (ID)
6. Run the export command first to make a text file
EXPORT to D:\STAFF. TXT of DEL SELECT * from staff
7. Then run the Mount command to reload the data into the STAFF1 table
LOAD from D:\STAFF. TXT of DEL INSERT into STAFF1 for EXCEPTION Staffexp
Because there is a unique index in table STAFF1, some of the data is inserted into the exception table Staffexp because it violates this constraint and cannot be inserted into the STAFF1 table.
Note that the exception table must be defined first, the loading command cannot automatically generate the exception table, and if the specified exception table cannot be found, an error will be given.
Example 14: About dump Files
An improperly formed line was rejected. These rejected records can be placed separately in the specified file by specifying the DumpFile file type modifier.
Experiment with the staff table in the sample database
1. Create a table with the same structure as the staff table STAFF1
CREATE TABLE STAFF1 like staff
2. Insert part of the data from the staff table into the STAFF1
INSERT into STAFF1 SELECT * from the staff WHERE id<=160
3. Create a table staffexp with the same structure as the STAFF1, as the exception table
CREATE TABLE staffexp like STAFF1
4. Add a column to the exception table
ALTER TABLE staffexp ADD COLUMN time TIMESTAMP
5. Create a unique index for the STAFF1 table
CREATE UNIQUE INDEX idxstaff on STAFF1 (ID)
6. Run the export command first to make a text file
EXPORT to D:\STAFF. TXT of DEL SELECT * from staff
Open the STAFF.TXT file on the D drive and replace the row with the first column equal to 320 with the following: "ABCF", "AAA", "SDFG"
7. Then run the Mount command to reload the data into the STAFF1 table
LOAD from D:\STAFF. TXT of DEL MODIFIED by Dumpfile=d:\dump INSERT to STAFF1 for EXCEPTION Staffexp
The loaded results report will have one of the following:
sql3118w the field value in row "32" column "1" cannot be converted to a SMALLINT value, but the destination column cannot be empty. The row is not mounted.
SQL3185W A previous error occurred while processing data in the line "32" of the input file.
Open the D-drive dump.000 file and you will see the row of data that caused the exception: "ABCF", "AAA", "SDFG"
With this example, we can understand that if a row of data is malformed, it will be rejected when it is loaded, the row record will be placed in the dump file, and if the data is well-formed but does not satisfy the constraints of the table, the row record will be placed in the exception table.
Example 15: Limit number of loading
Use the ROWCOUNT option to specify the number of records to load from the beginning of the file
LOAD from D:\STAFF. TXT of DEL ROWCOUNT 3 INSERT into STAFF1
Example 16: Forcing the mount operation to fail when a warning message appears
In some cases, the data in the file must be successfully entered into the target table to be successful, even if there is a record error. In this case, you can use the Warningcount option.
Open the STAFF.TXT file on the D drive and replace the row with the first column equal to 320 with the following: "ABCF", "AAA", "SDFG"
LOAD from D:\STAFF. TXT of DEL Warningcount 1 INSERT into STAFF1
The result of the run contains the following warning:
sql3118w the field value in row "32" column "1" cannot be converted to a smallint value, but the destination column cannot be empty. The row is not mounted.
SQL3185W A previous error occurred while processing data in the line "32" of the input file.
The SQL3502N utility encountered a "1" warning, which exceeded the maximum number of warnings allowed.
The table STAFF1 cannot be manipulated at this time, for example
SELECT * from STAFF1
will return:
ID NAME DEPT JOB years SALARY COMM
------ --------- ------ ----- ------ --------- ---------
sql0668n because of the table "USER." STAFF1 "Reason code" 3 "on, so the operation is not allowed.
sqlstate=57016
The reason is that the table is in the Mount pending state. The previous LOAD attempt on this table failed. Access to the table is not allowed until the LOAD operation is restarted or terminated.
The workaround is to restart or terminate the previously failed load operation on this table by issuing a load with the RESTART or Terminater option, respectively.
The load command containing the Terminater can terminate the mount process and return the target table to its normal usable state:
LOAD from D:\STAFF. TXT of DEL TERMINATE into STAFF1
The load command containing the restart can be used when the source file is modified correctly, allowing the loading process to start again:
LOAD from D:\STAFF. TXT of DEL RESTART into STAFF1
Example 17: Prevent warning messages from being generated
You can use the Norowwarnings file type modifier to suppress the generation of warning messages, which can be used when the loading process may have a large number of warning messages, and the user is not interested in this option, which can greatly improve the efficiency of loading
Open the STAFF.TXT file on the D drive and replace the row with the first column equal to 320 with the following: "ABCF", "AAA", "SDFG"
LOAD from D:\STAFF. TXT of DEL MODIFIED by norowwarnings inserts into STAFF1
In the end of the run, there is an error in line 32nd, the row cannot be mounted, but no warning message is generated.
Example 18: Generating statistical data
Use the statistics option to generate statistics during the load process, which can be used by the optimizer to determine the most efficient way to execute SQL statements.
You can produce different levels of detail statistics for tables and indexes:
① produces the most detailed statistics on tables and indexes:
LOAD from D:\STAFF. TXT of DEL REPLACE into STAFF1 STATISTICS YES with distribution and detailed INDEXES all
② produces a brief statistic on both the table and the index:
LOAD from D:\STAFF. TXT of DEL REPLACE into STAFF1 STATISTICS YES and INDEXES all
Other combinations can refer to the DB2 documentation.
Note: The statistics option is only compatible with replace and is not compatible with the INSERT option.
In addition, we do not see any direct results through the statistics option, and if we want to see the results, we need to find ourselves in the system tables.
Example 19: de-check pending status
1. Connect to the sample database:
Connect to sample
2. Create a table with the same structure as the staff table:
CREATE TABLE STAFF1 like staff
3. Add a check constraint to the table:
ALTER TABLE STAFF1 add constraint chk check (dept<100)
4. Open the STAFF.TXT file on the D drive and change the third column of the last row of data to 150 so that the data does not meet the 3rd step plus the check about
The load command loads the data from the file into the STAFF1 table:
LOAD from D:\STAFF. TXT of DEL INSERT into STAFF1
5. Run the query command at this point:
Select * from STAFF1
will get an error message:
sql0668n because of the table "USER." STAFF1 "Reason code" 1 "on, so the operation is not allowed.
sqlstate=57016
The reason is that there is data violating the check constraint when loading, causing the table to be in check pending state.
6. Remove the check suspend state of the table, using:
Set integrity for STAFF1 check immediate unchecked
Run the query command again:
Select * from STAFF1
The discovery table can be used normally, and the data that violates the check rule also exists.
Example 20: Performance factors
When importing data from a file to a table, when the data volume is particularly large, the load command is clearly an advantage because it does not insert a single row at a time like an import command, and in each row to check whether the constraints are met, the Mount command reads the data build page from the input file and writes the pages directly to the database. And when each row of data is loaded without judging whether or not to satisfy the constraint, and the loading command does not write the log, all of these factors result in more efficient loading than importing.
In addition, the load command has several options to control performance factors:
1. COPY yes/no and Nonrecoverable
①nonrecoverable (non-recoverable): Specifies that the mount operation is not recoverable and cannot be recovered by a subsequent roll-forward operation. The roll forward operation ignores the transaction and marks the table that is loading the data as "invalid".
②copy No (default option): In this case, if the archive log for the database in which the table resides is enabled, the table space in which the table resides will be in the backup pending state until the database or tablespace backup is complete, and the table space becomes a writable tablespace. The reason is that the changes caused by the mount operation are not recorded, so it is necessary to back up the database or tablespace if the failure occurs after the mount operation has been completed.
③copy Yes: In this case, if the archive log for the database is enabled, changes to the mount operation will be saved to the tape, directory, or TSM server, and the tablespace will no longer be in the backup pending state.
2. Fastparse
This file type modifier is used to reduce the number of data checks. It can only be used in cases where the data is known to be correct, especially for files of the DEL and ASC types.
3. Anyorder
If the Savecount option is not used, this parameter allows the load to be mounted without complying with the order of data in the input file, which increases the load performance when the Cpu_parallelism option is greater than 1 on an SMP (symmetric multiprocessor) system.
4. Data Buffer
This parameter is used to specify the number of memory pages in the 4K size allocated from the stack, as an internal buffer for loading, and specifying a large buffer can help improve load performance.
5. Cpu_parallelism
This option is available only on SMP systems and can indicate how many processes or threads are used to parse, transform, and format data.
6. Disk_parallelism
This option specifies the number of processes or threads that write data to disk.

DB2 Import Export Load