Query executes forever

Hi all,

This gets stuck:

SELECT LATCHSTATUS FROM
      DBO.POSITION WHERE MJID = 3199 FETCH FIRST 1 ROWS ONLY      

If I change the number of rows, it returns instantly (there are no results).

Why? What am I doing wrong?

Any help is appreciated
darrgyasAsked:
Who is Participating?
 
momi_sabagCommented:
FETCH FIRST 1 ROWS ONLY       implies OPTIMIZE FOR 1 ROW
this looks like some sort of a bug
i would try to run a reorg on the table and all the indexes and see if it solves it
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would adding the following help your query:
OPTIMIZE FOR 1 ROW
0
 
darrgyasAuthor Commented:
adding OPTIMIZE FOR 1 ROW did not change anything.

Can't run reorg - there's no scheduled downtime for the table at all, it is being continuously written to.

Anything else I can try?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as momi_sabag indicated, this may be some bug you hit, at least that is what I saw on some forums searching for this error/problem.
if you don't see any difference in the explain plan, try FIRST 2 ROWS if that gives the same issue.
can you add a ORDER BY to the query?
what version are you using?
0
 
darrgyasAuthor Commented:
As I stated in the question, changing the # of rows gets rid of the problem, the statement executed in under a second.

 ORDER BY does not change anything.
0
 
darrgyasAuthor Commented:
I am using DB2 Express-C 9.74
0
 
momi_sabagCommented:
then just change to fetch 2 rows
and in your code, only fetch one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.