Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Batch SPUFI 2
- Thread starter wing2x
- Start date
gregsimpson
Programmer
- Apr 18, 2002
- 521
wing2x,
//SPUFI EXEC PGM=IKJEFT01,REGION=2M,DYNAMNBR=100
//STEPLIB DD DISP=SHR,DSN=DBRES.DSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD*
DSN SYSTEM(DB21)
RUN PROGRAM(DSNTEP2) PLAN(DSNSPUFI)
END
//SYSIN DD *
UPDATE SYSY001.TABLE1
SET .......
//
Cheers
Greg
//SPUFI EXEC PGM=IKJEFT01,REGION=2M,DYNAMNBR=100
//STEPLIB DD DISP=SHR,DSN=DBRES.DSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD*
DSN SYSTEM(DB21)
RUN PROGRAM(DSNTEP2) PLAN(DSNSPUFI)
END
//SYSIN DD *
UPDATE SYSY001.TABLE1
SET .......
//
Cheers
Greg
- Thread starter
- #3
- Thread starter
- #4
I need a batch utility that I could use to extract data from tables. I'm looking at something similar to what the ISPF version of SPUFI generates. The DSNTEP2 utility outputs the data as a report that should fit on 133 character wide. If the table has many columns it wraps the output, which is not what I want. I've found this: but it is still limited by the 133-wide DSNTEP2 output.
Any suggestions?
Any suggestions?
-
2
- #5
- Thread starter
- #6
Marc,
I want to be able to just modify a table name inside a JCL card, and when job runs it will output an extract of that table with first row displaying column names and a footer displaying the total number of rows retrieved.
I was able to achieve this using the DSNTEP2 and REXX combination, but limited only to tables that does not have column names greater than 125 or so.
Will it be possible to run QMF batch without creating QMF queries, forms and procs? What will the format of the output be?
We have 200+ tables to extract and it is moderately important that we could just change the table name and the job takes care of most things.
Thanks
wing
I want to be able to just modify a table name inside a JCL card, and when job runs it will output an extract of that table with first row displaying column names and a footer displaying the total number of rows retrieved.
I was able to achieve this using the DSNTEP2 and REXX combination, but limited only to tables that does not have column names greater than 125 or so.
Will it be possible to run QMF batch without creating QMF queries, forms and procs? What will the format of the output be?
We have 200+ tables to extract and it is moderately important that we could just change the table name and the job takes care of most things.
Thanks
wing
Wing,
I'm a bit slow this morning so let me see if I've understood you correctly. What you want is a piece of JCL that you can feed the table name in, which then produces a report listing every field on the table, followed by a count of the number of rows on that table. Have I got that correct?
Marc
I'm a bit slow this morning so let me see if I've understood you correctly. What you want is a piece of JCL that you can feed the table name in, which then produces a report listing every field on the table, followed by a count of the number of rows on that table. Have I got that correct?
Marc
- Thread starter
- #8
Marc,
Not exactly. It should output a data extract, 1 line per table row. In addition, it should also display the column names in the 1st line; and another footer line to display the number of rows in the table.
Suppose a table has 500 rows, the output should have 502 lines: 1 header line + 500 data rows + 1 footer line.
Thanks
wing
Not exactly. It should output a data extract, 1 line per table row. In addition, it should also display the column names in the 1st line; and another footer line to display the number of rows in the table.
Suppose a table has 500 rows, the output should have 502 lines: 1 header line + 500 data rows + 1 footer line.
Thanks
wing
Wing,
You could use store the query in a dataset and then run QMF in batch with an initial procedure. That procedure would import the dataset as a query into QMF, which you could then run. You would probably need two queries, one to select the data and one to generate the count. QMF can do this, but needs forms to do so, and in the scenario that you have described, this does not seem possible.
DSNTEP2 appears to only output to 133, and according to the manual there is no way to override this. Have you run the REXX exec, as detailed on the Mainframe weekly site, as it looks to me that it is doing exactly what you are asking i.e. taking the DSNTEP2 output of 133 and reformatting it to a file of 4096 lrecl.
Marc
You could use store the query in a dataset and then run QMF in batch with an initial procedure. That procedure would import the dataset as a query into QMF, which you could then run. You would probably need two queries, one to select the data and one to generate the count. QMF can do this, but needs forms to do so, and in the scenario that you have described, this does not seem possible.
DSNTEP2 appears to only output to 133, and according to the manual there is no way to override this. Have you run the REXX exec, as detailed on the Mainframe weekly site, as it looks to me that it is doing exactly what you are asking i.e. taking the DSNTEP2 output of 133 and reformatting it to a file of 4096 lrecl.
Marc
- Thread starter
- #10
Marc,
Yes, I have successfully utilized the REXX exec from mainframe weekly. However, since DSNTEP2 is limited to 133 chars, the consequence is that I could not have individual fields with length greater than that limit on my target tables.
I could probably try the QMF approach and have REXX reformat some of the output. Can you point me to an article or tutorial on how to run QMF in batch with an initial procedure?
Thanks
wing
Yes, I have successfully utilized the REXX exec from mainframe weekly. However, since DSNTEP2 is limited to 133 chars, the consequence is that I could not have individual fields with length greater than that limit on my target tables.
I could probably try the QMF approach and have REXX reformat some of the output. Can you point me to an article or tutorial on how to run QMF in batch with an initial procedure?
Thanks
wing
Wing,
You need to set up a piece of JCL that executes TSO(IKJEFT01) with the relevant QMF libraries and PDF libraries concatenated. The input in the SYSTSIN should be something like:
PROFILE PREFIX(sqlid)
ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM M=B,I=user.procname,S=ssid)
The M=B refers to it being batch, and the I= parm is the initial input proc.
Articles I've found which might help:
Hope this helps.
Marc
You need to set up a piece of JCL that executes TSO(IKJEFT01) with the relevant QMF libraries and PDF libraries concatenated. The input in the SYSTSIN should be something like:
PROFILE PREFIX(sqlid)
ISPSTART PGM(DSQQMFE) NEWAPPL(DSQE) PARM M=B,I=user.procname,S=ssid)
The M=B refers to it being batch, and the I= parm is the initial input proc.
Articles I've found which might help:
Hope this helps.
Marc
- Thread starter
- #12
Wing,
You will have to create and save a proc in QMF that imports the query from a specific dataset and then runs it. I think that the default printer length is 133 so you will have to over write this to the max size of your ddname DSQPRINT dataset. I haven't got QMF at the site I am at, so I'm running from memory, but I think you will need to set up a proc something along the lines of:
IMPORT QUERY FROM 'WING.QMF.INPUT(SQLDATA)'
RUN QUERY
PRINT REPORT (PRINTER='' W=4096 L=CONT)
IMPORT QUERY FROM 'WING.QMF.INPUT(COUNT)'
RUN QUERY
PRINT REPORT (PRINTER='' W=4096 L=CONT)
The first import should be your data fetch, and the second is the count. You'll need to guess the maximum width of your output dataset ever and over write my 4096. The L=CONT bit stops QMF from adding headings at the top of each page, so that you get one heading followed by x lines of data.
Come back to me if this doesn't work, and I'll give it another shot. Roll on Monday when I'll be at a different site which hopefully has QMF!!!
You will have to create and save a proc in QMF that imports the query from a specific dataset and then runs it. I think that the default printer length is 133 so you will have to over write this to the max size of your ddname DSQPRINT dataset. I haven't got QMF at the site I am at, so I'm running from memory, but I think you will need to set up a proc something along the lines of:
IMPORT QUERY FROM 'WING.QMF.INPUT(SQLDATA)'
RUN QUERY
PRINT REPORT (PRINTER='' W=4096 L=CONT)
IMPORT QUERY FROM 'WING.QMF.INPUT(COUNT)'
RUN QUERY
PRINT REPORT (PRINTER='' W=4096 L=CONT)
The first import should be your data fetch, and the second is the count. You'll need to guess the maximum width of your output dataset ever and over write my 4096. The L=CONT bit stops QMF from adding headings at the top of each page, so that you get one heading followed by x lines of data.
Come back to me if this doesn't work, and I'll give it another shot. Roll on Monday when I'll be at a different site which hopefully has QMF!!!
- Status
Similar threads