Comparing two records

[Huddy]

Hi,

Is there a nice quick and easy way of comparing two records which identifies a change. Basically I'm checking to see if a file has changed on a day-to-day basis so i'm taking the same file but from two different libraries using the OVRDBF in the CLP. Our old RPGIII version just chains and compares each field but wondered if there was a new alternative.

Thanks

[Huddy]

Re: Comparing two records

the only thing i trie was this i found on these forums..

FBRT03L1   IF   E           K DISK    rename(b00fmt:filefmt1) 
FBMF00P    IF   e           K DISK    rename(b00fmt:filefmt2) 

d  file1DS      e ds                  extname(brt03l1) Qualified        
d  file2DS        ds                  likeds(file1ds)      

/free            

 setll *start brt03l1;                        
[COLOR="#FF0000"] read filefmt1 file1DS;   [/COLOR]                    
                                              
 dow not %eof;                                
                                              
[COLOR="#FF0000"] chain (b00brc:b00clt) filefmt2 File2Ds[/COLOR];      
 if %found;                                   
  if file1DS <> File2DS;                      
    b00its = 1;                               
  endif;                                      
 else;                                        
    b00its = 0;                               
 endif;                                       
                                              
[COLOR="#FF0000"] read filefmt1 file1DS;  [/COLOR]                     
 enddo;                                       
                                              
 *inlr = *on;                                 
 return;

but it doesn't compile.
*RNF7701 20 3 The data structure is not allowed for the operation.
Data structure FILE1DS is not allowed for the operation.

thanks

[Scott M]

Re: Comparing two records

How about telling us which line/operation gets the compile error.

[tomliotta]

Re: Comparing two records

You could XOR the two record images together. The offset of any non-null byte in the result would fall within the start and end positions of some field in the field list. That would tell you that that field had changed.

Personally, I'd think an appropriate journal seems a better solution.

[Huddy]

Re: Comparing two records

How about telling us which line/operation gets the compile error.

Lines in red.

I got the code from this post:

http://www.code400.com/forum/showthread.php/9711-Compare-record-of-two-files?p=55290&viewfull=1#post55290

edit -
I've just had a look and the red lines don't make sense to me.. I've removed the ds from the lines and it compiles but nothing gets moved in to the DS's.??

[Huddy]

Re: Comparing two records

You could XOR the two record images together. The offset of any non-null byte in the result would fall within the start and end positions of some field in the field list. That would tell you that that field had changed.

Can you expand on this please. Any examples? thanks

This is for report which shows changes to our client file during the day for audit. Unfortunately, Due to compliance restrictions, we can not use Journalling for this solution.

[GLS400]

Re: Comparing two records

Hi Huddy:

This should work for you:

hdftactgrp(*no)                                                 
fMYFILE    if   e           k disk    rename(pol : pl)       
fMYFILE2   if   e           k disk    extDESC('TFC36/MYFILE  ') 
f                                     extfile('TFC36/MYFILE  ') 
f                                     rename(pol : pl2)      
dply              ds                  likerec(pl)               
dply2             ds                  likerec(pl2)              
d x               s             10i 0                           
 /free                                                          
                                                                
  setll *start MYFILE   ;                                       
                                                                
   dow not %eof;                                                
      read MYFILE   ply ;                                       
      chain (PLY.KEY1  : PLY.KEY2  ) MYFILE2 PLY2 ;             
      IF %FOUND(MYFILE2) AND PLY <> PLY2 ;                     
        b00its = 1;                                
     ENDIF  ;                                
  enddo;                                     
                                             
      *inlr = *on ;                          
/end-free

Best of Luck
GLS

[MichaelCatalani]

Re: Comparing two records

The reason for your error is that you have to define the data structure with a LIKEREC( RcdFmt : *INPUT ) when you read / chain into a datastructure. This is one of those things that is slightly annoying (instead of using just a LikeDS), but it is what it is.

[Scott Klement]

Re: Comparing two records

