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
 

 

Join giving more records than expected

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

New User


Joined: 02 Dec 2014
Posts: 11
Location: Canada

PostPosted: Thu Mar 09, 2017 3:28 am    Post subject: Join giving more records than expected
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Thu Mar 09, 2017 3:38 am    Post subject: Reply to: Join giving more records than expected
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: 2276
Location: @my desk

PostPosted: Thu Mar 09, 2017 10:24 am    Post subject:
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    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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


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