Posted: Fri Mar 04, 2005 12:33 pm Post subject: Create one record. Input from two files. SYNCSORT
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.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Fri Mar 04, 2005 2:19 pm Post subject:
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'))
/*
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.
Joined: 19 Dec 2002 Posts: 78 Topics: 6 Location: Trivandrum, India
Posted: Wed Mar 09, 2005 1:37 pm Post subject:
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.
Joined: 19 Dec 2002 Posts: 78 Topics: 6 Location: Trivandrum, India
Posted: Thu Mar 10, 2005 11:40 am Post subject:
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.
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.
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
