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
 

 

Matching and replacing JOIN KEYS for PD and ZD fields

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> SYNCSORT
View previous topic :: :: View next topic  
Author Message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 5:03 am    Post subject: Matching and replacing JOIN KEYS for PD and ZD fields
Reply with quote

Hello Team,

I hope everyone is having a great day !

I have a requirement wherein I need to match records based on a key value. I know it can be done using JOIN KEYS. But, I kind of have a different requirement this time.


Two PS files that need to be compared are VB. But, First file has a LRECL of 3770 and the second file has a LRECL of 4704.

And, the key in the first file is a packed decimal. Its position is 193 (Including 4 bytes of VB) and its of length 7. Whereas, the same key in the second file is a zoned decimal and it's position is 22 and it is of length 13.

I need to compare these two files based on the keys mentioned above and match the records based on the keys and write it in a new file.

And the output file should also contain a couple of more fields from the first file and the second file.

From first file :

A field of 2 characters in position 14
A field of 7 PD in position 16.

From second file :

A field of 2 characters in position 24
A field of 7 PD in position 26.

Could anyone help me with the SORT jcl for the above requirement?

Thanks a lot :-)
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Mon Feb 16, 2015 6:56 am    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

It looks like a simple JOINKEYS with a REFORMAT statement for the small amount of data you want.

The only "complication" is that you will need to "normalise" the keys. The keys must be the same type and length.

So, in JNFnCNTL for the file with the ZD key, use INREC OVERLAY to convert it to PD of LENGTH=7.
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 7:04 am    Post subject:
Reply with quote

Thanks for the reply Bill.

I used the below sysin:

Code:
//SYSIN    DD *                       
  JOINKEYS FILES=F1,FIELDS=(193,7,A) 
  JOINKEYS FILES=F2,FIELDS=(22,13,A) 
  JOIN UNPAIRED,F2,ONLY               
  SORT FIELDS=COPY                   
//JNF2CNTL DD *                       
  INREC BUILD=(1,4,22,13,ZD,TO=PD) 



But, I am getting the below error.




Code:
WER146B  4K BYTES OF EMERGENCY SPACE ALLOCATED                       
WER486A  ERROR IN JNF2 PROCESSING                                   
WER482I  JNF1 STATISTICS                                             
WER483B  6,916K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,   
WER483B     0 BYTES RESERVE REQUESTED, 1,016K BYTES USED             
WER108I  SORTJNF1 : RECFM=VB   ; LRECL=  3770; BLKSIZE= 32760       
WER073I  SORTJNF1 : DSNAME=TMCAL.P25064.GROSS.DNYD                   
WER482I  JNF2 STATISTICS                                             
WER483B  6,916K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,   
WER483B     0 BYTES RESERVE REQUESTED, 1,016K BYTES USED             
WER108I  SORTJNF2 : RECFM=VB   ; LRECL=  4704; BLKSIZE= 27998       
WER073I  SORTJNF2 : DSNAME=TMCAL.P25064.CPF050.P1.EC865             
WER027A  CONTROL FIELD BEYOND RECORD                                 
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Mon Feb 16, 2015 12:32 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

Which release of SyncSORT do you have?

It looks like you have an old one.

It also looks like you have a short record.

Is F1 on tape? If not, why the silly blocksize? If yes, why are you testing with tapes?
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 12:37 pm    Post subject:
Reply with quote

Hi Bill,

I am not sure of the version of SyncSort.

May I know what exactly is meant by Short record? And how can I resolve the issue?

Yes, F1 is on tape because F1 file has large amount of data.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Mon Feb 16, 2015 12:57 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

If you look at the sysout from the step, you will see something like 1.4.x.x or 1.3.x.x. It probably also shows the Client company name, which we don't need to see.

A short record is one that is shorter than you are expecting. If you look at your JNF2CNTL you'd be making them if it is being used. You BUILD, so that only the key is on the extract, but then you specify the original position of the data on the JOINKEYS statement.

Often VB files will contain a mix of record-types, even if the other types are only header or trailer records. You may have this anyway.

