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
 

 

SPUFI => CSV
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Tue Jan 21, 2014 10:06 pm    Post subject: SPUFI => CSV
Reply with quote

Is it possible to store the Output from SPUFI in a CSV-File?
I want to Transfer it to Excel, so I want only one heading.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


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

PostPosted: Wed Jan 22, 2014 6:28 am    Post subject:
Reply with quote

Are we talking actual SPUFI, or DSNTEPn?
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Wed Jan 22, 2014 10:56 am    Post subject: Reply to: SPUFI => CSV
Reply with quote

I'm talking about SPUFI under ISPF, but if there is a solution only for a batchjob calling DSNTEP4, I'm happy too.
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 Jan 22, 2014 7:29 pm    Post subject:
Reply with quote

Hello,

You can code an SQL to select the columns you want interspersed with your delimeter.

i.e.
Code:
SELECT LASTNAME, X'05',
       FIRSTNAME, X'05'
       DEPARTMENT, X'05'
       STATUSDATE

will create output of those columns with a tab delimeter between them.
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Thu Jan 23, 2014 1:03 am    Post subject:
Reply with quote

Hi Dick,

yes this helps, but still I have to insert delimiters in the first heading, delete the other headings, the output at the beginning and at the end.

Is there no function to export the output from SPUFI as a CSV file?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jan 23, 2014 10:18 am    Post subject:
Reply with quote

Mike 1304 wrote:
Is there no function to export the output from SPUFI as a CSV file?
AFAIK, no there is no "direct function" as such and you've to do a bit of home-work in between.

You can try DSNTEP2 (SPUFI in batch, grumble) but the output will still have column headers (like SPUFI) - so this might not be of help for what you're after, I believe. Actually, DSNTEP2 is an IBM sample program written in PL/I. And it never runs a ROLLBACK after SQL errors and it becomes nasty when when you run multiple UPDATEs.

However, based on the DSNTEP2/4 code there is a CBT Tape file, which is C-version of the program with many other features and one of them is to WRITE a FILE before a SELECT statement to create a flat file with its report. You can download it from here: http://cbttape.org/cbtdowns.htm , FILE 866.

If CBT Tape is not your cup of tea at the moment, use DSNTIAUL. It will create a file without headings. And as you talked about exporting the output to a spread-sheet and if you have some DECIMAL in your table and you want to see them as character, you can use the SQL DIGITS() function.

Hope this helps.
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Thu Jan 23, 2014 10:44 am    Post subject:
Reply with quote

Thanks, for your hints, especially that there is no ready to use function.
I think I'll write an edit macro do make a csv file from spifi output.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jan 23, 2014 12:29 pm    Post subject:
Reply with quote

Mike 1304 wrote:
Thanks, for your hints, especially that there is no ready to use function.
I think I'll write an edit macro do make a csv file from spifi output.
As you said before that batch Job can be an option, try using DSNTIAUL, if you've not tried it yet. It might save your time writing a macro to remove headers, fwiw.
Back to top
View user's profile Send private message
don.leahy

Active Member


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

PostPosted: Thu Jan 23, 2014 8:07 pm    Post subject:
Reply with quote

The UNLOAD utility can produce CSV files. However it has limitations in the type and complexity of the WHERE clause that you can use.
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Fri Jan 24, 2014 3:30 pm    Post subject:
Reply with quote

I'm not familiar with DSNTIAUL, but I created a job, just with select * from a small table to see the result.
But I get a cc 12:
DSNTIAUL could not open a data set, an SQL statement returned a severe error code (-144, -302, -804, -805, -818, -902, -906, -911, -913, -922, -923, -924, or -927), or an error occurred in the SQL message formatting routine.
Where can I find more details on the eror reason?
What dataset?, What sql error code?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Fri Jan 24, 2014 4:47 pm    Post subject: Reply to: SPUFI => CSV
Reply with quote

Quote:
Where can I find more details on the eror reason?
What dataset?, What sql error code?

Ever thought about the manual(s)? Not sure which manual? - Google, or 'Look-at'
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 24, 2014 4:51 pm    Post subject:
Reply with quote

Can you post the job you used?

Also lot of samples about DSNTIAUL available in forum
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Jan 24, 2014 5:11 pm    Post subject:
Reply with quote

Mike 1304 wrote:
I'm not familiar with DSNTIAUL, but I created a job, just with select * from a small table to see the result.
But I get a cc 12:
You should have posted the Job you've used and the SYSOUT from the failed Job to help us to help you. Guessing is a tough game.

