Unloading to a Fixed-Text File

You can unload data from the database into fixed format files.

About this task

The following SQL statements unload the employee table in fixed text format into the emp_ext external table:
CREATE EXTERNAL TABLE emp_ext 
   ( name CHAR(18) EXTERNAL CHAR(20),
     hiredate DATE EXTERNAL CHAR(10),
     address VARCHAR(40) EXTERNAL CHAR(40),
     empno INTEGER EXTERNAL CHAR(6) )
USING (
   FORMAT 'FIXED',
    DATAFILES ("DISK:/work2/mydir/emp.fix")
    );
INSERT INTO emp_ext SELECT * FROM employee;

These statements create a fixed-text file with 20 character positions in the first field, the next 10 character positions in the second field, and so on. Because you are choosing the rows with a SELECT statement, you can format the SELECT list in any way that you want.