|
View previous topic :: View next topic
|
| Author |
Message |
mtridib
New User
Joined: 30 Mar 2006
Posts: 4
|
|
|
|
| Can anyone please give me a sample JCL to capture the SPUFI result in a dataset? I will be doing a SELECT query only in the SPUFI |
|
| Back to top |
|
 |
tusharguptait
New User
Joined: 15 Feb 2006
Posts: 32
|
|
|
|
Hi
Couldn't understand ur query. SPUFI result is moved to a output dataset itself. And If u mean SPUFI in batch then use DSNTEP2 (search forum ).
Regards
TG |
|
| Back to top |
|
|
mtridib
New User
Joined: 30 Mar 2006
Posts: 4
|
|
|
|
I will be doing the SPUFI in the JCL. Not the SPUFI option in mainframe that we usually have.
Will give u n example.
| Code: |
//TESTSPUF EXEC PGM=IKJEFT01,REGION=4M
//STEPLIB DD DSN=LOADLIBRARY,DISP=SHR
//SYSIN DD *
SELECT
B.COL1
CASE SUBSTR(A.COL2,2,1)
WHEN 'A' THEN '11111'
WHEN 'B' THEN '22222'
WHEN 'C' THEN '33333'
END AS PROD_LINE,
A.COL3
FROM TABLE1 A,
TABLE2 B
WHERE A.KEY = B.KEY
//SYSTSIN DD *
DSNECP00 SYSTEM(ENVIRONMENT)
RUN PROGRAM (DSNTEP2) PLAN (DSNTEP2) -
LIB('DBB.W.RUNLIB.LOAD')
END
//* |
How do I capture the results in this case |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
mtridib,
Add this SYSPRINT to your Job Step.
| Code: |
//SYSPRINT DD DSN=HLQ.SPUFI.RESULT,DISP=(,CATLG,DELETE),
// LRECL=LL,RECFM=FB,AVGREC=U |
Result would be stored in the dataset.
Regards,
Priyesh. |
|
| Back to top |
|
|
Jon_Walker
New User
Joined: 02 Jun 2008
Posts: 6
Location: Ottawa, Canada
|
|
|
|
DSNTEP2 looks great, but will the output have column headers (like SPUFI)? If so, how do you get rid of them (from your output file).
Someone on another forum mentioned REXX/SORT. I have no experience with REXX, so help would be appreciated.
Jon  |
|
| Back to top |
|
|
anv2005
New User
Joined: 14 Jul 2005
Posts: 44
Location: US
|
|
|
|
Please explain whether or not you want to see column names in your output, and if not why? Please provide an example of desired output.
There is a SPUFI product that would run in batch. Take a look at the forum's thread below: http://forum.relarc.com/phpbb3/viewtopic.php?f=7&t=17
Alex. |
|
| Back to top |
|
|
Jon_Walker
New User
Joined: 02 Jun 2008
Posts: 6
Location: Ottawa, Canada
|
|
|
|
I'm using DSNTEP2 (batch SPUFI) to extract SIN numbers, that will form a data feed to a business process that executes after my JCL. So column headers are NOT a desired option!
The output will be a single column of 9 digit SIN numbers.
999999999
111222333
444555666
777888999
etc...... |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Are you having any unload utilities (BMC) at your shop. ???
Thanks,
Arun |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You might look into submitting your query using your sort product as well. |
|
| Back to top |
|
|
Vladimir Mestovski
New User
Joined: 15 Nov 2011
Posts: 4
Location: Belarus
|
|
|
|
| DSNTEP2 is an IBM sample program written in PL/I. It never runs a ROLLBACK after SQL errors, that's very bad when you run multiple UPDATEs. Based on the DSNTEP2/4 code I created a C-version of the program with many new features, one of them is WRITE [dlm] FILE before a SELECT statement to create a flat file with its report. The program is free and can be downloaded from cbttape.org/cbtdowns.htm FILE 866. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Jon_Walker wrote: |
I'm using DSNTEP2 (batch SPUFI) to extract SIN numbers, that will form a data feed to a business process that executes after my JCL. So column headers are NOT a desired option!
The output will be a single column of 9 digit SIN numbers.
999999999
111222333
444555666
777888999
etc...... |
Use DSNTIAUL. It will create a file without headings.
If your SINs are in DECIMAL format and you want to see them as character, you can use the SQL DIGITS() function. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Don, unlucky. You fell for the multiple-tailgating. Jon was the original tailgater, Vladimir the latest. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Bill Woodger wrote: |
| Don, unlucky. You fell for the multiple-tailgating. Jon was the original tailgater, Vladimir the latest. |
LOL! That will teach me for not paying attention to the entire thread. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Or at least the first post before replies from "today" . . .  |
|
| Back to top |
|
|
anusha.manthena
New User
Joined: 30 Nov 2006
Posts: 1
Location: Pune/HYd
|
|
|
|
| If you want only the query results from the SPUFI output file, you can sort them accordingly. In your case include a sort condition to retreive only the records which are having 1-9 at the location from where the SIN numbers start. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| I've not read the older stuff in the thread but none of the latest replies actually assist in the original problem - writing SPUFI output to a dataset. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Nic Clouston wrote: |
| I've not read the older stuff in the thread but none of the latest replies actually assist in the original problem - writing SPUFI output to a dataset. |
Ahem. I believe that my suggestion to use DSNTIAUL is what the TS was after: "Spufi" output without headings.  |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Quote: |
| Capture SPUFI result in a dataset |
Is the topic title and, presumably, what the guy was after. But I ain't going to read through these old posts to see if he changed his requirements. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Nic Clouston wrote: |
| Quote: |
| Capture SPUFI result in a dataset |
Is the topic title and, presumably, what the guy was after. But I ain't going to read through these old posts to see if he changed his requirements. |
Remember, though, that to a software engineer "SPUFI" is a magic tool that takes vague requests written in something resembling SQL and miraculously produces results in the exact format wanted. Even analysts at my site, who should know better, refer to DSNTEP* as"batch SPUFI". |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| Akatsukami wrote: |
| Nic Clouston wrote: |
| Quote: |
| Capture SPUFI result in a dataset |
Is the topic title and, presumably, what the guy was after. But I ain't going to read through these old posts to see if he changed his requirements. |
Remember, though, that to a software engineer "SPUFI" is a magic tool that takes vague requests written in something resembling SQL and miraculously produces results in the exact format wanted. Even analysts at my site, who should know better, refer to DSNTEP* as"batch SPUFI". |
And I bet they call DSNTIAUL the "unload utility". That's still the common mis-usage at my site. So much so that when I talk about the Unload utility I have to refer to it as "the Real unload utility". |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Ah well, I am not a software engineer. I am a programmer. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Please notice that you have replied to a question that has been dormant for more than 4 years - Wed Jun 11, 2008 6:45 pm
It is best to reply to currently active posts, though this one is a bit of an exceptjion. There are multiple recent posts, but these are mostly us "old guys" chatting
Enjoy the forum! |
|
| Back to top |
|
|
|
|
