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
 

 

cursors or select query to use??

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

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Sun Jun 21, 2009 5:01 pm    Post subject: cursors or select query to use??
Reply with quote

Hi,

Just wanted to know which will be the better option in a cobol db2 program,in relevance to performance.

option A:
1. declare a cursor for a select query say A for a table 1.
2. open the cursor.
3. fetch a record.
4. update the same record with some new values then commit.
5. insert new record with few manipulations to fetched record then commit
6.close the cursor
7. repeat the above steps from 2 to 5.
8. close the cursor when no more records are available.

Now there might be a possibility that the record inserted might qualify for the query declared by the cursor so hence step 6 & 7. correct me if i am wrong on this.

option b
1. execute a static SQL for select query A for table 1, with fetch first 1 row only.
2. update table 1 ( for the same record returned by the select query) with some new values. then commit.
3.insert new record with few manipulations to fetched record. then commit.
4. repeat above steps till no more records are available.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Sun Jun 21, 2009 6:02 pm    Post subject:
Reply with quote

I would look at the different cursor options - scrollable, sensitive, etc...

and I would not commit after every update, or repeatedly close and open a cursor. There is no need to do so (after your research of above, you will understand).
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Sun Jun 21, 2009 8:20 pm    Post subject:
Reply with quote

Quote:
5. insert new record with few manipulations to fetched record then commit


why not insure that one of these "manipulations" disqualifies the new ROW?

knowing and using the correct terminology will help in asking questions and reading/understanding documentation.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Jun 22, 2009 1:56 am    Post subject:
Reply with quote

If you were worried that a newly inserted row would become part of the cursor,
Why would closing the cursor and reopening prevent this?

you really need to read about cursor handling, concepts and options available
for your version of db2.
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Mon Jun 22, 2009 12:12 pm    Post subject:
Reply with quote

its all about handling any given situation,

Quote:
why not insure that one of these "manipulations" disqualifies the new ROW?


business rules/data doesnt allow me.as far as the data we have now it might not happen.. but i am just making sure the program is foolproof.

Quote:
If you were worried that a newly inserted row would become part of the cursor,
Why would closing the cursor and reopening prevent this?

with the knowledge of cursors, i had till yesterday. once i fetch a record and insert into the target table a new row, the next fetch wouldnt retrieve me the inserted row since it fetches from the temporary table.
but thanks for widening the knowledge on cursors i am using the other option now
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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