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

Join giving more records than expected


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Danielle.Filteau

New User


Joined: 02 Dec 2014
Posts: 16
Location: Canada

PostPosted: Thu Mar 09, 2017 3:28 am
Reply with quote

Hi,
I have 2 input files: CCIDVAL and CCIDPIT. CCIDVAL is based on CCIDPIT, with CCIDPIT containing more columns and more rows.
We have a job that builds a new CCIDVAL nightly based on criteria to choose appropriate rows and a subset of fields from CCIDPIT. I don't want to duplicate this build, so I'm starting with the new CCIDVAL, and I need to produce a report showing a subset of what is in the CCIDVAL table. This subset also needs to include some of the additional columns from the CCIDPIT table. I have joined on all the columns the 2 files have in common (otherwise the row is not unique) and still end up with more rows than expected and I'm not sure why.

CCIDPIT 73401 rows
CCIDVAL 17568 rows
joined file 29224 rows
report from my join, after OMIT processing: 17710 rows - the report should actually have 17479 rows. It's a subset of the CCIDVAL table.

There are some rows in the joined file that need to be excluded from the report, and even after I do that I'm left with 17710 instead of 17479.

CCIDVAL
======
CCID col 3 (12 bytes)
USERID col 16 (8 bytes)
ENV col 25 (8 bytes)
STAGE col 34 (1 byte)
SYSTEM col 36 (8 bytes)
SUBSYS col 45 (8 bytes)
TYPE col 54 (8 bytes)

CCIDPIT
======
CCID col 3 (12 bytes)
ENV col 16 (8 bytes)
STAGE col 25 (1 byte)
SYSTEM col 27 (8 bytes)
SUBSYS col 31 (8 bytes)
EFF-DT col 45 (8 bytes)
XPRY-DT col 56 (8 bytes)
EXPRY-FLAG col 67 (1 byte)
COMMENT col 75 (43 bytes)
CREATE-UID col 120 (8 bytes)
CREATE-DT col 130 (8 bytes)
USERID col 145 (8 bytes)
TYPE col 155 (8 bytes)
LST-UPD-UID col 165 (8 bytes)
LST-UPD-DT col 175 (8 bytes)

Report
=====
CCID col 3 (12 bytes)
ENV col 16 (8 bytes)
STAGE col 25 (1 byte)
SYSTEM col 27 (8 bytes)
SUBSYS col 36 (8 bytes)
EFF-DT col 45 (8 bytes)
XPRY-DT col 54 (8 bytes)
CREATE-UID col 64 (8 bytes)
CREATE-DT col 73 (8 bytes)
COMMENT col 83 (43 bytes)
LST-UPD-UID col 127 (8 bytes)
LST-UPD-DT col 136 (8 bytes)

here is the syncsort syntax:
Code:

//BLDACTV EXEC  PGM=SYNCSORT,COND=(0,LT)
//SORTJNF1 DD DISP=SHR,DSN=SXSC.NDVRC1.CCIDVAL
//SORTJNF2 DD DISP=SHR,DSN=SXSC.NDVRC1.CCIDPIT
//*SORTOUT  DD DISP=OLD,
//*            DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH
//SORTOUT  DD DISP=(NEW,CATLG,DELETE),
//            DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.SORTOUT,
//            SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=129)
//MATCH    DD DISP=(NEW,CATLG,DELETE),
//            DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.JOIN.M,
//            SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=200)
//SYSOUT DD SYSOUT=*
//SORTMSG DD SYSOUT=*
//*******************************************************************
//* JOINKEYS CCIDVAL FIELDS: (IN ORDER, ASCENDING)
//*    CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM, USERID, TYPE
//*
//* JOINKEYS CCIDPIT FIELDS: (IN ORDER, ASCENDING)
//*    CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM, USERID, TYPE
//*
//* SORTOUT FORMAT:
//*    FROM CCIDVAL: CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM,
//*    FROM CCIDPIT: CCID-EFF,CCID-XPRY,CREATE-UID,CREATE-DT, COMMENT,
//*                  LST-UPD-UID,LST-UPD-DT,EXPRY-FLAG. REST OF ROW
//*                  IS FILLER TO PAD TO 200 CHARACTERS.
//* REPORT FORMAT:
//*    CCID, ENVIRONMENT, STAGE, SYSTEM, SUBSYSTEM,CCID-EFF,CCID-XPRY,
//*    CREATE-UID,CREATE-DT, COMMENT, LST-UPD-UID, LST-UPD-DT
//*    REST OF ROW SET TO BLANKS.
//*
//*******************************************************************
//SYSIN DD *
  SORT FIELDS=COPY
  JOINKEYS FILE=F1,
           FIELDS=(3,12,A,25,8,A,34,1,A,36,8,A,45,8,A,16,8,A,54,8,A),
           FORMAT=CH
  JOINKEYS FILE=F2,
           FIELDS=(3,12,A,16,8,A,25,1,A,27,8,A,36,8,A,145,8,A,155,8,A),
           FORMAT=CH
  REFORMAT FIELDS=(F1:3,12,25,8,34,1,36,8,45,8,
                   F2:45,8,56,8,120,8,130,8,75,43,165,8,175,8,
                   67,1)
  OUTFIL FNAMES=MATCH,
         BUILD=(2X,1,12,X,13,8,X,21,1,X,22,8,X,30,8,X,38,8,X,46,8,2X,
                54,8,X,62,8,2X,70,43,X,113,8,X,121,8,57X),
         OMIT=(((38,8,CH,EQ,C'00010101'),AND,(129,1,CH,EQ,C'N')),OR,
               ((38,8,CH,GT,C'00010101'),AND,(129,1,CH,EQ,C'Y')),OR,
               (1,1,CH,EQ,X'40')) 
   END


Those extra 231 rows in the outfil are bugging me. Any idea what is missing? I've even tried using syncsort to figure out what were the rows that didn't match and that is giving me the same 17710 rows. I've been staring at this far too long and just need a fresh pair of eyes.

Code:

//DUP     EXEC  PGM=SYNCSORT,COND=(0,LT)
//SORTJNF1 DD DISP=SHR,DSN=SXSC.NDVRC1.CCIDVAL
//SORTJNF2 DD DISP=SHR,DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.JOIN.M
//SORTOUT  DD DISP=(NEW,CATLG,DELETE),
//            DSN=DXF125.SXSC.NDVRC1.CCIDVAL.ACTSRCH.JOIN.ALL,
//            SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=129)
//MATCH    DD DISP=(NEW,CATLG,DELETE),
//            DSN=DXF125.DUPS,
//            SPACE=(CYL,(5,5)),UNIT=SYSDA,DCB=(RECFM=FB,LRECL=129)
//SYSOUT DD SYSOUT=*
//SORTMSG DD SYSOUT=*
//SYSIN DD *
  SORT FIELDS=COPY
  JOINKEYS FILE=F1,
           FIELDS=(3,12,A,25,8,A,34,1,A,36,8,A,45,8,A),
           FORMAT=CH
  JOINKEYS FILE=F2,
           FIELDS=(3,12,A,16,8,A,25,8,A,27,8,A,36,8,A),
           FORMAT=CH
  JOIN F2,UNPAIRED,ONLY
  OUTFIL  FNAMES=MATCH
  END
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 09, 2017 3:38 am
Reply with quote

You will have some duplicate keys, on either file, or on both.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Thu Mar 09, 2017 10:24 am
Reply with quote

The JNF1/JNF2 statistics ('PAIRED' counts) in the SYSOUT should give an idea on duplicate matches.
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 -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top