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
 

 

sqlerrd(3) not behaving normally

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

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 12:08 pm    Post subject: sqlerrd(3) not behaving normally
Reply with quote

Hi ,

I am just trying to move the sqlerrd(3) to some other variable with datatype as 9(9) and displaying it after opening and then fetching one cursor.Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places(after opening and after fetching also).

I thought there would have been some problem with display so directly checked the variable sqlerrd(3) if it's not = 0 then display "non zero record" else display 'zero records". it's going to else part only.

I know that sqlerrd(3) should give number of records processed after successful completion of the query.But not sure why it's not working.
some body plz let me know if any possible cause of the scenario.

thanks in advance
Back to top
View user's profile Send private message

Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Feb 11, 2010 12:49 pm    Post subject:
Reply with quote

check with the get dignostic and compare the results?
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 2:00 pm    Post subject: Reply to: sqlerrd(3) not behaving normally
Reply with quote

Hi Ketan ,

Thanks for the reply.Would plz let me know what do you exactly mean by comparing diagonostic results?
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Feb 11, 2010 2:03 pm    Post subject:
Reply with quote

I am asking to use the get dignostic and then move the desired variable and check what value is it displaying. Get dignostic is used for getting the number of rows been fetch. Are you using this in Multi row set ?
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 2:17 pm    Post subject: Reply to: sqlerrd(3) not behaving normally
Reply with quote

Yes I am using it for multi row fetch. Actually my cursor is only simple select statement and there is one constaraint if the number of rows fetch is more than some number then do not populate the records else if numebr of rows is less than or equal to the number then only populate.

So I just want to check the actual number rows fetched.
After successful Open or Fetch statement SQLERRD(3) should give the actual number of rows right!
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 2:23 pm    Post subject:
Reply with quote

Ketan Varhade wrote:
I am asking to use the get dignostic and then move the desired variable and check what value is it displaying. Get dignostic is used for getting the number of rows been fetch. Are you using this in Multi row set ?


Again would plz let me know the syntax to use get dioagnostic. This can also solve my problem a su said it would give number of rows fetched.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Feb 11, 2010 2:33 pm    Post subject:
Reply with quote

Code:
GET DIAGNOSTICS rcount = ROW_COUNT

use this rcount will have the number of rows
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 2:49 pm    Post subject:
Reply with quote

Ketan Varhade wrote:
Code:
GET DIAGNOSTICS rcount = ROW_COUNT

use this rcount will have the number of rows


so ican use it in this way:
exec sql
GET DIAGNOSTICS :rcount = ROW_COUNT
end-exec

where rcount is host variable.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Feb 11, 2010 7:02 pm    Post subject:
Reply with quote

Shaktiprasad,

Quote:
after opening and then fetching one cursor.

If you use
Code:
DISPLAY 'SQLERRD(3) = 'SQLERRD(3) 

after opening the cursor, you will get 0. Because, rows are fetched only after FETCH statement is executed.

Can you show us your code. That would be a lot easier to help.

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

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Thu Feb 11, 2010 8:20 pm    Post subject:
Reply with quote

Quote:
Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places


How are you so sure that the query is returnig rows? did you execute the query in spufi/qmf? I would suggest you to display SQLERRD(3) after the successful fetch. If it still shows 0, I would believe that SQL is not fetching any row.
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 10:28 pm    Post subject:
Reply with quote

Bharath Bhat wrote:
Quote:
Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places


How are you so sure that the query is returnig rows? did you execute the query in spufi/qmf? I would suggest you to display SQLERRD(3) after the successful fetch. If it still shows 0, I would believe that SQL is not fetching any row.


Yeah I saw the records written in the spool. Actually I am fetching the records one by one in the loop and displaying the sqlerrd(3) after the successful fetch.
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: Thu Feb 11, 2010 10:42 pm    Post subject:
Reply with quote

Hello,

Look here (about 1/3 down the page):
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/D.1

