IBM Mainframe Forum

by **ABRN170** » Thu Dec 03, 2009 4:09 pm

Hi All,

I need some help please. I am a beginner with Easytrieve and I think my problem is mostly because I don't quite understand it yet, but any help and patience will greatly be appreciated.
Let me explain what I am trying to do.

I have 2 files both sorted via Reference number and Document Sequence. So 1 reference number can contain multiple Sequence numbers, and thus some reference numbers are duplicated in both files.

1st file (FILEA) has 252,226 records
2nd file (FILEB) has 17,942 records

The 2nd file is an extract I ran on our database and from the 252,226 records 17,942 records has to be updated.

The 2nd file contains values I want to update the 1st file with. Example...the records in FILEA that has no Client Code I want to update with the value in FILEB.
So in short when I Match the files and create the Output dataset, I want to be able to use this dataset to upload the data, but the totals must be the same as FileA 252,226.
But with my JCL I have am 10,093 records more? What could cause this?

i split the Matched and unmatched into different datasets in the hope that it might work, but to no avail. My Easytrieve looks as follows:

Select all

/=/=/=/=/=/=/=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=//=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=
//*
//**********************************************************************
//STP01#01 EXEC PGM=EZTPA00
//**********************************************************************
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSNAP DD SYSOUT=*
//*
//FILEA DD DSN=SDIB.ABRN170.PAD.X28GADB.UAT.UNLOAD.SRT,DISP=SHR
//FILEB DD DSN=SDIB.ABRN170.PAD.GADB.UAT.CLNTCODE.UPDATE,DISP=SHR
//FILEC DD DSN=SDIB.ABRN170.PAD.X28GADB.UAT.MATCH,
// DISP=(MOD,CATLG),
// SPACE=(TRK,(50,50),RLSE),
// RECFM=FB,LRECL=409
//*
//FILED DD DSN=SDIB.ABRN170.PAD.X28GADB.UAT.NOMATCH,
// DISP=(MOD,CATLG),
// SPACE=(TRK,(50,50),RLSE),
// RECFM=FB,LRECL=409
//*
//SYSIN DD *
*
FILE FILEA FB(409 27812)
IRECA 001 409 A
A-REF_NO 001 005 P
A-DOC_SEQ 006 002 P
A-CLIENT_CODE 008 010 A
A-DATE 339 026 A
*
FILE FILEB FB(17 32759)
B-REF_NO 001 005 P
B-DOC_SEQ 006 002 P
B-CLIENT_CODE 008 010 A
*
FILE FILEC FB(409 27812)
ORECC 001 409 A
C-REF_NO 001 005 P
C-DOC_SEQ 006 002 P
C-CLIENT_CODE 008 010 A
*
FILE FILED FB(409 27812)
ORECD 001 409 A
D-REF_NO 001 005 P
D-DOC_SEQ 006 002 P
D-CLIENT_CODE 008 010 A
*
JOB INPUT (FILEA KEY(A-REF_NO) +
FILEB KEY(B-REF_NO))
*
IF MATCHED
ORECC = IRECA
C-CLIENT_CODE = B-CLIENT_CODE
PUT FILEC
GOTO JOB
END-IF
*
IF NOT MATCHED
ORECD = IRECA
PUT FILED
GOTO JOB
END-IF

/=/=/=/=/=/=/=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=//=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=

Any help or suggestions will be greatly appreciated.

by **dick scherrer** » Fri Dec 04, 2009 10:06 am

Hello and welcome to the forum,

Suggest you create a very small pair of files for testing - only 5 records or so per file. Make sure you can predict which should match and which should not.

Run your code and see what this shows. If it is not clear, post the data in the output files here and we can review what happened.

I would also discontinue using MOD unless there is some necessity to use MOD.

Also, suggest you practice a bit with the "Code" tag to preserve alignment and improve readability.

by **ABRN170** » Fri Dec 04, 2009 10:33 am

Thanks D.

Will set up smaller files and see what happens. There is no real need for the MOD, will change this.

Thanks for the tip on the code tag...will use it in future.

by **ABRN170** » Mon Dec 14, 2009 4:46 pm

I have split the file into smaller junks and discovered that my extract ran later than my unload. and thus there were newer records that caused the confusion with the MATCH totals. But this has not resolved the problem.

The "IF MATCHED" clause works 100%. Matching the records and writing them out as I want. GREAT!

But the "IF NOT MATCHED" is not working. This clause is picking up records from the Match clause and also adding them to my output Dataset. The â€œIF NOT MATCHEDâ€ is not skipping the records found in the "IF MATCH" clause.

Example I have a reference number that is repeated 27 times. Due to the nature of the data on our system this is how it was stored if coming from another business unit.

Anyways the "IF NOT MATCHED" is picking up 25 of the 27 records that was already picked up successfully in the "IF MATCHED" clause.

The 25 records are exact duplicates but they also need to be updated with Client Code: EXAMPLE001.

Below is the file I extracted with the Client code. Thus I now want to read FILEA and just add the client code.

