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
 

 

(File1:Document + File2:Document detail) - merge and sort

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Fri Apr 16, 2010 7:53 pm    Post subject: (File1:Document + File2:Document detail) - merge and sort
Reply with quote

Hi guys

I have no experience with DFSORT/ICETOOL and have been struggling with this, so I need your help :)

Consider a Documents file, which represents a series of documents in my system (generated by unloading a DB2 table) and has the following properties:

FB, LRECL = 424, logical record key (document number) a packed digit in position 1, 6 bytes long (s9(11) in cobol).

Example, not accurate in terms of lengths and LRECL:
Code:

372827ABCDE(...)
326256FGHXX(...)
326231MNNS1(...)
326255ZZ31E(...)


Consider the Document detail file, which represents a series of Document Chapters. This file is the result of a DB2 unload of a table that has a foreign key to the Document table.

FB, LRECL = 175, logical record key (document number) a packed digit in position 1, 6 bytes long (s9(11) in cobol).
Also, there exists a second logical key (chapter code) at position 21, 18 characters long.
This file may contain several records with the same document number but only one record per (document number,chapter code) pair. Also, no document number exists in this file that does not exist in file 1.

Example, not accurate in terms of positions and LRECL:
Code:

326256..............CHAP2.....
326231..............CHAP1.....
372827..............CHAP2.....
372827..............CHAP1.....
326255..............CHAP3.....
326255..............CHAP1.....
326256..............CHAP3.....
326231..............CHAP3.....


What I need to do is sort the Document Detail file so that the records are:
a) first, sorted by Document Number in the same order than the Document file and,
b)second, sort all records with the same document number ascending by Chapter code[/list]

Can anyone help me do this? I have been trying some things but I get a strange order (not present in any of the input files) :roll:

As far as I can see I do not have the JOINKEYS operator available.

If you need more info to help me, let me know.

Thanks

EDIT: Just to clear this up - I do not need any of the information present in the Document file.What I need is to sort the Document Detail file according to the order of the Document Number in the Document file AND the Chapter Code of each record in the Document Detail File.
Considering the examples above, the output should be
Code:

372827..............CHAP1.....
372827..............CHAP2.....
326256..............CHAP2.....
326256..............CHAP3.....
326231..............CHAP1.....
326231..............CHAP3.....
326255..............CHAP1.....
326255..............CHAP3.....
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Apr 16, 2010 8:10 pm    Post subject:
Reply with quote

I am assuming your key for second file (Document detail file) is COMP-3 based on 6 byte length. In Cobol it should look like S9(11) COMP-3.

Try this untested,

Code:
//SORT01   EXEC PGM=SORT                                               
//SORTIN   DD  DISP=SHR,DSN=YOUR.DOC.DETAIL.UNLOAD                             
//SORTOUT  DD  DSN=YOUR.DOC.DETAIL.UNLOAD.SRTD,                                   
//             DISP=(,CATLG,DELETE),                                   
//             UNIT=SYSDA                                               
//SYSIN DD *                                                           
   SORT FIELDS=(01,06,PD,D,                                             
                21,18,CH,A)                                             