The reason for your error is that you have to define the data structure with a LIKEREC( RcdFmt : *INPUT ) when you read / chain into a datastructure. This is one of those things that is slightly annoying (instead of using just a LikeDS), but it is what it is.

You don't have to use LIKEREC, you can use EXTNAME if you prefer. But, you do need to specify *INPUT or *OUTPUT. (This is why Huddy's original example didn't work.)

[tomliotta]

Re: Comparing two records

Can you expand on this please. Any examples?

You'd still want the DSs declared correctly, but a XOR operation over the two DSs would provide markers for each byte that changed between the two record images. It's not so much an improvement on simply comparing the two DSs byte by byte as it is an "alternative" (which is what you asked for).

By calling the List Fields (QUSLFLD) API, you can get an array of field names and the position of fields in I/O buffers. The DS positions should match the input buffer; so when you find a mismatch at an offset into the DS, you can check the field array to see the name of the field at that offset.

This is for report which shows changes to our client file during the day for audit.

If it really is an audit function, then a journal operation is even more important (possibly necessary). Simple database values have no assurances of being reliable. UPDDTA changes could eliminate "audit" reliability without anyone knowing. Changing journal entries is a much more difficult task.

[Huddy]

Re: Comparing two records

Hi Huddy:

This should work for you:

hdftactgrp(*no)                                                 
fMYFILE    if   e           k disk    rename(pol : pl)       
fMYFILE2   if   e           k disk    extDESC('TFC36/MYFILE  ') 
f                                     extfile('TFC36/MYFILE  ') 
f                                     rename(pol : pl2)      
dply              ds                  likerec(pl)               
dply2             ds                  likerec(pl2)              
d x               s             10i 0                           
 /free                                                          
                                                                
  setll *start MYFILE   ;                                       
                                                                
   dow not %eof;                                                
      read MYFILE   ply ;                                       
      chain (PLY.KEY1  : PLY.KEY2  ) MYFILE2 PLY2 ;             
      IF %FOUND(MYFILE2) AND PLY <> PLY2 ;                     
        b00its = 1;                                
     ENDIF  ;                                
  enddo;                                     
                                             
      *inlr = *on ;                          
/end-free

Best of Luck
GLS

Perfect example and crystal clear.. Worked first time.. Have some reps points


Thanks to you and everyone that replied. It's much appreciated.

[B.Hauser]

Re: Comparing two records

You only want to know, if 2 tables containing the same records, i.e. all row and column values are identical?
If so, just execute the following SQL Statement:

    (    Select * From Schema1/YourTable
    Except
         Select * from Schema2/YourTable)
Union
    (    Select * from Schema2/YourTable
     Except 
         Select * from Schema1/YourTable)

The first except clause (within the first parenthesis) returns all rows that are either not or different (all column values) in the second table.
The second except clause returns all rows that are either not or different in the first table.
The union clause merges the result of the both except clauses.

Birgitta

[ChrisL]

Re: Comparing two records

If it really is an audit function, then a journal operation is even more important (possibly necessary). Simple database values have no assurances of being reliable. UPDDTA changes could eliminate "audit" reliability without anyone knowing. Changing journal entries is a much more difficult task.

I'm with Tom on this, journal the file and process the journal entries, will give you a much better level of detail, switch on before images and you'll be able to see exactly who changed what and when.

Another option would be to add a trigger to the file that then writes to an Audit log, which will effectively give you the same as your journal entries.

[Huddy]

Re: Comparing two records

I agree and I have thought about this further so I'd like to give it a try and convince the team.

So my question is, how do i go about reading the journal for a specific file and report the neccesasary level of detail.

Date of change
time
field and field description
value.


thanks

[tomliotta]

Re: Comparing two records

Another option would be to add a trigger to the file that then writes to an Audit log, which will effectively give you the same as your journal entries.

Well, except that the "Audit log" suffers from the same problem as monitoring the source data file. The "log" is still subject to easy manipulation.