title: compare a postgres and db2 table
body: <p>normally i would solve this with a simple union.  but this one has me.  i have a table replicated to two different database engines.  one copy in postgres, one copy on an AS/400 with DB2. </p>
  
  <p>i need to come up with a routine that takes the postgres table, and compares the data in the fields of each record with the DB2 table and log the different records in a postgres table for reporting.  </p>
  
  <p>the current system follows this model</p>
  
  <pre><code>    load postgres data into recordset prs
  for each record in prs {
    search for record in db2
    compare record
    if diff, log
  }
  </code></pre>
  
  <p>this normally works.  the problem, seeing there are about 500,000 records it takes about 3 hours before it triggers a bug in our VPN that kills the connection.  and of course you have to start over and it will be quicker to redesign the routine than to fix the VPN bug.</p>
  
  <p>i was thinking of a routine that brings the db2 data over in a temporary table and doing a union query then, but i run into security issues with creating and dropping the table.  </p>
  
  <p>the most realistic method i can think of is loading the db2 data as a recordset, then loading it into a hashmap with the key being the db id number, and then looping the postgres data but that could really slam my memory resources.  but its still more of a brute force technique.  is there a more elegant solution you guys can think of?</p>
  
  <p>edit--
  sorry, the table starts the year with 3.5 million records so the in memory solution becomes pretty impractical.  any ideas?</p>