Syed Shareef Ahmed
asked on
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Tom
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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.
ASKER
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
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.
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.
ASKER
Thanks for the response, it will be very helpfull shall i have a sample / steps how to do it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please could any body provide me sample steps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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