Shulou(Shulou.com)06/01 Report--

DB2 export how to understand, I believe that many inexperienced people do not know what to do about this, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The EXPORT utility uses SQL select or XQUERY statements to extract data and put the information into a file. You can use the output file to move the data for IMPORT or LOAD operations, or to use the data for analysis.

EXPORT TO filename OF {IXF | DEL | WSF}

[LOBS TO lob-path [{, lob-path}...] ]

[LOBFILE lob-file [{, lob-file}...] ]

[XML TO xml-path [{, xml-path}...] ]

[XMLFILE file-name [{, filename}...] ]

[MODIFIED BY {filetype-mod... }] [XMLSAVESCHEMA]

[METHOD N (column-name [{, column-name}...] )]

[MESSAGES message-file]

{select-statement | XQUERY xquery-statement |

HIERARCHY {STARTING sub-table-name |

(sub-tablename [{, sub-table-name}...])

}

[WHERE... ]

}

Filetype-mod:

NODOUBLEDEL 、 LOBSINFILE 、 CHARDELx 、 COLDELx 、 DECPLUSBLANK 、 DECPTx 、 DATESISO 、 1 、 2 、 3 、 4 、 CODEPAGE=x 、 STRIPLZEROS 、 NOCHARDEL 、 LOBSINSEPFILES 、 XMLINSEPFILES 、 XMLCHAR 、 XMLGRAPHIC 、 XMLNODECLARATION 、 TIMESTAMPFORMAT=x

Description: 1. For most export operations, you need to provide a SELECT statement to specify the data that needs to be retrieved in order to export. When you export a typed table, you do not have to explicitly issue a SELECT statement, but only specify the traversal order of the child tables in the hierarchy.

2. MODIFIED BY filetype-mod file type modifiers provide many options that allow you to change data, date and time stamps, or code page formats, or specific data types have been written to separate files.

3. The METHOD parameter can specify different column names to use for the exported data.

Message file: EXPORT writes error messages, warning messages, and reference messages to the ASCII text message file in the table. The MESSAGES parameter specifies the names of these files.

5. Because EXPORT is an embedded SQL application and performs SQL memory access internally, the optimizations applied to SQL operations are also applied to the EXPORT utility. Consider the benefits of using large buffer pools, indexing, and sorting heaps, and minimize output file contention by placing output files outside the container and log devices.

6. For each table or view participating in the export operation, you must have DATAACCESS permissions or CONTROL or SELECT privileges.

Example:

1. Export the employee information about Department 20 in the STAFF table of the sample database to exp_sampl_staff.ixf in IXF output format.

D:\ > db2 export to exp_sampl_staff.ixf of ixf messages msgs.txt select * from staff where dept=20

Number of rows exported: 4

2. Export LOB to DEL file.

D:\ > db2 export to myfile.del of del lobs to d:\ mylobs\ lobfile lobs1,lobs2 modified by lobsinfile select * from emp_photo

The SQL3104N EXPORT utility is starting to export data to the file "myfile.del".

The SQL3105N Export utility has finished exporting line "8".

Number of rows exported: 8

3. Export LOB to a DEL file, and specify a second directory for files that may not be loaded into the first directory:

D:\ > db2 export to myfile.del of del lobs to d:\ mylobs1, d:\ mylobs2 modified by lobsinfile select * from emp_photo

The SQL3104N EXPORT utility is starting to export data to the file "myfile.del".

The SQL3105N Export utility has finished exporting line "8".

Number of rows exported: 8

4. Export the data to a DEL file, using single quotation marks as string delimiters, semicolons as column delimiters, and commas as decimal points.

D:\ > db2 export to myfile.del of del modified by chardel'' coldel; decpt, select * from staff

The SQL3104N EXPORT utility is starting to export data to the file "myfile.del".

The SQL3105N Export utility has finished exporting "35" lines.

Number of rows exported: 35

Compare with the contents of a file that does not specify a modifier:

D:\ > db2 export to myfile2.del of del select * from staff

The SQL3104N EXPORT utility is starting to export data to the file "myfile2.del".

The SQL3105N Export utility has finished exporting "35" lines.

Number of rows exported: 35

Some of the contents are compared as follows:

Myfile2: 10, "Sanders", 20, "Mgr", 7 pencil 98357.50

Myfile: 10 ~ (th), 7 ~ (th), 98357, 50

Table export considerations:

1. Some information will not be saved to the exported IXF file if any of the following occurs:

The index column name contains the hexadecimal value 0x2B or 0x2D.

The table contains the XML column.

The table is a multidimensional cluster table (MDC).

The table contains the table partitioning key.

Due to code page conversion, the length of the index name exceeds 128 bytes.

L the table is protected.

The l EXPORT command contains an operation string other than SELECT * FROM tablename.

L specifies the METHOD N parameter to the export utility.

2. If the column name specified in the index contains-or + characters, the index information is not collected and a warning SQL27984W is returned. The EXPORT utility completes the processing and does not affect the exported data. However, the index information is not saved in the IXF file. Therefore, you must use the db2look utility to create the index separately.

3. If the exported data exceeds the amount of free space in the file system where the exported file is created, the export operation will fail. In this case, you should limit the amount of data you select by specifying a condition in the WHERE clause so that the exported file can be stored on the target file system. You can run EXPORT multiple times to export all data.

4. If the IXF file format is not used for export, the output files do not contain a description of the target table, but they contain record data. To recreate the table and its data, create the target table and then populate it with the LOAD or IMPORT utility. You can use the db2look utility to capture the original table definition and generate the corresponding DDL.

After reading the above, have you mastered the method of how to understand DB2 export? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.