View previous topic :: View next topic
|
Author |
Message |
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
Are we talking actual SPUFI, or DSNTEPn? |
|
Back to top |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Can you post the job you used?
Also lot of samples about DSNTIAUL available in forum |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Please post the output of the job |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
Back to top |
|
|
vinaysetlur
New User
Joined: 04 Jan 2008 Posts: 13 Location: United States
|
|
Back to top |
|
|
Mike 1304
New User
Joined: 15 Oct 2011 Posts: 46 Location: Germany
|
|
|
|
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 |
|
|
vinaysetlur
New User
Joined: 04 Jan 2008 Posts: 13 Location: United States
|
|
Back to top |
|
|
|