Procedure to create a text file in DB2 on AS400

To my knowledge, the easiest way to create an export of data is to use "Create Table As" in SQL.

e.g.
create table MyScema.SomeData as (
  select someCode,
         someDescription
    from Production.MyCodes
   where someOtherColumn = 5      
) with data

Let me know if that meets your needs.

HTH,
DaveSlash

Define "text file". And define "output of a query". Also, what query product are you using? There is no "7.5 version of DB2/AS400". The latest release is 7.2.

Tom

It's been a few days, and I haven't seen any response, but I'll just add this one more suggestion that I've had success with in the past.

I use the "Create Table As" functionality to create a flat-file (as I described above). Then, I have an FTP script on my Wndows pc that connects to the '400 and ftp's the flat-file to my pc.

Works beautifully.

HTH,
DaveSlash

Sorry Experts :) for being not updating the post, actually I have requirement that the output of a query should be extracted in text file in specified format that each field should be separated by ~ (tild) and save a text file.

A sample file with the required output is attached.

What I have in my mind is that, I create a procedure with those queries running and inserting/adding the output of the quires in the text file and save in a particular location.

Thanks again for your valuable feedback and syntax to create a procedure to create a text file in AS400(db2).
KAAPSGP1436-092006042015.txt

As mentioned above, CPYTOIMPF will create a tilde-delimited file from a database file.   It sould help if you showed us what the database table that you are querying to produce this looks lieke.

At present I have developed a report to generate the output as in attached file using SSRS. will attached a report (rdl) file too.

Please could you help me to get the output of the report to text file directly, as i can't export a report in text format directly from SSRS.
PA---EDI-TEXT-FILE.rdl
KAAPSGP2016-025925022016.TXT

The procedure that I described works perfectly for this.  Use CREATE TABLE to create the output table with the correct columns in the correct sequence, and then use CPYTOIMPF, specifying the tilde delimiter to create the text file in the IFS.

Note that you will have to output the column headings, since CPYTOIMPF doesn't do that for you.

Just put the headings in a text file, and concatenate the heading file and the output file from CPYTOIMPF.

Thanks for the response, it will be very helpfull shall i have a sample / steps how to do it.

Please could any body provide me sample steps.