IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Syncsort - Joining on different data types


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Tue Jul 21, 2009 9:49 pm
Reply with quote

Hi!

I am trying to match 2 files based on a key, but the key has different data types. I have read that the the comparison is performed on binary data. So shouldn't be a problem in my case. But I am using the following SORT card and getting an empty matched file
Code:

JOINKEYS FILES=F1,FIELDS=(24,09,A)                         
JOINKEYS FILES=F2,FIELDS=(01,09,A)                         
JOIN UNPAIRED,F1                                           
REFORMAT FIELDS=(F1:01,280,F2:1,1)                         
SORT FIELDS=(24,9,ZD,A)                                     
OUTFIL FNAMES=MATCH,INCLUDE=(281,1,CH,NE,C' '),BUILD=(1,280)
OUTFIL FNAMES=NOMATCH,SAVE,BUILD=(1,280)                   
END                                                         


File 1 - key starts from position 24 to 32
Code:

Field 1        Field2           Field 3          Field 4
15/NUM          5/AN          3/AN          9/SNUM   
(1-15)          (16-20)       (21-23)       (24-32)   
1-------------- 2------------ 3------------ 4---------
********************************* TOP OF DATA ********
- - - - - - - - - - - - - - - - - - - - - - - - - 1381
       12345626 01742         220               123454
       12345864 06371         220               123456
       12345865 06371         220               133454



File 2 - key starts form 1 to 9.
Code:

Field1      Field2        Field3
9/AN        12/AN        1/AN           
(1-9)       (10-21)      (22-22)       
1---------- 2----------- 3-------------
********************************* TOP OF
000000000   -            -             
000123454   ........     .             
000123455   ........     .
           

I want two files...matched fiel shoud conatin matched records form first file...and second file should contain unmatched remaining records.

Thanks!
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Tue Jul 21, 2009 10:33 pm
Reply with quote

You say there are two different datatypes. What are they, ZD, PD, A, AN, blah blah blah

You know the things that we need to know to be able to help you, but unless you tell us what is needed to help, then we just sit around twiddling thumbs until psychic day arrives icon_rolleyes.gif again
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Tue Jul 21, 2009 10:43 pm
Reply with quote

Quote:
until psychic day arrives again
Oh, it's back? I hope it had a nice vacation!
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Tue Jul 21, 2009 11:05 pm
Reply with quote

expat wrote:
You say there are two different datatypes. What are they, ZD, PD, A, AN, blah blah blah

You know the things that we need to know to be able to help you, but unless you tell us what is needed to help, then we just sit around twiddling thumbs until psychic day arrives icon_rolleyes.gif again


First file has it defined as PIC S9(9) and the second one as PIC X(9). So one is signed the other isn't, not really different data types. I thought since the comapison is binary we might not need the data types.

Thanks!
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Tue Jul 21, 2009 11:16 pm
Reply with quote

First file is 280 in length and second one is 424.!
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Wed Jul 22, 2009 12:08 am
Reply with quote

Can anyone reply to this pleasE?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Wed Jul 22, 2009 1:17 am
Reply with quote

Your in trouble now icon_cry.gif

The people who post the most here spend alot of time helping out others on their own time whenever they can.

Many of them are from Europe and Asia, so they are probably at home with their families or asleep.

Just about the worst thing you can do is to be impatient. Help will arrive when and if possible.

Quote:
Can anyone reply to this pleasE?


Replied.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Jul 22, 2009 3:30 am
Reply with quote

Hello,

The "rules" for the joined fields say the fields have to be the same type. . .

You have defined them as "09,A".

If you get no matches, i suspect it is because of incorrect displacement or there really are no matches.

Is either of these a VB file? Did you account for the rdw(s).

Post a bit of data from each file that shows they match (use hex on, copy/paste and the "Code" tag).
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Wed Jul 22, 2009 8:19 pm
Reply with quote

dick scherrer wrote:
Hello,

The "rules" for the joined fields say the fields have to be the same type. . .

You have defined them as "09,A".

If you get no matches, i suspect it is because of incorrect displacement or there really are no matches.

Is either of these a VB file? Did you account for the rdw(s).

Post a bit of data from each file that shows they match (use hex on, copy/paste and the "Code" tag).


Yes, my first input file has the key field defined as signed numeric and the second file has it defined as alphanumeric. And I cannot change their definitions. Isn't this why the match is not being found? Both the files are not VB, they are FB. Field number 4 of first File is matched to Field no 1 of second file.
File 1 -
Code:

