Understanding DB2: Learning Visually with Examples (2nd Edition)

12.3. The DB2 EXPORT Utility

The export utility extracts data from a table into a file. Figure 12.4 shows the syntax diagram of the export command.

As you can see, the command supports many different options. Let's start with a simple export command and discuss how to use the options to customize the command. The following example of the export command exports all the rows in the employee table to the file empdata.ixf in IXF format.

export to empdata.ixf of ixf select * from employee

All the keywords in this command are mandatory, that is, you have to provide the output file name, specify the file format, and the SELECT statement that will retrieve the rows to be exported. The exported file can be in a format of DEL, IXF, or WSF.

Using the optional messages clause you can specify a file name where warning and error messages of the export operation are logged. If no message file accompanies the messages clause, the messages are written to standard output. Though optional, we highly recommend you use this clause so that all the messages generated by the utility are saved.

Figure 12.4. Syntax diagram of the export command

>>-EXPORT TO--filename--OF--filetype----------------------------> >--+-----------------------+--+-----------------------+---------> | .-,--------. | | .-,--------. | | V | | | V | | '-LOBS TO----lob-path-+-' '-LOBFILE----filename-+-' >--+-------------------------------+----------------------------> | .--------------. | | V | | '-MODIFIED BY----filetype-mod-+-' >--+---------------------------------+--------------------------> | .-,-----------. | | V | | '-METHOD N--(----column-name-+--)-' >--+------------------------+-----------------------------------> '-MESSAGES--message-file-' >--+-select-statement------------------------------------------+->< '-HIERARCHY--+-STARTING--sub-table-name-+--+--------------+-' '-| traversal-order-list |-' '-where-clause-' traversal-order-list: .-,--------------. V | |--(----sub-table-name-+--)-------------------------------------|

The export command also supports SELECT statements with join. Thus, if you want to export data from two tables, they can be joined as shown in the following example:

export to deptmgr.del of del messages deptmgr.out select deptno, deptname, firstnme, lastname, salary from employee, department where empno = mgrno

The above example joins the employee and department tables to obtain information for each department manager. If the command is successfully executed, the number of rows exported is returned :

Number of rows exported: 8

When the command finishes successfully with no warning or error message, the message file deptmgr.out will only include entries that indicate the beginning and end of the utility execution:

SQL3104N The Export utility is beginning to export data to file "c:\deptmgr.del". SQL3105N The Export utility has finished exporting "8" rows.

12.3.1. File Type Modifiers Supported in the Export Utility

The export utility exports data to a file using default file formats. For example, as mentioned earlier, if you are exporting a table to a file in DEL format, the default column delimiter is a comma, and the default string delimiter is the double quote. What happens if the table data to be exported contains these delimiters as part of the data? The file exported may contain data that can be confused as a delimiter, making it impossible for an import or load operation to work correctly. To customize the export file format to use different delimiters, use the modified by clause. The following sections introduce some of the most common file type modifiers. For a complete list of the modifier options, see the DB2 UDB Data Movement Utilities Guide and Reference.

12.3.1.1 Changing the Column Delimiter

To use a different column delimiter other than the comma, specify the coldel file type modifier in the modified by clause. The following example specifies to use a semicolon as the column modifier. Note that there is no space between the keyword coldel and the semicolon.

export to deptmgr.del of del modified by coldel; messages deptmgr.out select deptno, deptname, firstnme, lastname, salary from employee, department where empno = mgrno

12.3.1.2 Changing the Character Delimiter

You can enclose character strings with a different delimiter by using the keyword chardel. Continuing with the previous example, the character delimiter used here is a pair of single quote.

export to deptmgr.del of del modified by coldel; chardel'' messages deptmgr.out select deptno, deptname, firstnme, lastname, salary from employee, department where empno = mgrno

12.3.1.3 Changing the Date Format

You can also export data in a specific date format you prefer by using the timestampformat modifier.

export to deptmgr.del of del modified by coldel; chardel'' timestampformat="yyyy.mm.dd hh:mm" messages deptmgr.out select deptno, deptname, firstnme, lastname, salary from employee, department where empno = mgrno

12.3.1.4 Changing the Code Page

In many cases, the code page of the target database server is not the same as the source server. To ensure data is handled correctly in the target server, you should pay attention to the code page of the exported data. By default, data exported is in the same code page as the application for which the export command is invoked. With the export utility, you can use the codepage modifier to convert character data from the application.

export to deptmgr.del of del modified by coldel; chardel'' timestampformat="yyyy.mm.dd hh:mm" codepage=1208 messages deptmgr.out select deptno, deptname, firstnme, lastname, salary from employee, department where empno = mgrno

Note that this modifier cannot be used with the lobinsfile modifier, which is discussed in the next section.

