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
 

 

DB2 Batch Spufi - Formatted report with record length > 1

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Manju-Pacha

New User


Joined: 21 Mar 2007
Posts: 7
Location: Bangalore

PostPosted: Wed Mar 11, 2009 4:24 pm    Post subject: DB2 Batch Spufi - Formatted report with record length > 1
Reply with quote

Hi,

Does anyone have an example of running spufi in batch?
I have to run select query in a JCL and would like the output to be in spufi format (the length of the row is greater that 133 cols).

I tried running DSNTEP2 for batch, but the report is limited to 133 columns per row.

Thanks
Manju
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Mar 12, 2009 2:39 pm    Post subject:
Reply with quote

Manju & Experts,

I came across this SPUFI in BATCH, when i googled for the same. I don't know how to put it together & run it. If you do, find a way, let me know..........

Code:
SPUFI in BATCH
----------------

It's possible to run the real spufi using batch ispf.  I'm weak on
ispf panels, so have to make this work by trial and error
whenever db2 changes, but the following jcl does run spufi for
me.  Your dsns would all be different, but essentially you are cloneing your
tso logon proc statements.  I normally have to make changes in the vput
area:
//*   EXEC SPUFI IN BATCH
//*                                                                  *
//DEL01  EXEC  PGM=IEFBR14
//*------------------------------------------------------------------*
//*        DELETE TEMPORARY SPUFI INPUT AND OUTPUT DATASETS          *
//*------------------------------------------------------------------*
//DD1      DD  DSN=SYSRBA.BATCH.SPUFI.INPUT,
//             UNIT=SYSWRK,SPACE=(TRK,0),DISP=(MOD,DELETE)
//DD2      DD  DSN=SYSRBA.BATCH.SPUFI.OUTPUT,
//             UNIT=SYSWRK,SPACE=(TRK,0),DISP=(MOD,DELETE)
//*
//GENER02 EXEC PGM=IEBGENER
//*------------------------------------------------------------------*
//*            CREATE SPUFI OUTPUT DATASET                           *
//*------------------------------------------------------------------*
//SYSUT1   DD  DUMMY,DCB=(LRECL=400,BLKSIZE=4096,RECFM=VB,DSORG=PS)
//SYSUT2   DD  DSN=SYSRBA.BATCH.SPUFI.OUTPUT,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSWRK,SPACE=(CYL,(1,1))
//SYSIN    DD  DUMMY
//SYSPRINT DD  DUMMY
//*
//GENER03 EXEC PGM=IEBGENER
//*------------------------------------------------------------------*
//*        CREATE SPUFI INPUT DATASET  -  PUT SQL STATEMENTS HERE    *
//*------------------------------------------------------------------*
//SYSUT1   DD  *
 --                                                --
 -- SHOW DATE TIME
 --                                                --
 SELECT DISTINCT CURRENT DATE, CURRENT TIME
          FROM SYSIBM.SYSDATABASE;
 --                                                --

//SYSUT2   DD  DSN=SYSRBA.BATCH.SPUFI.INPUT,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSWRK,SPACE=(CYL,(1,1)),
//             DCB=(LRECL=80,BLKSIZE=6240,RECFM=FB,DSORG=PS)
//SYSIN    DD  DUMMY
//SYSPRINT DD  DUMMY
//*
//CLST04  EXEC PGM=IEBGENER,COND=(0,NE)
//*------------------------------------------------------------------*
//*  PUT ISPF CLIST STATEMENTS IN A WORK PDS - PUT SPUFI PARMS HERE  *
//*------------------------------------------------------------------*
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  DUMMY
//SYSUT2   DD  DSN=&&TEMP(RBAISPF),
//             DISP=(NEW,PASS,DELETE),
//             UNIT=SYSWRK,
//             SPACE=(TRK,(5,0,5)),
//             DCB=(LRECL=255,RECFM=VB,BLKSIZE=3120,DSORG=PO)
//SYSUT1   DD  *
  /* CONTROL NOFLUSH */
     CONTROL NOFLUSH SYMLIST
  SET DSNESV15 = 'SYSRBA.BATCH.SPUFI.INPUT'    /* SPUFI INPUT     */
  SET DSNESV16 = 'SYSRBA.BATCH.SPUFI.OUTPUT'   /* SPUFI OUTPUT    */
  SET DSNESV1A = NO                         /* CHANGE DEFAULTS    */
  SET DSNESV17 = NO                         /* EDIT INPUT         */
  SET DSNESV18 = YES                        /* EXECUTE            */
  SET DSNESV1D = YES                        /* AUTOCOMMIT         */
  SET DSNESV19 = NO                         /* BROWSE OUTPUT      */
  SET DSNEOV01 = DSN7                       /* DB2 SUBSYSTEM      */
  SET DSNEOV04 = 50                         /* LINES/PAGE (5-999) */
  SET DSNESV2C = 400                        /* LRECL              */
  SET DSNESV2D = 2000                       /* MAX LINES SELECTED */
  SET VPUT1 = &STR(DSNESV15 DSNESV16 DSNESV1A DSNESV17 DSNESV18)
  SET VPUT2 = &STR(DSNESV1D DSNESV19 DSNEOV01 DSNEOV04)
  SET VPUT3 = &STR(DSNESV2C DSNESV2D)
  ISPEXEC VPUT (&VPUT1 &VPUT2 &VPUT3) PROFILE
  ISPEXEC SELECT PANEL(DSNEPRI) OPT(1)
  /*    */
  EXIT