Field 1         Field 2          Field3          Field4
15/NUM          5/AN          3/AN          9/SNUM   
(1-15)          (16-20)       (21-23)       (24-32) 
1-------------- 2------------ 3------------ 4--------
********************************* TOP OF DATA *******
- - - - - - - - - - - - - - - - - - - - - - - - 12818
000000000060854 14068         599           00017845C
FFFFFFFFFFFFFFF FFFFF         FFF           FFFFFFFFC
000000000060854 14068         599           000178453
-----------------------------------------------------
000000012807528 14068         599           00017845C
FFFFFFFFFFFFFFF FFFFF         FFF           FFFFFFFFC
000000012807528 14068         599           000178453
-----------------------------------------------------
000000014177206 14068         599           00017845C
FFFFFFFFFFFFFFF FFFFF         FFF           FFFFFFFFC
000000014177206 14068         599           000178453 


File 2 -

Code:

Field 1         Field 2          Field3          Field4
9/AN        12/AN        1/AN         
(1-9)       (10-21)      (22-22)       
1---------- 2----------- 3-------------
********************************* TOP O
- - - - - - - - - - - - - - - - - - - -
000178453   D.DA....     D             


SO the last nibble is which is differing due to the sign. IS there any way to get around this.?

Thanks!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Jul 22, 2009 11:57 pm
Reply with quote

Hello,

Quote:
SO the last nibble is which is differing due to the sign.
Yup, that will do it. . .

One way to get around the problem is to reformat the data in the file with the smaller volume (not necessarily lrecl) and then JOIN.

Another is to do the join in program code.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Jul 23, 2009 12:05 am
Reply with quote

"Another is to do the join in program code."

Do you mean actually write code!
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Thu Jul 23, 2009 1:00 am
Reply with quote

[quote="dick scherrer"]One way to get around the problem is to reformat the data in the file with the smaller volume (not necessarily lrecl) and then JOIN.

Sorry but I am not sure what this means? Can you please explain?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Thu Jul 23, 2009 6:14 am
Reply with quote

Hello,

Copy the file with the smaller volume reformatting the problem field as needed. You will add or remove the sign depending on which file you reformat.

Once both files either have the sign (x'Cn') or do not have the sign (x'Fn') the compare will detect the equal you want.
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Tue Aug 04, 2009 2:03 am
Reply with quote

Quote:


Hi!

I tried converting the signed numeric to alphanumeric but did not work. I used the following but I am not gtting the expected output.
The field is at 24'th position in the input file and length is 9. the file length is 280. Please let me know how I can convert it to alphanumeric.
Code:

//SYSIN    DD *                           
 SORT FIELDS=COPY                                         
OUTREC FIELDS=(1:1,23,24:24,9,PD,ZD,LENGTH=9,33:33,247) 
/*                                       


Thanks!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Aug 04, 2009 2:40 am
Reply with quote

Hello,

You have specified PD input and the input is not packed. . .

Look in your Syncsort documentation for "ZDF" if you want to force an "F" sign instead of the "C" sign.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Aug 04, 2009 4:38 am
Reply with quote

shilpa.khaire,

As pointed above by dick, your input is ZD and not PD.

Since the length of the field still remains the same, I think an OVERLAY like this would solve your reformatting issue.(untested)
Code:
//SYSIN    DD *                           
 SORT FIELDS=COPY                                         
 OUTREC OVERLAY=(24:24,9,ZD,M11,LENGTH=9)
/* 
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Aug 04, 2009 4:44 am
Reply with quote

Hi Arun,

Welcome back icon_smile.gif

ZD/ZDF depending on which sign (C/F) this file contains?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Aug 04, 2009 4:58 am
Reply with quote

dick scherrer wrote:
ZD/ZDF depending on which sign (C/F) this file contains?
Thanks Dick. I was away for a while icon_smile.gif

I think ZD should work for both C/F signs in the input. I dont have access to mainframe and cant run a test now. icon_sad.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Aug 04, 2009 9:52 am
Reply with quote

I just ran a test and it looks fine. Alternatively it can also be given as
Code:
//SYSIN    DD *                           
 SORT FIELDS=COPY                                         
 OUTREC OVERLAY=(24:24,9,ZD,ZDF)
/*
Back to top
View user's profile Send private message
shilpa.khaire

New User


Joined: 21 Jul 2006
Posts: 39
Location: US

PostPosted: Tue Aug 04, 2009 8:05 pm
Reply with quote

Quote:

I understand the input is not PD. But what do I use for the input?
And I did try using ZDF too but did not work as expected.

Thanks!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Tue Aug 04, 2009 9:04 pm
Reply with quote

Hello,

The 2 files contain the match value - one with a C sign the other with an F sign. You have to change the format of one of them to match the other.

It does not matter which one you change, only that before trying to match the files, they both have the same sign.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Aug 06, 2009 5:33 am
Reply with quote

shilpa.khaire,

Did you try the code suggested above? This is to reformat one of the input files(file-1) to have 'F' sign before you do a file-matching.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
Search our Forums:

Back to Top