OOO L DD PP RR OOO CCC EEEE SSS SSS O O L D D P P R R O O C E S S O O L D D PP RR O O C EE SSS SSS O O L D D P R R O O C E S S OOO LLLL DD P R R OOO CCC EEEE SSS SSS ================================================================================================ ================================================================================================ Japan's imag table needs to be kept in synch with Southbury. To do this manually, follow these 4 steps. It should take maybe 5 minutes total. ----------------------------------------------------------------------------------------------- 0) To check the status of Southbury's imag table against Patolis's, run and compare these two queries. For US Granted images, In Southbury, set -o vi db2 connect to patent db2 "select isd,count(m.patn) as count from main m where patn like 'US%' and \ datasrc='USG' and year(isd)=2002 and \ exists (select i.patn from imag i where i.patn=m.patn) \ group by isd order by isd" In Patolis, db2 connect to patent db2 "select isd,count(m.patn) as count from inst1.main m where patn like 'US0%' and \ year(isd)=2002 and exists (select i.patn from inst1.imag i where i.patn=m.patn) \ group by isd order by isd" (This query may not show the latest week's worth of data if the bibliographic data has not been loaded into Patolis's DB/2 yet. To see if any of these exist, you can try db2 "select count(*) from imag i where patn like 'US0%' and \ not exists (select m.patn from main m where m.patn=i.patn)" W A R N I N G !!! This query takes about 14 minutes to run!!! ) For US Application images, In Southbury, db2 connect to patent db2 "select isd,count(m.patn) as count from main m where patn like 'US22%' and \ datasrc='UPA' and exists (select i.patn from imag i where i.patn=m.patn) \ group by isd order by isd" In Patolis, db2 connect to patent db2 "select isd,count(m.patn) as count from inst1.main m where patn like 'US22%' and \ exists (select i.patn from inst1.imag i where i.patn=m.patn) group by isd order by isd" (This query may not show the latest week's worth of data if the bibliographic data has not been loaded into Patolis's DB/2 yet. To see if any of these exist, you can try db2 "select count(*) from imag i where patn like 'US22%' and \ not exists (select m.patn from main m where m.patn=i.patn)" when I ran this on 4-15-2002, there were 5 such US22 patents (11 US21's). ----------------------------------------------------------------------------------------------- 1) Collect the newly-added information from a Southbury database (or replica). From jasper@jasper, ssh -l inst1 dephds059 (or you can tn to trantor and login as inst1) set -o vi db2 connect to patent rm imag For US Granted images, NO db2 "select patn,image_pages from imag where \ NO patn in (select patn from main where datasrc='USG' and isd>'2002-10-15')">imag Modify the line above to increment this Tuesday date -----> isd>'2002-xx-xx') Rick, the above query doesn't capture the US..X6 images where there is no entry in main. How about changing the query to perhaps use the insert_ts column of imag, ala db2 "select patn,image_pages from imag where \ patn like 'US%' and insert_ts>'2002-10-29-01.01.01'" > imag Modify the date in the above line--------------/\ /\ For US Application images, <==== This is now incorporated into the weekly US Apps process. db2 "select patn,image_pages from imag where \ patn in (select patn from main where datasrc='UPA' and isd='2002-06-06')">>imag ----------------------------------------------------------------------------------------------- 2) Then modify the imag file to conform to a DB/2 loadable input file. You'll be changing PATN IMAGE_PAGES ------------ ----------- US06334221__ 9 USD0452738__ 5 USRE037497__ 10 USPP012337__ 4 USD0452907__ 8 USPP012331__ 5 ... 4326 record(s) selected. to US06334221__|9|IPN-SBY US0D452738__|5|IPN-SBY US0RE37497__|10|IPN-SBY US0PP12337__|4|IPN-SBY US0D452907__|8|IPN-SBY US0PP12331__|5|IPN-SBY ... Notice the patent number switch from USD0 to US0D USH0 to US0H USRE0 to US0RE USPP0 to US0PP USHD0 to US0HD and USHP0 to US0HP We also choose to not give any UST or USX patents. All of these lines below can be swiped to accomplish the editing required. Just swipe them all and go. vi imag 3ddGdd..:1 :%s/^USD0/US0D/g :1 :%s/^USH0/US0H/g :1 :%s/^USRE0/US0RE/g :1 :%s/^USPP0/US0PP/g :1 :%s/^USHD0/US0HD/g :1 :%s/^USHP0/US0HP/g :1 :%s/ */|/ :1 :%s/$/|IPN-SBY/ :1 :g/^US[TX]/d :g/^US0[789]/d :x wc -l imag That last line is just to do a sanity check on the number of images you've got. It should be on the order of 2-3 thousand per week. ----------------------------------------------------------------------------------------------- 3) Now we ship that file over to Patolis. If you're on dephds059, first get the file over to Delphion, San Jose scp1 -p imag inst1@ips06i.ips4db2.com:/dfs/download/japio.imag.update.data or scp1 -p imag inst1@210.145.31.109:/dfs/download/japio.imag.update.data ----------------------------------------------------------------------------------------------- 4) Now login to Patolis and load the data, ssh ips06i tn ips03i Login as inst1, and db2 connect to patent db2 -cvtf imag.update.load.sql That imag.update.load.sql file contains the single line import from /dfs/download/japio.imag.update.data of del modified by coldel| chardel& \ commitcount 1000 insert into inst1.imag (patn,image_pages,cdlabel) ; You may get tons of error messages, all likely due to trying to load data that is already in the imag table. This is ok.