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
 

 

DB2 Stored Procedures - Getting SQLCODE +100 during Fetch

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

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Fri Jun 12, 2009 9:22 pm    Post subject: DB2 Stored Procedures - Getting SQLCODE +100 during Fetch
Reply with quote

Hi,
I am facing a strange problem. I have declared the cursor in a Stored Proc. The Stored proc opens the cursor succesfully but during the FETCH statement it always gives a SQLCODE of +100. On executing the query, i am able to retrieve the output. Even on changing the Input to the Stored Procedure, i am still facing the same problem.
Does anyone have any idea about it??
Back to top
View user's profile Send private message

Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri Jun 12, 2009 11:00 pm    Post subject: Reply to: DB2 Stored Procedures - Getting SQLCODE +100 durin
Reply with quote

Hi

Please check your DECLARE CURSOR statement. Make sure it is valid for a stored procedure. Or post your cursor declaration statement in the forum. Someone will help.

Regards
Raghu
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: Sat Jun 13, 2009 5:57 am    Post subject:
Reply with quote

Hello,

Possibly there is something i misunderstand, but an sqlcode of 100 is not necessarily an error/problem when fetching rows from a cursor. It just means no more data. . .
Back to top
View user's profile Send private message
rahuindo

New User


Joined: 09 Apr 2008
Posts: 83
Location: Chennai

PostPosted: Mon Jun 15, 2009 9:25 am    Post subject: Reply to: DB2 Stored Procedures - Getting SQLCODE +100 durin
Reply with quote

Hi,
I executed the query in the program by directly passing the Input in the query itself. I was able to retrieve the results. But when i pass the value from the Client program to the Stored Procedures, i get the result as +100.
Here is my declaration of the Stored Procedure:
Code:

01 SP-INP-L4-SSN-NUM.                               
   49 SP-INP-L4SSN-LTH             PIC S9(4) COMP.   
   49 SP-INP-L4SSN-NUM             PIC X(4).         
01 SP-INP-LAST-NAME.                                 
   49 SP-INP-LST-NM-LTH            PIC S9(4) COMP.   
   49 SP-INP-LST-NM                PIC X(30).       
01 SP-OUT-ERR-CD                   PIC S9(9) COMP.   
01 SP-OUT-ERR-MSG.                                   
   49 SP-ERR-MSG-LTH               PIC S9(4) COMP.   
   49 SP-ERR-MSG-TXT               PIC X(998).       

I had to take '49' variables becuase on passing the values from the Client pgm to Stub, the variables used to have 2 leading spaces and hence the requirement for the '49' variables.
Please let me know if i am missing something??
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Tue Jun 16, 2009 1:25 am    Post subject: Reply to: DB2 Stored Procedures - Getting SQLCODE +100 durin
Reply with quote

Hi rahuindo

Quote:
I was able to retrieve the results. But when i pass the value from the Client program to the Stored Procedures, i get the result as +100.


Many points to be cleared.
Quote:
I was able to retrieve the results.


Retrieving result means, you got the rows from the table into your working storage variable.

Quote:
But when i pass the value from the Client program to the Stored Procedures, i get the result as +100.


When you are passing value from Client program to Stored procedure(parameters), you should receive it in the linkage section. With the help of these values, declare and open cursor in the stored procedure. At the time of OPEN cursor you will not get any SQLCODE +100 in the STP(Stored Procedure).

How did you declared your locator variable in the client program?
Is the locator variables are properly used with sql statements?
At least you should post the program flow in the forum. Then only someone will help you.

Regards
Raghu
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 DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am


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