How to run SPUFI in batch
Tarrant, Stephen
Take it or leave it, this is how I worked it out.
You need the following seven PDS members to run SPUFI queries as a batch
job.
1. One job to run the SPUFI query
//jobid JOB (account),'SPUFI',CLASS=class,
// MSGCLASS=X,NOTIFY=&SYSUID
// JCLLIB ORDER=(your.local.proclib)
//RUN EXEC BATSPUF
//SYSUT1 DD *
SELECT * FROM
SYSIBM.SYSVOLUMES
2.
One procedure to be placed in your.local.proclib to setup spufi and run it
//BATSPUF PROC
//SQL EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUT1 DD DUMMY
//SYSUT2 DD DSN=&&SQLIN,RECFM=FB,SPACE=(TRK,(1,1)),LRECL=80,
// DISP=(,PASS)
//SYSIN DD DUMMY
//EXTRACT EXEC ISPFBTCH
//SQLIN DD DSN=&&SQLIN,DISP=(OLD,PASS)
//SQLOUT DD DSN=&&SQLOUT,RECFM=VB,LRECL=4096,DISP=(NEW,PASS),
// DSORG=PS
//ISPLIST DD DSN=&&LIST,RECFM=FB,LRECL=132,DISP=(NEW,PASS)
//TMPNAMES DD DSN=&&TMPN,RECFM=FB,LRECL=80,DISP=(NEW,PASS)
//ISPFPRMS DD DSN=&&TMPI,RECFM=FB,LRECL=80,DISP=(NEW,PASS)
//ISPTABL DD DSN=&&TABLE,RECFM=FB,LRECL=80,DISP=(NEW,PASS),DSORG=PO,
// SPACE=(CYL,(5,5,5))
//SYSTSIN DD DSN=your.local.pds(BATSPUI),DISP=SHR
//ISPFTMP DD DSN=your.local.pds(BATSPUJ),DISP=SHR
//OUTPUT EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSUT1 DD DSN=&&SQLOUT,DISP=SHR
//SYSUT2 DD SYSOUT=*
//SYSIN DD DUMMY
// PEND
3. One procedure to run ISPF in batch
//BATISPF PROC
//RUNISPF EXEC PGM=IKJEFT01
//SYSPROC DD DSN=your.local.pds,DISP=SHR
//ISPPLIB DD DSN=SYS1.ISPF.ISPPLIB,DISP=SHR
// DD DSN=SYS1.DB2.ISPPLIB,DISP=SHR
//ISPTLIB DD DSN=SYS1.ISPF.ISPTLIB,DISP=SHR
//ISPSLIB DD DSN=SYS1.ISPF.ISPSLIB,DISP=SHR
//ISPMLIB DD DSN=SYS1.ISPF.ISPMLIB,DISP=SHR
// DD DSN=SYS1.DB2.ISPMLIB,DISP=SHR
//ISPPROF DD DSN=&&TEMP,RECFM=FB,SPACE=(TRK,(1,1,5)),LRECL=80,
// DISP=(,PASS)
//ISPLOG DD DSN=&&LOG,RECFM=FB,LRECL=80,DISP=(NEW,PASS)
//ISPLIST DD DSN=&&LIST,RECFM=FB,LRECL=132,DISP=(NEW,PASS)
//ISPTABL DD DSN=&&TABLE,RECFM=FB,LRECL=80,DISP=(NEW,PASS),DSORG=PO,
// SPACE=(CYL,(5,5,5))
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSTSIN DD DUMMY
// PEND
4. One input PDS to the procedure, runs ISPF
ISPSTART PANEL(ISR@PRIM) -
OPT(6;MAKEPRM;SETISPRF;END;END)
ISPSTART PANEL(DSNEPRI) -
OPT(1;;END;END;END;END) -
NEWAPPL(DSNE)
5. Another PDS member for the procedure, sets up default ISPF variables
Edit this four your appropriate level of DB2, and max no of lines,
(dsnesv2d).
DSNESV1W "SECOND TIME"
DSNESV1X "310"
DSNESV15 "''"
DSNESV16 "''"
DSNESV1A N
DSNESV17 N
DSNESV18 Y
DSNESV1D Y
DSNESV19 N
DSNEOV01 DB2T
DSNEREL DIDFIRSTRUNAT310LEVEL
ZDSNE1S NO
DSNEAV05 NO
DSNESV2F CS
DSNESV2D 250
DSNESV2C 4092
DSNESV21 4096
DSNESV22 VB
DSNESV2E SYSDA
DSNESV24 33
DSNESV25 80
DSNESV26 NAMES
DSNESV3Z C
DSNEAV03 '0'
DSNEOV02 '0'
6. One REXX procedure
/* REXX */
'PROFILE NOPREFIX'
X=OUTTRAP('DDNS.','*')
'LISTALC SYSNAMES'
X=OUTTRAP('OFF')
DO I=1 TO DDNS.0
IF POS('SQLIN',DDNS.I)>0 THEN DO
NAME1=DDNS.I
END
IF POS('SQLOUT',DDNS.I)>0 THEN DO
NAME2=DDNS.I
END
END
'EXECIO * DISKR TMPNAMES(FINIS)'
'EXECIO * DISKR ISPFTMP(STEM ISPFTMP. FINIS)'
DO I=1 TO ISPFTMP.0
SELECT
WHEN WORD(ISPFTMP.I,1) = 'DSNESV15' THEN DO
PUSH "DSNESV15 '"NAME1"'"
END
WHEN WORD(ISPFTMP.I,1) = 'DSNESV16' THEN DO
PUSH "DSNESV16 '"NAME2"'"
END
OTHERWISE
PUSH ISPFTMP.I
END
'EXECIO 1 DISKW ISPFPRMS'
END
'EXECIO 0 DISKW ISPFPRMS(FINIS)'
7. One more REXX procedure.
/* REXX */
'EXECIO * DISKR ISPFPRMS(STEM PARMS. FINIS)'
'ISPEXEC TBCREATE DSNEPROF WRITE LIBRARY(ISPPROF)'
'ISPEXEC TBTOP DSNEPROF'
DO I = 1 TO PARMS.0
PARSE VAR PARMS.I VARNAM VARVAL
INTERPRET VARNAM'='VARVAL
VARARR.I=VARNAM
END
DUMMY=' '
SETSTR='ISPEXEC TBADD DSNEPROF SAVE('
DO I=1 TO PARMS.0
SETSTR=SETSTR||STRIP(VARARR.I)
IF I<PARMS.0 THEN
SETSTR=SETSTR||','
END
INTERPRET "'"SETSTR"'"
'ISPEXEC TBCLOSE DSNEPROF REPLCOPY LIBRARY(ISPPROF)'
Bolt these all together, they can all exist in the one PDS, and off you go.
Simply run the first job with your query as JCL input.
D Preble
Why go thru such trouble ? Why not run dsntiad or dsntep2 depending on
you needs ?
Kenneth Kornblum
>>Subject: Re: How to run SPUFI in batch
>>Author: D Preble <dpr...@WWNET.COM> at UNIXLINK
>>Date: 7/21/96 11:21 PM
>>Why go thru such trouble ? Why not run dsntiad or dsntep2 depending
>>on you needs ?
-----------------
They want to be able to run SELECTs in batch which disqualifies DSNTIAD,
and they may not have a PL/I compiler (or maybe not even the
transients). Thus, trying to run SPUFI in batch is a way to go.
James KWAN
>Why go thru such trouble ? Why not run dsntiad or dsntep2 depending on
>you needs ?
>
>
In spufi you can specify max number of rows to be return. This is good if
you are accessing a large table but you only need to have few rows.
Regards,
--------------------------------------------------------------------------------
James KWAN
DBA
EnergyAustralia Tel: 61-49-519316
--------------------------------------------------------------------------------
George White/VGI
To: DB2-L @ AMERICAN.EDU (Multiple recipients of list DB2-L) @ INET
cc: (bcc: George White/VGI)
From: vilagan @ OZNET02.OZEMAIL.COM.AU ("Ven C. Ilagan") @ INET
Date: 07/23/96 07:46:15 PM
Subject: Re: How to run SPUFI in batch
Does anybody know if an Assembler, C or COBOL version will ever be available
for batch SQL execution?
...............................................................................
...............................................................................
....................................
Dan,
Dsntiad is different in that it cannot support 'SELECT ...'
Dsntep2 on the other hand uses PL/1 and a lot of the shops are
getting rid of their PL/1 compilers.
That's why SPUFI in batch is a handy replacement to DSNTEP2.
Regards,
Ven
> From: D Preble <dpr...@WWNET.COM>
> Subject: Re: How to run SPUFI in batch
> Why go thru such trouble ? Why not run dsntiad or dsntep2 depending on
> you needs ?
>
>
V.C.Ilagan