Merge, Splice, what can I do?

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Hello,

I want to cross three files (RECFM=FB,LRECL=280). I�ve been trying to do a splice but I�ve got problems because sometimes I�ve got duplicates and the sort don�t sort them OK. Can yo help me with this?

Thanks a lot. (See the attachment)

Iratxe

edited to get rid of the attachment and inline the info provided (e.s)

I've got a problem. I've got three files FB and LRECL=280 like this:

File A contains:

guptae · Posted: Thu Jan 28, 2010 2:09 pm Post subject:

Hello Iratxe,

Would you please paste your code using Code Tags as all the member does not have access to download?

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Ok, this is:

File A contains:

PeterHolland · Posted: Thu Jan 28, 2010 2:17 pm Post subject:

And what is the NOT desired output?
And how do you sort?

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

This is the sort I'm working with:

Frank Yaeger · Posted: Fri Jan 29, 2010 12:02 am Post subject:

It appears that you want to match the first field in FileA to the first field in FileB and retain all of the matching records from those files. Then it appears you want to match the second field from FileB with the first field from FileC but only want to keep one record from FileC if there are duplicates. Is that correct?

In your job, you seem to be skipping records and including records with certain characters, etc, but you don't explain why and it doesn't seem to relate to your input/output example at all. What's that about?

You need to do a better job of explaining the rules for getting from input to output. Which files can have duplicates? How do you want to handle them?

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Hi, again,

The join between File A and File B is "quite easy", my problem appears when I try to join File B with File C, because in File B there are more than 1 regs with the same information in position 11, and I want each reg with his "partner" form File C.

As in the example I�ve this:

File B contains:
Code:
HAJB05AL: HKU013L3
HAJB05AM: HKU182L1
HAJB06BA: HKU013L3
HAJB07CG: HKU013L3

File C contains:
Code:
HKU009L3: LOAD DATA RESUME YES LOG YES DISCARDS 0 SHRLEVEL CHANGE INDDN SYSREC00
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HKU182L1: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE

And I want:
HAJB05AL: HKU013L3
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HAJB05AM: HKU182L1
HKU182L1: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HAJB06BA: HKU013L3
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
HAJB07CG: HKU013L3
HKU013L3: LOAD DATA DISCARDS 0 INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE

Is there any solution?

Thank you very much.
Iratxe

Frank Yaeger · Posted: Tue Feb 02, 2010 2:29 am Post subject:

Skolusu

Iratxe,

Here is my crack at your problem. I assumed you have 3 files all with an LRECL of 280 and RECFM FB.

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Hello,

I�m making proves with your solution, but, although all steps finish with 00 in the file result I don�t have all the information, I only have the information from the file with JCL control cards.

I�ve discovered my problem is in the STEP0300, in this step I don�t obtain the information I'm supposed to, and I don�t know why, I just do what you suggested, maybe, do you work anytime with VB file?
Is it possible I need a PTF specific for this?

Thanks a lot,

Iratxe

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Hi,

Just for giving more information about my problem:

After executing the first step this is what Sort says:

OUTPUT LRECL = 280, BLKSIZE = 27720, TYPE = FB
IN MAIN STORAGE SORT
INSERT 0, DELETE 10
RECORDS - IN: 259, OUT: 249
NUMBER OF BYTES SORTED: 72520

After executing the secofn step this is what Sort says:

SORTOUT : EXCP USED, LRECL = 280, BLKSIZE = 27720, TYPE = FB (SDB)
EF-K10929 CB-K90013 F0-K30362 E8-K44563
INSERT 0, DELETE 0
RECORDS - IN: 0, OUT: 0
SORTOUT : DELETED = 0, REPORT = 1, DATA = 0
SORTOUT : TOTAL IN = 0, TOTAL OUT = 1

Now after the third step, this is what Sort says:

SORTOUT : EXCP USED, LRECL = 280, BLKSIZE = 27720, TYPE = FB
DE-K24705 D5-K24705 D9-K24705 CB-K90013 E8-K44563
IN MAIN STORAGE SORT
INSERT 0, DELETE 0
RECORDS - IN: 510, OUT: 510
SORTOUT : DELETED = 510, REPORT = 0, DATA = 0
SORTOUT : TOTAL IN = 510, TOTAL OUT = 0
NO DATA RECORDS FOR AN OUTFIL DATA SET - RC=0
NUMBER OF BYTES SORTED: 142800

as you can see, this last one obtains 0 records for Sortout.

Iratxe

Skolusu · Posted: Thu Feb 04, 2010 11:00 pm Post subject:

Iratxe,

I need to see your JCL and control cards you used in order to help you.

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Good morning,

Here is my job:

