Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Glitch in Select query.....

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Tue Dec 01, 2009 11:45 am    Post subject: Glitch in Select query.....
Reply with quote

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.
Back to top
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Tue Dec 01, 2009 12:14 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Tue Dec 01, 2009 12:27 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Dec 01, 2009 12:58 pm    Post subject:
Reply with quote

an easy test to make would be construct a WHERE clause that would force a table scan.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Tue Dec 01, 2009 2:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Dec 01, 2009 2:35 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
notonly4u

New User


Joined: 26 Apr 2005
Posts: 87
Location: Hyderabad

PostPosted: Tue Dec 01, 2009 2:39 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Tue Dec 01, 2009 2:57 pm    Post subject:
Reply with quote

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 icon_confused.gif 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.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Dec 01, 2009 5:47 pm    Post subject:
Reply with quote

pkmurali,

Quote:
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.


statistics may not be updated.

Show us the records from the table using qmf or spufi.

Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Dec 01, 2009 9:01 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Dec 01, 2009 9:21 pm    Post subject:
Reply with quote

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

How is empid defined?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 02, 2009 4:11 am    Post subject:
Reply with quote

Hello,

Quote:
The SQLCODE returned for the above sql is +100. But the data is available in the table which i can browse through file-aid.
Is the +100 happening in spufi? Did you see a message like:
Code:
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
?
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Wed Dec 02, 2009 12:38 pm    Post subject:
Reply with quote

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.


Code:

select count(*) from a1jnbyn.tbname;

------------------
            6102

1 record(s) selected.


sel    owner         Name            T   DB Name   TS Name  Cols        Rows     Che   
       A1JNBYN       TWC1C00         T   DWC1T003  RWC1C00    97        44587104


The Problem got resolved by rebuilding the index,before rebuilding the index they identified that column has duplicate entries and after removing the duplicates they created the unique index for column empid.Now i can get the result for my select query.

But what gives the solution is not clear. By identifying the duplicates, and deleting the duplicates and creating unique index would done the job??
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Dec 02, 2009 8:41 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us