Creating a new file from an existing file

If you have FILEA and want to create FILEB using some or all of the data from FILEB, SQL is a good choice.

With SQL you can create an exact copy of a file, a derived file (only some of the fields and/or some of the records), or you can create a new file based on the format of the existing file without copying any of its data.

Create a new, empty file using the Format of an Existing File

create table coztest/customer as 
( select * from proddata/customer) with NO DATA

The file CUSTOMER is created in library COZTEST inheriting its fields and their attributes from the existing file named CUSTOMER in PRODDATA. The new file is empty.

Create a new file using the Format of an Existing File and include its data (CPYF)

create table coztest/customer as 
( select * from proddata/customer) with DATA

The file CUSTOMER is created in library COZTEST inheriting its fields and their attributes from the existing file named CUSTOMER in PRODDATA. All data from CUSTOMER in PRODDATA is copied to CUSTOMER in COZTEST; essentially making a copy of the file.

The SQL WITH DATA clause at the end of the CREATE TABLE statement causes the data in the subquery's result set to be copied to the new table.

If you need to create a subset of the original file as a new file, then use your SQL knowledge and specify the fields to be included on the SELECT clause. If you only need specific records, simply include the WHERE clause on the subquery.

Create a new file using some of the fields of an Existing File and include some of its data.

create table coztest/ILContact as
  ( select cstnbr,cstname,addr1,addr2,city,state,zipcode
    from proddata/customer where STATE = 'IL') with DATA

This statement creates the file named ILCONTACT (Illinois Contacts) in library COZTEST and includes the fields listed. Any other fields in the existing CUSTOMER file are not included. Only records containing 'IL' in the STATE field are copied to the new file.