Q

Data export command: Migration from a DB2 table to a flat file

To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a database to an operating system file, which, our expert explains, can be in one of several external file formats.

We are trying to export data from a DB2 table to a flat file by the following command:

db2 -tvf file.sql -z file.out

But it displays the output as below (i.e., it produces output including the sql, column name and the message ?EURoe2 record(s) selected?EUR?):

select tabname from syscat.tables where tabschema = 'VSC'
TABNAME
VEHICLE_TEMP
VSC_DASHBOARD_INTER

2 record(s) selected.

But we need only the records in the output file as appears below:

VEHICLE_TEMP
VSC_DASHBOARD_INTER

Please help us with this query.

You need to run an export command specifying the proper file format. The export command exports data from a database to an operating system file, which can be in one of several external file formats.

The following information is required when exporting data using the export command/utility:

 

  • An SQL SELECT statement specifying the data to be exported.
  • The path and name of the operating system file that will store the exported data.
  • The format of the data in the file. This format can be IXF, WSF, or DEL.

You might want to download a (free) copy of the IBM DB2 Data Movement Utilities Guide and Reference which contains information on how to formulate your export command and definitions of each of the various types of formats.

Quickly though, perhaps you are interested in creating a comma-delimited file. In that case, you would use the DEL format (delimited ASCII). A Delimited ASCII (DEL) file is a sequential ASCII file with row and column delimiters. Each DEL file is a stream of ASCII characters consisting of cell values ordered by row, and then by column. Rows in the data stream are separated by row delimiters; within each row, individual cell values are separated by column delimiters. When creating an ASCII delimited file (specifying a comma as the delimiter) you're file output would look something like this:

"Smith, Bob",4973,15.46
"Jones, Bill",12345,16.34
"Williams, Sam",452,193.78

Here is an example of an export requesting ASCII delimited output:

db2 export to test.del of del select tabname from syscat.tables where tabschema ='VSC';

 

  • Learn how using the data export command or copy utility is the best way to backup a database with SQL.
This was first published in April 2007

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close