To login to marge01us.thomsonpatentstore.net (63.84.162.201) or marge01eu.thomsonpatentstore.net (84.18.161.152 or 10.225.52.220) from jasper, /usr/local/bin/ssh -l rjasper marge01us.thomsonpatentstore.net or /usr/local/bin/ssh -l rjasper marge01eu.thomsonpatentstore.net or ssh -l rjasper wiladb2.intranet.wila ????????????????????????????????????????????????????????????? ? The ssh in AFS's /local/bin, doesn't work. It gets ? ? ? ? warning: Authentication failed. ? ? Disconnected; key exchange or algorithm negotiation ? ? failed (Key exchange failed.). ? ????????????????????????????????????????????????????????????? The password is my current one as of 8-13-2004. =============================================================================== You can ssh/scp from SBY to marge01eu.ipr-village.info (84.18.161.152) =============================================================================== Joachim Schneider's home phone number is 9-011-49-89-693-87246 his office phone number is 9-011-49-89-547-56221 =============================================================================== root password for marge01us = 9PhylaX You cannot log in as root directly. You must ssh as rjasper, then su - Another TPS password is tmt1Wtd! for the develop userid on the marges, and also for the tpsadmin userid on the F5. (tmt1Wtd! stands for "There's more than one way to do it", from the Perl book) See my TPS_Debugging file in this directory. =============================================================================== To connect to their database, use your db alias or to do it manually, . ~db2inst1/sqllib/db2profile db2 connect to scan raid.pdf => Their new main table, replacing their raid.done table db2 "select cc,count(*) from raid.pdf group by cc" CC COUNT -- --------- DE 3,073,241 (Germany) EP 2,205,925 JP 7,161,155 TW 210 (Taiwan) US 8,189,351 WO 1,185,349 raid.done => Their main table (equivalent to Delphion' imag table) raid.conc => The EP-to-WO equivalence per eSpaceNet raid.espace => The eSpaceNet "imag" table (CC, PN, DT, URL) 42 million total images from 91 different countries/authorities, the 8 with more than one million images are (counts are in the millions) are, JP (15), US (8), DE (4), EP/FR/GB (2 each), SU/EP (1 each) Note that in this table, PN=VARCHAR(10) and DT=VARCHAR(2) !!! raid.ext => Their copy of Delphion's imag table ------------------------------------------------------------------------------- db2 describe table raid.done Column Name Type (and my notes) name Length Scale Nulls ------------------------------ ----------- ------ ----- ----- CC (Country Code) CHARACTER 2 0 No PN (Notice all Numeric!) BIGINT 8 0 No DT (Their extended kind) VARCHAR 6 0 No PAGES SMALLINT 2 0 No ZEIT (Insert Timestamp) TIMESTAMP 10 0 No BIBL (Page Start and SMALLINT 2 0 No BIBL2 End Data) SMALLINT 2 0 No CLAIM SMALLINT 2 0 No CLAIM2 SMALLINT 2 0 No DRAWING SMALLINT 2 0 No DRAWING2 SMALLINT 2 0 No AMEND SMALLINT 2 0 No AMEND2 SMALLINT 2 0 No DESCR SMALLINT 2 0 No DESCR2 SMALLINT 2 0 No ABSTR SMALLINT 2 0 No ABSTR2 SMALLINT 2 0 No SR SMALLINT 2 0 No SR2 SMALLINT 2 0 No VART (CD Label) VARCHAR 5 0 No VNR (CD Number) INTEGER 4 0 No GR (PDF File Size before optimization, INTEGER 4 0 No so therefore, is useless) GROPT (Optimized PDF File Size) INTEGER 4 0 Yes AFORMAT (Original Image Format) CHARACTER 1 0 No LANG CHARACTER 2 0 Yes - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Once I was looking for an instance of an EP A1 patent NOT existing, but some other extended kind A1 image existing. db2 "select a.cc,a.pn from raid.done a where cc='EP' and dt like '%A1' and not exists (select pn from raid.done b where b.cc=a.cc and b.pn=a.pn and b.dt='A1')" showed me only one, EP_1271601 db2 "select * from raid.done where cc='EP' and pn=1271601" CC PN DT PAGES ZEIT (timestamp) VART VNR AFORMAT -- ---------- ------ ----- ---------------- ---- ------- ------- EP 1271601 A2 7 2003-01-13 MEPA 2003003 P EP 1271601 A8W1A1 1 2003-04-04 MEPA 2003026 P 2 record(s) selected. I didn't find any EP*A2's or EP*A3's or EP*B1's or EP*B2's in this state. I found 51 WO*A1's, including WO09916033 & WO03078011 CC PN DT PAGES ZEIT (timestamp) VART VNR AFORMAT -- ---------- ------ ----- ---------------- ---- ------- ------- WO 1990016033 A2 80 2001-12-15 WLD 1990031 B WO 1990016033 R5A1 2 2004-04-28 MIWO 2004021 T WO 2003078011 A2 40 2003-10-06 MIWO 2003046 T WO 2003078011 R6A1 39 2004-09-07 IB 2004054 T and 82 WO*A2's, including WO01032477 & WO04054199 CC PN DT PAGES ZEIT (timestamp) VART VNR AFORMAT -- ---------- ------ ----- ---------------- ---- ------- ------- WO 2001032477 A1 33 2001-10-30 MIWO 2001019 T WO 2001032477 R5A2 2 2001-10-30 MIWO 2001023 T WO 2001032477 R8A2 3 2001-12-13 MIWO 2001049 T WO 2004054199 A1 63 2004-06-30 MIWO 2004039 T WO 2004054199 R6A2 59 2004-08-25 MIWO 2004051 T and 108,782 WO*A3's, including WO08904114 & WO04077769 CC PN DT PAGES ZEIT (timestamp) VART VNR AFORMAT -- ---------- ------ ----- ---------------- ---- ------- ------- WO 1989004114 A2 40 2001-12-15 WLD 1989009 B WO 1989004114 R4A3 6 2004-04-28 MIWO 2004021 T WO 2004077769 A2 16 2004-09-14 IB 2004055 T WO 2004077769 R4A3 4 2004-10-06 IB 2004058 T =============================================================================== Another useful table is RAID.CONC (CONC stands for Concordance), which gives the EP-to-WO equivalence. db2 describe table raid.conc Column Name Type Name Length Nulls ----------------- ---------- ------ ----- CC CHARACTER 2 No PN BIGINT 8 No DT VARCHAR 6 No LANG CHARACTER 2 Yes CC2 CHARACTER 2 No PN2 BIGINT 8 No DT2 VARCHAR 6 No LANG2 CHARACTER 2 Yes ART SMALLINT 2 No For example, db2 "select * from raid.conc where cc='EP' and pn=1332654" CC PN DT LANG CC2 PN2 DT2 LANG2 ART -- -------- ------ ---- --- ---------- --- ----- --- EP 1332654 A2 - WO 2002039802 A2 EN 21 1 record(s) selected. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - This table has all cc='EP' (except for 2 test rows) and mostly A1 types. db2 "select dt,count(dt) from raid.conc group by dt order by dt" DT Count ------ ------- A0 64 A1 374,158 A2 63,911 =============================================================================== db2 "describe table raid.ext" Column Name Type Name Length Nulls ----------------- ---------- ------ ----- CC CHARACTER 2 No PN BIGINT 8 No DTO VARCHAR 6 No PAGES SMALLINT 2 Yes SO SMALLINT 2 No PNO BIGINT 8 No ED DATE 4 No VOL VARCHAR 11 Yes This is suppose to be their copy of Delphion's imag table. I sent them a refresh of this table on 11-10-2004 using Eric's program on rhino, /home/inst1/z_import/eric/export_imag_wila2.sql ftp-ing the load files by ftp ftp.ipr-village.com using delphion and _DIP as the userid & password, then cd DRSIMAG but I digress. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I wanted to find an instance of when TPS would have to call Delphion to satisfy a US image request. Out of 4,482,246 total rows on 12-28-2004, 4,029,224 were US, 374,950 were GB, 57,885 were FI, and 20,185 were CH (Swiss). I'm guessing the WebServicesAvailable & charon interfaces check in order, * raid.done * raid.espace * raid.ext and the raid.ext table has encoded US non-utility patents in it, since the max(pn)=90500185 (representing USD0500185__) so I want something like db2 "select e.cc,e.pn,e.dt from raid.ext e where e.cc='US' and e.dto='A' and e.pn<7000000 and not exists (select d.pn from raid.done d where d.cc='US' and d.pn=e.pn and d.dt='A1') and not exists (select s.pn from raid.espace s where s.cc='US' and s.dt in ('A','A1') and s.pn < '7000000' and s.pn <> '1400H' and (CAST (ltrim(rtrim(s.pn)) AS INTEGER))=e.pn)" =============================================================================== db2 "describe table raid.espace" Column Name Type Name Length Nulls ----------------- ---------- ------ ----- CC CHARACTER 2 No PN VARCHAR 10 No DT VARCHAR 2 Yes URL CHARACTER 17 No Some interesting aspects of this table are * the PN column is a VARCHAR instead of BIGINT like the other tables are. This means to do compares, you need to CAST(pn AS INTEGER). * the Certificate of Corrections are listed separately, as illustrated here, db2 "select * from raid.espace where cc='US' and pn='6000001'" CC PN DT URL -- ---------- -- ----------------- US 6000001 A1 US 6000001A1 I US 6000001 X6 US 6000001X6 I * Here are the counts for US patents in raid.espace with leading PN characters, 304 AI 3,668 B 52 BD 73 BRE 650 C 22 CD 9 CRE 1 CP 446,501 D 1,856 H 14,429 PP 6 P 36,778 RE 2 R 87 RD 3 RP 57 RX 1,267 T 2,004 X =============================================================================== When TPS comes to the Delphion site to get an image, it uses http://www5.delphion.com/cgi-bin/vdnload.com/US00999657__.pdf and appears to come from 63.84.162.201 = marge01us in Ann Arbor, Michigan or 84.18.161.152 = marge01eu.thomsonpatentstore.net in Limerick, Ireland ===============================================================================