MVSFORUMS.com

bade_miya · Beginner Joined: 10 Dec 2003 Posts: 110 Topics: 38

hi all,
i am using a cursor to select some records. this is the querry.

bade_miya · Beginner Joined: 10 Dec 2003 Posts: 110 Topics: 38

hi ravi,
ya as you said what i ment was that, the querry was taking lot of time(more time than expected). i am still working on the problem and figured out that the problem was due to the C.ACCT_NUM LIKE '1100%' statement. i am trying to replace it with other statement and trying to run it. suggestions are invited.
thanks

kolusu · Posted: Thu Mar 11, 2004 6:35 am Post subject:

Bade_miya,

As you already guessed it is your like statement that is going for tablespace scan.
is ACCT_NUM a primary key on the table D2TFCEA.ACCT_ARR? What is the definition of the the column acct_num.

Try to run explain on the sql . check this link for explanation of running explain in spufui.

http://www.mvsforums.com/helpboards/viewtopic.php?t=215&highlight=explain

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

bade_miya · Beginner Joined: 10 Dec 2003 Posts: 110 Topics: 38

hi kolsu,
thanks. it worked. i replaced the like statement some other statements. initially it was "LIKE 1100%" . NOW I CHANGED IT TO
COMP_CDE='11'
AND CTRY_CDE='00'.
THANKS FOR THE LINK ON EXPLAIN.
BYE FOR NOW.
JAISON.

Edited by moderator to collapse the double post.

hi kolusu,
even if the querry worked fine, i still have one doubt left. when i ran the wrong querry in spufi , it took lot of time. but when i ran the program using xpediter, the open cursor logic was successfully passed with a sql code of 100. and it got stuck only in the first fetch statement. according to my understanding abt cursors, a resultant table(ie the result of the querry) will be there after we open a cursor. and during fetch it access one row at a time. then how come it didnt get stuck in the open cursor logic when i was running the program in xpediter.
thanks in advance.

kolusu · Posted: Fri Mar 12, 2004 8:31 am Post subject:

Bade_miya,

Please do not open more than 1 topic for the same question. I have deleted your other post " still got a doubt". Post all your addtitional questions related to the orginal post in the orginal post itself.

Ok Now something to be looked upon. you said when you open the cursor you got an sqlcode of 100, but a successful open of cursor should give you an sqlcode of 0. so your cursor had an empty resultant table. So check if all the variables in the host varaibles of the where clause had the approriate values when opening the cursor.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

bade_miya · Beginner Joined: 10 Dec 2003 Posts: 110 Topics: 38

hi kolsu,
sorry it was a mistake Smile

. what i actually ment to say was that it passed successfully with an sqlcode of 0. sorry for the confusion caused by that mistake.
yes all the variables in the host variables of the where clause had the appropriate values while opening the cursor, because i went to db2 file aid and checked it. it retrieved some 3 rows.
thanx.