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
 

 

Positional Delete / Update with Dynamic SQL Cursor

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

New User


Joined: 05 May 2010
Posts: 19
Location: USA

PostPosted: Fri May 17, 2013 3:13 pm    Post subject: Positional Delete / Update with Dynamic SQL Cursor
Reply with quote

Hi All,

I was writing a COBOL code, that reads a SQL Select statement from a file. Then creates a dynamic cursor with ROWSET POSITIONING to faciliate multirow fetch. The cursor is then prepared and opened. NEXT ROWSET is fetched and displayed.
The code is working till here.
Next, I am adding a positional DELETE / UPDATE on the ROWSET to either delete the rows or update them at one go.
the problem starts here. Even though the COBOL Compile / DB2 Precomile works fine, the BIND fails with SQLCODE -504.
I am not sure how to solve this. When I am changing the Dynamic to a STATIC cursor in the program, the positional DELETE works fine.

Is it like, I can not use a positional DELETE with a Dynamic CURSOR?
When I do the same in REXX (without ROWSET POSITIONING, as DSNREXX still does not support multirow fetch), it works fine, where as REXX DB2 execution is Dynamic in nature.

here are the SQLCODE in my COBOL. The DB2 version used in our shop is V8

Dynamic Cursor:
Code:
 SELECT 1 FROM T1 WHERE DATE BETWEEN 19980101 AND 19981231
FOR UPDATE


DECLARE Statement

Code:

EXEC SQL                                                 
     DECLARE C51 CURSOR WITH ROWSET POSITIONING WITH HOLD
      FOR S51 
END-EXEC.   


PREPARE Statement (SATRING-VAR has the select statement)

Code:

EXEC SQL                         
    PREPARE S51 FROM :STRING-VAR 
END-EXEC. 


Open Cursor

Code:

EXEC SQL     
  OPEN C51   
END-EXEC.


Fetch Cursor (Execution works fine till here)
Code:

EXEC SQL                                       
  FETCH   NEXT ROWSET FROM C51 FOR :LOOP ROWS 
INTO    :WS-DUMMY-OUT:WS-DUMMY-NULL-IND   
END-EXEC


POSITIONAL DELETE - Bind Fails with -504 when I add this code.
Code:

EXEC SQL                   
  DELETE FROM T1       
    WHERE CURRENT OF C51   
END-EXEC.


COMMIT after the Delete
Code:

EXEC SQL   
  COMMIT   
END-EXEC.


If any of you can help here please.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Sat May 18, 2013 1:39 am    Post subject:
Reply with quote

Cursor cursor-name was referenced in a positioned UPDATE or DELETE statement which is not a supported operation for an allocated cursor....

I think multi row delete is not possible.
Back to top
View user's profile Send private message
Mainak_Dalal

New User


Joined: 05 May 2010
Posts: 19
Location: USA

PostPosted: Sun May 19, 2013 1:52 am    Post subject: Reply to: Positional Delete / Update with Dynamic SQL Cursor
Reply with quote

@ gylbharat , Multirow delete is possiible. As I said, when I change the dynamic to a static SQL cursor. It works fine.
Again, as I said, RE XX, which uses dynamic SQLs, is doing the positional delete for single fetch. With the current option of DSNREXX, multirow fetch is not possible.

I think with the COBOL program, I am missing some bind parameter. Not sure though.

Does any one else have any thought?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Sun May 19, 2013 2:28 am    Post subject:
Reply with quote

I Would certainly ask why you need program for delete?

Why not use just DSNTEP2?
Back to top
View user's profile Send private message
Mainak_Dalal

New User


Joined: 05 May 2010
Posts: 19
Location: USA

PostPosted: Mon May 20, 2013 7:21 pm    Post subject:
Reply with quote

Hi Pandora Box, There are mainly 2 reasons for not using DSNTEP2.

First, there are few intermediate business logics to be applied on the fetched records
Second, The number of record is huge. Deleting them at one go will exceed the DB2 logsize limit and will cause an S04E. That is why I will also have to apply intermediate commits while deleting through a program.
Back to top
View user's profile Send private message
Mainak_Dalal

New User


Joined: 05 May 2010
Posts: 19
Location: USA

PostPosted: Tue May 21, 2013 12:07 pm    Post subject:
Reply with quote

Guys, I was finally successful in implementing a solution. A very easy one :-)

In my prior code, even though the cursor was declared dynamic, the position delete was still a Static statement. I changed everything to dynamic, and it worked fine.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue May 21, 2013 3:16 pm    Post subject:
Reply with quote

Thanks for telling what worked for you. As SQLCODE=-504 says that CURSOR NAME cursor-name IS NOT DECLARED, which didn't really trigger 'this slow-processor' for a better suggestion. icon_smile.gif

If possible can you please share the code which worked for you.
Back to top
View user's profile Send private message
Mainak_Dalal

New User


Joined: 05 May 2010
Posts: 19
Location: USA

PostPosted: Wed May 22, 2013 10:02 am    Post subject:
Reply with quote

Anuj, As I said in my last post, I changed all the SQL codes to dynamic. The Static DELETE statement was converted to a COBOL string and an EXECUTE IMMEDIATE was issued on that string. It resolved the issue.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed May 22, 2013 5:36 pm    Post subject:
Reply with quote

Thanks Mainak.
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 delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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