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
 

 

USE OF CURSOR TO RETRIVE DATA

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

New User


Joined: 01 Apr 2006
Posts: 17
Location: Pune

PostPosted: Mon Apr 17, 2006 4:24 pm    Post subject: USE OF CURSOR TO RETRIVE DATA
Reply with quote

I WANT TO FETCH MORE THAN 1 ROWS FROM TABLE..IS USE OF CURSOR NECESSARY FOR THAT.HOW CAN I DO THAT..PLZ GIVE ANY EG.
NOTE :-THROUGH EMBEDDED SQL(COBOL+SQL)


THANKS IN ADVANCE..!
BYE...
Back to top
View user's profile Send private message

vidhyanarayanan

New User


Joined: 23 Dec 2005
Posts: 52

PostPosted: Mon Apr 17, 2006 4:37 pm    Post subject:
Reply with quote

Hi sandy_mcs,

cursor is mainly to retrieve more than one row of a table....
it involves 4 steps.

* Declare statement
* open statement
* fetch statement
* close statement.

syntax for fetch:
fetch cursor name into
: ws var1
: ws var2
Back to top
View user's profile Send private message
r2k1984

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Mon Apr 17, 2006 4:40 pm    Post subject: Re: USE OF CURSOR TO RETRIVE DATA
Reply with quote

u will not be able to get many rows at a time in embedded sql.but this can be overcome by using cursor command.
1).first for this u have to createa cursor
2). open the cursor.
3). fetch the values .
4). close the cursor.
these are about cursor.
Back to top
View user's profile Send private message
Jerry

New User


Joined: 16 Sep 2005
Posts: 42

PostPosted: Mon Apr 17, 2006 4:47 pm    Post subject:
Reply with quote

Hi Sandy,

Yes, if you need to retrieve more than one row from a table, you would need to use cursor.

You could get the exact syntax from the DB2/UDB V7 Programming Guide manual in this forum.

Correct me, if I am wrong.

Regards,
Ramya.
Back to top
View user's profile Send private message
sandy_mcs

New User


Joined: 01 Apr 2006
Posts: 17
Location: Pune

PostPosted: Mon Apr 17, 2006 5:33 pm    Post subject: Subsequent to (USE OF CURSOR TO RETRIVE DATA)
Reply with quote

I have written following cobol prog.

IDENTIFICATION DIVISION.
PROGRAM-ID. QUERY3.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 EID PIC 9(3).
77 ENAME PIC X(15).
77 EADDR PIC X(15).
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLOYEE
END-EXEC.
EXEC SQL
DECLARE CUR3 CURSOR FOR
SELECT ENAME,EADDR FROM LEM0U13.EMPLOYEE
END-EXEC.
PROCEDURE DIVISION.
EXEC SQL
OPEN CUR3
END-EXEC.
PERFORM FETCH-PARA UNTIL SQLCODE = 100.
PERFORM CLOSE-PARA.
STOP RUN.

and I am getting s322 error
I cant get the reason behind this..
I need help...
Back to top
View user's profile Send private message
vidhyanarayanan

New User


Joined: 23 Dec 2005
Posts: 52

PostPosted: Mon Apr 17, 2006 5:39 pm    Post subject:
Reply with quote

Hi,
s322 is time out error.....give more time before submitting jcl.....and
try......
Back to top
View user's profile Send private message
sri.mainframes

New User


Joined: 16 Feb 2006
Posts: 29
Location: MUMBAI

PostPosted: Mon Apr 17, 2006 6:47 pm    Post subject:
Reply with quote

Hi Sandy,

cursor is simillar to a file.when u issue a declare cursor statement , the cursor holds the table of data that matches the qualifying criteria .


host program can process one record at a time , it can not process more than one row at a time. first Fetch statement of cursor retrieves first record . again if u r issuing fetch statement it retrives the next record.

hope this will clear u

thanks
sri.prince
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Apr 18, 2006 4:49 am    Post subject: Re: USE OF CURSOR TO RETRIVE DATA
Reply with quote

Sandy,

Both vidhyanarayanan and sri.mainframes are correct in their posts.

An S322 is a timeout, which means you are in a loop somewhere, or you are processing a massive amount of data (I don't think this is the case with the EMPLOYEE table).

I see that you did not check the outcome of the open cursor. If this failed for some reason, the "PERFORM FETCH-PARA UNTIL SQLCODE = 100" would be in an endless loop, hence an S322 abend. I'm not saying this is the case, but it could be.

Also, please show us the code for "FETCH-PARA" as the loop may be in there also. If there is an error during the fetch and there is no error processing within the "FETCH-PARA" paragraph, this could be the problem.

What is it that you want to do with the rows when they are fetched? If "FETCH-PARA" only does the fetch I don't see where you are processing the rows.

Dave
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 Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm


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