/*                                                                     
//SYSOUT DD SYSOUT=*                                                   
//*                                                                     



Thanks,
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Fri Apr 16, 2010 8:15 pm    Post subject:
Reply with quote

Hi,

Yes the Document number is an S9(11) COMP-3 field on both files, but your code does not provide what I need.

I need to sort the records by Document Number in the same order that the document number appears in the Document file, not descending like in your solution. I.e., I need the Document file to provide the first level sorting of the Document Detail records...

But thanks anyway icon_smile.gif
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Apr 16, 2010 9:41 pm    Post subject:
Reply with quote

Hi,
Below is multi pass solution. I am not sure usage of temp. files in the same step and hence I have broken this into multiple steps.

Code:
//STEP0100 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD DISP=SHR,DSN=YOUR.DOC.FILE                               
//IN2      DD DSN=YOUR.DOC.DETAIL.FILE,DISP=SHR                         
//DOCH     DD DSN=&&DOCH,DISP=(,PASS),                                 
//         SPACE=(TRK,(50,10),RLSE)                                     
//DOCD     DD DSN=&&DOCD,DISP=(,PASS),                                 
//         SPACE=(TRK,(50,10),RLSE)                                     
//TOOLIN   DD *                                                         
 COPY FROM(IN1) TO(DOCH) USING(CTL1)                                   
 COPY FROM(IN2) TO(DOCD) USING(CTL2)                                   
//*                                                                     
//CTL1CNTL DD *                                                         
 INREC BUILD=(01,006,176:SEQNUM,08,ZD)                                 
/*                                                                     
//CTL2CNTL DD *                                                         
 INREC BUILD=(01,175,176:8C'0')                                   
/*                                                                     
//*                                                                     
//STEP0200 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//CON      DD DSN=&&DOCH,DISP=(OLD,DELETE)                             
//         DD DSN=&&DOCD,DISP=(OLD,DELETE)                             
//TEMP2    DD DSN=&&TEMP,DISP=(,PASS),                                 
//            SPACE=(TRK,(50,10),RLSE)                                 
//TOOLIN   DD *                                                         
 SPLICE FROM(CON) TO(TEMP2) ON(01,06,PD) -                             
   WITH(01,175) WITHALL                                                 
//*                                                                     
//STEP0300 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=&&TEMP,DISP=(OLD,DELETE)                             
//SORTOUT  DD  DSN=YOUR.DOC.DETAIL.FILE.SRTD,                           
//             DISP=(,CATLG,DELETE),                                   
//             UNIT=SYSDA                                               
//SYSIN    DD *                                                       
  SORT FIELDS=(176,08,ZD,A)                                           
  OUTREC BUILD=(1,175)                                                 
//*     



Let me know if it works.


Thanks,
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Apr 16, 2010 9:50 pm    Post subject:
Reply with quote

OOpps I forgot to resort your detail file on chapter code.

Please change STEP0300 as below...

Code:
//STEP0300 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=&&TEMP,DISP=(OLD,DELETE)                             
//SORTOUT  DD  DSN=YOUR.DOC.DETAIL.FILE.SRTD,                           
//             DISP=(,CATLG,DELETE),                                   
//             UNIT=SYSDA                                               
//SYSIN    DD *                                                       
  SORT FIELDS=(176,08,ZD,A,
               021,18,CH,A)                                           
  OUTREC BUILD=(1,175)                                                 
//*     
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri Apr 16, 2010 10:28 pm    Post subject:
Reply with quote

sqlcode1 has the right idea but this can actually be done with one ICETOOL step as follows:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/424)
//IN2 DD DSN=...  input file2 (FB/175)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/175)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,6,PD) WITHALL WITH(1,175)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(176:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(176:8X)
/*
//CTL3CNTL DD *
  SORT FIELDS=(176,8,ZD,A,21,8,CH,A)
  OUTREC BUILD=(1,175)
/*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Apr 16, 2010 10:40 pm    Post subject:
Reply with quote

Thanks for correction Frank,

I wasn't sure about creating temp. files in a step and then later read the same file in the same step.

Out of curiosity, Is there an advantange in terms of TAPE I/Os or CPU processing when we combine multiple steps into 1 against running them as separate steps?

I looked for an answer in all dfsort manuals but couldn't find it.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri Apr 16, 2010 10:43 pm    Post subject:
Reply with quote

Quote:
Out of curiosity, Is there an advantange in terms of TAPE I/Os or CPU processing when we combine multiple steps into 1 against running them as separate steps?


I don't know. You could try it and see.

People usually prefer to use one step if possible on the theory that less is better. icon_smile.gif
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Fri Apr 16, 2010 10:52 pm    Post subject:
Reply with quote

Thank you very much! icon_biggrin.gif

You have both saved me a lot of 'hair-pulling time' and taught me a something about DFSORT/ICETOOL.

Best regards
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Fri Apr 16, 2010 11:03 pm    Post subject:
Reply with quote

Just one more thing...
This works great for my test data but when I work on a large set of data, about 500k registers to sort in the detail file, I get an SD37 Abend.

Could this be because I'm trying to allocate more space for the temporary files than I'm allowed?
I have tried googling an explanation for this abend but nothing conclusive came up.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Apr 17, 2010 12:50 am    Post subject:
Reply with quote

Hello,

At the top of the page is a link to "IBM Manuals". Down the list is the z/OS System Codes publication.

Use the manual search (the flashlight/tubelight near the top left) and find d37. From there search for the actual message (IECxxxx) in the z/OS System Messages Vol VII.

If there are any questions, reply back here.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Sat Apr 17, 2010 12:55 am    Post subject:
Reply with quote

Using LookAt, I found the following explanation for the D37 ABEND in IEC031I:

A data set opened for output used all the primary space, and no secondary space was requested. Change the JCL specifying a larger primary quantity or add a secondary quantity to the space parameter on the DD statement.

What do your DD statements for the temporary files look like? Are you specifying a secondary quantity? Do you have that kind of space available on your system? I'd try something like

(CYL,(50,50))

and increase if needed.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Sat Apr 17, 2010 11:49 pm    Post subject:
Reply with quote

If this still doesn't work then try adding below lines to each ICETOOL steps.

Code:
//DFSPARM  DD *                                                 
  OPTION DYNALLOC=(,100)
/*


Let me know if it works.


Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Apr 20, 2010 1:39 am    Post subject:
Reply with quote

That would only work if the D37 ABEND was for one of DFSORT's dynamically allocated data sets. It would not help if the D37 ABEND is for one of the temporary data sets (T1 or T2). Of course, the OP didn't really say which data set got the D37 (well, why bother to actually supply pertinent information when you can have everyone guess instead).
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Tue Apr 20, 2010 11:05 pm    Post subject: Sorry
Reply with quote

I am sorry for the long time it took me to answer, but I was retained abroad (due to volcanic ashes icon_smile.gif )

Frank, I didn't provide more information about the abend 'cause I had none. I only got a memory dump and the message:

Code:

AN SD37  ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT (PHASE C 3)


Anyway, the problem was in fact in the allocation of one of the temporary files, it's solved now.

Thank you all for your help and patience.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Apr 20, 2010 11:23 pm    Post subject:
Reply with quote

Hmmm ... you should have gotten a JES message that indicated which data set the D37 was associated with. An IECxxxI message of some kind (not an ICExxxI message). If you still have the JES log from the job that got the D37, you might want to take another look for future reference. The JES messages have important information for some types of abends.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Apr 21, 2010 12:04 am    Post subject: Reply to: (File1:Document + File2:Document detail) - merge a
Reply with quote

Quote:
Thank you all for your help . . .
You're welcome icon_smile.gif

Good to hear it is working and thank you for the followup,

d
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Wed Apr 21, 2010 12:40 am    Post subject:
Reply with quote

Frank Yaeger wrote:
Hmmm ... you should have gotten a JES message that indicated which data set the D37 was associated with. An IECxxxI message of some kind (not an ICExxxI message).


You're absolutely right, mea culpa. I indeed found it and it referred me to the T1 dataset.

Thanks.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Wed Apr 21, 2010 12:57 am    Post subject:
Reply with quote

Quote:
Of course, the OP didn't really say which data set got the D37 (well, why bother to actually supply pertinent information when you can have everyone guess instead).


I retract this snippy comment given that you didn't know the info was available.
Back to top
View user's profile Send private message
henriquecosta

New User


Joined: 06 Jan 2010
Posts: 9
Location: Lisbon, Portugal

PostPosted: Wed Apr 21, 2010 1:07 am    Post subject:
Reply with quote

No problem, thanks icon_lol.gif

Now I know, so hopefully next time no snippiness will be required on this topic icon_cool.gif
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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