OTOH, look here APPENDIX1.4.1 Running DSNTIAUL - this gives the working example of using DSNTIAUL. Try a job from there and see if you still get some error.
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Fri Jan 24, 2014 5:25 pm    Post subject:
Reply with quote

I also tried both output dataset as dummy, still CC=12.
So it must be a SQL problem(?):
Code:
//UNLOAD  EXEC PGM=IKJEFT01,DYNAMNBR=20                             
//SYSTSPRT DD  SYSOUT=*                                             
//SYSTSIN  DD  *                                                   
 DSN SYSTEM(UWD0)                                                   
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTEP81) -                           
 LIB('SYS4.DB2.V8R1M0.NFM.RUNLIB.LOAD')                             
//SYSPRINT DD SYSOUT=*                                             
//SYSUDUMP DD SYSOUT=*                                             
//SYSREC00 DD DSN=P686263.IT2.DSNTIAUL.OUT,                         
//            UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(OLD,CATLG) 
//SYSPUNCH DD DSN=P686263.IT2.PUNCH,                               
//            UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(OLD,CATLG),     
//            RECFM=FB,LRECL=120,BLKSIZE=1200                       
//SYSIN    DD *                                                     
   SELECT * FROM OWTAACUW.TTAAPPRJ                                 
   ;                                                               
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 24, 2014 5:48 pm    Post subject:
Reply with quote

Please post the output of the job
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Jan 24, 2014 6:16 pm    Post subject: Reply to: SPUFI => CSV
Reply with quote

Quote:
However, based on the DSNTEP2/4 code there is a CBT Tape file, which is C-version of the program with many other features and one of them is to WRITE a FILE before a SELECT statement to create a flat file with its report. You can download it from here: http://cbttape.org/cbtdowns.htm , FILE 866.

If CBT Tape is not your cup of tea at the moment, use DSNTIAUL. It will create a file without headings. And as you talked about exporting the output to a spread-sheet and if you have some DECIMAL in your table and you want to see them as character, you can use the SQL DIGITS() function.


IMNSHO icon_wink.gif

for the majority of the people hanging around here

it would be wiser to look at

http://www.idug.org/p/cm/ld/fid=89

for
CSNTEP2 ( cobol clone of dsntep2)
RUNSQL - advanced diagnostics and tracing for DSNREXX
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Fri Jan 24, 2014 6:52 pm    Post subject:
Reply with quote

SYSPRINT:
Code:

********************************* TOP OF DATA *****
 DSNT490I SAMPLE DATA UNLOAD PROGRAM               
                                                   
******************************** BOTTOM OF DATA ***

SYSTSPRT:
Code:

********************************* TOP OF DATA *****************************************
READY                                                                                 
 DSN SYSTEM(UWD0)                                                                     
DSN                                                                                   
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTEP81)  LIB('SYS4.DB2.V8R1M0.NFM.RUNLIB.LOAD')         
DSN                                                                                   
END                                                                                   
******************************** BOTTOM OF DATA ***************************************

Was für ein Output wäre denn noch interessant?[/code]
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jan 24, 2014 7:05 pm    Post subject:
Reply with quote

German :
Quote:
Was für ein Output wäre denn noch interessant


English:
Quote:
What would be more interesting for an output


Atleast a few here are interested in the SQLCODE output
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 43
Location: Germany

PostPosted: Fri Jan 24, 2014 7:47 pm    Post subject:
Reply with quote

What is the SQLCODE output?
Do I have to insert a special DD card to get this output?
Or is it the SYSPRINT (see previous posting)?

When I googled, I found some output like my single line followed by a "SQL OPTION IN EFFECT" (I don't remember the exact words).
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: Fri Jan 24, 2014 9:45 pm    Post subject:
Reply with quote

Hello,

Somewhere in the generated output will be the diagnostic info.

When i run an SQL query in batch, this output is in the SQL1.SYSPRINT where the step execute is:
//SQL1 EXEC PGM=IKJEFT1B,
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problems in File-Aid / DB2 Spufi whil... amitc23 CLIST & REXX 6 Fri Feb 12, 2016 1:59 pm
No new posts Not able to access the DB2 via SPUFI sakthikumarT DB2 6 Sat Nov 28, 2015 1:52 am
No new posts DB2 program returned data is dif than... pradeepkit DB2 1 Tue Mar 03, 2015 5:39 am
No new posts How to skip foriegn Key constraint in... Arunkumar.chinnappa DB2 5 Fri Feb 07, 2014 2:17 pm
No new posts Suppress Warning messages in SPUFI Dsingh29 DB2 3 Thu Apr 04, 2013 1:01 pm


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