12.3.2. Exporting Large Objects

DB2 supports the following types of large objects: character large objects (CLOBs), binary large objects (BLOBs), and double-byte character large objects (DBCLOBs). LOB values can be as large as 2GB for CLOBs and BLOBs and 1GB for DBCLOBs. Due to these sizes, the export utility by default extracts only the first 32KB of data of the LOB values in the export file. To extract the entire LOB, you must use the lobsinfile modifier. All the LOB values for a particular LOB column are stored in a single file that is separate from the regular export data file. The export data file, however, contains a LOB location specifier (LLS) to link the regular data for the row with the LOB data of this row. Since all LOB values are stored in one file, the LLS string indicates the starting position (offset) where the associated LOB data can be found and the length of the LOB. The format of the LLS is

filename.ext.nnn.mmm

where:

• filename.ext is the name of the file that contains the LOB.

• nnn is the offset (measured in bytes) of the LOB within the file.

• mmm is the length (measured in bytes) of the LOB.

For example, the following export command generates three files. One file is the message file, mgrresume.out. Another file, mgrresume.del, is the data file, which contains all data columns for the rows except the LOB data. The third file, resume.001, is the file containing the LOB values for all rows.

export to mgrresume.del of del messages mgrresume.out lobs to c:\lobs lobfile resume modified by lobsinfine select deptno, deptname, firstnme, lastname, resume from employee a, emp_resume b where a.empno = b.empno

Note that the output file mgrresume.del contains the LLS instead of the LOB data. Figure 12.5 illustrates the contents of mgrresume.del. Notice that in the third column the LLS value is resume.001.0.1313, which means that the LOB of this record is stored in file resume.001. It begins at an offset of 0 bytes, then follows by the size of the LOB (1313 bytes). The following LLS entry shows the LOB data for the next row is also stored in file resume.001.0.1313 starting at offset 1313 and with a length of 1817 bytes. The next entry would start at offset 3130 (1313 + 1817). If the indicated size in the LLS is 0, the LOB is considered to have a length of 0. If the length is -1, the LOB is considered to be NULL and the offset and file name are ignored.

12.3.3. Specifying Column Names

The method n (column names) option is useful when a column is derived from one or more columns. For example, if you use the following SELECT statement in the export command:

SELECT empno, firstnme, lastname, salary * 1.3 FROM employee WHERE workdept='A00'

the following shows what the output of the SELECT statement would be. Notice that the last column in the select list is a derived column that does not have a column name.

EMPNO FIRSTNME LASTNAME 4 ------ ------------ --------------- ------------- 000010 CHRISTINE HAAS 130.000 000110 VINCENZO LUCCHESSI 60450.000 000120 SEAN O'CONNELL 38025.000

The import utility (which is discussed in more detail in section 12.4, The DB2 import Utility) can be executed with a create option that lets you create the target table if it does not already exist before data is imported. The input file must also contain the definition of the table. If you were to import the above result with the create option, the newly created table would have the fourth column named 4. Rather than using a number, you can provide a more descriptive name using the AS clause in the SELECT statement:

export to newsalary.ixf of ixf messages newsalary.out select empno, firstnme, lastname, salary * 1.3 as new_salary from employee where workdept='A00'

Alternatively, use the method n option to explicitly specify all the column names. This option is only supported when the export file format is IXF or WSF.

export to newsalary.ixf of ixf messages newsalary.out method n ('EMPLOYEENO', 'FIRSTNAME', 'LASTNAME', 'NEWSALARY') select empno, firstnme, lastname, salary * 1.3 from employee where workdept='A00'

With the method n clause and the specified columns, the resulting file will contain the new column names:

EMPLOYEENO FIRSTNAME LASTNAME NEWSALARY ---------- ------------ --------------- ------------- 000010 CHRISTINE HAAS 130.000 000110 VINCENZO LUCCHESSI 60450.000 000120 SEAN O'CONNELL 38025.000

12.3.4. Authorities Required to Perform an Export

There is no special authorization requirement to perform an export. Any authenticated user is able to execute the export command. However, the user must be able to access the data of the table being exported. Therefore, the user must hold SYSADM, DBADM, CONTROL, or SELECT privileges on each table or view referenced in the SELECT statement of the command.

12.3.5. Exporting a Table Using the Control Center

You can also perform an export from the Control Center. In the Control Center right-click on the table you want to export and select the Export option as shown in Figure 12.6.

This displays the Export Table dialog (see Figure 12.7). You can specify all the options discussed earlier in this chapter in this dialog, such as the output file, message file, file format, and the SELECT statement.

To specify the column names and LOB options, switch to the Columns tab (illustrated in Figure 12.8).

The last tab, Schedule, lets you run the export now or schedule it to run at some other time (see Figure 12.9).