Procedure to create a text file in DB2 on AS400

I want to create a procedure for creating a text file to store the output of a query. In DB2 running on AS400. I am using 7.5 version of DB2/AS400.
Syed Shareef AhmedSenior System AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
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
0
Gary PattersonVP Technology / Senior Consultant Commented:
IBM i provides the CPYTOIMPF command to export a database table as a text file.  So use CREATE TABLE as DaveSlash shows above to create an output table, and then use the CPYTOIMPF command (IBM provides command documentation in the IBM Knowledge Center) to copy the file from the DB2 table to a text file in the IFS.
0
tliottaCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

tliottaCommented:
A "text file" might be a single-field physical file you want to store lines in, or it might be a normal streamflie. The two types of "text file" could require different steps. "Output of a query" should refer to a result set, but I suspect that you want the result set formatted in some way perhaps as a report but perhaps as a .CSV or something else. The query product could be the older Query/400 product or it might be one of the SQL-based products, perhaps just SQL by itself.

There are many variations possible. E.g., you might simply use the Qshell db2 utility and redirect its output to a streamfile. That'd be the most direct way to get what you asked for, but it might not be what you need. The db2 utility output format is in the form of a 'report' suitable for printing.

In order to give a useful answer, we need to know more about what you need.

Tom
0
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
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
0
Gary PattersonVP Technology / Senior Consultant Commented:
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.
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
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
0
Gary PattersonVP Technology / Senior Consultant Commented:
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.
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Thanks for the response, it will be very helpfull shall i have a sample / steps how to do it.
0
tliottaCommented:
In recent IBM i versions, the CPYTOIMPF ADDCOLNAM() parameter can add either system or SQL column names to output if headings are needed.
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Please could any body provide me sample steps.
0
Gary PattersonVP Technology / Senior Consultant Commented:
Something like this:

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

CPYTOIMPF FROMFILE(MYSCHEMA/SOMEDATA)  
          TOSTMF('/myolder/myfile.txt')
          MBROPT(*REPLACE)              
          STMFCCSID(*PCASCII)          
          RCDDLM(*CRLF)                
          STRDLM(*NONE)                
          FLDDLM('~')                  
          ADDCOLNAM(*SQL)               --Or *SYS
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.