JOIN KEYS for PD and ZD fields

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

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 :-)

Bill Woodger · Posted: Mon Feb 16, 2015 6:56 am

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.

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Thanks for the reply Bill.

I used the below sysin:

Bill Woodger · Posted: Mon Feb 16, 2015 12:32 pm

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?

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

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.

Bill Woodger · Posted: Mon Feb 16, 2015 12:57 pm

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.

JAYACHANDRAN THAMPY · New User Joined: 06 Jun 2006 Posts: 8

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

rinsio · Posted: Mon Feb 16, 2015 10:11 pm

Hello Kunal
Try this code:

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Hello Everyone,

Thanks for the responses.

@Bill : The Version of SyncSort I am using is

rinsio · Posted: Mon Feb 16, 2015 10:26 pm

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.

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Hi Rinsio,

I am using the below SYSIN:

Bill Woodger · Posted: Mon Feb 16, 2015 10:37 pm

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?

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

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.

Bill Woodger · Posted: Mon Feb 16, 2015 11:35 pm

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.

Kunal Surpurkar · New User Joined: 15 Nov 2012 Posts: 47 Location: India

Hi Bill,
Thanks for the reply.

Here is my JCL:

Bill Woodger · Posted: Tue Feb 17, 2015 5:52 am

You have this:

rinsio · Posted: Tue Feb 17, 2015 4:36 pm

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

Bill Woodger · Posted: Tue Feb 17, 2015 6:08 pm

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.