Export

 The export utility extracts data from a table into a file.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 theoutput 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.

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 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

SQL3104N The Export utility is beginning to export data to file "c:\deptmgr.del".

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

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

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

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

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

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

 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'