COLUMS 0 - 9 = REF_NO
COLUMS 10-12 = DOC_SEQ
COLUMS 13-22 = CLIENT_CODE

Select all

----+----1----+----2----+----3----+----4----+----5
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 0EXAMPLE001
14 1EXAMPLE001
14 3EXAMPLE001

Below is the UNLOAD File I want to update. FILEA:

COLUMNS 0 - 9 = REF_NO
COLUMNS 10-12 = DOC_SEQ
COLUMNS 13-22 = CLIENT_CODE
COLUMNS 23-38 = ACCOUNT_NUMBER
COLUMNS 39-46 = PRODUCT_CODE
COLUMNS 47 = CLIENT_TYPE_GRP
COLUMNS 48-52 = CLIENT_TYPE
COLUMNS 53-92 = FIRST_NAME

Select all

----+----1----+----2----+----3----+----4----+----5----+----6----+----7
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999967CASI I00101ANET
14 0 0000000409999966CASI I00101ANET
14 0 0000000409999967CASI I00101ANET
14 0 0000000040999997    I00101ANET
14 0 0000000040999997    I00101ANET
14 0 0000000409999990CASI I00101ANET
14 0 0000000040999999    I00101ANET
14 0 0000000040999997    I00101ANET
14 0 0000000409999971CASI I00101ANET
14 0 0000000409999970CASI I00101ANET
14 0 0000000409999968CASI I00101ANET
14 0 0000000409999969CASI I00101ANET
14 0 0000000409999967CASI I00101ANET
14 0 0000000409999967CASI I00101ANET
14 0 0000000409999967CASI I00101ANET
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999965CASI I00101ANET
14 0 0000000409999964CASI I00101ANET
14 0 0000000409999964CASI I00101ANET
14 0 0000000409999964CASI I00101ANET
14 0 0000000409999964CASI I00101ANET
14 1    I00101ANET
14 3    I00101ANET

Only 3 are discarded in the "IF NOT MATCH" clause.

How can I make Easytrieve ignore the ones picked up in the "IF MATCHED" clause?

by **dick scherrer** » Tue Dec 15, 2009 3:17 am

Hello,

How can I make Easytrieve ignore the ones picked up in the "IF MATCHED" clause?

I believe that once 2 records "match" they are no longer considered for additional matching. Using the Easytrieve built-in MATCH, you might look into IF NOT filename or IF DUPLICATE filename.

You may need to look at a different approach (rather than using the Easytrieve MATCH). When i've need a 2-file match/merge (as this is), it was written in COBOL. Many of my clients use Easytrieve for reporting, but as they don't have deep expertise, they do not use it for multi-file processing.

by **ABRN170** » Tue Dec 15, 2009 10:44 am

Hi D,

Thanks...but I don't think even COBOL will be able to do the match/merge with my end file. I am sure there must be a way. I will give the IF NOT and IF DUPLICATE a shot and see if I am able to get it to work.

Because my actual file I need to update consists of 48 million records! And 24 million of 48 needs to be updated. Programatically and time allowed at our company this is not possible except to upload the file with DB2. Thus I need a solution to update the files and write a single output dataset to give to our dba's in order for them to upload. They said its possible with easytrieve but I did not expect it to be this hard.

Other questions are:

1) Do i need to sort the file in a difference way?
2) Do I need to expand my Keys i use in the Job Input?
3) My extract containing the value that needs to be updated in the big file....must I expand its columns as well...more fields maybe?

I am open for suggestions as I am getting quite frustrated with this

and I really really really really need this to work.

My Easytrieve JCL still looks as the one in my first post.

Thanks for the help sofar....really appreciated.

by **ABRN170** » Tue Dec 15, 2009 10:48 am

My JCL looks the same except for MOD part. Lol.
*
Tx.

by **dick scherrer** » Wed Dec 16, 2009 12:27 am

Hello,

but I don't think even COBOL will be able to do the match/merge with my end file.

It will unless i completely misunderstand the requirement. . .

If you need to match on both REF-NO and DOC-SEQ they both need to be used to MATCH. . .

It may help to keep in mind that this is a fairly straight-forward requirement to match 2 files by keys and generate output based on the comparison. . . This is not some "wild and crazy" unique requirement - again, unless i completely misunderstand. . .

by **Shri** » Wed Dec 16, 2009 10:20 pm

Hi,
As far as I know,
1) Both the files must be sorted (ascending) in key sequence: Ref no and Doc Seq in this case.
2) FILEB in ur case must have no duplicates at the keys level. (u could use xsum in a prev sort step to do this)
3) Use FILEB as ur finder file (meaning u give it first, then Filea) in ur match code:
Simply,
JOB INPUT (FILEB KEY(B-REF_NO , B-DOC) +
FILEA KEY(A-REF_NO, A-DOC))

IF MATCHED
...
If client_a ne client_b
whatever u need..
else
..
end-if.

Should work.

Shri

IBM Mainframe Forum

2 files. Update 1 from the other. Write new dataset

2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset

Re: 2 files. Update 1 from the other. Write new dataset