Glitch in Select query.....

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Hi,

I have issued an select query with where clause like,

select * from tbname where empid =1234 with ur;

where empid is one of the index of the table.

The SQLCODE returned for the above sql is +100. But the data is available in the table which i can browse through file-aid.

when i asked what could be the reason for this anomaly? Our DBA said this is due to inconsistency in indexes. please explain more about/ chances of getting the inconsistency in indexes.

Thanks,
Murali.

Terry Heinze · Posted: Tue Dec 01, 2009 12:14 pm

I don't see how this has anything to do with the presence/absence of whatever "inconsistency of indexes" means. +100 means exactly what the manual says it means -- no rows met the WHERE clause criteria.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Terry,

What manual says is correct,but this is the first i saw these kind of abnormality. If it is not with 'inconsistency of index' then what could be the reason for it????????

Thanks,
Murali

dbzTHEdinosauer · Posted: Tue Dec 01, 2009 12:58 pm

an easy test to make would be construct a WHERE clause that would force a table scan.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Dick,

To add salt to the wound, the total count of rows displayed (in IBM DB2 Administration Tool (DB2 Catalog Front End)) is different from select count(*) from tbname.

dbzTHEdinosauer · Posted: Tue Dec 01, 2009 2:35 pm

if you have dba's telling you that there are inconsistancies in the indexes
READ: incompetent DBA's who can't maintain a database,
why would you think that anything associated with the table is accurate?

notonly4u · New User Joined: 26 Apr 2005 Posts: 87 Location: Hyderabad

Do you have same table in different DB2 sub systems?
You may be executing the Select query in one and file-aid in some other sub system.

Pls check

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Dick,

Since we are getting the sqlcode +100 for a test data in development region, which brought this anomaly, i posted the question to get what could be the scenario? why it happens, does codd's rule fails in db2

or these DBA's faults leading to failure. Simplhy to know why it happens.

Thanks notonly4u.

But the subsytem, qualifier, database name,table space,schema everything are the same.

sushanth bobby · Posted: Tue Dec 01, 2009 5:47 pm

pkmurali,

GuyC · Posted: Tue Dec 01, 2009 9:01 pm

if only I got a dime for every nitwit how claims he found a glitch in DB2 ...

Maybe you should start with
- making sure you're selecting from the same table
- verifying column types and actual values of what is in the table.
- then reread you sql 3 times

Craq Giegerich · Posted: Tue Dec 01, 2009 9:21 pm

"select * from tbname where empid =1234 with ur; "

How is empid defined?

dick scherrer · Posted: Wed Dec 02, 2009 4:11 am

Hello,

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

All,

The SQLCODE +100 is returned when i executed the query in SPUFI. Yes Dick that's the message i got when i executed in SPUFI. empid is index
column.

GuyC · Posted: Wed Dec 02, 2009 8:41 pm

I rechecked :
The only way to accomplish this is by repairing an index that is rebuild pending.
Which sounds like what you've done : trying to create a unique index on duplicate data, the build failed and you (or dba) force repaired it.

Of course you get unpredictable results.