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
 

 

What happens to cursors opened two times

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

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 1:45 am    Post subject: What happens to cursors opened two times
Reply with quote

Hello, I have a problem:

I've got a cursor in my program. I open it, fetch some rows, and then I close it. When I open it again, the first fetch give me an SQLCODE 100.

Why this happened? I thought the cursor would return the same rows again...

An important thing: before I open the cursor for the second time, the table used is UPDATED. I tried with a COMMIT before the 2nd OPEN, but doesn't work.

Any help is gratefull...
Bye!!

PD: Sorry for my english, I'm from Argentina.
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Nov 26, 2008 1:48 am    Post subject:
Reply with quote

Hi

Please provide Declare Cursor, Open, fetch and Update statements.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Wed Nov 26, 2008 1:59 am    Post subject:
Reply with quote

Check the fields in your WHERE statement.
They probably have different values than during the 1st OPEN.
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 5:50 pm    Post subject:
Reply with quote

The cursor is a simple one, like

Code:

EXEC SQL                     
  DECLARE cursor_name CURSOR FOR
  SELECT  variable 1,
               variable 2,
               etc...
  FROM   table
  WHERE  BANCO    = :BANCO          AND
         PROD           = :PROD           AND
         SUBPROD     = :SUBPROD        AND
         TIPO           = :TIPO           AND
         CCC            = :CCC            AND
         OPERAC         = :OPERAC         AND
         TAREA          = :TAREA          AND
         CUOTA         <> 0                     
  ORDER BY CUOTA ASC, SECUENCIA DESC         
 END-EXEC.                                           


I have checked the fields from the Where statement before both OPENs, and they have the same values.

saludos,
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Nov 26, 2008 6:26 pm    Post subject:
Reply with quote

Quote:

An important thing: before I open the cursor for the second time, the table used is UPDATED.


any chance that any of the following columns where updated?

BANCO
PROD
SUBPROD
CCC
OPERAC
TAREA
CUOTA
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 6:41 pm    Post subject:
Reply with quote

Anyone of that columns where updated...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Nov 26, 2008 6:55 pm    Post subject:
Reply with quote

Nahuel,

Reason I asked: these are columns in your WHERE clause.
If any were UPDATEd,
then the results of the second cursor are not surprising.

the rows were a part of the first cursor.
change any of these columns
and then the second cursor will not find them,
because they nolonger contain the same values
that you used in your host variables of the WHERE clause.
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 7:06 pm    Post subject:
Reply with quote

Dick, I've misunderstood what I typed, my apologize.

I mean that none of the columns where updated.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:36 pm    Post subject:
Reply with quote

NahuelTori,

Are the values of the host variables in the WHERE condn. the same before each OPEN. Try DISPLAY ing these values.
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 7:40 pm    Post subject:
Reply with quote

Arun, yes they are the same.
I've DISPLAYed it and checked.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Wed Nov 26, 2008 7:44 pm    Post subject:
Reply with quote

NahuelTori,

Are you sure that you're the only person playing around with this table. icon_smile.gif
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Wed Nov 26, 2008 10:32 pm    Post subject:
Reply with quote

Yes, during the execution, no one modifies the table except me.
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Thu Nov 27, 2008 7:24 pm    Post subject:
Reply with quote

Well, I've solved it creating another cursor with the same code, but different name. This time it worked.

I think the problem was that the table has being taken, but I didn't realize where.

Thank you everyone for your help!!
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2171
Location: @my desk

PostPosted: Thu Nov 27, 2008 7:59 pm    Post subject:
Reply with quote

NahuelTori,

Did you mean that the problem got solved just by changing the cursor name on second open. icon_eek.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Nov 27, 2008 8:03 pm    Post subject:
Reply with quote

the reason we can not solve these problems is because we don't understand this heavy tech language:
Quote:
... table has being taken, but I didn't realize where



Changing the name on the cursor should make no difference.
Now that you have two (2) cursor declarations, 2 cursor opens , 2 cursor fetches, and 2 closes - that have the same where clause??

sorry, your solution to this 'problem' does not make any sense to me.
Back to top
View user's profile Send private message
NahuelTori

New User


Joined: 29 Sep 2008
Posts: 9
Location: Argentina

PostPosted: Thu Nov 27, 2008 8:15 pm    Post subject:
Reply with quote

Quote:
we don't understand this heavy tech language

I apologize again for my english, I'm still learning...


Yes, I have 2 cursor declarations, 2 cursor opens, etc, etc, with 2 paragraphs each, that have the same WHERE clause.

Also does not make any sense to me, if I find the reason promise I tell you.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Nov 27, 2008 8:17 pm    Post subject:
Reply with quote

you mentioned before that your language skills were lacking
and I apologize to you for my comment - about the tech lang.

But, as you, I don't understand the solution.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu Nov 27, 2008 9:58 pm    Post subject: Reply to: What happens to cursors opened two times
Reply with quote

Yes, I too am surprised by the "solution".
Do you fetch all the rows returned by the 1st cursor ?
Do you always receive SQLCODE = 100 in the 2nd cursor ?
The fields in the WHERE are not the same ones as the FETCH INTO ?
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 How to calculate an specific transact... lind sh CICS 2 Tue Sep 13, 2016 9:20 pm
No new posts OMEGAMON to see In-DB2 times and Wait... Archana89 IBM Tools 4 Fri Mar 04, 2016 11:28 am
This topic is locked: you cannot edit posts or make replies. Executing JCL specific steps at speci... Shruti Takkar JCL & VSAM 6 Mon Feb 22, 2016 12:39 pm
No new posts Repeating input records for variable ... ksouren007 DFSORT/ICETOOL 1 Fri Jul 31, 2015 4:16 pm
No new posts Executing a Job Step/ Job multiple ti... beeram527 JCL & VSAM 5 Fri Dec 12, 2014 7:28 am


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