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
 

 

Output of a query run through JCL into a dataset

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

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Fri Nov 20, 2009 1:14 pm    Post subject: Output of a query run through JCL into a dataset
Reply with quote

Hi,

I have the following Job

Code:

//EXECSQL1  EXEC PGM=IKJEFT01
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
DSN SYSTEM(DBCD)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) PARMS('ALIGN(LHS)')
END
//SYSIN    DD  *
SELECT a.* FROM PLAN_TABLE;
/*



I want to catch the results in a dataset. I tried giving dataset name in SYSPRINT. It has many columns and showing columns only till record length 133 and continuing in the next page..
Code:

//SYSPRINT DD  DSN=....


I tried to increase the LRECL or SYSPRINT dataset, but no use. Can someone please suggest me how to capture the output of a query which has many columns in a proper way( formatted way rather than having few columns in one page and other columns in another page etc) i.e. all the columns in one page.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 20, 2009 4:18 pm    Post subject:
Reply with quote

KranthiKumarb,

You want output like in SPUFI using a DSNTEP2. That can't be done.

May be you have to write a program for that.
or
GooGle for making SPUFI run in batch. One time i came across a job like that.

Sushanth
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Fri Nov 20, 2009 4:31 pm    Post subject: Reply to: Output of a query run through JCL into a dataset
Reply with quote

since the dsntep2 source is available, You can modify it according to Your needs


Code:

  *          The following variables can be changed to fit the       *  02980000
  *          specific environment of the user.                       *  02990000
  *                                                                  *  03000000
  *           Variable   Value   Meaning                             *  03010000
  *           Name                                                   *  03020000
  *           --------   -----   --------------------------          *  03030000
  *                                                                  *  03040000
  *           PAGEWIDTH   133    Maximum width of a page in          *  03050000
  *                              characters (including the control   *  03060000
  *                              character in column one)            *  03070000
  *                                                                  *  03080000
  *           MAXROW#LN     6    Maximum number of digits for the    *  03090000
  *                              row numbers in the output.          *  03100000
  *                                                                  *  03110000
  *           MAXPAGWD    125    Print line width controller =       *  03120000
  *                              maximum width - 1 (for control      *  03130000
  *                              character) - MAXROW#LN (length of   *  03140000
  *                              the column display) - 1 ( a '-'     *  03150000
  *                              between the column number display   *  03160000
  *                              the SQL output display).            *  03170000
  *                                                                  *  03180000
  *           MAXCOLWD    120    Maximum number of characters in a   *  03190000
  *                              character data type column.         *  03200000
  *                              Truncation occurs when this number  *  03210000
  *                              is exceeded.                        *  03220000
  *                                                                  *  03230000
  *           MAXPAGLN     60    Maximum number of lines on  the     *  03240000
  *                              print output pages 2 thrn N.  Page  *  03250000
  *                              1  will have MAXPAGLN + 1 lines.    *  03260000
  *                                                                  *  03270000
  *           PAGESIZE   4096    Size of a page.  All storage        *  03280000
  *                              allocation of the SQL buffer area   *  03290000
  *                              will be a multiple of this value.   *  03300000
  *                                                                  *  03310000
  *           MAXNCOLS    100    Initial maximum number of col-   @34*  03320000
  *                              umns in an answer, times 2 in    @34*  03330000
  *                              case a double SQLDA is required  @34*  03340000
  *                              for LOBs and/or UDTs. An initial @34*  03350000
  *                              setting of 100 will handle an    @34*  03360000
  *                              SQL statement of at least 50     @34*  03370000
  *                              columns.                         @34*  03380000
  *                              If an SQL statement described       *  03390000
  *                              into a single SQLDA has more     @34*  03400000
  *                              than 100 columns -or- an SQLDA   @34*  03410000
  *                              described into a double SQLDA    @34*  03420000
  *                              has more than 50 columns, a larger  *  03430000
  *                              SQLDA area will be allocated        *  03440000
  *                                                                  *  03450000
  *           MAXBUFFER 2097152  Maximum length of any large array   *  03465990
  *           STMTMAX            size (OUTBUF, BUFFSQL, COLSTART,    *  03471980
  *                              COLLN and STMTBUF)                  *  03480000
  *                              (increased from 32760 to 2M)        *  03490000
  *                                                                  *  03500000
  *           RECLEN       72    Length of the input record          *  03514990
  *                                                                  *  03520000
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Nov 20, 2009 5:37 pm    Post subject:
Reply with quote

Do yo have File-Aid?

1. If you can use DSNTIAUL, download (unload?) table to desired DSN.
2. Use the "PRINT" option of File-Aid to save the file in "formatted-layout."
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 20, 2009 7:29 pm    Post subject:
Reply with quote

Thank You Very Much Anuj.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Nov 20, 2009 7:53 pm    Post subject:
Reply with quote

You're welcome Sushanth... icon_smile.gif. Hope process can work for OP as well.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Wed Nov 25, 2009 1:26 pm    Post subject:
Reply with quote

Thanks much Anuj, worked perfectly for me.

Here is the JCL. Posting it for others

Code:

//DATARPT1  EXEC PGM=IKJEFT01                                   
//SYSTSIN   DD *                                                 
DSN SYSTEM(DBCD)                                                 
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL,100')           
END                                                             
/*                                                               
//SYSPUNCH DD SYSOUT=*                                           
//SYSTSPRT  DD SYSOUT=*                                         
//SYSPRINT  DD SYSOUT=*                                         
//SYSREC00 DD DSN=ABCD.ACSPATH.REPORT1,DISP=SHR       
//SYSIN     DD DSN=ABCD.ACSPATH.CTRL(REPORT1),DISP=SHR


All i want to know is what is the parameter that we are coing in PARMS ( after SQL). I've gone through manuals and came to know that the number indicates

Quote:

number of rows per fetch
|Specify a number from 1 to 32767 to specify the number of rows |per fetch that DSNTIAUL retrieves. If you do not specify this number, |DSNTIAUL retrieves 100 rows per fetch. This parameter can be specified |with the SQL parameter. |

#Specify 1 to retrieve data from a remote site when DSNTIAUL #is bound with the DBPROTOCOL(PRIVATE) option.


But i dont see any difference even if i give 100 or 200. it is retieving 10000 rows in same time under same contitions. Can some one throw light on this parameter?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Nov 25, 2009 1:45 pm    Post subject:
Reply with quote

Kranthikumarb,

That parameter doesn't limit, number of rows to be unloaded. That just specifies number of rows to retreive from the table for one fetch call, that is issued by DSNTIAUL.

To Limit number of rows to be unloaded, in the SQL statement specify
Code:
FETCH FIRST 100 rows only;

to fetch 100 rows

Thank You,
Sushanth
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 Browse dataset cvnlynn CLIST & REXX 4 Tue Jul 18, 2017 3:52 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts JES2 doesn't honor the priority of th... Aron Lendvai JCL & VSAM 7 Thu Jun 29, 2017 1:41 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm


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