//** FILE A: DB2 CHARACTERISTIC : RECFM=FB,LRECL=280
//*FEU00TL4: LOAD DATA INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
//*FEU00UL4: LOAD DATA INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
//*FEU00VL4: LOAD DATA INDDN SYSREC01 RESUME YES LOG YES SHRLEVEL CHANGE
//*FEU00LL1: LOAD DATA INDDN SYSREC01 RESUME YES
//*
//** FILE C: RELATION BETWWEN JOB AND DB2: RECFM=FB,LRECL=280
//*HBJBY2AZ:FEU00TL4
//*HBJBY3AZ:FEU00UL4
//*HBJBY4AZ:FEU00VL4
//*HBJBY8AK:FEU00LL1
//*
//** FILE E: JOB CHARATERISTICS : RECFM=FB,LRECL=280
//*HBJBY2AZ: //SYSIN DD DSN=SIC.PROD.DATAUTIL(FEU00TL4),DISP=SHR
//*HBJBY3AZ: //SYSIN DD DSN=SIC.PROD.DATAUTIL(FEU00UL4),DISP=SHR
//*HBJBY4AZ: //SYSIN DD DSN=SIC.PROD.DATAUTIL(FEU00VL4),DISP=SHR
//*HBJBY8AK: //SYSIN DD DSN=SIC.PROD.DATAUTIL(FEU00LL1),DISP=SHR
//*
//*********************************************************************
//* ELIMINATE DUPS FROM FILEC AND CREATE FILED *
//*********************************************************************
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=FILEA,DISP=SHR
//SORTOUT DD DSN=FILEB,
// DISP=(NEW,CATLG),DCB=(RECFM=FB,LRECL=280),
// SPACE=(280,(5000,5000),RLSE)
//SYSIN DD *
SORT FIELDS=(1,8,CH,A),EQUALS
SUM FIELDS=NONE
//*
//*
//*********************************************************************
//* CREAE A 1 LINE HEADER TO DISTINGUISH THE RECORDS FROM EACH FILE *
//*********************************************************************
//STEP0200 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL REMOVECC,BUILD=(280X),HEADER1=('HDR')
//*
//*********************************************************************
//* CONCATENATE FILED FROM STEP0100 AND FILEB AND GET THE MATCHING *
//* RECORDS. CREATE FILEE RECORDS WITH KEY OF FILEB FOR MATCH RECORDS *
//*********************************************************************
//STEP0300 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0200.SORTOUT
// DD DSN=FILEB,DISP=SHR
// DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0200.SORTOUT
// DD DSN=FILEC,DISP=SHR
//SORTOUT DD DSN=FILED,
// DISP=(NEW,CATLG),DCB=(RECFM=FB,LRECL=280),
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(281:ID=1)),
IFTHEN=(WHEN=(281,1,ZD,EQ,1),OVERLAY=(283:01,8)),
IFTHEN=(WHEN=(281,1,ZD,EQ,2),OVERLAY=(283:11,8))
SORT FIELDS=(283,8,CH,A),EQUALS
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(291:SEQNUM,8,ZD,RESTART=(283,8))),
IFTHEN=(WHEN=GROUP,BEGIN=(291,8,ZD,EQ,1),PUSH=(282:281,1,300:1,280))
OUTFIL INCLUDE=(281,2,ZD,EQ,21,AND,1,3,CH,NE,C'HDR'),
BUILD=(1,280,/,1,8,308,272)
//*
//********************************************************************
//* CONCATENATE FILEE FROM STEP0300 AND FILEA AND GET THE MATCHING
//* RECORDS.
//********************************************************************
//STEP0400 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0200.SORTOUT
// DD DSN=FILEE,DISP=SHR
// DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0200.SORTOUT
// DD DSN=FILED,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(281:ID=1))
SORT FIELDS=(01,8,CH,A),EQUALS
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(282:1,8)),
IFTHEN=(WHEN=INIT,OVERLAY=(290:SEQNUM,8,ZD,RESTART=(281,9))),
IFTHEN=(WHEN=GROUP,BEGIN=(281,1,ZD,EQ,2,AND,290,8,ZD,EQ,1),
PUSH=(300:11,8),RECORDS=2)
OUTFIL IFOUTLEN=280,OMIT=(1,3,CH,EQ,C'HDR'),
IFTHEN=(WHEN=(281,1,ZD,EQ,2,AND,290,8,ZD,EQ,2),
BUILD=(17:300,8,9,280))
//*

Skolusu

Iratxe,

The sample data you show in your latest post is different from your first post.

The job I have shown assumes

Key to be matched in file A is in pos 1 for a length of 8 bytes

Key to be matched in file B is in pos 11 for a length of 8 bytes

Key to be matched in file C is in pos 1 for a length of 8 bytes

The input you used shows the keys in a different position. You need to adjust the job accordingly.

If you have trouble changing that then tell me the key position and format in each file and I will show you a way to do it

Iratxe · New User Joined: 21 Feb 2006 Posts: 19

Skolusu,

Thanks very much. I've revised my data positions and I�ve changed them and it runs perfectly. Thanks very much and sorry if I�ve caused you any problem.

See you,

Iratxe