MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Create one record. Input from two files. SYNCSORT

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Dummy
Beginner


Joined: 03 Jan 2003
Posts: 8
Topics: 1

PostPosted: Fri Mar 04, 2005 12:33 pm    Post subject: Create one record. Input from two files. SYNCSORT Reply with quote

Its quite sometime since I used SORT. I have two files Input-A and Input-B. Need to create a Output file. There's a common field which needs to be used to join these two files.

This common field is in position 5&6 of the Input-A and position 1&2 of Input-B.

Output file will have data from the both files merged into one record of Input-A.

Input-A is FB 342 bytes
Input-B is FB 362 bytes
Output any format is fine.

Below is an Example:

Input-A
Code:

  TX01TX
  TX01TX
  CA01CA
  CA02CA
  MN01MN
  MN02MN
  MN02MN


Input-B
Code:


  CA LAWNMOVERS 901191
  TX FOOD       911191
  MN IT-DIVIS   002100
  NM FURNITURE  000021


Output-Final-Preferred
Code:

  TX01TX FOOD       911191
  TX01TX FOOD       911191
  CA01CA LAWNMOVERS 901191
  CA02CA LAWNMOVERS 901191
  MN01MN IT-DIVIS   002100
  MN02MN IT-DIVIS   002100
  MN03MN IT-DIVIS   002100
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12359
Topics: 75
Location: San Jose

PostPosted: Fri Mar 04, 2005 2:19 pm    Post subject: Reply with quote

Dummy,

Assuming that you have less than 300 unique values in input-b then the following JCL will give you desired results. If you have more than that then I will show you another way to do it.

Code:

//STEP0100 EXEC PGM=SYNCTOOL                               
//TOOLMSG   DD SYSOUT=*                                   
//DFSMSG    DD SYSOUT=*                                   
//INPUTB    DD *                                           
CA LAWNMOVERS 901191                                       
TX FOOD       911191                                       
MN IT-DIVIS   002100                                       
NM FURNITURE  000021                                       
//INPUTA    DD *                                           
TX01TX                                                     
TX01TX                                                     
CA01CA                                                     
CA02CA                                                     
MN01MN                                                     
MN02MN                                                     
MN02MN                                                     
//T1        DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(5,5),RLSE)
//OUT       DD SYSOUT=*                                   
//TOOLIN    DD *                                           
  COPY FROM(INPUTB) USING(CTL1)                             
  COPY FROM(INPUTA) USING(CTL2)                             
//CTL1CNTL  DD *                                               
  OUTFIL FNAMES=T1,                                             
  OUTREC=(22X,     $ 22 SPACES                                 
          C'C',    $ CHARACTER C                               
          X'7D',   $ OPENING QUOTE IN HEX                       
          1,2,     $ STATE CODE                                 
          X'7D',   $ CLOSING QUOTE IN HEX                       
          C',C',   $ CHARACTER COMMA AND C                     
          X'7D',   $ OPENING QUOTE IN HEX                       
          4,30,    $ STATE NAME                                 
          X'7D',   $ CLOSING QUOTE IN HEX                       
          C',',    $ COMMA                                     
          80:X)    $ PAD WITH SPACE TO 80 BYTES                 
//CTL2CNTL  DD *                                               
  OUTFIL FNAMES=OUT,                                           
  OUTREC=(1,7,                $ FIRST 7 BYTES IN INPUT-A       
          08:5,2,CHANGE=(30,  $ CHANGE THE STATE NAME           
//          DD DSN=&T1,DISP=OLD,VOL=REF=*.T1                   
//          DD *                                               
                      C'ZZ',C'NEVER TRUE CONDITION          '),
                      NOMATCH=(C'THERE IS NO VALID STATE CODE'))
