Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Sun Jan 28, 2007 6:05 am    Post subject: Summing and altering a field value based on the sum I have read this forum and seen several instances that come close to solving the problem that I have. My task is to sum the number of records that contain a matching value in column 9,15(in this example the field contains 00123), there are four records that contain that value, after summing the number of records that contain this value, altering the field value that in this example is in column 35,40. Input GANQ0014300123XXXXXXXXXXXXXXXXXXXXXXXX00001 GUNR0061800123XXXXXXXXXXXXXXXXXXXXXXXX00000 HBOO0001500123XXXXXXXXXXXXXXXXXXXXXXXX00001 IATZ93432400123XXXXXXXXXXXXXXXXXXXXXXXX00000 Desired Output GANQ0014300123XXXXXXXXXXXXXXXXXXXXXXXX00004 GANR0061800123XXXXXXXXXXXXXXXXXXXXXXXX00004 HBOO0001500123XXXXXXXXXXXXXXXXXXXXXXXX00004 IATZ93432400123XXXXXXXXXXXXXXXXXXXXXXXX00004 Thank you in advance Bill Ross

Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

 Posted: Sun Jan 28, 2007 10:01 pm    Post subject: Your example shows all of the records with the same value in 9,15. Is that actually the case, or do you have records with different values in 9,15? If so, please show a more complete example of your input records and expected output records. Also, what is the RECFM and LRECL of your input file?
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Mon Jan 29, 2007 6:58 am    Post subject: Re: Summing and altering a field value based on the sum The values of the fields in 9,15 change as this new example shows. Also the counter field is further out in the record, it acutally begins in column 191. The LRECL is 215 RECFM is FB. Input Example 1 GANQ0014300123XXXXXXXXXXXXXXXXXXXXXXXX00001 GUNR0061800123XXXXXXXXXXXXXXXXXXXXXXXX00000 HBOO0001500123XXXXXXXXXXXXXXXXXXXXXXXX00001 IATZ93432400123XXXXXXXXXXXXXXXXXXXXXXXX00000 Example 2 GAPP0014366423XXXXXXXXXXXXXXXXXXXXXXXX00001 GUTM0061866423XXXXXXXXXXXXXXXXXXXXXXXX00000 HBTZ0001566423XXXXXXXXXXXXXXXXXXXXXXXX00001 Example 3 GAPP00143050532XXXXXXXXXXXXXXXXXXXXXXXX00001 GUTM0061850532XXXXXXXXXXXXXXXXXXXXXXXX00000 Example 4 GANQ001471632XXXXXXXXXXXXXXXXXXXXXXXX00001 GUNR006171632XXXXXXXXXXXXXXXXXXXXXXXX00000 HBOO000171632XXXXXXXXXXXXXXXXXXXXXXXX00001 IATZ934371632XXXXXXXXXXXXXXXXXXXXXXXX00000 BBYZ384771632XXXXXXXXXXXXXXXXXXXXXXXX00000 MPOX343371632XXXXXXXXXXXXXXXXXXXXXXXX00003 Desired Output Example 1 GANQ0014300123XXXXXXXXXXXXXXXXXXXXXXXX00004 GANR0061800123XXXXXXXXXXXXXXXXXXXXXXXX00004 HBOO0001500123XXXXXXXXXXXXXXXXXXXXXXXX00004 IATZ93432400123XXXXXXXXXXXXXXXXXXXXXXXX00004 Example 2 GAPP0014366423XXXXXXXXXXXXXXXXXXXXXXXX00003 GUTM0061866423XXXXXXXXXXXXXXXXXXXXXXXX00000 HBTZ0001566423XXXXXXXXXXXXXXXXXXXXXXXX00003 Example 3 GAPP00143050532XXXXXXXXXXXXXXXXXXXXXXXX00002 GUTM0061850532XXXXXXXXXXXXXXXXXXXXXXXX00002 Example 4 GANQ001471632XXXXXXXXXXXXXXXXXXXXXXXX00006 GUNR006171632XXXXXXXXXXXXXXXXXXXXXXXX00006 HBOO000171632XXXXXXXXXXXXXXXXXXXXXXXX00006 IATZ934371632XXXXXXXXXXXXXXXXXXXXXXXXX00006 BBYZ384771632XXXXXXXXXXXXXXXXXXXXXXXXX00006 MPOX343371632XXXXXXXXXXXXXXXXXXXXXXXX00006 I hope this explains the problem better. Thank you in advance Bill Ross
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

 Posted: Mon Jan 29, 2007 9:50 pm    Post subject: Actually, I'm more confused now. You say the value is in 9,15 but I see 300123X in 9-15 for the first record and 800123X for the second record. None of these values matches. So where is your matching field, really - 10-14 or somewhere else? For example 2, you show 3 for the count in the first and third records, but 0 for the count in the second record - why? Also, are these four examples of four different input files? My question was really whether you could have different values in the same input file and I still don't know the answer. Does a given input file only have one value in all of the records (e.g. all of the records have 000123 or all of the records have 00125), or could it have more than one value (e.g. some records have 00123 and other records have 00125)?
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Tue Jan 30, 2007 1:03 am    Post subject: Re: Summing and altering a field value based on the sum I apologize the value begins in Column 9 and ends in Column 14. I mistakenly said the first post the field ended in Column 14. Yes the value in that field changes. The first and second examples are incorrect. The attachment contains a screen print of the file w/columns.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Tue Jan 30, 2007 2:05 am    Post subject:

Ah, now it's clear. Here's a DFSORT/ICETOOL job that will do what you asked for:

 Code: //S1 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN DD DSN=...  input file (FB/215) //T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS) //CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS) //    DD DSN=*.IN,VOL=REF=*.IN,DISP=(OLD,PASS) //OUT DD DSN=...  output file (FB/215) //TOOLIN DD * SORT FROM(IN) TO(T1) USING(CTL1) SPLICE FROM(CON) TO(OUT) ON(9,6,CH) -   WITHALL WITH(1,30) WITH(41,175) /* //CTL1CNTL DD *   SORT FIELDS=(9,6,CH,A)   OUTFIL FNAMES=T1,REMOVECC,NODETAIL,    SECTIONS=(9,6,      TRAILER3=(9:9,6,31:COUNT=(M11,LENGTH=10))) /*
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Tue Jan 30, 2007 3:32 am    Post subject: Re: Summing and altering a field value based on the sum Thank you I will try it.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Tue Jan 30, 2007 4:26 am    Post subject: Re: Summing and altering a field value based on the sum Did I not copy all of your code? I have attached the output from the execution.
William Thompson

Global Moderator

Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

 Posted: Tue Jan 30, 2007 4:47 am    Post subject: Has your system people kept up on the PTFs required? I would guess that either you didn't copy the code correctly or that your people are not up to the current level.... Frank, where does the PTF level show up?
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

 Posted: Tue Jan 30, 2007 5:24 am    Post subject: Bill, REMOVECC was flagged as not recognized. REMOVECC has been available with DFSORT since March, 2002. That means your site is really way behind in applying DFSORT service. Ask your System Programmer to install DFSORT R14 PTF UK90006 (April, 2006) - it's free and it will get you all of the current DFSORT function. For a timeline of which functions were added to DFSORT when, see: http://www.ibm.com/servers/storage/support/software/sort/mvs/summary_changes/srtmsocc.html
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Tue Jan 30, 2007 7:14 am    Post subject: Re: Summing and altering a field value based on the sum Thank you Sir, I will see the Systems person tomorrow.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Wed Jan 31, 2007 7:46 am    Post subject: I spoke to the Systems person late this afternoon. He said he would look into the upgrade.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Fri Feb 09, 2007 3:57 am    Post subject: New mainframe It?s a long story so I will give an abbreviated version. The shop I work at is in the process of converting to a new mainframe. Just today I was able to get logged on to the new mainframe, and got the file I needed as input copied over to the new mainframe. So I was able to try the solution you gave me. It didn?t sum the records and put the total in the correct field. The field that should contain the total begins in column 191 for a length of six.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Fri Feb 09, 2007 4:35 am    Post subject:

 Quote: So I was able to try the solution you gave me. It didn?t sum the records and put the total in the correct field. The field that should contain the total begins in column 191 for a length of six.

It sounds like you think this is my fault. Maybe you didn't mean it that way?

I based my example on the information you gave me previously. If the positions of the fields you told me were wrong, then obviously the job wouldn't work as is! GIGO.

You can adjust the job I gave you to use the correct positions and lengths for the fields.

If you need more specific help, then start over and tell me the actual starting position, length and format of each relevant field, and the RECFM and LRECL of the input file.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Fri Feb 09, 2007 8:29 pm    Post subject: Re: Summing and altering a field value based on the sum No sir did I mean to imply it was your fault. I did not explain the task sufficiently. Allow me to sit down and define the problem w/more thought. Thank you in advance.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Sat Feb 10, 2007 2:11 am    Post subject: I think the attachment will clarify the task I think the attached file will clarify the task. Also being an ICETOOL newbie, I would like to ask is it possible to sort a file and generate a report on that file in the same step? Thank you in advance Bill Ross
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Sat Feb 10, 2007 4:26 am    Post subject:

 Quote: Also being an ICETOOL newbie, I would like to ask is it possible to sort a file and generate a report on that file in the same step?

You can use DFSORT's SORT statement to sort the file and DFSORT's OUTFIL report function to generate a report from the sorted file, all in one pass. Alternatively, you can use a DFSORT/ICETOOL SORT operator to sort the file and a DISPLAY operator to generate a report from the sorted file, in one step but with two passes.

Since you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html

Anyway, here's a DFSORT/ICETOOL job that will put the values you want in positions 191-196. It's just a variation of the previous job I gave you.

 Code: //S1 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN DD DSN=...  input file (FB/220) //T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS) //CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS) //    DD DSN=*.IN,VOL=REF=*.IN,DISP=(OLD,PASS) //OUT DD DSN=... output file (FB/220) //TOOLIN DD * SORT FROM(IN) TO(T1) USING(CTL1) SPLICE FROM(CON) TO(OUT) ON(9,6,CH) -   WITHALL WITH(1,190) WITH(197,24) /* //CTL1CNTL DD *   SORT FIELDS=(9,6,CH,A)   OUTFIL FNAMES=T1,REMOVECC,NODETAIL,    SECTIONS=(9,6,      TRAILER3=(9:9,6,191:COUNT=(M11,LENGTH=6))) /*
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Mon Feb 12, 2007 7:55 pm    Post subject: Re: Summing and altering a field value based on the sum Thank you for the solution.
Bill Ross

New User

Joined: 25 Jan 2007
Posts: 39
Location: Charleston SC

 Posted: Mon Feb 12, 2007 9:46 pm    Post subject: Thank you Sir Thank you for the solution. I will checkout the Getting Started document.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am Copy set of records based on condition krish.deepu SYNCSORT 2 Thu Mar 08, 2018 11:39 am How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us