In order to resynch Patolis's imag table with Southbury's imag table, follow this 3-step procedure I developed 11-13-2002. This will add imag entries for the right patn ranges, that are missing in Patolis. This will NOT correct imag entries, e.g. wrong page count. The whole process takes about 45 minutes. ============================================================================= Step 1) Export Patolis's imag table and load it into a temporary table in Southbury. In Patolis, as inst1 on ips03i, db2 "export to /dfs/download/patolis.imag.del of del \ select patn,image_pages from imag where cdlabel='IPN-SBY'" compress /dfs/download/patolis.imag.del Compressed 72 MB down to 17 MB. (3 minutes total) Just for interest, 5-28-2002 11-13-2002 --------- ---------- US: 3,064,753 3,304,942 WO: 66,539 66,539 EP: 763,962 763,962 ========= ========= 3,895,254 4,135,443 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - In Southbury, as inst1 on dephds059, cd Japio.US.Update scp1 -p inst1@ips06i.ips4db2.com:/dfs/download/patolis.imag.del.Z . This takes about 5 minutes to download at 55 kiloBytes/second. uncompress patolis.imag.del.Z db2 "drop table japioimag" db2 "create table japioimag (patn character(12) ,image_pages smallint)" db2 "import from patolis.imag.del of del commitcount 10000 insert into japioimag" This took 15 minutes to load when 3,895,254 rows, which is 264,000 rows/minute. This took 23 minutes to load when 4,135,443 rows, which is 180,000 rows/minute. rm patolis.imag.del To create the index, db2 "create index ji1 on japioimag(patn)" which took 6 minutes. ============================================================================= Step 2) Run the check_All_US_data.sh script to generate the imag load file we need to resynch Patolis with Southbury. See the script for the details of which we supply Patolis. check_All_US_data.sh (4 minutes) ============================================================================= Step 3) Follow the directions to get that load file out to Patolis. and load the new data. From inst1 on dephds059, scp1 -p imag.ld.Z inst1@ips06i.ips4db2.com:/dfs/download Clean up after yourself. rm imag.ld.Z db2 "drop table japioimag" (5 minutes) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Finally, from inst1 on Patolis, uncompress /dfs/download/imag.ld.Z db2 "import from /dfs/download/imag.ld of del modified by coldel| chardel0x01 \ commitcount 1000 insert into inst1.imag (PATN, SRH, ABS, DESC, AMEND, \ DRAWING, CLAIM, BIBLIO, IMAGE_PAGES, BIB_STATUS, CDLABEL)" rm /dfs/download/imag.ld (10 minutes) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - One hiccup with the db2 import command, is that you may get instances of SQL3148W A row from the input file was not inserted into the table. SQLCODE "-803" was returned. SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "5" constrains table "INST1.IMAG" from having duplicate rows for those columns. SQLSTATE=23505 SQL3185W The previous error occurred while processing data from row "2443" of the input file. messages. This is due to us picking up the duplicate entries in Southbury's imag table. For example, when I first did this on 11-13-2002, 43 out of 266,803 rows were rejected because the imag.ld file had 43 instances of duplicate entries for the same patent, e.g. EP00188975B2||||||||1||IPN-SBY EP00188975B2||||||||7||IPN-SBY Why? db2 "select patn,datasrc,namesrc,image_pages from imag where patn='EP00188975B2'" PATN DATASRC NAMESRC IMAGE_PAGES ------------ ------- ------------------------------ ----------- EP00188975B2 EPB mepb2001043 7 EP00188975B2 EPB mepb2002002 1 2 record(s) selected. cd /dfs/images/EP/75/89 ls -l *EP00188975B2* -r-xr-xr-x 1 cotting delphion 15522 Jan 14 2002 DUPLICATE.mepb2002002_3.EP00188975B2.pdf -r-xr-xr-x 1 bex delphion 52606 Oct 26 2001 EP00188975B2.pdf The DUPLICATE.mepb2002002 PDF file has one page in it, the non-duplicate, 7. In this case, the second image that came in was declared the duplicate, but the mepb2002002 entry was incorrectly put into the imag DB2 table. This is a flaw with our image loading process. The result of all this is, since the 1-page imag entry was processed first, the correct, 7-page entry was rejected and we wound up with the wrong page count in Patolis's imag table. I'm not sure how to easily handle this.