SQL script output to a file

415 pts.
Tags:
AS/400 database
AS/400 DB2
iSeries database
iSeries DB2
iSeries Navigator
SQL scripts
V5R4
I am running ad hoc SQL scripts in iSeries Navigator (V5R4) and need to direct the output to a text file. Is there a way to do this? TIA... Steve B

Answer Wiki

Thanks. We'll let you know when a new response is added.

If by “text” file you mean a non-DB2 file, I don’t know how to do that directly.

Any SELECT statement can output to a DB2 file by wrapping it with an INSERT statement. But you have to define the DB2 file. It can then be copied anywhere as a “text” file.

If you want to output any SELECT statement into a fixed length, single long record file (which I do a lot to create free form log files) you will still wrap the SELECT with an INSERT but you will have to CONCAT the fields into the one long string field. You’ll also have to CAST numeric fields to STRING. It’s still a DB2 file but with 1 long field per record. Although a little tedious, it’s easy to do.

Does this help?

Otra forma seria generar el archivo en el servidor y descargar la informaciĆ³n via Trasnferencia de Archvios de estar utilizando Client Access.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SBatSI
    Thanks for the response... By a "text file" I mean a .txt file in the Windows environment. Because I typically use desktop tools to massage the results of ad hoc queries, it would be very handy to be able to send the results directly to a .txt file. In cases where the payoff is sufficient to justify doing this much work, I could certainly use the approach you suggest of INSERTing the output of the SELECT statement into a physical file I have created; I could then use CPYTOIMPF to transfer the data to a .txt file. It's a shame this cannot be done directly. Regards, Steve B
    415 pointsBadges:
    report
  • Cwc
    Your request sounds simple enough and like it would be supported through Navigator, but I can't think of a way off hand to direct that output to a file. I figured copying and pasting might work, but the interface doesn't provide that either. Do you use any .Net or Java applications? If so, you could use that architecture to initiate an SQL request and receive its result set back into the code, where you could loop through and write them to a file.
    4,290 pointsBadges:
    report
  • DoneThat
    You don't have to use CPYTOSTMF to get your file to Windows. You can set up a ".DTT" data transfer and the file will be transferred with a double-click. And it could/would be a ".TXT" file. Fixed length fields and all. Yes I know - not direct. I have one more way out suggestion. Techtarget just ran 2 articles - one of which was "Map Your Network Drives to the IBM i". If you output directly from the AS400 to the mapped drive, you could avoid a file transfer of any kind. Trying!
    830 pointsBadges:
    report
  • graybeard52
    A great product from ad hoc SQL is F.R.O.G. Its available for no charge from www.innovativesys.com
    3,115 pointsBadges:
    report
  • DoneThat
    Hello GB52... This is the 2nd time I've tried F.R.O.G. It connects to my AS400 but most options are "greyed out". I can enter an SQL statement but there is no way to run it. F5 doesn't react, "execute" isn't available. My libraries and files are visible so it's there and working. I've tried breaking/remaking the connection - no joy. FYI: I'm on V5R2 and XP SP3. What am I missing that I need to make an SQL run?
    830 pointsBadges:
    report
  • graybeard52
    I'm not sure if the out-of-date OS is the probem. SQL has progrssed a great deal in V5R3 and V5R4. I suggest you post the FROG question over on www.code400.com in the FROG forum. The author is a regular there and is very helpful.
    3,115 pointsBadges:
    report
  • SBatSI
    Greybeard52 - Thanks for the pointer to FROG - it looks very promising. I believe this is the correct link. Regards, Steve B
    report
  • SBatSI
    Silly me, I read the help text regarding inserting links, but I guess I didn't do enough reading between the lines... Here's another try. http://www.innovativesys.net/?page_id=4
    415 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following