For testing it is best not to use tape, but to use test data. If you have a file on tape you are allowed to use and extract data from, you can do that. Else create some.

Code:
  OPTION COPY
  INCLUDE COND=(1,4,BI,LE,199)


If you run that against F1, and a similar one for (different length for F2) you can check for short records anyway.
Back to top
View user's profile Send private message
JAYACHANDRAN THAMPY

New User


Joined: 06 Jun 2006
Posts: 8

PostPosted: Mon Feb 16, 2015 5:14 pm    Post subject:
Reply with quote

JNFCNTL statements are supported in Syncsort V1.4.2 version.

Syncsort v1.4.1 supports other Formats like CH, AQ, FI, PD and ZD in JOINKEYS apart from the default BI. The syntax is
JOINKEYS FIELDS=(POSITION,LENGTH,FORMAT,ORDER)
If Format value is omitted, then default format of BI (BINARY) is assumed.
An example JOINKEYS using different formats
Code:
 JOINKEYS FILES=F1,FIELDS=(1,6,CH,A,21,4,PD,A) 
 JOINKEYS FILES=F2,FIELDS=(1,6,CH,A,21,4,PD,A) 
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Mon Feb 16, 2015 10:11 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

Hello Kunal
Try this code:


Code:
//SYSIN    DD *                                             
  JOINKEYS F1=IN1,FIELDS=(05,13,A)                           
  JOINKEYS F2=IN2,FIELDS=(22,13,A)                           
  REFORMAT FIELDS=(F1:18,6,F2:35,6)                         
  OPTION COPY                                               
