IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Output of a query run through JCL into a dataset


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 115
Location: Hyderabad

PostPosted: Fri Nov 20, 2009 1:14 pm
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: 1020
Location: India

PostPosted: Fri Nov 20, 2009 4:18 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Nov 20, 2009 4:31 pm
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

Superior Member


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

PostPosted: Fri Nov 20, 2009 5:37 pm
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: 1020
Location: India

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

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

Superior Member


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

PostPosted: Fri Nov 20, 2009 7:53 pm
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: 115
Location: Hyderabad

PostPosted: Wed Nov 25, 2009 1:26 pm
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: 1020
Location: India

PostPosted: Wed Nov 25, 2009 1:45 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts RC query -Time column CA Products 3
No new posts Allocated cylinders of a dataset DB2 12
Search our Forums:

Back to Top