Unless i've read something incorrectly, your result is correct. . .
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 11:06 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Look here (about 1/3 down the page):
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/D.1

Unless i've read something incorrectly, your result is correct. . .


Thanks you.
So sqlerrd(3) gives the number of rows fetched in case of rowset fetch only. Can I use any other variable ex. sqlerrd(1) or sqlerrd(2).What is sensitive static cursor?
or I have to use a host variable to get the count.
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: Thu Feb 11, 2010 11:13 pm    Post subject:
Reply with quote

Hello,

If your code reads one row at a time, simply add 1 to some counter after each row is read and when there are no more rows, you have the number of rows processed.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Feb 11, 2010 11:15 pm    Post subject: Reply to: sqlerrd(3) not behaving normally
Reply with quote

Quote:
Actually I am fetching the records one by one in the loop
You might need to know the row count for a multiple row fetch, in order to process properly the receiving array;
but, why would You want to get the row count for single row fetch?
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 11:20 pm    Post subject: Re: Reply to: sqlerrd(3) not behaving normally
Reply with quote

enrico-sorichetti wrote:
Quote:
Actually I am fetching the records one by one in the loop
You might need to know the row count for a multiple row fetch, in order to process properly the receiving array;
but, why would You want to get the row count for single row fetch?


Hi Please read the discussion from the starting.I have mentioned I have to process only if count is matching a certain number.I am not checking the count after each fetch,I am checking after the loop is over and in case of successful execution of the query.
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: Thu Feb 11, 2010 11:23 pm    Post subject:
Reply with quote

Hello,

Quote:
I have mentioned I have to process only if count is matching a certain number.
Then you might consider counting the rows before reading them individually. . .
Back to top
View user's profile Send private message
shaktiprasad

New User


Joined: 20 Aug 2006
Posts: 34
Location: chennai

PostPosted: Thu Feb 11, 2010 11:29 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Quote:
I have mentioned I have to process only if count is matching a certain number.
Then you might consider counting the rows before reading them individually. . .


I can use count(*) ,but there is some performance issue.So planning to execute the fetch for the matching count and then will fetch once again outside the loop. If the outside loop is giving some results then I will process accordingly(discussed with my friends).thanks all for your time.
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: Thu Feb 11, 2010 11:38 pm    Post subject: Reply to: sqlerrd(3) not behaving normally
Reply with quote

You're welcome - good luck icon_smile.gif

d
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Feb 11, 2010 11:44 pm    Post subject:
Reply with quote

Quote:
Hi Please read the discussion from the starting.I have mentioned I have to process only if count is matching a certain number.I am not checking the count after each fetch,I am checking after the loop is over and in case of successful execution of the query.


why do You complain? reread my post, looks You had troubles understanding it...

do You use multi row fetching/retrieval ?
yes ==> sqlerrd(3) contains the number of rows returned

no ==> You must be using using single row fetch/retrieval
sqlerrd(3) is not set, full stop
and You will have to count the rows by Yourself

for some optimization,
You might get out of the fetch loop after target+1 fetches

how many rows constitute the target ??
if the number is <small> storing the data into an array will certainly give better results

Quote:
sqlerrd(3) not behaving normally

why blame on somebody else a misunderstanding of the docs ???
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
No new posts SORT(DFSORT) behaving abnormal chandracdac DFSORT/ICETOOL 8 Thu Dec 10, 2015 3:58 am
No new posts DSNT408I SQLCODE = -443 with a -818 S... pjbluesman DB2 4 Wed Oct 03, 2012 9:02 am
No new posts ISPF panel scrollable field indicator... Ron Masters TSO/ISPF 14 Wed Jan 25, 2012 11:44 pm
No new posts JCL behaving differently with differe... vinothsubramanian JCL & VSAM 3 Fri Jun 26, 2009 6:09 pm
No new posts Sort behaving strange!! candyboy JCL & VSAM 2 Thu Jun 18, 2009 9:29 pm


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