Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query in fetch delete .

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

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Tue May 26, 2009 2:08 pm    Post subject: Query in fetch delete .
Reply with quote

hi,

I have a small query .

1. I am reading a table with cursor based on account number .So all the account numbers are been fetched in to temp memory
2. Now iam fetching the records one by one.
3. On some processing Iam deleting some next ten rows from the table.


4. On the next fetch which records will be retrived from the fetch, whether the records deleted or the 11th record from table.

please let me know asap
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 26, 2009 2:37 pm    Post subject:
Reply with quote

Quote:


On some processing Iam deleting some next ten rows from the table.


???

Whichever rows satisfied the cursor will still be available ....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue May 26, 2009 4:25 pm    Post subject:
Reply with quote

ramakrishnan

Quote:
I am reading a table with cursor based on account number

what exactly is the DEFINE CURSOR statement.

Quote:
So all the account numbers are been fetched in to temp memory


temp memory? are you trying to be technical? What do you mean by this?

Quote:
Now iam fetching the records one by one

that depends on the DEFINE CURSOR statement and the clauses of the FETCH statement.

Quote:
On some processing Iam deleting some next ten rows from the table.

which table? Do you issue a COMMIT anytime during this processing.

Quote:
On the next fetch which records will be retrived from the fetch, whether the records deleted or the 11th record from table.

that depends on the answers to the above questions.

Quote:
please let me know asap

you are kidding, right?
have you tried this yourself - not a very involved test program.
and you realize, the lack of information provided, precludes any answer other than Ashmir's?
You think we have nothing else to do except use our time for your technically unqualified theoretical BS?
Back to top
View user's profile Send private message
r2k1984

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Tue May 26, 2009 6:14 pm    Post subject:
Reply with quote

hey see Iam explaingin in detail.

I have declared a cursor to read all the records from the database whcih contains some 500 accounts.

on processing Iam deleting 100 with delete statement ,

Now I need to know whether it will be updated in the table and also in the cursor.

I dont want the accounts to come in the fetch statement and process the records .

Whether the problem will be solved after if I put a commit statement after the delete.

Or I should use the cursor with update option in the cursor definition.

Thanks in advance for the help
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Tue May 26, 2009 7:01 pm    Post subject: Reply to: Query in fetch delete .
Reply with quote

r2k1984,
Quote:
[ I should use the cursor with update option in the cursor definition.
....How would this help you in your current scenario.
Quote:
Whether the problem will be solved after if I put a commit statement after the delete.
Your processing should be logical, Open the cursor, fetch from the cursor, delete rows from the table and then issue a commit ....Again open the cursor and carry on similar lines as per your logic or requirement.

WTH
Back to top
View user's profile Send private message
r2k1984

New User


Joined: 21 Jun 2005
Posts: 69
Location: chennai

PostPosted: Tue May 26, 2009 7:33 pm    Post subject:
Reply with quote

I dont want to close the cursor and open it again because it will be overburden and it will go in loop if last record is deleted during the processing.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Tue May 26, 2009 7:45 pm    Post subject: Reply to: Query in fetch delete .
Reply with quote

Quote:
I dont want to close the cursor and open it again because it will be overburden
All the open cursors will close on commit until and unless you are using 'WITH HOLD' option....so you don’t go and explicitly close them.
Quote:
it will go in loop if last record is deleted during the processing.
There is nothing like first and last in the result set until you are not using order by in your query. Secondly, what makes you think it would go in a loop, use the SQL codes to determine your logic.

Do let us know your final results.

WTH
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue May 26, 2009 8:14 pm    Post subject:
Reply with quote

On what basis are you deleting the records ???... why dont you keep track of all records to be deleted ( values from cursor) and issue a single delete ????
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: Tue May 26, 2009 9:44 pm    Post subject:
Reply with quote

Hello,

Quote:
I dont want to close the cursor and open it again because it will be overburden
Where did this belief originate?

Quote:
it will go in loop if last record is deleted during the processing.
No, it won't if the code is written properly. FWIW - there is no "last record".
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us