Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL query results in CSV format

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Wed Sep 14, 2011 1:48 am    Post subject: SQL query results in CSV format
Reply with quote

Is there a ready made utility that can read an sql query from a dd, run it dynamically, and ouput the results in a CSV format? This is useful for importing into other tools such as Excel spreadsheets.

The DSNTIAUL utility will execute sql but it outputs the results as "raw" data. It is difficult to import this into other tools.

DSNTEP2 will do something similiar but it wraps a long row onto multiple pages. I want to have one row per line

I could cheat and code the sql to output the results as character strings and then insert a literal comma between each column. I would rather the utility does the work for me.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1754
Location: Bloomington, IL

PostPosted: Wed Sep 14, 2011 2:19 am    Post subject:
Reply with quote

I don't know of any. Which, of course, doesn't mean that there isn't any.

Do you have Rexx at your disposal? How about the OpenSoftware REXXTOOLS package?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 14, 2011 2:45 am    Post subject:
Reply with quote

Hello,

Read here and see if this helps:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnugj10/2.30.4

Scroll down to Example 9 -
Example 9: Unloading data into a delimited file.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Sep 14, 2011 3:33 am    Post subject:
Reply with quote

Quote:
DSNTIAUL utility will execute sql but it outputs the results as "raw" data


only if you just do select column
you can cast columns to the result that you want,
select int(col1)
, ';'
. char(col2)
...
DSNTIAUL is actually SPUFI in batch.

you can also use other formating techniques with the link that
Dick Scherrer. provided.
depends on what is easiest for you to generate.
Back to top
View user's profile Send private message
Eshwar CICS

New User


Joined: 18 May 2011
Posts: 47
Location: India

PostPosted: Wed Sep 14, 2011 10:31 am    Post subject:
Reply with quote

Hi Jerryte,

If you have access to RDZ, in data perspective you can save results of your query in CSV, HTML, XML and other supported options.Thanks.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 14, 2011 12:02 pm    Post subject:
Reply with quote

maybe unload delimited can help you.
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Wed Sep 14, 2011 6:51 pm    Post subject:
Reply with quote

Thanks for the tips. I know about the UNLOAD delimited but this outputs the entire row for a single table and does not allow for SQL. Maybe someone at IBM can one day modify the DSNTIAUL to allow for CSV formatted output. It would be nice.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 650
Location: Whitby, ON, Canada

PostPosted: Wed Sep 14, 2011 7:42 pm    Post subject:
Reply with quote

Don't give up on UNLOAD until you have investigated the WHEN parameter.

WHEN allows you to code search conditions that might be enough to meet your requirement.

If not, then your best bet is to use the technique suggested by Dick. It's the one that I use when I want to produce delimited output from a complex query (i.e. one that is too complex to be satisfied by UNLOAD....WHEN).
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts How to pick only YYMMDD from DATE1P (... atulbaviskar SYNCSORT 7 Wed Mar 22, 2017 11:39 am
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us