/*


Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Dummy
Beginner


Joined: 03 Jan 2003
Posts: 8
Topics: 1

PostPosted: Mon Mar 07, 2005 12:53 pm    Post subject: Reply with quote

Thanks Kolusu.

Cool, the code generates sort control cards but am doubtful if it will be appilicable in my case. My bad, I thought of giving a simple version then transfer it to my real need. Let me give the actual files. Each file has a million records.

Input-A is 337 bytes in length. This is unique on Common field.
Input-B is 307 bytes in length.
Common field is in position 1 of Input-A. This is a Packed Decimal and 10 bytes in length (s 9 ( 1 8 ) comp-3 if you are a COBOL kind of guy).
Common field is in position 8 of Input-B.

Output will be
Code:

 Common-field! other data from file-B!other data from file-a!


I managed to get below code working. But still the below code does not merge the data from both the files into one record.

Code:

//SORT EXEC PGM=SYNCTOOL                                         
//TOOLMSG DD SYSOUT=*                                             
//DFSMSG DD SYSOUT=*                                             
//INPUTA DD DISP=SHR,DSN=MY.INPUTA                               
//INPUTB DD DISP=SHR,DSN=MY.INPUTB                               
//OUTB    DD DSN=&&LGL,UNIT=SYSDA,SPACE=(CYL,(55,55)),DISP=(,PASS)
//OUTA    DD DSN=&&ENR,UNIT=SYSDA,SPACE=(CYL,(55,55)),DISP=(,PASS)
//OUTBOTH DD DSN=*.OUTB,VOL=REF=*.OUTB,DISP=(OLD,PASS)           
//        DD DSN=*.OUTA,VOL=REF=*.OUTA,DISP=(OLD,PASS)           
//OUTFNL  DD DISP=SHR,DSN=MY.OUTPUT.FILE                         
//TOOLIN DD *                                                     
 COPY FROM(INPUTB) TO(OUTB) USING(OUTB)                           
 COPY FROM(INPUTA) TO(OUTA) USING(OUTA)                           
 SORT FROM(OUTBOTH) TO(OUTFNL) USING(FINL)                       
//OUTBCNTL DD *                                                   
 OUTREC FIELDS=(1:8,10,11:1,7,18:18,290,308:337X)                 
//OUTACNTL DD *                                                   
 OUTREC FIELDS=(1:1,10,11:307X,318:11,327)                       
//FINLCNTL DD *                                                   
 SORT FIELDS=(1,10,CH,A,18,22,CH,A)                               
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12359
Topics: 75
Location: San Jose

PostPosted: Wed Mar 09, 2005 10:36 am    Post subject: Reply with quote

Dummy,

you are not performing sum on additional fields to be merged as 1 record. So your jcl will not give you the desired results.

Check this link which explains in detail about merging two files into a single file

http://www.mvsforums.com/helpboards/viewtopic.php?t=10&highlight=merge

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
hari_uss
Beginner


Joined: 19 Dec 2002
Posts: 78
Topics: 6
Location: Trivandrum, India

PostPosted: Wed Mar 09, 2005 1:37 pm    Post subject: Reply with quote

Kolusu,

I was trying to do something like the one in your first example sometimes back.
What I wanted to do was, select a field (say field-1) from the first input file based on certain INCLUDE conditions. Then select records from file-2 where field2 of file-2 has same value as field-1 (of file-1).

Using the first INCLUDE condition, I created a list of field-1 values. Then I dynamically built a SORT card which had INCLUDE condition which will compare the field-1 with field-2 and extract all matching records from file-2.

It worked with small input files but when used the 'real' file, approximately 19k records were selected from my first input and SYNCSORT complained that it cannot handle that many conditions in its internal static storage area. Error message was,

WER002A EXCESS CARDS

Just curious to know the solution you have..
Quote:
Assuming that you have less than 300 unique values in input-b then the following JCL will give you desired results. If you have more than that then I will show you another way to do it.


If you want to have a look at my job, please let me know. I'll post it here.

Regards, Hari.
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12359
Topics: 75
Location: San Jose

PostPosted: Wed Mar 09, 2005 2:22 pm    Post subject: Reply with quote

hari_uss,

Check this link which discusses in detail about wer002a error.

http://www.mvsforums.com/helpboards/viewtopic.php?t=2491&highlight=wer002a

At any rate if you are generating more 1000 conditions then you will have a problem.

If you have the latest version of syncsort then there is a way to extract records using splice.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
hari_uss
Beginner


Joined: 19 Dec 2002
Posts: 78
Topics: 6
Location: Trivandrum, India

PostPosted: Thu Mar 10, 2005 11:40 am    Post subject: Reply with quote

Kolusu,

I tried the options given in the post you showed but none of them worked. MINCORE value at my installation is 282k. I tried to override it till 6M, but that too didn't help. May be because of the large number of records I have.
Quote:

At any rate if you are generating more 1000 conditions then you will have a problem.

Syncsort at my installation do support SPLICE. But I didn't get time to try it out today(as I told in the earlier post, I faced this problem sometime back and it was solved by writing a program. Now I am just trying it out of curiosity). Planning to try it when I get time.

Thanks,
Hari.
Back to top
View user's profile Send private message Send e-mail
Dummy
Beginner


Joined: 03 Jan 2003
Posts: 8
Topics: 1

PostPosted: Thu Mar 10, 2005 6:37 pm    Post subject: Reply with quote

Thanks Kolusu,

In the output I had posted, there will be one record from the inputA and one or more records from InputB for the same first 10 bytes.

But in my final output first 10 bytes are not unique so I avoided SUM FIELDS. However it will be unique on first 10 bytes + next 7 bytes. For the records we are taking from InputA you might observe that position 11 to 17 is being SPACES.

As an example my current output is:

Code:

1234567890ABCDEFGinputb-1
1234567890                       input-a
1234567890GHIJKLMinputb-2


Desired output will be
Code:

1234567890ABCDEFGinputb-1input-a
1234567890GHIJKLMinputb-2input-a
Back to top
View user's profile Send private message
mfjin
Beginner


Joined: 26 Apr 2003
Posts: 94
Topics: 17

PostPosted: Wed Jul 20, 2005 4:21 am    Post subject: Reply with quote

Sorry I have been a little out of touch with all this. But hasnt syncsort come up with an equivalent for dfsorts SPLICE operator ?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12359
Topics: 75
Location: San Jose

PostPosted: Wed Jul 27, 2005 7:03 am    Post subject: Reply with quote

mfjin,
Quote:

But hasnt syncsort come up with an equivalent for dfsorts SPLICE operator ?



The latest version of syncsort supports the splice operator

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group