Nothing returned by query of indexed field

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Nothing returned by query of indexed field

Post by barry.marcus »

We maintain two databases that have the same data. (Well, more or less. It is US patent data and should be the through 2006 at least.) A query that I run on server "A" returns 4 rows. When I run the same query on server "B" I get zero rows.

Here is the query:

SELECT PATN_WKU
FROM PATN
WHERE PATN_TTL\ABST_PAx\CLMS_PAx
LIKE '+plurality +apparatus @0 segment w/4'
AND PATN_ISD > '20060801'
AND PATN_ISD < '20060831';

All of the data returned by the query from the PATN table on server A are also in the PATN table on server B. My best guess is that there is something wrong with the index. Here are the commands used to create the index on both servers:

DROP INDEX PATN_ABST_CLMS_MI;
set delexp=0;
set addexp='\alnum{1,99}';
set keepnoise=1;
set indexmeter=1;
create METAMORPH INVERTED index PATN_ABST_CLMS_MI on PATN(PATN_TTL\ABST_PAx\CLMS_PAx,PATN_ISD);

Of significance, I suspect, is that several errors (about ten, actually) were returned when the index on server B was building. They were all returned at the end of the process, and they all looked like the following:

100 Error in data, file /data3/texisdb/camp/PATN.tbl, offset 0x1C9E27B28A
005 Bad size 0 for column DETD_PAx at offset 0xD10 in recid 0x1D439D4CA8 of /data3/texisdb/camp/PATN.tbl in the function fbuftofld

Anyway, not sure how to go about troubleshooting this issue. Thanks again for the help.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Nothing returned by query of indexed field

Post by mark »

Use kdbfchk to repair the table on server B. Or copy the clean table from server A. Then drop and rebuild all indices on the table.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Nothing returned by query of indexed field

Post by barry.marcus »

I ran the kdbfchk on PATN.tbl and on PATN_ABST_CLMS_MI.dat. It found no problems in either case, which seems strange considering that there were errors reported during the creation of the index. SHould I run kdbfchk against the other files involved in the query?

I will look into copying the data fromserver A to B, although considering how long it'll take to recreate all of the indexes (the data file is 130GB and server B is not the quickest machine around, to say the least!), I'd prefer to first try fixing things "in place" if possible.

Thanks.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Nothing returned by query of indexed field

Post by mark »

Apparently the overall table structure is intact but there's a problem within that one record. Use kdbfchk -p 0x1D439D4CA8 to dump that record. Use kdbfchk -d to delete the record. Then reinsert the record from source or from an export from the other machine.
Post Reply