Unloading to a Fixed-Text File

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

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.