//*
//ISPF05  EXEC PGM=IKJEFT01,DYNAMNBR=25,COND=(0,NE)
//*------------------------------------------------------------------*
//*           EXEC SPUFI IN ISPF BATCH                               *
//*------------------------------------------------------------------*
//STEPLIB  DD DSN=DB2.TEST.DSNEXIT,DISP=SHR
//         DD DSN=DB2.TEST.DSNLOAD,DISP=SHR
//ISPPROF  DD DSN=SYSRBA.BATCH.ISPPROF,DISP=SHR
//ISPLOG   DD DSN=SYSRBA.BATCH.ISPLOG,DISP=SHR
//ISPLLIB  DD DSN=DB2.TEST.RUNLIB.LOAD,DISP=SHR
//ISPPLIB  DD DSN=ISP.SISPPENU,DISP=SHR
//         DD DSN=DB2.TEST.DSNPFPE,DISP=SHR
//         DD DSN=DB2.TEST.DSNSPFP,DISP=SHR
//ISPSLIB  DD DSN=ISP.SISPSLIB,DISP=SHR
//ISPMLIB  DD DSN=ISP.SISPMENU,DISP=SHR
//         DD DSN=DB2.TEST.DSNSPFM,DISP=SHR
//ISPTLIB  DD DSN=ISP.SISPTENU,DISP=SHR
//SYSEXEC  DD  DSN=SYS2.REXX.EXEC,DISP=SHR
//         DD  DSN=SYS2.ISPF.USERS.EXEC,DISP=SHR
//         DD  DSN=RDC.ISPF.REXX.EXEC,DISP=SHR
//SYSPROC  DD  DSN=&&TEMP,DISP=SHR
//         DD  DSN=SYS2.ISR.ISRCLIB,DISP=SHR
//         DD  DSN=SYS2.CMDPROC,DISP=SHR
//         DD  DSN=SYS2.ISPF.USERS.EXEC,DISP=SHR
//         DD  DSN=RDC.ISPF.CLIST,DISP=SHR
//         DD  DSN=DB2.TEST.DSNCLIST,DISP=SHR
//SYSHELP  DD DSN=SYS1.HELP,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSLBC   DD DSN=SYS1.BRODCAST,DISP=SHR
//SYSUADS  DD DSN=SYS1.UADS,DISP=SHR
//SYSTSIN  DD  *
  PROFILE NOPREFIX
  ISPSTART CMD(%RBAISPF) NEWAPPL(DSNE) TRACEX BDISPMAX(2)
/*
//* ------------------------------------------------------------- *
//*     BDISPMAX(2) MEANS THAT ISPF BATCH WILL STOP RUNNING IF    *
//* MORE THAN 2 PANELS ARE DISPLAYED.  DO NOT CHANGE THIS NUMBER. *
//* ------------------------------------------------------------- *
//*
//*
//*
//STEP01  EXEC PGM=IEBGENER
//SYSUT1   DD  DISP=SHR,DSN=SYSRBA.BATCH.SPUFI.OUTPUT
//SYSUT2   DD  SYSOUT=*,DCB=(LRECL=133,RECFM=FB,BLKSIZE=6251)
//SYSPRINT DD  DUMMY
//SYSIN    DD  *
  GENERATE MAXFLDS=10
  RECORD   FIELD=(133)
//*
------------------------------------------------------------------------------------------------
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.


----------------------------------------------------------------------------------------------------------------


Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
kamlendrapandey

New User


Joined: 04 Jan 2009
Posts: 1
Location: Pune

PostPosted: Thu Mar 12, 2009 3:05 pm    Post subject: Reply to: DB2 Batch Spufi - Formatted report with record len
Reply with quote

Manju,

In Output dataset characterstics u can choose record length of output file.

Thanks,
Kamal
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm


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