Doubts in record length calculation of SYSRECXX(DSNTIAUL)



IBM's flagship relational database management system

Doubts in record length calculation of SYSRECXX(DSNTIAUL)

Postby magesh123 » Mon May 09, 2011 9:56 am

My DSNTIAUL utility program look likes the below,

//SYSREC00 DD DSN=SYSREC DATA SET NAME,
// DISP=(,CATLG),
// UNIT=SYSDA,SPACE=(CYL(100,200),RLSE)
//SYSPUNCH DD DUMMY
//SYSIN DD DSN=CONTROL CARD DATASET

My control card contains the below SQL.

SELECT
FIELD1
,FIELD2
,FIELD3
,FIELD4
,FIELD5
,SUBSTR('10.01.2009',1,10) AS DATE_INF (COLUMN NOT FOUND IN THE TABLE JUST PLACE THE VALUE IN THE SYSREC)
,SUM(FIELD6)
,SUM(FIELD7)
,SUM(FIELD8)
,SUM(FIELD9)

FROM TABLE_NAME
WHERE FIELD_1 IN ( 'A001','B001','C001')
GROUP BY
FIELD1
FIELD2
FIELD3

Question:
When i submit above program, I am getting the SYSREC00 dataset with record length as 56 whereas when i try with other environment i am getting the record length as 66. But here we didnt specify the LRECL(Logical record length) Of the SYSREC dataset.

It calls one assembler program to unload the table using DSNTIAUL utility. I am not sure wheter it calculates the record length for all the fields specified in the select statement.

If it calculates from the select statement(from above control card) then will it eliminates DATE_INF field as it is not the column of a table?

One more doubt.. Can anyone tell me whether it calculates some system parameter like DEC31 OR DEC15 for decimal fields. But the record structure of the below table is same in both environment. but the system parameter only varies. In one env we are using DEC31 Whereas in other environment we are using DEC15.

************************************************************
Field_name Length
~~~~~~~~~~ ~~~~~~~
FIELD1 4
FIELD2 4
FIELD3 10
FIELD4 1
FIELD5 3

FIELD6 4
FIELD7 15 (decimal field)
FIELD8 4
FIELD9 15 (decimal field)
~~~~~~~~~~~~~~~~~~~~
Total length : XX

Can any one please tell me whether the record length varies due to the SUM() in the select field or system parameter(DECARTH DEC31 OR DEC15) ?
Also if possible, please explain how it calculates internally..

Thanks in Advance,
Magesh
magesh123
 
Posts: 13
Joined: Fri Apr 15, 2011 11:29 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Doubts in record length calculation of SYSRECXX(DSNTIAUL

Postby GuyC » Mon May 09, 2011 5:55 pm

if you set your syspunch not to DUMMY, you probably are able to figure out which fields are on which position/length.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Doubts in record length calculation of SYSRECXX(DSNTIAUL

Postby magesh123 » Tue May 17, 2011 4:32 pm

Thank you. I will resubmit the JOB with SYSPUNCH.
magesh123
 
Posts: 13
Joined: Fri Apr 15, 2011 11:29 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post