View previous topic :: View next topic
|
Author |
Message |
kranthikumarb
Active User
Joined: 02 Jan 2009 Posts: 115 Location: Hyderabad
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Thank You Very Much Anuj. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
You're welcome Sushanth... . Hope process can work for OP as well. |
|
Back to top |
|
|
kranthikumarb
Active User
Joined: 02 Jan 2009 Posts: 115 Location: Hyderabad
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
|