/*                                                           
//JNF1CNTL DD *                                             
  INREC BUILD=(1,4,193,4,PD,TO=ZD,LENGTH=13,                 
                18,6)                                       
/*                                                           

Regards

Code'd
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 10:21 pm    Post subject:
Reply with quote

Hello Everyone,

Thanks for the responses.

@Bill : The Version of SyncSort I am using is

Code:
 SYNCSORT FOR Z/OS  1.4.0.1R 


So, Could you kindly accordingly suggest the control records?

@Jayachandran :

I have tried using the way you suggested. I am still facing the same error.

@Rinsio,

I tried using the control cards mentioned by you. It gave me the below errors.

Code:
   JOINKEYS F1=F1,FIELDS=(05,13,A)         
            *                               
   JOINKEYS F2=F2,FIELDS=(22,13,A)         
            *                               
   REFORMAT FIELDS=(F1:18,6,F2:35,6)       
   OPTION COPY                             
 WER268A  JOINKEYS STATEMENT: SYNTAX ERROR 
 WER268A  JOINKEYS STATEMENT: SYNTAX ERROR     



F1 is PD. So, I suppose, F1 uses 7 bytes not 13.
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Mon Feb 16, 2015 10:26 pm    Post subject:
Reply with quote

Hi Kunal

Are you using this card ? It convert the key1 into Zoned Decimal.

//JNF1CNTL DD *
INREC BUILD=(1,4,193,4,PD,TO=ZD,LENGTH=13,
18,6)
/*

The REFORMAT statatement you have to adapt to your requirements.
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 10:31 pm    Post subject:
Reply with quote

Hi Rinsio,

I am using the below SYSIN:

Code:
//SYSIN    DD *                               
  JOINKEYS F1=F1,FIELDS=(05,13,A)             
  JOINKEYS F2=F2,FIELDS=(22,13,A)             
  REFORMAT FIELDS=(F1:18,6,F2:35,6)           
  OPTION COPY                                 
/*                                             
//JNF1CNTL DD *                               
INREC BUILD=(1,4,193,4,PD,TO=ZD,LENGTH=13,18,6)


Code'd
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Mon Feb 16, 2015 10:37 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

Apart from missing a blank in front of INREC, you also have only 4 for the length of your PD, which you previously stated was 7.

Is your data sorted? Both files, one, none?

What about the other fields you said you wanted?
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Mon Feb 16, 2015 10:41 pm    Post subject:
Reply with quote

Hi Bill,

I corrected the INREC statement.

And yes, The length of PD is 7 not 4 bytes.

Data is not sorted.

I need other fields as well. I tried above example just as a sample, so that if it worked, I would add other fields to it.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Mon Feb 16, 2015 11:35 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

OK. What you may want to consider is chopping down your records to just what is needed before the SORT.

It would need both JNF1CNTL and JNF2CNTL, and a BUILD for all the data you need, but only the data you need. REFORMAT to match the new records.

With large amounts of data you'll get savings on workspace used, elapsed and CPU time.
Back to top
View user's profile Send private message
Kunal Surpurkar

New User


Joined: 15 Nov 2012
Posts: 47
Location: India

PostPosted: Tue Feb 17, 2015 1:30 am    Post subject:
Reply with quote

Hi Bill,
Thanks for the reply.

Here is my JCL:
Code:

//STEP01   EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SYSOUC   DD SYSOUT=*                                           
//SORTJNF1 DD DISP=SHR,DSN=TMCAL.P25064.GROSS.DNYD               
//SORTJNF2 DD DISP=SHR,DSN=TMCAL.P25064.CPF050.P1.EC865         
//SORTOUT  DD DSN=TMCAL.EPC.MATCHED.RECORDS3,                   
//            DISP=(NEW,CATLG,DELETE),                           
//            UNIT=SYSDA,                                       
//            SPACE=(CYL,(99,99),RLSE),                         
//            DCB=(DSORG=PS,                                     
//            RECFM=VB,LRECL=3770,BLKSIZE=0,BUFNO=14)           
//SYSIN    DD *                                                 
  JOINKEYS FILES=F1,FIELDS=(193,7,PD,A)                         
  JOINKEYS FILES=F2,FIELDS=(22,13,ZD,A)                         
  JOIN UNPAIRED,F2,ONLY                                         
  REFORMAT FIELDS=(F2:5,13,F1:14,2)                             
  SORT FIELDS=COPY                                               
//JNF2CNTL DD *                                                 
  INREC BUILD=(1,4,193,7,PD,TO=ZD)


As told earlier, I am getting the same error.

Code:
   JOINKEYS FILES=F1,FIELDS=(193,7,PD,A)                           
  JOINKEYS FILES=F2,FIELDS=(22,13,ZD,A)                           
  JOIN UNPAIRED,F2,ONLY                                           
  REFORMAT FIELDS=(F2:5,13,F1:14,2)                               
  SORT FIELDS=COPY                                                 
WER164B  93,024K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B     0 BYTES RESERVE REQUESTED, 1,016K BYTES USED           
WER146B  4K BYTES OF EMERGENCY SPACE ALLOCATED                     
WER230A  REFORMAT FIELD OUTSIDE RANGE     


Is my logic in the SYSIN cards correct?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Tue Feb 17, 2015 5:52 am    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

You have this:

Code:
 JOIN UNPAIRED,F2,ONLY


You have said you want matches only. So get rid of the JOIN statement altogether.

You are making a fixed-length REFORMAT record, but you specify DCB information in the JCL which say records are variable-length.

You are using 22,13 on JOINKEYS for F2, but your key starts at position 5.

Fix these things. If you still have problems, post the whole sysout from the step (don't need the version or name of the company).
Back to top
View user's profile Send private message
rinsio

New User


Joined: 16 Feb 2015
Posts: 13
Location: Madrid, Spain

PostPosted: Tue Feb 17, 2015 4:36 pm    Post subject:
Reply with quote

I see a mistake. In my opinion both keys must be the same format, or ZF or PD.
Why do you use JNF2 card? This correspond to File2 and this file has the key as ZD.


Regards
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Tue Feb 17, 2015 6:08 pm    Post subject: Reply to: Matching and replacing JOIN KEYS for PD and ZD fie
Reply with quote

Rinsio,

Welcome to the forum.

It is not an opinion, but a fact that the keys for JOINKEYS must be of the same type across statements.

At the moment we have to assume that Kunal Surpurkar is getting the key from the correct file. There is a lot which doesn't match to what has been said.
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 -> SYNCSORT All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts TSO Logon Failing - Acount Not matching SLNewton TSO/ISPF 1 Thu Aug 25, 2016 9:37 pm


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