Problem with index or data or both - mystery

barry.marcus · Post by **barry.marcus** » Wed Jul 22, 2009 2:41 pm

This is what I get:

$
id: 486e530f0$
{+ PATN_WKU+ }: {7301016}$
$

Post by **mark** » Wed Jul 22, 2009 2:49 pm

Odd. Does it find it this way:
select PATN_WKU FROM PATN WHERE PATN_WKU like '7301016';
or this way:
select PATN_WKU FROM PATN WHERE PATN_WKU like '/7301016';

What kind of index do you have on PATN_WKU?

barry.marcus · Post by **barry.marcus** » Wed Jul 22, 2009 3:10 pm

We have a regular index on PATN_WKU, created with this command:

CREATE INDEX PATN_WKU_I on PATN(PATN_WKU);

I've tried to run the first of those two queries before, but I was never patient enough to wait for the return

(I guess a query like that uses either a metamorph index or does a table scan, and there are about three million rows in the table.) I'm running the second query now (it's been running for about 10 minutes), but it has not returned yet.

Post by **mark** » Wed Jul 22, 2009 3:20 pm

Yes, those are linear queries in the absence of a metamorph index on the field.

When you recreated the PATN_WRU_I index did you drop it first?

barry.marcus · Post by **barry.marcus** » Wed Jul 22, 2009 3:21 pm

Yes, I dropped the index first.

Post by **mark** » Wed Jul 22, 2009 3:39 pm

If the database is not changing you can try dbverify

dbverify -v -i PATN_WRU_I

If you don't have dbverify you can create it as a symlink to monitor

cd INSTALLDIR/bin
ln -s monitor dbverify

barry.marcus · Post by **barry.marcus** » Wed Jul 22, 2009 5:05 pm

Please explain briefly how to use dbverify. I don't know what to do with it or what I expect to see from it.

Here's what I did: I ran it, and it returned

200 Looking at /opt/data1/texisdb/camp/PATN_WKU_I on PATN

and then basically showed nothing. From another command prompt I ran the other queries that I've been trying all day, and the dbverify window still showed nothing.

Also, is CTRL+C a proper way to exit from this?

Post by **mark** » Wed Jul 22, 2009 5:47 pm

Not much to it. Use dbverify -h for options help.
I gave the exact syntax you need above. You just have to wait for it to verify the files. It will report problems as it finds them and a summary at the end.
Example:

% dbverify -v -i xoptname
200 Looking at /usr/local/morph3/texis/testdb/xoptname on options
200 Counted 712 rows in options
200 Counted 712 rows in /usr/local/morph3/texis/testdb/xoptname
200 There were 0 differences
%

barry.marcus · Post by **barry.marcus** » Thu Jul 23, 2009 11:29 am

I ran dbverify on the index for PATN_WKU. Here is the output:

200 Looking at /opt/data1/texisdb/camp/PATN_WKU_I on PATN
200 Counted 2965710 rows in PATN
200 Counted 2965710 rows in /opt/data1/texisdb/camp/PATN_WKU_I
200 There were 0 differences

I also ran it on the metamorph index for PATN_TTL. Here is that output:

200 Looking at /opt/data1/texisdb/camp/PATN_PATN_TTL_MI on PATN
200 Reading new list from index PATN_PATN_TTL_MI
200 Reading delete list
200 Reading recids from table PATN
200 Comparing token recids to table

Is it significant that the verification of the PATN_WKU index explicitly showed 0 differences, while the verification of the PATN_TTL index did not?

Post by **John** » Thu Jul 23, 2009 11:58 am

No. The check on metamorph indexes does not explicitly report 0 differences.

Is PATN_WKU_I the only index on WKU? If you do the query with -V option to tsql it should show the query it will do and the index it will use, e.g.

tsql -V "select PATN_WKU FROM PATN WHERE PATN_WKU = '7301016';"