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



Unicenter CA-Easytrieve Plus Report Generator: CA's information retrieval and data management tool

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

Postby 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:

/=/=/=/=/=/=/=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=//=/=/=/==/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=
//*                                                                     
//**********************************************************************
//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.
ABRN170
 
Posts: 5
Joined: Thu Dec 03, 2009 2:46 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby 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.
ABRN170
 
Posts: 5
Joined: Thu Dec 03, 2009 2:46 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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


----+----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

----+----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?
ABRN170
 
Posts: 5
Joined: Thu Dec 03, 2009 2:46 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby 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.
ABRN170
 
Posts: 5
Joined: Thu Dec 03, 2009 2:46 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby ABRN170 » Tue Dec 15, 2009 10:48 am

My JCL looks the same except for MOD part. Lol.
*
Tx.
ABRN170
 
Posts: 5
Joined: Thu Dec 03, 2009 2:46 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby 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
Shri
 
Posts: 1
Joined: Wed Dec 16, 2009 9:46 pm
Has thanked: 0 time
Been thanked: 0 time


Return to CA-Easytrieve

 


  • Related topics
    Replies
    Views
    Last post