IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SPUFI => CSV


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 46
Location: Germany

PostPosted: Tue Jan 21, 2014 10:06 pm
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: 1788
Location: Bloomington, IL

PostPosted: Wed Jan 22, 2014 6:28 am
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: 46
Location: Germany

PostPosted: Wed Jan 22, 2014 10:56 am
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

Moderator Emeritus


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

PostPosted: Wed Jan 22, 2014 7:29 pm
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: 46
Location: Germany

PostPosted: Thu Jan 23, 2014 1:03 am
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

Superior Member


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

PostPosted: Thu Jan 23, 2014 10:18 am
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: 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: 46
Location: Germany

PostPosted: Thu Jan 23, 2014 10:44 am
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

Superior Member


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

PostPosted: Thu Jan 23, 2014 12:29 pm
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: 765
Location: Whitby, ON, Canada

PostPosted: Thu Jan 23, 2014 8:07 pm
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: 46
Location: Germany

PostPosted: Fri Jan 24, 2014 3:30 pm
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: 2455
Location: Hampshire, UK

PostPosted: Fri Jan 24, 2014 4:47 pm
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

Global Moderator


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

PostPosted: Fri Jan 24, 2014 4:51 pm
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

Superior Member


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

PostPosted: Fri Jan 24, 2014 5:11 pm
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: 46
Location: Germany

PostPosted: Fri Jan 24, 2014 5:25 pm
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

Global Moderator


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

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

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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jan 24, 2014 6:16 pm
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: 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

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: 46
Location: Germany

PostPosted: Fri Jan 24, 2014 6:52 pm
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

Global Moderator


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

PostPosted: Fri Jan 24, 2014 7:05 pm
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: 46
Location: Germany

PostPosted: Fri Jan 24, 2014 7:47 pm
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

Moderator Emeritus


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

PostPosted: Fri Jan 24, 2014 9:45 pm
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
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 46
Location: Germany

PostPosted: Fri Jan 24, 2014 11:51 pm
Reply with quote

SYSPRINT:
Code:

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

I think you are talking about this output.
It ends after the first record!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Sat Jan 25, 2014 12:25 am
Reply with quote

Hello,

Suggest you look in all of the sysout datasets created by the run - including these:
JES2.JESMSGLG
JES2.JESJCL
JES2.JESYSMSG

Also if any sysout data was written to dasd, look at those files as well.

There should be diagnostic info generated icon_confused.gif
Back to top
View user's profile Send private message
vinaysetlur

New User


Joined: 04 Jan 2008
Posts: 13
Location: United States

PostPosted: Sat Jan 25, 2014 11:42 am
Reply with quote

Do you also have a //SYSREC DD for capturing your output data ?
I see you actually invoke DSNTIAUL.
Please refer publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.ugref%2Fsrc%2Ftpc%2Fdb2z_dsntiaul.htm
if that helps.
Back to top
View user's profile Send private message
Mike 1304

New User


Joined: 15 Oct 2011
Posts: 46
Location: Germany

PostPosted: Sat Jan 25, 2014 1:05 pm
Reply with quote

can we try the another way:

Can somebody run a DSNTIAUL with a invalid select statement like SELECT ABC FORM X.Y.Z
and tell me, where he can find a detailed error message?

I think I have a error in the SELECT, but just RC=12 doesn't help me to find it.
Back to top
View user's profile Send private message
vinaysetlur

New User


Joined: 04 Jan 2008
Posts: 13
Location: United States

PostPosted: Tue Jan 28, 2014 2:48 am
Reply with quote

Hope this reference helps. Look in the SYSPRINT/ SYSTSPRT for the error
Detailed discussion on DSNTIAUL
ibmmainframes.com/about9681-0.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts DELETE SPUFI DB2 1
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
No new posts Beautifying the SPUFI output DB2 5
No new posts Using SPUFI with DISTINCT and MAX is ... DB2 8
No new posts SPUFI -- Joining 3 tables – data in... DB2 2
Search our Forums:

Back to Top